#2773 closed defect (fixed)
Report fails because of complex SQL
Reported by: | 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 , 19 years ago
comment:2 by , 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 , 19 years ago
Cc: | 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 , 19 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
I've added a note on the TracUpgrade page about it, but I'm working on a fix/workaround for this.
comment:5 by , 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 , 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 , 19 years ago
Cc: | added |
---|
comment:8 by , 18 years ago
Cc: | added |
---|
comment:10 by , 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 , 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.
follow-up: 13 comment:12 by , 18 years ago
Milestone: | → 0.10.1 |
---|
I think changes in changeset:2941/trunk/trac/ticket/report.py was overwritten in changeset:3300/trunk/trac/ticket/report.py.
comment:13 by , 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 , 18 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
comment:15 by , 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
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:17 by , 15 years ago
Cc: | removed |
---|
Sorry, SQL on description uses custom fields.
It occurs this SQL:
Error message is: