Bug #8614 Submitted: distinct sometimes ignored with group by "CONST" 19 Feb 2005 0:53 22 Jun 2005 2:29 Martin Friebe (Gold Quality Contributor) (OCA) Closed None MySQL Server: Optimizer S3 (Non-critical) 4.1.10 Any (*) Igor Babaev Any

[19 Feb 2005 0:53] Martin Friebe
```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:
-```
[16 Jun 2005 16:45] Bugs System
```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```
[16 Jun 2005 18:59] Bugs System
```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```
[17 Jun 2005 17:44] Igor Babaev
```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.```
[22 Jun 2005 2:29] Jon Stephens
```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