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

Inefficient WHERE timestamp_column=datetime_const_expr

    XMLWordPrintable

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)
    • 11.3.2, 11.4.1
    • Data types, Optimizer
    • 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

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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