Bug #121085 A VIEW whose column is an expression drops a group in query

• Submitted: 12 Aug 21:49
• Modified: 13 Aug 6:21
• Reporter: Junwen An
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.46,8.4.11, 9.7.2
• OS: Linux
• CPU Architecture: ARM

[12 Aug 21:49] Junwen An

Description:
Please see the repro

How to repeat:
CREATE TABLE t__base (c_pk BIGINT NOT NULL, c_int BIGINT);
INSERT INTO t__base VALUES (1, NULL);
INSERT INTO t__base VALUES (2, -7);

CREATE VIEW t AS SELECT c_pk+0 AS c_pk, c_int FROM t__base;

SELECT t.c_pk, COUNT(t.c_pk) FROM t GROUP BY t.c_pk HAVING (t.c_pk AND COUNT(t.c_pk)) ORDER BY t.c_pk;

-- expected: (1,1), (2,1) -- actual: (2,1)

The following results are expected:

(1) Materializing as a table instead of a view

CREATE TABLE t AS SELECT c_pk+0 AS c_pk, c_int FROM t__base;

SELECT t.c_pk, COUNT(t.c_pk) FROM t GROUP BY t.c_pk HAVING (t.c_pk AND COUNT(t.c_pk)) ORDER BY t.c_pk;

(2) Remove the expression in the view

CREATE VIEW t AS SELECT c_pk AS c_pk, c_int FROM t__base;

SELECT t.c_pk, COUNT(t.c_pk) FROM t GROUP BY t.c_pk HAVING (t.c_pk AND COUNT(t.c_pk)) ORDER BY t.c_pk;

[13 Aug 6:21] Chaithra Marsur Gopala Reddy

Hi Junwen An,

Thank you for the test case. Verified as described.