[MDEV-10237] Subquery with Empty Set in result returns incorrect result Created: 2016-06-15  Updated: 2022-01-25

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.1.10, 5.5, 10.0, 10.1, 10.2
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Dylan Su Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None


 Description   

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.

 Comments   
Comment by Elena Stepanova [ 2016-06-15 ]

Surprisingly, all of MariaDB, MySQL, PostgreSQL and Oracle return exactly the same number of rows – empty result for the first query and 1 for the other two. This doesn't usually happen even with much less questionable cases.

Although, PostgreSQL returns '' for the 2nd query and FALSE for the 3rd query:

postgres=# select (select 1 limit 0);
 
 ?column? 
----------
 
         
(1 row)
 
 
 
postgres=# select exists(select 1 limit 0);
 
 exists 
--------
 
 f
 
(1 row)

While others return 1. 

MySQL [test]> select @@version;
 
+--------------+
 
| @@version    |
 
+--------------+
 
| 5.7.13-debug |
 
+--------------+
 
1 row in set (0.00 sec)
 
 
 
MySQL [test]> (select 1 limit 0);
 
Empty set (0.00 sec)
 
 
 
MySQL [test]> select (select 1 limit 0);
 
+--------------------+
 
| (select 1 limit 0) |
 
+--------------------+
 
|                  1 |
 
+--------------------+
 
1 row in set (0.00 sec)
 
 
 
MySQL [test]> select exists(select 1 limit 0);
 
+--------------------------+
 
| exists(select 1 limit 0) |
 
+--------------------------+
 
|                        1 |
 
+--------------------------+
 
1 row in set (0.00 sec)

I'll leave it to an expert to decide what would be the correct result here.

Generated at Thu Feb 08 07:40:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.