MySQL Bugs: #7946: distinct with subquery and group by

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

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

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)

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"

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

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)

Thank you for bugreport. Bugfix is pushed into 4.1.10 (5.0.3) source repository

Mentioned in 4.1.10 and 5.0.3 change notes.