diff options
| -rw-r--r-- | mysql-test/r/join_cache.result | 35 | ||||
| -rw-r--r-- | mysql-test/t/join_cache.test | 35 | ||||
| -rw-r--r-- | sql/opt_index_cond_pushdown.cc | 12 |
3 files changed, 80 insertions, 2 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 0d2706c7739..3a22dfc8354 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -6056,4 +6056,39 @@ a4 b5 SET SESSION optimizer_switch = 'outer_join_with_cache=off'; SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2,t3,t4,t5; +# +# Bug #694092: incorrect detection of index only pushdown conditions +# +CREATE TABLE t1 ( +f1 varchar(10), f3 int(11), PRIMARY KEY (f3) +); +INSERT INTO t1 VALUES ('y',1),('or',5); +CREATE TABLE t2 ( +f3 int(11), f2 varchar(1024), f4 varchar(10), PRIMARY KEY (f3) +); +INSERT INTO t2 VALUES (6,'RPOYT','y'),(10,'JINQE','m'); +SET SESSION join_cache_level = 1; +SET SESSION optimizer_switch = 'index_condition_pushdown=off'; +EXPLAIN +SELECT * FROM t1,t2 +WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using MRR +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using MRR; Using join buffer (flat, BNL join) +SELECT * FROM t1,t2 +WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); +f1 f3 f3 f2 f4 +SET SESSION optimizer_switch = 'index_condition_pushdown=on'; +EXPLAIN +SELECT * FROM t1,t2 +WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); +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 MRR +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Using MRR; Using join buffer (flat, BNL join) +SELECT * FROM t1,t2 +WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); +f1 f3 f3 f2 f4 +SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch = DEFAULT; +DROP TABLE t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 1905297b25b..823af0b929d 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -2700,5 +2700,40 @@ SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2,t3,t4,t5; +--echo # +--echo # Bug #694092: incorrect detection of index only pushdown conditions +--echo # + +CREATE TABLE t1 ( + f1 varchar(10), f3 int(11), PRIMARY KEY (f3) +); +INSERT INTO t1 VALUES ('y',1),('or',5); + +CREATE TABLE t2 ( + f3 int(11), f2 varchar(1024), f4 varchar(10), PRIMARY KEY (f3) +); +INSERT INTO t2 VALUES (6,'RPOYT','y'),(10,'JINQE','m'); + +SET SESSION join_cache_level = 1; + +SET SESSION optimizer_switch = 'index_condition_pushdown=off'; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); +SELECT * FROM t1,t2 + WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); + +SET SESSION optimizer_switch = 'index_condition_pushdown=on'; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); +SELECT * FROM t1,t2 + WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); + +SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch = DEFAULT; + +DROP TABLE t1,t2; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc index 6f0210a3d21..9f6cd69663d 100644 --- a/sql/opt_index_cond_pushdown.cc +++ b/sql/opt_index_cond_pushdown.cc @@ -155,7 +155,11 @@ Item *make_cond_for_index(Item *cond, TABLE *table, uint keyno, new_cond->argument_list()->push_back(fix); used_tables|= fix->used_tables(); } - n_marked += test(item->marker == ICP_COND_USES_INDEX_ONLY); + if (test(item->marker == ICP_COND_USES_INDEX_ONLY)) + { + n_marked++; + item->marker= 0; + } } if (n_marked ==((Item_cond*)cond)->argument_list()->elements) cond->marker= ICP_COND_USES_INDEX_ONLY; @@ -184,7 +188,11 @@ Item *make_cond_for_index(Item *cond, TABLE *table, uint keyno, if (!fix) return (COND*) 0; new_cond->argument_list()->push_back(fix); - n_marked += test(item->marker == ICP_COND_USES_INDEX_ONLY); + if (test(item->marker == ICP_COND_USES_INDEX_ONLY)) + { + n_marked++; + item->marker= 0; + } } if (n_marked ==((Item_cond*)cond)->argument_list()->elements) cond->marker= ICP_COND_USES_INDEX_ONLY; |