Bug #120260 CREATE VIEW materialization changes byte-valued outputs after aggregate or window processing

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

[14 Apr 2:48] Peiyuan Liu

Description:
Materializing an aggregate-producing intermediate result as a view keeps the row count unchanged but changes the byte-valued output consumed by a later `GROUP_CONCAT`. The original outer `GROUP_CONCAT` produces ASCII-digit byte `0x33`, but after exposing the same grouped inner query through a view the outer `GROUP_CONCAT` produces raw byte `0x03` instead.

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

CREATE TABLE t2 (
  c12 BIT(8) NULL
);

INSERT INTO t2 VALUES (b'00000011');

SELECT GROUP_CONCAT(cte.col_4 ORDER BY cte.col_4) AS col_1
FROM (
  SELECT c12 AS col_4
  FROM t2
  GROUP BY c12
) AS cte;

CREATE VIEW V_cte_6140f8de AS
SELECT *
FROM (
  SELECT c12 AS col_4
  FROM t2
  GROUP BY c12
) AS cte;

SELECT GROUP_CONCAT(V_cte_6140f8de.col_4 ORDER BY V_cte_6140f8de.col_4) AS col_1
FROM V_cte_6140f8de;

Original result:
+-------+
| col_1 |
+-------+
| 0x33  |
+-------+

After create view, result:
+-------+
| col_1 |
+-------+
| 0x03  |
+-------+

[14 Apr 4:42] Chaithra Marsur Gopala Reddy

Hi Peiyuan Liu,

Thanks for the test case. Verified as described.