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

Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.3.9
    • Optimizer
    • None

    Description

      I create and populate a table like this:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE OR REPLACE TABLE t1 (a TIME);
      		 
      INSERT INTO t1 VALUES ('00:00:10'),('00:00:20');

      Now I do some time arithmetic:

      SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1;


      +----------+---------------------------+---------------------------+
      		 
      | a        | SUBTIME(a,TIME'00:00:01') | ADDTIME(a,TIME'00:00:01') |
      		 
      +----------+---------------------------+---------------------------+
      		 
      | 00:00:10 | 00:00:09                  | 00:00:11                  |
      		 
      | 00:00:20 | 00:00:19                  | 00:00:21                  |
      		 
      +----------+---------------------------+---------------------------+

      Now I put the same arithmetic expressions into WHERE:

      SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09';


      +----------+
      		 
      | a        |
      		 
      +----------+
      		 
      | 00:00:10 |
      		 
      +----------+


      SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; 


      Empty set (0.01 sec)

      So far so good:

      • The first query correctly returned one row
      • The second query correctly returned empty set

      Now I mix these two conditions using AND and expect to get empty set again:

      SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; 


      +----------+
      		 
      | a        |
      		 
      +----------+
      		 
      | 00:00:10 |
      		 
      +----------+

      However, the server returned one row. This is wrong!

      The following EXPLAIN script demonstrates that the second condition part was erroneously eliminated by the optimizer:

      EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; 
      SHOW WARNINGS;


      ...
      		 
      +-------+------+---------------------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                                       |
      		 
      +-------+------+---------------------------------------------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' |
      		 
      +-------+------+---------------------------------------------------------------------------------------------------------------+

      Notice, there is no the ADDTIME() condition in the optimzed query.

      The problem happens because Item_func_add_time::func_name() erroneously always returns the same value, which makes the equal expression propagation code erroneously think ADDTIME() and SUBTIME() are equal to each other.

      Item_func_add_time::func_name() should return three different names depending on the exact SQL function it handles:

      • timestamp
      • addtime
      • subtime

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16398 Spider Creates Query With Non-Existent Function

          • Major - Major loss of function.
          • Closed

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.