Default reports ({6}, {7}, {11} maybe others) are not working for all backends

[Dave Abrahams <dave@…>]

at least under postgresql… there's a syntax error in the last line:

   (CASE status WHEN 'closed' THEN modified ELSE (-1)*p.value END) DESC

psql flags "modified" as a syntax error.

[Dave Abrahams <dave@…>]

Several fixes are required in this report:

1. $User becomes '$User'
2. modified becomes changetime
3. p.value needs to be cast to integer; I believe the correct SQL is CAST ( p.value AS integer ) though for PostgreSQL only you can use p.value::integer, which is what I tested it with.

[Christian Boos]

#5644, #6334 and #6491 were closed as duplicates.

[Christian Boos]

#5840 was closed as duplicate.

[Christian Boos]

#5756 was closed as duplicate (mentions the same fix as suggested in comment:1)

[Christian Boos]

Another duplicate, #6846.

[trac-ja@…]

I encount this bug at {6} with native stacktrace. ({7} runs normally.)

There are two problems:

1. SQL compatibility
2. When catch Exception from DB, Must show TracError page

This patch fixes problem 1 againsts source:trunk@6653​.

It is tested on SQLite-3.4.3 (on Ubuntu-7.10) and PostgreSQL-8.2.6(on Ubuntu-7.10), however it is not tested on MySQL.

trac/db_default.py

@@ -280 +280 @@
   FROM ticket t
   LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
   ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'),
-        (CASE status WHEN 'closed' THEN modified ELSE (-1)*p.value END) DESC
-"""),
+        (CASE status WHEN 'closed' THEN changetime ELSE (-1) * %s END) DESC
+""" % db.cast('p.value', 'int')),
 #----------------------------------------------------------------------------
 ('My Tickets',
 """

[Christian Boos]

Thanks for the patch! I'll (reluctantly) check for MySQL.

I think it's worth getting applied for 0.11, as we can anticipate a lot of fresh installs when 0.11 ships and … a lot of new duplicates of this ticket if it is not fixed before.

[Christian Boos]

Verified for MySQL and committed as [6674].