Bug #4893 Right join after inner join produces wrong results
Submitted: 4 Aug 2004 11:12 Modified: 9 Aug 2004 21:34
Reporter: Alan Tam Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.20 OS:Linux (Debian Linux)
Assigned to: Dean Ellis

[4 Aug 2004 11:12] Alan Tam
Description:
See how to repeat.
The expected result mentioned is confirmed using PostgreSQL.

Same for SQL using "USING" and "ON".
Same for fields being NULL and NOT NULL.
Same for fields being indexed and otherwise.
Same for 4.0.20 and 4.1.3.

How to repeat:
CREATE TABLE A (A int);
CREATE TABLE B (B int);
CREATE TABLE C (A int, B int);

INSERT INTO A VALUES (1), (2);
INSERT INTO B VALUES (1), (2);
INSERT INTO C VALUES (1, 1);

SELECT B.B
  FROM A
 INNER JOIN C USING (A)
 RIGHT JOIN B USING (B)
 WHERE C.B IS NULL;

Expected Result:
B
-
2

Actual Result:
B
-
1
2
2
[9 Aug 2004 21:34] Dean Ellis
This required nested join support, which was implemented in 5.0.1, and is too large a change for backporting.  FYI, the query does produce the expected results against the current 5.0.2 development branch.
[11 Aug 2004 10:18] Alan Tam
Thanks for your quick fix. I opine that if it is a feature not yet supported in earlier releases, it should give a warning/error instead of wrong result, explaining why it is not supported. (Personally I have no idea why it needs 5.0.x to have it work - I am only trying to rewrite a subquery to target for 4.0.x.)