Bug #108548 | GROUP_CONCAT NOT SET USED_TABLES WHEN DERIVED_MERGE. | ||
---|---|---|---|
Submitted: | 20 Sep 2022 6:37 | Modified: | 20 Sep 2022 7:35 |
Reporter: | casa zhang (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.7, 5.7.39 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | transformation |
[20 Sep 2022 6:37]
casa zhang
[20 Sep 2022 6:42]
casa zhang
GROUP_CONCAT NOT SET USED_TABLES WHEN DERIVED_MERGE.
Attachment: 0001-bugfix-GROUP_CONCAT-NOT-SET-USED_TABLES-WHEN-DERIVED.patch (application/octet-stream, text), 5.79 KiB.
[20 Sep 2022 6:44]
casa zhang
we found that only GROUP_CONCAT() function cause the wrong result, so add fix_after_pullout() for Item_func_group_concat item, hope that will be helpful.
[20 Sep 2022 7:04]
MySQL Verification Team
Hello casa zhang, Thank you for the report and Contribution. In which version are you seeing this issue? I quickly tried and confirmed that this issue is no longer reproducible on current GA. - 5.7.39 bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.39 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> CREATE TABLE `t1` (`c1` bigint(20), `c2` varchar(30), `c3` varchar(30), `c4` varchar(30), PRIMARY KEY (`c1`)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1 VALUES(1, 'one', 'category', 'ab'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t1 VALUES(2, 'two', 'category', 'cc'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1 VALUES(3, 'three', 'category', 'aa'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1 VALUES(4, 'four', 'category', 'bb'); Query OK, 1 row affected (0.00 sec) mysql> mysql> CREATE TABLE `t2` ( `c1` varchar(10), `c2` varchar(80), PRIMARY KEY (`c1`)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t2 VALUES('fine', 'aa'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t2 VALUES('thanks', 'bb'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SET @@optimizer_switch="derived_merge=on"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))); +---+-------+------+--------+------+ | a | b | c | d | e | +---+-------+------+--------+------+ | 3 | three | aa | fine | aa | | 4 | four | bb | thanks | bb | +---+-------+------+--------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM -> ( -> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))) -> ) AS D; +---+------+------+--------+------+ | a | b | c | d | e | +---+------+------+--------+------+ | 4 | four | bb | fine | aa | | 4 | four | bb | thanks | bb | +---+------+------+--------+------+ 2 rows in set (0.00 sec) mysql> SET SESSION OPTIMIZER_SWITCH="derived_merge=off"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))); +---+-------+------+--------+------+ | a | b | c | d | e | +---+-------+------+--------+------+ | 3 | three | aa | fine | aa | | 4 | four | bb | thanks | bb | +---+-------+------+--------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM -> ( -> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))) -> ) AS D; +---+-------+------+--------+------+ | a | b | c | d | e | +---+-------+------+--------+------+ | 3 | three | aa | fine | aa | | 4 | four | bb | thanks | bb | +---+-------+------+--------+------+ 2 rows in set (0.00 sec) - 8.0.30 bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.30 MySQL Community Server - GPL Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test Database changed mysql> SET @@optimizer_switch="derived_merge=on"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))); +---+-------+------+--------+------+ | a | b | c | d | e | +---+-------+------+--------+------+ | 3 | three | aa | fine | aa | | 4 | four | bb | thanks | bb | +---+-------+------+--------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM -> ( -> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))) -> ) AS D; +---+-------+------+--------+------+ | a | b | c | d | e | +---+-------+------+--------+------+ | 3 | three | aa | fine | aa | | 4 | four | bb | thanks | bb | +---+-------+------+--------+------+ 2 rows in set (0.00 sec) mysql> SET SESSION OPTIMIZER_SWITCH="derived_merge=off"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))); +---+-------+------+--------+------+ | a | b | c | d | e | +---+-------+------+--------+------+ | 3 | three | aa | fine | aa | | 4 | four | bb | thanks | bb | +---+-------+------+--------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM -> ( -> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))) -> ) AS D; +---+-------+------+--------+------+ | a | b | c | d | e | +---+-------+------+--------+------+ | 3 | three | aa | fine | aa | | 4 | four | bb | thanks | bb | +---+-------+------+--------+------+ 2 rows in set (0.00 sec) regards, Umesh
[20 Sep 2022 7:28]
casa zhang
you repeat it, the result is not stable, but both are wrong. mysql> SET @@optimizer_switch="derived_merge=on"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))); +---+-------+------+--------+------+ | a | b | c | d | e | +---+-------+------+--------+------+ | 3 | three | aa | fine | aa | | 4 | four | bb | thanks | bb | +---+-------+------+--------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM -> ( -> SELECT t1.c1 AS a, t1.c2 AS b, t1.c4 AS c, t2.c1 AS d, t2.c2 AS e -> FROM t1, t2 -> WHERE t1.c3 = 'category' -> AND find_in_set(t1.c4, (SELECT group_concat(t2.c2))) -> ) AS D; +---+------+------+--------+------+ | a | b | c | d | e | +---+------+------+--------+------+ | 4 | four | bb | fine | aa | | 4 | four | bb | thanks | bb | +---+------+------+--------+------+ 2 rows in set (0.00 sec) The second SQL give the different result. +---+-------+------+--------+------+ | a | b | c | d | e | +---+-------+------+--------+------+ | 3 | three | aa | fine | aa | | 4 | four | bb | thanks | bb | +---+-------+------+--------+------+ is not same as +---+------+------+--------+------+ | a | b | c | d | e | +---+------+------+--------+------+ | 4 | four | bb | fine | aa | | 4 | four | bb | thanks | bb | +---+------+------+--------+------+
[20 Sep 2022 7:33]
MySQL Verification Team
Agree, thank you for the feedback.
[20 Sep 2022 7:35]
casa zhang
The problem will appear on 5.7 not 8.0.