Sorting reports on columns aliased to 'Est.' or 'Act.' fails with PostgreSQL

[joshuah@…]

Sorting a report on a column name aliased to end with a period (for example, 'Est.', 'Act.') fails with the following error in sqlite:

Report execution failed: no such column: Est.ASC

Here's the query for the report:

SELECT p.value AS __color__,
   id AS ticket, milestone, summary as 'Summ.', priority as 'Pri.', 
   date(milestone.Due,'unixepoch') as due,
   status,
   changetime AS _changetime, t.description AS _description
  FROM ticket t, enum p 
  JOIN milestone ON milestone.name=t.milestone
WHERE t.owner = '$USER' 
  AND status <> 'closed' 
  AND p.name = t.priority AND p.type = 'priority' 
ORDER BY owner, milestone.Due, p.value, t.type, time

Escaping the column name with single quotes in the ORDER BY clause generated by Trac seems to fix this problem.

[Remy Blank]

Replying to joshuah@…:

Escaping the column name with single quotes in the ORDER BY clause generated by Trac seems to fix this problem.

Would you mind attaching a patch? This would increase the chances of this getting fixed quickly :-)

[anonymous]

Replying to rblank:

Replying to joshuah@…:

Escaping the column name with single quotes in the ORDER BY clause generated by Trac seems to fix this problem.

Would you mind attaching a patch? This would increase the chances of this getting fixed quickly :-)

Well, the obvious path of using placeholders doesn't quite work. The query executes without error but the sort order appears to ignore the ORDER BY clause, probably because 'Est.' is interpreted as a string literal. Apparently double-quoting it is needed to make SQLite see it as an identifier; see ​SQLite Keywords.

[Remy Blank]

We should check if this is still present on trunk, as there have been some changes to the reports.

[Christian Boos]

Still a problem on trunk (r9234).

[Mark Mc Mahon]

I can't actually reproduce this issue on Trac 0.12dev-r9325. I looked through the log messages since R9234 bug didn't find anything in particular that looked like it would be a solution to this issue.

[Christian Boos]

The report given above in the description works fine for the SQLite backend, but when used here (PostgreSQL backend) it fails:

syntax error at or near "'Summ.'" LINE 3: id AS ticket, milestone, summary as 'Summ.', priority as ... ^

[mark.m.mcmahon@…]

Changed summary based on cboos comment to reference PostgreSQL rather than sqlite. (and also removed sqlite keywords)

[Remy Blank]

This is actually a duplicate of #7764.