Bug #7946 distinct with subquery and group by
Submitted: 16 Jan 2005 19:20 Modified: 4 Feb 2005 21:04
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.9 OS:
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[16 Jan 2005 19:20] Martin Friebe
Description:
see repeat section. the query is supposed to build sum() for various groups, and the to return the distinct sums. 
which is to be ablle to see, if a specific sum is in the result or not.

How to repeat:
create table x (a integer, b integer);
insert into x values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);

# the next query will return the correct result.
# Each sum is 4, distinct is specified, so only one row with 4 is returned
select distinct sum(b) from x group by a;
+--------+
| sum(b) |
+--------+
|      4 |
+--------+
1 row in set (0.01 sec)

# the same query, but rewritten. hiding table x in a subquery
select distinct sum(b) from (select a,b from x) y group by a;
+--------+
| sum(b) |
+--------+
|      4 |
|      4 |
|      4 |
+--------+
3 rows in set (0.00 sec)
# the distinct is ignored

Suggested fix:
-
[16 Jan 2005 19:40] Martin Friebe
neither does this return distinct:

select distinct (select  sum(b) from x where a=y.a )  from x as y group by a;
+--------------------------------------+
| (select  sum(b) from x where a=y.a ) |
+--------------------------------------+
|                                    4 |
|                                    4 |
|                                    4 |
+--------------------------------------+
3 rows in set (0.00 sec)
 
select distinct (select 4 )  from x as y group by a;
+-------------+
| (select 4 ) |
+-------------+
|           4 |
|           4 |
|           4 |
+-------------+
3 rows in set (0.00 sec)
[19 Jan 2005 18:46] Martin Friebe
One more: it happens the other way round to 

# group by constant, naturaly one row
select  * from (select 1 union  select 2 ) x group by "A";
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.20 sec)
 
# the above was allready distinct
mysql> select distinct  * from (select 1 union  select 2 ) x group by "A";
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

# now I have to results for the same fix group value "A"
[19 Jan 2005 19:09] Martin Friebe
sorry for adding that rather late (and in tiny bits), but I thing it is related, so I put it here instead of opening a new bug. 

try the 2d query from the original post with rollup

 select distinct sum(b) from (select a,b from x) y group by a with rollup;
+--------+
| sum(b) |
+--------+
|      4 |
|      8 |
|     12 |
+--------+

it works fine without the distinct
[20 Jan 2005 11:57] Oleksandr Byelkin
ChangeSet
  1.2146 05/01/20 13:56:22 bell@sanja.is.com.ua +3 -0
  fixed problem with distinct select with grouping and subqueries (BUG#7946)
[1 Feb 2005 12:03] Oleksandr Byelkin
Thank you for bugreport. Bugfix is pushed into 4.1.10 (5.0.3) source repository
[4 Feb 2005 21:04] Paul Dubois
Mentioned in 4.1.10 and 5.0.3 change notes.