sorting on custom field label with multiple words generates "Report execution failed: ... syntax error"

[anonymous]

Our trac installation (0.11.7) has a custome field, named due_date with label "Due Date". The following is a query that presents Due Date as an column:

SELECT p.value AS __color__,
   id AS ticket, summary, t.type AS type, 
   owner, status,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter,
   c.value as 'Due Date'
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  left join ticket_custom c on (t.id = c.ticket and c.name = 'due_date')
  WHERE status <> 'closed' and t.type = 'task' 
  ORDER BY CAST(p.value AS integer), milestone, t.type, time

When this query is executed and the user clicks on the Due Date colume to sort the query result, the following error will show up:

Report execution failed: near "Date": syntax error

Further investigation using trac's debug level logging shows the failed query is

2011-06-13 15:17:21,004 Trac[report] DEBUG: Query SQL: SELECT * FROM ( SELECT p.value AS __color__,
   id AS ticket, summary, t.type AS type, 
   owner, status,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter,
   c.value as 'Due Date'
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  left join ticket_custom c on (t.id = c.ticket and c.name = 'due_date')
  WHERE status <> 'closed' and t.type = 'task' 
  ORDER BY CAST(p.value AS integer), milestone, t.type, time
 ) AS tab  ORDER BY Due Date DESC LIMIT 100 OFFSET 0

The problem is in the last line. The label "Due Date" contains a space and should be quoted. Replacing it with the following line

 ) AS tab  ORDER BY `Due Date` DESC LIMIT 100 OFFSET 0

generats correct result.

The fix:

--- report.py.orig	2011-06-13 15:26:15.000000000 -0400
+++ report.py	2011-06-13 14:57:57.000000000 -0400
@@ -571,7 +571,7 @@
                 if '__group__' in cols:
                     order_cols.append('__group__')
                 if sort_col in cols:
-                    order_cols.append(sort_col)
+                    order_cols.append("`" + sort_col + "`")
                 else:
                     raise TracError(_('Query parameter "sort=%(sort_col)s" '
                                       ' is invalid', sort_col=sort_col))

[Remy Blank]

Thanks for the report. Quoting is backend-specific, so the fix will be slightly different, but the idea is correct.

[anonymous]

From the reporter of the bug: The bug only exists in 0.11.7. It doesn't exist in 0.12. I know this since we have an installation of 0.12, too.

[Remy Blank]

Replying to anonymous:

From the reporter of the bug: The bug only exists in 0.11.7. It doesn't exist in 0.12. I know this since we have an installation of 0.12, too.

Oh, thanks for clarifying. We have indeed added some quoting in 0.12, so this must have been covered.

0.11.7 is end-of-life, so we won't backport the fix.