MySQL Bugs: #80310: Regression for a grouping query with DISTINCT

Bug #80310	Regression for a grouping query with DISTINCT
Submitted:	9 Feb 2016 3:55	Modified:	12 Dec 2016 1:30
Reporter:	Igor Babaev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7, 5.6.29, 5.7.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
The following query returns a wrong result in mysql-5.7/5.6
SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP by a:

mysql> SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP by a;
+---+
| c |
+---+
| 0 |
+---+

In mysql-5.5 the result is correct.

How to repeat:
create table t1 (a int, b int);
insert into t1 values (1,3), (2,4), (1,5), (1,3), (2,1), (1,5), (1,7), (3,1), (3,2), (3,1), (2,4);
SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP BY a;

Hello Igor,

Thank you for the report and test case.
Observed that 5.6.29/5.7.11 are affected.

Thanks,
Umesh

-- 5.6.29/5.7.11 - affected

-- 5.6.29

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.29: 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.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> create table t1 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP BY a;
+---+
| c |
+---+
| 0 |
+---+
1 row in set (0.00 sec)

mysql>

-- 5.7.11

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.11: 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.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> create table t1 (a int, b int);
Query OK, 0 rows affected (0.01 sec)

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

mysql> SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP BY a;
+---+
| c |
+---+
| 0 |
+---+
1 row in set (0.00 sec)

mysql>

-- 5.5 and below versions not affected

-- 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> use test
Database changed
mysql> create table t1 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP BY a;
+---+
| c |
+---+
| 4 |
| 3 |
+---+
2 rows in set (0.02 sec)

mysql>

-- 5.0.96

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.0.96: 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.0.96 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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> create table t1 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP BY a;
+---+
| c |
+---+
| 4 |
| 3 |
+---+
2 rows in set (0.00 sec)

mysql>

-- 5.1.77

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.1.77: 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.1.77 Source distribution

Copyright (c) 2000, 2013, 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> create table t1 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP BY a;
+---+
| c |
+---+
| 4 |
| 3 |
+---+
2 rows in set (0.07 sec)

mysql>

-- 5.5.49

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.5.49: 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.5.49-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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> create table t1 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT DISTINCT (COUNT(distinct b) + 1) AS c FROM t1 GROUP BY a;
+---+
| c |
+---+
| 4 |
| 3 |
+---+
2 rows in set (0.00 sec)

mysql>

Posted by developer:
 
Noted in 5.6.36, 5.7.18, 8.0.1.

Queries that used an aggregate function with DISTINCT could produce
incorrect results.