Bug #76283 Incorrect result for DISTINCT SELECT

• Submitted: 12 Mar 2015 9:50
• Modified: 21 Aug 2017 18:06
• Reporter: Benny Wang
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[12 Mar 2015 9:50] Benny Wang

Description:
Returns incorrect result for the following query:
select distinct least(1,count(distinct `a`)) from t group by `a`;

mysql> select  least(1,count(distinct `a`)) from t group by `a`;
+------------------------------+
| least(1,count(distinct `a`)) |
+------------------------------+
|                            1 |
|                            1 |
+------------------------------+
2 rows in set (0.00 sec)

mysql> select distinct least(1,count(distinct `a`)) from t group by `a`;
+------------------------------+
| least(1,count(distinct `a`)) |
+------------------------------+
|                            0 | <<<<< should be 1
+------------------------------+
1 row in set (0.00 sec)

How to repeat:
create table t(a int, b int);
insert into t values(1,2);
insert into t values(2,4);
select  least(1,count(distinct `a`)) from t group by `a`;
select distinct least(1,count(distinct `a`)) from t group by `a`;

[21 Aug 2017 18:06] Paul DuBois

Posted by developer:
 
Fixed in 8.0.3.

Some SELECT DISTINCT queries with GROUP BY could return incorrect
results.