[MDEV-7626] incorrect result order when reusing aliases Created: 2015-02-24  Updated: 2017-05-23  Resolved: 2017-05-23

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

Type: Bug Priority: Major
Reporter: Hans Wurst Assignee: Oleksandr Byelkin
Resolution: Not a Bug Votes: 0
Labels: verified

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

 Description    

DROP TABLE IF EXISTS `foobar`;
 
CREATE  TABLE IF NOT EXISTS `foobar` (
 
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
  `name` VARCHAR(45) NULL )
 
ENGINE = InnoDB;
 
 
 
INSERT INTO `foobar` (`name`) VALUES ('A');
 
INSERT INTO `foobar` (`name`) VALUES ('B');
 
INSERT INTO `foobar` (`name`) VALUES ('C');
 
INSERT INTO `foobar` (`name`) VALUES ('D');
 
INSERT INTO `foobar` (`name`) VALUES ('E');
 
 
 
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`;

When executing in MySQL the result is sorted correctly at random. In MariaDB the result is incorrectly sorted alphabetically by name. When renaming the first alias (`SubQuery`.`Static` AS `Random`) to `Random2`, the sorting is correct in MariaDB, too.

 Comments   
Comment by Elena Stepanova [ 2015-02-25 ]

Thanks for the report. As a workaround, you can try setting optimizer_switch=derived_merge=off.

Comment by Oleksandr Byelkin [ 2017-05-19 ] 

CREATE  TABLE IF NOT EXISTS `foobar` (
 
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
  `name` VARCHAR(45) NULL )
 
ENGINE = InnoDB;
 
 
INSERT INTO `foobar` (`name`) VALUES ('A');
 
INSERT INTO `foobar` (`name`) VALUES ('B');
 
INSERT INTO `foobar` (`name`) VALUES ('C');
 
INSERT INTO `foobar` (`name`) VALUES ('D');
 
INSERT INTO `foobar` (`name`) VALUES ('E');
 
 
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`;
 
 
 
 
 
SELECT
 
  `SubQuery`.`Static` AS `Random2`,
 
  `SubQuery`.`Name`
 
FROM (
 
  SELECT
 
    RAND() AS `Random`,
 
    1 AS `Static`,
 
    `Name`
 
  FROM `foobar`
 
  ) AS `SubQuery`
 
ORDER BY
 
`SubQuery`.`Random`,
 
`SubQuery`.`Name`;
 
 
 
DROP table `foobar`;

Comment by Oleksandr Byelkin [ 2017-05-22 ]

I think it is not a bug.

1) ORDER BY actually should use references on result column names.
2) Resolving names of columns and expressions in ORDER BY is MySQL/MariaDB extension
3) Naturally names of result columns precede names of underlying tables columns in resolving.

So IMHO in MySQL is just unfixed bug. Use explicit expression in the ORDER BY if you want like:

ORDER BY rand(), name

Comment by Elena Stepanova [ 2017-05-22 ]

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

Comment by Oleksandr Byelkin [ 2017-05-23 ]

I think it should be other MDEV.

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