Bug #78497 Large select with nested subqueries return 0 rows

• Submitted: 21 Sep 2015 8:44
• Modified: 21 Sep 2015 9:09
• Reporter: Jose Ignacio Andrés
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6, 5.6.26, 5.6.28, 5.7.10
• OS: Ubuntu
• CPU Architecture: Any
• Tags: IN, subquery

[21 Sep 2015 8:44] Jose Ignacio Andrés

Description:
I've a complex SQL which I simplify to report this bug. It queries a table with 200K rows, a where condition which is a subquery and finally and IN () with contains another subquery.

It only fails when the number of rows in the list_1 and list_2 tables increase, if you have only one row on each table, it works fine.

How to repeat:
DROP TABLE IF EXISTS `list_1`;
CREATE TABLE IF NOT EXISTS `list_1` (
  `uid` int(11) NOT NULL,
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO list_1 SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5,
(select 0 union all select 1) t6,
(SELECT @row:=0) t7;

DROP TABLE IF EXISTS `list_2`;
CREATE TABLE IF NOT EXISTS `list_2` (
  `uid` int(11) NOT NULL,
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `list_2` SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 union all select 1 union all select 2) t5,
(SELECT @row:=0) t6;

DROP TABLE IF EXISTS `list_3`;
CREATE TABLE IF NOT EXISTS `list_3` (
  `uid` int(11) NOT NULL,
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `list_3` VALUES (1);

-- test data ready, begin test

SELECT uid
FROM list_1
WHERE  (
	select (
		select IF(count(list_3.uid) = 0, 0, 1)
		from list_3
		where list_3.uid = list_1.uid
	)
) = 1
AND list_1.uid IN (
	SELECT list_2.uid FROM list_2
)
LIMIT 10;

Suggested fix:
In this example I'm able to simplify the query to this:

SELECT uid
FROM list_1
WHERE  (
	select (
		select IF(count(list_3.uid) = 0, 0, 1)
		from list_3
		where list_3.uid = 1
	)
) = 1
AND list_1.uid IN (
	SELECT list_2.uid FROM list_2
)
LIMIT 10;

but in my piece of software I'm not, because I have an IF statement over the nested subquery which I cannot remove.

-----

As I said before, it only fails when the number of rows in the list_1 and list_2 tables increase, if you have only one row on each table, it works fine.

[21 Sep 2015 9:09] MySQL Verification Team

Hello Jose Ignacio Andrés,

Thank you for the report and test case.
Verified as described with 5.6.26, 5.6.28, and 5.7.10 builds.

Thanks,
Umesh

[21 Sep 2015 9:09] MySQL Verification Team

// 5.6.28 - affected

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> use test
Database changed
mysql> DROP TABLE IF EXISTS `list_1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `list_1` (
    ->   `uid` int(11) NOT NULL,
    ->   UNIQUE KEY `uid` (`uid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO list_1 SELECT @row := @row + 1 as row FROM
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5,
    -> (select 0 union all select 1) t6,
    -> (SELECT @row:=0) t7;

DROP TABLE IF EXISTS `list_2`;
CREATE TABLE IF NOT EXISTS `list_2` (
  `uid` int(11) NOT NULL,
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `list_2` SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 union all select 1 union all select 2) t5,
(SELECT @row:=0) t6;

DROP TABLE IF EXISTS `list_3`;
CREATE TABLE IF NOT EXISTS `list_3` (
  `uid` int(11) NOT NULL,
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `list_3` VALUES (1);Query OK, 200000 rows affected (0.82 sec)
Records: 200000  Duplicates: 0  Warnings: 0

mysql>
mysql> DROP TABLE IF EXISTS `list_2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `list_2` (
    ->   `uid` int(11) NOT NULL,
    ->   UNIQUE KEY `uid` (`uid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO `list_2` SELECT @row := @row + 1 as row FROM
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
    -> (select 0 union all select 1 union all select 2) t5,
    -> (SELECT @row:=0) t6;
Query OK, 30000 rows affected (0.16 sec)
Records: 30000  Duplicates: 0  Warnings: 0

mysql>
mysql> DROP TABLE IF EXISTS `list_3`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `list_3` (
    ->   `uid` int(11) NOT NULL,
    ->   UNIQUE KEY `uid` (`uid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO `list_3` VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT uid
    -> FROM list_1
    -> WHERE  (select (select IF(count(list_3.uid) = 0, 0, 1) from list_3 where list_3.uid = list_1.uid)) = 1 AND list_1.uid IN (SELECT list_2.uid FROM list_2)LIMIT 10;
Empty set (0.13 sec)

mysql>

[21 Sep 2015 9:09] MySQL Verification Team

// 5.7.10 - affected

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS `list_1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `list_1` (
    ->   `uid` int(11) NOT NULL,
    ->   UNIQUE KEY `uid` (`uid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO list_1 SELECT @row := @row + 1 as row FROM
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5,
    -> (select 0 union all select 1) t6,
    -> (SELECT @row:=0) t7;

DROP TABLE IF EXISTS `list_2`;
CREATE TABLE IF NOT EXISTS `list_2` (
  `uid` int(11) NOT NULL,
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `list_2` SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 union all select 1 union all select 2) t5,
(SELECT @row:=0) t6;

DROP TABLE IF EXISTS `list_3`;
CREATE TABLE IF NOT EXISTS `list_3` (
  `uid` int(11) NOT NULL,
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `list_3` VALUES (1);Query OK, 200000 rows affected (1.22 sec)
Records: 200000  Duplicates: 0  Warnings: 0

mysql>
mysql> DROP TABLE IF EXISTS `list_2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `list_2` (
    ->   `uid` int(11) NOT NULL,
    ->   UNIQUE KEY `uid` (`uid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO `list_2` SELECT @row := @row + 1 as row FROM
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
    -> (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
    -> (select 0 union all select 1 union all select 2) t5,
    -> (SELECT @row:=0) t6;
Query OK, 30000 rows affected (0.19 sec)
Records: 30000  Duplicates: 0  Warnings: 0

mysql>
mysql> DROP TABLE IF EXISTS `list_3`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `list_3` (
    ->   `uid` int(11) NOT NULL,
    ->   UNIQUE KEY `uid` (`uid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO `list_3` VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT uid
    -> FROM list_1
    -> WHERE  (select (select IF(count(list_3.uid) = 0, 0, 1) from list_3 where list_3.uid = list_1.uid)) = 1 AND list_1.uid IN (SELECT list_2.uid FROM list_2)LIMIT 10;
Empty set (0.18 sec)

mysql>