pingcap / pingcap/tidb

some FD case failure in nested left join

Open
#34,556 0 comments 0 reactions 1 assignee Claimed by @AilinKid View on GitHub
severity/minor 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)
```
create table customer1(pk int primary key, a int);
create table customer2(pk int primary key, b int);

Query1:
select c1.a, count(*) from customer2 c3 left join (customer1 c1 left join customer2 c2 on c1.a=c2.b) on c3.b=c2.b group by c2.b;

alter table customer2 add column c int;

Query2:
select c1.a, c2.c, count(*) from customer2 c3 left join (customer1 c1 left join customer2 c2 on c1.a=c2.b and c2.c=1) on c3.b=c2.b group by c2.b;

Query3:
select c1.a, c2.c, count(*) from customer2 c3 left join (customer1 c1 left join customer2 c2 on c1.a=c2.b and c2.c is null) on c3.b=c2.b group by c2.b;
```

### 2. What did you expect to see? (Required)
Query 1&3 should be successful, Query 2 should fail.

### 3. What did you see instead (Required)
Both fail
```
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.c1.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
```

### 4. What is your TiDB version? (Required)

master

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.