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 Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.7.8, 5.6.30, 5.7.12 OS:Any
Assigned to: 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] Umesh Shastry
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.