Brittle queries

[bewst]

I have some ticket_change entries with a blank newvalue field:

trac=# select author, oldvalue, newvalue from ticket_change where field = 'hours';
 author  | oldvalue | newvalue
---------+----------+----------
 gargol  |          | 12
 gargol  |          | 8
 gargol  |          | 2
 gargol  |          |
 gargol  |          |
 gargol  |          |
 gargol  |          |
 gargol  | 0        | 4
 gargol  | 0        | 7
 gargol  | 0        | 1
 gargol  | 0        | 4
 gargol  |          | 4
 gargol  | 0        | 3
 gargol  | 0        | 5
 gargol  | 0        | 2
 towerx  | 0        | 9
 pcaster | 0        | 16
(17 rows)

This is causing all the queries to break on CAST(newvalue as DECIMAL) (PostgreSQL):

ERROR:  invalid input syntax for type numeric: ""

The simplest workaround I could find was to use CAST(('0'||newvalue) as DECIMAL) instead. I'm hardly an SQL expert, though.

[bewst]

The following SQL updated my reports; I don't know how to update just the reports that appear in custom_reports (SQL n00b).

UPDATE report set query=regexp_replace(query,'CAST[(]([a-z_.]+) as DECIMAL[)]', 'CAST(''0'' || \\1 as DECIMAL)', 'gi') where query ~ 'CAST[(]([a-z_.]+) as DECIMAL[)]';

[bewst]

A patch that fixes the reports -- does NOT repair previously-generated reports!

[Russ Tyndall]

I appreciate the effort, though I do not think I can use the patch. The string concat operator you are using doesnt appear in mysql. I will try to get everything rewritten to use CASE statements to better guarantee reporting success across databases.

[Russ Tyndall]

btw, thanks for the nice bug report and patch!

[Russ Tyndall]

closes #1697 I was wrong about this, It was just a leftover quirk and has been removed

closes #901 reports now contain time remaining

closes #1958 added case statements all over the place to better support postegres when there are null/empty values

closes #1959 changed the work summary report to include links to the ticket and ticket summaries

Now at version 0.4.9