[Commits] c2789fdd09f: Issue #790, MyRocks/MRR: make ref(const) access switch to MRR

revision-id: c2789fdd09f84fbf4321c5bde74851e5b58d01f7 (fb-prod201903-162-gc2789fdd09f) parent(s): 269ee6f6076173a579e721d35b3834d8433271c3 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-09-14 22:30:06 +0300 message: Issue #790, MyRocks/MRR: make ref(const) access switch to MRR Followup patch: update test results: - index_merge_innodb uses a table that's large enough so that the choice to use MRR is cost-based - Other tests set mrr=on,mrr_cost_based=off, which forces MRR to be used. --- mysql-test/r/index_merge_innodb.result | 2 +- .../r/innodb_explain_json_non_select_all.result | 18 ++++------- mysql-test/r/innodb_explain_non_select_all.result | 4 +-- mysql-test/r/join_cache_bka.result | 6 ++-- mysql-test/r/join_cache_bka_nixbnl.result | 6 ++-- mysql-test/r/join_cache_bnl.result | 6 ++-- mysql-test/r/join_cache_nojb.result | 6 ++-- mysql-test/r/join_nested_bka.result | 2 +- mysql-test/r/join_nested_bka_nixbnl.result | 2 +- .../r/myisam_explain_json_non_select_all.result | 18 ++++------- mysql-test/r/myisam_explain_non_select_all.result | 4 +-- mysql-test/r/myisam_mrr.result | 2 +- mysql-test/r/myisam_mrr_all.result | 2 +- mysql-test/r/myisam_mrr_icp.result | 2 +- mysql-test/r/order_by_all.result | 8 ++--- mysql-test/r/order_by_icp_mrr.result | 8 ++--- mysql-test/r/range_all.result | 28 ++++++++--------- mysql-test/r/range_icp_mrr.result | 28 ++++++++--------- mysql-test/r/range_mrr.result | 28 ++++++++--------- mysql-test/r/select_all.result | 26 ++++++++-------- mysql-test/r/select_all_bka.result | 26 ++++++++-------- mysql-test/r/select_all_bka_nixbnl.result | 26 ++++++++-------- mysql-test/r/select_icp_mrr.result | 26 ++++++++-------- mysql-test/r/select_icp_mrr_bka.result | 26 ++++++++-------- mysql-test/r/select_icp_mrr_bka_nixbnl.result | 26 ++++++++-------- mysql-test/r/subquery_all.result | 8 ++--- mysql-test/r/subquery_all_bka.result | 8 ++--- mysql-test/r/subquery_all_bka_nixbnl.result | 8 ++--- mysql-test/r/subquery_nomat_nosj.result | 8 ++--- mysql-test/r/subquery_nomat_nosj_bka.result | 8 ++--- mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result | 8 ++--- mysql-test/r/subquery_sj_all.result | 35 ++++++++++------------ mysql-test/r/subquery_sj_all_bka.result | 35 ++++++++++------------ mysql-test/r/subquery_sj_all_bka_nixbnl.result | 35 ++++++++++------------ 34 files changed, 234 insertions(+), 255 deletions(-) diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index 98d69097140..f55b26ab31c 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -746,7 +746,7 @@ key1 key2 key3 key4 filler1 -1 -1 200 -1 key3 explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 999 NULL +1 SIMPLE t1 range sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 NULL 999 Using index condition; Using MRR explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const 3999 Using where diff --git a/mysql-test/r/innodb_explain_json_non_select_all.result b/mysql-test/r/innodb_explain_json_non_select_all.result index 6729e40dd9a..800f6763b78 100644 --- a/mysql-test/r/innodb_explain_json_non_select_all.result +++ b/mysql-test/r/innodb_explain_json_non_select_all.result @@ -4699,7 +4699,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using where +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -4713,7 +4713,7 @@ EXPLAIN "using_filesort": false, "table": { "table_name": "t1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "c1_idx" ] /* possible_keys */, @@ -4722,12 +4722,9 @@ EXPLAIN "c1_idx" ] /* used_key_parts */, "key_length": "2", - "ref": [ - "const" - ] /* ref */, "rows": 2, "filtered": 100, - "attached_condition": "((`test`.`t1`.`c1_idx` <=> 'y') and (`test`.`t1`.`c1_idx` = 'y'))" + "index_condition": "(`test`.`t1`.`c1_idx` = 'y')" } /* table */ } /* ordering_operation */ } /* query_block */ @@ -4792,7 +4789,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using where +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -4806,7 +4803,7 @@ EXPLAIN "using_filesort": false, "table": { "table_name": "t1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "c1_idx" ] /* possible_keys */, @@ -4815,12 +4812,9 @@ EXPLAIN "c1_idx" ] /* used_key_parts */, "key_length": "2", - "ref": [ - "const" - ] /* ref */, "rows": 2, "filtered": 100, - "attached_condition": "((`test`.`t1`.`c1_idx` <=> 'y') and (`test`.`t1`.`c1_idx` = 'y'))" + "index_condition": "(`test`.`t1`.`c1_idx` = 'y')" } /* table */ } /* ordering_operation */ } /* query_block */ diff --git a/mysql-test/r/innodb_explain_non_select_all.result b/mysql-test/r/innodb_explain_non_select_all.result index 9b80213dd12..945a9d3fde3 100644 --- a/mysql-test/r/innodb_explain_non_select_all.result +++ b/mysql-test/r/innodb_explain_non_select_all.result @@ -2211,7 +2211,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using where +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -2245,7 +2245,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using where +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution diff --git a/mysql-test/r/join_cache_bka.result b/mysql-test/r/join_cache_bka.result index 89177082752..cab5504b367 100644 --- a/mysql-test/r/join_cache_bka.result +++ b/mysql-test/r/join_cache_bka.result @@ -1302,8 +1302,8 @@ t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND t1.metaid = t2.metaid AND t1.affiliateid = '2'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 system PRIMARY NULL NULL NULL 1 NULL -1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1 NULL -1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where; Using join buffer (Batched Key Access) +1 SIMPLE t1 range t1_affiliateid,t1_metaid t1_affiliateid 4 NULL 1 Using index condition; Using MRR +1 SIMPLE t4 range PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 NULL 1 Using index condition; Using where; Using MRR; Using join buffer (Block Nested Loop) 1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where; Using join buffer (Batched Key Access) 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (Batched Key Access) 1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index @@ -2216,7 +2216,7 @@ ORDER BY t1.col_int_key, t1.col_datetime LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL col_int_key NULL NULL NULL 3 Using temporary; Using filesort -1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (Batched Key Access) +1 SIMPLE t2 range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR; Using join buffer (Block Nested Loop) SELECT t1.col_int_key, t1.col_datetime FROM t1,t2 WHERE t2.col_int_key = 1 AND t2.col_int >= 3 diff --git a/mysql-test/r/join_cache_bka_nixbnl.result b/mysql-test/r/join_cache_bka_nixbnl.result index 3d89af07b57..b27e9675112 100644 --- a/mysql-test/r/join_cache_bka_nixbnl.result +++ b/mysql-test/r/join_cache_bka_nixbnl.result @@ -1302,8 +1302,8 @@ t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND t1.metaid = t2.metaid AND t1.affiliateid = '2'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 system PRIMARY NULL NULL NULL 1 NULL -1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1 NULL -1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where; Using join buffer (Batched Key Access) +1 SIMPLE t1 range t1_affiliateid,t1_metaid t1_affiliateid 4 NULL 1 Using index condition; Using MRR +1 SIMPLE t4 range PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 NULL 1 Using index condition; Using where; Using MRR 1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where; Using join buffer (Batched Key Access) 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (Batched Key Access) 1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index @@ -2216,7 +2216,7 @@ ORDER BY t1.col_int_key, t1.col_datetime LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL col_int_key NULL NULL NULL 3 Using temporary; Using filesort -1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (Batched Key Access) +1 SIMPLE t2 range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR SELECT t1.col_int_key, t1.col_datetime FROM t1,t2 WHERE t2.col_int_key = 1 AND t2.col_int >= 3 diff --git a/mysql-test/r/join_cache_bnl.result b/mysql-test/r/join_cache_bnl.result index 1ebf6f8bd71..853a2f8a225 100644 --- a/mysql-test/r/join_cache_bnl.result +++ b/mysql-test/r/join_cache_bnl.result @@ -1303,8 +1303,8 @@ t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND t1.metaid = t2.metaid AND t1.affiliateid = '2'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 system PRIMARY NULL NULL NULL 1 NULL -1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1 NULL -1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where +1 SIMPLE t1 range t1_affiliateid,t1_metaid t1_affiliateid 4 NULL 1 Using index condition; Using MRR +1 SIMPLE t4 range PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 NULL 1 Using index condition; Using where; Using MRR; Using join buffer (Block Nested Loop) 1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 NULL 1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index @@ -2217,7 +2217,7 @@ ORDER BY t1.col_int_key, t1.col_datetime LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL col_int_key NULL NULL NULL 3 Using temporary; Using filesort -1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where +1 SIMPLE t2 range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR; Using join buffer (Block Nested Loop) SELECT t1.col_int_key, t1.col_datetime FROM t1,t2 WHERE t2.col_int_key = 1 AND t2.col_int >= 3 diff --git a/mysql-test/r/join_cache_nojb.result b/mysql-test/r/join_cache_nojb.result index e7982de302c..a045c4d890a 100644 --- a/mysql-test/r/join_cache_nojb.result +++ b/mysql-test/r/join_cache_nojb.result @@ -1303,8 +1303,8 @@ t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND t1.metaid = t2.metaid AND t1.affiliateid = '2'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 system PRIMARY NULL NULL NULL 1 NULL -1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1 NULL -1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where +1 SIMPLE t1 range t1_affiliateid,t1_metaid t1_affiliateid 4 NULL 1 Using index condition; Using MRR +1 SIMPLE t4 range PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 NULL 1 Using index condition; Using where; Using MRR 1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 NULL 1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index @@ -2217,7 +2217,7 @@ ORDER BY t1.col_int_key, t1.col_datetime LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL col_int_key NULL NULL NULL 3 Using temporary; Using filesort -1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where +1 SIMPLE t2 range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR SELECT t1.col_int_key, t1.col_datetime FROM t1,t2 WHERE t2.col_int_key = 1 AND t2.col_int >= 3 diff --git a/mysql-test/r/join_nested_bka.result b/mysql-test/r/join_nested_bka.result index bf1bc232c04..5bdffe0a7c7 100644 --- a/mysql-test/r/join_nested_bka.result +++ b/mysql-test/r/join_nested_bka.result @@ -1055,7 +1055,7 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where +1 SIMPLE t0 range idx_a idx_a 5 NULL 1 100.00 Using index condition; Using where; Using MRR 1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using join buffer (Batched Key Access) 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop) diff --git a/mysql-test/r/join_nested_bka_nixbnl.result b/mysql-test/r/join_nested_bka_nixbnl.result index 895435deb26..f7827381b2f 100644 --- a/mysql-test/r/join_nested_bka_nixbnl.result +++ b/mysql-test/r/join_nested_bka_nixbnl.result @@ -1055,7 +1055,7 @@ t0.b=t1.b AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where +1 SIMPLE t0 range idx_a idx_a 5 NULL 1 100.00 Using index condition; Using where; Using MRR 1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 2 100.00 Using join buffer (Batched Key Access) 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where diff --git a/mysql-test/r/myisam_explain_json_non_select_all.result b/mysql-test/r/myisam_explain_json_non_select_all.result index 9b72fcea450..e0443347640 100644 --- a/mysql-test/r/myisam_explain_json_non_select_all.result +++ b/mysql-test/r/myisam_explain_json_non_select_all.result @@ -4556,7 +4556,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using index condition; Using MRR; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -4570,7 +4570,7 @@ EXPLAIN "using_filesort": true, "table": { "table_name": "t1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "c1_idx" ] /* possible_keys */, @@ -4579,13 +4579,10 @@ EXPLAIN "c1_idx" ] /* used_key_parts */, "key_length": "2", - "ref": [ - "const" - ] /* ref */, "rows": 2, "filtered": 100, "index_condition": "(`test`.`t1`.`c1_idx` = 'y')", - "attached_condition": "((`test`.`t1`.`c1_idx` <=> 'y'))" + "using_MRR": true } /* table */ } /* ordering_operation */ } /* query_block */ @@ -4655,7 +4652,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using index condition; Using MRR; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -4669,7 +4666,7 @@ EXPLAIN "using_filesort": true, "table": { "table_name": "t1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "c1_idx" ] /* possible_keys */, @@ -4678,13 +4675,10 @@ EXPLAIN "c1_idx" ] /* used_key_parts */, "key_length": "2", - "ref": [ - "const" - ] /* ref */, "rows": 2, "filtered": 100, "index_condition": "(`test`.`t1`.`c1_idx` = 'y')", - "attached_condition": "((`test`.`t1`.`c1_idx` <=> 'y'))" + "using_MRR": true } /* table */ } /* ordering_operation */ } /* query_block */ diff --git a/mysql-test/r/myisam_explain_non_select_all.result b/mysql-test/r/myisam_explain_non_select_all.result index 330713ddef3..ba36f354ee2 100644 --- a/mysql-test/r/myisam_explain_non_select_all.result +++ b/mysql-test/r/myisam_explain_non_select_all.result @@ -2085,7 +2085,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using index condition; Using MRR; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution @@ -2124,7 +2124,7 @@ FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using index condition; Using MRR; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index bf0485383b8..a2ea0318800 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -347,7 +347,7 @@ GROUP BY t2.pk ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using where +2 SUBQUERY t2 range int_key int_key 5 NULL 1 100.00 Using where; Using MRR Warnings: Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 DROP TABLE t1, t2; diff --git a/mysql-test/r/myisam_mrr_all.result b/mysql-test/r/myisam_mrr_all.result index 8eda02da1d1..37e1e3d573a 100644 --- a/mysql-test/r/myisam_mrr_all.result +++ b/mysql-test/r/myisam_mrr_all.result @@ -347,7 +347,7 @@ GROUP BY t2.pk ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition +2 SUBQUERY t2 range int_key int_key 5 NULL 1 100.00 Using index condition; Using MRR Warnings: Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 DROP TABLE t1, t2; diff --git a/mysql-test/r/myisam_mrr_icp.result b/mysql-test/r/myisam_mrr_icp.result index de98834f39c..fe637227b4a 100644 --- a/mysql-test/r/myisam_mrr_icp.result +++ b/mysql-test/r/myisam_mrr_icp.result @@ -347,7 +347,7 @@ GROUP BY t2.pk ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition +2 SUBQUERY t2 range int_key int_key 5 NULL 1 100.00 Using index condition; Using MRR Warnings: Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0 DROP TABLE t1, t2; diff --git a/mysql-test/r/order_by_all.result b/mysql-test/r/order_by_all.result index 3389f44979c..c50053bab4f 100644 --- a/mysql-test/r/order_by_all.result +++ b/mysql-test/r/order_by_all.result @@ -602,7 +602,7 @@ KEY StringField (FieldKey,StringVal(32)) INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using where +1 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 3 Using where SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; FieldKey LongVal StringVal 1 0 2 @@ -683,10 +683,10 @@ a b c d 1 1 92 -9 select * from t1 where a=1 and b in (1); a b c d +1 1 2 0 1 1 92 -9 1 1 52 -5 1 1 12 -1 -1 1 2 0 drop table t1, t2; create table t1 (col1 int, col int); create table t2 (col2 int, col int); @@ -1102,7 +1102,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 111 Using where EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref k2 k2 5 const 7341 Using where; Using filesort +1 SIMPLE t2 range k2 k2 5 NULL 7341 Using index condition; Using MRR; Using filesort EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 73 Using where @@ -1487,7 +1487,7 @@ SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort +1 SIMPLE t1 range a,b b 4 NULL 4 Using index condition; Using where; Using MRR; Using temporary; Using filesort 1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' diff --git a/mysql-test/r/order_by_icp_mrr.result b/mysql-test/r/order_by_icp_mrr.result index de8c9858994..18595dfeb98 100644 --- a/mysql-test/r/order_by_icp_mrr.result +++ b/mysql-test/r/order_by_icp_mrr.result @@ -602,7 +602,7 @@ KEY StringField (FieldKey,StringVal(32)) INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3'); EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref FieldKey,LongField,StringField LongField 38 const 3 Using where +1 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 3 Using where SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal; FieldKey LongVal StringVal 1 0 2 @@ -683,10 +683,10 @@ a b c d 1 1 92 -9 select * from t1 where a=1 and b in (1); a b c d +1 1 2 0 1 1 92 -9 1 1 52 -5 1 1 12 -1 -1 1 2 0 drop table t1, t2; create table t1 (col1 int, col int); create table t2 (col2 int, col int); @@ -1102,7 +1102,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 111 Using where EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref k2 k2 5 const 7341 Using where; Using filesort +1 SIMPLE t2 range k2 k2 5 NULL 7341 Using index condition; Using MRR; Using filesort EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index k2 k3 5 NULL 73 Using where @@ -1487,7 +1487,7 @@ SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort +1 SIMPLE t1 range a,b b 4 NULL 4 Using index condition; Using where; Using MRR; Using temporary; Using filesort 1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' diff --git a/mysql-test/r/range_all.result b/mysql-test/r/range_all.result index 629fe9b1ec4..3032dd19893 100644 --- a/mysql-test/r/range_all.result +++ b/mysql-test/r/range_all.result @@ -224,27 +224,27 @@ Table Op Msg_type Msg_text test.t1 analyze status OK explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select count(*) from t1 where x in (1); id select_type table type possible_keys key key_len ref rows Extra @@ -326,8 +326,8 @@ KEY recount( owner, line ) INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30; id columnid tableid content showid line ordinal -13 13 1 188 1 5 0 15 15 1 188 1 1 0 +13 13 1 188 1 5 0 drop table t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -619,7 +619,7 @@ INSERT INTO t1 (a) VALUES ('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb'); explain select * from t1 where a='aaa'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 11 const 2 Using index condition +1 SIMPLE t1 range a a 11 NULL 2 Using index condition; Using MRR explain select * from t1 where a=binary 'aaa'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 11 NULL 2 Using index condition; Using MRR @@ -708,8 +708,8 @@ WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using index condition -1 SIMPLE s ref OXLEFT,OXROOTID OXROOTID 34 const 5 Using index condition; Using where +1 SIMPLE v range OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 NULL 5 Using index condition; Using MRR +1 SIMPLE s range OXLEFT,OXROOTID OXROOTID 34 NULL 5 Using index condition; Using where; Using MRR; Using join buffer (Block Nested Loop) SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND @@ -1026,10 +1026,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 13 NULL # Using index condition; Using MRR explain select * from t2 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using index condition +1 SIMPLE t2 range a a 13 NULL # Using index condition; Using MRR explain select * from t2 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using index condition +1 SIMPLE t2 range a a 13 NULL # Using index condition; Using MRR update t1 set a='b' where a<>'a'; explain select * from t1 where a not between 'b' and 'b'; id select_type table type possible_keys key key_len ref rows Extra @@ -1115,7 +1115,7 @@ Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 20 NULL 2 Using index condition; Using MRR Warnings: Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 @@ -1983,7 +1983,7 @@ INSERT INTO t2 VALUES (1, 1, 2); # range estimates k is selected. EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref k,l,m,n k 5 const 66 Using where +1 SIMPLE t2 range k,l,m,n k 5 NULL 66 Using index condition; Using where; Using MRR DROP TABLE t1, t2; # # BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER diff --git a/mysql-test/r/range_icp_mrr.result b/mysql-test/r/range_icp_mrr.result index a21557d4f3c..4a7b4a22205 100644 --- a/mysql-test/r/range_icp_mrr.result +++ b/mysql-test/r/range_icp_mrr.result @@ -224,27 +224,27 @@ Table Op Msg_type Msg_text test.t1 analyze status OK explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 3 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using index condition; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using index condition; Using MRR; Using join buffer (Block Nested Loop) explain select count(*) from t1 where x in (1); id select_type table type possible_keys key key_len ref rows Extra @@ -326,8 +326,8 @@ KEY recount( owner, line ) INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30; id columnid tableid content showid line ordinal -13 13 1 188 1 5 0 15 15 1 188 1 1 0 +13 13 1 188 1 5 0 drop table t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -619,7 +619,7 @@ INSERT INTO t1 (a) VALUES ('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb'); explain select * from t1 where a='aaa'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 11 const 2 Using index condition +1 SIMPLE t1 range a a 11 NULL 2 Using index condition; Using MRR explain select * from t1 where a=binary 'aaa'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 11 NULL 2 Using index condition; Using MRR @@ -708,8 +708,8 @@ WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using index condition -1 SIMPLE s ref OXLEFT,OXROOTID OXROOTID 34 const 5 Using index condition; Using where +1 SIMPLE v range OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 NULL 5 Using index condition; Using MRR +1 SIMPLE s range OXLEFT,OXROOTID OXROOTID 34 NULL 5 Using index condition; Using where; Using MRR; Using join buffer (Block Nested Loop) SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND @@ -1026,10 +1026,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 13 NULL # Using index condition; Using MRR explain select * from t2 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using index condition +1 SIMPLE t2 range a a 13 NULL # Using index condition; Using MRR explain select * from t2 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using index condition +1 SIMPLE t2 range a a 13 NULL # Using index condition; Using MRR update t1 set a='b' where a<>'a'; explain select * from t1 where a not between 'b' and 'b'; id select_type table type possible_keys key key_len ref rows Extra @@ -1115,7 +1115,7 @@ Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 20 NULL 2 Using index condition; Using MRR Warnings: Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 @@ -1983,7 +1983,7 @@ INSERT INTO t2 VALUES (1, 1, 2); # range estimates k is selected. EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref k,l,m,n k 5 const 66 Using where +1 SIMPLE t2 range k,l,m,n k 5 NULL 66 Using index condition; Using where; Using MRR DROP TABLE t1, t2; # # BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER diff --git a/mysql-test/r/range_mrr.result b/mysql-test/r/range_mrr.result index 77f202ac6ff..53bd403c82c 100644 --- a/mysql-test/r/range_mrr.result +++ b/mysql-test/r/range_mrr.result @@ -224,27 +224,27 @@ Table Op Msg_type Msg_text test.t1 analyze status OK explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using where; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using where; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using where; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using where; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using where; Using MRR 1 SIMPLE t2 range x x 5 NULL 3 Using where; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using where; Using MRR 1 SIMPLE t2 range x x 5 NULL 3 Using where; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using where; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using where; Using MRR; Using join buffer (Block Nested Loop) explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref y y 5 const 1 NULL +1 SIMPLE t1 range y y 5 NULL 1 Using where; Using MRR 1 SIMPLE t2 range x x 5 NULL 2 Using where; Using MRR; Using join buffer (Block Nested Loop) explain select count(*) from t1 where x in (1); id select_type table type possible_keys key key_len ref rows Extra @@ -326,8 +326,8 @@ KEY recount( owner, line ) INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5); SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30; id columnid tableid content showid line ordinal -13 13 1 188 1 5 0 15 15 1 188 1 1 0 +13 13 1 188 1 5 0 drop table t1; create table t1 (id int(10) primary key); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -619,7 +619,7 @@ INSERT INTO t1 (a) VALUES ('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb'); explain select * from t1 where a='aaa'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 11 const 2 Using where +1 SIMPLE t1 range a a 11 NULL 2 Using where; Using MRR explain select * from t1 where a=binary 'aaa'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 11 NULL 2 Using where; Using MRR @@ -708,8 +708,8 @@ WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE v ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 Using where -1 SIMPLE s ref OXLEFT,OXROOTID OXROOTID 34 const 5 Using where +1 SIMPLE v range OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 NULL 5 Using where; Using MRR +1 SIMPLE s range OXLEFT,OXROOTID OXROOTID 34 NULL 5 Using where; Using MRR; Using join buffer (Block Nested Loop) SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND @@ -1026,10 +1026,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 13 NULL # Using where; Using MRR explain select * from t2 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using where +1 SIMPLE t2 range a a 13 NULL # Using where; Using MRR explain select * from t2 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using where +1 SIMPLE t2 range a a 13 NULL # Using where; Using MRR update t1 set a='b' where a<>'a'; explain select * from t1 where a not between 'b' and 'b'; id select_type table type possible_keys key key_len ref rows Extra @@ -1115,7 +1115,7 @@ Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 20 NULL 2 Using where; Using MRR Warnings: Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 @@ -1983,7 +1983,7 @@ INSERT INTO t2 VALUES (1, 1, 2); # range estimates k is selected. EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref k,l,m,n k 5 const 66 Using where +1 SIMPLE t2 range k,l,m,n k 5 NULL 66 Using where; Using MRR DROP TABLE t1, t2; # # BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER diff --git a/mysql-test/r/select_all.result b/mysql-test/r/select_all.result index 4f76c62f66c..0709621b817 100644 --- a/mysql-test/r/select_all.result +++ b/mysql-test/r/select_all.result @@ -2363,7 +2363,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 NULL +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2371,7 +2371,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 NULL +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) select * from t1 left join t2 on a=c where d = 4; a b c d @@ -2718,7 +2718,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort +1 SIMPLE t1 range b,c b 5 NULL 1 Using index condition; Using MRR; Using temporary; Using filesort 1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (Block Nested Loop) 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) DROP TABLE t1,t2,t3; @@ -3540,7 +3540,7 @@ WHERE t1.id=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 1 SIMPLE t2 const idx1 NULL NULL NULL 1 NULL -1 SIMPLE t3 ref idx1 idx1 5 const 3 NULL +1 SIMPLE t3 range idx1 idx1 5 NULL 3 Using index condition; Using MRR SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id WHERE t1.id=2; id a b c d e @@ -3677,12 +3677,12 @@ COUNT(*) 2 EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 ( ID1_with_null int NULL, @@ -3714,34 +3714,34 @@ COUNT(*) EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/r/select_all_bka.result b/mysql-test/r/select_all_bka.result index 0f411eb35ac..7a2192973a7 100644 --- a/mysql-test/r/select_all_bka.result +++ b/mysql-test/r/select_all_bka.result @@ -2364,7 +2364,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 NULL +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2372,7 +2372,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 NULL +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) select * from t1 left join t2 on a=c where d = 4; a b c d @@ -2719,7 +2719,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort +1 SIMPLE t1 range b,c b 5 NULL 1 Using index condition; Using MRR; Using temporary; Using filesort 1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (Block Nested Loop) 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) DROP TABLE t1,t2,t3; @@ -3541,7 +3541,7 @@ WHERE t1.id=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 1 SIMPLE t2 const idx1 NULL NULL NULL 1 NULL -1 SIMPLE t3 ref idx1 idx1 5 const 3 NULL +1 SIMPLE t3 range idx1 idx1 5 NULL 3 Using index condition; Using MRR SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id WHERE t1.id=2; id a b c d e @@ -3678,12 +3678,12 @@ COUNT(*) 2 EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 ( ID1_with_null int NULL, @@ -3715,34 +3715,34 @@ COUNT(*) EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/r/select_all_bka_nixbnl.result b/mysql-test/r/select_all_bka_nixbnl.result index c67da491f07..6068f71ca58 100644 --- a/mysql-test/r/select_all_bka_nixbnl.result +++ b/mysql-test/r/select_all_bka_nixbnl.result @@ -2364,7 +2364,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t1 ref a a 5 test.t2.c 2 Using join buffer (Batched Key Access) select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2372,7 +2372,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t1 ref a a 5 test.t2.c 2 Using join buffer (Batched Key Access) select * from t1 left join t2 on a=c where d = 4; a b c d @@ -2719,7 +2719,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort +1 SIMPLE t1 range b,c b 5 NULL 1 Using index condition; Using MRR; Using temporary; Using filesort 1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) DROP TABLE t1,t2,t3; @@ -3541,7 +3541,7 @@ WHERE t1.id=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 1 SIMPLE t2 const idx1 NULL NULL NULL 1 NULL -1 SIMPLE t3 ref idx1 idx1 5 const 3 NULL +1 SIMPLE t3 range idx1 idx1 5 NULL 3 Using index condition; Using MRR SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id WHERE t1.id=2; id a b c d e @@ -3678,12 +3678,12 @@ COUNT(*) 2 EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 ( ID1_with_null int NULL, @@ -3715,34 +3715,34 @@ COUNT(*) EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/r/select_icp_mrr.result b/mysql-test/r/select_icp_mrr.result index f78aefbed7b..d7cdd279f6c 100644 --- a/mysql-test/r/select_icp_mrr.result +++ b/mysql-test/r/select_icp_mrr.result @@ -2363,7 +2363,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 NULL +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2371,7 +2371,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 NULL +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) select * from t1 left join t2 on a=c where d = 4; a b c d @@ -2718,7 +2718,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort +1 SIMPLE t1 range b,c b 5 NULL 1 Using index condition; Using MRR; Using temporary; Using filesort 1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (Block Nested Loop) 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) DROP TABLE t1,t2,t3; @@ -3540,7 +3540,7 @@ WHERE t1.id=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 1 SIMPLE t2 const idx1 NULL NULL NULL 1 NULL -1 SIMPLE t3 ref idx1 idx1 5 const 3 NULL +1 SIMPLE t3 range idx1 idx1 5 NULL 3 Using index condition; Using MRR SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id WHERE t1.id=2; id a b c d e @@ -3677,12 +3677,12 @@ COUNT(*) 2 EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 ( ID1_with_null int NULL, @@ -3714,34 +3714,34 @@ COUNT(*) EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/r/select_icp_mrr_bka.result b/mysql-test/r/select_icp_mrr_bka.result index eb652487533..b77df1ca808 100644 --- a/mysql-test/r/select_icp_mrr_bka.result +++ b/mysql-test/r/select_icp_mrr_bka.result @@ -2364,7 +2364,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 NULL +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2372,7 +2372,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 NULL +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using MRR 1 SIMPLE t1 ALL a NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) select * from t1 left join t2 on a=c where d = 4; a b c d @@ -2719,7 +2719,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort +1 SIMPLE t1 range b,c b 5 NULL 1 Using index condition; Using MRR; Using temporary; Using filesort 1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index; Using join buffer (Block Nested Loop) 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) DROP TABLE t1,t2,t3; @@ -3541,7 +3541,7 @@ WHERE t1.id=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 1 SIMPLE t2 const idx1 NULL NULL NULL 1 NULL -1 SIMPLE t3 ref idx1 idx1 5 const 3 NULL +1 SIMPLE t3 range idx1 idx1 5 NULL 3 Using index condition; Using MRR SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id WHERE t1.id=2; id a b c d e @@ -3678,12 +3678,12 @@ COUNT(*) 2 EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 ( ID1_with_null int NULL, @@ -3715,34 +3715,34 @@ COUNT(*) EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/r/select_icp_mrr_bka_nixbnl.result b/mysql-test/r/select_icp_mrr_bka_nixbnl.result index cd592e0fc56..ff7819edbb7 100644 --- a/mysql-test/r/select_icp_mrr_bka_nixbnl.result +++ b/mysql-test/r/select_icp_mrr_bka_nixbnl.result @@ -2364,7 +2364,7 @@ insert into t1 values (1,2), (2,2), (3,2), (4,2); insert into t2 values (1,3), (2,3), (3,4), (4,4); explain select * from t1 left join t2 on a=c where d in (4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t1 ref a a 5 test.t2.c 2 Using join buffer (Batched Key Access) select * from t1 left join t2 on a=c where d in (4); a b c d @@ -2372,7 +2372,7 @@ a b c d 4 2 4 4 explain select * from t1 left join t2 on a=c where d = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref c,d d 5 const 2 Using where +1 SIMPLE t2 range c,d d 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t1 ref a a 5 test.t2.c 2 Using join buffer (Batched Key Access) select * from t1 left join t2 on a=c where d = 4; a b c d @@ -2719,7 +2719,7 @@ explain select straight_join DISTINCT t2.a,t2.b, t1.c from t1, t3, t2 where (t1.c=t2.a or (t1.c=t3.a and t2.a=t3.b)) and t1.b=556476786 and t2.b like '%%' order by t2.b limit 0,1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b,c b 5 const 1 Using temporary; Using filesort +1 SIMPLE t1 range b,c b 5 NULL 1 Using index condition; Using MRR; Using temporary; Using filesort 1 SIMPLE t3 index PRIMARY,a,b PRIMARY 8 NULL 2 Using index 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) DROP TABLE t1,t2,t3; @@ -3541,7 +3541,7 @@ WHERE t1.id=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL 1 SIMPLE t2 const idx1 NULL NULL NULL 1 NULL -1 SIMPLE t3 ref idx1 idx1 5 const 3 NULL +1 SIMPLE t3 range idx1 idx1 5 NULL 3 Using index condition; Using MRR SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id WHERE t1.id=2; id a b c d e @@ -3678,12 +3678,12 @@ COUNT(*) 2 EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 ( ID1_with_null int NULL, @@ -3715,34 +3715,34 @@ COUNT(*) EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP INDEX idx1 ON t1; CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null); EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null IS NULL ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 1 Using where +1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 1 Using index condition; Using where; Using MRR DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/r/subquery_all.result b/mysql-test/r/subquery_all.result index 1916b8ffd6c..bf7bf727917 100644 --- a/mysql-test/r/subquery_all.result +++ b/mysql-test/r/subquery_all.result @@ -1242,7 +1242,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where +1 PRIMARY t1 range salary salary 5 NULL 1 100.00 Using where; Using MRR 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (/* select#2 */ select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -6274,7 +6274,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6435,7 +6435,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6714,7 +6714,7 @@ t1 AS t1f STRAIGHT_JOIN t3 AS t3f; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1f ALL NULL NULL NULL NULL 2 NULL 1 PRIMARY t3f index NULL col_varchar_key 9 NULL 3 Using index; Using join buffer (Block Nested Loop) -2 DEPENDENT SUBQUERY t1s ref col_int_key col_int_key 5 const 1 Using where +2 DEPENDENT SUBQUERY t1s range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR 2 DEPENDENT SUBQUERY t3s index NULL col_int_key 5 NULL 3 Using index; Using join buffer (Block Nested Loop) DROP TABLE t1,t3; # diff --git a/mysql-test/r/subquery_all_bka.result b/mysql-test/r/subquery_all_bka.result index 99e3475ea24..73e4edb14eb 100644 --- a/mysql-test/r/subquery_all_bka.result +++ b/mysql-test/r/subquery_all_bka.result @@ -1243,7 +1243,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where +1 PRIMARY t1 range salary salary 5 NULL 1 100.00 Using where; Using MRR 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (/* select#2 */ select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -6275,7 +6275,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6436,7 +6436,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6715,7 +6715,7 @@ t1 AS t1f STRAIGHT_JOIN t3 AS t3f; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1f ALL NULL NULL NULL NULL 2 NULL 1 PRIMARY t3f index NULL col_varchar_key 9 NULL 3 Using index; Using join buffer (Block Nested Loop) -2 DEPENDENT SUBQUERY t1s ref col_int_key col_int_key 5 const 1 Using where +2 DEPENDENT SUBQUERY t1s range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR 2 DEPENDENT SUBQUERY t3s index NULL col_int_key 5 NULL 3 Using index; Using join buffer (Block Nested Loop) DROP TABLE t1,t3; # diff --git a/mysql-test/r/subquery_all_bka_nixbnl.result b/mysql-test/r/subquery_all_bka_nixbnl.result index 6e333e1f0aa..fa97394b5e0 100644 --- a/mysql-test/r/subquery_all_bka_nixbnl.result +++ b/mysql-test/r/subquery_all_bka_nixbnl.result @@ -1243,7 +1243,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where +1 PRIMARY t1 range salary salary 5 NULL 1 100.00 Using where; Using MRR 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (/* select#2 */ select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -6275,7 +6275,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6436,7 +6436,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6715,7 +6715,7 @@ t1 AS t1f STRAIGHT_JOIN t3 AS t3f; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1f ALL NULL NULL NULL NULL 2 NULL 1 PRIMARY t3f index NULL col_varchar_key 9 NULL 3 Using index -2 DEPENDENT SUBQUERY t1s ref col_int_key col_int_key 5 const 1 Using where +2 DEPENDENT SUBQUERY t1s range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR 2 DEPENDENT SUBQUERY t3s index NULL col_int_key 5 NULL 3 Using index DROP TABLE t1,t3; # diff --git a/mysql-test/r/subquery_nomat_nosj.result b/mysql-test/r/subquery_nomat_nosj.result index cfbaf0125b7..d7294392ff0 100644 --- a/mysql-test/r/subquery_nomat_nosj.result +++ b/mysql-test/r/subquery_nomat_nosj.result @@ -1242,7 +1242,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where +1 PRIMARY t1 range salary salary 5 NULL 1 100.00 Using where; Using MRR 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (/* select#2 */ select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -6274,7 +6274,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6435,7 +6435,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6714,7 +6714,7 @@ t1 AS t1f STRAIGHT_JOIN t3 AS t3f; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1f ALL NULL NULL NULL NULL 2 NULL 1 PRIMARY t3f index NULL col_varchar_key 9 NULL 3 Using index; Using join buffer (Block Nested Loop) -2 DEPENDENT SUBQUERY t1s ref col_int_key col_int_key 5 const 1 Using where +2 DEPENDENT SUBQUERY t1s range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR 2 DEPENDENT SUBQUERY t3s index NULL col_int_key 5 NULL 3 Using index; Using join buffer (Block Nested Loop) DROP TABLE t1,t3; # diff --git a/mysql-test/r/subquery_nomat_nosj_bka.result b/mysql-test/r/subquery_nomat_nosj_bka.result index 75bae58be9f..712d01c6731 100644 --- a/mysql-test/r/subquery_nomat_nosj_bka.result +++ b/mysql-test/r/subquery_nomat_nosj_bka.result @@ -1243,7 +1243,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where +1 PRIMARY t1 range salary salary 5 NULL 1 100.00 Using where; Using MRR 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (/* select#2 */ select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -6275,7 +6275,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6436,7 +6436,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6715,7 +6715,7 @@ t1 AS t1f STRAIGHT_JOIN t3 AS t3f; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1f ALL NULL NULL NULL NULL 2 NULL 1 PRIMARY t3f index NULL col_varchar_key 9 NULL 3 Using index; Using join buffer (Block Nested Loop) -2 DEPENDENT SUBQUERY t1s ref col_int_key col_int_key 5 const 1 Using where +2 DEPENDENT SUBQUERY t1s range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR 2 DEPENDENT SUBQUERY t3s index NULL col_int_key 5 NULL 3 Using index; Using join buffer (Block Nested Loop) DROP TABLE t1,t3; # diff --git a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result index 5dc81ff69e4..649378133b0 100644 --- a/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result +++ b/mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result @@ -1243,7 +1243,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where +1 PRIMARY t1 range salary salary 5 NULL 1 100.00 Using where; Using MRR 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (/* select#2 */ select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -6275,7 +6275,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6436,7 +6436,7 @@ INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL -2 DERIVED t1 ref a a 5 const 1 NULL +2 DERIVED t1 range a a 5 NULL 1 Using index condition; Using MRR EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); id select_type table type possible_keys key key_len ref rows Extra @@ -6715,7 +6715,7 @@ t1 AS t1f STRAIGHT_JOIN t3 AS t3f; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1f ALL NULL NULL NULL NULL 2 NULL 1 PRIMARY t3f index NULL col_varchar_key 9 NULL 3 Using index -2 DEPENDENT SUBQUERY t1s ref col_int_key col_int_key 5 const 1 Using where +2 DEPENDENT SUBQUERY t1s range col_int_key col_int_key 5 NULL 1 Using index condition; Using where; Using MRR 2 DEPENDENT SUBQUERY t3s index NULL col_int_key 5 NULL 3 Using index DROP TABLE t1,t3; # diff --git a/mysql-test/r/subquery_sj_all.result b/mysql-test/r/subquery_sj_all.result index 15a1b675f3b..e1c19cd33d4 100644 --- a/mysql-test/r/subquery_sj_all.result +++ b/mysql-test/r/subquery_sj_all.result @@ -6240,7 +6240,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer (Block Nested Loop) 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 1 NULL -2 MATERIALIZED t3 ref uid uid 5 const 1 Using where +2 MATERIALIZED t3 range uid uid 5 NULL 1 Using index condition; Using where; Using MRR; Using join buffer (Block Nested Loop) select t2.uid from t2, t1 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) and t2.uid=t1.fid; @@ -6874,7 +6874,7 @@ explain select name from t2, t1 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) and t2.uid=t1.fid; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref uid uid 5 const 4 Using where; Start temporary +1 SIMPLE t3 range uid uid 5 NULL 4 Using index condition; Using where; Using MRR; Start temporary 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index 1 SIMPLE t1 ref uid uid 5 test.t3.fid 2 Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1 End temporary @@ -7952,7 +7952,7 @@ WHERE t2.col_int_key = 1 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE <subquery2> eq_ref <auto_key> <auto_key> 9 test.t4.col_int_nokey,test.t4.col_varchar_key 1 NULL -2 MATERIALIZED t2 ref col_int_key col_int_key 5 const 3 Using where +2 MATERIALIZED t2 range col_int_key col_int_key 5 NULL 3 Using index condition; Using where; Using MRR 2 MATERIALIZED t1 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index SELECT * FROM t4 @@ -8113,9 +8113,9 @@ AND grandparent1.col_int_key <> 3 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using index condition; Using where +2 DEPENDENT SUBQUERY grandparent1 range col_int_key col_int_key 4 NULL 12 Using index condition; Using where; Using MRR 2 DEPENDENT SUBQUERY <subquery3> eq_ref <auto_key> <auto_key> 8 test.grandparent1.col_int_nokey,test.grandparent1.col_int_nokey 1 NULL -3 MATERIALIZED parent1 ref col_int_key col_int_key 4 func 2 NULL +3 MATERIALIZED parent1 range col_int_key col_int_key 4 NULL 12 Using where; Using MRR 3 MATERIALIZED parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop) explain format=json SELECT * FROM t3 WHERE g1 NOT IN @@ -8146,7 +8146,7 @@ EXPLAIN { "table": { "table_name": "grandparent1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "col_int_key" ], @@ -8155,12 +8155,10 @@ EXPLAIN "col_int_key" ], "key_length": "4", - "ref": [ - "func" - ], - "rows": 2, + "rows": 12, "filtered": 100, "index_condition": "(`test`.`grandparent1`.`col_int_key` <> 3)", + "using_MRR": true, "attached_condition": "(((`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`) and (<cache>('8') = `test`.`grandparent1`.`col_int_nokey`)) and ((`test`.`grandparent1`.`col_int_nokey` is not null) and (`test`.`grandparent1`.`col_int_nokey` is not null)))" } }, @@ -8182,7 +8180,7 @@ EXPLAIN { "table": { "table_name": "parent1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "col_int_key" ], @@ -8191,11 +8189,10 @@ EXPLAIN "col_int_key" ], "key_length": "4", - "ref": [ - "func" - ], - "rows": 2, - "filtered": 100 + "rows": 12, + "filtered": 100, + "using_MRR": true, + "attached_condition": "1" } }, { @@ -8328,7 +8325,7 @@ FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system PRIMARY,col_int_key NULL NULL NULL 1 NULL -1 SIMPLE t1a ref col_int_key col_int_key 4 const 1 NULL +1 SIMPLE t1a range col_int_key col_int_key 4 NULL 1 Using index condition; Using MRR 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t1a); Using join buffer (Block Nested Loop) 1 SIMPLE t1b ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) SELECT t1a.* @@ -10623,7 +10620,7 @@ EXPLAIN SELECT name FROM t2, t1 WHERE t1.uid IN (SELECT t4.uid FROM t4, t3 WHERE t3.uid=1 AND t4.uid=t3.fid) AND t2.uid=t1.fid; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref uid uid 5 const 4 Using where; Start temporary +1 SIMPLE t3 range uid uid 5 NULL 4 Using index condition; Using where; Using MRR; Start temporary 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index 1 SIMPLE t1 ALL uid NULL NULL NULL 11 Using where; End temporary; Using join buffer (Block Nested Loop) 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1 NULL @@ -10650,7 +10647,7 @@ Handler_read_key 16 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 -Handler_read_rnd 0 +Handler_read_rnd 4 Handler_read_rnd_next 12 DROP TABLE t1,t2,t3,t4; # End of test for Bug#18194196 diff --git a/mysql-test/r/subquery_sj_all_bka.result b/mysql-test/r/subquery_sj_all_bka.result index 40a4e06a887..0d921663cee 100644 --- a/mysql-test/r/subquery_sj_all_bka.result +++ b/mysql-test/r/subquery_sj_all_bka.result @@ -6243,7 +6243,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer (Block Nested Loop) 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 1 NULL -2 MATERIALIZED t3 ref uid uid 5 const 1 Using where; Using join buffer (Batched Key Access) +2 MATERIALIZED t3 range uid uid 5 NULL 1 Using index condition; Using where; Using MRR; Using join buffer (Block Nested Loop) select t2.uid from t2, t1 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) and t2.uid=t1.fid; @@ -6879,7 +6879,7 @@ explain select name from t2, t1 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) and t2.uid=t1.fid; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref uid uid 5 const 4 Using where; Start temporary +1 SIMPLE t3 range uid uid 5 NULL 4 Using index condition; Using where; Using MRR; Start temporary 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index 1 SIMPLE t1 ref uid uid 5 test.t3.fid 2 Using where; Using join buffer (Batched Key Access) 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1 End temporary; Using join buffer (Batched Key Access) @@ -7957,7 +7957,7 @@ WHERE t2.col_int_key = 1 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE <subquery2> eq_ref <auto_key> <auto_key> 9 test.t4.col_int_nokey,test.t4.col_varchar_key 1 NULL -2 MATERIALIZED t2 ref col_int_key col_int_key 5 const 3 Using where +2 MATERIALIZED t2 range col_int_key col_int_key 5 NULL 3 Using index condition; Using where; Using MRR 2 MATERIALIZED t1 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index SELECT * FROM t4 @@ -8118,9 +8118,9 @@ AND grandparent1.col_int_key <> 3 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using index condition; Using where +2 DEPENDENT SUBQUERY grandparent1 range col_int_key col_int_key 4 NULL 12 Using index condition; Using where; Using MRR 2 DEPENDENT SUBQUERY <subquery3> eq_ref <auto_key> <auto_key> 8 test.grandparent1.col_int_nokey,test.grandparent1.col_int_nokey 1 NULL -3 MATERIALIZED parent1 ref col_int_key col_int_key 4 func 2 NULL +3 MATERIALIZED parent1 range col_int_key col_int_key 4 NULL 12 Using where; Using MRR 3 MATERIALIZED parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop) explain format=json SELECT * FROM t3 WHERE g1 NOT IN @@ -8151,7 +8151,7 @@ EXPLAIN { "table": { "table_name": "grandparent1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "col_int_key" ], @@ -8160,12 +8160,10 @@ EXPLAIN "col_int_key" ], "key_length": "4", - "ref": [ - "func" - ], - "rows": 2, + "rows": 12, "filtered": 100, "index_condition": "(`test`.`grandparent1`.`col_int_key` <> 3)", + "using_MRR": true, "attached_condition": "(((`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`) and (<cache>('8') = `test`.`grandparent1`.`col_int_nokey`)) and ((`test`.`grandparent1`.`col_int_nokey` is not null) and (`test`.`grandparent1`.`col_int_nokey` is not null)))" } }, @@ -8187,7 +8185,7 @@ EXPLAIN { "table": { "table_name": "parent1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "col_int_key" ], @@ -8196,11 +8194,10 @@ EXPLAIN "col_int_key" ], "key_length": "4", - "ref": [ - "func" - ], - "rows": 2, - "filtered": 100 + "rows": 12, + "filtered": 100, + "using_MRR": true, + "attached_condition": "1" } }, { @@ -8333,7 +8330,7 @@ FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system PRIMARY,col_int_key NULL NULL NULL 1 NULL -1 SIMPLE t1a ref col_int_key col_int_key 4 const 1 NULL +1 SIMPLE t1a range col_int_key col_int_key 4 NULL 1 Using index condition; Using MRR 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t1a); Using join buffer (Block Nested Loop) 1 SIMPLE t1b ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) SELECT t1a.* @@ -10629,7 +10626,7 @@ EXPLAIN SELECT name FROM t2, t1 WHERE t1.uid IN (SELECT t4.uid FROM t4, t3 WHERE t3.uid=1 AND t4.uid=t3.fid) AND t2.uid=t1.fid; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref uid uid 5 const 4 Using where; Start temporary +1 SIMPLE t3 range uid uid 5 NULL 4 Using index condition; Using where; Using MRR; Start temporary 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index 1 SIMPLE t1 ALL uid NULL NULL NULL 11 Using where; End temporary; Using join buffer (Block Nested Loop) 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1 Using join buffer (Batched Key Access) @@ -10656,7 +10653,7 @@ Handler_read_key 16 Handler_read_last 0 Handler_read_next 15 Handler_read_prev 0 -Handler_read_rnd 11 +Handler_read_rnd 15 Handler_read_rnd_next 12 DROP TABLE t1,t2,t3,t4; # End of test for Bug#18194196 diff --git a/mysql-test/r/subquery_sj_all_bka_nixbnl.result b/mysql-test/r/subquery_sj_all_bka_nixbnl.result index d2c62e5b707..c54af1095d4 100644 --- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result +++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result @@ -6253,7 +6253,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 NULL 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 1 NULL -2 MATERIALIZED t3 ref uid uid 5 const 1 Using where; Using join buffer (Batched Key Access) +2 MATERIALIZED t3 range uid uid 5 NULL 1 Using index condition; Using where; Using MRR select t2.uid from t2, t1 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) and t2.uid=t1.fid; @@ -6889,7 +6889,7 @@ explain select name from t2, t1 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) and t2.uid=t1.fid; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref uid uid 5 const 4 Using where; Start temporary +1 SIMPLE t3 range uid uid 5 NULL 4 Using index condition; Using where; Using MRR; Start temporary 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index 1 SIMPLE t1 ref uid uid 5 test.t3.fid 2 Using where; Using join buffer (Batched Key Access) 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1 End temporary; Using join buffer (Batched Key Access) @@ -7967,7 +7967,7 @@ WHERE t2.col_int_key = 1 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE <subquery2> eq_ref <auto_key> <auto_key> 9 test.t4.col_int_nokey,test.t4.col_varchar_key 1 NULL -2 MATERIALIZED t2 ref col_int_key col_int_key 5 const 3 Using where +2 MATERIALIZED t2 range col_int_key col_int_key 5 NULL 3 Using index condition; Using where; Using MRR 2 MATERIALIZED t1 ref col_varchar_key col_varchar_key 4 test.t2.col_varchar_key 2 Using index SELECT * FROM t4 @@ -8129,9 +8129,9 @@ AND grandparent1.col_int_key <> 3 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using index condition; Using where +2 DEPENDENT SUBQUERY grandparent1 range col_int_key col_int_key 4 NULL 12 Using index condition; Using where; Using MRR 2 DEPENDENT SUBQUERY <subquery3> eq_ref <auto_key> <auto_key> 8 test.grandparent1.col_int_nokey,test.grandparent1.col_int_nokey 1 NULL -3 MATERIALIZED parent1 ref col_int_key col_int_key 4 func 2 NULL +3 MATERIALIZED parent1 range col_int_key col_int_key 4 NULL 12 Using where; Using MRR 3 MATERIALIZED parent2 ref col_int_key col_int_key 4 test.parent1.col_int_nokey 2 Using index explain format=json SELECT * FROM t3 WHERE g1 NOT IN @@ -8162,7 +8162,7 @@ EXPLAIN { "table": { "table_name": "grandparent1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "col_int_key" ], @@ -8171,12 +8171,10 @@ EXPLAIN "col_int_key" ], "key_length": "4", - "ref": [ - "func" - ], - "rows": 2, + "rows": 12, "filtered": 100, "index_condition": "(`test`.`grandparent1`.`col_int_key` <> 3)", + "using_MRR": true, "attached_condition": "(((`test`.`grandparent1`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`) and (<cache>('8') = `test`.`grandparent1`.`col_int_nokey`)) and ((`test`.`grandparent1`.`col_int_nokey` is not null) and (`test`.`grandparent1`.`col_int_nokey` is not null)))" } }, @@ -8198,7 +8196,7 @@ EXPLAIN { "table": { "table_name": "parent1", - "access_type": "ref", + "access_type": "range", "possible_keys": [ "col_int_key" ], @@ -8207,11 +8205,10 @@ EXPLAIN "col_int_key" ], "key_length": "4", - "ref": [ - "func" - ], - "rows": 2, - "filtered": 100 + "rows": 12, + "filtered": 100, + "using_MRR": true, + "attached_condition": "1" } }, { @@ -8345,7 +8342,7 @@ FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system PRIMARY,col_int_key NULL NULL NULL 1 NULL -1 SIMPLE t1a ref col_int_key col_int_key 4 const 1 NULL +1 SIMPLE t1a range col_int_key col_int_key 4 NULL 1 Using index condition; Using MRR 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t1a) 1 SIMPLE t1b ALL NULL NULL NULL NULL 4 Using where SELECT t1a.* @@ -10638,7 +10635,7 @@ EXPLAIN SELECT name FROM t2, t1 WHERE t1.uid IN (SELECT t4.uid FROM t4, t3 WHERE t3.uid=1 AND t4.uid=t3.fid) AND t2.uid=t1.fid; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref uid uid 5 const 4 Using where; Start temporary +1 SIMPLE t3 range uid uid 5 NULL 4 Using index condition; Using where; Using MRR; Start temporary 1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index 1 SIMPLE t1 ref uid uid 5 test.t3.fid 4 Using where; End temporary; Using join buffer (Batched Key Access) 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1 Using join buffer (Batched Key Access) @@ -10665,7 +10662,7 @@ Handler_read_key 20 Handler_read_last 0 Handler_read_next 26 Handler_read_prev 0 -Handler_read_rnd 22 +Handler_read_rnd 26 Handler_read_rnd_next 0 DROP TABLE t1,t2,t3,t4; # End of test for Bug#18194196