Hello,
First of all, I'm not sure is this a good place to report a SQLite bug,
however let me try.
SQLite version: 3.7.15.2
How to reproduce error:
1. Create in-memory (or disk, whatever) database
2. Execute following statements to prepare schema:
CREATE TABLE data (id INTEGER PRIMARY KEY AUTOINCREMENT, a INTEGER NOT
NULL, b INTEGER NOT NULL)
INSERT INTO data(a,b) VALUES(1,1)
INSERT INTO data(a,b) VALUES(2,2)
INSERT INTO data(a,b) VALUES(3,3)
INSERT INTO data(a,b) VALUES(4,4)
CREATE VIEW dataView AS
SELECT d.a AS a,
(CASE WHEN d.a%2 = 1 THEN d.a ELSE d.b END) AS b,
CAST((CASE WHEN d.a%2 = 1 THEN d.a ELSE d.b END) AS INTEGER) AS c
FROM data d
For now, you have database set up.
3. Let us execute the following query:
SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM dataView
output:
1,integer,1,integer,1,integer
2,integer,2,integer,2,integer
3,integer,3,integer,3,integer
4,integer,4,integer,4,integer
As far, as we see all columns have type affinity "integer". Values of
each column in corresponding rows are the same.
On the other hand, take a look at a type seen by SQLite for strings:
SELECT '1', typeof('1')
output:
1,text
So, type of '1' is 'text'.
4. Before we continue, let us cite the following sentence from SQLite
documentation (Sec. 3.3. from Datatypes In SQLite Version 3):
"Affinity is applied to operands of a comparison operator prior to the
comparison according to the following rules in the order shown:
* If one operand has INTEGER, REAL or NUMERIC affinity and the other
operand as TEXT or NONE affinity then NUMERIC affinity is applied to
other operand. "
So, if we compare 1 with '1' (e.g. 1='1'), '1' should be converted to
numeric, right? It's not particularly true. See the following point.
5. Let us execute a query from point 3 with additional constraint in
WHERE clause (note the quotes around '1'):
SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM dataView WHERE a='1'
output:
1,integer,1,integer,1,integer
The output is not surprising, SQLite returned the only row that has 1 in
'a' column. Ok, let us do the same for 'b' column:
SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM dataView WHERE b='1'
output:
0 rows
Strange, right? As we have seen there is a row for which b is 1, however
SQLite did not returned that. What happen then? Note that 'b' is not a
column in table 'data', its a column in view 'dataView' defined as a
result of CASE statement. I hypothesize that the result of CASE
statement, regardless type reported by typeof(b) function, is not
internally seen as integer/numeric, so the conversion of the other
argument (i.e. '1' to 1) is not applied. To confirm this hypothesis we
created additional column 'c' in the view. C's definition is the same as
b's, except that we explicitly cast value returned by CASE statement to
integer. The query now works as expected.
SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM dataView WHERE c='1'
output:
1,integer,1,integer,1,integer
In conclusion, I think that there is an error in SQLite, that computes
wrong type affinity for values returned by CASE statement.
I am looking forward for explanation of SQLite behavior or fixing of the
bug described above.
With best regards,
Tomasz Pawlak
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
