-- 8.0.17 ./mtr reconsider_index_for_order_bad_optimization.test Logging: ./mtr reconsider_index_for_order_bad_optimization.test MySQL Version 8.0.17 Checking supported features Using 'all' suites Collecting tests Checking leftover processes Removing old var directory Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-8.0.17/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ [100%] main.reconsider_index_for_order_bad_optimization [ fail ] Test ended at 2019-09-25 08:28:49 Result file '/export/umesh/server/binaries/GABuilds/mysql-8.0.17/mysql-test/r/reconsider_index_for_order_bad_optimization.result' doesn't exist. Either create a result file or disable check-testcases and run the test case. Use --nocheck-testcases option to disable check-testcases. Mysqltest client output from logfile ----------- MYSQLTEST OUTPUT START ----------- # # Preparing tables # CREATE TABLE `t` ( `id` BIGINT NOT NULL, `other_id` BIGINT NOT NULL, `covered_column` VARCHAR(50) NOT NULL, `non_covered_column` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), INDEX `index_other_id_covered_column` (`other_id`, `covered_column`) ) ENGINE=InnoDB; # Inserting a bunch of data into table t. START TRANSACTION; COMMIT; # Shifting some of the data to the "end" of the table. UPDATE `t` SET `id`=`id`+1000000 WHERE `other_id` = 555; # # Warming the cache so that the timed queries are from cache. # SELECT SUM(`non_covered_column`) FROM `t` WHERE `other_id` = 555; SUM(`non_covered_column`) 500055000 SET optimizer_trace = "enabled=on", optimizer_trace_max_mem_size = 1000000, end_markers_in_json = ON; ### ### ### Testing SELECT ... WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT ... ### ### # # Running with a small LIMIT which causes reconsideration. # EXPLAIN SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL index index_other_id_covered_column PRIMARY 8 NULL 996 0.10 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t`.`non_covered_column` AS `non_covered_column` from `test`.`t` where (`test`.`t`.`other_id` = 555) order by `test`.`t`.`id` limit 1 SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1; non_covered_column 555 SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%index_order_summary%plan_changed%false%') AS should_be_1 FROM information_schema.optimizer_trace; should_be_1 0 # Run time exceeded limit! Time was: 0.550162 # # Running with a larger LIMIT which doesn't cause reconsideration. # EXPLAIN SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL ref index_other_id_covered_column index_other_id_covered_column 8 const 1000 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t`.`non_covered_column` AS `non_covered_column` from `test`.`t` where (`test`.`t`.`other_id` = 555) order by `test`.`t`.`id` limit 5 SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 5; non_covered_column 555 1555 2555 3555 4555 SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%index_order_summary%plan_changed%false%') AS should_be_1 FROM information_schema.optimizer_trace; should_be_1 1 # Run time was as expected (fast, < 0.1s). # # Cleaning up. # DROP TABLE `t`; ------------ MYSQLTEST OUTPUT END ----------- - the logfile can be found in '/export/umesh/server/binaries/GABuilds/mysql-8.0.17/mysql-test/var/log/main.reconsider_index_for_order_bad_optimization/reconsider_index_for_order_bad_optimization.log' ------------------------------------------------------------------------------ The servers were restarted 0 times The servers were reinitialized 0 times Spent 0.000 of 181 seconds executing testcases Completed: Failed 1/1 tests, 0.00% were successful. Failing test(s): main.reconsider_index_for_order_bad_optimization The log files in var/log may give you some hint of what went wrong. If you want to report this error, please read first the documentation at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html mysql-test-run: *** ERROR: there were failing test cases - 5.7.27 ./mtr reconsider_index_for_order_bad_optimization.test Logging: ./mtr reconsider_index_for_order_bad_optimization.test MySQL Version 5.7.27 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-5.7.27/mysql-test/var'... Installing system database... Using parallel: 1 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 # # Preparing tables # CREATE TABLE `t` ( `id` BIGINT NOT NULL, `other_id` BIGINT NOT NULL, `covered_column` VARCHAR(50) NOT NULL, `non_covered_column` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), INDEX `index_other_id_covered_column` (`other_id`, `covered_column`) ) ENGINE=InnoDB; # Inserting a bunch of data into table t. START TRANSACTION; COMMIT; # Shifting some of the data to the "end" of the table. UPDATE `t` SET `id`=`id`+1000000 WHERE `other_id` = 555; # # Warming the cache so that the timed queries are from cache. # SELECT SUM(`non_covered_column`) FROM `t` WHERE `other_id` = 555; SUM(`non_covered_column`) 500055000 SET optimizer_trace = "enabled=on", optimizer_trace_max_mem_size = 1000000, end_markers_in_json = ON; ### ### ### Testing SELECT ... WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT ... ### ### # # Running with a small LIMIT which causes reconsideration. # EXPLAIN SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL index index_other_id_covered_column PRIMARY 8 NULL 997 0.10 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t`.`non_covered_column` AS `non_covered_column` from `test`.`t` where (`test`.`t`.`other_id` = 555) order by `test`.`t`.`id` limit 1 SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1; non_covered_column 555 SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%index_order_summary%plan_changed%false%') AS should_be_1 FROM information_schema.optimizer_trace; should_be_1 0 # Run time exceeded limit! Time was: 0.487185 # # Running with a larger LIMIT which doesn't cause reconsideration. # EXPLAIN SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL ref index_other_id_covered_column index_other_id_covered_column 8 const 1000 100.00 Using index condition; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t`.`non_covered_column` AS `non_covered_column` from `test`.`t` where (`test`.`t`.`other_id` = 555) order by `test`.`t`.`id` limit 5 SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 5; non_covered_column 555 1555 2555 3555 4555 SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%index_order_summary%plan_changed%false%') AS should_be_1 FROM information_schema.optimizer_trace; should_be_1 1 # Run time was as expected (fast, < 0.1s). # # Cleaning up. # DROP TABLE `t`; [100%] main.reconsider_index_for_order_bad_optimization [ pass ] 108154 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 108.154 of 114 seconds executing testcases Completed: All 1 tests were successful. - 5.6.45 ./mtr reconsider_index_for_order_bad_optimization.test Logging: ./mtr reconsider_index_for_order_bad_optimization.test 2019-09-25 08:48:34 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2019-09-25 08:48:34 0 [Note] /export/umesh/server/binaries/GABuilds/mysql-5.6.45/bin/mysqld (mysqld 5.6.45) starting as process 4469 ... 2019-09-25 08:48:35 4469 [Note] Plugin 'FEDERATED' is disabled. 2019-09-25 08:48:35 4469 [Note] Binlog end 2019-09-25 08:48:35 4469 [Note] Shutting down plugin 'CSV' 2019-09-25 08:48:35 4469 [Note] Shutting down plugin 'MyISAM' MySQL Version 5.6.45 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-5.6.45/mysql-test/var'... Installing system database... Using parallel: 1 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 # # Preparing tables # CREATE TABLE `t` ( `id` BIGINT NOT NULL, `other_id` BIGINT NOT NULL, `covered_column` VARCHAR(50) NOT NULL, `non_covered_column` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`), INDEX `index_other_id_covered_column` (`other_id`, `covered_column`) ) ENGINE=InnoDB; # Inserting a bunch of data into table t. START TRANSACTION; COMMIT; # Shifting some of the data to the "end" of the table. UPDATE `t` SET `id`=`id`+1000000 WHERE `other_id` = 555; # # Warming the cache so that the timed queries are from cache. # SELECT SUM(`non_covered_column`) FROM `t` WHERE `other_id` = 555; SUM(`non_covered_column`) 500055000 SET optimizer_trace = "enabled=on", optimizer_trace_max_mem_size = 1000000, end_markers_in_json = ON; ### ### ### Testing SELECT ... WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT ... ### ### # # Running with a small LIMIT which causes reconsideration. # EXPLAIN SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t index index_other_id_covered_column PRIMARY 8 NULL 676 Using where SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 1; non_covered_column 555 SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%index_order_summary%plan_changed%false%') AS should_be_1 FROM information_schema.optimizer_trace; should_be_1 0 # Run time exceeded limit! Time was: 0.880011 # # Running with a larger LIMIT which doesn't cause reconsideration. # EXPLAIN SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t ref index_other_id_covered_column index_other_id_covered_column 8 const 999 Using where; Using filesort SELECT `non_covered_column` FROM `t` WHERE `other_id` = 555 ORDER BY `id` ASC LIMIT 5; non_covered_column 555 1555 2555 3555 4555 SELECT (trace LIKE '%reconsidering_access_paths_for_index_ordering%index_order_summary%plan_changed%false%') AS should_be_1 FROM information_schema.optimizer_trace; should_be_1 1 # Run time was as expected (fast, < 0.1s). # # Cleaning up. # DROP TABLE `t`; main.reconsider_index_for_order_bad_optimization [ pass ] 117009 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 117.009 of 123 seconds executing testcases Completed: All 1 tests were successful.