pingcap / pingcap/tidb

TiDB resulting "field list is ambiguous" error when field is used in having exists

Open
#35,282 0 comments 0 reactions 1 assignee Claimed by @AilinKid View on GitHub
affects-6.1 affects-6.5 affects-7.1 affects-7.5 affects-8.1 severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
```sql
drop table if exists t1,t2;
CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (c int, d int);

INSERT INTO t1 VALUES
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
INSERT INTO t2 VALUES
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);

SELECT a FROM t1 GROUP BY a
HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
```

### 2. What did you expect to see? (Required)
```sql
mysql> SELECT a FROM t1 GROUP BY a
-> HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
+------+
| a |
+------+
| 1 |
| 3 |
| 4 |
+------+
3 rows in set (0.00 sec)
```
### 3. What did you see instead (Required)
```sql
mysql> SELECT a FROM t1 GROUP BY a
-> HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
ERROR 1052 (23000): Column 'a' in field list is ambiguous
```
### 4. What is your TiDB version? (Required)
```sql
mysql> select version();
+-----------------------------------------------+
| version() |
+-----------------------------------------------+
| 5.7.25-TiDB-v6.1.0-alpha-482-g81a852077-dirty |
+-----------------------------------------------+
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.