only_full_group_by check is not compatible with MySQL

[hawkingrei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table

CREATE TABLE `defer_regress_profile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `profile1` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
  );

CREATE TABLE `defer_regress_request` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `profile_id` int(11) DEFAULT NULL,
  `location_id` int(11) NOT NULL,
  `request1` varchar(255) NOT NULL,
  `request2` varchar(255) NOT NULL,
  `request3` varchar(255) NOT NULL,
  `request4` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
  );

CREATE TABLE `defer_regress_request_items` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,
    `request_id` int(11) NOT NULL,
    `item_id` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `defer_regress_request_items_request_id_item_id_124dc438_uniq` (`request_id`,`item_id`)
    );

CREATE TABLE `defer_regress_location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location1` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) 
  );

run problem sql:

SELECT `defer_regress_request`.`id`,
       `defer_regress_request`.`profile_id`,
       `defer_regress_request`.`location_id`,
       COUNT(`defer_regress_request_items`.`item_id`) AS `items__count`,
       `defer_regress_profile`.`id`,
       `defer_regress_profile`.`profile1`,
       `defer_regress_location`.`id`,
       `defer_regress_location`.`location1`
FROM `defer_regress_request`
LEFT OUTER JOIN `defer_regress_request_items` ON (`defer_regress_request`.`id` = `defer_regress_request_items`.`request_id`)
LEFT OUTER JOIN `defer_regress_profile` ON (`defer_regress_request`.`profile_id` = `defer_regress_profile`.`id`)
INNER JOIN `defer_regress_location` ON (`defer_regress_request`.`location_id` = `defer_regress_location`.`id`)
GROUP BY `defer_regress_request`.`id`
ORDER BY NULL;

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

not error message in the mysql 8.0.25

3. What did you see instead (Required)

Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_django_tests.defer_regress_location.id' 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)

Release Version: v5.2.0-alpha-392-g71638ee46
Edition: Community
Git Commit Hash: 71638ee
Git Branch: master
UTC Build Time: 2021-07-26 10:23:54
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[morgo]

@hawkingrei can you provide SHOW CREATE TABLE defer_regress_location? Thanks.

[hawkingrei]

@hawkingrei can you provide SHOW CREATE TABLE defer_regress_location? Thanks.

Sorry, I have updated the issue.

[yudongusa]

@hawkingrei the error message shows "this is incompatible with sql_mode=only_full_group_by" which means sql_mode is set to use only_full_group_by that requires columns on select list to be included in group by clause. IMO, this is expected behavior, right?

[morgo]

@hawkingrei the error message shows "this is incompatible with sql_mode=only_full_group_by" which means sql_mode is set to use only_full_group_by that requires columns on select list to be included in group by clause. IMO, this is expected behavior, right?

The error is not returned by MySQL, but it is by TiDB. I can verify this as described:

mysql [localhost:8024] {root} (test) > SELECT `defer_regress_request`.`id`,
    ->        `defer_regress_request`.`profile_id`,
    ->        `defer_regress_request`.`location_id`,
    ->        COUNT(`defer_regress_request_items`.`item_id`) AS `items__count`,
    ->        `defer_regress_profile`.`id`,
    ->        `defer_regress_profile`.`profile1`,
    ->        `defer_regress_location`.`id`,
    ->        `defer_regress_location`.`location1`
    -> FROM `defer_regress_request`
    -> LEFT OUTER JOIN `defer_regress_request_items` ON (`defer_regress_request`.`id` = `defer_regress_request_items`.`request_id`)
    -> LEFT OUTER JOIN `defer_regress_profile` ON (`defer_regress_request`.`profile_id` = `defer_regress_profile`.`id`)
    -> INNER JOIN `defer_regress_location` ON (`defer_regress_request`.`location_id` = `defer_regress_location`.`id`)
    -> GROUP BY `defer_regress_request`.`id`
    -> ORDER BY NULL;
Empty set (0.01 sec)

tidb> SELECT `defer_regress_request`.`id`,
    ->        `defer_regress_request`.`profile_id`,
    ->        `defer_regress_request`.`location_id`,
    ->        COUNT(`defer_regress_request_items`.`item_id`) AS `items__count`,
    ->        `defer_regress_profile`.`id`,
    ->        `defer_regress_profile`.`profile1`,
    ->        `defer_regress_location`.`id`,
    ->        `defer_regress_location`.`location1`
    -> FROM `defer_regress_request`
    -> LEFT OUTER JOIN `defer_regress_request_items` ON (`defer_regress_request`.`id` = `defer_regress_request_items`.`request_id`)
    -> LEFT OUTER JOIN `defer_regress_profile` ON (`defer_regress_request`.`profile_id` = `defer_regress_profile`.`id`)
    -> INNER JOIN `defer_regress_location` ON (`defer_regress_request`.`location_id` = `defer_regress_location`.`id`)
    -> GROUP BY `defer_regress_request`.`id`
    -> ORDER BY NULL;
ERROR 1055 (42000): Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.defer_regress_profile.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[morgo]

I belive the issue is because the group by column is a primary key, and MySQL understands that expression 5 will not produce multiple rows, whereas TiDB does not.

MySQL's only-full-group-by check is actually quite sophisticated. I believe the property here is the "functional dependence" which is detailed in https://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-recognizing-functional-dependencies-enabled-by-default/

[yudongusa]

Thanks @morgo for the detailed explanation. Yes, similar to #25089 , we will need to continue improving functional dependency with thorough design to avoid any incorrect output issue.

[Alkaagr81]

Bug Report

In Window_function test file the following query is compatible with ONLY_FULL_GROUP_BY (on/off) in both case. In TIDB its only work with ONLY_FULL_GROUP_BY is OFF.

1. Minimal reproduce step (Required)

drop table IF EXISTS tj;
CREATE TABLE tj(j JSON, i INT DEFAULT 7);
INSERT INTO tj(j) VALUES (NULL);
INSERT INTO tj(j) VALUES ('3.14');
INSERT INTO tj(j) VALUES ('[1,2,3]');
 #Working in mysql with ONLY_FULL_GROUP_BY not in TIDB
SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT DISTINCT i,NTILE(3) OVER (ORDER BY i), SUM(i) OVER (), COUNT(*) OVER () FROM tj ORDER BY NTILE(3) OVER (ORDER BY i); 
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SELECT DISTINCT i,NTILE(3) OVER (ORDER BY i), SUM(i) OVER (), COUNT(*) OVER () FROM tj ORDER BY NTILE(3) OVER (ORDER BY i);

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

mysql> CREATE TABLE tj(j JSON, i INT DEFAULT 7);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tj(j) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tj(j) VALUES ('3.14');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tj(j) VALUES ('[1,2,3]');
Query OK, 1 row affected (0.00 sec)

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT i,NTILE(3) OVER (ORDER BY i), SUM(i) OVER (), COUNT(*) OVER () FROM tj ORDER BY NTILE(3) OVER (ORDER BY i);
+------+----------------------------+----------------+------------------+
| i    | NTILE(3) OVER (ORDER BY i) | SUM(i) OVER () | COUNT(*) OVER () |
+------+----------------------------+----------------+------------------+
|    7 |                          1 |             21 |                3 |
|    7 |                          2 |             21 |                3 |
|    7 |                          3 |             21 |                3 |
+------+----------------------------+----------------+------------------+
3 rows in set (0.00 sec)

mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT i,NTILE(3) OVER (ORDER BY i), SUM(i) OVER (), COUNT(*) OVER () FROM tj ORDER BY NTILE(3) OVER (ORDER BY i);
+------+----------------------------+----------------+------------------+
| i    | NTILE(3) OVER (ORDER BY i) | SUM(i) OVER () | COUNT(*) OVER () |
+------+----------------------------+----------------+------------------+
|    7 |                          1 |             21 |                3 |
|    7 |                          2 |             21 |                3 |
|    7 |                          3 |             21 |                3 |
+------+----------------------------+----------------+------------------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> drop table IF EXISTS tj;
Query OK, 0 rows affected (0.32 sec)

mysql> CREATE TABLE tj(j JSON, i INT DEFAULT 7);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO tj(j) VALUES (NULL);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO tj(j) VALUES ('3.14');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tj(j) VALUES ('[1,2,3]');
Query OK, 1 row affected (0.00 sec)

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT i,NTILE(3) OVER (ORDER BY i), SUM(i) OVER (), COUNT(*) OVER () FROM tj ORDER BY NTILE(3) OVER (ORDER BY i);
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column '' which is not in SELECT list; this is incompatible with DISTINCT
mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT i,NTILE(3) OVER (ORDER BY i), SUM(i) OVER (), COUNT(*) OVER () FROM tj ORDER BY NTILE(3) OVER (ORDER BY i);
+------+----------------------------+----------------+------------------+
| i    | NTILE(3) OVER (ORDER BY i) | SUM(i) OVER () | COUNT(*) OVER () |
+------+----------------------------+----------------+------------------+
|    7 |                          1 |             21 |                3 |
|    7 |                          2 |             21 |                3 |
|    7 |                          3 |             21 |                3 |
+------+----------------------------+----------------+------------------+
3 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.2.2
Edition: Community
Git Commit Hash: da1c21fd45a4ea5900ac16d2f4a248143f378d18
Git Branch: heads/refs/tags/v5.2.2
UTC Build Time: 2021-10-20 06:03:45
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[Alkaagr81]

create table t1(a int, b int, c int) engine=InnoDB;
create table t2(a int, b int, c int) engine=InnoDB;
insert into t2 values();insert into t1 values(200,1,1),(100,1,2),(400,2,2),(300,2,1);

 #Working in tidb with ONLY_FULL_GROUP_BY not in mysql 
SET sql_mode = 'ONLY_FULL_GROUP_BY';
select distinct min(b) from t1 group by a order by -min(b);   
 SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
select distinct min(b) from t1 group by a order by -min(b);   

In distinct_innodb test file the following queries is compatible with ONLY_FULL_GROUP_BY (on/off) in both case. In TIDB its only work with ONLY_FULL_GROUP_BY is OFF.

 #Working in mysql with ONLY_FULL_GROUP_BY not in tidb 

select distinct a, min(b) from t1 group by a order by max(b-2)-min(c*5);

 select distinct abs(b),b from t1 as S2 order by
 (select floor(10*S2.b) from t1 as S3 limit 1);

SELECT distinct 1 FROM t1 group by a order by any_value(count(*)-count(b));

SELECT distinct 1 FROM t1 group by a order by any_value(count(*))-any_value(count(b));

SELECT distinct a, min(b) FROM t1 group by a order by count(*)-count(*);

select * from t1 as t2 where t2.a in
  (SELECT distinct 1 FROM t1 group by a order by count(t2.a)-max(t2.a));

SELECT distinct sum(a) FROM t1 order by count(*)-count(*);

SELECT distinct sum(a) FROM t1 order by count(*)-count(b);

select
 (select distinct 1 from t1 t1_inner
  group by t1_inner.a order by max(t1_outer.b))
from t1 t1_outer;

[AilinKid]

winoros#7 In our latest feature branch, this case has been resolved.