MySQL Bugs: #85117: Left join with subquery error

Bug #85117	Left join with subquery error
Submitted:	22 Feb 2017 1:28	Modified:	28 Jun 2021 7:32
Reporter:	Ngô Nguyên	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.7/8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Yesterday, I upgrade MySQL from 5.2 to 5.7 and my system had the strange phenomenon.

How to repeat:
I have an query like this

-- Init

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `table1` (`id`, `value`) VALUES 
  (1, 100),
  (2, 200),
  (3, 300);
CREATE TABLE `table1_use` (
  `id` int(11) NOT NULL,
  `table1id` int(11) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `table1_use` (`id`, `table1id`, `uid`) VALUES 
  (1, 1, 99);

-- my query (with out error in MySQL 5.2) 
select
      temp.sel
from
    table1 t1
    left join (
         select *,1 as sel from table1_use t1u where t1u.`table1id`=1
    ) temp on temp.table1id = t1.id
order by t1.value

Result must be 1,null,null but I got null,null,null

When I remove order by clause, result is 1,null,null but it can't order

Thank you for the bug report. Not repeatable on 5.6 and repeatable with 5.7/8.0:

Your MySQL connection id is 1
Server version: 5.6.36-debug Source distribution PULL: 2017-FEB-01

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.6 > use test
Database changed
mysql 5.6 >
mysql 5.6 > CREATE TABLE `table1` (
    ->   `id` int(11) NOT NULL,
    ->   `value` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.48 sec)

mysql 5.6 > INSERT INTO `table1` (`id`, `value`) VALUES
    ->   (1, 100),
    ->   (2, 200),
    ->   (3, 300);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.6 > CREATE TABLE `table1_use` (
    ->   `id` int(11) NOT NULL,
    ->   `table1id` int(11) DEFAULT NULL,
    ->   `uid` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.38 sec)

mysql 5.6 > INSERT INTO `table1_use` (`id`, `table1id`, `uid`) VALUES
    ->   (1, 1, 99);
Query OK, 1 row affected (0.03 sec)

mysql 5.6 >
mysql 5.6 > -- my query (with out error in MySQL 5.2)
mysql 5.6 > select
    ->       temp.sel
    -> from
    ->     table1 t1
    ->     left join (
    ->          select *,1 as sel from table1_use t1u where t1u.`table1id`=1
    ->     ) temp on temp.table1id = t1.id
    -> order by t1.value;
+------+
| sel  |
+------+
|    1 |
| NULL |
| NULL |
+------+
3 rows in set (0.09 sec)

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.18 Source distribution PULL: 2017-FEB-01

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.7 > USE test
Database changed
mysql 5.7 > CREATE TABLE `table1` (
    ->   `id` int(11) NOT NULL,
    ->   `value` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.64 sec)

mysql 5.7 > INSERT INTO `table1` (`id`, `value`) VALUES
    ->   (1, 100),
    ->   (2, 200),
    ->   (3, 300);
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.7 > CREATE TABLE `table1_use` (
    ->   `id` int(11) NOT NULL,
    ->   `table1id` int(11) DEFAULT NULL,
    ->   `uid` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.39 sec)

mysql 5.7 > INSERT INTO `table1_use` (`id`, `table1id`, `uid`) VALUES
    ->   (1, 1, 99);
Query OK, 1 row affected (0.05 sec)

mysql 5.7 > select
    ->       temp.sel
    -> from
    ->     table1 t1
    ->     left join (
    ->          select *,1 as sel from table1_use t1u where t1u.`table1id`=1
    ->     ) temp on temp.table1id = t1.id
    -> order by t1.value;
+------+
| sel  |
+------+
|    1 |
|    1 |
|    1 |
+------+
3 rows in set (0.14 sec)

mysql 5.7 > select
    ->       temp.sel
    -> from
    ->     table1 t1
    ->     left join (
    ->          select *,1 as sel from table1_use t1u where t1u.`table1id`=1
    ->     ) temp on temp.table1id = t1.id;
+------+
| sel  |
+------+
|    1 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

Your MySQL connection id is 5
Server version: 8.0.1-dmr Source distribution PULL: 2017-FEB-01

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 8.0 > use test
Database changed
mysql 8.0 > CREATE TABLE `table1` (
    ->   `id` int(11) NOT NULL,
    ->   `value` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.37 sec)

mysql 8.0 > INSERT INTO `table1` (`id`, `value`) VALUES
    ->   (1, 100),
    ->   (2, 200),
    ->   (3, 300);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 8.0 > CREATE TABLE `table1_use` (
    ->   `id` int(11) NOT NULL,
    ->   `table1id` int(11) DEFAULT NULL,
    ->   `uid` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.34 sec)

mysql 8.0 > INSERT INTO `table1_use` (`id`, `table1id`, `uid`) VALUES
    ->   (1, 1, 99);
Query OK, 1 row affected (0.03 sec)

mysql 8.0 >
mysql 8.0 > -- my query (with out error in MySQL 5.2)
mysql 8.0 > select
    ->       temp.sel
    -> from
    ->     table1 t1
    ->     left join (
    ->          select *,1 as sel from table1_use t1u where t1u.`table1id`=1
    ->     ) temp on temp.table1id = t1.id
    -> order by t1.value;
+------+
| sel  |
+------+
|    1 |
|    1 |
|    1 |
+------+
3 rows in set (0.08 sec)

mysql 8.0 > select
    ->       temp.sel
    -> from
    ->     table1 t1
    ->     left join (
    ->          select *,1 as sel from table1_use t1u where t1u.`table1id`=1
    ->     ) temp on temp.table1id = t1.id;
+------+
| sel  |
+------+
|    1 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

Posted by developer:
 
A work-around is to set optimizer_switch='derived_merge=off':

mysql> set optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0,00 sec)

mysql> select temp.sel from table1 t1 left join (select *,1 as sel from table1_use t1u where t1u.`table1id`=1) temp on temp.table1id = t1.id order by t1.value;
+------+
| sel  |
+------+
|    1 |
| NULL |
| NULL |
+------+
3 rows in set (0,00 sec)

When I set optimizer_switch='derived_merge=off', my system seems to slow down a bit.
Anyway, thanks for your support.

It looks like this was fixed by bug#101628 in MySQL 8.0.23.