[Python-apps-team] Bug#630739: trac: sorting on custom field label with multiple words generates "Report execution failed..."

[Xiaolin Charlene Zang]

Package: trac
Version: 0.11.7
Severity: normal

*** Please type your report below this line ***

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

The following fix may not be the right thing but it works

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

Note: This bug doesn't exist in 0.12.

-- System Information:
Debian Release: 6.0.1
   APT prefers stable
   APT policy: (500, 'stable')
Architecture: i386 (i686)

Kernel: Linux 2.6.32-5-686-bigmem (SMP w/1 CPU core)
Locale: LANG=en_US.utf8, LC_CTYPE=en_US.utf8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash

Versions of packages trac depends on:
ii  python                  2.6.6-3+squeeze6 interactive high-level object-orie
ii  python-central          0.6.16+nmu1      register and build utility for Pyt
ii  python-genshi           0.6-1            Python XML-based template engine
ii  python-setuptools       0.6.14-4         Python Distutils Enhancements (set

Versions of packages trac recommends:
ii  apache2                2.2.16-6+squeeze1 Apache HTTP Server metapackage
ii  apache2-mpm-prefork [h 2.2.16-6+squeeze1 Apache HTTP Server - traditional n
ii  libjs-jquery           1.4.2-2           JavaScript library for dynamic web
ii  python-pygments        1.3.1+dfsg-1      syntax highlighting package writte
pn  python-subversion      <none>            (no description available)
ii  python-tz              2010b-1           Python version of the Olson timezo

Versions of packages trac suggests:
ii  libapache2-mod-wsgi       3.3-2          Python WSGI adapter module for Apa
pn  python-docutils           <none>         (no description available)
ii  python-textile            1:2.1.4-1      Python parser for the Textile mark
ii  trac-accountmanager       0.2.1+r7731-1  account management plugin for Trac
pn  trac-authopenid           <none>         (no description available)
pn  trac-bitten               <none>         (no description available)
pn  trac-bzr                  <none>         (no description available)
ii  trac-customfieldadmin     0.2.2+r6485-1  panel for administrating custom ti
ii  trac-email2trac           1.0.0-1        Creates and amends Trac tickets fr
ii  trac-git                  0.0.20100513-2 Git version control backend for Tr
ii  trac-graphviz             0.7.5-1        Graphs printing plugin for Trac
pn  trac-ja-resource          <none>         (no description available)
ii  trac-mastertickets        2.1.3+r4179-1  adds inter-ticket dependencies to
pn  trac-mercurial            <none>         (no description available)
pn  trac-spamfilter           <none>         (no description available)
ii  trac-wikiprint            1.6+r5729-1    Make Trac wiki pages printable, ex
ii  trac-wikirename           2.1.1-1        trac plugin which allows to rename
pn  trac-wysiwyg              <none>         (no description available)
pn  trac-xmlrpc               <none>         (no description available)

======================================================================