MySQL Bugs: #566: Possible optimization bug with LEFT JOIN

Bug #566	Possible optimization bug with LEFT JOIN
Submitted:	2 Jun 2003 4:21	Modified:	13 Jun 2003 9:06
Reporter:	Timo Salmi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.13-Max	OS:	Linux (Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
There is a possible optimization bug with joining tables.

I have table A with non-unique ID (most of them have ID=0) and table B with associated name to that ID (non unique, too).

Table B has index to column ID.

How to repeat:
SELECT A.name,A.id,B.name FROM A LEFT JOIN B ON A.id>0 AND A.id=B.id;
1314 rows in set (10.68 sec)

SELECT A.name,A.id,B.name FROM A LEFT JOIN B ON IF(A.id>0,A.id,-1)=B.id;
1314 rows in set (0.04 sec)

Dear  Timo,

Unfortunately information you have provided is not enough to repeat the problem.
You do not even reference table type for the table you're using.

Could you please try to create small repeatable test case which can be simply tested as mysql test <  bug.sql  ?

I you fail to create it with reasonably small table, please upload table to
ftp://support.mysql.com/pub/mysql/secret and let us know the file name.

I'm using InnoDB.

Here is a sample database. I'm using bash to add data.

mysql> create table A (id int not null,name char(1) not null,uid int not null,primary key (id),index uid_index (uid));

mysql> create table B (id int not null,name char(1) not null,uid int not null,primary key (id),index uid_index (uid));

bash# for (( i=1; i<=1000; i++ )) do echo "INSERT INTO test.A SET id=$i,uid=0,name=' '" | mysql test; done;

bash# for (( i=1; i<=26; i++ )) do echo "INSERT INTO test.A SET id=1000+$i,uid=$i,name=CHAR($i+64)" | mysql test; done;

mysql> insert into B select * from A;

mysql> select A.name,B.name from A left join B on A.uid=B.uid AND A.uid>0;
...
1026 rows in set (11.24 sec)

mysql> select A.name,B.name from A left join B on IF(A.uid>0,A.uid,-1)=B.uid;
...
1026 rows in set (0.02 sec)

Output consists 1000 rows with A.name = '' and B.name = NULL followed by both columns A-Z.

Seems to be an optimizer bug and hence not dependent on the table type - happens with MyISAM 
as well.

It's not a bug: currently the optimizer does not split ON expressions by 
extracting conjucts that can be evaluated at some steps of the execution as it 
does for WHERE conditions. So now the ON expressions is evaluated only when  
all columns of the corresponding outer join can be accessed. 
The split procedure for ON expression will be added to our TODO list.