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

current-release
closed-fixed
Fred Toussi
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