Bug #81684 select + where exists( select + group by + limit) works incorrectly

• Submitted: 2 Jun 2016 6:05
• Modified: 2 Jun 2016 8:38
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.7.8, 5.6.30, 5.7.12
• OS: Any
• CPU Architecture: Any
• Tags: regression

[2 Jun 2016 6:05] Su Dylan

Description:
Output:
=====
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1),(1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| c1   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> select 1 from dual where exists (select c1 from t1 group by c1 limit 1,1);
+---+
| 1 |
+---+
| 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:
=====
"Empty set" is expected.

This is really a serious bug.

How to repeat:

drop table t1;
create table t1(c1 int);
insert into t1 values(1),(1);
select * from t1;
select 1 from dual where exists (select c1 from t1 group by c1 limit 1,1);

Suggested fix:
"Empty set" is returned.

[2 Jun 2016 8:38] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.6.30 and 5.7.12 are affected.

Thanks,
Umesh

[2 Jun 2016 13:22] Peter Laursen

Same in MariaDB 10.1.14.