Report fails because of complex SQL

[trac-ja@…]

When Report has complex SQL like:

SELECT (CASE owner WHEN '$USER' THEN '1:My own tickets'
        ELSE CASE reporter WHEN '$USER' THEN '2:Tickets reported by me'
        ELSE '3:Tickets join as CC' END END) AS __group__,
       value AS __color__,
       id AS ticket,
       summary,
       type AS _type,
       component AS _component,
       milestone,
       status,
       resolution,
       version AS _version,
       priority AS _priority,
       severity AS _severity,
       time AS _created,
       changetime AS modified,
       reporter AS _reporter,
       owner,
       cc AS _cc,
       description AS _description
FROM ticket t,
     (SELECT name, value FROM enum WHERE type = 'priority') p
WHERE t.priority = p.name AND
      (reporter = '$USER' OR owner = '$USER' OR cc like '%$USER%') AND
      NOT EXISTS(SELECT *
                 FROM ticket_custom
                 WHERE name = 'completed' AND
                       value = '1' AND
                       ticket = id)
ORDER BY __group__, __color__, id

fails with that message:

Report execution failed: unsupported format character ''' (0x27) at index 769

Perhaps, This error occurs at source:/tags/trac-0.9.4/trac/db.py@2575#L217 that replacing %s to ?.

Additionally, It doesn't occur in trac-0.9.3, but db.py is no diffs between trac-0.9.3 to trac-0.9.4.

[anonymous]

Sorry, SQL on description uses custom fields.

It occurs this SQL:

SELECT (CASE owner WHEN '$USER' THEN '1:My own tickets'
        ELSE CASE reporter WHEN '$USER' THEN '2:Tickets reported by me'
        ELSE '3:Tickets join as CC' END END) AS __group__,
       id AS ticket,
       summary
FROM ticket t
WHERE (reporter = '$USER' OR owner = '$USER' OR cc like '%$USER%')
ORDER BY __group__, id

Error message is:

Report execution failed: unsupported format character ''' (0x27) at index 284

[trac@…]

Also using SQL wildcards causes the following error

Report execution failed: not enough arguments for format string

Shortest query I could come up with to test this is

SELECT id as ticket
FROM ticket
WHERE keywords LIKE '%test%'

This problem did not occur in version 0.9.3 either.

[trac@…]

Update, the SQL wildcard can be worked around by using %% instead of %, but I couldn't find this in the documentation for reports, so if this is going to be the standard way to do wildcards maybe just a doc change is needed.

[mgood]

I've added a note on the TracUpgrade page about it, but I'm working on a fix/workaround for this.

[mgood]

Ok, [2941] should address this, but I'm going to get some feedback on the MailingList before merging to 0.9-stable.

[ritter@…]

the workaround (using %% instead of %) doesn't work in all cases:

works:

SELECT id as ticket
FROM ticket
WHERE keywords LIKE '%%test%%'

error:

SELECT id as ticket
FROM ticket
WHERE keywords LIKE '%%$USER%%'

error message: Report execution failed: near "ritter": syntax error

[anonymous]

Can confirm this bug is still in 0.9.6

[Piotr Banasik <piotr@…>]

Hi,

We just upgraded from 0.8.x to 0.9.6 and this problem is well .. a problem .. we were using a lot of custom reports with wildcards and custom variables, so this issue is affecting us a lot. Is there an estimate time when a fix for this will be commited into the code?

Thanks

[Piotr Banasik <piotr@…>]

This is still a prety major regression the way I see it .. but I found a new workaround ..

the example above uses $USER which you don't really have control over, but if you're experiencing this issue with manual custom variables, you can include the %'s on the url, eg: ?MILESTONE=%25foo%25 (url encoded % is %25), and then in the report just use WHERE milestone like '$MILESTONE' . This seems to be valid in 0.9.6, I vaguely remember that %'s in custom variables were a big nono in previous versions.

[trac-ja@…]

I think changes in changeset:2941/trunk/trac/ticket/report.py was overwritten in changeset:3300/trunk/trac/ticket/report.py.

[mgood]

Replying to trac-ja@i-act.co.jp:

I think changes in changeset:2941/trunk/trac/ticket/report.py was overwritten in changeset:3300/trunk/trac/ticket/report.py.

Argh, yeah it looks like the patch wasn't updated when those changes were merged in.

[mgood]

Ok, the behavior has been restored in r4030 (trunk) and r4031 (0.10-stable).

[ktvtch@…]

I seem to be having this problem as well, using version 0.10.4. The following SQL gives the error "Report execution failed: not enough arguments for format string":

SELECT

(upper(substr(t.type, 1, 1)) substr(t.type, 2, length(t.type) - 1)) AS group, p.value AS color, t.id, t.summary, t.component, t.owner, t.time, c.value AS working status, t.reporter FROM ticket t, ticket_custom c, enum p WHERE t.status IN ('new', 'assigned', 'reopened') AND t.id = c.ticket AND c.name = 'working_status' AND c.value = 'Approved' AND (t.owner = '$USER') AND p.name = t.priority AND p.type = 'priority' AND ((t.milestone NOT LIKE "Fixed%" AND t.milestone NOT LIKE "Release%") OR t.milestone IS NULL) ORDER BY t.type, p.value, t.component, t.id DESC

Adding a second percent sign after each one seems to work. Is having paired percent signs the official "way to do it" now, or is this bug still not fixed?