MySQL Bugs: #8614: distinct sometimes ignored with group by "CONST"

Bug #8614	distinct sometimes ignored with group by "CONST"
Submitted:	19 Feb 2005 0:53	Modified:	22 Jun 2005 2:29
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any (*)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
see examples, all result are distinct, but not alway grouped by "CONST".
group by "CONST" should always only return one row.
it doesnt matter if the group is applied to the distinct result or the distinct after the group (would be intersting to know, which way round it should be?)

How to repeat:
select distinct  * from (select 1 union  select 2 ) x group by "CONST";
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

# more interesting cases with a table 
create table t1 (a integer, b integer); insert into t1 values (1,2), (1,3), (1,4), (1,5);

# first 2 are correct
 select    distinct  a,b from t1 x group by "A";
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

select    distinct  a from t1 x group by "A";
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

# but this isnt (interesting that a,b did work / btw b,a also works, the order is not of a matter)
select    distinct  b from t1 x group by "A";
+------+
| b    |
+------+
|    2 |
|    3 |
|    4 |
|    5 |
+------+
4 rows in set (0.00 sec)

# and to add one more
# this works
 select distinct  a from (select 1 a,3 b union  select 1,2 ) x group by "CONST";
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

#this doesnt, but it did when the data came from a table, instead of a subquery/union
select distinct  a,b from (select 1 a,3 b union  select 1,2 ) x group by "CONST";
+---+---+
| a | b |
+---+---+
| 1 | 3 |
| 1 | 2 |
+---+---+
2 rows in set (0.00 sec)

Suggested fix:
-

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26082

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26089

ChangeSet
  1.2297 05/06/16 09:45:41 igor@rurik.mysql.com +3 -0
  group_by.result, group_by.test:
    Added a test case for bug #8614.
  sql_select.cc:
    Fixed bug #8614.
    SELECT DISTINCT ... GROUP BY 'const' must be equivalent to
    SELECT ... GROUP BY 'const'.

The fix will appear in 4.1.13 and 5.0.8.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in Change History for 4.1.13 and 5.0.8; marked bug Closed.