MySQL Bugs: #20553: sometimes 'select distinct A,A as m' with 'order by' returns a wrong result

Bug #20553	sometimes 'select distinct A,A as m' with 'order by' returns a wrong result
Submitted:	20 Jun 2006 6:59	Modified:	20 Jun 2006 7:29
Reporter:	zhao ike	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19	OS:	Linux (centos 3.0)
Assigned to:		CPU Architecture:	Any

Description:
sometimes 'select distinct A,A as m' with 'order by' returns a wrong result

How to repeat:
CREATE TABLE `TestTest` (                   
          `A` int(11) NOT NULL,                 
          KEY `A` (`A`)                     
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into TestTest values (4),(4),(3),(2),(3),(1),(3),(4),(4),(1);

select distinct A,A as m from TestTest order by A;

result:
4	4
4	4
4	4
4	4

Hi.
Thanks for your problem report. I was unable to verify with MySQL server 5.0.23 BK on Suse 10 and WinXP Pro SP2. If you can give me closer description of "sometimes" or you feel you can add more usefull data, please reopen this report.

Suse test:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE `TestTest` (
    ->           `A` int(11) NOT NULL,
    ->           KEY `A` (`A`)
    ->         ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into TestTest values (4),(4),(3),(2),(3),(1),(3),(4),(4),(1);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TestTest ORDER BY A;
+---+
| A |
+---+
| 1 |
| 1 |
| 2 |
| 3 |
| 3 |
| 3 |
| 4 |
| 4 |
| 4 |
| 4 |
+---+
10 rows in set (0.00 sec)

mysql> select distinct A,A as m from TestTest order by A\G
*************************** 1. row ***************************
A: 1
m: 1
*************************** 2. row ***************************
A: 2
m: 2
*************************** 3. row ***************************
A: 3
m: 3
*************************** 4. row ***************************
A: 4
m: 4
4 rows in set (0.00 sec)

mysql> select distinct A,A as m from TestTest order by A;
+---+---+
| A | m |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
4 rows in set (0.00 sec)

mysql> select distinct A,A as m from TestTest order by A;
+---+---+
| A | m |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
4 rows in set (0.00 sec)

Win32 test:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE `TestTest` (
    ->           `A` int(11) NOT NULL,
    ->           KEY `A` (`A`)
    ->         ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into TestTest values (4),(4),(3),(2),(3),(1),(3),(4),(4),(1);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select distinct A,A as m from TestTest order by A\G
*************************** 1. row ***************************
A: 1
m: 1
*************************** 2. row ***************************
A: 2
m: 2
*************************** 3. row ***************************
A: 3
m: 3
*************************** 4. row ***************************
A: 4
m: 4
4 rows in set (0.02 sec)

mysql> select distinct A,A as m from TestTest order by A;
+---+---+
| A | m |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
4 rows in set (0.00 sec)

mysql> select distinct A,A as m from TestTest order by A;
+---+---+
| A | m |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
4 rows in set (0.00 sec)

mysql> select distinct A,A as m from TestTest order by A;
+---+---+
| A | m |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
4 rows in set (0.00 sec)

mysql> select distinct A,A as m from TestTest order by A;
+---+---+
| A | m |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
4 rows in set (0.00 sec)

mysql>

Bug: http://bugs.mysql.com/bug.php?id=20606 was marked as duplicate
of this one.

I am using 5.0.21-community-nt and I am getting the same error, when the column is the first/only column in a primary key.
Example is:
create table years (
years	smallint not null,
primary key id(years)
);
insert into years values(2006);
insert into years values(2007);
insert into years values(2008);
insert into years values(2009);
insert into years values(2010);
insert into years values(2011);
insert into years values(2012);
insert into years values(2013);
insert into years values(2014);
insert into years values(2015);
SELECT distinct years as label, years as tag FROM years WHERE years between year(now()) and year(date_add(now(), interval 2 year)) ORDER BY years

returns 4 rows of 2008 2008
if I remove the ORDER BY clause I get the correct result
Note: it occurs for all other tables with the above conditions, this example is the easiest to give you

Also when I did an explain of the queries:

with the ORDER BY (incorrect result)
Extra: Using where; Using index for group-by; Using filesort

without ORDER BY (correct result)
Extra: Using where; Using index for group-by; Using temporary

Rest of explain output is the same.

oops I meant three rows of 2008 2008