Bug #120543 MySQL `MERGE` vs `TEMPTABLE` view evaluation changes `MAX(SET)` result

• Submitted: 25 May 9:57
• Modified: 27 May 5:37
• Reporter: Peiyuan Liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.4.8
• OS: Any
• CPU Architecture: Any

[25 May 9:57] Peiyuan Liu

Description:
Two algorithm-specific views with the same definition are expected to preserve query semantics. On MySQL `8.4.8`, `MAX()` over a `SET` column returns different values depending on whether the view uses `ALGORITHM=MERGE` or `ALGORITHM=TEMPTABLE`.

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

CREATE TABLE t2 (
  c11 SET('a','b','c','d')
);

INSERT INTO t2 VALUES
  ('a,b,c,d'),
  ('d');

CREATE INDEX idx_c11 ON t2(c11);

CREATE OR REPLACE ALGORITHM=MERGE VIEW v_merge AS
SELECT c11
FROM t2;

CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v_temptable AS
SELECT c11
FROM t2;

SELECT MAX(c11) AS col_1 FROM v_merge;
SELECT MAX(c11) AS col_1 FROM v_temptable;

MERGE query:

| col_1     |
| --------- |
| a,b,c,d   |

TEMPTABLE query:

| col_1 |
| ----- |
| d     |

[27 May 5:37] Chaithra Marsur Gopala Reddy

Hi Peiyuan Liu,

Thank you for the test case. Verified as described.