MySQL Bugs: #27659: SELECT DISTINCT returns incorrect result set when field is repeated

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

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.

Thank you for the report.

Verified as described.

Workaround: use GROUP BY

Changed priority to P2 as this is wrong result.

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.

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.

Pushed into 5.1.18-beta

Pushed into 5.0.40

Noted in 5.0.40, 5.1.18 changelogs.

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