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

Allow index usage for DATE(datetime_column) = const

Details

    • 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-4, 10.2.3-1, 10.2.3-2

    Description

      Would it be possible to make DATE() on datetime column sargable in some cases?
      Like rewrite "DATE(col) = const" to "col BETWEEN concat(const, ' 00:00:00') AND concat(const, ' 23:59:59')"

      Other similar cases:

      • "YEAR(col) = const" is almost the same
      • "YEAR(col) = c1 AND MONTH(col) = c2" and other such combinations may be too complex to be worth it.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30901 Index usage for DATE(datetime_column) = const does not work for engine Memory

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30913 Index usage for DATE(datetime_column) = const does not work for engine Connect

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30946 Index usage for DATE(datetime_column) = const does not work for DELETE and UPDATE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33299 Assertion `(tm->tv_usec % (int) log_10_int[6 - dec]) == 0' failed in void my_timestamp_to_binary(const timeval*, uchar*, uint)

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is duplicated by

          Task - A task that needs to be done. MDEV-8937 Optimize date functions in the parser

          • Major - Major loss of function.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-30930 Make more date conditions sargable

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-14635 Convert date operations on indexed column to sargable equivalents where possible

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            jkavalik Jiri Kavalik created issue -
            jkavalik Jiri Kavalik made changes -
            Field Original Value New Value
            Description Would it be possible to make DATE() on datetime column sargable in some cases?
            Like rewrite "DATE(col) = const" to "col BETWEEN concat(const, ' 00:00:00' AND concat(const, ' 23:59:59'"

            Other similar cases:
            - "YEAR(col) = const" is almost the same
            - "YEAR(col) = c1 AND MONTH(col) = c2" and other such combinations may be too complex to be worth it.             		Would it be possible to make DATE() on datetime column sargable in some cases?
            Like rewrite "DATE(col) = const" to "col BETWEEN concat(const, ' 00:00:00' AND concat(const, ' 23:59:59')"

            Other similar cases:
            - "YEAR(col) = const" is almost the same
            - "YEAR(col) = c1 AND MONTH(col) = c2" and other such combinations may be too complex to be worth it.
            jkavalik Jiri Kavalik made changes -
            Description Would it be possible to make DATE() on datetime column sargable in some cases?
            Like rewrite "DATE(col) = const" to "col BETWEEN concat(const, ' 00:00:00' AND concat(const, ' 23:59:59')"

            Other similar cases:
            - "YEAR(col) = const" is almost the same
            - "YEAR(col) = c1 AND MONTH(col) = c2" and other such combinations may be too complex to be worth it.             		Would it be possible to make DATE() on datetime column sargable in some cases?
            Like rewrite "DATE(col) = const" to "col BETWEEN concat(const, ' 00:00:00') AND concat(const, ' 23:59:59')"

            Other similar cases:
            - "YEAR(col) = const" is almost the same
            - "YEAR(col) = c1 AND MONTH(col) = c2" and other such combinations may be too complex to be worth it.
            serg Sergei Golubchik made changes -
            Summary FR: allow index usage for DATE(datetime_column) = const Allow index usage for DATE(datetime_column) = const
            michaeldg Michaël de groot made changes -
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Fix Version/s 10.2 [ 14601 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Assignee Sergei Petrunia [ psergey ] Alexey Botchkov [ holyfoot ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.2-2 [ 82 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked lower
            holyfoot Alexey Botchkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.2-2 [ 82 ] 10.2.2-2, 10.2.2-3 [ 82, 83 ]
            holyfoot Alexey Botchkov made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            holyfoot Alexey Botchkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.2-2, 10.2.2-3 [ 82, 83 ] 10.2.2-2, 10.2.2-3, 10.2.2-1 [ 82, 83, 89 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked higher
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.2-2, 10.2.2-3, 10.2.2-1 [ 82, 83, 89 ] 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2 [ 82, 83, 89, 92 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked lower
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2 [ 82, 83, 89, 92 ] 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3 [ 82, 83, 89, 92, 94 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3 [ 82, 83, 89, 92, 94 ] 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-4 [ 82, 83, 89, 92, 94, 96 ]
            holyfoot Alexey Botchkov made changes -
            Assignee Alexey Botchkov [ holyfoot ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-4 [ 82, 83, 89, 92, 94, 96 ] 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-4, 10.2.3-1 [ 82, 83, 89, 92, 94, 96, 102 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked lower
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-4, 10.2.3-1 [ 82, 83, 89, 92, 94, 96, 102 ] 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-4, 10.2.3-1, 10.2.3-2 [ 82, 83, 89, 92, 94, 96, 102, 105 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.2 [ 14601 ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Petrunia [ psergey ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 70012 ] MariaDB v4 [ 131651 ]
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Sergei Petrunia [ psergey ] Oleg Smirnov [ JIRAUSER50405 ]
            oleg.smirnov Oleg Smirnov made changes -
            Fix Version/s 11.1 [ 28549 ]
            oleg.smirnov Oleg Smirnov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Oleg Smirnov [ JIRAUSER50405 ] Sergei Petrunia [ psergey ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Oleg Smirnov [ JIRAUSER50405 ]
            psergei Sergei Petrunia made changes -
            Status Stalled [ 10000 ] In Testing [ 10301 ]
            psergei Sergei Petrunia made changes -
            Assignee Oleg Smirnov [ JIRAUSER50405 ] Elena Stepanova [ elenst ]
            serg Sergei Golubchik made changes -
            Assignee Elena Stepanova [ elenst ] Lena Startseva [ JIRAUSER50478 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels Preview_11.1
            lstartseva Lena Startseva made changes -
            lstartseva Lena Startseva made changes -
            oleg.smirnov Oleg Smirnov made changes -
            lstartseva Lena Startseva made changes -
            lstartseva Lena Startseva made changes -
            Assignee Lena Startseva [ JIRAUSER50478 ] Oleg Smirnov [ JIRAUSER50405 ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Blocker [ 1 ]
            oleg.smirnov Oleg Smirnov made changes -
            Fix Version/s 11.1.1 [ 28704 ]
            Fix Version/s 11.1 [ 28549 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            bar Alexander Barkov made changes -

            People

              oleg.smirnov Oleg Smirnov
              jkavalik Jiri Kavalik
              Votes:
              5 Vote for this issue
              Watchers:
              12 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.