Thanks to E.Pasma for finding a logic error in the new LEFT JOIN strength reduction optimization!
A new pre-release snapshot with this bug fixed has now been uploaded to the https://sqlite.org/download.html page. Please, everybody, continue testing! The LEFT JOIN strength reduction optimization changes a LEFT JOIN into an ordinary JOIN if any column of the right table of the LEFT JOIN is used in a way that requires the value to be non-NULL. The prover that checks this condition was mistakenly assuming that a CASE expression that contained a NULL value would always have a NULL answer. This is obviously wrong when you think about it for half a second, but I missed this case when coding up the prover. E.Pasma found a query that exercises that case, however. A simplified example: CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2),(3,4); CREATE TABLE t2(x); SELECT * FROM t1 LEFT JOIN t2 WHERE CASE WHEN FALSE THEN a=x ELSE 1 END; Needless to say, new test cases have been added to SQLite's test suites to make sure this particular mistake never happens again. But there are plenty of other potential mistakes out there, so do not slack up on your testing, please! On 3/24/18, E.Pasma <pasm...@concepts.nl> wrote: > Dear Richard, the rather complicated script below used to print a tree: > > node1|node2|node3|node4|node5 > 1|||| > 1|11||| > 1|12||| > 1|12|121|| > 1|13||| > > and with yesterday's pre-release snapshot it does not yield any result. > A clue is this depends on the size of the query, not so much on the > logic. > If you reduce the number of anticipated levels, by leaving out the > last four lines, the output is alright. > Hope this causes no headache, E. Pasma > > .version > SQLite 3.23.0 2018-03-22 12:00:43 > dd568c27b1d7656388ea5b4132cc0265aedd7348d265d8e8c7412b00b28a31aa > zlib version 1.2.3 > gcc-4.0.1 (Apple Inc. build 5465) > > create table node (node integer primary key, parent integer) > ; > insert into node values (1,0),(11,1),(12,1),(13,1),(121,12) > ; > create index node_parent on node (parent) > ; > create table bit (bit integer primary key) > ; > insert into bit values(0),(1) > ; > SELECT node1, node2, node3, node4, node5 > /* level 1 is root */ > FROM (SELECT node AS node1 FROM node WHERE parent=0) > /* level 2 */ > JOIN (SELECT bit AS bit1 FROM bit) > LEFT JOIN (SELECT node AS node2, parent AS parent2 FROM node) > ON bit1 AND parent2=node1 > /* level 3 */ > JOIN (SELECT bit AS bit2 FROM bit) > ON bit2<=CASE WHEN bit1 THEN CASE WHEN node2 THEN 1 ELSE -1 END > ELSE 0 END > LEFT JOIN (SELECT node AS node3, parent AS parent3 FROM node) > ON bit2 AND parent3=node2 > /* level 4 */ > JOIN (SELECT bit AS bit3 FROM bit) > ON bit3<=CASE WHEN bit2 THEN CASE WHEN node3 THEN 1 ELSE -1 END > ELSE 0 END > LEFT JOIN (SELECT node AS node4, parent AS parent4 FROM node) > ON bit3 AND parent4=node3 > /* level 5 */ > JOIN (SELECT bit AS bit4 FROM bit) > ON bit4<=CASE WHEN bit3 THEN CASE WHEN node4 THEN 1 ELSE -1 END > ELSE 0 END > LEFT JOIN (SELECT node AS node5, parent AS parent5 FROM node) > ON bit4 AND parent5=node4 > ; > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
