Hi, I noticed that some of my views were not working with sqlite 3.8.1. I managed to isolate the problem in the simple test case below.
Then I discovered that the 3.8.2 pre-release seems to do it right. I suspect it was fixed along with https://www.sqlite.org/src/tktview?name=c620261b5b but I don't know for sure if this boils down to the same problem. In this case, the problem seems to be triggered by the combination of a LEFT JOIN to an empty set together with a JOIN on the primary key column of a table, and can be strangely avoided with a CAST. CREATE TABLE T (i INTEGER PRIMARY KEY); INSERT INTO T (i) VALUES (1968); --This join fails to return any rows in sqlite 3.8.1. --Succeeds in 3.7.17. --Succeeds in 3.8.2 2013-11-27 pre-release SELECT A.i, typeof(T.i) t_type, typeof(A.i) a_type FROM (SELECT 1968 i) A LEFT JOIN (SELECT 1968 i WHERE 0<>0) B ON B.i=A.i JOIN T ON T.i = A.i ; --But a CAST() makes the join succeed, even though both types are already integer. SELECT A.i, typeof(T.i) t_type, typeof(A.i) a_type FROM (SELECT 1968 i) A LEFT JOIN (SELECT 1968 i WHERE 0<>0) B ON B.i=A.i JOIN T ON CAST(T.i AS INTEGER)=A.i ; _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
