Bug #39950 Ordering by constants ingore subseqent conditions

• Submitted: 9 Oct 2008 10:31
• Modified: 31 Oct 2008 14:59
• Reporter: Colin Guthrie
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0.67
• OS: Any (Mandriva packages 2009.0, but also MySQL packages going back to 5.0.51a (at least))
• CPU Architecture: Any

[9 Oct 2008 10:31] Colin Guthrie

Description:
When ordering by a constant value (probably rather unlikely, but none the less perfectly valid; could be used as a place holder in a query for later replacing), the optimizer ignores any subsequent ordering restrictions.

This could be similar to #30596.

How to repeat:
mysql> create table orderby (id int(10) unsigned auto_increment primary key);                                                                                                                                 
Query OK, 0 rows affected (0.04 sec)                                                                                                                                                                          

mysql> insert into orderby values(null),(null),(null),(null);
Query OK, 4 rows affected (0.03 sec)                         
Records: 4  Duplicates: 0  Warnings: 0                       

mysql> select * from orderby order by id;
+----+                                   
| id |                                   
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> select * from orderby order by id desc;
+----+
| id |
+----+
|  4 |
|  3 |
|  2 |
|  1 |
+----+
4 rows in set (0.00 sec)

mysql> select * from orderby order by 1,id;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> select * from orderby order by 1,id desc;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.01 sec)

Suggested fix:
Don't know but the last query should obviously be ordered differently!

[31 Oct 2008 14:59] Susanne Ebrecht

Many thanks for writing a bug report.

The sorting works correct here.

Select a,b,c from t order by 1; means all will be ordered by "a" because it is the first argument in select list. Order by 2 would mean here that all will be ordered by "b" and so on.

This rule is given by SQL standard.

select * from order by 1, id desc; 
is the same as 
select id from orderby order by id asc, id desc

This is not a bug this is an expected behaviour.