Using SQLite version 3.7.17 2013-05-20 00:56:22
If I have the table
CREATE Temporary TABLE "TestTable" (
"text1" TEXT,
"text2" TEXT,
"value" Integer,
PRIMARY KEY ("text1", "text2") );
with the following data
INSERT INTO TestTable VALUES('abc','d',0);
INSERT INTO TestTable VALUES('abc','e',0);
INSERT INTO TestTable VALUES('abc','f',0);
INSERT INTO TestTable VALUES('abc','g',1);
text1 text2 value
---------- ---------- ----------
abc d 0
abc e 0
abc f 0
abc g 1
and I create a view as follows
CREATE Temporary VIEW Test as
Select T.*, Int2
FROM TestTable T Left Join (
Select f.text1, f.text2, f.value as Int2
FROM TestTable f
) MS on T.text1 = MS.text1 and T.text2 = MS.text2 ;
text1 text2 value Int2
---------- ---------- ---------- ----------
abc d 0 0
abc e 0 0
abc f 0 0
abc g 1 1
if I do the following queries
Select * FROM Test
WHERE (Int2 = 1 OR Int2 is NULL)
ORDER BY text2;
I get
text1 text2 value Int2
---------- ---------- ---------- ----------
abc d 0
abc e 0
abc f 0
abc g 1 1
whereas doing nearly the same query but changing the ORDER BY clause I get
a different number of rows
Select * FROM Test
WHERE (Int2 = 1 OR Int2 is NULL)
ORDER BY text1;
text1 text2 value Int2
---------- ---------- ---------- ----------
abc g 1 1
In versions of sqlite3 before and including SQLite version 3.7.14.1
2012-10-04 19:37:12, these queries gave the same resulting rows is this a
bug?
I tested the follow versions of sqlite3
SQLite version 3.7.17 2013-05-20 00:56:22
SQLite version 3.7.16.2 2013-04-12 11:52:43
SQLite version 3.7.15.2 2013-01-09 11:53:05
SQLite version 3.7.14.1 2012-10-04 19:37:12
SQLite version 3.7.12 2012-04-03 19:43:07 (included with Mac OSX 10.8.3)
I using Mac OSX 10.8.3 with 2.3 GHz Intel Core i7 processes and 16 GB
1600 MHz DDR3 RAM
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
