MySQL Bugs: #212: SELECT query containing a NATURAL JOIN and parentheses in the WHERE clause

Bug #212	SELECT query containing a NATURAL JOIN and parentheses in the WHERE clause
Submitted:	31 Mar 2003 7:59	Modified:	16 Dec 2003 1:50
Reporter:	Aad Mathijssen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	3.23.56, 4.0.12	OS:	Any (any)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
SELECT queries of the following form return the wrong result:
  SELECT * FROM Table1 NATURAL JOIN Table2 WHERE 1 AND (b AND c)

Here, b and c are arbitrary boolean expressions.
It seems that the boolean expression c is ignored.
When the parentheses are left out or the expressions 1 and (b AND c) are exchanged, expression c is evaluated.

Remark: in version 3.23.46 for Windows 2000 it worked fine.

How to repeat:
We create tables Test1 and Test2 on which we will perform a natural join of the desribed form.

CREATE TABLE Test1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
CREATE TABLE Test2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
INSERT INTO Test1 VALUES (1, 'A');
INSERT INTO Test2 VALUES (1, 'B');

Now the following query should have an empty result set:
SELECT * FROM Test1 NATURAL JOIN Test2 
WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B')

But it returns the result as if the expression Value2 <> 'B' is ignored.

Note that the following queries do give the right result:
SELECT * FROM Test1 NATURAL JOIN Test2 
WHERE 1 AND Value1 = 'A' AND Value2 <> 'B'

SELECT * FROM Test1 NATURAL JOIN Test2 
WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1

Confirmed on 4.0.12 as well.

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

I fixed it in 4.1 tree

I have now back ported the bug fix to MySQL 3.23 and it will also be in next MySQL 4.0 release