Description:
MySQL returns different results when a derived table containing `TIME(c5)` is
materialized into a temporary table. The original query compares the
expression with two `DATETIME` string literals and returns no rows. The
materialized query compares the resulting `TIME` column with the same literals
and returns one row in the minimal case (ten rows in the original VECT case).
Materialization must preserve comparison semantics. The two forms use
different coercion paths:
```text
original: CAST(c5 AS TIME) BETWEEN '2000-01-01 00:00:00'
AND '2000-12-31 23:59:59'
materialized: value BETWEEN TIME '00:00:00' AND TIME '23:59:59'
```
The residual `TIME` column is compared as a temporal column instead of
retaining the expression-vs-string comparison used by the original query.
This is a MySQL temporal type-coercion/optimizer logic bug.
# Expected result
The original and materialized statements should return the same result:
```text
original_rows: 0
materialized_rows: 0
```
# Actual result
On MySQL 9.7.1, the self-contained reproducer returns:
```text
original_rows: 0
materialized_rows: 1
```
The preserved VECT `round6` case has the same coercion change and returns
0 versus 10 rows.
How to repeat:
The following script is self-contained. It creates the database and table,
inserts one row, and runs the original expression and a temporary-table
materialization side by side. The date range is fixed in 2000 so the result
does not depend on the current server date.
```sql
DROP DATABASE IF EXISTS mysql_time_materialization_repro;
CREATE DATABASE mysql_time_materialization_repro;
USE mysql_time_materialization_repro;
CREATE TABLE source (
id INT PRIMARY KEY,
c5 DATETIME NOT NULL
);
INSERT INTO source VALUES
(1, '2024-01-02 12:34:56');
-- Expression-vs-literal comparison.
SELECT COUNT(*) AS original_rows
FROM (
SELECT TIME(c5) AS value
FROM source
) AS expression_result
WHERE expression_result.value
BETWEEN '2000-01-01 00:00:00' AND '2000-12-31 23:59:59';
-- Materialization fixes the projected value as a TIME column.
CREATE TEMPORARY TABLE cut AS
SELECT TIME(c5) AS value
FROM source;
SELECT COUNT(*) AS materialized_rows
FROM cut
WHERE value BETWEEN '2000-01-01 00:00:00'
AND '2000-12-31 23:59:59';
```
Description: MySQL returns different results when a derived table containing `TIME(c5)` is materialized into a temporary table. The original query compares the expression with two `DATETIME` string literals and returns no rows. The materialized query compares the resulting `TIME` column with the same literals and returns one row in the minimal case (ten rows in the original VECT case). Materialization must preserve comparison semantics. The two forms use different coercion paths: ```text original: CAST(c5 AS TIME) BETWEEN '2000-01-01 00:00:00' AND '2000-12-31 23:59:59' materialized: value BETWEEN TIME '00:00:00' AND TIME '23:59:59' ``` The residual `TIME` column is compared as a temporal column instead of retaining the expression-vs-string comparison used by the original query. This is a MySQL temporal type-coercion/optimizer logic bug. # Expected result The original and materialized statements should return the same result: ```text original_rows: 0 materialized_rows: 0 ``` # Actual result On MySQL 9.7.1, the self-contained reproducer returns: ```text original_rows: 0 materialized_rows: 1 ``` The preserved VECT `round6` case has the same coercion change and returns 0 versus 10 rows. How to repeat: The following script is self-contained. It creates the database and table, inserts one row, and runs the original expression and a temporary-table materialization side by side. The date range is fixed in 2000 so the result does not depend on the current server date. ```sql DROP DATABASE IF EXISTS mysql_time_materialization_repro; CREATE DATABASE mysql_time_materialization_repro; USE mysql_time_materialization_repro; CREATE TABLE source ( id INT PRIMARY KEY, c5 DATETIME NOT NULL ); INSERT INTO source VALUES (1, '2024-01-02 12:34:56'); -- Expression-vs-literal comparison. SELECT COUNT(*) AS original_rows FROM ( SELECT TIME(c5) AS value FROM source ) AS expression_result WHERE expression_result.value BETWEEN '2000-01-01 00:00:00' AND '2000-12-31 23:59:59'; -- Materialization fixes the projected value as a TIME column. CREATE TEMPORARY TABLE cut AS SELECT TIME(c5) AS value FROM source; SELECT COUNT(*) AS materialized_rows FROM cut WHERE value BETWEEN '2000-01-01 00:00:00' AND '2000-12-31 23:59:59'; ```