Fascinating. From the same source table see also: sqlite> select tab2.id is not null as c from tab left join tab as tab2 on 0 where c = 0; QUERY PLAN |--SCAN TABLE tab AS tab2 (~983040 rows) `--SCAN TABLE tab (~1048576 rows)
sqlite> select tab2.id is not null as c from tab left join tab as tab2 on 0 where c is 0; QUERY PLAN |--SCAN TABLE tab (~1048576 rows) `--SCAN TABLE tab AS tab2 (~917504 rows) 0 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Danny >Sent: Monday, 4 February, 2019 23:24 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Bug due to left join strength reduction >optimization? > >The result of the query described below changed (became incorrect, I >believe) with the addition of the left join strength reduction >optimization in revision dd568, and remains that way in trunk (2c876, >at >the time of writing). > >Consider the following statements: > >``` >CREATE TABLE tab (id INT); >INSERT INTO tab VALUES (1); >SELECT tab2.id IS NOT NULL AS c > FROM tab LEFT JOIN tab AS tab2 ON 0 > WHERE c = 0; >``` > >As of revision a8dfe (parent of dd568), the SELECT outputs one row >with >one column containing 0, as I would expect. At dd568 (and at trunk), >however, it outputs no rows. > >This looks similar in spirit to an existing, fixed bug [1], but the >output for the test case there has gone back to its pre-LJSRO value >at >some point since dd568, while this one has not. > >Thanks, >Danny > >[1] https://www.sqlite.org/src/tktview/1e39b966ae9ee7394334 >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
