Inefficient WHERE timestamp_column=datetime_const_expr

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.