Bug #120261 CREATE VIEW materialization changes a key scalar output

• Submitted: 14 Apr 2:52
• Modified: 14 Apr 4:32
• Reporter: Peiyuan Liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.4.8
• OS: Any
• CPU Architecture: Any

[14 Apr 2:52] Peiyuan Liu

Description:
Materializing one scalar-subquery branch as a view preserves row count but changes a key scalar output value. The original first row is coerced to `127`, but after exposing the scalar-subquery branch through a view the transformed first row becomes `2001`. The transformed query remains structurally equivalent, but one result value drifts to a different numeric payload.

How to repeat:
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
USE t1;

CREATE TABLE t3 (
  c4 YEAR,
  c6 TINYINT
);

INSERT INTO t3 VALUES (2001, 10);

SELECT (SELECT c4 FROM t3 LIMIT 1) AS col_2
UNION ALL
SELECT c6 AS col_2
FROM t3;

CREATE VIEW V_t3_09 AS
SELECT (SELECT c4 FROM t3 LIMIT 1) AS col_2;

SELECT col_2
FROM V_t3_09
UNION ALL
SELECT c6 AS col_2
FROM t3;

Original result:
+-------+
| col_2 |
+-------+
| 127   |
| 10    |
+-------+

After create view, result:
+-------+
| col_2 |
+-------+
| 2001  |
| 10    |
+-------+

[14 Apr 4:32] Chaithra Marsur Gopala Reddy

Hi Peiyuan Liu,

Thank you for the test case. Verified as described.