[MDEV-15829] BETWEEN datetime not working with derived table and condition pushdown Created: 2018-04-09  Updated: 2020-08-25  Resolved: 2018-04-17

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.14, 10.3.5
Fix Version/s: 10.2.15, 10.3.6

Type: Bug Priority: Critical
Reporter: Chris Calender (Inactive) Assignee: Alice Sherepa
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-15765 BETWEEN not working in certain cases Closed

 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)



 Comments   
Comment by Chris Calender (Inactive) [ 2018-04-09 ]

For 10.3, this issue begins in 10.3.3.

Comment by Chris Calender (Inactive) [ 2018-04-09 ]

For 10.2, this issue begins in 10.2.14.

Comment by Alice Sherepa [ 2018-04-17 ]

Fixed by Igor Babaev, commit 740fc2ae084f8f81990de557d696

Generated at Thu Feb 08 08:24:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.