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: | |
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
[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;