Bug #110915 Having clause missing scope

• Submitted: 4 May 2023 16:28
• Modified: 5 May 2023 15:16
• Reporter: Pedro Ferreira
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Any
• CPU Architecture: Any
• Tags: having clause

[4 May 2023 16:28] Pedro Ferreira

Description:
I am not sure if this is a bug, so please close if that's not the case.

Create these tables:

create table t0 (c0 int, c1 int, c2 int);
create table t7 (c0 int, c1 int, c2 int);

Then run this query:

SELECT 1 FROM t0 JOIN t7 on true GROUP BY t7.c0, t0.c0 HAVING t7.c0 = 1;
--ERROR 1054 (42S22): Unknown column 't7.c0' in 'having clause'

I don't understand the error here, because t7.c0 is a grouping column and HAVING should find it. Is this expected behavior?

How to repeat:
Run the queries above.

[5 May 2023 11:14] Pedro Ferreira

Another case:

create table t7(c1 int);
mysql> SELECT 1 FROM t7 tx JOIN t7 ty ON TRUE GROUP BY ty.c1, tx.c1 HAVING (SELECT 1 GROUP BY tx.c1);
ERROR 1054 (42S22): Unknown column 'tx.c1' in 'group statement'

[5 May 2023 12:39] MySQL Verification Team

Hi Mr. Ferreira,

Thank you for your bug report.

We repeated the behaviour and this is a bug, but very insignificant one ......

Verified.

[5 May 2023 12:59] Pedro Ferreira

Another error case:

mysql> SELECT x.x FROM (SELECT variance(1) FROM t0 GROUP BY t0.c0 WITH ROLLUP) x(x);
ERROR 1054 (42S22): Unknown column 'x.x' in 'field list'

If I remove "WITH ROLLUP" the query goes fine.

[5 May 2023 13:00] MySQL Verification Team

This shall be added to the internal bugs database too .....

[5 May 2023 15:16] Pedro Ferreira

One more case:

mysql> SELECT 1 FROM t0 GROUP BY t0.c1 HAVING EXISTS (SELECT t0.c1 x ORDER BY x);
ERROR 1054 (42S22): Unknown column 'x' in 'order clause'