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:
None 
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
Description:
Attached test case shows inconsistent retrieved rows when different execution plans are used. It affects MySQL Community 5.7.23. It couldn't be reproduced on version 5.6.

How to repeat:
See attached test case.
[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 !!!!!