On 2019/02/05 10:13 AM, Dominique Devienne wrote:
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.
"on 0" is similar to "on FALSE" which simply means "don't join
anything... ever" but since it is a left-join, it will still produce any
rows forthcoming from the initial FROM table, /IF/ it clears the WHERE
clause, which in this case it should because the WHERE condition is one
that should always evaluate to TRUE.
If no rows are left-joined, the joined row-values can never have any
other value than NULL, so the "tab2.id IS NOT NULL" must by definition
always be FALSE (aka "0") - Which in turn means the WHERE condition of
"c = 0" must always evaluate to TRUE, which means the 1 row from the
base table MUST be output.
@OP:
As a matter of interest - what happens when the aliasing is taken out of
the loop and the query changes to:
CREATE TABLE tab (id INT);
INSERT INTO tab VALUES (1);
SELECT 1
FROM tab LEFT JOIN tab AS tab2 ON 0
WHERE (tab2.id IS NOT NULL) = 0
;
I don't have that broken version currently, so can't test on my side, but I'm
assuming your example is minimal and it works if anything is changed, which
means it's likely the fault of the logic that checks the aliased value (unless
the above query still fails, in which case my assumption is wrong and the above
is a better test case).
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
