Description:
For this sql:
mysql> SELECT (CASE WHEN 1 THEN TIME '0:0:0' WHEN 2 THEN TIMESTAMP '1970-1-1 0:0:0' END) < 1024;
+-----------------------------------------------------------------------------------+
| (CASE WHEN 1 THEN TIME '0:0:0' WHEN 2 THEN TIMESTAMP '1970-1-1 0:0:0' END) < 1024 |
+-----------------------------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
I think the result is wrong, because
mysql> SELECT (CASE WHEN 1 THEN TIME '0:0:0' WHEN 2 THEN TIMESTAMP '1970-1-1 0:0:0' END);
+----------------------------------------------------------------------------+
| (CASE WHEN 1 THEN TIME '0:0:0' WHEN 2 THEN TIMESTAMP '1970-1-1 0:0:0' END) |
+----------------------------------------------------------------------------+
| 2023-03-10 00:00:00 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
and "2023-03-10 00:00:00" is bigger than 1024.
Try a different SQL which should return the same result:
mysql> SELECT (CASE WHEN 1 THEN TIME '0:0:0' WHEN 2 THEN TIMESTAMP '1970-1-1 0:0:0' END) into @var;
Query OK, 1 row affected (0.00 sec)
mysql> select @var;
+---------------------+
| @var |
+---------------------+
| 2023-03-10 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> select @var < 1024;
+-------------+
| @var < 1024 |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2023-03-10 00:00:00' |
+---------+------+---------------------------------------------------------+
Although the result is truncated to 2023, but it still can show these two sql return a different result.
How to repeat:
See description.
Suggested fix:
I read the source code, I found args[0] in item_func_le is transformed to item_cache_datetime, but the args in item_func_le->cmp is not changed. That's the key point about the different result. Saving args into a user variable will use item_cache_datetime's value, and cmp object is using Item_func_case's value.
I think cmp and args in item_func_le should keep the same.
