MySQL Bugs: #16908: LEFT JOIN with a VIEW returns Unexpected Results

Bug #16908	LEFT JOIN with a VIEW returns Unexpected Results
Submitted:	30 Jan 2006 17:36	Modified:	2 Feb 2006 2:31
Reporter:	Darwin Fan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18-standard	OS:	Linux (Redhat Linux Release 9)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
I perform a LEFT JOIN with a TABLE and a VIEW.  It appears that the results of the query include rows from the VIEW that do not satisfy the WHERE clause in the definition of the VIEW.

How to repeat:
mysql> create table aaaa ( ID int, Field_Val int, primary key (ID) );
mysql> create table bbbb ( ID int, Field_Val int, BeenDeleted enum ('Y','N'), primary key (ID) );

mysql> insert into aaaa (ID,Field_Val) VALUES (1,10);
mysql> insert into bbbb (ID,Field_Val,BeenDeleted) VALUES (1,20,'Y');

mysql> create view bbbb_v as select ID, Field_Val from bbbb where bbbb.BeenDeleted != 'Y';

mysql> select * from bbbb_v;
Empty set (0.00 sec)

mysql> select t1.Field_Val, t2.Field_Val FROM aaaa t1 LEFT JOIN bbbb t2 ON ( t1.ID = t2.ID );
+-----------+-----------+
| Field_Val | Field_Val |
+-----------+-----------+
|        10 |        20 |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> select t1.Field_Val, t2.Field_Val FROM aaaa t1 LEFT JOIN bbbb t2 ON ( t1.ID = t2.ID AND t2.BeenDeleted != 'Y');
+-----------+-----------+
| Field_Val | Field_Val |
+-----------+-----------+
|        10 |      NULL |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> select t1.Field_Val, t2.Field_Val FROM aaaa t1 LEFT JOIN bbbb_v t2 ON ( t1.ID = t2.ID );
+-----------+-----------+
| Field_Val | Field_Val |
+-----------+-----------+
|        10 |        20 |
+-----------+-----------+
1 row in set (0.00 sec)

/*** I expected that the last two queries should return the same results 
/*** Both should return (10, NULL)

With the current tree:
mysql> select version() ;
+--------------+
| version()    |
+--------------+
| 5.0.19-debug |
+--------------+
1 row in set (0.00 sec)

I had correct result sets:

mysql> select t1.Field_Val, t2.Field_Val FROM aaaa t1 LEFT JOIN bbbb t2 ON (
    -> t1.ID = t2.ID AND t2.BeenDeleted != 'Y');
+-----------+-----------+
| Field_Val | Field_Val |
+-----------+-----------+
|        10 |      NULL |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> select t1.Field_Val, t2.Field_Val FROM aaaa t1 LEFT JOIN bbbb_v t2 ON (
    -> t1.ID = t2.ID );
+-----------+-----------+
| Field_Val | Field_Val |
+-----------+-----------+
|        10 |      NULL |
+-----------+-----------+
1 row in set (0.00 sec)