MySQL Bugs: #82750: mysql order by with case not working

Bug #82750	mysql order by with case not working
Submitted:	27 Aug 2016 7:00	Modified:	27 Aug 2016 20:30
Reporter:	X X	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.9, 5.7.12	OS:	Windows (Server 2012 R2)
Assigned to:		CPU Architecture:	Any
Tags:	order, sorting, windows

Description:
Sorting behaviour is random. Sometimes following code works, sometimes doesn´t.
Code example:

SET @orderBy = 0;
SET @isDescending = 1;

DROP TEMPORARY TABLE IF EXISTS orders;

CREATE TEMPORARY TABLE orders (ID BIGINT NOT NULL PRIMARY KEY, CreationDate DATETIME NOT NULL, CustomerID BIGINT NOT NULL);
INSERT INTO orders VALUES(1, '2016/08/28', 2);
INSERT INTO orders VALUES(2, '2016/08/26', 2);
INSERT INTO orders VALUES(3, '2016/08/27', 2);

SELECT * FROM orders WHERE CustomerID = 2
ORDER BY
    CASE WHEN @orderBy = 0 AND @isDescending = 0 THEN
        CreationDate
    END ASC,
    CASE WHEN @orderBy = 0 AND @isDescending = 1 THEN
        CreationDate
    END DESC;

DROP TEMPORARY TABLE IF EXISTS orders;

It seems to work in version 5.7.14, but it might be a lucky coincidence. Because in version 5.7.12 was everything working until now (more than week).

How to repeat:
SET @orderBy = 0;
SET @isDescending = 1;

DROP TEMPORARY TABLE IF EXISTS orders;

CREATE TEMPORARY TABLE orders (ID BIGINT NOT NULL PRIMARY KEY, CreationDate DATETIME NOT NULL, CustomerID BIGINT NOT NULL);
INSERT INTO orders VALUES(1, '2016/08/28', 2);
INSERT INTO orders VALUES(2, '2016/08/26', 2);
INSERT INTO orders VALUES(3, '2016/08/27', 2);

SELECT * FROM orders WHERE CustomerID = 2
ORDER BY
    CASE WHEN @orderBy = 0 AND @isDescending = 0 THEN
        CreationDate
    END ASC,
    CASE WHEN @orderBy = 0 AND @isDescending = 1 THEN
        CreationDate
    END DESC;

DROP TEMPORARY TABLE IF EXISTS orders;

Please print here your real result and the expected one. Thanks.

c:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 Source distribution PULL: 2016-AUG-26

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > SET @orderBy = 0;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > SET @isDescending = 1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 >
mysql 5.7 > DROP TEMPORARY TABLE IF EXISTS orders;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 >
mysql 5.7 > CREATE TEMPORARY TABLE orders (ID BIGINT NOT NULL PRIMARY KEY, CreationDate DATETIME NOT NULL, Cus
GINT NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > INSERT INTO orders VALUES(1, '2016/08/28', 2);
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > INSERT INTO orders VALUES(2, '2016/08/26', 2);
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > INSERT INTO orders VALUES(3, '2016/08/27', 2);
Query OK, 1 row affected (0.00 sec)

mysql 5.7 >
mysql 5.7 > SELECT * FROM orders WHERE CustomerID = 2
    -> ORDER BY
    ->     CASE WHEN @orderBy = 0 AND @isDescending = 0 THEN
    ->         CreationDate
    ->     END ASC,
    ->     CASE WHEN @orderBy = 0 AND @isDescending = 1 THEN
    ->         CreationDate
    ->     END DESC;
+----+---------------------+------------+
| ID | CreationDate        | CustomerID |
+----+---------------------+------------+
|  1 | 2016-08-28 00:00:00 |          2 |
|  3 | 2016-08-27 00:00:00 |          2 |
|  2 | 2016-08-26 00:00:00 |          2 |
+----+---------------------+------------+
3 rows in set (0.00 sec)

mysql 5.7 >
mysql 5.7 > DROP TEMPORARY TABLE IF EXISTS orders;
Query OK, 0 rows affected (0.00 sec)

Expected result:
+----+---------------------+------------+
| ID | CreationDate        | CustomerID |
+----+---------------------+------------+
|  1 | 2016-08-28 00:00:00 |          2 |
|  3 | 2016-08-27 00:00:00 |          2 |
|  2 | 2016-08-26 00:00:00 |          2 |
+----+---------------------+------------+
Real result:
+----+---------------------+------------+
| ID | CreationDate        | CustomerID |
+----+---------------------+------------+
|  1 | 2016-08-28 00:00:00 |          2 |
|  2 | 2016-08-26 00:00:00 |          2 |
|  3 | 2016-08-27 00:00:00 |          2 |
+----+---------------------+------------+

Thanks.

Thank you for the feedback. Please try latest released version. Thanks.

[miguel@tikal ~]$ mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET @orderBy = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @isDescending = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DROP TEMPORARY TABLE IF EXISTS orders;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TEMPORARY TABLE orders (ID BIGINT NOT NULL PRIMARY KEY, CreationDate DATETIME NOT NULL, CustomerID BIGINT NOT NULL);
INSERT INTO orders VALUES(1, '2016/08/28', 2);
INSERT INTO orders VALUES(2, '2016/08/26', 2);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO orders VALUES(1, '2016/08/28', 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders VALUES(2, '2016/08/26', 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders VALUES(3, '2016/08/27', 2);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM orders WHERE CustomerID = 2
    -> ORDER BY
    ->     CASE WHEN @orderBy = 0 AND @isDescending = 0 THEN
    ->         CreationDate
    ->     END ASC,
    ->     CASE WHEN @orderBy = 0 AND @isDescending = 1 THEN
    ->         CreationDate
    ->     END DESC;

DROP TEMPORARY TABLE IF EXISTS orders;+----+---------------------+------------+
| ID | CreationDate        | CustomerID |
+----+---------------------+------------+
|  1 | 2016-08-28 00:00:00 |          2 |
|  3 | 2016-08-27 00:00:00 |          2 |
|  2 | 2016-08-26 00:00:00 |          2 |
+----+---------------------+------------+
3 rows in set (0.03 sec)

mysql>
mysql> DROP TEMPORARY TABLE IF EXISTS orders;
Query OK, 0 rows affected (0.00 sec)

mysql>

As I mentioned in my first commentary, it seems to work in 5.7.14 version. However, I am not fully convinced thanks to fact that in version 5.7.12 was everything fine for quite a long time. It´s strange. But thank you. I hope it´s fixed.