Bug #27659 SELECT DISTINCT returns incorrect result set when field is repeated
Submitted: 4 Apr 2007 22:40 Modified: 17 Apr 2007 20:16
Reporter: Paul Keenan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:Ver 14.12 Distrib 5.0.37, for Win32 (ia3, 5.1, 5.2 OS:Linux (, Win XP Professional v5.1.2600 Service Pack 2 Build 2600)
Assigned to: Georgi Kodinov CPU Architecture:Any

[4 Apr 2007 22:40] Paul Keenan
Description:
The server returns the wrong results set when a SELECT DISTINCT is used with a repeating field.  This has broken my application when I upgraded from 5.0.21 to 5.0.37, thus S2 severity.  I've enclosed a simple test case.

Please advise urgently.

Regards,
Paul

How to repeat:
mysql> create table t1 (f1 varchar(10), f2 varchar(10));
Query OK, 0 rows affected (0.19 sec)

mysql> insert into t1 values('Hello','World'),('Hello','There'),('Hello','Campers');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select distinct f1, f2 from t1;
+-------+---------+
| f1    | f2      |
+-------+---------+
| Hello | World   |
| Hello | There   |
| Hello | Campers |
+-------+---------+
3 rows in set (0.02 sec)

(as expected)

mysql> select distinct f1, f1, f2 from t1;
+-------+-------+-------+
| f1    | f1    | f2    |
+-------+-------+-------+
| Hello | Hello | World |
+-------+-------+-------+
1 row in set (0.00 sec)

This last case should return all three rows.  Only the first row is returned.  The error occurs for both InnoDB and MyISAM table types.
[5 Apr 2007 8:24] Sveta Smirnova
Thank you for the report.

Verified as described.

Workaround: use GROUP BY
[6 Apr 2007 11:18] Timour Katchaounov
Changed priority to P2 as this is wrong result.
[6 Apr 2007 14:30] 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/commits/23987

ChangeSet@1.2435, 2007-04-06 17:30:10+03:00, gkodinov@magare.gmz +3 -0
  Bug #27659: 
  The optimizer transforms DISTINCT into a GROUP BY
  when possible.
  It does that by constructing the same structure
  (a list of ORDER instances) the parser makes when
  parsing GROUP BY.
  While doing that it also eliminates duplicates.
  But if a duplicate is found it doesn't advance the
  pointer to ref_pointer array, so the next 
  (and subsequent) ORDER structures point to the wrong
  element in the SELECT list.
  Fixed by advancing the pointer in ref_pointer_array
  even in the case of a duplicate.
[10 Apr 2007 13:56] 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/commits/24173

ChangeSet@1.2435, 2007-04-10 16:55:48+03:00, gkodinov@magare.gmz +3 -0
  Bug #27659: 
  The optimizer transforms DISTINCT into a GROUP BY
  when possible.
  It does that by constructing the same structure
  (a list of ORDER instances) the parser makes when
  parsing GROUP BY.
  While doing that it also eliminates duplicates.
  But if a duplicate is found it doesn't advance the
  pointer to ref_pointer array, so the next 
  (and subsequent) ORDER structures point to the wrong
  element in the SELECT list.
  Fixed by advancing the pointer in ref_pointer_array
  even in the case of a duplicate.
[15 Apr 2007 16:48] Bugs System
Pushed into 5.1.18-beta
[15 Apr 2007 16:53] Bugs System
Pushed into 5.0.40
[17 Apr 2007 20:16] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

SELECT DISTINCT could return incorrect results if the select list
contained duplicated columns.