MySQL Bugs: #72421: Regression bug with subqueries in WHERE component

Bug #72421	Regression bug with subqueries in WHERE component
Submitted:	22 Apr 2014 11:33	Modified:	17 Sep 2014 5:21
Reporter:	Steve Exley	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.17-4	OS:	Linux (RH Enterprise, x64)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
I have found an obscure difference in behaviour between:
mysql-server-5.1.71-1.el6.x86_64 (Redhat RPM)
And
mysql-community-server.x86_64 0:5.6.17-4.el6

It occurs when multiple sub-queries are used within the WHERE component of a select statement to look for matches via an IN clause, which then trigger a match in the outer query.

When there is a valid match between sub-queries, MySQL 5.6 returns no results.  The expected behaviour is in MySQL 5.1 which does return a result.  

The example below isn't practical but is the smallest test I can produce to recreate the issue. Adding indexes or changing the table engine does not appear to change the behaviour.

In MySQL 5.6, sql_mode=NO_ENGINE_SUBSTITUTION.
 

How to repeat:
CREATE TABLE `test1` (
	`test1_col1` INT(10) NULL
);
INSERT INTO `test1` (`test1_col1`) VALUES (5);

CREATE TABLE `test2` (
	`test2_col1` INT(10) NULL
);
INSERT INTO `test2` (`test2_col1`) VALUES (1),(2),(3),(4),(5);

CREATE TABLE `test3` (
	`test3_col1` INT(10) NULL
);
INSERT INTO `test3` (`test3_col1`) VALUES (3);

SELECT * FROM test1 WHERE (
	SELECT test2_col1
		FROM test2
		WHERE test2_col1=3) 
	IN (
		SELECT test3_col1
		FROM test3)

In MySQL 5.1, this returns the number 5 from table test1.  In MySQL 5.6, it returns no rows.

Substituting the subqueries with numerical values of the same result makes the query return the row from table test1.

Suggested fix:
I would expect MySQL 5.6 to behave like MySQL 5.1 in this test.

C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, 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.1 > CREATE DATABASE D22;
Query OK, 1 row affected (0.03 sec)

mysql 5.1 > USE D22
Database changed
mysql 5.1 > CREATE TABLE `test1` (
    ->  `test1_col1` INT(10) NULL
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql 5.1 > INSERT INTO `test1` (`test1_col1`) VALUES (5);
Query OK, 1 row affected (0.01 sec)

mysql 5.1 > CREATE TABLE `test2` (
    ->  `test2_col1` INT(10) NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 5.1 > INSERT INTO `test2` (`test2_col1`) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 5.1 > CREATE TABLE `test3` (
    ->  `test3_col1` INT(10) NULL
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql 5.1 > INSERT INTO `test3` (`test3_col1`) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 > SELECT * FROM test1 WHERE (
    ->  SELECT test2_col1
    ->          FROM test2
    ->          WHERE test2_col1=3)
    ->  IN (
    ->          SELECT test3_col1
    ->          FROM test3);
+------------+
| test1_col1 |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql 5.1 > exit
Bye

C:\dbs>net start mysqld56
The MySQLD56 service is starting.
The MySQLD56 service was started successfully.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19 Source distribution

Copyright (c) 2000, 2014, 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 > CREATE DATABASE D22;
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > USE D22
Database changed
mysql 5.6 > CREATE TABLE `test1` (
    ->  `test1_col1` INT(10) NULL
    -> );
Query OK, 0 rows affected (0.44 sec)

mysql 5.6 > INSERT INTO `test1` (`test1_col1`) VALUES (5);
Query OK, 1 row affected (0.02 sec)

mysql 5.6 > CREATE TABLE `test2` (
    ->  `test2_col1` INT(10) NULL
    -> );
Query OK, 0 rows affected (0.41 sec)

mysql 5.6 > INSERT INTO `test2` (`test2_col1`) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 5.6 > CREATE TABLE `test3` (
    ->  `test3_col1` INT(10) NULL
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql 5.6 > INSERT INTO `test3` (`test3_col1`) VALUES (3);
Query OK, 1 row affected (0.03 sec)

mysql 5.6 > SELECT * FROM test1 WHERE (
    ->  SELECT test2_col1
    ->          FROM test2
    ->          WHERE test2_col1=3)
    ->  IN (
    ->          SELECT test3_col1
    ->          FROM test3);
Empty set (0.00 sec)

mysql 5.6 >

Thank you for the bug report. Verified as described.

C:\dbs>net start mysqld55
The MySQLD55 service is starting..
The MySQLD55 service was started successfully.

C:\dbs>55

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.38 Source distribution

Copyright (c) 2000, 2014, 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.5 > CREATE DATABASE D22;
Query OK, 1 row affected (0.02 sec)

mysql 5.5 > USE D22
Database changed
mysql 5.5 > CREATE TABLE `test1` (
    ->  `test1_col1` INT(10) NULL
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql 5.5 > INSERT INTO `test1` (`test1_col1`) VALUES (5);
Query OK, 1 row affected (0.03 sec)

mysql 5.5 > CREATE TABLE `test2` (
    ->  `test2_col1` INT(10) NULL
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql 5.5 > INSERT INTO `test2` (`test2_col1`) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 5.5 > CREATE TABLE `test3` (
    ->  `test3_col1` INT(10) NULL
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql 5.5 > INSERT INTO `test3` (`test3_col1`) VALUES (3);
Query OK, 1 row affected (0.03 sec)

mysql 5.5 > SELECT * FROM test1 WHERE (
    ->  SELECT test2_col1
    ->          FROM test2
    ->          WHERE test2_col1=3)
    ->  IN (
    ->          SELECT test3_col1
    ->          FROM test3);
+------------+
| test1_col1 |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

mysql 5.5 >

This has been fixed in 5.6.21 and 5.7.5, under the heading of BUG#18447874.