Report execution failed using Dynamic variables

[fermaf@…]

I have tried to do a custom query, which works propertly if I do not use dynamic variables. Like this:

select sum(cuenta.Reabiertos) as "Casos Re-Abiertos Totales",sum (cuenta.Total) as "Casos Totales",to_char(sum(cuenta.Reabiertos)*100/sum(cuenta.Total),'99.9') as "%"
from
(select count (distinct (tt.id)) as Total,0 as Reabiertos
from ticket tt
where
tt.reporter='soporteoperaciones'and 
to_date('01/02/2009', 'DD/MM/YYYY')<=to_timestamp(tt.time) and 
to_date('01/03/2009', 'DD/MM/YYYY')>= to_timestamp(tt.time) 
union all
select 0 as Total,count (distinct (tc.ticket)) as Reabiertos
from ticket_change tc
where
tc.ticket in (select  t.id as ID
from ticket_change tc2,ticket t
where
t.id=tc2.ticket and
t.reporter='soporteoperaciones'and 
to_date('01/02/2009', 'DD/MM/YYYY')<=to_timestamp(t.time) and 
to_date('01/03/2009', 'DD/MM/YYYY')>= to_timestamp(t.time) and
tc2.field='status'and 
tc2.oldvalue='closed'and 
tc2.newvalue='reopened') and 
tc.field='resolution' and
tc.newvalue='fixed' ) cuenta

But when I use Dynamic Variables in dates:
$FECHAINI

$FECHAFIN

for a report like: ​http://172.30.2.2/trac/report/62?FECHAINI=09/01/2009&FECHAFIN=13/02/2009

select sum(cuenta.Reabiertos) as "Casos Re-Abiertos Totales",sum (cuenta.Total) as "Casos Totales",to_char(sum(cuenta.Reabiertos)*100/sum(cuenta.Total),'99.9') as "%"
from
(select count (distinct (tt.id)) as Total,0 as Reabiertos
from ticket tt
where
tt.reporter='soporteoperaciones'and 
to_date('$FECHAINI', 'DD/MM/YYYY')<=to_timestamp(tt.time) and 
to_date('$FECHAFIN', 'DD/MM/YYYY')>= to_timestamp(tt.time) 
union all
select 0 as Total,count (distinct (tc.ticket)) as Reabiertos
from ticket_change tc
where
tc.ticket in (select  t.id as ID
from ticket_change tc2,ticket t
where
t.id=tc2.ticket and
t.reporter='soporteoperaciones'and 
to_date('$FECHAINI', 'DD/MM/YYYY')<=to_timestamp(t.time) and 
to_date('$FECHAFIN', 'DD/MM/YYYY')>= to_timestamp(t.time) and
tc2.field='status'and 
tc2.oldvalue='closed'and 
tc2.newvalue='reopened') and 
tc.field='resolution' and
tc.newvalue='fixed' ) cuenta

I've got an error

''Report execution failed: list index out of range''

[Christian Boos]

Which database backend are you using?

[fermaf@…]

The Database is PostgreSQL 8.1.11 running in redhat 4 32bits (the same server where is trac).

By the way, the custom queries I posted are generic I mean, they can run in any default trac implementation.

[Christian Boos]

Well, no, the time manipulation functions are unfortunately very DB specific. For example, your query won't work with SQLite (no such function: to_timestamp).

But with a PostgreSQL db, the problem can indeed be reproduced (at least on 0.11-stable). Did you try on trunk by any chance?

[fermaf@…]

No, I am not able to implement other (newer) version to check if the problem is fixed. Did you check (or are you able to check) if this problem is fixed in a newer version?

[Ryan J Ollos]

Moved ticket assigned to next-stable-1.0.x since maintenance of 1.0.x is coming to a close. Please move the ticket back if it's critical to fix on 1.0.x.

[Ryan J Ollos]

Milestone renamed