Description:
When a `TIMEDIFF(...)` expression is compared directly with date/datetime string
literals, MySQL evaluates the predicate in a temporal expression context. The
same expression first materialized into a temporary table is inferred as a
`TIME` column. A residual predicate then compares that `TIME` column with the
same date/datetime strings, causing MySQL to convert the strings to `TIME`.
The conversion is surprising:
```sql
CAST('2023-01-01' AS TIME) = '00:20:23'
```
As a result, the following logically identical predicates return different
values:
- direct expression: `1`
- materialized `TIME` column: `0`
This was observed in VECT Query 286. The affected projection is:
```sql
TIMEDIFF(t2.c5, t2.c5)
NOT BETWEEN '2023-01-01 00:00:00'
AND '2023-12-31 23:59:59'
```
The original query returns `1`, while cuts whose boundary materializes the
`TIMEDIFF` expression return `0`. The mismatch is reproducible for the
`derived_table`, `computed_expression` (two candidates), and
`atomic_predicate` cuts, proving that it is caused by the shared materialization
boundary rather than one candidate implementation.
This is an undesirable DBMS conversion behavior. VECT also needs to preserve
the original expression type/context or emit an explicit conversion so that
materialization does not change query semantics.
- Database: MySQL `9.7.1`
- Character set/collation: `utf8mb4_0900_as_cs`
How to repeat:
Run the following statements in one MySQL session:
```sql
SELECT
TIMEDIFF('2025-01-01', '2025-01-01') AS value,
TIMEDIFF('2025-01-01', '2025-01-01')
NOT BETWEEN '2023-01-01 00:00:00'
AND '2023-12-31 23:59:59' AS direct_result;
```
Observed result:
```text
value = 00:00:00
direct_result = 1
```
Then materialize the expression:
```sql
DROP TEMPORARY TABLE IF EXISTS timediff_case;
CREATE TEMPORARY TABLE timediff_case AS
SELECT TIMEDIFF('2025-01-01', '2025-01-01') AS value;
SHOW CREATE TABLE timediff_case;
SELECT
value,
value NOT BETWEEN '2023-01-01 00:00:00'
AND '2023-12-31 23:59:59' AS materialized_result,
CAST(value AS CHAR) AS value_as_text,
CAST('2023-01-01' AS TIME) AS lower_bound_as_time;
```
Observed results:
```text
value = 00:00:00
materialized_result = 0
value_as_text = 00:00:00
lower_bound_as_time = 00:20:23
```
`SHOW CREATE TABLE` reports the materialized column as:
```sql
`value` time DEFAULT NULL
```
The VECT reproduction is Query 286 in the round1 seed. Its affected cut
materializes `TIMEDIFF(t2.c5, t2.c5)` and evaluates the residual
`NOT BETWEEN` predicate against the temporary table column.
Description: When a `TIMEDIFF(...)` expression is compared directly with date/datetime string literals, MySQL evaluates the predicate in a temporal expression context. The same expression first materialized into a temporary table is inferred as a `TIME` column. A residual predicate then compares that `TIME` column with the same date/datetime strings, causing MySQL to convert the strings to `TIME`. The conversion is surprising: ```sql CAST('2023-01-01' AS TIME) = '00:20:23' ``` As a result, the following logically identical predicates return different values: - direct expression: `1` - materialized `TIME` column: `0` This was observed in VECT Query 286. The affected projection is: ```sql TIMEDIFF(t2.c5, t2.c5) NOT BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' ``` The original query returns `1`, while cuts whose boundary materializes the `TIMEDIFF` expression return `0`. The mismatch is reproducible for the `derived_table`, `computed_expression` (two candidates), and `atomic_predicate` cuts, proving that it is caused by the shared materialization boundary rather than one candidate implementation. This is an undesirable DBMS conversion behavior. VECT also needs to preserve the original expression type/context or emit an explicit conversion so that materialization does not change query semantics. - Database: MySQL `9.7.1` - Character set/collation: `utf8mb4_0900_as_cs` How to repeat: Run the following statements in one MySQL session: ```sql SELECT TIMEDIFF('2025-01-01', '2025-01-01') AS value, TIMEDIFF('2025-01-01', '2025-01-01') NOT BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AS direct_result; ``` Observed result: ```text value = 00:00:00 direct_result = 1 ``` Then materialize the expression: ```sql DROP TEMPORARY TABLE IF EXISTS timediff_case; CREATE TEMPORARY TABLE timediff_case AS SELECT TIMEDIFF('2025-01-01', '2025-01-01') AS value; SHOW CREATE TABLE timediff_case; SELECT value, value NOT BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AS materialized_result, CAST(value AS CHAR) AS value_as_text, CAST('2023-01-01' AS TIME) AS lower_bound_as_time; ``` Observed results: ```text value = 00:00:00 materialized_result = 0 value_as_text = 00:00:00 lower_bound_as_time = 00:20:23 ``` `SHOW CREATE TABLE` reports the materialized column as: ```sql `value` time DEFAULT NULL ``` The VECT reproduction is Query 286 in the round1 seed. Its affected cut materializes `TIMEDIFF(t2.c5, t2.c5)` and evaluates the residual `NOT BETWEEN` predicate against the temporary table column.