Bug #121091 Wrapping a SET column in VIEW/CTE changes `HAVING MAX(set_col) > numeric_literal` semantics

• Submitted: 13 Aug 14:00
• Modified: 14 Aug 11:07
• Reporter: Peiyuan Liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.46, 8.4.11, 9.7.2
• OS: Any
• CPU Architecture: Any

[13 Aug 14:00] Peiyuan Liu

Description:
Materializing a plain `SET` column through a logical wrapper changes the result of a grouped `HAVING` predicate. 

`CREATE TABLE AS SELECT` and `CREATE TEMPORARY TABLE AS SELECT` both stay equal to the original query, while a `WITH ... AS (...)` CTE reproduces the same empty-result drift as the view case.

How to repeat:
CREATE TABLE t3 (
  c1 INT,
  c8 INT,
  c14 SET('x','y','z')
);

INSERT INTO t3 VALUES (1, 30, 'z');

SELECT c1
FROM t3
GROUP BY c1, c8
HAVING MAX(c14) > 3;

CREATE VIEW v AS
SELECT * FROM t3;

SELECT c1
FROM v
GROUP BY c1, c8
HAVING MAX(c14) > 3;

Original result:

+------+
| c1   |
+------+
|    1 |
+------+

After create view, result:

Empty set

[14 Aug 11:07] Chaithra Marsur Gopala Reddy

Hi Peiyuan Liu,

Thank you for the test case. Verified as described.