Bug #110332 Different result when using a user variable
Submitted: 10 Mar 2023 7:16 Modified: 10 Mar 2023 8:37
Reporter: Fengchun Hua Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.32, 5.7.41 OS:Any
Assigned to: CPU Architecture:Any
Tags: wrong result returned

[10 Mar 2023 7:16] Fengchun Hua
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.
[10 Mar 2023 8:37] MySQL Verification Team
Hello Fengchun Hua,

Thank you for the report and test case.

thanks,
Umesh