Opened 11 years ago

Closed 11 years ago

#1958 closed defect (fixed)

Brittle queries

Reported by: bewst Owned by: Russ Tyndall
Priority: normal Component: TimingAndEstimationPlugin
Severity: major Keywords:
Cc: Trac Release: 0.11


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.

Attachments (1)

reports.patch (9.5 KB) - added by bewst 11 years ago.
A patch that fixes the reports -- does NOT repair previously-generated reports!

Download all attachments as: .zip

Change History (5)

comment:1 Changed 11 years ago by 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[)]';

Changed 11 years ago by bewst

Attachment: reports.patch added

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

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

comment:3 Changed 11 years ago by Russ Tyndall

btw, thanks for the nice bug report and patch!

comment:4 Changed 11 years ago by Russ Tyndall

Resolution: fixed
Status: newclosed

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

Modify Ticket

Change Properties
Set your email in Preferences
as closed The owner will remain Russ Tyndall.
The resolution will be deleted.

Add Comment

E-mail address and name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.