Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
10.2.14, 10.3.5
-
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
- duplicates
-
-
- Closed
-