Why is MySQL ignoring the ORDER BY on this condition?

user_2183760 0
Viewed 111

I want to show rows that have updated_at more than 3 hours ago. MySQL seems to be completely ignoring the ORDER BY clause. Any idea why?

Edit: as pointed out by Sebastian, this only occurs in certain timezones, like GMT+5 or GMT+8.

mysql> SET time_zone='+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE test1; USE test1;
Query OK, 1 row affected (0.01 sec)
Database changed

mysql> CREATE TABLE `boxes` (
    -> `box_id` int unsigned NOT NULL AUTO_INCREMENT,
    -> `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (`box_id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `boxes` (`box_id`, `updated_at`) VALUES
    -> (1, '2020-08-22 05:25:35'),
    -> (2, '2020-08-26 18:49:05'),
    -> (3, '2020-08-23 03:28:30'),
    -> (4, '2020-08-23 03:32:55');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-08-26 20:49:59 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT b.box_id, updated_at, (b.updated_at < NOW() - INTERVAL 3 HOUR) AS more_than_3hr
    -> FROM boxes b
    -> ORDER BY more_than_3hr DESC;
+--------+---------------------+---------------+
| box_id | updated_at          | more_than_3hr |
+--------+---------------------+---------------+
|      1 | 2020-08-22 05:25:35 |             1 |
|      2 | 2020-08-26 18:49:05 |             0 | <--- WHY IS THIS HERE???
|      3 | 2020-08-23 03:28:30 |             1 |
|      4 | 2020-08-23 03:32:55 |             1 |
+--------+---------------------+---------------+
4 rows in set (0.00 sec)

Expectation: the rows with "1" should show up first.

Actual results: ORDER BY is ignored, and the resultset is sorted by primary key

I have a hunch it has something to do with MySQL storing timestamps as UTC and displaying them in the current timezone. My current timezone is GMT+8. However, it still doesn't make sense -- I am sorting the results based on the aliased expression, and the expression's value is clearly shown in the resultset.

MySQL version 8.0.21.

I also tried moving the expression to the ORDER BY clause, and the results are the same.

mysql
2 Answers

I don't know why but it compares wrong timezones in the background and thus values at the end are correct, but comparisons are invalid (for specific timezones).

When you query a TIMESTAMP value, MySQL converts the UTC value back to your connection’s time zone. Note that this conversion does not take place for other temporal data types such as DATETIME.

https://www.mysqltutorial.org/mysql-timestamp.aspx/

Changing type from TIMESTAMP to DATETIME fixes problem.

Other solution may be casting to the decimal number.

SELECT b.box_id, updated_at, FORMAT((b.updated_at < NOW() - INTERVAL 3 HOUR),0) AS more_than_3hr
FROM boxes b
ORDER BY more_than_3hr DESC;
user_70821640

From the documentation:

https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

HAVING, GROUP BY, and ORDER BY, when referring to a variable that is assigned a value in the select expression list do not work as expected because the expression is evaluated on the client and thus can use stale column values from a previous row.

Basically, you can't use a variable name you created with "AS" in your sorting.

The solution is to use the verbose statement you used for the AS in sorting. Yeah, it's verbose. ‍♂️ It is what it is.

user_25849030
Related