Edgewall Software
Modify

Opened 19 years ago

Closed 18 years ago

Last modified 15 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@… Branch:
Release Notes:
API Changes:
Internal 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 by anonymous, 19 years ago

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 by trac@…, 19 years ago

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 by trac@…, 19 years ago

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 by Matthew Good, 19 years ago

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 by Matthew Good, 19 years ago

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

comment:6 by ritter@…, 19 years ago

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 by Russell Hind <rhind@…>, 19 years ago

Cc: rhind@… added

comment:8 by anonymous, 18 years ago

Cc: vortechs2000@… added

comment:9 by anonymous, 18 years ago

Can confirm this bug is still in 0.9.6

comment:10 by Piotr Banasik <piotr@…>, 18 years ago

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 by Piotr Banasik <piotr@…>, 18 years ago

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 by trac-ja@…, 18 years ago

Milestone: 0.10.1

in reply to:  12 comment:13 by Matthew Good, 18 years ago

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 by Matthew Good, 18 years ago

Resolution: fixed
Status: assignedclosed

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

comment:15 by ktvtch@…, 17 years ago

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 by anonymous, 15 years ago

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

comment:17 by rhind@…, 15 years ago

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. Next status will be 'reopened'.
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.