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
            View 27 older comments
            oleg.smirnov Oleg Smirnov added a comment -

            There are test failures looking like

            main.sargable_date_cond                  w3 [ fail ]
            		 
                    Test ended at 2023-03-16 04:35:43
            		 
             
            		 
            CURRENT_TEST: main.sargable_date_cond
            		 
            --- /usr/share/mysql/mysql-test/main/sargable_date_cond.result	2023-03-16 01:36:33.000000000 -0400
            		 
            +++ /dev/shm/var/3/log/sargable_date_cond.reject	2023-03-16 04:35:42.953159711 -0400
            		 
            @@ -1919,6 +1919,8 @@
            		 
             timestampadd(hour, B.a, date_add('2016-01-01', interval A.a*8 day)),
            		 
             date_add('2016-01-01', interval A.a*7 day)
            		 
             from t1 A, t0 B;
            		 
            +Warnings:
            		 
            +Warning	1299	Invalid TIMESTAMP value in column 'a' at row 11

            that occur only on some machines and not on others. Turns out it depends on whether the system time zone employs daylight saving time.

            If it does then calling timestampadd() can lead to execution of the following code

            my_time.cc

            		 
              /*
            		 
                Fix that if we are in the non existing daylight saving time hour
            		 
                we move the start of the next real hour.
            		 
             
            		 
                This code doesn't handle such exotical thing as time-gaps whose length
            		 
                is more than one hour or non-integer (latter can theoretically happen
            		 
                if one of seconds will be removed due leap correction, or because of
            		 
                general time correction like it happened for Africa/Monrovia time zone
            		 
                in year 1972).
            		 
              */
            		 
              if (loop == 2 && t->hour != (uint) l_time->tm_hour)
            		 
              {
            		 
                int days= t->day - l_time->tm_mday;
            		 
                if (days < -1)
            		 
                  days=1;					/* Month has wrapped */
            		 
                else if (days > 1)
            		 
                  days= -1;
            		 
                diff=(3600L*(long) (days*24+((int) t->hour - (int) l_time->tm_hour))+
            		 
            	  (long) (60*((int) t->minute - (int) l_time->tm_min)) +
            		 
                      (long) ((int) t->second - (int) l_time->tm_sec));
            		 
                if (diff == 3600)
            		 
                  tmp+=3600 - t->minute*60 - t->second;	/* Move to next hour */
            		 
                else if (diff == -3600)
            		 
                  tmp-=t->minute*60 + t->second;		/* Move to previous hour */
            		 
             
            		 
                *error_code= ER_WARN_INVALID_TIMESTAMP;
            		 
              }

            oleg.smirnov Oleg Smirnov added a comment - There are test failures looking like main.sargable_date_cond w3 [ fail ] Test ended at 2023-03-16 04:35:43   CURRENT_TEST: main.sargable_date_cond --- /usr/share/mysql/mysql-test/main/sargable_date_cond.result 2023-03-16 01:36:33.000000000 -0400 +++ /dev/shm/var/3/log/sargable_date_cond.reject 2023-03-16 04:35:42.953159711 -0400 @@ -1919,6 +1919,8 @@ timestampadd(hour, B.a, date_add('2016-01-01', interval A.a*8 day)), date_add('2016-01-01', interval A.a*7 day) from t1 A, t0 B; +Warnings: +Warning 1299 Invalid TIMESTAMP value in column 'a' at row 11 that occur only on some machines and not on others. Turns out it depends on whether the system time zone employs daylight saving time. If it does then calling timestampadd() can lead to execution of the following code my_time.cc /* Fix that if we are in the non existing daylight saving time hour we move the start of the next real hour.   This code doesn't handle such exotical thing as time-gaps whose length is more than one hour or non-integer (latter can theoretically happen if one of seconds will be removed due leap correction, or because of general time correction like it happened for Africa/Monrovia time zone in year 1972). */ if (loop == 2 && t->hour != (uint) l_time->tm_hour) { int days= t->day - l_time->tm_mday; if (days < -1) days=1; /* Month has wrapped */ else if (days > 1) days= -1; diff=(3600L*(long) (days*24+((int) t->hour - (int) l_time->tm_hour))+ (long) (60*((int) t->minute - (int) l_time->tm_min)) + (long) ((int) t->second - (int) l_time->tm_sec)); if (diff == 3600) tmp+=3600 - t->minute*60 - t->second; /* Move to next hour */ else if (diff == -3600) tmp-=t->minute*60 + t->second; /* Move to previous hour */   *error_code= ER_WARN_INVALID_TIMESTAMP; }
            psergei Sergei Petrunia added a comment -

            https://github.com/MariaDB/server/tree/preview-11.1-mdev-8320

            psergei Sergei Petrunia added a comment - https://github.com/MariaDB/server/tree/preview-11.1-mdev-8320
            lstartseva Lena Startseva added a comment -

            At the moment, the task has been tested with cases for the SELECT, a fix of MDEV-30946 is needed to continue testing

            lstartseva Lena Startseva added a comment - At the moment, the task has been tested with cases for the SELECT, a fix of MDEV-30946 is needed to continue testing
            lstartseva Lena Startseva added a comment -

            Testing done. Ok to push

            lstartseva Lena Startseva added a comment - Testing done. Ok to push
            oleg.smirnov Oleg Smirnov added a comment -

            Pushed to 11.1

            oleg.smirnov Oleg Smirnov added a comment - Pushed to 11.1

            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.