Description:
When executing two syntactically equivalent statements involving date types, the query results are inconsistent.
How to repeat:
1.Import data.
create table t0(mediumint_col mediumint, date_col date);
insert into t0 values(NULL,'1998-09-02');
2.Execute queries and check the query plans.
mysql> SELECT 1 FROM t0 B WHERE B.date_col = '1998-09-02' AND B.date_col NOT BETWEEN '1992-03-04' AND B.mediumint_col;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '1998-09-02' |
+---------+------+-------------------------------------------------+
mysql> explain format=tree SELECT 1 FROM t0 B WHERE B.date_col = '1998-09-02' AND B.date_col NOT BETWEEN '1992-03-04' AND B.mediumint_col;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((B.date_col = DATE'1998-09-02') and ('1998-09-02' not between '1992-03-04' and B.mediumint_col)) (cost=0.35 rows=1)
-> Table scan on B (cost=0.35 rows=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#Replace the condition(B.date_col not between '1992-03-04' and B.mediumint_col) with ('1998-09-02' not between '1992-03-04' and B.mediumint_col).
mysql> SELECT 1 FROM t0 B WHERE B.date_col = '1998-09-02' AND '1998-09-02' NOT BETWEEN '1992-03-04' AND B.mediumint_col;
Empty set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1998-09-02' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1992-03-04' |
+---------+------+------------------------------------------------+
2 rows in set (0.00 sec)
mysql> explain format=tree SELECT 1 FROM t0 B WHERE B.date_col = '1998-09-02' AND '1998-09-02' NOT BETWEEN '1992-03-04' AND B.mediumint_col;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((B.date_col = DATE'1998-09-02') and ('1998-09-02' not between '1992-03-04' and B.mediumint_col)) (cost=0.35 rows=1)
-> Table scan on B (cost=0.35 rows=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Description: When executing two syntactically equivalent statements involving date types, the query results are inconsistent. How to repeat: 1.Import data. create table t0(mediumint_col mediumint, date_col date); insert into t0 values(NULL,'1998-09-02'); 2.Execute queries and check the query plans. mysql> SELECT 1 FROM t0 B WHERE B.date_col = '1998-09-02' AND B.date_col NOT BETWEEN '1992-03-04' AND B.mediumint_col; +---+ | 1 | +---+ | 1 | +---+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: '1998-09-02' | +---------+------+-------------------------------------------------+ mysql> explain format=tree SELECT 1 FROM t0 B WHERE B.date_col = '1998-09-02' AND B.date_col NOT BETWEEN '1992-03-04' AND B.mediumint_col; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: ((B.date_col = DATE'1998-09-02') and ('1998-09-02' not between '1992-03-04' and B.mediumint_col)) (cost=0.35 rows=1) -> Table scan on B (cost=0.35 rows=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) #Replace the condition(B.date_col not between '1992-03-04' and B.mediumint_col) with ('1998-09-02' not between '1992-03-04' and B.mediumint_col). mysql> SELECT 1 FROM t0 B WHERE B.date_col = '1998-09-02' AND '1998-09-02' NOT BETWEEN '1992-03-04' AND B.mediumint_col; Empty set, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '1998-09-02' | | Warning | 1292 | Truncated incorrect DOUBLE value: '1992-03-04' | +---------+------+------------------------------------------------+ 2 rows in set (0.00 sec) mysql> explain format=tree SELECT 1 FROM t0 B WHERE B.date_col = '1998-09-02' AND '1998-09-02' NOT BETWEEN '1992-03-04' AND B.mediumint_col; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: ((B.date_col = DATE'1998-09-02') and ('1998-09-02' not between '1992-03-04' and B.mediumint_col)) (cost=0.35 rows=1) -> Table scan on B (cost=0.35 rows=1) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)