We have an input table named tab with a single row whose sole column named id as a value of 1. This table is joined to itself in a left join. The cartesian product would be
tab.id tab2.id 1 1 The clause ON 0 evaluates to FALSE, so the row is eliminated from the result set. LEFT JOIN processing requires that the result set be extended by a special row for each lhs row, giving tab.id tab2.id 1 NULL From this the result expression evaluates to (NULL IS NOT NULL) which gives the number 0. Then the WHERE clause is evaluated to (0 = 0) which gives TRUE. This yields a single result row with a value 0 Changing the order of the tables gives tab2.id tab.id 1 1 Which devolves into tab2.id tab.id 1 NULL Yielding c (1 IS NOT NULL) which gives the number 1 Which means the row is excluded by the WHERE clause No output Testing of R.Smiths changed query reveals that the difference is caused by different handling of the equality and the IS operator. asql> explain query plan select 1 from tab left join tab as tab2 on 0 where (tab2.id IS NOT NULL) = 0; id parent notu deta ---- ------------- ---- ---- 4 0 0 SCAN TABLE tab AS tab2 11 0 0 SCAN TABLE tab asql> explain query plan select 1 from tab left join tab as tab2 on 0 where (tab2.id IS NOT NULL) is 0; id parent notu deta ---- ------------- ---- ---- 3 0 0 SCAN TABLE tab 5 0 0 SCAN TABLE tab AS tab2 -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Dominique Devienne Gesendet: Dienstag, 05. Februar 2019 09:14 An: SQLite mailing list <[email protected]> Betreff: [EXTERNAL] Re: [sqlite] Bug due to left join strength reduction optimization? On Tue, Feb 5, 2019 at 7:47 AM Keith Medcalf <[email protected]> wrote: > sqlite> select ... from tab left join tab as tab2 on 0 ... > Can someone please educate me on this {{ on 0 }} join "condition" ? I'm not following what the intended meaning is... Must have one, since OP "expects 1 row with one column containing 0", despite a table with a single 1 row. Thanks, --DD _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
