| Bug #114378 | Incorrect query result caused by CONVERT_TZ | ||
|---|---|---|---|
| Submitted: | 17 Mar 2024 12:44 | Modified: | 18 Mar 2024 7:50 |
| Reporter: | Ye Shiyang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0, 8.0.35 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Mar 2024 7:49]
MySQL Verification Team
Hello Ye Shiyang, Thank you for the report and feedback regards, Umesh
[23 Apr 2024 16:37]
Dag Wanvik
Posted by developer:
I see the difference with the simplified repro:
CREATE TABLE t0(c0 FLOAT);
INSERT INTO t0(c0) VALUES(-1);
SELECT f1 FROM (SELECT ((t0.c0) ^ (CONVERT_TZ('2001-10-22 16:13:59.9', '-01:00','+00:00'))) AS f1 FROM t0) as t WHERE (f1) > (1);
18446724062687380255
SELECT f1 FROM (SELECT ((t0.c0) ^ (CONVERT_TZ('2001-10-22 16:13:59.9', '-01:00','+00:00'))) AS f1 FROM t0) as t;
18446724062687380215
The reason we get differents due to different kinds of rounding: in the first case, the rounding happens
indirectly: due to an intervening decimal conversion step, the rounding happens at the decimal level:
#0:Item_func_convert_tz::get_date at item_timefunc.cc:2444:10
#1:Item_datetime_func::val_date_temporal at item_timefunc.cc:943:10
#2:Item::val_temporal_by_field_type at item.h:1907:12
#3:Item_cache_datetime::cache_value_int at item.cc:9962:24
#4:Item_cache_datetime::val_decimal at item.cc:10043:26
#5:Item::val_int_from_decimal at item.cc:480:32
#6:Item_cache_datetime::val_int at item.cc:10134:50
#7:Item::val_uint at item.h:1935:51
#8:long long Item_func_bit_two_param::eval_int_op<std::__1::bit_xor<unsigned long long>>
using my_decimal2int called from val_int_from_decimal (frame #5) above. This leads to the
"2001-10-22 16:13:59".9 (it is already a decimal) being rounded up to "2001-10-22 16:13:60" essentially. Decimal rounding
doesn't understand that 13.59.0 rounds up to 14.00, rather it rounds to the integer equivalent of 13.60.
If there is now WHERE clause, the rounding happens when a datetime value is converted to int (no cached datetime here):
#0:Item_func_convert_tz::get_date at item_timefunc.cc:2444:10
#1:Item::val_int_from_datetime at item.cc:511:7
#2:Item_datetime_func::val_int at item_timefunc.h:680:40
#3:Item::val_uint at item.h:1935:51
#4:long long Item_func_bit_two_param::eval_int_op<std::__1::bit_xor<unsigned long long>>
using TIME_to_ulonglong_datetime_round called from val_int_from_datetime (frame #1). This leads to
2001-10-22 16:13:59.9 being rounded up to 2001-10-22 16:14:00.
This difference in conversion leads to the odd result.
Description: Dear devps, Our fuzzer may found a bug. Consider the following statements, qeury1 and query2 should reutrn the same reuslt. However, qeury1 return 18446724062687370255 while query2 return 18446724062687370215. ``` CREATE TABLE t0(c0 FLOAT); INSERT INTO t0(c0) VALUES(-1); -- query1 SELECT f1 FROM (SELECT ((t0.c0) ^ (CONVERT_TZ('2001-10-22 16:13:59.9', 'GMT','MET'))) AS f1 FROM t0) as t WHERE (f1) > (1); +----------------------+ | f1 | +----------------------+ | 18446724062687370255 | +----------------------+ 1 row in set (0.00 sec) -- query2 SELECT ALL f1 FROM (SELECT ((t0.c0) ^ (CONVERT_TZ('2001-10-22 16:13:59.9', 'GMT','MET'))) AS f1, (((t0.c0) ^ (CONVERT_TZ('2001-10-22 16:13:59.9', 'GMT','MET'))) > (1)) IS TRUE AS flag FROM t0) as t WHERE flag=1; +----------------------+ | f1 | +----------------------+ | 18446724062687370215 | +----------------------+ 1 row in set (0.00 sec) ``` How to repeat: CREATE TABLE t0(c0 FLOAT); INSERT INTO t0(c0) VALUES(-1); SELECT f1 FROM (SELECT ((t0.c0) ^ (CONVERT_TZ('2001-10-22 16:13:59.9', 'GMT','MET'))) AS f1 FROM t0) as t WHERE (f1) > (1); SELECT f1 FROM (SELECT ((t0.c0) ^ (CONVERT_TZ('2001-10-22 16:13:59.9', 'GMT','MET'))) AS f1, (((t0.c0) ^ (CONVERT_TZ('2001-10-22 16:13:59.9', 'GMT','MET'))) > (1)) IS TRUE AS flag FROM t0) as t WHERE flag=1;