http://www.sqlite.org/datatype3.html section 3+
When you declare a field as type "bool" it gets assigned NUMERIC type affinity.
"A column with NUMERIC affinity may contain values using all five storage
classes. When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if such
conversion is lossless and reversible. For conversions between TEXT and REAL
storage classes, SQLite considers the conversion to be lossless and reversible
if the first 15 significant decimal digits of the number are preserved. If the
lossless conversion of TEXT to INTEGER or REAL is not possible then the value
is stored using the TEXT storage class. No attempt is made to convert NULL or
BLOB values."
So with a field declared as "bool" then the text value of '1' gets inserted, it
is stored as the integer 1
1) SELECT * FROM TEST WHERE posted = 1;
[(‘inv’, 100, 1)]
2) SELECT * FROM TEST WHERE posted = ‘1’;
[(‘inv’, 100, 1)]
3) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = 1;
[(‘inv’, 100, 1)]
4) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = ‘1’;
[]
http://www.sqlite.org/datatype3.html section 4.2, 4.3, etc
In your case 2, "posted" refers directly to the NUMERIC field and thus
maintains NUMERIC affinity, so the text '1' gets converted losslessly to the
integer 1 and so the result of the comparison is true.
It seems that the CASE expression results in NO AFFINITY as an expression, and
does not carry the affinity of "posted" even though it is a direct reference to
the field. Therefore the first bullet point in section 4.3 does NOT get
applied, and the text '1' does NOT get converted to an integer for the
comparison.
I am a little confused though why bullet point 2 doesn't get applied then, and
the 1 from the case expression isn't treated as text.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
