Bug #110459 Derive_merge optimizer missing error

• Submitted: 22 Mar 2023 8:09
• Modified: 7 Jun 2023 8:51
• Reporter: Pedro Ferreira
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Ubuntu (22.04)
• CPU Architecture: x86 (x86_64)
• Tags: derive_merge

[22 Mar 2023 8:09] Pedro Ferreira

Description:
Create these tables:

CREATE TABLE t2 (c0 INT);
CREATE TABLE t4 (c0 INT);

Then run these queries:

SET @@SESSION.OPTIMIZER_SWITCH = 'derived_merge=on';
SELECT ty.c0 FROM t2 ty JOIN LATERAL (SELECT ty.c0 FROM t4) x(x) ON TRUE GROUP BY x.x; --no error
SET @@SESSION.OPTIMIZER_SWITCH = 'derived_merge=off';
SELECT ty.c0 FROM t2 ty JOIN LATERAL (SELECT ty.c0 FROM t4) x(x) ON TRUE GROUP BY x.x; --error

`ty.c0` is ungrouped, so the query is semantically incorrect. However, turning the `derived_merge` optimizer on seems to ignore this error. I would expect the error to be still thrown. If this is expected, please close this bug.

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.

[22 Mar 2023 8:12] Pedro Ferreira

Set the right category

[22 Mar 2023 11:29] MySQL Verification Team

Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh

[7 Jun 2023 8:51] Pedro Ferreira

Had a similar issue today with:

CREATE TABLE t0 (c1 INT);
CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1(c1,c2) VALUES (NULL,76),(131,120);
SET @@SESSION.OPTIMIZER_SWITCH = 'derived_merge=off';
SELECT 1 FROM t1 WHERE ROW(t1.c2, t1.c1) NOT IN (SELECT 3, x.x FROM (SELECT t0.c1 FROM t0 RIGHT JOIN t1 ON FALSE) x(x));
-- 2 rows
SET @@SESSION.OPTIMIZER_SWITCH = 'derived_merge=on';
SELECT 1 FROM t1 WHERE ROW(t1.c2, t1.c1) NOT IN (SELECT 3, x.x FROM (SELECT t0.c1 FROM t0 RIGHT JOIN t1 ON FALSE) x(x));
--no rows