Description:
Materializing the first two branches of a heterogeneous `UNION` in a CTE changes the final query result. The CTE rewrite preserves the same rows and set-operation structure, but a `TIME` value acquires the current date and becomes a different value.
The result of a query should not change merely because a reusable prefix of the `UNION` is represented by a CTE. This behavior is therefore considered a DBMS logic bug in temporal type resolution across a CTE materialization boundary.
The source branch types are:
| Branch | Expression | MySQL type |
|---|---|---|
| 1 | `TIME(...)` | `TIME` |
| 2 | `CAST(... AS DATETIME)` | `DATETIME` |
| 3 | Numeric expression | `DOUBLE` |
| 4 | `DATETIME` expression | `DATETIME` |
In the original flat `UNION`, MySQL resolves all branch types directly to `VARCHAR`. The `TIME` value is converted directly to a string:
```text
TIME -> VARCHAR
03:04:05
```
After CTE materialization, MySQL resolves the first two branches independently. Their common type is `DATETIME`, so MySQL first converts the `TIME` value to `DATETIME` and unexpectedly supplies the current date. The outer `UNION` subsequently converts that changed value to `VARCHAR`:
```text
TIME -> DATETIME -> VARCHAR
03:04:05 -> 2026-08-22 03:04:05
```
Both queries expose the same final `VARCHAR(22)` column. Nevertheless, MySQL returns different values because it applies incompatible temporal coercion paths depending on whether the common subexpression is written as a CTE.
Expected behavior: both query forms return the same row values. MySQL should apply consistent type resolution to the complete `UNION`, or otherwise avoid changing a `TIME` value by injecting the current date at the CTE boundary.
How to repeat:
Run the flat `UNION`:
```sql
SELECT 'a' AS k, TIME('2024-01-02 03:04:05') AS value
UNION
SELECT 'b', CAST('2024-02-03 04:05:06' AS DATETIME)
UNION
SELECT 'c', 12
UNION
SELECT 'd', CAST('2025-03-04 05:06:07' AS DATETIME);
```
The row from the first branch is:
```text
a | 03:04:05
```
Materialize the first two branches through a CTE:
```sql
WITH cut AS (
SELECT 'a' AS k, TIME('2024-01-02 03:04:05') AS value
UNION
SELECT 'b', CAST('2024-02-03 04:05:06' AS DATETIME)
)
SELECT k, value FROM cut
UNION
SELECT 'c', 12
UNION
SELECT 'd', CAST('2025-03-04 05:06:07' AS DATETIME);
```
The corresponding row becomes:
```text
a | 2026-08-22 03:04:05
```
The intermediate and final types can be inspected with temporary tables:
```sql
CREATE TEMPORARY TABLE original_result AS
SELECT 'a' AS k, TIME('2024-01-02 03:04:05') AS value
UNION
SELECT 'b', CAST('2024-02-03 04:05:06' AS DATETIME)
UNION
SELECT 'c', 12
UNION
SELECT 'd', CAST('2025-03-04 05:06:07' AS DATETIME);
CREATE TEMPORARY TABLE cut_result AS
SELECT 'a' AS k, TIME('2024-01-02 03:04:05') AS value
UNION
SELECT 'b', CAST('2024-02-03 04:05:06' AS DATETIME);
SHOW COLUMNS FROM original_result;
SHOW COLUMNS FROM cut_result;
```
Observed types:
```text
original_result.value: VARCHAR(22)
cut_result.value: DATETIME
```
For the original pair 365, four first-branch values changed:
```text
02:51:52 -> 2026-08-22 02:51:52
09:07:07 -> 2026-08-22 09:07:07
05:20:27 -> 2026-08-22 05:20:27
10:25:41 -> 2026-08-22 10:25:41
```
The two query forms have the same final output type but return different results, demonstrating inconsistent type inference and value conversion across the CTE boundary.
Description: Materializing the first two branches of a heterogeneous `UNION` in a CTE changes the final query result. The CTE rewrite preserves the same rows and set-operation structure, but a `TIME` value acquires the current date and becomes a different value. The result of a query should not change merely because a reusable prefix of the `UNION` is represented by a CTE. This behavior is therefore considered a DBMS logic bug in temporal type resolution across a CTE materialization boundary. The source branch types are: | Branch | Expression | MySQL type | |---|---|---| | 1 | `TIME(...)` | `TIME` | | 2 | `CAST(... AS DATETIME)` | `DATETIME` | | 3 | Numeric expression | `DOUBLE` | | 4 | `DATETIME` expression | `DATETIME` | In the original flat `UNION`, MySQL resolves all branch types directly to `VARCHAR`. The `TIME` value is converted directly to a string: ```text TIME -> VARCHAR 03:04:05 ``` After CTE materialization, MySQL resolves the first two branches independently. Their common type is `DATETIME`, so MySQL first converts the `TIME` value to `DATETIME` and unexpectedly supplies the current date. The outer `UNION` subsequently converts that changed value to `VARCHAR`: ```text TIME -> DATETIME -> VARCHAR 03:04:05 -> 2026-08-22 03:04:05 ``` Both queries expose the same final `VARCHAR(22)` column. Nevertheless, MySQL returns different values because it applies incompatible temporal coercion paths depending on whether the common subexpression is written as a CTE. Expected behavior: both query forms return the same row values. MySQL should apply consistent type resolution to the complete `UNION`, or otherwise avoid changing a `TIME` value by injecting the current date at the CTE boundary. How to repeat: Run the flat `UNION`: ```sql SELECT 'a' AS k, TIME('2024-01-02 03:04:05') AS value UNION SELECT 'b', CAST('2024-02-03 04:05:06' AS DATETIME) UNION SELECT 'c', 12 UNION SELECT 'd', CAST('2025-03-04 05:06:07' AS DATETIME); ``` The row from the first branch is: ```text a | 03:04:05 ``` Materialize the first two branches through a CTE: ```sql WITH cut AS ( SELECT 'a' AS k, TIME('2024-01-02 03:04:05') AS value UNION SELECT 'b', CAST('2024-02-03 04:05:06' AS DATETIME) ) SELECT k, value FROM cut UNION SELECT 'c', 12 UNION SELECT 'd', CAST('2025-03-04 05:06:07' AS DATETIME); ``` The corresponding row becomes: ```text a | 2026-08-22 03:04:05 ``` The intermediate and final types can be inspected with temporary tables: ```sql CREATE TEMPORARY TABLE original_result AS SELECT 'a' AS k, TIME('2024-01-02 03:04:05') AS value UNION SELECT 'b', CAST('2024-02-03 04:05:06' AS DATETIME) UNION SELECT 'c', 12 UNION SELECT 'd', CAST('2025-03-04 05:06:07' AS DATETIME); CREATE TEMPORARY TABLE cut_result AS SELECT 'a' AS k, TIME('2024-01-02 03:04:05') AS value UNION SELECT 'b', CAST('2024-02-03 04:05:06' AS DATETIME); SHOW COLUMNS FROM original_result; SHOW COLUMNS FROM cut_result; ``` Observed types: ```text original_result.value: VARCHAR(22) cut_result.value: DATETIME ``` For the original pair 365, four first-branch values changed: ```text 02:51:52 -> 2026-08-22 02:51:52 09:07:07 -> 2026-08-22 09:07:07 05:20:27 -> 2026-08-22 05:20:27 10:25:41 -> 2026-08-22 10:25:41 ``` The two query forms have the same final output type but return different results, demonstrating inconsistent type inference and value conversion across the CTE boundary.