Tickets with created with "" Milestone show up under "Release"

[Markus Tacker <m@…>]

If you set up a new test env. You have the dropdown box with "", "milestone1", "milestone2", "milestone3" and "milestone4".

If you create a ticket and do not select a milestone it will show up in the {3} All Tickets by Milestone Report under "Release" and not under "None".

See {6} for reference in this trac.

[anonymous]

The default reports are just examples so you may need to tweak them. I only use the custom query UI now, but I reenabled reports to see what I did to fix it when I used the reports. I have that report as:

SELECT p.value AS __color__,
   (CASE milestone WHEN '' THEN 'None' ELSE milestone||' Release' END) AS __group__,
   id AS ticket, summary, component, version, t.type AS type, severity, 
   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t, enum p
  WHERE status IN ('new', 'assigned', 'reopened') 
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY (milestone IS NULL),milestone, p.value, severity, time

Not sure if it is totally correct since I really don't know SQL. I am also not sure if this needs fixed in the core since the reports module is on the way out.

[Markus Tacker <m@…>]

I've tweaked the report. This helps:

SELECT p.value AS __color__,
   (CASE milestone WHEN '' THEN NULL ELSE milestone END) AS __group__,
   id AS ticket, summary, component, t.type AS type, 
   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t, enum p
  WHERE status IN ('new', 'assigned', 'reopened') 
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY __group__, p.value, t.type, time

[sid]

This still happens with one of the default reports from Trac. For current trunk (r4377), the patch to fix this would be:

trac/db_default.py

@@ -217 +217 @@
 """,
 """
 SELECT p.value AS __color__,
-   %s AS __group__,
+   (CASE milestone WHEN '' THEN 'None' ELSE %s END) AS __group__,
    id AS ticket, summary, component, version, t.type AS type, 
    (CASE status WHEN 'assigned' THEN %s ELSE owner END) AS owner,
    time AS created,

Or instead of 'None', you could use NULL as Markus points out.

[Christian Boos]

or (no milestone set).

[Thijs Triemstra]

That patch from comment 4 doesn't seem to work here on 0.12-stable, getting:

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 '%s END) AS __group__,\r\n id AS ticket, summary, component, version, t.type AS ' at line 2")

[Christian Boos]

Replying to thijstriemstra:

That patch from comment 4 doesn't seem to work here on 0.12-stable, getting:

 '%s END) AS __group__,

Well, the patch being meant for trac/db_default.py you need to expand '%s' by hand before testing it manually.