pingcap / pingcap/tidb

CTE doesn't respect ONLY_FULL_GROUP_BY

Open
#43,395 1 comment 0 reactions 1 assignee Claimed by @winoros View on GitHub
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
MySQL [test]> create table t(a bigint, b bigint, c bigint);
Query OK, 0 rows affected (0.13 sec)

MySQL [test]> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [test]> with t_cte as (select * from t) select a,b,max(c) from t_cte group by b ;
Empty set (0.00 sec)
```

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

In MySQL:

```sql
mysql> with t_cte as (select * from t) select a,b,max(c) from t_cte group by b ;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't_cte.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
```

### 3. What did you see instead (Required)

Query succeeds.

### 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.