[MDEV-12872] incorrect result order when reusing aliases Created: 2017-05-23  Updated: 2017-10-09  Resolved: 2017-10-09

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.0, 10.1, 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Oleksandr Byelkin Assignee: Oleksandr Byelkin
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7626 incorrect result order when reusing a... Closed
Sprint: 10.2.10

 Description   

In fact, this behavior was changed in scope of MDEV-8913 – that is, 10.x now works the same way as described here for MySQL, the result is sorted at random. 

MariaDB [test]> SELECT   `SubQuery`.`Static` AS `Random`,   `SubQuery`.`Name` FROM (   SELECT     RAND() AS `Random`,     1 AS `Static`,     `Name`   FROM `foobar`   ) AS `SubQuery` ORDER BY `SubQuery`.`Random`, `SubQuery`.`Name`;
 
+--------+------+
 
| Random | Name |
 
+--------+------+
 
|      1 | D    |
 
|      1 | A    |
 
|      1 | E    |
 
|      1 | C    |
 
|      1 | B    |
 
+--------+------+
 
5 rows in set (0.01 sec)
 
 
 
MariaDB [test]> SELECT   `SubQuery`.`Static` AS `Random`,   `SubQuery`.`Name` FROM (   SELECT     RAND() AS `Random`,     1 AS `Static`,     `Name`   FROM `foobar`   ) AS `SubQuery` ORDER BY `SubQuery`.`Random`, `SubQuery`.`Name`;
 
+--------+------+
 
| Random | Name |
 
+--------+------+
 
|      1 | B    |
 
|      1 | C    |
 
|      1 | D    |
 
|      1 | A    |
 
|      1 | E    |
 
+--------+------+
 
5 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+-----------------------+
 
| @@version             |
 
+-----------------------+
 
| 10.0.31-MariaDB-debug |
 
+-----------------------+
 
1 row in set (0.00 sec)

On the other hand, MySQL 5.7 now sorts by name.

So, if it was an unfixed bug in MySQL and correct result in MariaDB, it's vice versa now, and you'll have to re-fix it. 

commit fa1438cbf4307731a54ea4137d5f7d4b744cdfbc
 
Author: Oleksandr Byelkin
 
Date:   Tue Oct 27 11:17:52 2015 +0100
 
 
 
    MDEV-8913 Derived queries with same column names as final projection causes issues when using Order By
 
    
    find_item_in_list() now recognize view fields like a fields even if they rever to an expression.
 
    The problem of schema name do not taken into account for field with it and
 
    derived table fixed.
 
    Duplicating code removed



 Comments   
Comment by Oleksandr Byelkin [ 2017-10-09 ] 

create table foobar (name varchar(10));
 
insert into foobar values ('A'),('B'),('D'),('E'),('F');
 
SELECT
 
`SubQuery`.`Static` AS `Random`,
 
`SubQuery`.`Name` FROM (
 
SELECT 
RAND() AS `Random`,
 
1 AS `Static`,
 
`Name`   FROM `foobar`) AS `SubQuery`
 
  ORDER BY `SubQuery`.`Random`, `SubQuery`.`Name`;
 
Random	Name
 
1	F
 
1	A
 
1	E
 
1	B
 
1	D
 
SELECT
 
`SubQuery`.`Static` AS `Random`,
 
`SubQuery`.`Name` FROM (
 
SELECT 
RAND() AS `Random`,
 
1 AS `Static`,
 
`Name`   FROM `foobar`) AS `SubQuery`
 
  ORDER BY `Random`, `Name`;
 
Random	Name
 
1	A
 
1	B
 
1	D
 
1	E
 
1	F
 
drop table foobar;

First select explicit order by derived table (table name is mentioned), second by result column (no table name). IMHO it is correct.

Comment by Oleksandr Byelkin [ 2017-10-09 ]

MySQL 8.0 in both cases uses result list which is not correct.

Generated at Thu Feb 08 08:01:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.