Description:
When executing SQL queries that involve comparing a dynamically computed date using DATE_ADD with a static date column using the BETWEEN clause, the results are inconsistent.
How to repeat:
Create and populate the table t0:
CREATE TABLE t0 (c_0 DATETIME, c_1 INT, c_2 DATETIME);
INSERT INTO t0 VALUES ('6722-01-03 04:01:57.000000', '3525', '6731-08-29 04:01:57.000000');
SELECT c_2, (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) FROM t0;
| c_2 | (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) |
| ------------------- | --------------------------------------- |
| 6731-08-29 04:01:57 | 6731-08-29 04:01:57 |
Thus c_2 and (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) have the same value in t0.
Execute this SELECT:
SELECT t0.c_1 FROM t0 WHERE t0.c_2 BETWEEN -3.1881253607903353e+38 AND '2935-07-28 18:13:52.000000';
Output:
| c_1 |
| ---- |
| 3525 |
Change c_2 to (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) and get a new SELECT:
SELECT t0.c_1 FROM t0 WHERE (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) BETWEEN -3.1881253607903353e+38 AND '2935-07-28 18:13:52.000000';
Output:
| c_1 |
| ---- |
Expected Behavior:
The second SELECT query should return the same result as the first one since c_2 and (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) have the same value.
Description: When executing SQL queries that involve comparing a dynamically computed date using DATE_ADD with a static date column using the BETWEEN clause, the results are inconsistent. How to repeat: Create and populate the table t0: CREATE TABLE t0 (c_0 DATETIME, c_1 INT, c_2 DATETIME); INSERT INTO t0 VALUES ('6722-01-03 04:01:57.000000', '3525', '6731-08-29 04:01:57.000000'); SELECT c_2, (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) FROM t0; | c_2 | (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) | | ------------------- | --------------------------------------- | | 6731-08-29 04:01:57 | 6731-08-29 04:01:57 | Thus c_2 and (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) have the same value in t0. Execute this SELECT: SELECT t0.c_1 FROM t0 WHERE t0.c_2 BETWEEN -3.1881253607903353e+38 AND '2935-07-28 18:13:52.000000'; Output: | c_1 | | ---- | | 3525 | Change c_2 to (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) and get a new SELECT: SELECT t0.c_1 FROM t0 WHERE (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) BETWEEN -3.1881253607903353e+38 AND '2935-07-28 18:13:52.000000'; Output: | c_1 | | ---- | Expected Behavior: The second SELECT query should return the same result as the first one since c_2 and (DATE_ADD(t0.c_0, INTERVAL t0.c_1 DAY)) have the same value.