diff options
44 files changed, 700 insertions, 119 deletions
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index e77cb220375..f412843ded0 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -852,3 +852,25 @@ SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +--echo # check "Handler_pushed" status varuiables +CREATE TABLE t1 ( + c1 CHAR(1), + c2 CHAR(1), + KEY (c1) +); + +INSERT INTO t1 VALUES ('3', '3'),('4','4'),('5','5'); + +flush status; +show status like "Handler_pushed%"; + +SELECT * FROM t1 FORCE INDEX(c1) WHERE (c1='3' or c1='4') and c1 % 2 = 0 ; + +show status like "Handler_pushed%"; + +SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ; + +show status like "Handler_pushed%"; + +DROP TABLE t1; + diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index 721d4277775..c5376bee756 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -273,4 +273,13 @@ ON alias3.f4 != 0 ) ON alias3.f4 != 0; f4 f4 f2 f4 drop table t1,t2,t3,t4; +# +# LP BUG#910123 MariaDB 5.3.3 causes 1093 error on Drupal +# Fix: force materialization in case of conflict +# +SET optimizer_switch='derived_merge=on'; +CREATE TABLE t1 ( i INT ); +INSERT INTO t1 VALUES ( (SELECT 1 FROM ( SELECT * FROM t1 ) as a) ); +drop table t1; +set optimizer_switch=@save_optimizer_switch; set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index 0f9da2ea3b6..50b0147b6ad 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -549,7 +549,7 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using index condition; Using where +1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 3b69791d373..9ab7ed4eec7 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -783,7 +783,7 @@ create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); explain select * from t1 where a > 0 and a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index 08238289330..d8e41113f63 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -167,7 +167,7 @@ WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where DROP TABLE t1; # @@ -431,7 +431,7 @@ SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition +2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using where 2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); @@ -452,7 +452,7 @@ PRIMARY KEY (pk) INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; SELECT pk, c1 FROM t1 WHERE pk <> 3; pk c1 @@ -507,8 +507,8 @@ SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using join buffer (flat, BNL join) SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); @@ -680,7 +680,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; @@ -793,7 +793,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where 1 PRIMARY t2 ref g g 5 test.t.c 9 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY d 3 NULL 64 Using where; Using index -2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index condition; Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) @@ -808,5 +808,32 @@ COUNT(*) 1478 SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +# check "Handler_pushed" status varuiables +CREATE TABLE t1 ( +c1 CHAR(1), +c2 CHAR(1), +KEY (c1) +); +INSERT INTO t1 VALUES ('3', '3'),('4','4'),('5','5'); +flush status; +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 0 +Handler_pushed_index_cond_filtered 0 +SELECT * FROM t1 FORCE INDEX(c1) WHERE (c1='3' or c1='4') and c1 % 2 = 0 ; +c1 c2 +4 4 +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 2 +Handler_pushed_index_cond_filtered 1 +SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ; +c1 c2 +4 4 +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 2 +Handler_pushed_index_cond_filtered 1 +DROP TABLE t1; set optimizer_switch=@innodb_icp_tmp; set storage_engine= @save_storage_engine; diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result index 81536f2a43b..90f59b96e61 100644 --- a/mysql-test/r/innodb_mrr_cpk.result +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -82,7 +82,7 @@ insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020) explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered scan +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; a b filler a b a-1010=A 1010 filler a-1010=A 1010 @@ -91,7 +91,7 @@ a-1030=A 1030 filler a-1030=A 1030 explain select * from t1, t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where -1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered scan +1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a; a b filler a b a-1010=A 1010 filler a-1010=A 1010 @@ -133,7 +133,7 @@ set join_cache_level=6; explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where -1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered scan +1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; a b c filler a b set optimizer_switch='index_condition_pushdown=off'; diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index dda0f4c8f66..6908f40e854 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -3506,6 +3506,7 @@ insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), (4,4, 'qwerty'); +flush status; set join_cache_level=5; select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; @@ -3519,6 +3520,10 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 20 +Handler_pushed_index_cond_filtered 16 set join_cache_level=6; select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; @@ -3532,6 +3537,10 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 40 +Handler_pushed_index_cond_filtered 32 set join_cache_level=7; select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; @@ -3545,6 +3554,10 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 60 +Handler_pushed_index_cond_filtered 48 set join_cache_level=8; select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; @@ -3558,6 +3571,10 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 80 +Handler_pushed_index_cond_filtered 64 drop table t1,t2; set join_cache_level=default; # diff --git a/mysql-test/r/maria_icp.result b/mysql-test/r/maria_icp.result index 2d2b4d0c1f7..b9d3ca6c7c0 100644 --- a/mysql-test/r/maria_icp.result +++ b/mysql-test/r/maria_icp.result @@ -814,5 +814,32 @@ COUNT(*) 1478 SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +# check "Handler_pushed" status varuiables +CREATE TABLE t1 ( +c1 CHAR(1), +c2 CHAR(1), +KEY (c1) +); +INSERT INTO t1 VALUES ('3', '3'),('4','4'),('5','5'); +flush status; +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 0 +Handler_pushed_index_cond_filtered 0 +SELECT * FROM t1 FORCE INDEX(c1) WHERE (c1='3' or c1='4') and c1 % 2 = 0 ; +c1 c2 +4 4 +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 2 +Handler_pushed_index_cond_filtered 1 +SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ; +c1 c2 +4 4 +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 2 +Handler_pushed_index_cond_filtered 1 +DROP TABLE t1; set storage_engine= @save_storage_engine; set optimizer_switch=@maria_icp_tmp; diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result index c480e35df42..3171753adb4 100644 --- a/mysql-test/r/myisam_icp.result +++ b/mysql-test/r/myisam_icp.result @@ -812,6 +812,33 @@ COUNT(*) 1478 SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; +# check "Handler_pushed" status varuiables +CREATE TABLE t1 ( +c1 CHAR(1), +c2 CHAR(1), +KEY (c1) +); +INSERT INTO t1 VALUES ('3', '3'),('4','4'),('5','5'); +flush status; +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 0 +Handler_pushed_index_cond_filtered 0 +SELECT * FROM t1 FORCE INDEX(c1) WHERE (c1='3' or c1='4') and c1 % 2 = 0 ; +c1 c2 +4 4 +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 2 +Handler_pushed_index_cond_filtered 1 +SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ; +c1 c2 +4 4 +show status like "Handler_pushed%"; +Variable_name Value +Handler_pushed_index_cond_checks 2 +Handler_pushed_index_cond_filtered 1 +DROP TABLE t1; drop table if exists t0, t1, t1i, t1m; # # BUG#826935 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index 57862ded4f2..c42f80f0e85 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -328,15 +328,15 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using where EXPLAIN SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using where EXPLAIN SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 1198 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 1198 Using where EXPLAIN SELECT * FROM City WHERE Country > 'A' AND Country < 'ARG'; id select_type table type possible_keys key key_len ref rows Extra @@ -355,7 +355,7 @@ WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND (Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using index condition; Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using where EXPLAIN SELECT * FROM City WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) @@ -369,7 +369,7 @@ WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 200) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using index condition; Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using where SELECT * FROM City USE INDEX () WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND @@ -601,11 +601,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3400 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 400 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 400 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'P%'; id select_type table type possible_keys key key_len ref rows Extra @@ -765,27 +765,27 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4025 AND 4035; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4028 AND 4032; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3500 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 300 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 300 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4000 AND 4300; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 250 and 260 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra @@ -1422,7 +1422,7 @@ SELECT * FROM t1 WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND (t1.c=0 OR t1.a=500); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using index condition; Using where +1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using where SELECT * FROM t1 WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND (t1.c=0 OR t1.a=500); diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index b0744726390..e75cabe0e58 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -276,6 +276,8 @@ Handler_commit 0 Handler_delete 0 Handler_discover 0 Handler_prepare 0 +Handler_pushed_index_cond_checks 0 +Handler_pushed_index_cond_filtered 0 Handler_read_first 0 Handler_read_key 4 Handler_read_next 0 @@ -297,7 +299,7 @@ Created_tmp_files 0 Created_tmp_tables 2 Handler_tmp_update 2 Handler_tmp_write 7 -Rows_tmp_read 35 +Rows_tmp_read 37 drop table t1; CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM; insert into t1 values (1),(2),(3),(4),(5); @@ -311,6 +313,8 @@ Handler_commit 0 Handler_delete 0 Handler_discover 0 Handler_prepare 0 +Handler_pushed_index_cond_checks 0 +Handler_pushed_index_cond_filtered 0 Handler_read_first 0 Handler_read_key 2 Handler_read_next 2 diff --git a/mysql-test/r/status_user.result b/mysql-test/r/status_user.result index 17c44df1d3c..175839b2098 100644 --- a/mysql-test/r/status_user.result +++ b/mysql-test/r/status_user.result @@ -101,6 +101,8 @@ Handler_commit 19 Handler_delete 1 Handler_discover 0 Handler_prepare 18 +Handler_pushed_index_cond_checks 0 +Handler_pushed_index_cond_filtered 0 Handler_read_first 0 Handler_read_key 3 Handler_read_next 0 diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index dd358ced14a..769373429da 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2753,6 +2753,129 @@ DROP table t1, t2; set @@optimizer_switch= @os_912513; set @@join_cache_level= @jcl_912513; # End +# +# BUG#934342: outer join + semijoin materialization +# + join_cache_level > 2 +# +CREATE TABLE t1 (a varchar(1), b varchar(1), INDEX idx_a(a) ); +INSERT INTO t1 VALUES ('v','v'), ('w','w'), ('t','t'); +CREATE TABLE t2 (c varchar(1), INDEX idx_c(c) ); +INSERT INTO t2 VALUES ('v'), ('v'), ('s'), ('j'); +CREATE TABLE t3 (c varchar(1), d varchar(1), INDEX idx_c(c) ); +INSERT INTO t3 VALUES ('v','v'), ('v','v'), ('s','s'), ('j','j'); +INSERT INTO t3 VALUES ('m','m'), ('d','d'), ('k','k'), ('m','m'); +set @tmp_otimizer_switch= @@optimizer_switch; +set @tmp_join_cache_level=@@join_cache_level; +set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on'; +set join_cache_level=0; +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) +WHERE (a, b) IN (SELECT a, b FROM t1 t); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx_a NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY t2 ref idx_c idx_c 4 test.t1.b 2 Using where; Using index +2 MATERIALIZED t ALL idx_a NULL NULL NULL 3 +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) +WHERE (a, b) IN (SELECT a, b FROM t1 t); +a b c +v v v +v v v +w w NULL +t t NULL +EXPLAIN +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) +WHERE (a, b) IN (SELECT a, b FROM t1 t); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx_a NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY t3 ref idx_c idx_c 4 test.t1.b 2 Using where +2 MATERIALIZED t ALL idx_a NULL NULL NULL 3 +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) +WHERE (a, b) IN (SELECT a, b FROM t1 t); +a b c d +v v v v +v v v v +w w NULL NULL +t t NULL NULL +set join_cache_level=6; +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) +WHERE (a, b) IN (SELECT a, b FROM t1 t); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx_a NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY t2 ref idx_c idx_c 4 test.t1.b 2 Using where; Using index +2 MATERIALIZED t ALL idx_a NULL NULL NULL 3 +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) +WHERE (a, b) IN (SELECT a, b FROM t1 t); +a b c +v v v +v v v +w w NULL +t t NULL +EXPLAIN +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) +WHERE (a, b) IN (SELECT a, b FROM t1 t); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx_a NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 +1 PRIMARY t3 ref idx_c idx_c 4 test.t1.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +2 MATERIALIZED t ALL idx_a NULL NULL NULL 3 +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) +WHERE (a, b) IN (SELECT a, b FROM t1 t); +a b c d +v v v v +v v v v +w w NULL NULL +t t NULL NULL +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@tmp_join_cache_level; +DROP TABLE t1,t2,t3; +# End +# +# BUG#934348: GROUP BY with HAVING + semijoin materialization +# + join_cache_level > 2 +# +CREATE TABLE t1 (a varchar(1), INDEX idx_a(a)); +INSERT INTO t1 VALUES ('c'), ('v'), ('c'); +CREATE TABLE t2 (b varchar(1)); +INSERT INTO t2 VALUES ('v'), ('c'); +set @tmp_otimizer_switch= @@optimizer_switch; +set @tmp_join_cache_level=@@join_cache_level; +set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on'; +set join_cache_level=0; +EXPLAIN +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) +GROUP BY a HAVING a != 'z'; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) +GROUP BY a HAVING a != 'z'; +a +c +v +set join_cache_level=6; +EXPLAIN +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) +GROUP BY a HAVING a != 'z'; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) +GROUP BY a HAVING a != 'z'; +a +c +v +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@tmp_join_cache_level; +DROP TABLE t1,t2; +# End set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index e98d3931020..80ff21230fb 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -778,7 +778,7 @@ create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); explain select * from t1 where a > 0 and a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); diff --git a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result index 30e58aacff5..2cb761c6ea1 100644 --- a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result @@ -121,7 +121,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using index condition; Using where +Extra Using where id 2 select_type DERIVED table NULL @@ -328,7 +328,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using index condition; Using where +Extra Using where id 2 select_type DERIVED table NULL diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 4dcce4d0373..62dbc849241 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2839,7 +2839,7 @@ SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using index condition; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using filesort 1 SIMPLE t2 ref a a 5 test.t1.pk 1 Using index SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 diff --git a/mysql-test/suite/maria/r/maria-gis-recovery.result b/mysql-test/suite/maria/r/maria-gis-recovery.result new file mode 100644 index 00000000000..ca406fc46e5 --- /dev/null +++ b/mysql-test/suite/maria/r/maria-gis-recovery.result @@ -0,0 +1,64 @@ +set global aria_log_file_size=4294967295; +drop database if exists mysqltest; +create database mysqltest; +use mysqltest; +* shut down mysqld, removed logs, restarted it +CREATE TABLE t1 ( +i int, +shape GEOMETRY NOT NULL, +SPATIAL (shape) +) ENGINE=ARIA; +insert into t1 values(1,POINT(1, 1)); +* copied t1 for feeding_recovery +insert into t1 values(2,POINT(2, 2)), (3,POINT(3, 3)), (4,POINT(4, 4)); +flush table t1; +* copied t1 for comparison +SET SESSION debug="+d,maria_flush_whole_log,maria_crash"; +* crashing mysqld intentionally +set global aria_checkpoint_interval=1; +ERROR HY000: Lost connection to MySQL server during query +* copied t1 back for feeding_recovery +* recovery happens +check table t1 extended; +Table Op Msg_type Msg_text +mysqltest.t1 check status OK +* testing that checksum after recovery is as expected +Checksum-check +ok +use mysqltest; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + `shape` geometry NOT NULL, + SPATIAL KEY `shape` (`shape`) +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +* TEST of UPDATE vs state.auto_increment +* copied t1 for feeding_recovery +update t1 set shape=POINT(5, 5) where i=2; +flush table t1; +* copied t1 for comparison +SET SESSION debug="+d,maria_flush_whole_log,maria_crash"; +* crashing mysqld intentionally +set global aria_checkpoint_interval=1; +ERROR HY000: Lost connection to MySQL server during query +* copied t1 back for feeding_recovery +* recovery happens +check table t1 extended; +Table Op Msg_type Msg_text +mysqltest.t1 check status OK +* testing that checksum after recovery is as expected +Checksum-check +ok +use mysqltest; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + `shape` geometry NOT NULL, + SPATIAL KEY `shape` (`shape`) +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +drop table t1; +drop database mysqltest_for_feeding_recovery; +drop database mysqltest_for_comparison; +drop database mysqltest; diff --git a/mysql-test/suite/maria/t/maria-gis-recovery-master.opt b/mysql-test/suite/maria/t/maria-gis-recovery-master.opt new file mode 100644 index 00000000000..58d0d012c54 --- /dev/null +++ b/mysql-test/suite/maria/t/maria-gis-recovery-master.opt @@ -0,0 +1 @@ +--skip-stack-trace --skip-core-file --loose-aria-log-dir-path=$MYSQLTEST_VARDIR/tmp diff --git a/mysql-test/suite/maria/t/maria-gis-recovery.test b/mysql-test/suite/maria/t/maria-gis-recovery.test new file mode 100644 index 00000000000..c40cc3788de --- /dev/null +++ b/mysql-test/suite/maria/t/maria-gis-recovery.test @@ -0,0 +1,64 @@ +--source include/not_embedded.inc +# Don't test this under valgrind, memory leaks will occur as we crash +--source include/not_valgrind.inc +# Binary must be compiled with debug for crash to occur +--source include/have_debug.inc +--source include/have_maria.inc +--source include/have_geometry.inc + +set global aria_log_file_size=4294967295; +let $MARIA_LOG=../../tmp; + +--disable_warnings +drop database if exists mysqltest; +--enable_warnings +create database mysqltest; +let $mms_tname=t; + +# Include scripts can perform SQL. For it to not influence the main test +# they use a separate connection. This way if they use a DDL it would +# not autocommit in the main test. +connect (admin, localhost, root,,mysqltest,,); +--enable_reconnect + +connection default; +use mysqltest; +--enable_reconnect + +-- source include/maria_empty_logs.inc +let $mms_tables=1; +CREATE TABLE t1 ( + i int, + shape GEOMETRY NOT NULL, + SPATIAL (shape) +) ENGINE=ARIA; +insert into t1 values(1,POINT(1, 1)); +-- source include/maria_make_snapshot_for_feeding_recovery.inc +insert into t1 values(2,POINT(2, 2)), (3,POINT(3, 3)), (4,POINT(4, 4)); +-- source include/maria_make_snapshot_for_comparison.inc +let $mvr_restore_old_snapshot=1; +let $mms_compare_physically=0; +let $mvr_debug_option="+d,maria_flush_whole_log,maria_crash"; +let $mvr_crash_statement= set global aria_checkpoint_interval=1; +-- source include/maria_verify_recovery.inc +show create table t1; + +# Test that UPDATE's effect on auto-increment is recovered +--echo * TEST of UPDATE vs state.auto_increment +-- source include/maria_make_snapshot_for_feeding_recovery.inc +update t1 set shape=POINT(5, 5) where i=2; +-- source include/maria_make_snapshot_for_comparison.inc +let $mvr_restore_old_snapshot=1; +let $mms_compare_physically=0; +let $mvr_debug_option="+d,maria_flush_whole_log,maria_crash"; +let $mvr_crash_statement= set global aria_checkpoint_interval=1; +-- source include/maria_verify_recovery.inc +show create table t1; +drop table t1; + +# clean up everything +let $mms_purpose=feeding_recovery; +eval drop database mysqltest_for_$mms_purpose; +let $mms_purpose=comparison; +eval drop database mysqltest_for_$mms_purpose; +drop database mysqltest; diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index be84fa74c2e..14cf325feb1 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -152,7 +152,7 @@ a b c 2 -2 -2 explain select * from t3 where a between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> select * from t3 where b between -2 and -1; a b c @@ -176,7 +176,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> select * from t3 where a between 1 and 2 order by c; a b c @@ -184,7 +184,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol> select * from t3 where b between -2 and -1 order by a; a b c diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test index 42f3ce296e1..c2f831036e1 100644 --- a/mysql-test/t/derived_opt.test +++ b/mysql-test/t/derived_opt.test @@ -202,5 +202,15 @@ RIGHT JOIN ( drop table t1,t2,t3,t4; +--echo # +--echo # LP BUG#910123 MariaDB 5.3.3 causes 1093 error on Drupal +--echo # Fix: force materialization in case of conflict +--echo # +SET optimizer_switch='derived_merge=on'; +CREATE TABLE t1 ( i INT ); +INSERT INTO t1 VALUES ( (SELECT 1 FROM ( SELECT * FROM t1 ) as a) ); +drop table t1; +set optimizer_switch=@save_optimizer_switch; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 9d7b07f6b9e..6cb8f48bd70 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -1526,12 +1526,14 @@ insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), (4,4, 'qwerty'); +flush status; set join_cache_level=5; select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; explain select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_pushed%"; set join_cache_level=6; select t2.f1, t2.f2, t2.f3 from t1,t2 @@ -1539,6 +1541,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; explain select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_pushed%"; set join_cache_level=7; select t2.f1, t2.f2, t2.f3 from t1,t2 @@ -1546,6 +1549,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; explain select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_pushed%"; set join_cache_level=8; select t2.f1, t2.f2, t2.f3 from t1,t2 @@ -1553,6 +1557,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; explain select t2.f1, t2.f2, t2.f3 from t1,t2 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_pushed%"; drop table t1,t2; set join_cache_level=default; diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test index 6d563cab3d3..e86ed017951 100644 --- a/mysql-test/t/subselect_sj_jcl6.test +++ b/mysql-test/t/subselect_sj_jcl6.test @@ -116,6 +116,98 @@ set @@join_cache_level= @jcl_912513; --echo # End +--echo # +--echo # BUG#934342: outer join + semijoin materialization +--echo # + join_cache_level > 2 +--echo # + +CREATE TABLE t1 (a varchar(1), b varchar(1), INDEX idx_a(a) ); +INSERT INTO t1 VALUES ('v','v'), ('w','w'), ('t','t'); + +CREATE TABLE t2 (c varchar(1), INDEX idx_c(c) ); +INSERT INTO t2 VALUES ('v'), ('v'), ('s'), ('j'); + +CREATE TABLE t3 (c varchar(1), d varchar(1), INDEX idx_c(c) ); +INSERT INTO t3 VALUES ('v','v'), ('v','v'), ('s','s'), ('j','j'); +INSERT INTO t3 VALUES ('m','m'), ('d','d'), ('k','k'), ('m','m'); + +set @tmp_otimizer_switch= @@optimizer_switch; +set @tmp_join_cache_level=@@join_cache_level; +set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on'; + +set join_cache_level=0; + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); + +set join_cache_level=6; + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); +SELECT * FROM t1 LEFT JOIN t2 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); +SELECT * FROM t1 LEFT JOIN t3 ON (c = b) + WHERE (a, b) IN (SELECT a, b FROM t1 t); + +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@tmp_join_cache_level; + +DROP TABLE t1,t2,t3; + +--echo # End + +--echo # +--echo # BUG#934348: GROUP BY with HAVING + semijoin materialization +--echo # + join_cache_level > 2 +--echo # + +CREATE TABLE t1 (a varchar(1), INDEX idx_a(a)); +INSERT INTO t1 VALUES ('c'), ('v'), ('c'); + +CREATE TABLE t2 (b varchar(1)); +INSERT INTO t2 VALUES ('v'), ('c'); + +set @tmp_otimizer_switch= @@optimizer_switch; +set @tmp_join_cache_level=@@join_cache_level; +set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on'; + +set join_cache_level=0; + +EXPLAIN +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) + GROUP BY a HAVING a != 'z'; +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) + GROUP BY a HAVING a != 'z'; + +set join_cache_level=6; + +EXPLAIN +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) + GROUP BY a HAVING a != 'z'; +SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) + GROUP BY a HAVING a != 'z'; + +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@tmp_join_cache_level; + +DROP TABLE t1,t2; + +--echo # End + set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/sql/handler.cc b/sql/handler.cc index e165a16b544..4a8cd77ebc3 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -4555,6 +4555,27 @@ int handler::compare_key2(key_range *range) } +/** + ICP callback - to be called by an engine to check the pushed condition +*/ +extern "C" enum icp_result handler_index_cond_check(void* h_arg) +{ + handler *h= (handler*)h_arg; + THD *thd= h->table->in_use; + enum icp_result res; + + if (thd_killed(thd)) + return ICP_ABORTED_BY_USER; + + if (h->end_range && h->compare_key2(h->end_range) > 0) + return ICP_OUT_OF_RANGE; + h->increment_statistics(&SSV::ha_pushed_index_cond_checks); + if ((res= h->pushed_idx_cond->val_int()? ICP_MATCH : ICP_NO_MATCH) == + ICP_NO_MATCH) + h->increment_statistics(&SSV::ha_pushed_index_cond_filtered); + return res; +} + int handler::index_read_idx_map(uchar * buf, uint index, const uchar * key, key_part_map keypart_map, enum ha_rkey_function find_flag) diff --git a/sql/handler.h b/sql/handler.h index 87ac1ab9ef3..28d8a96a895 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -1553,6 +1553,8 @@ public: {} }; +extern "C" enum icp_result handler_index_cond_check(void* h_arg); + uint calculate_key_len(TABLE *, uint, const uchar *, key_part_map); /* bitmap with first N+1 bits set @@ -2632,6 +2634,8 @@ public: { return ht; } inline int ha_write_tmp_row(uchar *buf); inline int ha_update_tmp_row(const uchar * old_data, uchar * new_data); + + friend enum icp_result handler_index_cond_check(void* h_arg); }; #include "multi_range_read.h" diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 109d0220a4a..4e2fa473b8d 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -8291,6 +8291,8 @@ SHOW_VAR status_vars[]= { {"Handler_mrr_init", (char*) offsetof(STATUS_VAR, ha_multi_range_read_init_count), SHOW_LONG_STATUS}, #endif {"Handler_prepare", (char*) offsetof(STATUS_VAR, ha_prepare_count), SHOW_LONG_STATUS}, + {"Handler_pushed_index_cond_checks",(char*) offsetof(STATUS_VAR, ha_pushed_index_cond_checks), SHOW_LONG_STATUS}, + {"Handler_pushed_index_cond_filtered",(char*) offsetof(STATUS_VAR, ha_pushed_index_cond_filtered), SHOW_LONG_STATUS}, {"Handler_read_first", (char*) offsetof(STATUS_VAR, ha_read_first_count), SHOW_LONG_STATUS}, {"Handler_read_key", (char*) offsetof(STATUS_VAR, ha_read_key_count), SHOW_LONG_STATUS}, {"Handler_read_next", (char*) offsetof(STATUS_VAR, ha_read_next_count), SHOW_LONG_STATUS}, diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc index 5240267b4ac..c2b49d863a1 100644 --- a/sql/opt_index_cond_pushdown.cc +++ b/sql/opt_index_cond_pushdown.cc @@ -329,13 +329,23 @@ void push_index_cond(JOIN_TAB *tab, uint keyno) { DBUG_ENTER("push_index_cond"); Item *idx_cond; - + + /* + Backported the following from MySQL 5.6: + 6. The index is not a clustered index. The performance improvement + of pushing an index condition on a clustered key is much lower + than on a non-clustered key. This restriction should be + re-evaluated when WL#6061 is implemented. + */ if ((tab->table->file->index_flags(keyno, 0, 1) & HA_DO_INDEX_COND_PUSHDOWN) && optimizer_flag(tab->join->thd, OPTIMIZER_SWITCH_INDEX_COND_PUSHDOWN) && tab->join->thd->lex->sql_command != SQLCOM_UPDATE_MULTI && tab->join->thd->lex->sql_command != SQLCOM_DELETE_MULTI && - tab->type != JT_CONST && tab->type != JT_SYSTEM) + tab->type != JT_CONST && tab->type != JT_SYSTEM && + !(keyno == tab->table->s->primary_key && // (6) + tab->table->file->primary_key_is_clustered())) // (6) + { DBUG_EXECUTE("where", print_where(tab->select_cond, "full cond", QT_ORDINARY);); diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 8d4b2e971ad..e0841d3a696 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -552,6 +552,14 @@ public: increment_use_count(1); use_count++; } + void incr_refs_all() + { + for (SEL_ARG *pos=first(); pos ; pos=pos->next) + { + pos->increment_use_count(1); + } + use_count++; + } void free_tree() { for (SEL_ARG *pos=first(); pos ; pos=pos->next) @@ -1090,9 +1098,11 @@ int SEL_IMERGE::and_sel_tree(RANGE_OPT_PARAM *param, SEL_TREE *tree, for (SEL_TREE** or_tree= trees; or_tree != trees_next; or_tree++) { SEL_TREE *res_or_tree= 0; - if (!(res_or_tree= new SEL_TREE())) + SEL_TREE *and_tree= 0; + if (!(res_or_tree= new SEL_TREE()) || + !(and_tree= new SEL_TREE(tree, TRUE, param))) return (-1); - if (!and_range_trees(param, *or_tree, tree, res_or_tree)) + if (!and_range_trees(param, *or_tree, and_tree, res_or_tree)) { if (new_imerge->or_sel_tree(param, res_or_tree)) return (-1); @@ -1305,7 +1315,7 @@ SEL_TREE::SEL_TREE(SEL_TREE *arg, bool without_merges, for (uint idx= 0; idx < param->keys; idx++) { if ((keys[idx]= arg->keys[idx])) - keys[idx]->incr_refs(); + keys[idx]->incr_refs_all(); } if (without_merges) diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 9c5d251c728..11fd5db2020 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1707,11 +1707,12 @@ TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, t_name= table->table_name; t_alias= table->alias; +retry: DBUG_PRINT("info", ("real table: %s.%s", d_name, t_name)); - for (;;) + for (TABLE_LIST *tl= table_list;;) { - if (((! (res= find_table_in_global_list(table_list, d_name, t_name))) && - (! (res= mysql_lock_have_duplicate(thd, table, table_list)))) || + if (((! (res= find_table_in_global_list(tl, d_name, t_name))) && + (! (res= mysql_lock_have_duplicate(thd, table, tl)))) || ((!res->table || res->table != table->table) && (!check_alias || !(lower_case_table_names ? my_strcasecmp(files_charset_info, t_alias, res->alias) : @@ -1724,10 +1725,23 @@ TABLE_LIST* unique_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, processed in derived table or top select of multi-update/multi-delete (exclude_from_table_unique_test) or prelocking placeholder. */ - table_list= res->next_global; + tl= res->next_global; DBUG_PRINT("info", ("found same copy of table or table which we should skip")); } + if (res && res->belong_to_derived) + { + /* Try to fix */ + TABLE_LIST *derived= res->belong_to_derived; + if (derived->is_merged_derived()) + { + DBUG_PRINT("info", + ("convert merged to materialization to resolve the conflict")); + derived->change_refs_to_fields(); + derived->set_materialized_derived(); + } + goto retry; + } DBUG_RETURN(res); } diff --git a/sql/sql_class.h b/sql/sql_class.h index 58af7888385..c04af55a127 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -588,6 +588,8 @@ typedef struct system_status_var ulong ha_tmp_update_count; ulong ha_tmp_write_count; ulong ha_prepare_count; + ulong ha_pushed_index_cond_checks; + ulong ha_pushed_index_cond_filtered; ulong ha_discover_count; ulong ha_savepoint_count; ulong ha_savepoint_rollback_count; diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index 78f95a7ac7e..15b5efbfacb 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -2576,6 +2576,15 @@ void JOIN_CACHE::print_explain_comment(String *str) str->append(STRING_WITH_LEN(")")); } +/** + get thread handle. +*/ + +THD *JOIN_CACHE::thd() +{ + return join->thd; +} + static void add_mrr_explain_info(String *str, uint mrr_mode, handler *file) { @@ -4015,7 +4024,11 @@ bool bka_skip_index_tuple(range_seq_t rseq, range_id_t range_info) { DBUG_ENTER("bka_skip_index_tuple"); JOIN_CACHE_BKA *cache= (JOIN_CACHE_BKA *) rseq; - bool res= cache->skip_index_tuple(range_info); + THD *thd= cache->thd(); + bool res; + status_var_increment(thd->status_var.ha_pushed_index_cond_checks); + if ((res= cache->skip_index_tuple(range_info))) + status_var_increment(thd->status_var.ha_pushed_index_cond_filtered); DBUG_RETURN(res); } @@ -4490,7 +4503,12 @@ bool bkah_skip_index_tuple(range_seq_t rseq, range_id_t range_info) { DBUG_ENTER("bka_unique_skip_index_tuple"); JOIN_CACHE_BKAH *cache= (JOIN_CACHE_BKAH *) rseq; - DBUG_RETURN(cache->skip_index_tuple(range_info)); + THD *thd= cache->thd(); + bool res; + status_var_increment(thd->status_var.ha_pushed_index_cond_checks); + if ((res= cache->skip_index_tuple(range_info))) + status_var_increment(thd->status_var.ha_pushed_index_cond_filtered); + DBUG_RETURN(res); } diff --git a/sql/sql_join_cache.h b/sql/sql_join_cache.h index f5d64d5530a..ba8e4ba8e4a 100644 --- a/sql/sql_join_cache.h +++ b/sql/sql_join_cache.h @@ -643,6 +643,8 @@ public: /* Add a comment on the join algorithm employed by the join cache */ virtual void print_explain_comment(String *str); + THD *thd(); + virtual ~JOIN_CACHE() {} void reset_join(JOIN *j) { join= j; } void free() diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f29f51325a9..4a69cd3b1fa 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2885,6 +2885,7 @@ void st_lex::cleanup_after_one_table_open() if (all_selects_list != &select_lex) { derived_tables= 0; + select_lex.exclude_from_table_unique_test= false; /* cleunup underlying units (units of VIEW) */ for (SELECT_LEX_UNIT *un= select_lex.first_inner_unit(); un; diff --git a/sql/sql_list.h b/sql/sql_list.h index 873a8656ebe..adedd9a3a4d 100644 --- a/sql/sql_list.h +++ b/sql/sql_list.h @@ -257,7 +257,12 @@ public: last= &first; return tmp->info; } - inline void disjoin(base_list *list) + /* + Remove from this list elements that are contained in the passed list. + We assume that the passed list is a tail of this list (that is, the whole + list_node* elements are shared). + */ + inline void disjoin(const base_list *list) { list_node **prev= &first; list_node *node= first; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 547b0b256b3..f2720513b6f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1266,9 +1266,20 @@ JOIN::optimize() Item *ref_item= *ref_item_ptr; if (!ref_item->used_tables() && !(select_options & SELECT_DESCRIBE)) continue; - COND_EQUAL *equals= tab->first_inner ? tab->first_inner->cond_equal : - cond_equal; - ref_item= substitute_for_best_equal_field(tab, ref_item, equals, map2table); + COND_EQUAL *equals= cond_equal; + JOIN_TAB *first_inner= tab->first_inner; + while (equals) + { + ref_item= substitute_for_best_equal_field(tab, ref_item, + equals, map2table); + if (first_inner) + { + equals= first_inner->cond_equal; + first_inner= first_inner->first_upper; + } + else + equals= 0; + } ref_item->update_used_tables(); if (*ref_item_ptr != ref_item) { @@ -9213,7 +9224,7 @@ uint check_join_cache_usage(JOIN_TAB *tab, Check whether table tab and the previous one belong to the same nest of inner tables and if so do not use join buffer when joining table tab. */ - if (tab->first_inner) + if (tab->first_inner && tab != tab->first_inner) { for (JOIN_TAB *first_inner= tab[-1].first_inner; first_inner; @@ -9223,7 +9234,7 @@ uint check_join_cache_usage(JOIN_TAB *tab, goto no_join_cache; } } - else if (tab->first_sj_inner_tab && + else if (tab->first_sj_inner_tab && tab != tab->first_sj_inner_tab && tab->first_sj_inner_tab == tab[-1].first_sj_inner_tab) goto no_join_cache; } @@ -12696,9 +12707,8 @@ optimize_cond(JOIN *join, COND *conds, List<TABLE_LIST> *join_list, multiple equality contains a constant. */ DBUG_EXECUTE("where", print_where(conds, "original", QT_ORDINARY);); - conds= build_equal_items(join->thd, conds, NULL, join_list, - &join->cond_equal); - DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY);); + conds= build_equal_items(join->thd, conds, NULL, join_list, cond_equal); + DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY);); /* change field = field to field = const for each found field = const */ propagate_cond_constants(thd, (I_List<COND_CMP> *) 0, conds, conds); diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 90d9405ebe5..599dc993483 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -961,6 +961,7 @@ bool st_select_lex::cleanup() } non_agg_fields.empty(); inner_refs_list.empty(); + exclude_from_table_unique_test= FALSE; DBUG_RETURN(error); } diff --git a/sql/table.cc b/sql/table.cc index f1ff353a8c4..13e643f015b 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4378,6 +4378,36 @@ bool TABLE_LIST::prepare_security(THD *thd) DBUG_RETURN(FALSE); } +#ifndef DBUG_OFF +void TABLE_LIST::set_check_merged() +{ + DBUG_ASSERT(derived); + /* + It is not simple to check all, but at least this should be checked: + this select is not excluded or the exclusion came from above. + */ + DBUG_ASSERT(!derived->first_select()->exclude_from_table_unique_test || + derived->outer_select()-> + exclude_from_table_unique_test); +} +#endif + +void TABLE_LIST::set_check_materialized() +{ + DBUG_ASSERT(derived); + if (!derived->first_select()->exclude_from_table_unique_test) + derived->set_unique_exclude(); + else + { + /* + The subtree should be already excluded + */ + DBUG_ASSERT(!derived->first_select()->first_inner_unit() || + derived->first_select()->first_inner_unit()->first_select()-> + exclude_from_table_unique_test); + } +} + Natural_join_column::Natural_join_column(Field_translator *field_param, TABLE_LIST *tab) @@ -5919,8 +5949,9 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view) */ if (is_materialized_derived()) { - unit->master_unit()->set_unique_exclude(); + set_check_materialized(); } + /* Create field translation for mergeable derived tables/views. For derived tables field translation can be created only after diff --git a/sql/table.h b/sql/table.h index 8d91804eb06..8de71658493 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1747,16 +1747,18 @@ struct TABLE_LIST inline void set_merged_derived() { derived_type= ((derived_type & DTYPE_MASK) | - DTYPE_TABLE | DTYPE_MERGE); + DTYPE_TABLE | DTYPE_MERGE); + set_check_merged(); } inline bool is_materialized_derived() { return (derived_type & DTYPE_MATERIALIZE); } - inline void set_materialized_derived() + void set_materialized_derived() { derived_type= ((derived_type & DTYPE_MASK) | - DTYPE_TABLE | DTYPE_MATERIALIZE); + DTYPE_TABLE | DTYPE_MATERIALIZE); + set_check_materialized(); } inline bool is_multitable() { @@ -1802,6 +1804,12 @@ struct TABLE_LIST private: bool prep_check_option(THD *thd, uint8 check_opt_type); bool prep_where(THD *thd, Item **conds, bool no_where_clause); + void set_check_materialized(); +#ifndef DBUG_OFF + void set_check_merged(); +#else + inline void set_check_merged() {} +#endif /* Cleanup for re-execution in a prepared statement or a stored procedure. diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index 92b2a965706..407350900bf 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -2243,21 +2243,6 @@ int ha_maria::delete_row(const uchar * buf) return maria_delete(file, buf); } -C_MODE_START - -ICP_RESULT index_cond_func_maria(void *arg) -{ - ha_maria *h= (ha_maria*)arg; - if (h->end_range) - { - if (h->compare_key2(h->end_range) > 0) - return ICP_OUT_OF_RANGE; /* caller should return HA_ERR_END_OF_FILE already */ - } - return h->pushed_idx_cond->val_int() ? ICP_MATCH : ICP_NO_MATCH; -} - -C_MODE_END - int ha_maria::index_read_map(uchar * buf, const uchar * key, key_part_map keypart_map, enum ha_rkey_function find_flag) @@ -2277,7 +2262,7 @@ int ha_maria::index_read_idx_map(uchar * buf, uint index, const uchar * key, /* Use the pushed index condition if it matches the index we're scanning */ end_range= NULL; if (index == pushed_idx_cond_keyno) - ma_set_index_cond_func(file, index_cond_func_maria, this); + ma_set_index_cond_func(file, handler_index_cond_check, this); error= maria_rkey(file, buf, index, key, keypart_map, find_flag); @@ -2358,7 +2343,7 @@ int ha_maria::index_init(uint idx, bool sorted) { active_index=idx; if (pushed_idx_cond_keyno == idx) - ma_set_index_cond_func(file, index_cond_func_maria, this); + ma_set_index_cond_func(file, handler_index_cond_check, this); return 0; } @@ -3791,7 +3776,7 @@ Item *ha_maria::idx_cond_push(uint keyno_arg, Item* idx_cond_arg) pushed_idx_cond= idx_cond_arg; in_range_check_pushed_down= TRUE; if (active_index == pushed_idx_cond_keyno) - ma_set_index_cond_func(file, index_cond_func_maria, this); + ma_set_index_cond_func(file, handler_index_cond_check, this); return NULL; } diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index 5f1caa31b74..a6ece45734a 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -1765,27 +1765,11 @@ int ha_myisam::delete_row(const uchar *buf) } -C_MODE_START - -ICP_RESULT index_cond_func_myisam(void *arg) -{ - ha_myisam *h= (ha_myisam*)arg; - if (h->end_range) - { - if (h->compare_key2(h->end_range) > 0) - return ICP_OUT_OF_RANGE; /* caller should return HA_ERR_END_OF_FILE already */ - } - return (ICP_RESULT) test(h->pushed_idx_cond->val_int()); -} - -C_MODE_END - - int ha_myisam::index_init(uint idx, bool sorted) { active_index=idx; if (pushed_idx_cond_keyno == idx) - mi_set_index_cond_func(file, index_cond_func_myisam, this); + mi_set_index_cond_func(file, handler_index_cond_check, this); return 0; } @@ -1821,7 +1805,7 @@ int ha_myisam::index_read_idx_map(uchar *buf, uint index, const uchar *key, /* Use the pushed index condition if it matches the index we're scanning */ end_range= NULL; if (index == pushed_idx_cond_keyno) - mi_set_index_cond_func(file, index_cond_func_myisam, this); + mi_set_index_cond_func(file, handler_index_cond_check, this); res= mi_rkey(file, buf, index, key, keypart_map, find_flag); mi_set_index_cond_func(file, NULL, 0); return res; @@ -2340,7 +2324,7 @@ Item *ha_myisam::idx_cond_push(uint keyno_arg, Item* idx_cond_arg) pushed_idx_cond= idx_cond_arg; in_range_check_pushed_down= TRUE; if (active_index == pushed_idx_cond_keyno) - mi_set_index_cond_func(file, index_cond_func_myisam, this); + mi_set_index_cond_func(file, handler_index_cond_check, this); return NULL; } diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc index 972a4407eea..0d64a3f8b14 100644 --- a/storage/xtradb/handler/ha_innodb.cc +++ b/storage/xtradb/handler/ha_innodb.cc @@ -12573,28 +12573,6 @@ bool ha_innobase::is_thd_killed() * Index Condition Pushdown interface implementation */ -/*************************************************************//** -InnoDB index push-down condition check -@return ICP_NO_MATCH, ICP_MATCH, or ICP_OUT_OF_RANGE */ -extern "C" UNIV_INTERN -enum icp_result -innobase_index_cond( -/*================*/ - void* file) /*!< in/out: pointer to ha_innobase */ -{ - ha_innobase *h= (ha_innobase*) file; - - if (h->is_thd_killed()) - return ICP_ABORTED_BY_USER; - - if (h->end_range) - { - if (h->compare_key2(h->end_range) > 0) - return ICP_OUT_OF_RANGE; /* caller should return HA_ERR_END_OF_FILE already */ - } - return h->pushed_idx_cond->val_int()? ICP_MATCH : ICP_NO_MATCH; -} - /** Attempt to push down an index condition. * @param[in] keyno MySQL key number * @param[in] idx_cond Index condition to be checked diff --git a/storage/xtradb/handler/ha_innodb.h b/storage/xtradb/handler/ha_innodb.h index 749438e0c89..d234a7236e3 100644 --- a/storage/xtradb/handler/ha_innodb.h +++ b/storage/xtradb/handler/ha_innodb.h @@ -223,6 +223,7 @@ class ha_innobase: public handler bool check_if_incompatible_data(HA_CREATE_INFO *info, uint table_changes); bool check_if_supported_virtual_columns(void) { return TRUE; } + private: /** Builds a 'template' to the prebuilt struct. diff --git a/storage/xtradb/include/ha_prototypes.h b/storage/xtradb/include/ha_prototypes.h index db71c37afc3..28c28838400 100644 --- a/storage/xtradb/include/ha_prototypes.h +++ b/storage/xtradb/include/ha_prototypes.h @@ -252,7 +252,7 @@ InnoDB index push-down condition check @return ICP_NO_MATCH, ICP_MATCH, or ICP_OUT_OF_RANGE */ UNIV_INTERN enum icp_result -innobase_index_cond( +handler_index_cond_check( /*================*/ void* file) /*!< in/out: pointer to ha_innobase */ __attribute__((nonnull, warn_unused_result)); diff --git a/storage/xtradb/row/row0sel.c b/storage/xtradb/row/row0sel.c index fca3c90109f..52a6263efe5 100644 --- a/storage/xtradb/row/row0sel.c +++ b/storage/xtradb/row/row0sel.c @@ -3443,7 +3443,7 @@ row_search_idx_cond_check( index, if the case of the column has been updated in the past, or a record has been deleted and a record inserted in a different case. */ - result = innobase_index_cond(prebuilt->idx_cond); + result = handler_index_cond_check(prebuilt->idx_cond); switch (result) { case ICP_MATCH: /* Convert the remaining fields to MySQL format. |