Edgewall Software

Opened 10 years ago

Closed 9 years ago

#10623 closed defect (duplicate)

When using an alias with a space in it for a column name in a report you get this error 'Date ASC LIMIT 100 OFFSET 0'

Reported by: signup@… Owned by:
Priority: normal Milestone:
Component: report system Version: 0.11.6
Severity: normal Keywords:
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description (last modified by Ryan J Ollos <ryano@…>)

Take the default Active Ticket report and modify it to add a space in the id column alias

SELECT p.value AS __color__,
   id AS ticket,
SELECT p.value AS __color__,
   id AS 'ticket id',

Now execute the report and sort on the 'ticket id' column and you will get the following error

Report execution failed: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Date ASC LIMIT 100 OFFSET 0' at line 13")

The can be traced to this file report.py

System info;
Trac: 0.11.6 
Python: 2.5.4 (r254:67916, Dec 23 2008, 15:10:54) [MSC v.1310 32 bit (Intel)] 
setuptools: 0.6c11 
MySQL: server: "5.1.41-community", client: "5.0.27", thread-safe: 1 
MySQLdb: 1.2.2 
Genshi: 0.5.1 
mod_python: 3.3.1 
Subversion: 1.6.6 (r40053) 
jQuery: 1.2.6 

Results of debug, notice the sort column is Due Date instead of 'Due Date' Without the single quotes around the column alias the order by clause causes an error.

2012-03-12 21:20:23,657 Trac[report] DEBUG: Columns [u'__color__', u'ticket', u'Due Date', u'summary', u'component', u'version', u'milestone', u'type', u'owner', u'status', u'created', u'_changetime', u'_description', u'_reporter'], Sort column Due Date
2012-03-12 21:20:23,657 Trac[report] DEBUG: Query SQL: SELECT * FROM ( 

SELECT p.value AS __color__,

   id AS ticket, d.value AS 'Due Date', summary, component, version, milestone, t.type AS type, 

   owner, status,

   time AS created,

   changetime AS _changetime, description AS _description,

   reporter AS _reporter

  FROM ticket t

  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'

  LEFT JOIN ticket_custom d ON t.id = d.ticket AND d.name = 'duedate'

  WHERE status <> 'closed'

  ORDER BY CAST(p.value AS signed), milestone, t.type, time

 ) AS tab  ORDER BY Due Date ASC LIMIT 100 OFFSET 0
2012-03-12 21:20:23,657 Trac[chrome] DEBUG: Prepare chrome data for request
2012-03-12 21:20:23,657 Trac[session] DEBUG: Retrieving session for ID u'anonymous'

Attachments (0)

Change History (3)

comment:1 by signup@…, 10 years ago

This appears to be the fix for the issue. Minimal testing has been done with MySQL and no testing was done with SQLite

Within report.py ## added quotes ` around the column name to be sorted in the ORDER BY clause ro handle spaces ## this was the original line order_cols.append(sort_col) order_cols.append("".join(['',sort_col,'']))

comment:2 by Ryan J Ollos <ryano@…>, 9 years ago

Description: modified (diff)

comment:3 by Christian Boos, 9 years ago

Resolution: duplicate
Status: newclosed

Duplicate of #7764.

Modify Ticket

Change Properties
Set your email in Preferences
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) 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.