Edgewall Software
Modify

Opened 13 years ago

Closed 13 years ago

#10223 closed defect (wontfix)

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

Reported by: anonymous Owned by: Remy Blank
Priority: normal Milestone:
Component: report system Version: 0.11.7
Severity: normal Keywords:
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description

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))

Attachments (0)

Change History (3)

comment:1 by Remy Blank, 13 years ago

Milestone: 0.12.3
Owner: set to Remy Blank

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

comment:2 by anonymous, 13 years ago

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.

in reply to:  2 comment:3 by Remy Blank, 13 years ago

Milestone: 0.12.3
Resolution: wontfix
Status: newclosed

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.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Remy Blank.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Remy Blank to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.