| Bug #96823 | MySQL produces different result for materialized subquery if index exists or not | ||
|---|---|---|---|
| Submitted: | 10 Sep 2019 14:42 | Modified: | 24 Oct 2019 22:39 |
| Reporter: | Domen Kermc | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.17 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 Sep 2019 14:48]
Domen Kermc
Correction: you should drop index `city` from `table_city_user`, not `table_city`.
[10 Sep 2019 15:39]
MySQL Verification Team
Thank you for the bug report. Please print here the real result and the expected one. Thanks.
[10 Sep 2019 17:59]
Domen Kermc
Expected result (both queries on 8.0.16 and query 2 on 8.0.17): id | users -------------------- 1 | 1,2,3,4,5,6,7,8 2 | 1,2,3,4,5 3 | 2,5 4 | 5,2,3,8,1 5 | NULL 6 | NULL Wrong result (query 1 on 8.0.17 when index `table_city_user`.`city` exists): id | users -------------------- 1 | 1,2,3,4,5,6,7,8 2 | NULL 3 | NULL 4 | NULL 5 | NULL 6 | NULL
[10 Sep 2019 18:27]
MySQL Verification Team
Thank you for the feedback. Repeatable with released version 8.0.17 but not anymore with most recent source, so it's was fixed somewhat;
Your MySQL connection id is 8
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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> USE TEST
Database changed
mysql> SELECT `id`, (
-> SELECT GROUP_CONCAT(`id`) FROM (
-> SELECT `table_user`.`id` FROM `table_user` WHERE `id` IN (SELECT `user` FROM `table_city_user` WHERE `table_city_user`.`city` = `table_city`.`id`) GROUP BY `table_user`.`id`
-> ) AS `unusedalias`
-> ) AS `users` FROM `table_city`;
+----+-----------------+
| id | users |
+----+-----------------+
| 1 | 1,2,3,4,5,6,7,8 |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+----+-----------------+
6 rows in set (0.02 sec)
mysql>
mysql> SELECT `id`, (
-> SELECT GROUP_CONCAT(`id`) FROM (
-> SELECT `table_user`.`id` FROM `table_user` INNER JOIN `table_city_user` ON `table_city_user`.`user` = `table_user`.`id` WHERE `table_city_user`.`city` = `table_city`.`id` GROUP BY `table_user`.`id`
-> ) AS `unusedalias`
-> ) AS `users` FROM `table_city`;
+----+-----------------+
| id | users |
+----+-----------------+
| 1 | 1,2,3,4,5,6,7,8 |
| 2 | 1,2,3,4,5 |
| 3 | 2,5 |
| 4 | 5,2,3,8,1 |
| 5 | NULL |
| 6 | NULL |
+----+-----------------+
6 rows in set (0.00 sec)
mysql>
---------------------------------------------------------------------------------------------------------------
d:\dbs>d:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18 Source distribution BUILT: 2019-AUG-26
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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 8.0 > use test
Database changed
mysql 8.0 > CREATE TABLE `table_city` (`id` int UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql 8.0 > CREATE TABLE `table_user` (`id` int UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql 8.0 > CREATE TABLE `table_city_user` (`city` int UNSIGNED NOT NULL, `user` int UNSIGNED NOT NULL, KEY `city` (`city`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql 8.0 >
mysql 8.0 > INSERT INTO `table_city` (`id`) VALUES (1),(2),(3),(4),(5),(6);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql 8.0 > INSERT INTO `table_user` (`id`) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql 8.0 > INSERT INTO `table_city_user` (`city`, `user`) VALUES (1,1),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(2,1),(2,2),(2,3),(2,4),(2,5),(3,2),(3,5),(4,5),(4,2),(4,3),(4,8),(4,1);
Query OK, 21 rows affected (0.01 sec)
Records: 21 Duplicates: 0 Warnings: 0
mysql 8.0 > SELECT `id`, (
-> SELECT GROUP_CONCAT(`id`) FROM (
-> SELECT `table_user`.`id` FROM `table_user` WHERE `id` IN (SELECT `user` FROM `table_city_user` WHERE `table_city_user`.`city` = `table_city`.`id`) GROUP BY `table_user`.`id`
-> ) AS `unusedalias`
-> ) AS `users` FROM `table_city`;
+----+-----------------+
| id | users |
+----+-----------------+
| 1 | 1,2,3,4,5,6,7,8 |
| 2 | 1,2,3,4,5 |
| 3 | 2,5 |
| 4 | 5,2,3,8,1 |
| 5 | NULL |
| 6 | NULL |
+----+-----------------+
6 rows in set (0.00 sec)
mysql 8.0 > SELECT `id`, (
-> SELECT GROUP_CONCAT(`id`) FROM (
-> SELECT `table_user`.`id` FROM `table_user` INNER JOIN `table_city_user` ON `table_city_user`.`user` = `table_user`.`id` WHERE `table_city_user`.`city` = `table_city`.`id` GROUP BY `table_user`.`id`
-> ) AS `unusedalias`
-> ) AS `users` FROM `table_city`;
+----+-----------------+
| id | users |
+----+-----------------+
| 1 | 1,2,3,4,5,6,7,8 |
| 2 | 1,2,3,4,5 |
| 3 | 2,5 |
| 4 | 5,2,3,8,1 |
| 5 | NULL |
| 6 | NULL |
+----+-----------------+
6 rows in set (0.00 sec)
mysql 8.0 >
[10 Sep 2019 18:37]
MySQL Verification Team
I verified again and then I get the not expected result, so verifyin it:
mysql 8.0 > show variables like "%version%";
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| immediate_server_version | 999999 |
| innodb_version | 8.0.18 |
| original_server_version | 999999 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version | 8.0.18 |
| version_comment | Source distribution BUILT: 2019-AUG-26 |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
| version_compile_zlib | 1.2.11 |
+--------------------------+----------------------------------------+
11 rows in set (0.00 sec)
mysql 8.0 > SELECT `id`, (
-> SELECT GROUP_CONCAT(`id`) FROM (
-> SELECT `table_user`.`id` FROM `table_user` WHERE `id` IN (SELECT `user` FROM `table_city_user` WHERE `table_city_user`.`city` = `table_city`.`id`) GROUP BY `table_user`.`id`
-> ) AS `unusedalias`
-> ) AS `users` FROM `table_city`;
+----+-----------------+
| id | users |
+----+-----------------+
| 1 | 1,2,3,4,5,6,7,8 |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
+----+-----------------+
6 rows in set (0.00 sec)
mysql 8.0 >
[20 Sep 2019 16:13]
Scott Pearson
For further information, I have a bug that behaves similarly to a SELECT statement via an INDEX. When the INDEX is not present, the SELECT statement retries one row as expected. When the INDEX is present, the same SELECT statement incorrectly returns 0 rows. I'm running MySQL on XAMPP 7.3.9-0. I'm happy to share more details if desired.
[24 Oct 2019 22:39]
Jon Stephens
Documented fix as follows in the MySQL 8.0.19 changelog:
A materialized subquery could yield different results depending
on whether it used an index.
Closed.
Description: In a scenario described below MySQL produces NULL for column `users` on non-first rows when it should not. Column in first row has the correct value. Issue disappears if you drop index that is used in subquery. How to repeat: First insert test data (below). Then execute query 1 and 2. When both queries are executed on MySQL 8.0.16, result is the same. But on 8.0.17 result of query 1 is changed: column `users` for first row has the correct value, but in other rows it's NULL. Now drop index `city` from `table_city`. Query 1 now returns correct result on 8.0.17. ------------- Test data: CREATE TABLE `table_city` (`id` int UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; CREATE TABLE `table_user` (`id` int UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; CREATE TABLE `table_city_user` (`city` int UNSIGNED NOT NULL, `user` int UNSIGNED NOT NULL, KEY `city` (`city`)) ENGINE=InnoDB; INSERT INTO `table_city` (`id`) VALUES (1),(2),(3),(4),(5),(6); INSERT INTO `table_user` (`id`) VALUES (1),(2),(3),(4),(5),(6),(7),(8); INSERT INTO `table_city_user` (`city`, `user`) VALUES (1,1),(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(2,1),(2,2),(2,3),(2,4),(2,5),(3,2),(3,5),(4,5),(4,2),(4,3),(4,8),(4,1); Query 1 (with subquery): SELECT `id`, ( SELECT GROUP_CONCAT(`id`) FROM ( SELECT `table_user`.`id` FROM `table_user` WHERE `id` IN (SELECT `user` FROM `table_city_user` WHERE `table_city_user`.`city` = `table_city`.`id`) GROUP BY `table_user`.`id` ) AS `unusedalias` ) AS `users` FROM `table_city`; Query 2 (with inner join): SELECT `id`, ( SELECT GROUP_CONCAT(`id`) FROM ( SELECT `table_user`.`id` FROM `table_user` INNER JOIN `table_city_user` ON `table_city_user`.`user` = `table_user`.`id` WHERE `table_city_user`.`city` = `table_city`.`id` GROUP BY `table_user`.`id` ) AS `unusedalias` ) AS `users` FROM `table_city`;