Opened 7 years ago

Closed 7 years ago

#1958 closed defect (fixed)

Brittle queries

Reported by: bewst Owned by: bobbysmith007
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 7 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 7 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 7 years ago by bewst

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

comment:2 Changed 7 years ago by bobbysmith007

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 7 years ago by bobbysmith007

btw, thanks for the nice bug report and patch!

comment:4 Changed 7 years ago by bobbysmith007

  • Resolution set to fixed
  • Status changed from new to closed

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

Add Comment

Modify Ticket

as closed .
The resolution will be deleted. Next status will be 'reopened'.

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

Note: See TracTickets for help on using tickets.