Ticket #1958 (closed defect: fixed)

Opened 6 years ago

Last modified 6 years ago

Brittle queries

Reported by: bewst Assigned to: bobbysmith007
Priority: normal Component: TimingAndEstimationPlugin
Severity: major Keywords:
Cc: Trac Release: 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.

Attachments

reports.patch (9.5 kB) - added by bewst on 08/21/07 21:11:36.
A patch that fixes the reports -- does NOT repair previously-generated reports!

Change History

08/21/07 21:05:34 changed 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[)]';

08/21/07 21:11:36 changed by bewst

  • attachment reports.patch added.

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

08/21/07 21:43:13 changed 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.

08/21/07 21:43:50 changed by bobbysmith007

btw, thanks for the nice bug report and patch!

08/22/07 19:38:06 changed by bobbysmith007

  • status changed from new to closed.
  • resolution set to fixed.

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/Change #1958 (Brittle queries)




Change Properties
Action