Bug #76501 "select + distinct + group by " works incorrectly
Submitted: 27 Mar 2015 6:50 Modified: 27 Mar 2015 15:09
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.22 OS:Any
Assigned to: CPU Architecture:Any

[27 Mar 2015 6:50] Su Dylan
Description:

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a bigint primary key, b char(20),c bigint , index i2(b) , unique index i1(c));
Query OK, 0 rows affected (0.01 sec)

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

mysql> select b from t1 group by c;
+------+
| b    |
+------+
| a    |
| a    |
|      |
|      |
| NULL |
+------+
5 rows in set (0.00 sec)

mysql> select distinct b from t1 group by c;
+------+
| b    |
+------+
| a    |
|      |
+------+
2 rows in set (0.00 sec)

Expected:
mysql> select distinct b from t1 group by c;
+------+
| b    |
+------+
| NULL |
|      |
| a    |
+------+

How to repeat:
drop table if exists t1;
create table t1 (a bigint primary key, b char(20),c bigint , index i2(b) , unique index i1(c));
insert into t1 values(1,'a',1),(2,'a',2),(3,'',3),(4,'',4),(5,NULL,5);
select b from t1 group by c;
select distinct b from t1 group by c;
[27 Mar 2015 12:08] MySQL Verification Team
See http://bugs.mysql.com/bug.php?id=70657.
[27 Mar 2015 15:09] MySQL Verification Team
First of all, according to SQL standard and MySQL behavior, this type of query:

select b from t1 group by c;

would give totally inconsistent results. In 5.7 it will return an error. However, as column 'c' is unique across the entire table, column 'b' should contain exact and definite values. Hence, this is an exception.

Results that I get are equal to the results reported here. I have tested on both 5.6 and 5.7. These are the results:

+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | a    |    1 |
| 2 | a    |    2 |
| 3 |      |    3 |
| 4 |      |    4 |
| 5 | NULL |    5 |
+---+------+------+
+------+
| b    |
+------+
| a    |
| a    |
|      |
|      |
| NULL |
+------+

+------+
| b    |
+------+
| a    |
|      |
+------+

Queries were:

drop table if exists t1;
create table t1 (a bigint primary key, b char(20),c bigint , index i2(b) , unique index i1(c));
insert into t1 values(1,'a',1),(2,'a',2),(3,'',3),(4,'',4),(5,NULL,5);
select * from t1;
select b from t1 group by c;

select distinct b from t1 group by c;
drop table if exists t1;