Bug #121012 subquery_to_derived bypasses only_full_group_by check

• Submitted: 27 Jul 9:55
• Modified: 27 Jul 14:15
• Reporter: Jacob Ding
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.46, 8.4.10, 9.7.1
• OS: MacOS
• CPU Architecture: ARM
• Tags: logical-bug

[27 Jul 9:55] Jacob Ding

Description:
```sql
CREATE TABLE t0 (id INT PRIMARY KEY, c0 INT, c1 DECIMAL(20,4), c2 VARCHAR(50));
CREATE TABLE t1 (id INT PRIMARY KEY, c0 INT, c1 DECIMAL(20,4), c2 VARCHAR(50));
INSERT INTO t0 VALUES (1,10,1,'a'),(2,20,2,'b'),(3,30,3,'c');
INSERT INTO t1 VALUES (1,10,100,'x'),(2,20,200,'y');

SET SESSION optimizer_switch = 'subquery_to_derived=off';
SELECT c0, (SELECT SUM(t1.c1) FROM t1 WHERE t1.c2 = t0.c2) AS x
FROM t0 GROUP BY c0;
-- ERROR 1055: Expression #2 contains nonaggregated column 't0.c2'

SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT c0, (SELECT SUM(t1.c1) FROM t1 WHERE t1.c2 = t0.c2) AS x
FROM t0 GROUP BY c0;
-- (10,NULL), (20,NULL), (30,NULL)
```

With subquery_to_derived=on, the only_full_group_by check is bypassed:
the query returns a result instead of error 1055.

How to repeat:
Tested on MySQL 8.4.10 and 9.7.1 (Homebrew, macOS arm64). The bug is
reproducible on both versions. Execute the above test case; the second
SELECT returns 3 rows instead of raising error 1055.

[27 Jul 14:15] Chaithra Marsur Gopala Reddy

Hi Jacob Ding,

Thank you for the test case. Verified as described.