Bug #120261 CREATE VIEW materialization changes a key scalar output
Submitted: 14 Apr 2:52 Modified: 14 Apr 4:32
Reporter: Peiyuan Liu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.4.8 OS:Any
Assigned to: 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.