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
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.20
• OS: Linux (Debian Linux)
• Assigned to: Dean Ellis
• CPU Architecture: Any

[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.)