Bug #81855 Subquery with Empty Set in result returns incorrect result

• Submitted: 15 Jun 2016 8:07
• Modified: 15 Jun 2016 12:16
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.7.8, 5.5.50, 5.6.31, 5.7.13
• OS: Any
• CPU Architecture: Any

[15 Jun 2016 8:07] Su Dylan

Description:
Output:
===
mysql> (select 1 limit 0);
Empty set (0.00 sec)

mysql> select (select 1 limit 0);
+--------------------+
| (select 1 limit 0) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> select exists(select 1 limit 0);
+--------------------------+
| exists(select 1 limit 0) |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.7.8-rc-log |
+--------------+
1 row in set (0.00 sec)

Problem:
===
The last 2 queries is expected to return NULL.

How to repeat:

(select 1 limit 0);
select (select 1 limit 0);
select exists(select 1 limit 0);

Suggested fix:
The last 2 queries is expected to return NULL.

[15 Jun 2016 12:16] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.

Thanks,
Umesh

[15 Jun 2016 12:17] MySQL Verification Team

-- 4.1.25

[umshastr@hod03]/export/umesh/server/binaries/mysql-4.1.25: bin/mysql -uroot -S/tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.25-classic

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> (select 1 limit 0);
Empty set (0.00 sec)

mysql> select (select 1 limit 0);
+--------------------+
| (select 1 limit 0) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> select exists(select 1 limit 0);
+--------------------------+
| exists(select 1 limit 0) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.01 sec)

mysql>

-- 5.0.96, 5.1.77, 5.5.50, 5.6.31, 5.7.13

mysql> (select 1 limit 0);
Empty set (0.00 sec)

mysql> select (select 1 limit 0);
+--------------------+
| (select 1 limit 0) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

mysql> select exists(select 1 limit 0);
+--------------------------+
| exists(select 1 limit 0) |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

mysql>