MySQL Bugs: #68920: Inconsistent ordering with column alias in expression

Bug #68920	Inconsistent ordering with column alias in expression
Submitted:	10 Apr 2013 16:09	Modified:	11 Apr 2013 15:58
Reporter:	KESZEG ALEXANDRU	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.5.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using a column alias in an order by with a table containing the same column name gives different results when using expression or not

How to repeat:
CREATE TABLE `foo` ( `i` int(10), `c` varchar(10) );

INSERT INTO `foo` (`i`, `c`) VALUES (10, 'ten'), (20, 'twenty');

select 1 as i, foo.* from foo order by i desc;
+---+------+--------+
| i | i    | c      |
+---+------+--------+
| 1 |   10 | ten    |
| 1 |   20 | twenty |
+---+------+--------+
2 rows in set (0.00 sec)

select 1 as i, foo.* from foo order by coalesce(i) desc;
+---+------+--------+
| i | i    | c      |
+---+------+--------+
| 1 |   20 | twenty |
| 1 |   10 | ten    |
+---+------+--------+

Not sure there is any bug here.  The coalesce query does a filesort.
But since you are ordering on the literal value "1" for both, why should the other columns be in order if you don't specify them in the order by also?

Check my example:
----------
drop table if exists `t1`;
create table `t1`( `i` int, `c` int);
insert into `t1`(`i`, `c`) values (30,3),(20,1);
select 1 `i`, `t1`.* from `t1` order by `i`;
select 1 `i`, `t1`.* from `t1` order by coalesce(`i`);
select 1 `i`, `t1`.* from `t1` order by `i`,`t1`.`i`;
select 1 `i`, `t1`.* from `t1` order by coalesce(`i`),`t1`.`i`;
---------

mysql> select 1 `i`, `t1`.* from `t1` order by `i`;
+---+------+------+
| i | i    | c    |
+---+------+------+
| 1 |   30 |    3 |
| 1 |   20 |    1 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select 1 `i`, `t1`.* from `t1` order by coalesce(`i`);
+---+------+------+
| i | i    | c    |
+---+------+------+
| 1 |   20 |    1 |
| 1 |   30 |    3 |
+---+------+------+
2 rows in set (0.00 sec)

mysql>
mysql> select 1 `i`, `t1`.* from `t1` order by `i`,`t1`.`i`;
+---+------+------+
| i | i    | c    |
+---+------+------+
| 1 |   20 |    1 |
| 1 |   30 |    3 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select 1 `i`, `t1`.* from `t1` order by coalesce(`i`),`t1`.`i`;
+---+------+------+
| i | i    | c    |
+---+------+------+
| 1 |   20 |    1 |
| 1 |   30 |    3 |
+---+------+------+
2 rows in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.5/en/select.html
"A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses."

Lets remove the constant and coalesce:

----------
drop table if exists `t1`;
create table `t1`( `i` int, `c` int);
insert into `t1`(`i`, `c`) values (30,1),(20,3);

select `t1`.`i` * t1.`c` `i`, `t1`.* from `t1` order by `i` + 0;
select `t1`.`i` * t1.`c` `i`, `t1`.* from `t1` order by `i`;
---------

mysql> select `t1`.`i` * t1.`c` `i`, `t1`.* from `t1` order by `i` + 0;
+------+------+------+
| i    | i    | c    |
+------+------+------+
|   60 |   20 |    3 |
|   30 |   30 |    1 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> select `t1`.`i` * t1.`c` `i`, `t1`.* from `t1` order by `i`;
+------+------+------+
| i    | i    | c    |
+------+------+------+
|   30 |   30 |    1 |
|   60 |   20 |    3 |
+------+------+------+
2 rows in set (0.00 sec)

Is it wrong to expect the two queries to have the same order?

Actually, this is not a bug.

There are two problems here. First one is sorting by column as one option and function or expression involving that column as a second option. Order is not guaranteed to be the same in both cases. Order is guaranteed on the re-run of the same query. This is because different methods are used for ORDER BY. In first case, sorting is optimized out, while in second case filesort has to be used. Filesort must be used whenever final result has to be sorted and we are not dealing with indexed column ONLY, but with some function or expression.

There is a second problem. That one is contained in ambiguity when alias has the same name as the column. I consulted SQL99 and alias takes precedence.

I had to change my mind, because of the second example.

There is an alias and a column. In one case, ordering is done by alias and in another case it is done by column.

That is a bug. A VERY SMALL BUG. But, still a bug.