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: | |
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 |
[27 Aug 2016 7:00]
X X
[27 Aug 2016 17:53]
MySQL Verification Team
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)
[27 Aug 2016 19:51]
X X
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.
[27 Aug 2016 20:03]
MySQL Verification Team
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>
[27 Aug 2016 20:30]
X X
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.