MySQL Bugs: #54609: Select returning incorrect result

Bug #54609	Select returning incorrect result
Submitted:	18 Jun 2010 9:38	Modified:	18 Jun 2010 19:10
Reporter:	Richard Kojedzinszky	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.1.47-1~bpo50+1-log, 6.0.14	OS:	Linux (debian lenny)
Assigned to:		CPU Architecture:	Any

Description:
When creating a left join result set, applying a where clause to that filters rows which are in the set.

How to repeat:
mysql> create table asd(id int auto_increment primary key, v1 int not
null, v2 int not null) engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into asd(v1, v2) values (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2
= 2 and b.v2 = 1) ;
+----+----+----+------+------+------+
| id | v1 | v2 | id   | v1   | v2   |
+----+----+----+------+------+------+
|  1 |  1 |  1 | NULL | NULL | NULL |
+----+----+----+------+------+------+
1 row in set (0.00 sec)

mysql> select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2
= 2 and b.v2 = 1) where a.id = 1;
+----+----+----+------+------+------+
| id | v1 | v2 | id   | v1   | v2   |
+----+----+----+------+------+------+
|  1 |  1 |  1 | NULL | NULL | NULL |
+----+----+----+------+------+------+
1 row in set (0.00 sec)

mysql> alter table asd add key (v1);
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2
= 2 and b.v2 = 1) where a.id = 1;
Empty set (0.00 sec)

Test case to copy-paste easily:

create table asd(id int auto_increment primary key, v1 int not null, v2 int not null) engine=innodb;
insert into asd(v1, v2) values (1, 1);
select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) ;
select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
EXPLAIN select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
alter table asd add key (v1);
select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
EXPLAIN select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;

Repeatable with 6.0.14. Not repeatable with current 5.1.49 from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table asd(id int auto_increment primary key, v1 int not null, v2 int not null) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into asd(v1, v2) values (1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) ;
+----+----+----+------+------+------+
| id | v1 | v2 | id   | v1   | v2   |
+----+----+----+------+------+------+
|  1 |  1 |  1 | NULL | NULL | NULL |
+----+----+----+------+------+------+
1 row in set (0.00 sec)

mysql> select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
+----+----+----+------+------+------+
| id | v1 | v2 | id   | v1   | v2   |
+----+----+----+------+------+------+
|  1 |  1 |  1 | NULL | NULL | NULL |
+----+----+----+------+------+------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> alter table asd add key (v1);
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
+----+----+----+------+------+------+
| id | v1 | v2 | id   | v1   | v2   |
+----+----+----+------+------+------+
|  1 |  1 |  1 | NULL | NULL | NULL |
+----+----+----+------+------+------+
1 row in set (0.02 sec)

mysql> EXPLAIN select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                   |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 |                         |
|  1 | SIMPLE      | b     | const | v1            | NULL    | NULL    | NULL  |    1 | Impossible ON condition |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------------------+
2 rows in set (0.01 sec)

Not repeatable for me with 5.1.48 officially released yesterday:

valeriy-kravchuks-macbook-pro:mysql-5.1.48-osx10.5-x86_64 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table asd(id int auto_increment primary key, v1 int not null, v2 int not null) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into asd(v1, v2) values (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) ;
+----+----+----+------+------+------+
| id | v1 | v2 | id   | v1   | v2   |
+----+----+----+------+------+------+
|  1 |  1 |  1 | NULL | NULL | NULL |
+----+----+----+------+------+------+
1 row in set (0.00 sec)

mysql> select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
+----+----+----+------+------+------+
| id | v1 | v2 | id   | v1   | v2   |
+----+----+----+------+------+------+
|  1 |  1 |  1 | NULL | NULL | NULL |
+----+----+----+------+------+------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> alter table asd add key (v1);
Query OK, 1 row affected (0.18 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
+----+----+----+------+------+------+
| id | v1 | v2 | id   | v1   | v2   |
+----+----+----+------+------+------+
|  1 |  1 |  1 | NULL | NULL | NULL |
+----+----+----+------+------+------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from asd as a left join asd as b on (a.v1 = b.v1 and a.v2 = 2 and b.v2 = 1) where a.id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                   |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 |                         |
|  1 | SIMPLE      | b     | const | v1            | NULL    | NULL    | NULL  |    1 | Impossible ON condition |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------------------+
2 rows in set (0.00 sec)

Please, check with 5.1.48 in your environment and inform about the results.

Based on test case and versions affected it likely a duplicate of Bug #53334.

With 5.1.48 it seems to be fixed.