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

Equal expression propagation does not work for temporal literals

Details

    Description

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a VARCHAR(20));
      		 
      INSERT INTO t1 VALUES ('a'),('b');
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)='a' AND COALESCE(a)>='a'; 
      SHOW WARNINGS;

      returns

      +-------+------+----------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                |
      		 
      +-------+------+----------------------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 'a') |
      		 
      +-------+------+----------------------------------------------------------------------------------------+

      I.e. the right AND part with COALESCE() was optimized away from the condition by equal expression propagation.

      If I do the same for DATE data type:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a DATE);
      		 
      INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-01' AND COALESCE(a)>=DATE'2001-01-01';
      		 
      SHOW WARNINGS;

      it returns:

      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                                                                                   |
      		 
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = DATE'2001-01-01') and (coalesce(`test`.`t1`.`a`) >= DATE'2001-01-01')) |
      		 
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+

      i.e. equal expression propagation was not applied.

      Attachments

        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.