Opened 9 years ago

Closed 9 years ago

Brittle queries

Reported by: Owned by: bewst bobbysmith007 normal TimingAndEstimationPlugin major 0.11

Description

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.

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