An Unexpected NULL Column Caused by Where Clause in RIGHT JOIN
(1) By Jinsheng Ba (bajinsheng) on 2022-05-13 14:15:29 [source]
In the RIGHT OUTER JOIN, a specific WHERE clause causes a column to be NULL unexpectedly.
CREATE TABLE t0 (c0 INT);
CREATE TABLE t1 (c0 INT);
insert into t0 values(2);
insert into t1 values(NULL);
SELECT t0.c0, t0.c0 IS NULL FROM t0 RIGHT OUTER JOIN t1;
-- 2|0
SELECT t0.c0, t0.c0 IS NULL FROM t0 RIGHT OUTER JOIN t1 WHERE t0.c0 IS NULL;
-- |1
The first SELECT indicates that the only value in t0.c0 is 2 (not NULL). The value in t0.c0 is returned as NULL when we add a particular WHERE clause, as illustrated in the second SELECT.
We think a WHERE clause should not affect the values of the data that is fetched.
Environment:
Version: 3.39.0
Commit ID: c7e3a13a
OS: Ubuntu 20.04
Configuration Options: ./configure --enable-all
Compiler: gcc-9
Client: CLI
(2) By Keith Medcalf (kmedcalf) on 2022-05-13 17:04:10 in reply to 1 [link] [source]
The result is indeed incorrect. The second statement should return no rows (there is no row where t0.c0 is null).
If you change "t0 right join t1" to "t1 left join t0" you will get the corrrect results.
(3) By Richard Hipp (drh) on 2022-05-13 17:19:16 in reply to 2 [link] [source]
Check-in 02b24863e6dc617c adds a bunch of new test cases, one of which (joinE-32) demonstrates the problem. I'm testing a patch now.
The problem only comes up for RIGHT JOIN, and is thus unreleased.
(4) By Richard Hipp (drh) on 2022-05-13 17:47:01 in reply to 3 [link] [source]
I believe the issue has now been resolved by check-in 238d9c247cf69cc7.
(5) By Richard Hipp (drh) on 2022-05-13 18:23:55 in reply to 4 [link] [source]
Heads-up: check-in 238d9c247cf69cc7 fixes the RIGHT JOIN problem described on this thread, but at the same time it introduces some new problems. So additional patches are pending.
(6) By Richard Hipp (drh) on 2022-05-13 21:04:44 in reply to 5 [link] [source]
Following-up issues have been resolved. There are no known problems with the latest trunk check-in (0f96810b840dd6f2).
Therefore, intrepid hackers, go forth and try to break it. Report your successes by starting a new thread on this forum.
(7) By Jinsheng Ba (bajinsheng) on 2022-05-14 01:14:28 in reply to 6 [link] [source]
Thanks! Yes, the bug is fixed now.