Edgewall Software
Modify

Opened 12 years ago

Closed 11 years ago

Last modified 8 years ago

#2773 closed defect (fixed)

Report fails because of complex SQL

Reported by: trac-ja@… Owned by: Matthew Good
Priority: normal Milestone: 0.10.1
Component: report system Version: 0.9.4
Severity: normal Keywords:
Cc: trac@…, vortechs2000@…
Release Notes:
API Changes:

Description

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.

Attachments (0)

Change History (17)

comment:1 Changed 12 years ago by 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

comment:2 Changed 12 years ago by 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.

comment:3 Changed 12 years ago by trac@…

Cc: trac@… added

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.

comment:4 Changed 12 years ago by Matthew Good

Owner: changed from daniel to Matthew Good
Status: newassigned

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

comment:5 Changed 12 years ago by Matthew Good

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

comment:6 Changed 12 years ago by 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

comment:7 Changed 12 years ago by Russell Hind <rhind@…>

Cc: rhind@… added

comment:8 Changed 11 years ago by anonymous

Cc: vortechs2000@… added

comment:9 Changed 11 years ago by anonymous

Can confirm this bug is still in 0.9.6

comment:10 Changed 11 years ago by 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

comment:11 Changed 11 years ago by 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.

comment:12 Changed 11 years ago by trac-ja@…

Milestone: 0.10.1

comment:13 in reply to:  12 Changed 11 years ago by Matthew Good

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.

comment:14 Changed 11 years ago by Matthew Good

Resolution: fixed
Status: assignedclosed

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

comment:15 Changed 10 years ago by 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?

comment:16 Changed 8 years ago by anonymous

Use single quotes instead of double quotes; it works then.

comment:17 Changed 8 years ago by rhind@…

Cc: rhind@… removed

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Matthew Good.
The resolution will be deleted.
to The owner will be changed from Matthew Good 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.