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

FR: allow index usage for DATE(datetime_column) = const

        Details

        • Type: Task
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Fix Version/s: None
        • Component/s: Optimizer
        • Labels:
          None

          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.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                7 older comments
                Hide
                Permalink
                jkavalik Jiri Kavalik added a comment -

                Thank you all for hints. I tried to implement this in https://github.com/jkavalik/server/commit/2a66fa9bde198bca78625210f632112586c818e7

                No idea if it is the right way to do such a thing but it seems to work as I imagined. I did not yet manage to run complete test suite (had to put my laptop to sleep and it then crashed on timeout..) but I ran some (innodb, select, explain, type_date, type_datetime directly and then some 1200 from the entire suite before the sleep) without troubles.

                Show
                jkavalik Jiri Kavalik added a comment - Thank you all for hints. I tried to implement this in https://github.com/jkavalik/server/commit/2a66fa9bde198bca78625210f632112586c818e7 No idea if it is the right way to do such a thing but it seems to work as I imagined. I did not yet manage to run complete test suite (had to put my laptop to sleep and it then crashed on timeout..) but I ran some (innodb, select, explain, type_date, type_datetime directly and then some 1200 from the entire suite before the sleep) without troubles.
                Hide
                Permalink
                psergey Sergei Petrunia added a comment -

                Commented on the patch in github. Summary: found a number of issues which need to be resolved before this can be pushed.

                Show
                psergey Sergei Petrunia added a comment - Commented on the patch in github. Summary: found a number of issues which need to be resolved before this can be pushed.
                Hide
                Permalink
                jkavalik Jiri Kavalik added a comment -

                Thank you for the comments. I will try to work on those issues.

                Show
                jkavalik Jiri Kavalik added a comment - Thank you for the comments. I will try to work on those issues.
                Hide
                Permalink
                danblack Daniel Black added a comment -

                Minor addition, when these optimisations occur it would be good to see an addition text in the 'Extra' of the explain output like '

                {funct}

                (colref) optimised to range;'

                Show
                danblack Daniel Black added a comment - Minor addition, when these optimisations occur it would be good to see an addition text in the 'Extra' of the explain output like ' {funct} (colref) optimised to range;'
                Hide
                Permalink
                psergey Sergei Petrunia added a comment -

                Saw a customer case with conditions like this:

                DATE(table.datetime_col) BETWEEN '2015-08-12' AND '2015-08-12'

DATE(table.date_col) BETWEEN '2015-08-12' AND '2015-08-12'

                The columns have types DATE and DATETIME.

                Show
                psergey Sergei Petrunia added a comment - Saw a customer case with conditions like this: DATE(table.datetime_col) BETWEEN '2015-08-12' AND '2015-08-12' DATE(table.date_col) BETWEEN '2015-08-12' AND '2015-08-12' The columns have types DATE and DATETIME .

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    jkavalik Jiri Kavalik
                  • Votes:
                    1 Vote for this issue
                    Watchers:
                    4 Start watching this issue

                    Dates

                    • Created:
                      Updated: