ERROR 1054 (42S22): Unknown column 't1_outer.b' in 'field list'

[Alkaagr81]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
SELECT 1 FROM t1 as t1_outer GROUP BY a  HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
SELECT 1 FROM t1 as t1_outer GROUP BY b HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);

2. What did you expect to see? (Required)

mysql> SELECT 1 FROM t1 as t1_outer GROUP BY a  HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
6 rows in set (0.00 sec)

mysql> SELECT 1 FROM t1 as t1_outer GROUP BY b HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT 1 FROM t1 as t1_outer GROUP BY a  HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
ERROR 1054 (42S22): Unknown column 't1_outer.a' in 'field list'
mysql> SELECT 1 FROM t1 as t1_outer GROUP BY b HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
ERROR 1054 (42S22): Unknown column 't1_outer.b' in 'field list'

4. What is your TiDB version? (Required)

| Release Version: v5.2.1
Edition: Community
Git Commit Hash: cd8fb24c5f7ebd9d479ed228bb41848bd5e97445
Git Branch: heads/refs/tags/v5.2.1
UTC Build Time: 2021-09-08 02:29:53
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[morgo]

Verified against master as described (I removed the set sql-mode - I can reproduce this with defaults):

tidb> SELECT 1 FROM t1 as t1_outer GROUP BY a  HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
ERROR 1054 (42S22): Unknown column 't1_outer.a' in 'field list'
tidb> SELECT 1 FROM t1 as t1_outer GROUP BY b HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
ERROR 1054 (42S22): Unknown column 't1_outer.b' in 'field list'
tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.3.0-alpha-1212-g5b5020d73
Edition: Community
Git Commit Hash: 5b5020d73de3a4d4adb707815543d1e325b8ec00
Git Branch: master
UTC Build Time: 2021-10-26 20:25:58
GoVersion: go1.16.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

Further, I can reproduce the correct result in both MySQL 5.7 and 8.0.

[winoros]

This one is the same as #29084.
closed for duplication.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.