- ENV cat /etc/*release Oracle Linux Server release 7.1 NAME="Oracle Linux Server" VERSION="7.1" ID="ol" VERSION_ID="7.1" PRETTY_NAME="Oracle Linux Server 7.1" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:7:1" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://bugzilla.oracle.com/" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7" ORACLE_BUGZILLA_PRODUCT_VERSION=7.1 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=7.1 Red Hat Enterprise Linux Server release 7.1 (Maipo) Oracle Linux Server release 7.1 rm -rf 92809 bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/92809 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/92809 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/92809/log.err 2>&1 & - build cat docs/INFO_SRC commit: 0e7361b225055945d74cef67c838faa03d634137 date: 2018-06-08 15:28:05 +0530 build-date: 2018-06-08 11:56:42 +0200 short: 0e7361b branch: mysql-5.7.23-release MySQL source 5.7.23 -- Pulled provided test case mysql-bug-data-92809.zip(dump_92809.sql.gz) from sftp [umshastr@hod03]/tmp: gunzip dump_92809.sql.gz [umshastr@hod03]/tmp: ls -l -rw-r--r-- 1 umshastr common 175113862 Oct 16 23:36 dump_92809.sql ###### ## sftp file will be removed after 7 days, copied it to designated location on support h/w which can be accessed from any support boxes ###### [umshastr@hod03]/tmp: ls -l /usr/local/support/bugs/community/bug92809 total 171266 -rw-r--r--+ 1 umshastr common 175113862 Oct 24 09:08 dump_92809.sql [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.23: time bin/mysql -uroot -S /tmp/mysql_ushastry.sock < /tmp/dump_92809.sql real 1m41.586s user 0m2.670s sys 0m0.101s [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.23: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.23 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> select @@optimizer_switch\G *************************** 1. row *************************** @@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 1 row in set (0.00 sec) 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.04 sec) mysql> 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 | | 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 | .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 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | .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) mysql> show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2`,`test`.`t3`.`c3` AS `c3`,`test`.`t4`.`c1` AS `c1`,`test`.`t4`.`c2` AS `c2`,`test`.`t4`.`c3` AS `c3`,`test`.`t4`.`c4` AS `c4`,`test`.`t4`.`c5` AS `c5`,`test`.`t5`.`c1` AS `c1`,`test`.`t5`.`c2` AS `c2`,`test`.`t5`.`c3` AS `c3`,`test`.`t6`.`c1` AS `c1`,`test`.`t7`.`c1` AS `c1`,`test`.`t7`.`c2` AS `c2`,`test`.`t8`.`c1` AS `c1`,`test`.`t8`.`c2` AS `c2`,`test`.`t8`.`c3` AS `c3` from `test`.`t1` semi join (`test`.`t9`) semi join (`test`.`t10`) join `test`.`t2` join `test`.`t3` join `test`.`t4` join `test`.`t5` join `test`.`t6` join `test`.`t7` join `test`.`t8` where ((`test`.`t3`.`c3` = `test`.`t4`.`c4`) and (`test`.`t2`.`c2` = `test`.`t4`.`c3`) and (`test`.`t3`.`c2` = `test`.`t4`.`c3`) and (`test`.`t1`.`c1` = `test`.`t9`.`c3`) and (`test`.`t4`.`c2` = `test`.`t9`.`c3`) and (`test`.`t2`.`c1` = `test`.`t9`.`c3`) and (`test`.`t3`.`c1` = `test`.`t9`.`c3`) and (`test`.`t6`.`c1` = `test`.`t4`.`c5`) and (`test`.`t8`.`c2` = `test`.`t7`.`c1`) and (`test`.`t4`.`c1` = ``.`c3`) and (`test`.`t5`.`c1` = ``.`c3`) and (`test`.`t7`.`c2` = ``.`c3`) and (`test`.`t9`.`c1` = 13900180) and (`test`.`t9`.`c3` = ifnull(NULL,`test`.`t9`.`c3`)) and (`test`.`t10`.`c2` = 13900180)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> pager md5sum ; PAGER set to 'md5sum' mysql> 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.02 sec) mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.23: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.23 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> 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 | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where | | 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 2 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | | 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 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t2 | NULL | ref | PRIMARY | PRIMARY | 2 | .c3 | 7 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | ref | PRIMARY | PRIMARY | 4 | .c3,test.t2.c2 | 8 | 100.00 | Using index | | 1 | SIMPLE | t4 | NULL | ref | key2 | key2 | 14 | .c3,test.t2.c2,test.t3.c3,.c3 | 1 | 100.00 | Using where | | 1 | SIMPLE | t6 | NULL | eq_ref | PRIMARY | PRIMARY | 3 | test.t4.c5 | 1 | 100.00 | Using index | | 1 | SIMPLE | t7 | NULL | ref | PRIMARY,key1 | key1 | 6 | .c3 | 1 | 100.00 | Using index | | 1 | SIMPLE | t8 | NULL | ref | key1 | key1 | 6 | test.t7.c1 | 13 | 100.00 | NULL | | 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 | +----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------------------------------+------+----------+----------------------------------------------------+ 12 rows in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2`,`test`.`t3`.`c3` AS `c3`,`test`.`t4`.`c1` AS `c1`,`test`.`t4`.`c2` AS `c2`,`test`.`t4`.`c3` AS `c3`,`test`.`t4`.`c4` AS `c4`,`test`.`t4`.`c5` AS `c5`,`test`.`t5`.`c1` AS `c1`,`test`.`t5`.`c2` AS `c2`,`test`.`t5`.`c3` AS `c3`,`test`.`t6`.`c1` AS `c1`,`test`.`t7`.`c1` AS `c1`,`test`.`t7`.`c2` AS `c2`,`test`.`t8`.`c1` AS `c1`,`test`.`t8`.`c2` AS `c2`,`test`.`t8`.`c3` AS `c3` from `test`.`t1` semi join (`test`.`t9`) semi join (`test`.`t10`) join `test`.`t2` join `test`.`t3` join `test`.`t4` FORCE INDEX (`key2`) join `test`.`t5` join `test`.`t6` join `test`.`t7` join `test`.`t8` where ((`test`.`t4`.`c4` = `test`.`t3`.`c3`) and (`test`.`t3`.`c2` = `test`.`t2`.`c2`) and (`test`.`t4`.`c3` = `test`.`t2`.`c2`) and (`test`.`t1`.`c1` = ``.`c3`) and (`test`.`t2`.`c1` = ``.`c3`) and (`test`.`t3`.`c1` = ``.`c3`) and (`test`.`t4`.`c2` = ``.`c3`) and (`test`.`t6`.`c1` = `test`.`t4`.`c5`) and (`test`.`t8`.`c2` = `test`.`t7`.`c1`) and (`test`.`t5`.`c1` = ``.`c3`) and (`test`.`t4`.`c1` = ``.`c3`) and (`test`.`t7`.`c2` = ``.`c3`) and (`test`.`t9`.`c1` = 13900180) and (`test`.`t9`.`c3` = ifnull(NULL,`test`.`t9`.`c3`)) and (`test`.`t10`.`c2` = 13900180)) | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> pager md5sum ; PAGER set to 'md5sum' mysql> 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); 92ac8ecc163c231f0864f1f574dd8e8e - 218 rows in set (0.01 sec) mysql>