From e0c1b3f24246d22e6785315f9a8448bd9a590422 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 6 Nov 2011 01:23:03 -0700 Subject: Fixed LP bug #886145. The bug happened because in some cases the function JOIN::exec did not save the value of TABLE::pre_idx_push_select_cond in TABLE::select->pre_idx_push_select_cond for the sort table. Noticed and fixed a bug in the function make_cond_remainder that builds the remainder condition after extraction of an index pushdown condition from the where condition. The code erroneously assumed that the function make_cond_for_table left the value of ICP_COND_USES_INDEX_ONLY in sub-condition markers. Adjusted many result files from the regression test suite after this fix . --- mysql-test/r/innodb_icp.result | 35 +++++++++++++++++++++++++++++++++-- 1 file changed, 33 insertions(+), 2 deletions(-) (limited to 'mysql-test/r/innodb_icp.result') diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index edebc64ef20..9bfa3990970 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -506,7 +506,7 @@ 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 +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) SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR @@ -648,12 +648,43 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; a b d xdmbdkpjda DROP TABLE t1; +# +# Bug#886145: join with ICP + ORDER BY +# +CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b)); +INSERT INTO t1 VALUES (1,4,'Ill'); +CREATE TABLE t2 (a varchar(1024), KEY (a(512))); +INSERT INTO t2 VALUES +('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); +SET SESSION optimizer_switch='index_condition_pushdown=off'; +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 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; +b c +1 4 +SET SESSION optimizer_switch='index_condition_pushdown=on'; +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 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; +b c +1 4 +DROP TABLE t1,t2; set optimizer_switch=@innodb_icp_tmp; set storage_engine= @save_storage_engine; -- cgit v1.2.1