Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32148

Inefficient WHERE timestamp_column=datetime_const_expr



    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.11, 11.0, 11.1, 11.2, 11.3
    • 11.3.2, 11.4.1
    • Data types, Optimizer
    • None


      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.


        Issue Links



              bar Alexander Barkov
              bar Alexander Barkov
              1 Vote for this issue
              8 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.