Opened 9 years ago

Closed 9 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 9 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 9 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 9 years ago by bewst

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

comment:2 Changed 9 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 9 years ago by bobbysmith007

btw, thanks for the nice bug report and patch!

comment:4 Changed 9 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 owner will remain bobbysmith007.
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.