Help save net neutrality! Learn more.
Close

#1299 LEFT JOIN (SELECT...) <-> LEFT JOIN .... non-equivalence

current-release
closed-fixed
None
1
2013-12-30
2013-04-25
Anonymous
No

Running the following script:

CREATE TABLE A(id INTEGER);
INSERT INTO A VALUES (1), (2), (3), (4);
CREATE TABLE B(id INTEGER);
INSERT INTO B VALUES (2), (4);

-- Statement 1
SELECT *
FROM A
LEFT JOIN B
ON b.id = a.id
WHERE b.id IS NULL;

-- Statement 2
SELECT *
FROM A
LEFT JOIN (SELECT *
FROM B) B
ON b.id = a.id
WHERE b.id IS NULL;

Statement 1 returns results:
A.id, B.id
2, 2
4, 4

Statement 2 returns results:
A.id, B.id
1, NULL
2, NULL
3, NULL
4, NULL

This is extremely non-intuitive, to say the least.

Discussion

  • Fred Toussi

    Fred Toussi - 2013-04-25

    Thanks for reporting. The second query returns incorrect result in version 2.2.9 due to a regression in 2.2.9 which has been fixed.

    You actually reported the result for WHERE B.ID IS NOT NULL for the first query.

    Both queries return the correct (same) result when tested with the latest version 2.3.0 or version 2.2.8.

    1,NULL
    3,NULL

     
  • Fred Toussi

    Fred Toussi - 2013-04-25
    • assigned_to: nobody --> fredt
    • priority: 5 --> 1
    • status: open --> open-fixed
     
  • Fred Toussi

    Fred Toussi - 2013-12-30
    • status: open-fixed --> closed-fixed
     

Log in to post a comment.