Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2, 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 11.0(EOL), 11.3(EOL)
-
None
Description
A query with this condition:
WHERE timestamp_column=datetime_const_expr |
for example
WHERE timestamp_col = DATE_SUB('2023-09-01 17:17:15',INTERVAL 1 HOUR)
|
goes through an inefficient execution path. It involves a gmt_sec_to_TIME() call per every row, which in case of the @@time_zone=SYSTEM involves a slow localtime_r() call.
This happens because TIMESTAMP and DATETIME are compared as DATETIME historically.
This could be optimized to use TIMESTAMP comparison when it's possible to avoid TIMESTAMP->DATETIME per-row conversion. This should be faster for big tables.
What can be done
It's generally not possible to compare always TIMESTAMP and DATETIME as TIMESTAMP without behavior changes, because:
- DATETIME has a wider range.
- Two different TIMESTAMP values can have the same DATETIME value near the "fall back" DST change, as well as for leap seconds.
- There are DATETIME gaps during the "spring forward" DST switch, which are adjusted to the start of the gap.
However, if the DATETIME side is a constant inside monotone continuous periods (without DST changes and leap seconds), then we can compare it to TIMESTAMP as TIMESTAMP. The DATETIME argument can be converted once to TIMESTAMP, so no data type conversion will happen per row.
Attachments
Issue Links
- causes
-
MDEV-33299 Assertion `(tm->tv_usec % (int) log_10_int[6 - dec]) == 0' failed in void my_timestamp_to_binary(const timeval*, uchar*, uint)
- Closed
-
MDEV-34085 Server crash ASAN used-after-poison upon 2nd execution of PS with erroneous timestamp conversion
- Closed
- relates to
-
MDEV-14271 Dynamic SQL: TIMESTAMP parameter value is not preserved on timezone change
- Open
-
MDEV-16422 DST and unix_timestamp(now())
- Confirmed
-
MDEV-16423 DST and timestamp comparison
- Confirmed
-
MDEV-32113 utf8mb3_key_col=utf8mb4_value cannot be used for ref access
- Closed
-
MDEV-32152 Wrong results near DST for `WHERE indexed_timestamp_column='2010-10-31 02:30:00'`
- Open
-
MDEV-32203 Raise notes when an index cannot be used on data type mismatch
- Closed
-
MDEV-19121 optimizer fails to optimize query on timestamp column if where condition falls in the DST hour that do not exist
- Confirmed
-
MDEV-22828 Bad results near DST change: UNIX_TIMESTAMP(timestamp_column)=<integer literal>
- Open