IFNULL or COALESCE functions convert table data badly

[jim@…]

When reporting on ticket_custom fields, some are unpopulated (i.e. NULL) because the custom fields were added after the database was populated. This means that my report returns NULLs for some rows …

A short example looking for the custom field "charge", results as (trimmed) CSV :-

select t.id,c.value from ticket t LEFT OUTER JOIN ticket_custom c ON (t.id=c.ticket AND c.name='charge')

33,None
34,None
35,36m
36,

This produces a report with some of the c.value's returned as the string "None". I don't like that, and would prefer to have no data visible where the source is a NULL, so I tried to use IFNULL and/or COALESCE to replace the NULLs with an empty string …

select t.id,coalesce(c.value,'fred') from ticket t LEFT OUTER JOIN ticket_custom c ON (t.id=c.ticket AND c.name='charge')

33,0.0
34,0.0
35,36.0
36,0.0

Unfortunately, this results in what looks like all the fields being run through atof() or similar - the NULL or "empty" fields are rendered as "0.0" and the populated fields are also converted to numeric (where the original was a text field)

I can work around it with a CASE statement (using 'fred' to make the actual ' ' visible) …

  (CASE WHEN c.value ISNULL THEN 'fred' ELSE c.value END)

33,fred
34,fred
35,36m
36,

however, I feel that IFNULL and COALESCE shouldn't behave like this in the first place. They behave fine when invoked from the SQLite command line monitor, so I think it must be something within the Trac report module that's causing this.

sqlite> select t.id,coalesce(c.value,'fred') from ticket t LEFT OUTER JOIN ticket_custom c ON (t.id=c.ticket AND c.name='charge');
...
33|fred
34|fred
35|36m
36|

[jim@…]

Moved to milestone 0.8.1

[Christopher Lenz]

Later.

[Matthew Good]

This is an upstream problem in SQLite 2 due to it always reporting the column type as "numeric" for the result of coalesce. So, when Pysqlite converts the query results into Python types it believes that the column should be a number. SQLite 3 seems to fix this, so upgrading should resolve this problem.

$ sqlite :memory: "select typeof(coalesce('a', 'b'));"
numeric
$ sqlite3 :memory: "select typeof(coalesce('a', 'b'));"
text