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

BETWEEN datetime not working with derived table and condition pushdown

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Duplicate
    • Affects Version/s: 10.2.14, 10.3.5
    • Fix Version/s: 10.2.15, 10.3.6
    • Component/s: Optimizer
    • Labels:
      None

      Description

      A bug appears to exist when using a datetime BETWEEN on results of a derived table.

      The bug does not exist on the 10.1 (10.1.28 tested) branch, but it does in the more recent versions of 10.2 and 10.3.

      When using >=/<= instead of "between" the expected results are returned.

      This bug happens when there is a derived table, such as when a group by and/or an aggregate function such as MIN/MAX are used in the sub query.

      From the explain, it looks like with BETWEEN the condition is getting pushed down and becoming NULL, which is why no results are returned.

      EXPLAIN when using BETWEEN (Incorrect):

      explain format=json select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "<derived2>",
            "access_type": "ALL",
            "rows": 4,
            "filtered": 100,
            "attached_condition": "a.min_column4 between <cache>('2018-04-06 00:00:00') and <cache>('2018-04-09 23:59:59')",
            "materialized": {
              "query_block": {
                "select_id": 2,
                "having_condition": "min_column4 between <cache>(NULL) and <cache>(NULL)",
                "table": {
                  "table_name": "t1",
                  "access_type": "index",
                  "key": "PRIMARY",
                  "key_length": "41",
                  "used_key_parts": ["column1", "column2", "column3"],
                  "rows": 4,
                  "filtered": 100
                }
              }
            }
          }
        }
      }

      EXPLAIN when not using BETWEEN (Correct):

      explain format=json select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 >= '2018-04-06 00:00:00' and a.min_column4 <= '2018-04-09 23:59:59';
      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "<derived2>",
            "access_type": "ALL",
            "rows": 4,
            "filtered": 100,
            "attached_condition": "a.min_column4 >= '2018-04-06 00:00:00' and a.min_column4 <= '2018-04-09 23:59:59'",
            "materialized": {
              "query_block": {
                "select_id": 2,
                "having_condition": "min_column4 >= '2018-04-06 00:00:00' and min_column4 <= '2018-04-09 23:59:59'",
                "table": {
                  "table_name": "t1",
                  "access_type": "index",
                  "key": "PRIMARY",
                  "key_length": "41",
                  "used_key_parts": ["column1", "column2", "column3"],
                  "rows": 4,
                  "filtered": 100
                }
              }
            }
          }
        }
      }

      Test Case:

      CREATE TABLE t1(column1 varchar(32), column2 date, column3 int, column4 datetime, PRIMARY KEY(column1,column2,column3)) Engine=INNODB;
      INSERT INTO t1 VALUES('orlando', '2018-04-09', HOUR(CAST('2018-04-09 13:00:00' AS DATETIME)), '2018-04-09 13:15:00');
      INSERT INTO t1 VALUES('orlando', '2018-04-09', HOUR(CAST('2018-04-09 14:00:00' AS DATETIME)), '2018-04-09 14:23:00');
      INSERT INTO t1 VALUES('orlando', '2018-04-09', HOUR(CAST('2018-04-09 12:32:10' AS DATETIME)), '2018-04-09 12:31:10');
      INSERT INTO t1 VALUES('orlando', '2018-04-08', HOUR(CAST('2018-04-08 18:21:10' AS DATETIME)), '2018-04-08 18:21:10');
      select * from t1;
      select * from t1 WHERE column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3;
      select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 WHERE column4 >= '2018-04-09 16:00:00' - INTERVAL 24 HOUR  GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      select * from (select column1, column2, column3, (column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 >= '2018-04-06 00:00:00' and a.min_column4 <= '2018-04-09 23:59:59';
      

      Correct Results (10.1.28):

      mysql> select * from t1;
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | column4             |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.02 sec)
       
      mysql> select * from t1 WHERE column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | column4             |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.04 sec)
       
      mysql> select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3;
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | min_column4         |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.00 sec)
       
      mysql> select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | min_column4         |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.04 sec)
       
      mysql> select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 WHERE column4 >= '2018-04-09 16:00:00' - INTERVAL 24 HOUR  GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06
      nd '2018-04-09 23:59:59';
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | min_column4         |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.03 sec)
       
      mysql> select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | min_column4         |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      +---------+------------+---------+---------------------+
      1 row in set (0.00 sec)
       
      mysql> select * from (select column1, column2, column3, (column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | min_column4         |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.00 sec)
       
      mysql> select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 >= '2018-04-06 00:00:00' and a.min_column4 <= '2018-04-09 23:59:59';
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | min_column4         |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.00 sec)
      

      Incorrect Results (10.2.14 & 10.3.5):

      mysql> select * from t1;
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | column4             |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.04 sec)
       
      mysql> select * from t1 WHERE column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | column4             |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.10 sec)
       
      mysql> select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3;
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | min_column4         |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.05 sec)
       
      mysql> select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      Empty set (0.04 sec)
       
      mysql> select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 WHERE column4 >= '2018-04-09 16:00:00' - INTERVAL 24 HOUR  GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06 00:00:00' a
      nd '2018-04-09 23:59:59';
      Empty set (0.06 sec)
       
      mysql> select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      Empty set (0.00 sec)
       
      mysql> select * from (select column1, column2, column3, (column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 between '2018-04-06 00:00:00' and '2018-04-09 23:59:59';
      Empty set (0.02 sec)
       
      mysql> select * from (select column1, column2, column3, MIN(column4) as min_column4 FROM t1 GROUP BY column1, column2, column3) a WHERE a.min_column4 >= '2018-04-06 00:00:00' and a.min_column4 <= '2018-04-09 23:59:59';
      +---------+------------+---------+---------------------+
      | column1 | column2    | column3 | min_column4         |
      +---------+------------+---------+---------------------+
      | orlando | 2018-04-08 |      18 | 2018-04-08 18:21:10 |
      | orlando | 2018-04-09 |      12 | 2018-04-09 12:31:10 |
      | orlando | 2018-04-09 |      13 | 2018-04-09 13:15:00 |
      | orlando | 2018-04-09 |      14 | 2018-04-09 14:23:00 |
      +---------+------------+---------+---------------------+
      4 rows in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              alice Alice Sherepa
              Reporter:
              ccalender Chris Calender
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: