| Bug #53161 | Left join optimized into join leads to incorrect results | ||
|---|---|---|---|
| Submitted: | 26 Apr 2010 13:12 | Modified: | 18 Jan 2013 1:51 |
| Reporter: | Adam Trotter | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.1.37, 5.1.46, 5.1.47 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | GROUP BY, join, left join, Optimizer | ||
[26 Apr 2010 13:30]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described:
77-52-4-109: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 33
Server version: 5.1.47-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> drop temporary table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create temporary table t1
-> (
-> id char(50) not null,
-> type tinyint(1) not null,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> drop temporary table if exists t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop temporary table if exists t3;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create temporary table t2 like t1;
Query OK, 0 rows affected (0.01 sec)
mysql> create temporary table t3 like t1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 set id='001', type=1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 set id='001:201', type=2;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 set id='001:201:301', type=3;
Query OK, 1 row affected (0.00 sec)
mysql> drop temporary table if exists t4;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create temporary table t4
-> (
-> id char(50) not null,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> select t2.id,
-> (select t3.id
-> from t3
-> left join t4 on t4.id=t3.id
-> where t3.type=3
-> and t3.id rlike concat(t2.id, ':[[:alnum:]]{3}$')
-> limit 1
-> ) as bug
-> from t1
-> join t2 on t2.type = 2
-> and t2.id rlike concat('^', t1.id, ':[[:alnum:]]{3}$')
-> where t1.id='001'
-> ;
+---------+-------------+
| id | bug |
+---------+-------------+
| 001:201 | 001:201:301 |
+---------+-------------+
1 row in set (0.04 sec)
mysql> select t2.id, -> (select t3.id -> from t3 -> left join t4 on t4.id=t3.id
-> where t3.type=3
-> and t3.id rlike concat(t2.id, ':[[:alnum:]]{3}$')
-> limit 1
-> ) as bug
-> from t1
-> join t2 on t2.type = 2
-> and t2.id rlike concat('^', t1.id, ':[[:alnum:]]{3}$')
-> where t1.id='001'
-> group by t2.id;
+---------+------+
| id | bug |
+---------+------+
| 001:201 | NULL |
+---------+------+
1 row in set (0.00 sec)
mysql> explain select t2.id, (select t3.id from t3 left join t4 on t4.id=t3.id where t3.type=3 and t3.id rlike concat(t2.id, ':[[:alnum:]]{3}$') limit 1 ) as bug from t1 join t2 on t2.type = 2 and t2.id rlike concat('^', t1.id, ':[[:alnum:]]{3}$') where t1.id='001' group by t2.id;
+----+--------------------+-------+--------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | PRIMARY | t1 | system | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+--------------------+-------+--------+---------------+------+---------+------+------+-----------------------------------------------------+
3 rows in set (0.03 sec)
mysql> explain select t2.id, (select t3.id from t3 left join t4 on t4.id=t3.id where t3.type=3 and t3.id rlike concat(t2.id, ':[[:alnum:]]{3}$') limit 1 ) as bug from t1 join t2 on t2.type = 2 and t2.id rlike concat('^', t1.id, ':[[:alnum:]]{3}$') where t1.id='001';
+----+--------------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+------+---------+------+------+---------------------+
| 1 | PRIMARY | t1 | system | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DEPENDENT SUBQUERY | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DEPENDENT SUBQUERY | t4 | system | PRIMARY | NULL | NULL | NULL | 0 | const row not found |
+----+--------------------+-------+--------+---------------+------+---------+------+------+---------------------+
4 rows in set (0.00 sec)
[26 Apr 2010 13:34]
Valeriy Kravchuk
Same results with 5.0.91 and 5.1.37, so this is not a recent regression.
[18 Jan 2013 1:51]
Paul DuBois
Noted in 5.1.68, 5.5.31 changelogs. Subqueries with OUTER JOIN could return incorrect results if the subquery referred to a column from another SELECT.
Description: In some cases, the query optimizer is incorrectly treating a left join as a join inside of a sub-query. Expected results without the group by: +---------+-------------+ | id | bug | +---------+-------------+ | 001:201 | 001:201:301 | +---------+-------------+ Actual results with the group by: +---------+------+ | id | bug | +---------+------+ | 001:201 | NULL | +---------+------+ Explain: +----+--------------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | 1 | PRIMARY | t1 | system | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | | | 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+--------------------+-------+--------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ The impossible where is incorrect! mysql> show warnings; +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1276 | Field or reference 'ERP_DATABASE.t2.id' of SELECT #2 was resolved in SELECT #1 | | Note | 1003 | select '001:201' AS `id`,(select '001:201:301' from `ERP_DATABASE`.`t3` join `ERP_DATABASE`.`t4` where 0 limit 1) AS `bug` from `ERP_DATABASE`.`t1` join `ERP_DATABASE`.`t2` where (('001:201' regexp concat('^','001',':[[:alnum:]]{3}$'))) group by '001:201' | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) Here you see the left join is being optimized into a join, incorrectly. If the temp tables are innodb, the optimized query is slightly different, yet still wrong: +----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+ | 1 | PRIMARY | t1 | const | PRIMARY | PRIMARY | 50 | const | 1 | 100.00 | Using index | | 1 | PRIMARY | t2 | index | NULL | PRIMARY | 50 | NULL | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t3 | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t4 | eq_ref | PRIMARY | PRIMARY | 50 | ERP_DATABASE.t3.id | 1 | 100.00 | Using where; Using index | +----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+ 4 rows in set, 2 warnings (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1276 Message: Field or reference 'ERP_DATABASE.t2.id' of SELECT #2 was resolved in SELECT #1 *************************** 2. row *************************** Level: Note Code: 1003 Message: select `ERP_DATABASE`.`t2`.`id` AS `id`,(select `ERP_DATABASE`.`t3`.`id` from `ERP_DATABASE`.`t3` join `ERP_DATABASE`.`t4` where ((`ERP_DATABASE`.`t4`.`id` = `ERP_DATABASE`.`t3`.`id`) and (`ERP_DATABASE`.`t3`.`type` = 3) and (`ERP_DATABASE`.`t3`.`id` regexp concat(`ERP_DATABASE`.`t2`.`id`,':[[:alnum:]]{3}$'))) limit 1) AS `bug` from `ERP_DATABASE`.`t1` join `ERP_DATABASE`.`t2` where ((`ERP_DATABASE`.`t2`.`type` = 2) and (`ERP_DATABASE`.`t2`.`id` regexp concat('^','001',':[[:alnum:]]{3}$'))) group by `ERP_DATABASE`.`t2`.`id` 2 rows in set (0.00 sec) How to repeat: drop temporary table if exists t1; create temporary table t1 ( id char(50) not null, type tinyint(1) not null, primary key(id) ); drop temporary table if exists t2; drop temporary table if exists t3; create temporary table t2 like t1; create temporary table t3 like t1; insert into t1 set id='001', type=1; insert into t2 set id='001:201', type=2; insert into t3 set id='001:201:301', type=3; drop temporary table if exists t4; create temporary table t4 ( id char(50) not null, primary key(id) ); select t2.id, (select t3.id from t3 left join t4 on t4.id=t3.id where t3.type=3 and t3.id rlike concat(t2.id, ':[[:alnum:]]{3}$') limit 1 ) as bug from t1 join t2 on t2.type = 2 and t2.id rlike concat('^', t1.id, ':[[:alnum:]]{3}$') where t1.id='001' group by t2.id;