| Bug #92809 | Inconsistent ResultSet for different Execution Plans | ||
|---|---|---|---|
| Submitted: | 16 Oct 2018 21:31 | Modified: | 14 May 2019 12:53 |
| Reporter: | Juan Arruti | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.23, 5.7.24 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[16 Oct 2018 21:31]
Juan Arruti
[16 Oct 2018 21:33]
Juan Arruti
Test case
Attachment: test_case_incorrect_rows.txt (text/plain), 25.85 KiB.
[16 Oct 2018 21:38]
MySQL Verification Team
Thank you for the bug report. This bugdb is for MySQL server you are using:Server version: 5.7.23-23 Percona Server (GPL), Release 23, Revision 500fcf5. Please report the bug at Percona. Thanks.
[16 Oct 2018 22:09]
Juan Arruti
Hi Miguel, thanks for you feedback. Please review the complete test case, it also shows MySQL Community is affected. I've uploaded the dump data to reproduce this issue via the sftp. Many thanks!
[16 Oct 2018 22:44]
MySQL Verification Team
Thank you for the feedback. If the zipped dump file is < 3MB please attach better here using the Files tab (private if you wish) otherwise please provide the file name.
[16 Oct 2018 22:50]
Juan Arruti
Hi Miguel, the file name is mysql-bug-data-92809.zip, I had to uploaded to the sftp due the size of the dump data, compressed it's 42 MB :) Regards,
[17 Oct 2018 11:54]
MySQL Verification Team
Hi, Please, try to repeat the results on the MySQL server, not on Percona server and let us know how it went ...
[17 Oct 2018 11:56]
MySQL Verification Team
Hi, Please also do explain what do you mean by getting different result sets for different plans ??? Execution plans do depend on the contents of the table and the precise query definition. If you change a single row, or a single condition or expression it is expected behaviour to get different result sets. This does not apply to cases where execution is changed by forcing the index, provided that you are using our server.
[17 Oct 2018 16:20]
Juan Arruti
Hi Sinisa, The first query execution of the test case shows that 3488 rows are returned, as follows: c6176573c3fcbb08631ab7021429e6ae - 3488 rows in set (0.02 sec) The second query, which is identical to the first execution where it was only forced a different index to modify the execution plan and produce this case, it returns just 218 rows, as follows: 92ac8ecc163c231f0864f1f574dd8e8e - 218 rows in set (0.03 sec) Regardless of the execution plan (if materialization feature is used or not, if an index is forced or not, etc) the same query should return the same rows, which is not the case on this issue. Regards,
[18 Oct 2018 12:32]
MySQL Verification Team
Hi, Please, try to answer all of our questions, which you did not. First of all, try to repeat it on the MySQL server, not Percona server and let us know when you manage to do that. Second, if you repeat the bug on our server, we need a repeatable test case. Repeatable test case presumes the dump of all tables involved, as well. You have not provided that either !!!!
[18 Oct 2018 13:07]
MySQL Verification Team
Hi, I have been searching for your file mysql-bug-data-92809.zip on our SFTP and it is not in the directory /support/incoming/ ....... Hence, where exactly did you upload it to ????
[18 Oct 2018 16:12]
Juan Arruti
Hi Sinisa, I've tried again to upload the file with the dump data to sftp.oracle.com and it failed with "already exists" error, as follows: Comando: put "/Users/jarruti/Downloads/mysql-bug-data-92809.zip" "mysql-bug-data-92809.zip" Error: //support/incoming/mysql-bug-data-92809.zip: open for write: received failure with description 'File //support/incoming/mysql-bug-data-92809.zip already exists, Please specify a different filename' Anyway I've uploaded the file again as mysql-bug-data-92809-2.zip. Can you check if you can see it? Regarding the MySQL version, as mentioned earlier the test case shows at the bottom this issue is also reproducible in MySQL Community 5.7.23, as follows: Server version: 5.7.23 MySQL Community Server (GPL) Regards,
[19 Oct 2018 12:37]
MySQL Verification Team
HI, We have currently problems with our SFTP server, so you will have to wait a bit ......
[19 Oct 2018 15:41]
MySQL Verification Team
Hi, Our server 5.7.23 has no problems what so ever. Yes, with FORCE INDEX execution plan is changed , but that is expected behaviour. Number of rows returned is always the same , which is 218. I both counted the rows with SELECT * and got the number with SELECT COUNT(*). Always the same rows and always the same number of those: SELECT count(*) FROM t1 INNER JOIN t2 ON t2.c1 = t1.c1 INNER JOIN t3 ON t3.c1 = t2.c1 AND t3.c2 = t2.c2 INNER JOIN t4 ON t4.c2 = t3.c1 AND t4.c3 = t3.c2 AND t4.c4 = t3.c3 INNER JOIN t5 ON t5.c1 = t4.c1 INNER JOIN t6 ON t6.c1 = t4.c5 INNER JOIN t7 ON t7.c2 = t4.c1 INNER JOIN t8 ON t8.c2 = t7.c1 WHERE t1.c1 IN (SELECT DISTINCT c3 FROM t9 WHERE t9.c1 = 13900180 AND t9.c3 = Ifnull(NULL, t9.c3)) AND t4.c1 IN (SELECT c3 FROM t10 WHERE c2 = 13900180); +----------+ | count(*) | +----------+ | 218 | +----------+ SELECT count(*) FROM t1 INNER JOIN t2 ON t2.c1 = t1.c1 INNER JOIN t3 ON t3.c1 = t2.c1 AND t3.c2 = t2.c2 INNER JOIN t4 force index (key2) ON t4.c2 = t3.c1 AND t4.c3 = t3.c2 AND t4.c4 = t3.c3 INNER JOIN t5 ON t5.c1 = t4.c1 INNER JOIN t6 ON t6.c1 = t4.c5 INNER JOIN t7 ON t7.c2 = t4.c1 INNER JOIN t8 ON t8.c2 = t7.c1 WHERE t1.c1 IN (SELECT DISTINCT c3 FROM t9 WHERE t9.c1 = 13900180 AND t9.c3 = Ifnull(NULL, t9.c3)) AND t4.c1 IN (SELECT c3 FROM t10 WHERE c2 = 13900180); +----------+ | count(*) | +----------+ | 218 | +----------+ Not a bug !!!!
[19 Oct 2018 15:41]
MySQL Verification Team
These are the EXPLAINs: EXPLAIN SELECT count(*) FROM t1 INNER JOIN t2 ON t2.c1 = t1.c1 INNER JOIN t3 ON t3.c1 = t2.c1 AND t3.c2 = t2.c2 INNER JOIN t4 ON t4.c2 = t3.c1 AND t4.c3 = t3.c2 AND t4.c4 = t3.c3 INNER JOIN t5 ON t5.c1 = t4.c1 INNER JOIN t6 ON t6.c1 = t4.c5 INNER JOIN t7 ON t7.c2 = t4.c1 INNER JOIN t8 ON t8.c2 = t7.c1 WHERE t1.c1 IN (SELECT DISTINCT c3 FROM t9 WHERE t9.c1 = 13900180 AND t9.c3 = Ifnull(NULL, t9.c3)) AND t4.c1 IN (SELECT c3 FROM t10 WHERE c2 = 13900180); +----+-------------+-------+------------+--------+-------------------+---------+---------+----------------------------------------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+-------------------+---------+---------+----------------------------------------------+------+----------+------------------------------+ | 1 | SIMPLE | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where; Start temporary | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | bug92809.t9.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | Using where | | 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY,key1,key2 | PRIMARY | 5 | bug92809.t10.c3 | 1 | 5.00 | Using where; End temporary | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bug92809.t9.c3,bug92809.t4.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | eq_ref | PRIMARY | PRIMARY | 6 | bug92809.t9.c3,bug92809.t4.c3,bug92809.t4.c4 | 1 | 100.00 | Using index | | 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | bug92809.t10.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | bug92809.t4.c5 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | bug92809.t10.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | bug92809.t7.c1 | 13 | 100.00 | Using index | +----+-------------+-------+------------+--------+-------------------+---------+---------+----------------------------------------------+------+----------+------------------------------+ EXPLAIN SELECT count(*) FROM t1 INNER JOIN t2 ON t2.c1 = t1.c1 INNER JOIN t3 ON t3.c1 = t2.c1 AND t3.c2 = t2.c2 INNER JOIN t4 force index (key2) ON t4.c2 = t3.c1 AND t4.c3 = t3.c2 AND t4.c4 = t3.c3 INNER JOIN t5 ON t5.c1 = t4.c1 INNER JOIN t6 ON t6.c1 = t4.c5 INNER JOIN t7 ON t7.c2 = t4.c1 INNER JOIN t8 ON t8.c2 = t7.c1 WHERE t1.c1 IN (SELECT DISTINCT c3 FROM t9 WHERE t9.c1 = 13900180 AND t9.c3 = Ifnull(NULL, t9.c3)) AND t4.c1 IN (SELECT c3 FROM t10 WHERE c2 = 13900180); +----+--------------+-------------+------------+--------+---------------+---------+---------+-------------------------------------------------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------+---------+---------+-------------------------------------------------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | <subquery2>.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | <subquery3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | <subquery3>.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | ref | PRIMARY | PRIMARY | 2 | <subquery2>.c3 | 68 | 100.00 | Using index | | 1 | SIMPLE | t4 | NULL | ref | key2 | key2 | 14 | <subquery2>.c3,bug92809.t3.c2,bug92809.t3.c3,<subquery3>.c3 | 1 | 100.00 | Using where | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | <subquery2>.c3,bug92809.t3.c2 | 1 | 100.00 | Using index | | 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | bug92809.t4.c5 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | <subquery3>.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | bug92809.t7.c1 | 13 | 100.00 | Using index | | 2 | MATERIALIZED | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where | | 3 | MATERIALIZED | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | NULL | +----+--------------+-------------+------------+--------+---------------+---------+---------+-------------------------------------------------------------+------+----------+----------------------------------------------------+ Not a Bug !!!!!
[19 Oct 2018 16:14]
Juan Arruti
Hi Sinisa, Thanks for testing this issue. As I mentioned in the test case you may need to execute "analyze table" command a couple of times to produce the same execution plan where table t9 is accessed first and table t10 is materialized and is the last to be accessed, as follows: mysql> analyze table t1, t2, t3, t4, t5, t6, t7, t8, t9, t10 ; +----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+---------+----------+----------+ | test.t1 | analyze | status | OK | | test.t2 | analyze | status | OK | | test.t3 | analyze | status | OK | | test.t4 | analyze | status | OK | | test.t5 | analyze | status | OK | | test.t6 | analyze | status | OK | | test.t7 | analyze | status | OK | | test.t8 | analyze | status | OK | | test.t9 | analyze | status | OK | | test.t10 | analyze | status | OK | +----------+---------+----------+----------+ 10 rows in set (0.05 sec) +----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where | | 1 | SIMPLE | <subquery3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | test.t9.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY,key1,key2 | PRIMARY | 5 | <subquery3>.c3 | 1 | 5.00 | Using where | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t9.c3,test.t4.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | eq_ref | PRIMARY | PRIMARY | 6 | test.t9.c3,test.t4.c3,test.t4.c4 | 1 | 100.00 | Using index | | 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | test.t4.c5 | 1 | 100.00 | Using index | | 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | <subquery3>.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | <subquery3>.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | test.t7.c1 | 13 | 100.00 | NULL | | 3 | MATERIALIZED | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | NULL | +----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) Above execution plan should return 3488 rows for the same query instead of 218 rows. Regards,
[22 Oct 2018 11:29]
MySQL Verification Team
Hi, I have run ANALYZE and the query 10 times consecutively and the number of rows is same all the time.
[22 Oct 2018 12:39]
Juan Arruti
Hi Sinisa, Did you manage to get the same explain that I post in my previous reply? Regards,
[22 Oct 2018 12:52]
MySQL Verification Team
No, I was never able to get that EXPLAIN output.
[22 Oct 2018 13:02]
MySQL Verification Team
Hi, I have now run 10 times the same batch of commands. 5 (five) ANALYZE commands of all tables, query without FORCE INDEX and query with FORCE INDEX. All queries returned 218 rows. I used our own build of 5.7.23 server. I think I did enough. Not a bug.
[22 Oct 2018 13:08]
Sveta Smirnova
Sinisa, try following: 1. cd mysql-test 2. ./mtr --start innodb & 3. ../bin/mysql -uroot -h127.0.0.1 -P13000 test < dump.sql 4. Connect to mysql and run the first query - Get 3488 rows Run analyze table - Get the correct number of rows with the same query.
[22 Oct 2018 13:41]
Sveta Smirnova
Sinisa,
try also these two queries:
SELECT *
FROM t1
STRAIGHT_JOIN t4
STRAIGHT_JOIN t2
ON t2.c1 = t1.c1
STRAIGHT_JOIN t3
ON t3.c1 = t2.c1
AND t3.c2 = t2.c2
AND t4.c2 = t3.c1
AND t4.c3 = t3.c2
AND t4.c4 = t3.c3
STRAIGHT_JOIN t6
ON t6.c1 = t4.c5
STRAIGHT_JOIN t5
ON t5.c1 = t4.c1
STRAIGHT_JOIN t7
ON t7.c2 = t4.c1
STRAIGHT_JOIN t8
ON t8.c2 = t7.c1
WHERE t1.c1 IN (SELECT DISTINCT c3
FROM t9
WHERE t9.c1 = 13900180
AND t9.c3 = Ifnull(NULL, t9.c3))
AND t4.c1 IN (SELECT c3
FROM t10
WHERE c2 = 13900180);
and
SELECT *
FROM t1
STRAIGHT_JOIN t4 force index(key2)
STRAIGHT_JOIN t2
ON t2.c1 = t1.c1
STRAIGHT_JOIN t3
ON t3.c1 = t2.c1
AND t3.c2 = t2.c2
AND t4.c2 = t3.c1
AND t4.c3 = t3.c2
AND t4.c4 = t3.c3
STRAIGHT_JOIN t6
ON t6.c1 = t4.c5
STRAIGHT_JOIN t5
ON t5.c1 = t4.c1
STRAIGHT_JOIN t7
ON t7.c2 = t4.c1
STRAIGHT_JOIN t8
ON t8.c2 = t7.c1
WHERE t1.c1 IN (SELECT DISTINCT c3
FROM t9
WHERE t9.c1 = 13900180
AND t9.c3 = Ifnull(NULL, t9.c3))
AND t4.c1 IN (SELECT c3
FROM t10
WHERE c2 = 13900180);
First returns 3488 rows and second 218
[22 Oct 2018 13:46]
MySQL Verification Team
Hi Sveta, I was told by the reporter to execute ANALYZE TABLE first for a couple of times and then the problem will pop up. However, if I have to run the queries immediately after the load of the dump, then I will have to do it all over again. Due to our load, I do not think I will find time for that during this week. If your last queries can be run on the tables that I have now, then I can do it during this week. Otherwise, you will have to wait .......
[22 Oct 2018 13:48]
Sveta Smirnova
Sinisa, just check queries in my last comment. They will show the issue consistently after ANALYZE TABLE.
[22 Oct 2018 13:52]
Sveta Smirnova
Workaround: set optimizer_switch='materialization=off';
[23 Oct 2018 12:49]
MySQL Verification Team
No luck .... Both queries return 218 rows ...... COUNT(*) returns that and I counted rows with SELECT * .... , by piping the output to a text file and counting lines. Not a bug.
[23 Oct 2018 12:58]
Sveta Smirnova
What is the output of
EXPLAIN
SELECT *
FROM t1
STRAIGHT_JOIN t4
STRAIGHT_JOIN t2
ON t2.c1 = t1.c1
STRAIGHT_JOIN t3
ON t3.c1 = t2.c1
AND t3.c2 = t2.c2
AND t4.c2 = t3.c1
AND t4.c3 = t3.c2
AND t4.c4 = t3.c3
STRAIGHT_JOIN t6
ON t6.c1 = t4.c5
STRAIGHT_JOIN t5
ON t5.c1 = t4.c1
STRAIGHT_JOIN t7
ON t7.c2 = t4.c1
STRAIGHT_JOIN t8
ON t8.c2 = t7.c1
WHERE t1.c1 IN (SELECT DISTINCT c3
FROM t9
WHERE t9.c1 = 13900180
AND t9.c3 = Ifnull(NULL, t9.c3))
AND t4.c1 IN (SELECT c3
FROM t10
WHERE c2 = 13900180);
?
In my case it is:
mysql> explain SELECT * FROM t1 STRAIGHT_JOIN t4 STRAIGHT_JOIN t2 ON t2.c1 = t1.c1 STRAIGHT_JOIN t3 ON t3.c1 = t2.c1 AND t3.c2 = t2.c2 AND t4.c2 = t3.c1 AND t4.c3 = t3.c2 AND t4.c4 = t3.c3 STRAIGHT_JOIN t6 ON t6.c1 = t4.c5 STRAIGHT_JOIN t5 ON t5.c1 = t4.c1 STRAIGHT_JOIN t7 ON t7.c2 = t4.c1 STRAIGHT_JOIN t8 ON t8.c2 = t7.c1 WHERE t1.c1 IN (SELECT DISTINCT c3 FROM t9 WHERE t9.c1 = 13900180 AND t9.c3 = Ifnull(
+----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where |
| 1 | SIMPLE | <subquery3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | 100.00 | Using where; Using index |
| 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY,key1,key2 | PRIMARY | 5 | <subquery3>.c3 | 1 | 5.00 | Using where |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t9.c3,test.t4.c3 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t3 | NULL | eq_ref | PRIMARY | PRIMARY | 6 | func,test.t4.c3,test.t4.c4 | 1 | 100.00 | Using where; Using index |
| 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | test.t4.c5 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | <subquery3>.c3 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | <subquery3>.c3 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | test.t7.c1 | 13 | 100.00 | NULL |
| 3 | MATERIALIZED | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | NULL |
+----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------+------+----------+----------------------------------------------------+
11 rows in set, 1 warning (0.02 sec)
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.7.23-debug-log |
+------------------+
1 row in set (0.00 sec)
mysql> \s
--------------
../bin/mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper
Connection id: 2
Current database: test
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.23-debug-log Source distribution
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 13000
Uptime: 2 min 0 sec
Threads: 1 Questions: 24 Slow queries: 0 Opens: 138 Flush tables: 1 Open tables: 111 Queries per second avg: 0.200
--------------
[23 Oct 2018 13:06]
MySQL Verification Team
Hi, Why don't you read what I wrote ???? My output is still same as I posted it above: +----+-------------+-------+------------+--------+-------------------+---------+---------+------------------------------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+-------------------+---------+---------+------------------------------------+------+----------+------------------------------+ | 1 | SIMPLE | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where; Start temporary | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | 100.00 | Using where; Using index | | 1 | SIMPLE | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | Using where | | 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY,key1,key2 | PRIMARY | 5 | bug92809.t10.c3 | 1 | 5.00 | Using where; End temporary | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bug92809.t9.c3,bug92809.t4.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | eq_ref | PRIMARY | PRIMARY | 6 | func,bug92809.t4.c3,bug92809.t4.c4 | 1 | 100.00 | Using where; Using index | | 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | bug92809.t4.c5 | 1 | 100.00 | Using index | | 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | bug92809.t10.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | bug92809.t10.c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | bug92809.t7.c1 | 13 | 100.00 | Using index | +----+-------------+-------+------------+--------+-------------------+---------+---------+------------------------------------+------+----------+------------------------------+ I was also using 5.7.24.
[23 Oct 2018 18:07]
Juan Arruti
Hi Sinisa,
Increasing the number of sample pages I can consistently obtain the execution plan that is returning 3488 rows:
mysql [localhost] {msandbox} (test) > set global innodb_stats_persistent_sample_pages=10000;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > analyze table t1, t2, t3, t4, t5, t6, t7, t8, t9, t10 ;
...
10 rows in set (1.83 sec)
# Optimizer switch is set to the default value
mysql [localhost] {msandbox} (test) > select @@optimizer_switch ;
...
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
mysql [localhost] {msandbox} (test) > SELECT *
-> FROM t1
-> INNER JOIN t2
-> ON t2.c1 = t1.c1
-> INNER JOIN t3
-> ON t3.c1 = t2.c1
-> AND t3.c2 = t2.c2
-> INNER JOIN t4
-> -- force index (key2)
-> ON t4.c2 = t3.c1
-> AND t4.c3 = t3.c2
-> AND t4.c4 = t3.c3
-> INNER JOIN t5
-> ON t5.c1 = t4.c1
-> INNER JOIN t6
-> ON t6.c1 = t4.c5
-> INNER JOIN t7
-> ON t7.c2 = t4.c1
-> INNER JOIN t8
-> ON t8.c2 = t7.c1
-> WHERE t1.c1 IN (SELECT DISTINCT c3
-> FROM t9
-> WHERE t9.c1 = 13900180
-> AND t9.c3 = Ifnull(NULL, t9.c3))
-> AND t4.c1 IN (SELECT c3
-> FROM t10
-> WHERE c2 = 13900180);
c6176573c3fcbb08631ab7021429e6ae -
3488 rows in set (0.04 sec)
mysql [localhost] {msandbox} (test) > explain
-> SELECT *
-> FROM t1
-> INNER JOIN t2
-> ON t2.c1 = t1.c1
-> INNER JOIN t3
-> ON t3.c1 = t2.c1
-> AND t3.c2 = t2.c2
-> INNER JOIN t4
-> -- force index (key2)
-> ON t4.c2 = t3.c1
-> AND t4.c3 = t3.c2
-> AND t4.c4 = t3.c3
-> INNER JOIN t5
-> ON t5.c1 = t4.c1
-> INNER JOIN t6
-> ON t6.c1 = t4.c5
-> INNER JOIN t7
-> ON t7.c2 = t4.c1
-> INNER JOIN t8
-> ON t8.c2 = t7.c1
-> WHERE t1.c1 IN (SELECT DISTINCT c3
-> FROM t9
-> WHERE t9.c1 = 13900180
-> AND t9.c3 = Ifnull(NULL, t9.c3))
-> AND t4.c1 IN (SELECT c3
-> FROM t10
-> WHERE c2 = 13900180);
+----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t9 | NULL | ref | PRIMARY,key1 | PRIMARY | 5 | const | 16 | 10.00 | Using where |
| 1 | SIMPLE | <subquery3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | test.t9.c3 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY,key1,key2 | PRIMARY | 5 | <subquery3>.c3 | 1 | 5.00 | Using where |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t9.c3,test.t4.c3 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t3 | NULL | eq_ref | PRIMARY | PRIMARY | 6 | test.t9.c3,test.t4.c3,test.t4.c4 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | test.t4.c5 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t5 | NULL | ref | PRIMARY | PRIMARY | 5 | <subquery3>.c3 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | <subquery3>.c3 | 1 | 100.00 | Using index |
| 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | test.t7.c1 | 13 | 100.00 | NULL |
| 3 | MATERIALIZED | t10 | NULL | ref | key1,key2 | key1 | 6 | const | 16 | 100.00 | NULL |
+----+--------------+-------------+------------+--------+-------------------+---------+---------+----------------------------------+------+----------+----------------------------------------------------+
11 rows in set, 1 warning (0.00 sec)
Regards,
[24 Oct 2018 8:18]
MySQL Verification Team
Thank you for the feedback, test case and other details. Observed reported issue(2/2 times against each build) on both 5.7.23 and 5.7.24. Joining results shortly. regards, Umesh
[24 Oct 2018 8:19]
MySQL Verification Team
MySQL Server 5.7.23 - observed as reported
Attachment: 92809_5.7.23.results (application/octet-stream, text), 28.05 KiB.
[24 Oct 2018 8:19]
MySQL Verification Team
MySQL Server 5.7.24 - observed as reported
Attachment: 92809_5.7.24.results (application/octet-stream, text), 29.05 KiB.
[4 Dec 2018 17:18]
Yura Sorokin
The problem was introduced in the implementation of the WL#7339 "Use improved records per key estimate interface in optimizer" https://dev.mysql.com/worklog/task/?id=7339 https://github.com/mysql/mysql-server/commit/c9787e5c4a2e86dbaa68632b24832e6842619db2 where optimizer started to use new "records per key" interface with floating point numbers.
[5 Dec 2018 13:29]
MySQL Verification Team
Thank you Mr. Sorokin ......
[12 Dec 2018 18:17]
Yura Sorokin
It turned out that this issue was silently fixed in 8.0 branch as part of the WL#9158 "Join Order Hints" (https://dev.mysql.com/worklog/task/?id=9158) implementation (https://github.com/mysql/mysql-server/commit/d2d91c3). *** Optimize_table_order::advance_sj_state(): added a change to prevent that inner tables of different semijoin nests are interleaved for MatScan. ***
[12 Dec 2018 18:25]
Yura Sorokin
Although the problem cannot be directly reproduced in 5.6 branch with the provided tests case, code analysis shows that this fix should be applied to this code branch as well. The bug does not reveal itself in 5.6 because this branch does not have floating point "records per key" interface yet. See WL#7339 "Use improved records per key estimate interface in optimizer" (https://dev.mysql.com/worklog/task/?id=7339) (https://github.com/mysql/mysql-server/commit/c9787e5c4a2e86dbaa68632b24832e6842619db2)
[12 Dec 2018 18:26]
Yura Sorokin
Suggested patch for 5.6 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug92809_5_6.diff (application/octet-stream, text), 7.39 KiB.
[12 Dec 2018 18:27]
Yura Sorokin
Suggested patch for 5.7 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug92809_5_7.diff (application/octet-stream, text), 3.94 KiB.
[13 Dec 2018 13:22]
MySQL Verification Team
Hi Mr. Sorokin, Your patches have been forwarded to the appropriate department. Thank you.
[14 May 2019 12:53]
Jon Stephens
Documented fix in the MySQL 5.7.27 changelog as follows:
Inner tables of different semijoin nests were interleaved during
materialization, which could lead to a different result for the
same query when it used a different query plan. To keep this
from occurring, a check is added to prevent such interleaving.
Closed.
[14 May 2019 13:09]
MySQL Verification Team
Jon, Thanks a lot !!!!!
