diff options
| author | Igor Babaev <igor@askmonty.org> | 2011-11-10 13:28:02 -0800 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2011-11-10 13:28:02 -0800 |
| commit | 402258ff9577b35646460f90e6db1c95d13a826a (patch) | |
| tree | 3efed971e6cdcbece3c7fa682eaf7132e26651fe | |
| parent | 250bc599bf584ea057edd3de0a4cb0420bc92951 (diff) | |
| parent | 9426530d368e134f8ceea1a01e919d646db91dc7 (diff) | |
| download | mariadb-git-402258ff9577b35646460f90e6db1c95d13a826a.tar.gz | |
Merge of the maria-5.3-icp tree into the 5.3 tree
37 files changed, 2916 insertions, 608 deletions
diff --git a/include/my_compare.h b/include/my_compare.h index 7e886d1e0dc..058d6b6d94a 100644 --- a/include/my_compare.h +++ b/include/my_compare.h @@ -103,6 +103,8 @@ extern HA_KEYSEG *ha_find_null(HA_KEYSEG *keyseg, const uchar *a); if we're scanning "t.key BETWEEN 10 AND 20" and got a "t.key=21" tuple (the engine should stop scanning and return HA_ERR_END_OF_FILE right away). + 3=ICP_ABORTED_BY_USER - engine must stop scanning and should return + HA_ERR_ABORTED_BY_USER right away -1= ICP_ERROR - Reserved for internal errors in engines. Should not be returned by index_cond_func_xxx */ @@ -111,7 +113,8 @@ typedef enum icp_result { ICP_ERROR=-1, ICP_NO_MATCH=0, ICP_MATCH=1, - ICP_OUT_OF_RANGE=2 + ICP_OUT_OF_RANGE=2, + ICP_ABORTED_BY_USER=3, } ICP_RESULT; diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index 4af8bf9c452..3a278d5f10c 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -87,6 +87,152 @@ SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; DROP TABLE t1; --echo # +--echo # Bug#43617 - Innodb returns wrong results with timestamp's range value +--echo # in IN clause +--echo # (Note: Fixed by patch for BUG#42580) +--echo # + +CREATE TABLE t1( + c1 TIMESTAMP NOT NULL, + c2 TIMESTAMP NULL, + c3 DATE, + c4 DATETIME, + PRIMARY KEY(c1), + UNIQUE INDEX(c2) +); + +INSERT INTO t1 VALUES + ('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'), + ('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'), + ('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL, NULL), + ('2007-05-23 09:15:28','2007-05-23 09:15:28','2007-05-24','2007-05-24 09:15:28'), + ('2007-05-27 00:00:00','2007-05-25 00:00:00','2007-05-26','2007-05-26 00:00:00'), + ('2008-01-01 00:00:00', NULL, '2008-01-02','2008-01-03 00:00:00'), + ('2009-01-29 11:11:27','2009-01-29 11:11:27','2009-01-29','2009-01-29 11:11:27'), + ('2038-01-09 03:14:07','2038-01-09 03:14:07','2009-01-05','2009-01-06 00:00:00'); + +--echo +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2; + +--echo +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 LIMIT 2; + +--echo +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 DESC; + +--echo +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 DESC LIMIT 2; + +--echo +DROP TABLE t1; + +--echo # +--echo # BUG#43618: MyISAM&Maria returns wrong results with 'between' +--echo # on timestamp +--echo # + +CREATE TABLE t1( + ts TIMESTAMP NOT NULL, + c char NULL, + PRIMARY KEY(ts) +); + +INSERT INTO t1 VALUES + ('1971-01-01','a'), + ('2007-05-25','b'), + ('2008-01-01','c'), + ('2038-01-09','d'); + +--disable_warnings + +--echo +--echo # Execute select with invalid timestamp, desc ordering +SELECT * +FROM t1 +WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' +ORDER BY ts DESC +LIMIT 2; + +--echo +--echo # Should use index condition +EXPLAIN +SELECT * +FROM t1 +WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' +ORDER BY ts DESC +LIMIT 2; +--echo + +--enable_warnings + +DROP TABLE t1; + +--echo # +--echo # BUG#49906: Assertion failed - Field_varstring::val_str in field.cc +--echo # (Note: Fixed by patch for LP BUG#625841) +--echo # + +CREATE TABLE t1 ( + f1 VARCHAR(1024), + f2 VARCHAR(10), + INDEX test_idx USING BTREE (f2,f1(5)) +); + +INSERT INTO t1 VALUES ('a','c'), ('b','d'); + +SELECT f1 +FROM t1 +WHERE f2 LIKE 'd' +ORDER BY f1; + +DROP TABLE t1; + +--echo # +--echo # Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on +--echo # an index containing TEXT" +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +CREATE TABLE t2 (a INT); +INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B; + +CREATE TABLE t3 ( + c1 TINYTEXT NOT NULL, + i1 INT NOT NULL, + KEY (c1(6),i1) +); + +INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2; + +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; + +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; + +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; + +DROP TABLE t1, t2, t3; + +--echo # --echo # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on --echo # @@ -102,6 +248,7 @@ SELECT * FROM t WHERE a > 2 FOR UPDATE; DROP TABLE t; + --echo # --echo # Bug#35080 - Innodb crash at mem_block_get_len line 72 --echo # @@ -227,6 +374,273 @@ DROP PROCEDURE insert_data; DROP TABLE t1, t2, t3; --echo # +--echo # Bug#57372 "Multi-table updates and deletes fail when running with ICP +--echo # against InnoDB" +--echo # + +CREATE TABLE t1 ( + a INT KEY, + b INT +); + +CREATE TABLE t2 ( + a INT KEY, + b INT +); + +INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); + +UPDATE t1, t2 +SET t1.a = t1.a + 100, t2.b = t1.a + 10 +WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100; + +--sorted_result +SELECT * FROM t1; +--sorted_result +SELECT * FROM t2; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug#52605 - "Adding LIMIT 1 clause to query with complex range +--echo # predicate causes wrong results" +--echo # + +CREATE TABLE t1 ( + pk INT NOT NULL, + c1 INT, + PRIMARY KEY (pk), + KEY k1 (c1) +); + +INSERT INTO t1 VALUES (1,NULL); +INSERT INTO t1 VALUES (2,6); +INSERT INTO t1 VALUES (3,NULL); +INSERT INTO t1 VALUES (4,6); +INSERT INTO t1 VALUES (5,NULL); +INSERT INTO t1 VALUES (6,NULL); +INSERT INTO t1 VALUES (7,9); +INSERT INTO t1 VALUES (8,0); + +SELECT pk, c1 +FROM t1 +WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 +ORDER BY c1 +LIMIT 1; + +EXPLAIN SELECT pk, c1 +FROM t1 +WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 +ORDER BY c1 +LIMIT 1; + +DROP TABLE t1; + +--echo # +--echo # Bug#59259 "Incorrect rows returned for a correlated subquery +--echo # when ICP is on" +--echo # + +CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (11,0); +INSERT INTO t1 VALUES (12,5); +INSERT INTO t1 VALUES (15,0); + +CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (11,1); +INSERT INTO t2 VALUES (12,2); +INSERT INTO t2 VALUES (15,4); + +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=off'; + +EXPLAIN +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); +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); + +set optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug #58816 "Extra temporary duplicate rows in result set when +--echo # switching ICP off" +--echo # + +set @save_optimizer_switch= @@optimizer_switch; + +CREATE TABLE t1 ( + pk INT NOT NULL, + c1 INT NOT NULL, + 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; + +SET SESSION optimizer_switch='index_condition_pushdown=off'; + +SELECT pk, c1 FROM t1 WHERE pk <> 3; + +DROP TABLE t1; + +set optimizer_switch= @save_optimizer_switch; + +--echo # +--echo # Bug#58837: ICP crash or valgrind error due to uninitialized +--echo # value in innobase_index_cond +--echo # + +CREATE TABLE t1 ( + t1_int INT, + t1_time TIME +); + +CREATE TABLE t2 ( + t2_int int PRIMARY KEY, + t2_int2 INT +); + +--disable_warnings +INSERT INTO t2 VALUES (); +INSERT INTO t1 VALUES (); +--enable_warnings + +SELECT * FROM t1 AS t1a + WHERE NOT EXISTS (SELECT * FROM t1 AS t1b + WHERE t1b.t1_int NOT IN + (SELECT t2.t2_int FROM t2 + WHERE t1b.t1_time LIKE t1b.t1_int + OR t1b.t1_time <> t2.t2_int2 + AND 6=7)); + +DROP TABLE t1,t2; + +--echo # +--echo # Bug#59186: Wrong results of join when ICP is enabled +--echo # (fixed by the patch for LP bug #694092) +--echo # + +CREATE TABLE t1 ( + pk INTEGER NOT NULL, + c1 VARCHAR(3) NOT NULL, + PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1,'y'),(0,'or'); + +CREATE TABLE t2 ( + pk INTEGER NOT NULL, + c1 VARCHAR(3) NOT NULL, + c2 VARCHAR(6) NOT NULL, + PRIMARY KEY (pk) +); +INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE'); + +EXPLAIN +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); +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); + +DROP TABLE t1, t2; + +--echo # +--echo # Bug#58838: "Wrong results with HAVING + LIMIT without GROUP BY when +--echo # ICP is enabled". +--echo # (Fixed by the patches for LP bugs #668644, #702322) +--echo # + +CREATE TABLE t1 ( + pk INT NOT NULL, + c1 INT, + PRIMARY KEY (pk), + KEY col_int_key (c1) +); + +INSERT INTO t1 VALUES (1,37),(2,8),(3,-25),(4,NULL),(5,55); + +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 0; +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 1; +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 2; +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 5; + +DROP TABLE t1; + +--echo # +--echo # Bug#59483 "Crash on INSERT/REPLACE in +--echo # rec_convert_dtuple_to_rec_comp with ICP on" +--echo # + +CREATE TABLE t1 ( + pk INTEGER AUTO_INCREMENT PRIMARY KEY, + i1 INTEGER, + c1 CHAR(6), + i2 INTEGER NOT NULL, + KEY (i2) +); + +INSERT INTO t1 VALUES + (NULL, 4, 'that', 8), + (NULL, 1, 'she', 6), + (NULL, 6, 'tell', 2); + +SELECT * FROM t1 WHERE i2 IN (3, 6) LIMIT 2 FOR UPDATE; +INSERT INTO t1 (i2) VALUES (1); + +DROP TABLE t1; + +--echo # +--echo # Bug #11766678 - 59843: +--echo # USING UNINITIALISED VALUE IN USES_INDEX_FIELDS_ONLY +--echo # + +CREATE TABLE t1 ( + col999 FLOAT NOT NULL, + COL1000 VARBINARY(179) NOT NULL, + col1003 DATE DEFAULT NULL, + KEY idx4267 (col1000, col1003) +); + +INSERT INTO t1 VALUES (),(); +SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate(); + +DROP TABLE t1; + +--echo # +--echo # BUG#12822678 - ICP WITH STRAIGHT_JOIN +--echo # + +CREATE TABLE t1 ( + i1 INTEGER NOT NULL, + d1 DOUBLE, + KEY k1 (d1) +); +INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL); + +CREATE TABLE t2 ( + pk INTEGER NOT NULL, + i1 INTEGER NOT NULL, + PRIMARY KEY (pk) +); +INSERT INTO t2 VALUES (4,1); + +EXPLAIN +SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 + WHERE t2.pk <> t1.d1 AND t2.pk = 4; +SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 + WHERE t2.pk <> t1.d1 AND t2.pk = 4; + +DROP TABLE t1, t2; + +--echo # --echo # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89 --echo # CREATE TABLE t1 ( f11 int) ; @@ -254,3 +668,59 @@ WHERE (6, 234) IN ( DROP TABLE t1,t2,t3,t4; set optimizer_switch= @tmp_778434; + +--echo # +--echo # Bug#885168: ICP for one index + potential ORDER BY for another +--echo # + +CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) ; +INSERT INTO t1 VALUES + ('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'), + ('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei'); + +SET SESSION optimizer_switch='index_condition_pushdown=off'; +EXPLAIN +SELECT * FROM t1 + WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' + ORDER BY a; +SELECT * FROM t1 + WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' + ORDER BY a; + +SET SESSION optimizer_switch='index_condition_pushdown=on'; +EXPLAIN +SELECT * FROM t1 + WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' + ORDER BY a; +SELECT * FROM t1 + WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' + ORDER BY a; + +DROP TABLE t1; + +--echo # +--echo # Bug#886145: join with ICP + ORDER BY +--echo # + +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; +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; + +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; +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; + +DROP TABLE t1,t2; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 9ab7ed4eec7..3b69791d373 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 where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using index condition 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 6df834168fa..9bfa3990970 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -83,6 +83,150 @@ c1 c2 c3 c4 DROP TABLE t1; # +# Bug#43617 - Innodb returns wrong results with timestamp's range value +# in IN clause +# (Note: Fixed by patch for BUG#42580) +# +CREATE TABLE t1( +c1 TIMESTAMP NOT NULL, +c2 TIMESTAMP NULL, +c3 DATE, +c4 DATETIME, +PRIMARY KEY(c1), +UNIQUE INDEX(c2) +); +INSERT INTO t1 VALUES +('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'), +('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'), +('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL, NULL), +('2007-05-23 09:15:28','2007-05-23 09:15:28','2007-05-24','2007-05-24 09:15:28'), +('2007-05-27 00:00:00','2007-05-25 00:00:00','2007-05-26','2007-05-26 00:00:00'), +('2008-01-01 00:00:00', NULL, '2008-01-02','2008-01-03 00:00:00'), +('2009-01-29 11:11:27','2009-01-29 11:11:27','2009-01-29','2009-01-29 11:11:27'), +('2038-01-09 03:14:07','2038-01-09 03:14:07','2009-01-05','2009-01-06 00:00:00'); + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 LIMIT 2; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 DESC; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 DESC LIMIT 2; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +DROP TABLE t1; +# +# BUG#43618: MyISAM&Maria returns wrong results with 'between' +# on timestamp +# +CREATE TABLE t1( +ts TIMESTAMP NOT NULL, +c char NULL, +PRIMARY KEY(ts) +); +INSERT INTO t1 VALUES +('1971-01-01','a'), +('2007-05-25','b'), +('2008-01-01','c'), +('2038-01-09','d'); + +# Execute select with invalid timestamp, desc ordering +SELECT * +FROM t1 +WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' +ORDER BY ts DESC +LIMIT 2; +ts c +2008-01-01 00:00:00 c +2007-05-25 00:00:00 b + +# Should use index condition +EXPLAIN +SELECT * +FROM t1 +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 2 Using index condition + +DROP TABLE t1; +# +# BUG#49906: Assertion failed - Field_varstring::val_str in field.cc +# (Note: Fixed by patch for LP BUG#625841) +# +CREATE TABLE t1 ( +f1 VARCHAR(1024), +f2 VARCHAR(10), +INDEX test_idx USING BTREE (f2,f1(5)) +); +INSERT INTO t1 VALUES ('a','c'), ('b','d'); +SELECT f1 +FROM t1 +WHERE f2 LIKE 'd' +ORDER BY f1; +f1 +b +DROP TABLE t1; +# +# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on +# an index containing TEXT" +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 (a INT); +INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B; +CREATE TABLE t3 ( +c1 TINYTEXT NOT NULL, +i1 INT NOT NULL, +KEY (c1(6),i1) +); +INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2; +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c1 c1 8 NULL 3 Using where +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; +c1 +c-1004=w +c-1005=w +c-1006=w +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c1 c1 12 NULL 2 Using index condition; Using where +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; +c1 +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL c1 NULL NULL NULL 100 Using where +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; +c1 +c-1004=w +c-1005=w +c-1006=w +DROP TABLE t1, t2, t3; +# # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on # CREATE TABLE t ( @@ -203,6 +347,260 @@ COUNT(*) DROP PROCEDURE insert_data; DROP TABLE t1, t2, t3; # +# Bug#57372 "Multi-table updates and deletes fail when running with ICP +# against InnoDB" +# +CREATE TABLE t1 ( +a INT KEY, +b INT +); +CREATE TABLE t2 ( +a INT KEY, +b INT +); +INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +UPDATE t1, t2 +SET t1.a = t1.a + 100, t2.b = t1.a + 10 +WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100; +SELECT * FROM t1; +a b +1 101 +102 102 +103 103 +104 104 +5 105 +SELECT * FROM t2; +a b +1 1 +2 12 +3 13 +4 14 +5 5 +DROP TABLE t1, t2; +# +# Bug#52605 - "Adding LIMIT 1 clause to query with complex range +# predicate causes wrong results" +# +CREATE TABLE t1 ( +pk INT NOT NULL, +c1 INT, +PRIMARY KEY (pk), +KEY k1 (c1) +); +INSERT INTO t1 VALUES (1,NULL); +INSERT INTO t1 VALUES (2,6); +INSERT INTO t1 VALUES (3,NULL); +INSERT INTO t1 VALUES (4,6); +INSERT INTO t1 VALUES (5,NULL); +INSERT INTO t1 VALUES (6,NULL); +INSERT INTO t1 VALUES (7,9); +INSERT INTO t1 VALUES (8,0); +SELECT pk, c1 +FROM t1 +WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 +ORDER BY c1 +LIMIT 1; +pk c1 +4 6 +EXPLAIN SELECT pk, c1 +FROM t1 +WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 +ORDER BY c1 +LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 3 Using where; Using index +DROP TABLE t1; +# +# Bug#59259 "Incorrect rows returned for a correlated subquery +# when ICP is on" +# +CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (11,0); +INSERT INTO t1 VALUES (12,5); +INSERT INTO t1 VALUES (15,0); +CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; +INSERT INTO t2 VALUES (11,1); +INSERT INTO t2 VALUES (12,2); +INSERT INTO t2 VALUES (15,4); +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=off'; +EXPLAIN +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 t2 index NULL PRIMARY 4 NULL 3 Using index +2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition +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); +pk i +12 5 +set optimizer_switch=@save_optimizer_switch; +DROP TABLE t1, t2; +# +# Bug #58816 "Extra temporary duplicate rows in result set when +# switching ICP off" +# +set @save_optimizer_switch= @@optimizer_switch; +CREATE TABLE t1 ( +pk INT NOT NULL, +c1 INT NOT NULL, +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 +SET SESSION optimizer_switch='index_condition_pushdown=off'; +SELECT pk, c1 FROM t1 WHERE pk <> 3; +pk c1 +1 9 +2 7 +4 3 +5 1 +DROP TABLE t1; +set optimizer_switch= @save_optimizer_switch; +# +# Bug#58837: ICP crash or valgrind error due to uninitialized +# value in innobase_index_cond +# +CREATE TABLE t1 ( +t1_int INT, +t1_time TIME +); +CREATE TABLE t2 ( +t2_int int PRIMARY KEY, +t2_int2 INT +); +INSERT INTO t2 VALUES (); +INSERT INTO t1 VALUES (); +SELECT * FROM t1 AS t1a +WHERE NOT EXISTS (SELECT * FROM t1 AS t1b +WHERE t1b.t1_int NOT IN +(SELECT t2.t2_int FROM t2 +WHERE t1b.t1_time LIKE t1b.t1_int +OR t1b.t1_time <> t2.t2_int2 +AND 6=7)); +t1_int t1_time +DROP TABLE t1,t2; +# +# Bug#59186: Wrong results of join when ICP is enabled +# (fixed by the patch for LP bug #694092) +# +CREATE TABLE t1 ( +pk INTEGER NOT NULL, +c1 VARCHAR(3) NOT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1,'y'),(0,'or'); +CREATE TABLE t2 ( +pk INTEGER NOT NULL, +c1 VARCHAR(3) NOT NULL, +c2 VARCHAR(6) NOT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE'); +EXPLAIN +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) +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); +c2 +DROP TABLE t1, t2; +# +# Bug#58838: "Wrong results with HAVING + LIMIT without GROUP BY when +# ICP is enabled". +# (Fixed by the patches for LP bugs #668644, #702322) +# +CREATE TABLE t1 ( +pk INT NOT NULL, +c1 INT, +PRIMARY KEY (pk), +KEY col_int_key (c1) +); +INSERT INTO t1 VALUES (1,37),(2,8),(3,-25),(4,NULL),(5,55); +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 0; +pk +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 1; +pk +3 +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 2; +pk +3 +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 5; +pk +3 +DROP TABLE t1; +# +# Bug#59483 "Crash on INSERT/REPLACE in +# rec_convert_dtuple_to_rec_comp with ICP on" +# +CREATE TABLE t1 ( +pk INTEGER AUTO_INCREMENT PRIMARY KEY, +i1 INTEGER, +c1 CHAR(6), +i2 INTEGER NOT NULL, +KEY (i2) +); +INSERT INTO t1 VALUES +(NULL, 4, 'that', 8), +(NULL, 1, 'she', 6), +(NULL, 6, 'tell', 2); +SELECT * FROM t1 WHERE i2 IN (3, 6) LIMIT 2 FOR UPDATE; +pk i1 c1 i2 +2 1 she 6 +INSERT INTO t1 (i2) VALUES (1); +DROP TABLE t1; +# +# Bug #11766678 - 59843: +# USING UNINITIALISED VALUE IN USES_INDEX_FIELDS_ONLY +# +CREATE TABLE t1 ( +col999 FLOAT NOT NULL, +COL1000 VARBINARY(179) NOT NULL, +col1003 DATE DEFAULT NULL, +KEY idx4267 (col1000, col1003) +); +INSERT INTO t1 VALUES (),(); +Warnings: +Warning 1364 Field 'col999' doesn't have a default value +Warning 1364 Field 'COL1000' doesn't have a default value +SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate(); +col999 +DROP TABLE t1; +# +# BUG#12822678 - ICP WITH STRAIGHT_JOIN +# +CREATE TABLE t1 ( +i1 INTEGER NOT NULL, +d1 DOUBLE, +KEY k1 (d1) +); +INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL); +CREATE TABLE t2 ( +pk INTEGER NOT NULL, +i1 INTEGER NOT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t2 VALUES (4,1); +EXPLAIN +SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 +WHERE t2.pk <> t1.d1 AND t2.pk = 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k1 9 NULL 3 Using index +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where +SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 +WHERE t2.pk <> t1.d1 AND t2.pk = 4; +d1 pk i1 +1 4 1 +DROP TABLE t1, t2; +# # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89 # CREATE TABLE t1 ( f11 int) ; @@ -225,5 +623,68 @@ FROM t3 JOIN t4 ON t4.f11 = t3.f10 f11 f10 DROP TABLE t1,t2,t3,t4; set optimizer_switch= @tmp_778434; +# +# Bug#885168: ICP for one index + potential ORDER BY for another +# +CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) ; +INSERT INTO t1 VALUES +('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'), +('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei'); +SET SESSION optimizer_switch='index_condition_pushdown=off'; +EXPLAIN +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 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 +SET SESSION optimizer_switch='index_condition_pushdown=on'; +EXPLAIN +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; 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; diff --git a/mysql-test/r/innodb_mrr.result b/mysql-test/r/innodb_mrr.result index 73c3bdd4228..f56d55f7da2 100644 --- a/mysql-test/r/innodb_mrr.result +++ b/mysql-test/r/innodb_mrr.result @@ -186,7 +186,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan +1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using where; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler @@ -208,7 +208,7 @@ NULL NULL NULL NULL-1 explain select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan +1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using where; Rowid-ordered scan select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); a b c filler b-1 NULL c-1 NULL-15 @@ -728,6 +728,7 @@ JA USA DROP TABLE t1,t2; # # Testcase backport: Bug#43249 +# (Note: Fixed by patch for BUG#42580) # CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY KEY(c1), UNIQUE INDEX(c2)) engine=innodb; INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01'); diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 039ec061819..1f9ef1dfeda 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -4968,7 +4968,7 @@ 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; Rowid-ordered scan +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; 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); diff --git a/mysql-test/r/maria_icp.result b/mysql-test/r/maria_icp.result index 81bf90f5439..f71df0a9867 100644 --- a/mysql-test/r/maria_icp.result +++ b/mysql-test/r/maria_icp.result @@ -83,6 +83,150 @@ c1 c2 c3 c4 DROP TABLE t1; # +# Bug#43617 - Innodb returns wrong results with timestamp's range value +# in IN clause +# (Note: Fixed by patch for BUG#42580) +# +CREATE TABLE t1( +c1 TIMESTAMP NOT NULL, +c2 TIMESTAMP NULL, +c3 DATE, +c4 DATETIME, +PRIMARY KEY(c1), +UNIQUE INDEX(c2) +); +INSERT INTO t1 VALUES +('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'), +('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'), +('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL, NULL), +('2007-05-23 09:15:28','2007-05-23 09:15:28','2007-05-24','2007-05-24 09:15:28'), +('2007-05-27 00:00:00','2007-05-25 00:00:00','2007-05-26','2007-05-26 00:00:00'), +('2008-01-01 00:00:00', NULL, '2008-01-02','2008-01-03 00:00:00'), +('2009-01-29 11:11:27','2009-01-29 11:11:27','2009-01-29','2009-01-29 11:11:27'), +('2038-01-09 03:14:07','2038-01-09 03:14:07','2009-01-05','2009-01-06 00:00:00'); + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 LIMIT 2; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 DESC; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 DESC LIMIT 2; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +DROP TABLE t1; +# +# BUG#43618: MyISAM&Maria returns wrong results with 'between' +# on timestamp +# +CREATE TABLE t1( +ts TIMESTAMP NOT NULL, +c char NULL, +PRIMARY KEY(ts) +); +INSERT INTO t1 VALUES +('1971-01-01','a'), +('2007-05-25','b'), +('2008-01-01','c'), +('2038-01-09','d'); + +# Execute select with invalid timestamp, desc ordering +SELECT * +FROM t1 +WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' +ORDER BY ts DESC +LIMIT 2; +ts c +2008-01-01 00:00:00 c +2007-05-25 00:00:00 b + +# Should use index condition +EXPLAIN +SELECT * +FROM t1 +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 + +DROP TABLE t1; +# +# BUG#49906: Assertion failed - Field_varstring::val_str in field.cc +# (Note: Fixed by patch for LP BUG#625841) +# +CREATE TABLE t1 ( +f1 VARCHAR(1024), +f2 VARCHAR(10), +INDEX test_idx USING BTREE (f2,f1(5)) +); +INSERT INTO t1 VALUES ('a','c'), ('b','d'); +SELECT f1 +FROM t1 +WHERE f2 LIKE 'd' +ORDER BY f1; +f1 +b +DROP TABLE t1; +# +# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on +# an index containing TEXT" +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 (a INT); +INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B; +CREATE TABLE t3 ( +c1 TINYTEXT NOT NULL, +i1 INT NOT NULL, +KEY (c1(6),i1) +); +INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2; +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c1 c1 8 NULL 3 Using where +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; +c1 +c-1004=w +c-1005=w +c-1006=w +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c1 c1 12 NULL 2 Using index condition; Using where +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; +c1 +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL c1 NULL NULL NULL 100 Using where +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; +c1 +c-1004=w +c-1005=w +c-1006=w +DROP TABLE t1, t2, t3; +# # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on # CREATE TABLE t ( @@ -203,6 +347,266 @@ COUNT(*) DROP PROCEDURE insert_data; DROP TABLE t1, t2, t3; # +# Bug#57372 "Multi-table updates and deletes fail when running with ICP +# against InnoDB" +# +CREATE TABLE t1 ( +a INT KEY, +b INT +); +CREATE TABLE t2 ( +a INT KEY, +b INT +); +INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +UPDATE t1, t2 +SET t1.a = t1.a + 100, t2.b = t1.a + 10 +WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100; +SELECT * FROM t1; +a b +1 101 +102 102 +103 103 +104 104 +5 105 +SELECT * FROM t2; +a b +1 1 +2 12 +3 13 +4 14 +5 5 +DROP TABLE t1, t2; +# +# Bug#52605 - "Adding LIMIT 1 clause to query with complex range +# predicate causes wrong results" +# +CREATE TABLE t1 ( +pk INT NOT NULL, +c1 INT, +PRIMARY KEY (pk), +KEY k1 (c1) +); +INSERT INTO t1 VALUES (1,NULL); +INSERT INTO t1 VALUES (2,6); +INSERT INTO t1 VALUES (3,NULL); +INSERT INTO t1 VALUES (4,6); +INSERT INTO t1 VALUES (5,NULL); +INSERT INTO t1 VALUES (6,NULL); +INSERT INTO t1 VALUES (7,9); +INSERT INTO t1 VALUES (8,0); +SELECT pk, c1 +FROM t1 +WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 +ORDER BY c1 +LIMIT 1; +pk c1 +4 6 +EXPLAIN SELECT pk, c1 +FROM t1 +WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 +ORDER BY c1 +LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using where +DROP TABLE t1; +# +# Bug#59259 "Incorrect rows returned for a correlated subquery +# when ICP is on" +# +CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; +Warnings: +Warning 1286 Unknown table engine 'InnoDB' +Warning 1266 Using storage engine Aria for table 't1' +INSERT INTO t1 VALUES (11,0); +INSERT INTO t1 VALUES (12,5); +INSERT INTO t1 VALUES (15,0); +CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; +Warnings: +Warning 1286 Unknown table engine 'InnoDB' +Warning 1266 Using storage engine Aria for table 't2' +INSERT INTO t2 VALUES (11,1); +INSERT INTO t2 VALUES (12,2); +INSERT INTO t2 VALUES (15,4); +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=off'; +EXPLAIN +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 t2 index NULL PRIMARY 4 NULL 3 Using index +2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition +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); +pk i +12 5 +set optimizer_switch=@save_optimizer_switch; +DROP TABLE t1, t2; +# +# Bug #58816 "Extra temporary duplicate rows in result set when +# switching ICP off" +# +set @save_optimizer_switch= @@optimizer_switch; +CREATE TABLE t1 ( +pk INT NOT NULL, +c1 INT NOT NULL, +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 ALL PRIMARY NULL NULL NULL 5 Using where +SET SESSION optimizer_switch='index_condition_pushdown=off'; +SELECT pk, c1 FROM t1 WHERE pk <> 3; +pk c1 +1 9 +2 7 +4 3 +5 1 +DROP TABLE t1; +set optimizer_switch= @save_optimizer_switch; +# +# Bug#58837: ICP crash or valgrind error due to uninitialized +# value in innobase_index_cond +# +CREATE TABLE t1 ( +t1_int INT, +t1_time TIME +); +CREATE TABLE t2 ( +t2_int int PRIMARY KEY, +t2_int2 INT +); +INSERT INTO t2 VALUES (); +INSERT INTO t1 VALUES (); +SELECT * FROM t1 AS t1a +WHERE NOT EXISTS (SELECT * FROM t1 AS t1b +WHERE t1b.t1_int NOT IN +(SELECT t2.t2_int FROM t2 +WHERE t1b.t1_time LIKE t1b.t1_int +OR t1b.t1_time <> t2.t2_int2 +AND 6=7)); +t1_int t1_time +DROP TABLE t1,t2; +# +# Bug#59186: Wrong results of join when ICP is enabled +# (fixed by the patch for LP bug #694092) +# +CREATE TABLE t1 ( +pk INTEGER NOT NULL, +c1 VARCHAR(3) NOT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1,'y'),(0,'or'); +CREATE TABLE t2 ( +pk INTEGER NOT NULL, +c1 VARCHAR(3) NOT NULL, +c2 VARCHAR(6) NOT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE'); +EXPLAIN +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 2 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; 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); +c2 +DROP TABLE t1, t2; +# +# Bug#58838: "Wrong results with HAVING + LIMIT without GROUP BY when +# ICP is enabled". +# (Fixed by the patches for LP bugs #668644, #702322) +# +CREATE TABLE t1 ( +pk INT NOT NULL, +c1 INT, +PRIMARY KEY (pk), +KEY col_int_key (c1) +); +INSERT INTO t1 VALUES (1,37),(2,8),(3,-25),(4,NULL),(5,55); +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 0; +pk +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 1; +pk +3 +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 2; +pk +3 +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 5; +pk +3 +DROP TABLE t1; +# +# Bug#59483 "Crash on INSERT/REPLACE in +# rec_convert_dtuple_to_rec_comp with ICP on" +# +CREATE TABLE t1 ( +pk INTEGER AUTO_INCREMENT PRIMARY KEY, +i1 INTEGER, +c1 CHAR(6), +i2 INTEGER NOT NULL, +KEY (i2) +); +INSERT INTO t1 VALUES +(NULL, 4, 'that', 8), +(NULL, 1, 'she', 6), +(NULL, 6, 'tell', 2); +SELECT * FROM t1 WHERE i2 IN (3, 6) LIMIT 2 FOR UPDATE; +pk i1 c1 i2 +2 1 she 6 +INSERT INTO t1 (i2) VALUES (1); +DROP TABLE t1; +# +# Bug #11766678 - 59843: +# USING UNINITIALISED VALUE IN USES_INDEX_FIELDS_ONLY +# +CREATE TABLE t1 ( +col999 FLOAT NOT NULL, +COL1000 VARBINARY(179) NOT NULL, +col1003 DATE DEFAULT NULL, +KEY idx4267 (col1000, col1003) +); +INSERT INTO t1 VALUES (),(); +Warnings: +Warning 1364 Field 'col999' doesn't have a default value +Warning 1364 Field 'COL1000' doesn't have a default value +SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate(); +col999 +DROP TABLE t1; +# +# BUG#12822678 - ICP WITH STRAIGHT_JOIN +# +CREATE TABLE t1 ( +i1 INTEGER NOT NULL, +d1 DOUBLE, +KEY k1 (d1) +); +INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL); +CREATE TABLE t2 ( +pk INTEGER NOT NULL, +i1 INTEGER NOT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t2 VALUES (4,1); +EXPLAIN +SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 +WHERE t2.pk <> t1.d1 AND t2.pk = 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k1 9 NULL 3 Using index +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where +SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 +WHERE t2.pk <> t1.d1 AND t2.pk = 4; +d1 pk i1 +1 4 1 +DROP TABLE t1, t2; +# # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89 # CREATE TABLE t1 ( f11 int) ; @@ -225,5 +629,68 @@ FROM t3 JOIN t4 ON t4.f11 = t3.f10 f11 f10 DROP TABLE t1,t2,t3,t4; set optimizer_switch= @tmp_778434; +# +# Bug#885168: ICP for one index + potential ORDER BY for another +# +CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) ; +INSERT INTO t1 VALUES +('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'), +('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei'); +SET SESSION optimizer_switch='index_condition_pushdown=off'; +EXPLAIN +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 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 +SET SESSION optimizer_switch='index_condition_pushdown=on'; +EXPLAIN +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; 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 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 ref a a 515 const 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 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 ref a a 515 const 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 storage_engine= @save_storage_engine; set optimizer_switch=@maria_icp_tmp; diff --git a/mysql-test/r/maria_mrr.result b/mysql-test/r/maria_mrr.result index de7a129e897..652bea93e7a 100644 --- a/mysql-test/r/maria_mrr.result +++ b/mysql-test/r/maria_mrr.result @@ -187,7 +187,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan +1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using where; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler @@ -209,7 +209,7 @@ NULL NULL NULL NULL-1 explain select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan +1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using where; Rowid-ordered scan select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); a b c filler b-1 NULL c-1 NULL-15 diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result index a4df3680dec..bf45702fcc0 100644 --- a/mysql-test/r/mrr_icp_extra.result +++ b/mysql-test/r/mrr_icp_extra.result @@ -72,7 +72,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); insert into t1 values (2, 11), (1, 11), (4, 14), (3, 14), (6, 12), (5, 12); explain select * from t1 where b=1 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -81,7 +81,7 @@ a b 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 3 Using index condition; Using where; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL @@ -105,7 +105,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using index condition; Rowid-ordered scan; Using filesort EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort +1 SIMPLE t1 range StringField StringField 38 NULL 4 Using index condition; Using filesort SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; FieldKey LongVal StringVal 3 1 2 @@ -123,7 +123,7 @@ Table Op Msg_type Msg_text test.t1 optimize status OK explain select * from t1 force index (a) where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Rowid-ordered scan +1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using where; Rowid-ordered scan select * from t1 force index (a) where a=0 or a=2; a b c 0 NULL 0 diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result index a36e0b49399..6799682c273 100644 --- a/mysql-test/r/myisam_icp.result +++ b/mysql-test/r/myisam_icp.result @@ -81,6 +81,150 @@ c1 c2 c3 c4 DROP TABLE t1; # +# Bug#43617 - Innodb returns wrong results with timestamp's range value +# in IN clause +# (Note: Fixed by patch for BUG#42580) +# +CREATE TABLE t1( +c1 TIMESTAMP NOT NULL, +c2 TIMESTAMP NULL, +c3 DATE, +c4 DATETIME, +PRIMARY KEY(c1), +UNIQUE INDEX(c2) +); +INSERT INTO t1 VALUES +('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'), +('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'), +('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL, NULL), +('2007-05-23 09:15:28','2007-05-23 09:15:28','2007-05-24','2007-05-24 09:15:28'), +('2007-05-27 00:00:00','2007-05-25 00:00:00','2007-05-26','2007-05-26 00:00:00'), +('2008-01-01 00:00:00', NULL, '2008-01-02','2008-01-03 00:00:00'), +('2009-01-29 11:11:27','2009-01-29 11:11:27','2009-01-29','2009-01-29 11:11:27'), +('2038-01-09 03:14:07','2038-01-09 03:14:07','2009-01-05','2009-01-06 00:00:00'); + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 LIMIT 2; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 DESC; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +SELECT * +FROM t1 +WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') +ORDER BY c2 DESC LIMIT 2; +c1 c2 c3 c4 +2038-01-09 03:14:07 2038-01-09 03:14:07 2009-01-05 2009-01-06 00:00:00 + +DROP TABLE t1; +# +# BUG#43618: MyISAM&Maria returns wrong results with 'between' +# on timestamp +# +CREATE TABLE t1( +ts TIMESTAMP NOT NULL, +c char NULL, +PRIMARY KEY(ts) +); +INSERT INTO t1 VALUES +('1971-01-01','a'), +('2007-05-25','b'), +('2008-01-01','c'), +('2038-01-09','d'); + +# Execute select with invalid timestamp, desc ordering +SELECT * +FROM t1 +WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' +ORDER BY ts DESC +LIMIT 2; +ts c +2008-01-01 00:00:00 c +2007-05-25 00:00:00 b + +# Should use index condition +EXPLAIN +SELECT * +FROM t1 +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 + +DROP TABLE t1; +# +# BUG#49906: Assertion failed - Field_varstring::val_str in field.cc +# (Note: Fixed by patch for LP BUG#625841) +# +CREATE TABLE t1 ( +f1 VARCHAR(1024), +f2 VARCHAR(10), +INDEX test_idx USING BTREE (f2,f1(5)) +); +INSERT INTO t1 VALUES ('a','c'), ('b','d'); +SELECT f1 +FROM t1 +WHERE f2 LIKE 'd' +ORDER BY f1; +f1 +b +DROP TABLE t1; +# +# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on +# an index containing TEXT" +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 (a INT); +INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B; +CREATE TABLE t3 ( +c1 TINYTEXT NOT NULL, +i1 INT NOT NULL, +KEY (c1(6),i1) +); +INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2; +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c1 c1 8 NULL 3 Using where +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w'; +c1 +c-1004=w +c-1005=w +c-1006=w +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range c1 c1 12 NULL 2 Using index condition; Using where +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; +c1 +EXPLAIN +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 ALL c1 NULL NULL NULL 100 Using where +SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' or i1 > 2; +c1 +c-1004=w +c-1005=w +c-1006=w +DROP TABLE t1, t2, t3; +# # Bug#40992 - InnoDB: Crash when engine_condition_pushdown is on # CREATE TABLE t ( @@ -201,6 +345,266 @@ COUNT(*) DROP PROCEDURE insert_data; DROP TABLE t1, t2, t3; # +# Bug#57372 "Multi-table updates and deletes fail when running with ICP +# against InnoDB" +# +CREATE TABLE t1 ( +a INT KEY, +b INT +); +CREATE TABLE t2 ( +a INT KEY, +b INT +); +INSERT INTO t1 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +UPDATE t1, t2 +SET t1.a = t1.a + 100, t2.b = t1.a + 10 +WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b - 100; +SELECT * FROM t1; +a b +1 101 +102 102 +103 103 +104 104 +5 105 +SELECT * FROM t2; +a b +1 1 +2 12 +3 13 +4 14 +5 5 +DROP TABLE t1, t2; +# +# Bug#52605 - "Adding LIMIT 1 clause to query with complex range +# predicate causes wrong results" +# +CREATE TABLE t1 ( +pk INT NOT NULL, +c1 INT, +PRIMARY KEY (pk), +KEY k1 (c1) +); +INSERT INTO t1 VALUES (1,NULL); +INSERT INTO t1 VALUES (2,6); +INSERT INTO t1 VALUES (3,NULL); +INSERT INTO t1 VALUES (4,6); +INSERT INTO t1 VALUES (5,NULL); +INSERT INTO t1 VALUES (6,NULL); +INSERT INTO t1 VALUES (7,9); +INSERT INTO t1 VALUES (8,0); +SELECT pk, c1 +FROM t1 +WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 +ORDER BY c1 +LIMIT 1; +pk c1 +4 6 +EXPLAIN SELECT pk, c1 +FROM t1 +WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 +ORDER BY c1 +LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using where +DROP TABLE t1; +# +# Bug#59259 "Incorrect rows returned for a correlated subquery +# when ICP is on" +# +CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; +Warnings: +Warning 1286 Unknown table engine 'InnoDB' +Warning 1266 Using storage engine MyISAM for table 't1' +INSERT INTO t1 VALUES (11,0); +INSERT INTO t1 VALUES (12,5); +INSERT INTO t1 VALUES (15,0); +CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB; +Warnings: +Warning 1286 Unknown table engine 'InnoDB' +Warning 1266 Using storage engine MyISAM for table 't2' +INSERT INTO t2 VALUES (11,1); +INSERT INTO t2 VALUES (12,2); +INSERT INTO t2 VALUES (15,4); +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=off'; +EXPLAIN +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 t2 index NULL PRIMARY 4 NULL 3 Using index +2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition +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); +pk i +12 5 +set optimizer_switch=@save_optimizer_switch; +DROP TABLE t1, t2; +# +# Bug #58816 "Extra temporary duplicate rows in result set when +# switching ICP off" +# +set @save_optimizer_switch= @@optimizer_switch; +CREATE TABLE t1 ( +pk INT NOT NULL, +c1 INT NOT NULL, +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 ALL PRIMARY NULL NULL NULL 5 Using where +SET SESSION optimizer_switch='index_condition_pushdown=off'; +SELECT pk, c1 FROM t1 WHERE pk <> 3; +pk c1 +1 9 +2 7 +4 3 +5 1 +DROP TABLE t1; +set optimizer_switch= @save_optimizer_switch; +# +# Bug#58837: ICP crash or valgrind error due to uninitialized +# value in innobase_index_cond +# +CREATE TABLE t1 ( +t1_int INT, +t1_time TIME +); +CREATE TABLE t2 ( +t2_int int PRIMARY KEY, +t2_int2 INT +); +INSERT INTO t2 VALUES (); +INSERT INTO t1 VALUES (); +SELECT * FROM t1 AS t1a +WHERE NOT EXISTS (SELECT * FROM t1 AS t1b +WHERE t1b.t1_int NOT IN +(SELECT t2.t2_int FROM t2 +WHERE t1b.t1_time LIKE t1b.t1_int +OR t1b.t1_time <> t2.t2_int2 +AND 6=7)); +t1_int t1_time +DROP TABLE t1,t2; +# +# Bug#59186: Wrong results of join when ICP is enabled +# (fixed by the patch for LP bug #694092) +# +CREATE TABLE t1 ( +pk INTEGER NOT NULL, +c1 VARCHAR(3) NOT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1,'y'),(0,'or'); +CREATE TABLE t2 ( +pk INTEGER NOT NULL, +c1 VARCHAR(3) NOT NULL, +c2 VARCHAR(6) NOT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE'); +EXPLAIN +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 2 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan; 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); +c2 +DROP TABLE t1, t2; +# +# Bug#58838: "Wrong results with HAVING + LIMIT without GROUP BY when +# ICP is enabled". +# (Fixed by the patches for LP bugs #668644, #702322) +# +CREATE TABLE t1 ( +pk INT NOT NULL, +c1 INT, +PRIMARY KEY (pk), +KEY col_int_key (c1) +); +INSERT INTO t1 VALUES (1,37),(2,8),(3,-25),(4,NULL),(5,55); +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 0; +pk +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 1; +pk +3 +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 2; +pk +3 +SELECT pk FROM t1 WHERE c1 <> 1 HAVING pk = 3 ORDER BY pk LIMIT 5; +pk +3 +DROP TABLE t1; +# +# Bug#59483 "Crash on INSERT/REPLACE in +# rec_convert_dtuple_to_rec_comp with ICP on" +# +CREATE TABLE t1 ( +pk INTEGER AUTO_INCREMENT PRIMARY KEY, +i1 INTEGER, +c1 CHAR(6), +i2 INTEGER NOT NULL, +KEY (i2) +); +INSERT INTO t1 VALUES +(NULL, 4, 'that', 8), +(NULL, 1, 'she', 6), +(NULL, 6, 'tell', 2); +SELECT * FROM t1 WHERE i2 IN (3, 6) LIMIT 2 FOR UPDATE; +pk i1 c1 i2 +2 1 she 6 +INSERT INTO t1 (i2) VALUES (1); +DROP TABLE t1; +# +# Bug #11766678 - 59843: +# USING UNINITIALISED VALUE IN USES_INDEX_FIELDS_ONLY +# +CREATE TABLE t1 ( +col999 FLOAT NOT NULL, +COL1000 VARBINARY(179) NOT NULL, +col1003 DATE DEFAULT NULL, +KEY idx4267 (col1000, col1003) +); +INSERT INTO t1 VALUES (),(); +Warnings: +Warning 1364 Field 'col999' doesn't have a default value +Warning 1364 Field 'COL1000' doesn't have a default value +SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate(); +col999 +DROP TABLE t1; +# +# BUG#12822678 - ICP WITH STRAIGHT_JOIN +# +CREATE TABLE t1 ( +i1 INTEGER NOT NULL, +d1 DOUBLE, +KEY k1 (d1) +); +INSERT INTO t1 VALUES (10,1), (17,NULL), (22,NULL); +CREATE TABLE t2 ( +pk INTEGER NOT NULL, +i1 INTEGER NOT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t2 VALUES (4,1); +EXPLAIN +SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 +WHERE t2.pk <> t1.d1 AND t2.pk = 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k1 9 NULL 3 Using index +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 Using where +SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 +WHERE t2.pk <> t1.d1 AND t2.pk = 4; +d1 pk i1 +1 4 1 +DROP TABLE t1, t2; +# # BUG#778434 Wrong result with in_to_exists=on in maria-5.3-mwl89 # CREATE TABLE t1 ( f11 int) ; @@ -223,6 +627,69 @@ FROM t3 JOIN t4 ON t4.f11 = t3.f10 f11 f10 DROP TABLE t1,t2,t3,t4; set optimizer_switch= @tmp_778434; +# +# Bug#885168: ICP for one index + potential ORDER BY for another +# +CREATE TABLE t1 (a varchar(64), b varchar(10), INDEX(a), INDEX(b)) ; +INSERT INTO t1 VALUES +('Ohio','Iowa'), ('k','d'), ('bdkpj','mbdkpjdanp'), ('d','xdmbdkpjda'), +('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei'); +SET SESSION optimizer_switch='index_condition_pushdown=off'; +EXPLAIN +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 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 +SET SESSION optimizer_switch='index_condition_pushdown=on'; +EXPLAIN +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; 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 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 ref a a 515 const 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 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 ref a a 515 const 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; drop table if exists t0, t1, t1i, t1m; # # BUG#826935 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed @@ -260,4 +727,34 @@ SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; a MIN(c) 5 y DROP TABLE t1; +# +# BUG#887026: Wrong result with ICP, outer join, subquery in maria-5.3-icp +# +CREATE TABLE t1 (c varchar(1)); +INSERT INTO t1 VALUES ('c'), ('c'); +CREATE TABLE t2 (c varchar(1), b int); +INSERT INTO t2 VALUES ('d', NULL),('d', NULL); +CREATE TABLE t3 (c varchar(1)); +INSERT INTO t3 VALUES ('c'); +INSERT INTO t3 VALUES ('c'); +CREATE TABLE t4 ( b int, c varchar(1), KEY (b)); +INSERT INTO t4 VALUES (7,'c'); +INSERT INTO t4 VALUES (7,'c'); +# Must be t1,t2,t3,t4, with t4 having Full-scan-on-NULL but not Using index condition +explain +SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b +WHERE +t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t4 ref_or_null b b 5 func 2 Using where; Full scan on NULL key +SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b +WHERE +t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); +c c b +c NULL NULL +c NULL NULL +DROP TABLE t1,t2,t3,t4; set optimizer_switch=@myisam_icp_tmp; diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index b7ff8981ef5..5bb6b9db49e 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -188,7 +188,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE t4 range idx1 idx1 29 NULL 10 Using index condition; Using where; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler @@ -210,7 +210,7 @@ NULL NULL NULL NULL-1 explain select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Rowid-ordered scan +1 SIMPLE t4 range idx1 idx1 29 NULL 21 Using index condition; Using where; Rowid-ordered scan select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); a b c filler b-1 NULL c-1 NULL-15 @@ -365,7 +365,7 @@ update t1 set b=repeat(char(65+a), 20) where a < 25; This must show range + using index condition: explain select * from t1 where a < 10 and b = repeat(char(65+a), 20); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 19 Using where +1 SIMPLE t1 range a a 5 NULL 19 Using index condition; Using where select * from t1 where a < 10 and b = repeat(char(65+a), 20); a b filler 0 AAAAAAAAAAAAAAAAAAAA filler diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index cf6ce2b9d24..cb9ea051ae0 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -927,7 +927,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Rowid-ordered scan +1 SIMPLE t1 range status status 23 NULL 10 Using index condition; Using where; Rowid-ordered scan SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; id status 53 C @@ -1031,13 +1031,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan explain select * from t1 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan +1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where; Rowid-ordered scan 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 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 ref a a 13 const # Using index condition; Using where 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 @@ -1795,7 +1795,7 @@ INSERT INTO t100(I,J) VALUES(8,26); EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Rowid-ordered scan +1 SIMPLE t100 range I I 10 NULL 4 Using index condition; Using where; Rowid-ordered scan SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); K I J diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 9037b58858f..1c36b1e99cd 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -3427,7 +3427,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan DROP TABLE t1,t2; CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); INSERT t1 SET i = 0; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 99c3153fe72..b87aa59a037 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -369,7 +369,7 @@ EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result index 7a4fec3823e..7a28b82455c 100644 --- a/mysql-test/r/subselect_mat_cost.result +++ b/mysql-test/r/subselect_mat_cost.result @@ -285,7 +285,7 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish') AND Code = Country; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Rowid-ordered scan +1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 45 Using index condition; Using where; Rowid-ordered scan 1 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using index condition 2 SUBQUERY CountryLanguage ref PRIMARY,Language Language 30 const 47 Using index condition SELECT Country.Name @@ -348,7 +348,7 @@ FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000)) AND Language IN ('English','Spanish'); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY CountryLanguage range Language Language 30 NULL 72 Using index condition; Using where; Rowid-ordered scan -2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 2 Using index condition +2 DEPENDENT SUBQUERY City ref CityName CityName 35 func 2 Using index condition; Using where 2 DEPENDENT SUBQUERY Country eq_ref PRIMARY PRIMARY 3 world.City.Country 1 Using where; Using index select count(*) from CountryLanguage diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 8c11dc29884..4827524ae63 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -374,7 +374,7 @@ EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 80a85b06339..e7a04efa627 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -370,7 +370,7 @@ EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 266c37465f8..756431a2c6b 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -373,7 +373,7 @@ EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 0efe3a7ba45..caa75349a7a 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -370,7 +370,7 @@ EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' from `test`.`t8` where 1)) diff --git a/mysql-test/t/innodb_mrr.test b/mysql-test/t/innodb_mrr.test index 7afa18b43ef..996ceca0d2c 100644 --- a/mysql-test/t/innodb_mrr.test +++ b/mysql-test/t/innodb_mrr.test @@ -420,6 +420,7 @@ DROP TABLE t1,t2; --echo # --echo # Testcase backport: Bug#43249 +--echo # (Note: Fixed by patch for BUG#42580) --echo # CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY KEY(c1), UNIQUE INDEX(c2)) engine=innodb; INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01'); diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test index 7e7affb1b8f..bbff6c30e56 100644 --- a/mysql-test/t/myisam_icp.test +++ b/mysql-test/t/myisam_icp.test @@ -227,4 +227,34 @@ SELECT a, MIN(c) FROM t1 WHERE b = 'x' AND c > 'x' GROUP BY a; DROP TABLE t1; +--echo # +--echo # BUG#887026: Wrong result with ICP, outer join, subquery in maria-5.3-icp +--echo # + +CREATE TABLE t1 (c varchar(1)); +INSERT INTO t1 VALUES ('c'), ('c'); + +CREATE TABLE t2 (c varchar(1), b int); +INSERT INTO t2 VALUES ('d', NULL),('d', NULL); + +CREATE TABLE t3 (c varchar(1)); +INSERT INTO t3 VALUES ('c'); +INSERT INTO t3 VALUES ('c'); + +CREATE TABLE t4 ( b int, c varchar(1), KEY (b)); +INSERT INTO t4 VALUES (7,'c'); +INSERT INTO t4 VALUES (7,'c'); + +--echo # Must be t1,t2,t3,t4, with t4 having Full-scan-on-NULL but not Using index condition +explain +SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b +WHERE + t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b +WHERE + t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); + +DROP TABLE t1,t2,t3,t4; + set optimizer_switch=@myisam_icp_tmp; diff --git a/sql/filesort.cc b/sql/filesort.cc index da275c1c14f..15020b56e52 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -546,12 +546,11 @@ static ha_rows find_all_keys(SORTPARAM *param, SQL_SELECT *select, /* Temporary set for register_used_fields and register_field_in_read_map */ sort_form->read_set= &sort_form->tmp_set; register_used_fields(param); - if (select && select->cond) - select->cond->walk(&Item::register_field_in_read_map, 1, - (uchar*) sort_form); - if (select && select->pre_idx_push_select_cond) - select->pre_idx_push_select_cond->walk(&Item::register_field_in_read_map, - 1, (uchar*) sort_form); + Item *sort_cond= !select ? + 0 : !select->pre_idx_push_select_cond ? + select->cond : select->pre_idx_push_select_cond; + if (sort_cond) + sort_cond->walk(&Item::register_field_in_read_map, 1, (uchar*) sort_form); sort_form->column_bitmaps_set(&sort_form->tmp_set, &sort_form->tmp_set, &sort_form->tmp_set); diff --git a/sql/handler.cc b/sql/handler.cc index 66e22aa40dc..6c5d3a580ec 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -4878,6 +4878,9 @@ int handler::ha_reset() /* reset the bitmaps to point to defaults */ table->default_column_bitmaps(); pushed_cond= NULL; + /* Reset information about pushed engine conditions */ + cancel_pushed_idx_cond(); + /* Reset information about pushed index conditions */ DBUG_RETURN(reset()); } diff --git a/sql/handler.h b/sql/handler.h index ce10872867a..66320c7e98f 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -1666,7 +1666,7 @@ public: handler(handlerton *ht_arg, TABLE_SHARE *share_arg) :table_share(share_arg), table(0), estimation_rows_to_insert(0), ht(ht_arg), - ref(0), in_range_check_pushed_down(FALSE), + ref(0), end_range(NULL), in_range_check_pushed_down(FALSE), key_used_on_scan(MAX_KEY), active_index(MAX_KEY), ref_length(sizeof(my_off_t)), ft_handler(0), inited(NONE), @@ -1723,6 +1723,7 @@ public: DBUG_ENTER("ha_rnd_init"); DBUG_ASSERT(inited==NONE || (inited==RND && scan)); inited= (result= rnd_init(scan)) ? NONE: RND; + end_range= NULL; DBUG_RETURN(result); } int ha_rnd_end() @@ -1730,6 +1731,7 @@ public: DBUG_ENTER("ha_rnd_end"); DBUG_ASSERT(inited==RND); inited=NONE; + end_range= NULL; DBUG_RETURN(rnd_end()); } int ha_rnd_init_with_error(bool scan) __attribute__ ((warn_unused_result)); @@ -2391,6 +2393,13 @@ public: */ virtual void cond_pop() { return; }; virtual Item *idx_cond_push(uint keyno, Item* idx_cond) { return idx_cond; } + /** Reset information about pushed index conditions */ + virtual void cancel_pushed_idx_cond() + { + pushed_idx_cond= NULL; + pushed_idx_cond_keyno= MAX_KEY; + in_range_check_pushed_down= false; + } virtual bool check_if_incompatible_data(HA_CREATE_INFO *create_info, uint table_changes) { return COMPATIBLE_DATA_NO; } diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc index e0a2d3b1f30..3a9c813b93c 100644 --- a/sql/opt_index_cond_pushdown.cc +++ b/sql/opt_index_cond_pushdown.cc @@ -83,15 +83,44 @@ bool uses_index_fields_only(Item *item, TABLE *tbl, uint keyno, case Item::FIELD_ITEM: { Item_field *item_field= (Item_field*)item; - if (item_field->field->table != tbl) + Field *field= item_field->field; + if (field->table != tbl) return TRUE; /* The below is probably a repetition - the first part checks the other two, but let's play it safe: */ - return item_field->field->part_of_key.is_set(keyno) && - item_field->field->type() != MYSQL_TYPE_GEOMETRY && - item_field->field->type() != MYSQL_TYPE_BLOB; + if(!field->part_of_key.is_set(keyno) || + field->type() == MYSQL_TYPE_GEOMETRY || + field->type() == MYSQL_TYPE_BLOB) + return FALSE; + KEY *key_info= tbl->key_info + keyno; + KEY_PART_INFO *key_part= key_info->key_part; + KEY_PART_INFO *key_part_end= key_part + key_info->key_parts; + for ( ; key_part < key_part_end; key_part++) + { + if (field->eq(key_part->field)) + return !(key_part->key_part_flag & HA_PART_KEY_SEG); + } + if ((tbl->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) && + tbl->s->primary_key != MAX_KEY && + tbl->s->primary_key != keyno) + { + key_info= tbl->key_info + tbl->s->primary_key; + key_part= key_info->key_part; + key_part_end= key_part + key_info->key_parts; + for ( ; key_part < key_part_end; key_part++) + { + /* + It does not make sense to use the fact that the engine can read in + a full field if the key if the index is built only over a part + of this field. + */ + if (field->eq(key_part->field)) + return !(key_part->key_part_flag & HA_PART_KEY_SEG); + } + } + return FALSE; } case Item::REF_ITEM: return uses_index_fields_only(item->real_item(), tbl, keyno, @@ -210,11 +239,9 @@ Item *make_cond_for_index(Item *cond, TABLE *table, uint keyno, } -Item *make_cond_remainder(Item *cond, bool exclude_index) +Item *make_cond_remainder(Item *cond, TABLE *table, uint keyno, + bool other_tbls_ok, bool exclude_index) { - if (exclude_index && cond->marker == ICP_COND_USES_INDEX_ONLY) - return 0; /* Already checked */ - if (cond->type() == Item::COND_ITEM) { table_map tbl_map= 0; @@ -228,7 +255,8 @@ Item *make_cond_remainder(Item *cond, bool exclude_index) Item *item; while ((item=li++)) { - Item *fix= make_cond_remainder(item, exclude_index); + Item *fix= make_cond_remainder(item, table, keyno, + other_tbls_ok, exclude_index); if (fix) { new_cond->argument_list()->push_back(fix); @@ -255,7 +283,8 @@ Item *make_cond_remainder(Item *cond, bool exclude_index) Item *item; while ((item=li++)) { - Item *fix= make_cond_remainder(item, FALSE); + Item *fix= make_cond_remainder(item, table, keyno, + other_tbls_ok, FALSE); if (!fix) return (COND*) 0; new_cond->argument_list()->push_back(fix); @@ -267,7 +296,14 @@ Item *make_cond_remainder(Item *cond, bool exclude_index) return new_cond; } } - return cond; + else + { + if (exclude_index && + uses_index_fields_only(cond, table, keyno, other_tbls_ok)) + return 0; + else + return cond; + } } @@ -288,30 +324,13 @@ void push_index_cond(JOIN_TAB *tab, uint keyno) { DBUG_ENTER("push_index_cond"); Item *idx_cond; - bool do_index_cond_pushdown= - ((tab->table->file->index_flags(keyno, 0, 1) & - HA_DO_INDEX_COND_PUSHDOWN) && - optimizer_flag(tab->join->thd, OPTIMIZER_SWITCH_INDEX_COND_PUSHDOWN)); - - /* - Do not try index condition pushdown on indexes which have partially-covered - columns. Unpacking from a column prefix into index tuple is not a supported - operation in some engines, see e.g. MySQL BUG#42991. - TODO: a better solution would be not to consider partially-covered columns - as parts of the index and still produce/check index condition for - fully-covered index columns. - */ - KEY *key_info= tab->table->key_info + keyno; - for (uint kp= 0; kp < key_info->key_parts; kp++) - { - if ((key_info->key_part[kp].key_part_flag & HA_PART_KEY_SEG)) - { - do_index_cond_pushdown= FALSE; - break; - } - } - if (do_index_cond_pushdown) + 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) { DBUG_EXECUTE("where", print_where(tab->select_cond, "full cond", QT_ORDINARY);); @@ -356,7 +375,8 @@ void push_index_cond(JOIN_TAB *tab, uint keyno) tab->ref.disable_cache= TRUE; Item *row_cond= tab->idx_cond_fact_out ? - make_cond_remainder(tab->select_cond, TRUE) : + make_cond_remainder(tab->select_cond, tab->table, keyno, + tab->icp_other_tables_ok, TRUE) : tab->pre_idx_push_select_cond; DBUG_EXECUTE("where", diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c6b5369c356..5e1992b8816 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2565,7 +2565,9 @@ JOIN::exec() if (curr_table->pre_idx_push_select_cond && !curr_table->pre_idx_push_select_cond->fixed) curr_table->pre_idx_push_select_cond->fix_fields(thd, 0); - + + curr_table->select->pre_idx_push_select_cond= + curr_table->pre_idx_push_select_cond; curr_table->set_select_cond(curr_table->select->cond, __LINE__); curr_table->select_cond->top_level_item(); DBUG_EXECUTE("where",print_where(curr_table->select->cond, @@ -9099,11 +9101,9 @@ uint check_join_cache_usage(JOIN_TAB *tab, case JT_EQ_REF: if (cache_level <=2 || (no_hashed_cache && no_bka_cache)) goto no_join_cache; - for (uint i= 0; i < tab->ref.key_parts; i++) - { - if (tab->ref.cond_guards[i]) - goto no_join_cache; - } + if (tab->ref.is_access_triggered()) + goto no_join_cache; + if (!tab->is_ref_for_hash_join()) { flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT; @@ -9394,9 +9394,9 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); } - else if (!jcl || jcl > 4) + else if ((!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); - break; + break; case JT_EQ_REF: tab->read_record.unlock_row= join_read_key_unlock_row; /* fall through */ @@ -9406,7 +9406,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); } - else if (!jcl || jcl > 4) + else if ((!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); break; case JT_REF_OR_NULL: @@ -9421,7 +9421,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) table->enable_keyread(); - else if (!jcl || jcl > 4) + else if ((!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); break; case JT_ALL: @@ -9778,6 +9778,22 @@ bool TABLE_REF::tmp_table_index_lookup_init(THD *thd, } +/* + Check if ref access uses "Full scan on NULL key" (i.e. it actually alternates + between ref access and full table scan) +*/ + +bool TABLE_REF::is_access_triggered() +{ + for (uint i = 0; i < key_parts; i++) + { + if (cond_guards[i]) + return TRUE; + } + return FALSE; +} + + /** Partially cleanup JOIN after it has executed: close index or rnd read (table cursors), free quick selects. @@ -16654,13 +16670,8 @@ bool test_if_ref(Item *root_cond, Item_field *left_item,Item *right_item) between ref access and full table scan), then no equality can be guaranteed to be true. */ - for (uint i = 0; i < join_tab->ref.key_parts; i++) - { - if (join_tab->ref.cond_guards[i]) - { - return FALSE; - } - } + if (join_tab->ref.is_access_triggered()) + return FALSE; Item *ref_item=part_of_refkey(field->table,field); if (ref_item && (ref_item->eq(right_item,1) || @@ -17932,8 +17943,10 @@ check_reverse_order: condition are not relevant anymore */ if (tab->select && tab->select->pre_idx_push_select_cond) + { tab->set_cond(tab->select->pre_idx_push_select_cond); - + tab->table->file->cancel_pushed_idx_cond(); + } /* TODO: update the number of records in join->best_positions[tablenr] */ @@ -17994,6 +18007,9 @@ skipped_filesort: } if (orig_cond_saved && !changed_key) tab->set_cond(orig_cond); + if (!no_changes && changed_key && table->file->pushed_idx_cond) + table->file->cancel_pushed_idx_cond(); + DBUG_RETURN(1); use_filesort: @@ -18005,6 +18021,7 @@ use_filesort: } if (orig_cond_saved) tab->set_cond(orig_cond); + DBUG_RETURN(0); } @@ -20748,7 +20765,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, keylen_str_buf); tmp3.append(keylen_str_buf, length, cs); } - if (tab->select && tab->select->quick) + if (tab->type != JT_CONST && tab->select && tab->select->quick) tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3); if (key_info || (tab->select && tab->select->quick)) { diff --git a/sql/sql_select.h b/sql/sql_select.h index 0a416966995..5476ef9b46c 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -136,6 +136,7 @@ typedef struct st_table_ref bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it, bool value, uint skip= 0); + bool is_access_triggered(); } TABLE_REF; diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index 12c291ac99d..a6636316efb 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -2538,9 +2538,6 @@ int ha_maria::extra(enum ha_extra_function operation) int ha_maria::reset(void) { - pushed_idx_cond= NULL; - pushed_idx_cond_keyno= MAX_KEY; - in_range_check_pushed_down= FALSE; ma_set_index_cond_func(file, NULL, 0); ds_mrr.dsmrr_close(); if (file->trn) diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index 84c16caebe1..2ee516bf9ec 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -1971,9 +1971,6 @@ int ha_myisam::extra(enum ha_extra_function operation) int ha_myisam::reset(void) { - pushed_idx_cond= NULL; - pushed_idx_cond_keyno= MAX_KEY; - in_range_check_pushed_down= FALSE; mi_set_index_cond_func(file, NULL, 0); ds_mrr.dsmrr_close(); return mi_reset(file); diff --git a/storage/xtradb/dict/dict0dict.c b/storage/xtradb/dict/dict0dict.c index 18880a5c72c..8d4bd76c32c 100644 --- a/storage/xtradb/dict/dict0dict.c +++ b/storage/xtradb/dict/dict0dict.c @@ -472,10 +472,12 @@ Looks for column n in an index. ULINT_UNDEFINED if not contained */ UNIV_INTERN ulint -dict_index_get_nth_col_pos( -/*=======================*/ - const dict_index_t* index, /*!< in: index */ - ulint n) /*!< in: column number */ +dict_index_get_nth_col_or_prefix_pos( +/*=================================*/ + const dict_index_t* index, /*!< in: index */ + ulint n, /*!< in: column number */ + ibool inc_prefix) /*!< in: TRUE=consider + column prefixes too */ { const dict_field_t* field; const dict_col_t* col; @@ -497,7 +499,8 @@ dict_index_get_nth_col_pos( for (pos = 0; pos < n_fields; pos++) { field = dict_index_get_nth_field(index, pos); - if (col == field->col && field->prefix_len == 0) { + if (col == field->col + && (inc_prefix || field->prefix_len == 0)) { return(pos); } diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc index a511d764b24..f09f1bf4310 100644 --- a/storage/xtradb/handler/ha_innodb.cc +++ b/storage/xtradb/handler/ha_innodb.cc @@ -126,11 +126,6 @@ static pthread_cond_t commit_cond; static pthread_mutex_t commit_cond_m; static bool innodb_inited = 0; -C_MODE_START -static xtradb_icp_result_t index_cond_func_innodb(void *arg); -C_MODE_END - - #define INSIDE_HA_INNOBASE_CC @@ -2008,14 +2003,24 @@ trx_is_strict( /**************************************************************//** Resets some fields of a prebuilt struct. The template is used in fast retrieval of just those column values MySQL needs in its processing. */ -static void -reset_template( -/*===========*/ - row_prebuilt_t* prebuilt) /*!< in/out: prebuilt struct */ +inline +ha_innobase::reset_template(void) +/*=============================*/ { + ut_ad(prebuilt->magic_n == ROW_PREBUILT_ALLOCATED); + ut_ad(prebuilt->magic_n2 == prebuilt->magic_n); + prebuilt->keep_other_fields_on_keyread = 0; prebuilt->read_just_key = 0; + /* Reset index condition pushdown state. */ + if (prebuilt->idx_cond) { + prebuilt->idx_cond = NULL; + prebuilt->idx_cond_n_cols = 0; + /* Invalidate prebuilt->mysql_template + in ha_innobase::write_row(). */ + prebuilt->template_type = ROW_MYSQL_NO_TEMPLATE; + } } /*****************************************************************//** @@ -2077,7 +2082,7 @@ ha_innobase::init_table_handle_for_HANDLER(void) we???? */ prebuilt->used_in_HANDLER = TRUE; - reset_template(prebuilt); + reset_template(); } /*********************************************************************//** @@ -4203,8 +4208,8 @@ static inline uint get_field_offset( /*=============*/ - TABLE* table, /*!< in: MySQL table object */ - Field* field) /*!< in: MySQL field object */ + const TABLE* table, /*!< in: MySQL table object */ + const Field* field) /*!< in: MySQL field object */ { return((uint) (field->ptr - table->record[0])); } @@ -4767,44 +4772,170 @@ ha_innobase::store_key_val_for_row( } /**************************************************************//** +Determines if a field is needed in a prebuilt struct 'template'. +@return field to use, or NULL if the field is not needed */ +static +const Field* +build_template_needs_field( +/*=======================*/ + ibool index_contains, /*!< in: + dict_index_contains_col_or_prefix( + index, i) */ + ibool read_just_key, /*!< in: TRUE when MySQL calls + ha_innobase::extra with the + argument HA_EXTRA_KEYREAD; it is enough + to read just columns defined in + the index (i.e., no read of the + clustered index record necessary) */ + ibool fetch_all_in_key, + /*!< in: true=fetch all fields in + the index */ + ibool fetch_primary_key_cols, + /*!< in: true=fetch the + primary key columns */ + dict_index_t* index, /*!< in: InnoDB index to use */ + const TABLE* table, /*!< in: MySQL table object */ + ulint i, /*!< in: field index in InnoDB table */ + ulint sql_idx) /*!< in: field index in SQL table */ +{ + const Field* field = table->field[sql_idx]; + + ut_ad(index_contains == dict_index_contains_col_or_prefix(index, i)); + + if (!index_contains) { + if (read_just_key) { + /* If this is a 'key read', we do not need + columns that are not in the key */ + + return(NULL); + } + } else if (fetch_all_in_key) { + /* This field is needed in the query */ + + return(field); + } + + if (bitmap_is_set(table->read_set, sql_idx) + || bitmap_is_set(table->write_set, sql_idx)) { + /* This field is needed in the query */ + + return(field); + } + + if (fetch_primary_key_cols + && dict_table_col_in_clustered_key(index->table, i)) { + /* This field is needed in the query */ + + return(field); + } + + /* This field is not needed in the query, skip it */ + + return(NULL); +} + +/**************************************************************//** +Adds a field is to a prebuilt struct 'template'. +@return the field template */ +static +mysql_row_templ_t* +build_template_field( +/*=================*/ + row_prebuilt_t* prebuilt, /*!< in/out: template */ + dict_index_t* clust_index, /*!< in: InnoDB clustered index */ + dict_index_t* index, /*!< in: InnoDB index to use */ + TABLE* table, /*!< in: MySQL table object */ + const Field* field, /*!< in: field in MySQL table */ + ulint i) /*!< in: field index in InnoDB table */ +{ + mysql_row_templ_t* templ; + const dict_col_t* col; + + ut_ad(field == table->field[i]); + ut_ad(clust_index->table == index->table); + + col = dict_table_get_nth_col(index->table, i); + + templ = prebuilt->mysql_template + prebuilt->n_template++; + UNIV_MEM_INVALID(templ, sizeof *templ); + templ->col_no = i; + templ->clust_rec_field_no = dict_col_get_clust_pos(col, clust_index); + ut_a(templ->clust_rec_field_no != ULINT_UNDEFINED); + + if (dict_index_is_clust(index)) { + templ->rec_field_no = templ->clust_rec_field_no; + } else { + templ->rec_field_no = dict_index_get_nth_col_pos(index, i); + } + + if (field->null_ptr) { + templ->mysql_null_byte_offset = + (ulint) ((char*) field->null_ptr + - (char*) table->record[0]); + + templ->mysql_null_bit_mask = (ulint) field->null_bit; + } else { + templ->mysql_null_bit_mask = 0; + } + + templ->mysql_col_offset = (ulint) get_field_offset(table, field); + + templ->mysql_col_len = (ulint) field->pack_length(); + templ->type = col->mtype; + templ->mysql_type = (ulint)field->type(); + + if (templ->mysql_type == DATA_MYSQL_TRUE_VARCHAR) { + templ->mysql_length_bytes = (ulint) + (((Field_varstring*)field)->length_bytes); + } + + templ->charset = dtype_get_charset_coll(col->prtype); + templ->mbminlen = col->mbminlen; + templ->mbmaxlen = col->mbmaxlen; + templ->is_unsigned = col->prtype & DATA_UNSIGNED; + + if (!dict_index_is_clust(index) + && templ->rec_field_no == ULINT_UNDEFINED) { + prebuilt->need_to_access_clustered = TRUE; + } + + if (prebuilt->mysql_prefix_len < templ->mysql_col_offset + + templ->mysql_col_len) { + prebuilt->mysql_prefix_len = templ->mysql_col_offset + + templ->mysql_col_len; + } + + if (templ->type == DATA_BLOB) { + prebuilt->templ_contains_blob = TRUE; + } + + return(templ); +} + +/**************************************************************//** Builds a 'template' to the prebuilt struct. The template is used in fast retrieval of just those column values MySQL needs in its processing. */ -static +UNIV_INTERN void -build_template( -/*===========*/ - row_prebuilt_t* prebuilt, /*!< in/out: prebuilt struct */ - THD* thd, /*!< in: current user thread, used - only if templ_type is - ROW_MYSQL_REC_FIELDS */ - TABLE* table, /* in: MySQL table */ - ha_innobase* file, /* in: ha_innobase handler */ - uint templ_type) /* in: ROW_MYSQL_WHOLE_ROW or - ROW_MYSQL_REC_FIELDS */ +ha_innobase::build_template( +/*========================*/ + bool whole_row) /*!< in: true=ROW_MYSQL_WHOLE_ROW, + false=ROW_MYSQL_REC_FIELDS */ { dict_index_t* index; dict_index_t* clust_index; - mysql_row_templ_t* templ; - Field* field; ulint n_fields, n_stored_fields; - ulint n_requested_fields = 0; ibool fetch_all_in_key = FALSE; ibool fetch_primary_key_cols = FALSE; - ulint sql_idx, innodb_idx=0; - /* byte offset of the end of last requested column */ - ulint mysql_prefix_len = 0; - ibool do_idx_cond_push= FALSE; - ibool need_second_pass= FALSE; - + ulint i, sql_idx; + if (prebuilt->select_lock_type == LOCK_X) { /* We always retrieve the whole clustered index record if we use exclusive row level locks, for example, if the read is done in an UPDATE statement. */ - templ_type = ROW_MYSQL_WHOLE_ROW; - } - - if (templ_type == ROW_MYSQL_REC_FIELDS) { + whole_row = true; + } else if (!whole_row) { if (prebuilt->hint_need_to_fetch_extra_cols == ROW_RETRIEVE_ALL_COLS) { @@ -4821,7 +4952,7 @@ build_template( fetch_all_in_key = TRUE; } else { - templ_type = ROW_MYSQL_WHOLE_ROW; + whole_row = true; } } else if (prebuilt->hint_need_to_fetch_extra_cols == ROW_RETRIEVE_PRIMARY_KEY) { @@ -4838,19 +4969,12 @@ build_template( clust_index = dict_table_get_first_index(prebuilt->table); - if (templ_type == ROW_MYSQL_REC_FIELDS) { - index = prebuilt->index; - } else { - index = clust_index; - } + index = whole_row ? clust_index : prebuilt->index; - if (index == clust_index) { - prebuilt->need_to_access_clustered = TRUE; - } else { - prebuilt->need_to_access_clustered = FALSE; - /* Below we check column by column if we need to access - the clustered index */ - } + prebuilt->need_to_access_clustered = (index == clust_index); + + /* Below we check column by column if we need to access + the clustered index. */ n_fields = (ulint)table->s->fields; /* number of columns */ n_stored_fields= (ulint)table->s->stored_fields; /* number of stored columns */ @@ -4860,160 +4984,206 @@ build_template( mem_alloc(n_stored_fields * sizeof(mysql_row_templ_t)); } - prebuilt->template_type = templ_type; + prebuilt->template_type = whole_row + ? ROW_MYSQL_WHOLE_ROW : ROW_MYSQL_REC_FIELDS; prebuilt->null_bitmap_len = table->s->null_bytes; + /* Prepare to build prebuilt->mysql_template[]. */ prebuilt->templ_contains_blob = FALSE; + prebuilt->mysql_prefix_len = 0; + prebuilt->n_template = 0; + prebuilt->idx_cond_n_cols = 0; + + /* Note that in InnoDB, i is the column number in the table. + MySQL calls columns 'fields'. */ + + if (active_index != MAX_KEY && active_index == pushed_idx_cond_keyno) { + /* Push down an index condition or an end_range check. */ + for (i = 0, sql_idx = 0; i < n_stored_fields; i++, sql_idx++) { + + while (!table->field[sql_idx]->stored_in_db) { + sql_idx++; + } + + const ibool index_contains + = dict_index_contains_col_or_prefix(index, i); + + /* Test if an end_range or an index condition + refers to the field. Note that "index" and + "index_contains" may refer to the clustered index. + Index condition pushdown is relative to prebuilt->index + (the index that is being looked up first). */ + + /* When join_read_always_key() invokes this + code via handler::ha_index_init() and + ha_innobase::index_init(), end_range is not + yet initialized. Because of that, we must + always check for index_contains, instead of + the subset + field->part_of_key.is_set(active_index) + which would be acceptable if end_range==NULL. */ + if (index == prebuilt->index + ? index_contains + : dict_index_contains_col_or_prefix( + prebuilt->index, i)) { + /* Needed in ICP */ + const Field* field; + mysql_row_templ_t* templ; + + if (whole_row) { + field = table->field[sql_idx]; + } else { + field = build_template_needs_field( + index_contains, + prebuilt->read_just_key, + fetch_all_in_key, + fetch_primary_key_cols, + index, table, i, sql_idx); + if (!field) { + continue; + } + } - - /* - Setup index condition pushdown (note: we don't need to check if - this is a scan on primary key as that is checked in idx_cond_push) - */ - if (file->active_index == file->pushed_idx_cond_keyno && - file->active_index != MAX_KEY && - templ_type == ROW_MYSQL_REC_FIELDS) - do_idx_cond_push= need_second_pass= TRUE; - - /* Note that in InnoDB, i is the column number. MySQL calls columns - 'fields'. */ - for (sql_idx = 0; sql_idx < n_fields; sql_idx++) { - templ = prebuilt->mysql_template + n_requested_fields; - field = table->field[sql_idx]; - if (!field->stored_in_db) - goto skip_field; - - if (UNIV_LIKELY(templ_type == ROW_MYSQL_REC_FIELDS)) { - /* Decide which columns we should fetch - and which we can skip. */ - register const ibool index_contains_field = - dict_index_contains_col_or_prefix(index, innodb_idx); - register const ibool index_covers_field = - field->part_of_key.is_set(file->active_index); - - if (!index_contains_field && prebuilt->read_just_key) { - /* If this is a 'key read', we do not need - columns that are not in the key */ - - goto skip_field; - } - - if (index_contains_field && fetch_all_in_key) { - /* This field is needed in the query */ - - goto include_field; - } - - if (bitmap_is_set(table->read_set, sql_idx) || - bitmap_is_set(table->write_set, sql_idx)) { - /* This field is needed in the query */ - - goto include_field; - } + templ = build_template_field( + prebuilt, clust_index, index, + table, field, i); + prebuilt->idx_cond_n_cols++; + ut_ad(prebuilt->idx_cond_n_cols + == prebuilt->n_template); + + if (index == prebuilt->index) { + templ->icp_rec_field_no + = templ->rec_field_no; + } else { + templ->icp_rec_field_no + = dict_index_get_nth_col_pos( + prebuilt->index, i); + } - if (fetch_primary_key_cols - && dict_table_col_in_clustered_key( - index->table, innodb_idx)) { - /* This field is needed in the query */ + if (dict_index_is_clust(prebuilt->index)) { + ut_ad(templ->icp_rec_field_no + != ULINT_UNDEFINED); + /* If the primary key includes + a column prefix, use it in + index condition pushdown, + because the condition is + evaluated before fetching any + off-page (externally stored) + columns. */ + if (templ->icp_rec_field_no + < prebuilt->index->n_uniq) { + /* This is a key column; + all set. */ + continue; + } + } else if (templ->icp_rec_field_no + != ULINT_UNDEFINED) { + continue; + } - goto include_field; + /* This is a column prefix index. + The column prefix can be used in + an end_range comparison. */ + + templ->icp_rec_field_no + = dict_index_get_nth_col_or_prefix_pos( + prebuilt->index, i, TRUE); + ut_ad(templ->icp_rec_field_no + != ULINT_UNDEFINED); + + /* Index condition pushdown can be used on + all columns of a secondary index, and on + the PRIMARY KEY columns. */ + /* TODO: enable this assertion + (but first ensure that end_range is + valid here and use an accurate condition + for end_range) + ut_ad(!dict_index_is_clust(prebuilt->index) + || templ->rec_field_no + < prebuilt->index->n_uniq); + */ } - - /* This field is not needed in the query, skip it */ - - goto skip_field; -include_field: - if (do_idx_cond_push && - ((need_second_pass && !index_covers_field) || - (!need_second_pass && index_covers_field))) - goto skip_field; } - n_requested_fields++; - templ->col_no = innodb_idx; - templ->clust_rec_field_no = dict_col_get_clust_pos( - &index->table->cols[innodb_idx], clust_index); - ut_ad(templ->clust_rec_field_no != ULINT_UNDEFINED); + ut_ad(prebuilt->idx_cond_n_cols > 0); + ut_ad(prebuilt->idx_cond_n_cols == prebuilt->n_template); + + /* Include the fields that are not needed in index condition + pushdown. */ + for (i = 0, sql_idx = 0; i < n_stored_fields; i++, sql_idx++) { + + while (!table->field[sql_idx]->stored_in_db) { + sql_idx++; + } + + const ibool index_contains + = dict_index_contains_col_or_prefix(index, i); + + if (index == prebuilt->index + ? !index_contains + : !dict_index_contains_col_or_prefix( + prebuilt->index, i)) { + /* Not needed in ICP */ + const Field* field; + + if (whole_row) { + field = table->field[sql_idx]; + } else { + field = build_template_needs_field( + index_contains, + prebuilt->read_just_key, + fetch_all_in_key, + fetch_primary_key_cols, + index, table, i, sql_idx); + if (!field) { + continue; + } + } - if (index == clust_index) { - templ->rec_field_no = templ->clust_rec_field_no; - } else { - templ->rec_field_no = dict_index_get_nth_col_pos( - index, innodb_idx); - if (templ->rec_field_no == ULINT_UNDEFINED) { - prebuilt->need_to_access_clustered = TRUE; + build_template_field(prebuilt, + clust_index, index, + table, field, i); } } - if (field->null_ptr) { - templ->mysql_null_byte_offset = - (ulint) ((char*) field->null_ptr - - (char*) table->record[0]); + prebuilt->idx_cond = this; + } else { + /* No index condition pushdown */ + prebuilt->idx_cond = NULL; - templ->mysql_null_bit_mask = (ulint) field->null_bit; - } else { - templ->mysql_null_bit_mask = 0; - } + for (i = 0, sql_idx = 0; i < n_stored_fields; i++, sql_idx++) { + const Field* field; - templ->mysql_col_offset = (ulint) - get_field_offset(table, field); - - templ->mysql_col_len = (ulint) field->pack_length(); - if (mysql_prefix_len < templ->mysql_col_offset - + templ->mysql_col_len) { - mysql_prefix_len = templ->mysql_col_offset - + templ->mysql_col_len; - } - templ->type = index->table->cols[innodb_idx].mtype; - templ->mysql_type = (ulint)field->type(); + while (!table->field[sql_idx]->stored_in_db) { + sql_idx++; + } - if (templ->mysql_type == DATA_MYSQL_TRUE_VARCHAR) { - templ->mysql_length_bytes = (ulint) - (((Field_varstring*)field)->length_bytes); - } + if (whole_row) { + field = table->field[sql_idx]; + } else { + field = build_template_needs_field( + dict_index_contains_col_or_prefix( + index, i), + prebuilt->read_just_key, + fetch_all_in_key, + fetch_primary_key_cols, + index, table, i, sql_idx); + if (!field) { + continue; + } + } - templ->charset = dtype_get_charset_coll( - index->table->cols[innodb_idx].prtype); - templ->mbminlen = index->table->cols[innodb_idx].mbminlen; - templ->mbmaxlen = index->table->cols[innodb_idx].mbmaxlen; - templ->is_unsigned = index->table->cols[innodb_idx].prtype - & DATA_UNSIGNED; - if (templ->type == DATA_BLOB) { - prebuilt->templ_contains_blob = TRUE; - } -skip_field: - if (need_second_pass && (sql_idx+1 == n_fields)) - { - prebuilt->n_index_fields= n_requested_fields; - need_second_pass= FALSE; - sql_idx= (~(ulint)0); /* to start from 0 */ - innodb_idx= (~(ulint)0); /* to start from 0 */ ///psergey-merge-merge-last-change + build_template_field(prebuilt, clust_index, index, + table, field, i); } - if (field->stored_in_db) { - innodb_idx++; - } - } - - prebuilt->n_template = n_requested_fields; - prebuilt->mysql_prefix_len = mysql_prefix_len; - - if (do_idx_cond_push) - { - prebuilt->idx_cond_func= index_cond_func_innodb; - prebuilt->idx_cond_func_arg= file; - } - else - { - prebuilt->idx_cond_func= NULL; - prebuilt->n_index_fields= n_requested_fields; } if (index != clust_index && prebuilt->need_to_access_clustered) { /* Change rec_field_no's to correspond to the clustered index record */ - for (ulint i = do_idx_cond_push? prebuilt->n_index_fields : 0; - i < n_requested_fields; i++) { - templ = prebuilt->mysql_template + i; + for (i = 0; i < prebuilt->n_template; i++) { + mysql_row_templ_t* templ + = &prebuilt->mysql_template[i]; templ->rec_field_no = templ->clust_rec_field_no; } } @@ -5276,7 +5446,7 @@ no_commit: /* Build the template used in converting quickly between the two database formats */ - build_template(prebuilt, NULL, table, this, ROW_MYSQL_WHOLE_ROW); + build_template(true); } innodb_srv_conc_enter_innodb(prebuilt->trx); @@ -5977,8 +6147,7 @@ ha_innobase::index_read( necessarily prebuilt->index, but can also be the clustered index */ if (prebuilt->sql_stat_start) { - build_template(prebuilt, user_thd, table, this, - ROW_MYSQL_REC_FIELDS); + build_template(false); } if (key_ptr) { @@ -6193,7 +6362,7 @@ ha_innobase::change_active_index( the flag ROW_MYSQL_WHOLE_ROW below, but that caused unnecessary copying. Starting from MySQL-4.1 we use a more efficient flag here. */ - build_template(prebuilt, user_thd, table, this, ROW_MYSQL_REC_FIELDS); + build_template(false); DBUG_RETURN(0); } @@ -8588,7 +8757,7 @@ ha_innobase::check( /* Build the template; we will use a dummy template in index scans done in checking */ - build_template(prebuilt, NULL, table, this, ROW_MYSQL_WHOLE_ROW); + build_template(true); } if (prebuilt->table->ibd_file_missing) { @@ -9080,12 +9249,7 @@ ha_innobase::extra( } break; case HA_EXTRA_RESET_STATE: - reset_template(prebuilt); - /* Reset index condition pushdown state */ - pushed_idx_cond= FALSE; - pushed_idx_cond_keyno= MAX_KEY; - prebuilt->idx_cond_func= NULL; - in_range_check_pushed_down= FALSE; + reset_template(); break; case HA_EXTRA_NO_KEYREAD: prebuilt->read_just_key = 0; @@ -9131,14 +9295,8 @@ ha_innobase::reset() row_mysql_prebuilt_free_blob_heap(prebuilt); } - reset_template(prebuilt); - - /* Reset index condition pushdown state */ - pushed_idx_cond_keyno= MAX_KEY; - pushed_idx_cond= NULL; - in_range_check_pushed_down= FALSE; + reset_template(); ds_mrr.dsmrr_close(); - prebuilt->idx_cond_func= NULL; /* TODO: This should really be reset in reset_template() but for now it's safer to do it explicitly here. */ @@ -9188,7 +9346,7 @@ ha_innobase::start_stmt( prebuilt->sql_stat_start = TRUE; prebuilt->hint_need_to_fetch_extra_cols = 0; - reset_template(prebuilt); + reset_template(); if (!prebuilt->mysql_has_locked) { /* This handle is for a temporary table created inside @@ -9307,7 +9465,7 @@ ha_innobase::external_lock( prebuilt->sql_stat_start = TRUE; prebuilt->hint_need_to_fetch_extra_cols = 0; - reset_template(prebuilt); + reset_template(); if (lock_type == F_WRLCK) { @@ -9490,7 +9648,7 @@ ha_innobase::transactional_table_lock( prebuilt->sql_stat_start = TRUE; prebuilt->hint_need_to_fetch_extra_cols = 0; - reset_template(prebuilt); + reset_template(); if (lock_type == F_WRLCK) { prebuilt->select_lock_type = LOCK_X; @@ -12306,39 +12464,47 @@ bool ha_innobase::is_thd_killed() * Index Condition Pushdown interface implementation */ -C_MODE_START - -/* - Index condition check function to be called from within Innobase. - See note on ICP_RESULT for return values description. -*/ - -static xtradb_icp_result_t index_cond_func_innodb(void *arg) +/*************************************************************//** +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*)arg; + ha_innobase *h= (ha_innobase*) file; + if (h->is_thd_killed()) - return XTRADB_ICP_ABORTED_BY_USER; + return ICP_ABORTED_BY_USER; if (h->end_range) { if (h->compare_key2(h->end_range) > 0) - return XTRADB_ICP_OUT_OF_RANGE; /* caller should return HA_ERR_END_OF_FILE already */ + return ICP_OUT_OF_RANGE; /* caller should return HA_ERR_END_OF_FILE already */ } - return h->pushed_idx_cond->val_int()? XTRADB_ICP_MATCH : XTRADB_ICP_NO_MATCH; + return h->pushed_idx_cond->val_int()? ICP_MATCH : ICP_NO_MATCH; } -C_MODE_END - - -Item *ha_innobase::idx_cond_push(uint keyno_arg, Item* idx_cond_arg) -{ - if (keyno_arg != primary_key && prebuilt->select_lock_type != LOCK_X) - { - pushed_idx_cond_keyno= keyno_arg; - pushed_idx_cond= idx_cond_arg; - in_range_check_pushed_down= TRUE; - return NULL; /* Table handler will check the entire condition */ - } - return idx_cond_arg; /* Table handler will not make any checks */ +/** Attempt to push down an index condition. +* @param[in] keyno MySQL key number +* @param[in] idx_cond Index condition to be checked +* @return idx_cond if pushed; NULL if not pushed +*/ +UNIV_INTERN +class Item* +ha_innobase::idx_cond_push( + uint keyno, + class Item* idx_cond) +{ + DBUG_ENTER("ha_innobase::idx_cond_push"); + DBUG_ASSERT(keyno != MAX_KEY); + DBUG_ASSERT(idx_cond != NULL); + + pushed_idx_cond = idx_cond; + pushed_idx_cond_keyno = keyno; + in_range_check_pushed_down = TRUE; + /* Table handler will check the entire condition */ + DBUG_RETURN(NULL); } diff --git a/storage/xtradb/handler/ha_innodb.h b/storage/xtradb/handler/ha_innodb.h index 599b48287e3..7b263db2537 100644 --- a/storage/xtradb/handler/ha_innodb.h +++ b/storage/xtradb/handler/ha_innodb.h @@ -223,27 +223,81 @@ 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. + + The template is used in fast retrieval of just those column + values MySQL needs in its processing. + @param whole_row true if access is needed to a whole row, + false if accessing individual fields is enough */ + void build_template(bool whole_row); + /** Resets a query execution 'template'. + @see build_template() */ + inline void reset_template(); + public: - /** - * Multi Range Read interface - */ - int multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param, - uint n_ranges, uint mode, HANDLER_BUFFER *buf); - int multi_range_read_next(range_id_t *range_info); - ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, - void *seq_init_param, - uint n_ranges, uint *bufsz, + /** @name Multi Range Read interface @{ */ + /** Initialize multi range read @see DsMrr_impl::dsmrr_init + * @param seq + * @param seq_init_param + * @param n_ranges + * @param mode + * @param buf + */ + int multi_range_read_init(RANGE_SEQ_IF* seq, + void* seq_init_param, + uint n_ranges, uint mode, + HANDLER_BUFFER *buf); + /** Process next multi range read @see DsMrr_impl::dsmrr_next + * @param range_info + */ + int multi_range_read_next(range_id_t *range_info); + /** Initialize multi range read and get information. + * @see ha_myisam::multi_range_read_info_const + * @see DsMrr_impl::dsmrr_info_const + * @param keyno + * @param seq + * @param seq_init_param + * @param n_ranges + * @param bufsz + * @param flags + * @param cost + */ + ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, + void *seq_init_param, + uint n_ranges, uint *bufsz, + uint *flags, COST_VECT *cost); + /** Initialize multi range read and get information. + * @see DsMrr_impl::dsmrr_info + * @param keyno + * @param n_ranges + * @param keys + * @param key_parts + * @param bufsz + * @param flags + * @param cost + */ + ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, + uint key_parts, uint *bufsz, uint *flags, COST_VECT *cost); - ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint key_parts, uint *bufsz, - uint *flags, COST_VECT *cost); - int multi_range_read_explain_info(uint mrr_mode, char *str, size_t size); - DsMrr_impl ds_mrr; + int multi_range_read_explain_info(uint mrr_mode, + char *str, size_t size); + + /** Attempt to push down an index condition. + * @param[in] keyno MySQL key number + * @param[in] idx_cond Index condition to be checked + * @return idx_cond if pushed; NULL if not pushed + */ + class Item* idx_cond_push(uint keyno, class Item* idx_cond); + + /* An helper function for index_cond_func_innodb: */ + bool is_thd_killed(); - Item *idx_cond_push(uint keyno, Item* idx_cond); +private: + /** The multi range read session object */ + DsMrr_impl ds_mrr; - /* An helper function for index_cond_func_innodb: */ - bool is_thd_killed(); + /* @} */ }; /* Some accessor functions which the InnoDB plugin needs, but which diff --git a/storage/xtradb/include/dict0dict.h b/storage/xtradb/include/dict0dict.h index 2baecdc958a..9c5925c1301 100644 --- a/storage/xtradb/include/dict0dict.h +++ b/storage/xtradb/include/dict0dict.h @@ -832,13 +832,25 @@ dict_index_get_nth_col_no( Looks for column n in an index. @return position in internal representation of the index; ULINT_UNDEFINED if not contained */ -UNIV_INTERN +UNIV_INLINE ulint dict_index_get_nth_col_pos( /*=======================*/ const dict_index_t* index, /*!< in: index */ ulint n); /*!< in: column number */ /********************************************************************//** +Looks for column n in an index. +@return position in internal representation of the index; +ULINT_UNDEFINED if not contained */ +UNIV_INTERN +ulint +dict_index_get_nth_col_or_prefix_pos( +/*=================================*/ + const dict_index_t* index, /*!< in: index */ + ulint n, /*!< in: column number */ + ibool inc_prefix); /*!< in: TRUE=consider + column prefixes too */ +/********************************************************************//** Returns TRUE if the index contains a column or a prefix of that column. @return TRUE if contains the column or its prefix */ UNIV_INTERN diff --git a/storage/xtradb/include/dict0dict.ic b/storage/xtradb/include/dict0dict.ic index bd7534dc7e2..02527d8edd2 100644 --- a/storage/xtradb/include/dict0dict.ic +++ b/storage/xtradb/include/dict0dict.ic @@ -656,6 +656,20 @@ dict_index_get_nth_col_no( return(dict_col_get_no(dict_index_get_nth_col(index, pos))); } +/********************************************************************//** +Looks for column n in an index. +@return position in internal representation of the index; +ULINT_UNDEFINED if not contained */ +UNIV_INLINE +ulint +dict_index_get_nth_col_pos( +/*=======================*/ + const dict_index_t* index, /*!< in: index */ + ulint n) /*!< in: column number */ +{ + return(dict_index_get_nth_col_or_prefix_pos(index, n, FALSE)); +} + #ifndef UNIV_HOTBACKUP /********************************************************************//** Returns the minimum data size of an index record. diff --git a/storage/xtradb/include/ha_prototypes.h b/storage/xtradb/include/ha_prototypes.h index 445d94eeabb..db71c37afc3 100644 --- a/storage/xtradb/include/ha_prototypes.h +++ b/storage/xtradb/include/ha_prototypes.h @@ -247,6 +247,15 @@ innobase_get_at_most_n_mbchars( ulint data_len, /*!< in: length of the string in bytes */ const char* str); /*!< in: character string */ +/*************************************************************//** +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( +/*================*/ + void* file) /*!< in/out: pointer to ha_innobase */ + __attribute__((nonnull, warn_unused_result)); /******************************************************************//** Returns true if the thread supports XA, global value of innodb_supports_xa if thd is NULL. diff --git a/storage/xtradb/include/row0mysql.h b/storage/xtradb/include/row0mysql.h index cd9dec2f089..b18e1fca5dc 100644 --- a/storage/xtradb/include/row0mysql.h +++ b/storage/xtradb/include/row0mysql.h @@ -547,6 +547,10 @@ struct mysql_row_templ_struct { Innobase record in the clustered index; not defined if template_type is ROW_MYSQL_WHOLE_ROW */ + ulint icp_rec_field_no; /*!< field number of the column in an + Innobase record in the current index; + not defined unless + index condition pushdown is used */ ulint mysql_col_offset; /*!< offset of the column in the MySQL row format */ ulint mysql_col_len; /*!< length of the column in the MySQL @@ -585,16 +589,6 @@ struct mysql_row_templ_struct { #define ROW_PREBUILT_ALLOCATED 78540783 #define ROW_PREBUILT_FREED 26423527 - -typedef enum xtradb_icp_result { - XTRADB_ICP_ERROR=-1, - XTRADB_ICP_NO_MATCH=0, - XTRADB_ICP_MATCH=1, - XTRADB_ICP_OUT_OF_RANGE=2, - XTRADB_ICP_ABORTED_BY_USER=3, -} xtradb_icp_result_t; - -typedef xtradb_icp_result_t (*index_cond_func_t)(void *param); /** A struct for (sometimes lazily) prebuilt structures in an Innobase table handle used within MySQL; these are used to save CPU time. */ @@ -792,16 +786,15 @@ struct row_prebuilt_struct { store it here so that we can return it to MySQL */ /*----------------------*/ + void* idx_cond; /*!< In ICP, pointer to a ha_innobase, + passed to innobase_index_cond(). + NULL if index condition pushdown is + not used. */ + ulint idx_cond_n_cols;/*!< Number of fields in idx_cond_cols. + 0 if and only if idx_cond == NULL. */ + /*----------------------*/ ulint magic_n2; /*!< this should be the same as magic_n */ - /*----------------------*/ - index_cond_func_t idx_cond_func;/* Index Condition Pushdown function, - or NULL if there is none set */ - void* idx_cond_func_arg;/* ICP function argument */ - ulint n_index_fields; /* Number of fields at the start of - mysql_template. Valid only when using - ICP. */ - /*----------------------*/ }; #define ROW_PREBUILT_FETCH_MAGIC_N 465765687 diff --git a/storage/xtradb/row/row0sel.c b/storage/xtradb/row/row0sel.c index 6c66feb487c..1dbd9e3a42d 100644 --- a/storage/xtradb/row/row0sel.c +++ b/storage/xtradb/row/row0sel.c @@ -58,6 +58,8 @@ Created 12/19/1997 Heikki Tuuri #include "buf0lru.h" #include "ha_prototypes.h" +#include "my_compare.h" /* enum icp_result */ + /* Maximum number of rows to prefetch; MySQL interface has another parameter */ #define SEL_MAX_N_PREFETCH 16 @@ -2674,144 +2676,96 @@ row_sel_field_store_in_mysql_format( } /**************************************************************//** -Convert a row in the Innobase format to a row in the MySQL format. -Note that the template in prebuilt may advise us to copy only a few -columns to mysql_rec, other columns are left blank. All columns may not -be needed in the query. -@return TRUE on success, FALSE if not all columns could be retrieved */ +Convert a field in the Innobase format to a field in the MySQL format. */ static __attribute__((warn_unused_result)) ibool -row_sel_store_mysql_rec( -/*====================*/ - byte* mysql_rec, /*!< out: row in the MySQL format */ - row_prebuilt_t* prebuilt, /*!< in: prebuilt struct */ - const rec_t* rec, /*!< in: Innobase record in the index - which was described in prebuilt's - template, or in the clustered index; - must be protected by a page latch */ - ibool rec_clust, /*!< in: TRUE if rec is in the - clustered index instead of - prebuilt->index */ - const ulint* offsets, /* in: array returned by - rec_get_offsets() */ - ulint start_field_no, /* in: start from this field */ - ulint end_field_no) /* in: end at this field */ +row_sel_store_mysql_field( +/*======================*/ + byte* mysql_rec, /*!< out: record in the + MySQL format */ + row_prebuilt_t* prebuilt, /*!< in/out: prebuilt struct */ + const rec_t* rec, /*!< in: InnoDB record; + must be protected by + a page latch */ + const ulint* offsets, /*!< in: array returned by + rec_get_offsets() */ + ulint field_no, /*!< in: templ->rec_field_no or + templ->clust_rec_field_no */ + const mysql_row_templ_t*templ) /*!< in: row template */ { - mem_heap_t* extern_field_heap = NULL; - mem_heap_t* heap; - ulint i; + const byte* data; + ulint len; - ut_ad(prebuilt->mysql_template); ut_ad(prebuilt->default_rec); + ut_ad(templ); + ut_ad(templ >= prebuilt->mysql_template); + ut_ad(templ < &prebuilt->mysql_template[prebuilt->n_template]); + ut_ad(field_no == templ->clust_rec_field_no + || field_no == templ->rec_field_no + || field_no == templ->icp_rec_field_no); ut_ad(rec_offs_validate(rec, NULL, offsets)); - ut_ad(!rec_get_deleted_flag(rec, rec_offs_comp(offsets))); - if (UNIV_LIKELY_NULL(prebuilt->blob_heap)) { - mem_heap_free(prebuilt->blob_heap); - prebuilt->blob_heap = NULL; - } - - for (i = start_field_no; i < end_field_no /* prebuilt->n_template */ ; i++) { - - const mysql_row_templ_t*templ = prebuilt->mysql_template + i; - const byte* data; - ulint len; - ulint field_no; - - field_no = rec_clust - ? templ->clust_rec_field_no : templ->rec_field_no; + if (UNIV_UNLIKELY(rec_offs_nth_extern(offsets, field_no))) { - if (UNIV_UNLIKELY(rec_offs_nth_extern(offsets, field_no))) { + mem_heap_t* heap; + /* Copy an externally stored field to a temporary heap */ - /* Copy an externally stored field to the temporary - heap */ + ut_a(!prebuilt->trx->has_search_latch); + ut_ad(field_no == templ->clust_rec_field_no); - ut_a(!prebuilt->trx->has_search_latch); + if (UNIV_UNLIKELY(templ->type == DATA_BLOB)) { + if (prebuilt->blob_heap == NULL) { + prebuilt->blob_heap = mem_heap_create( + UNIV_PAGE_SIZE); + } - if (UNIV_UNLIKELY(templ->type == DATA_BLOB)) { - if (prebuilt->blob_heap == NULL) { - prebuilt->blob_heap = mem_heap_create( - UNIV_PAGE_SIZE); - } + heap = prebuilt->blob_heap; + } else { + heap = mem_heap_create(UNIV_PAGE_SIZE); + } - heap = prebuilt->blob_heap; - } else { - extern_field_heap - = mem_heap_create(UNIV_PAGE_SIZE); + /* NOTE: if we are retrieving a big BLOB, we may + already run out of memory in the next call, which + causes an assert */ - heap = extern_field_heap; - } + data = btr_rec_copy_externally_stored_field( + rec, offsets, + dict_table_zip_size(prebuilt->table), + field_no, &len, heap); - /* NOTE: if we are retrieving a big BLOB, we may - already run out of memory in the next call, which - causes an assert */ - - data = btr_rec_copy_externally_stored_field( - rec, offsets, - dict_table_zip_size(prebuilt->table), - field_no, &len, heap); - - if (UNIV_UNLIKELY(!data)) { - /* The externally stored field - was not written yet. This - record should only be seen by - recv_recovery_rollback_active() - or any TRX_ISO_READ_UNCOMMITTED - transactions. */ - - if (extern_field_heap) { - mem_heap_free(extern_field_heap); - } + if (UNIV_UNLIKELY(!data)) { + /* The externally stored field was not written + yet. This record should only be seen by + recv_recovery_rollback_active() or any + TRX_ISO_READ_UNCOMMITTED transactions. */ - return(FALSE); + if (heap != prebuilt->blob_heap) { + mem_heap_free(heap); } - ut_a(len != UNIV_SQL_NULL); - } else { - /* Field is stored in the row. */ - - data = rec_get_nth_field(rec, offsets, field_no, &len); + ut_a(prebuilt->trx->isolation_level + == TRX_ISO_READ_UNCOMMITTED); + return(FALSE); + } - if (UNIV_UNLIKELY(templ->type == DATA_BLOB) - && len != UNIV_SQL_NULL) { + ut_a(len != UNIV_SQL_NULL); - /* It is a BLOB field locally stored in the - InnoDB record: we MUST copy its contents to - prebuilt->blob_heap here because later code - assumes all BLOB values have been copied to a - safe place. */ + row_sel_field_store_in_mysql_format( + mysql_rec + templ->mysql_col_offset, + templ, data, len); - if (prebuilt->blob_heap == NULL) { - prebuilt->blob_heap = mem_heap_create( - UNIV_PAGE_SIZE); - } - - data = memcpy(mem_heap_alloc( - prebuilt->blob_heap, len), - data, len); - } + if (heap != prebuilt->blob_heap) { + mem_heap_free(heap); } + } else { + /* Field is stored in the row. */ - if (len != UNIV_SQL_NULL) { - row_sel_field_store_in_mysql_format( - mysql_rec + templ->mysql_col_offset, - templ, data, len); - - /* Cleanup */ - if (extern_field_heap) { - mem_heap_free(extern_field_heap); - extern_field_heap = NULL; - } + data = rec_get_nth_field(rec, offsets, field_no, &len); - if (templ->mysql_null_bit_mask) { - /* It is a nullable column with a non-NULL - value */ - mysql_rec[templ->mysql_null_byte_offset] - &= ~(byte) templ->mysql_null_bit_mask; - } - } else { + if (len == UNIV_SQL_NULL) { /* MySQL assumes that the field for an SQL NULL value is set to the default value. */ + ut_ad(templ->mysql_null_bit_mask); UNIV_MEM_ASSERT_RW(prebuilt->default_rec + templ->mysql_col_offset, @@ -2822,6 +2776,85 @@ row_sel_store_mysql_rec( (const byte*) prebuilt->default_rec + templ->mysql_col_offset, templ->mysql_col_len); + return(TRUE); + } + + if (UNIV_UNLIKELY(templ->type == DATA_BLOB)) { + + /* It is a BLOB field locally stored in the + InnoDB record: we MUST copy its contents to + prebuilt->blob_heap here because + row_sel_field_store_in_mysql_format() stores a + pointer to the data, and the data passed to us + will be invalid as soon as the + mini-transaction is committed and the page + latch on the clustered index page is + released. */ + + if (prebuilt->blob_heap == NULL) { + prebuilt->blob_heap = mem_heap_create( + UNIV_PAGE_SIZE); + } + + data = mem_heap_dup(prebuilt->blob_heap, data, len); + } + + row_sel_field_store_in_mysql_format( + mysql_rec + templ->mysql_col_offset, + templ, data, len); + } + + ut_ad(len != UNIV_SQL_NULL); + + if (templ->mysql_null_bit_mask) { + /* It is a nullable column with a non-NULL + value */ + mysql_rec[templ->mysql_null_byte_offset] + &= ~(byte) templ->mysql_null_bit_mask; + } + + return(TRUE); +} + +/**************************************************************//** +Convert a row in the Innobase format to a row in the MySQL format. +Note that the template in prebuilt may advise us to copy only a few +columns to mysql_rec, other columns are left blank. All columns may not +be needed in the query. +@return TRUE on success, FALSE if not all columns could be retrieved */ +static __attribute__((warn_unused_result)) +ibool +row_sel_store_mysql_rec( +/*====================*/ + byte* mysql_rec, /*!< out: row in the MySQL format */ + row_prebuilt_t* prebuilt, /*!< in: prebuilt struct */ + const rec_t* rec, /*!< in: Innobase record in the index + which was described in prebuilt's + template, or in the clustered index; + must be protected by a page latch */ + ibool rec_clust, /*!< in: TRUE if rec is in the + clustered index instead of + prebuilt->index */ + const ulint* offsets) /*!< in: array returned by + rec_get_offsets(rec) */ +{ + ulint i; + + if (UNIV_LIKELY_NULL(prebuilt->blob_heap)) { + mem_heap_free(prebuilt->blob_heap); + prebuilt->blob_heap = NULL; + } + + for (i = 0; i < prebuilt->n_template; i++) { + const mysql_row_templ_t*templ = &prebuilt->mysql_template[i]; + + if (!row_sel_store_mysql_field(mysql_rec, prebuilt, + rec, offsets, + rec_clust + ? templ->clust_rec_field_no + : templ->rec_field_no, + templ)) { + return(FALSE); } } @@ -3192,31 +3225,19 @@ UNIV_INLINE __attribute__((warn_unused_result)) ibool row_sel_push_cache_row_for_mysql( /*=============================*/ - row_prebuilt_t* prebuilt, /*!< in: prebuilt struct */ - const rec_t* rec, /*!< in: record to push, in the index - which was described in prebuilt's - template, or in the clustered index; - must be protected by a page latch */ - ibool rec_clust, /*!< in: TRUE if rec is in the - clustered index instead of - prebuilt->index */ - const ulint* offsets, /* in: rec_get_offsets() */ - ulint start_field_no, /* in: start from this field */ - byte* remainder_buf) /* in: if start_field_no !=0, - where to take prev fields */ + byte* mysql_rec, /*!< in/out: MySQL record */ + row_prebuilt_t* prebuilt) /*!< in/out: prebuilt struct */ { - byte* buf; - ulint i; - ut_ad(prebuilt->n_fetch_cached < MYSQL_FETCH_CACHE_SIZE); - ut_ad(rec_offs_validate(rec, NULL, offsets)); - ut_ad(!rec_get_deleted_flag(rec, rec_offs_comp(offsets))); ut_a(!prebuilt->templ_contains_blob); - if (prebuilt->fetch_cache[0] == NULL) { + if (UNIV_UNLIKELY(prebuilt->fetch_cache[0] == NULL)) { + ulint i; /* Allocate memory for the fetch cache */ + ut_ad(prebuilt->n_fetch_cached == 0); for (i = 0; i < MYSQL_FETCH_CACHE_SIZE; i++) { + byte* buf; /* A user has reported memory corruption in these buffers in Linux. Put magic numbers there to help @@ -3236,46 +3257,14 @@ row_sel_push_cache_row_for_mysql( UNIV_MEM_INVALID(prebuilt->fetch_cache[prebuilt->n_fetch_cached], prebuilt->mysql_row_len); - if (UNIV_UNLIKELY(!row_sel_store_mysql_rec( - prebuilt->fetch_cache[ - prebuilt->n_fetch_cached], - prebuilt, - rec, - rec_clust, - offsets, - start_field_no, - prebuilt->n_template))) { - return(FALSE); - } - - if (start_field_no) { - - for (i=0; i < start_field_no; i++) { - register ulint offs; - mysql_row_templ_t* templ; - register byte * null_byte; - - templ = prebuilt->mysql_template + i; + memcpy(prebuilt->fetch_cache[prebuilt->n_fetch_cached], + mysql_rec, prebuilt->mysql_row_len); - if (templ->mysql_null_bit_mask) { - offs = templ->mysql_null_byte_offset; - - null_byte= prebuilt->fetch_cache[ - prebuilt->n_fetch_cached]+offs; - (*null_byte)&= ~templ->mysql_null_bit_mask; - (*null_byte)|= (*(remainder_buf + offs) & - templ->mysql_null_bit_mask); - } - - offs = templ->mysql_col_offset; - memcpy(prebuilt->fetch_cache[prebuilt->n_fetch_cached] - + offs, - remainder_buf + offs, - templ->mysql_col_len); - } + if (++prebuilt->n_fetch_cached < MYSQL_FETCH_CACHE_SIZE) { + return(FALSE); } - prebuilt->n_fetch_cached++; + row_sel_pop_cached_row_for_mysql(mysql_rec, prebuilt); return(TRUE); } @@ -3353,6 +3342,81 @@ row_sel_try_search_shortcut_for_mysql( return(SEL_FOUND); } +/*********************************************************************//** +Check a pushed-down index condition. +@return ICP_NO_MATCH, ICP_MATCH, or ICP_OUT_OF_RANGE */ +static +enum icp_result +row_search_idx_cond_check( +/*======================*/ + byte* mysql_rec, /*!< out: record + in MySQL format (invalid unless + prebuilt->idx_cond!=NULL and + we return ICP_MATCH) */ + row_prebuilt_t* prebuilt, /*!< in/out: prebuilt struct + for the table handle */ + const rec_t* rec, /*!< in: InnoDB record */ + const ulint* offsets) /*!< in: rec_get_offsets() */ +{ + enum icp_result result; + ulint i; + + ut_ad(rec_offs_validate(rec, prebuilt->index, offsets)); + + if (!prebuilt->idx_cond) { + return(ICP_MATCH); + } + + /* Convert to MySQL format those fields that are needed for + evaluating the index condition. */ + + if (UNIV_LIKELY_NULL(prebuilt->blob_heap)) { + mem_heap_empty(prebuilt->blob_heap); + } + + for (i = 0; i < prebuilt->idx_cond_n_cols; i++) { + const mysql_row_templ_t*templ = &prebuilt->mysql_template[i]; + + if (!row_sel_store_mysql_field(mysql_rec, prebuilt, + rec, offsets, + templ->icp_rec_field_no, + templ)) { + return(ICP_NO_MATCH); + } + } + + /* We assume that the index conditions on + case-insensitive columns are case-insensitive. The + case of such columns may be wrong in a secondary + 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); + switch (result) { + case ICP_MATCH: + /* Convert the remaining fields to MySQL format. + If this is a secondary index record, we must defer + this until we have fetched the clustered index record. */ + if (!prebuilt->need_to_access_clustered + || dict_index_is_clust(prebuilt->index)) { + if (!row_sel_store_mysql_rec(mysql_rec, prebuilt, + rec, + FALSE, offsets)) { + ut_ad(dict_index_is_clust(prebuilt->index)); + result = ICP_NO_MATCH; + } + } + /* fall through */ + case ICP_NO_MATCH: + case ICP_OUT_OF_RANGE: + case ICP_ABORTED_BY_USER: + return(result); + default: ; + } + + ut_error; +} + /********************************************************************//** Searches for rows in the database. This is used in the interface to MySQL. This function opens a cursor, and also implements fetch next @@ -3415,10 +3479,8 @@ row_search_for_mysql( mem_heap_t* heap = NULL; ulint offsets_[REC_OFFS_NORMAL_SIZE]; ulint* offsets = offsets_; - ibool some_fields_in_buffer; ibool table_lock_waited = FALSE; ibool problematic_use = FALSE; - ibool get_clust_rec = 0; rec_offs_init(offsets_); @@ -3681,10 +3743,24 @@ row_search_for_mysql( mtr_commit(&mtr). */ ut_ad(!rec_get_deleted_flag(rec, comp)); + if (prebuilt->idx_cond) { + switch (row_search_idx_cond_check( + buf, prebuilt, + rec, offsets)) { + case ICP_NO_MATCH: + case ICP_OUT_OF_RANGE: + case ICP_ABORTED_BY_USER: + goto shortcut_mismatch; + case ICP_MATCH: + goto shortcut_match; + default: ; + } + ut_error; + } + if (!row_sel_store_mysql_rec(buf, prebuilt, rec, FALSE, - offsets, 0, - prebuilt->n_template)) { + offsets)) { /* Only fresh inserts may contain incomplete externally stored columns. Pretend that such @@ -3695,13 +3771,12 @@ row_search_for_mysql( rolling back a recovered transaction. Rollback happens at a lower level, not here. */ - ut_a(trx->isolation_level - == TRX_ISO_READ_UNCOMMITTED); /* Proceed as in case SEL_RETRY. */ break; } + shortcut_match: mtr_commit(&mtr); /* ut_print_name(stderr, index->name); @@ -3713,6 +3788,7 @@ row_search_for_mysql( goto release_search_latch_if_needed; case SEL_EXHAUSTED: + shortcut_mismatch: mtr_commit(&mtr); /* ut_print_name(stderr, index->name); @@ -3804,8 +3880,9 @@ retry_check: if (!prebuilt->sql_stat_start) { /* No need to set an intention lock or assign a read view */ - if (trx->read_view == NULL - && prebuilt->select_lock_type == LOCK_NONE) { + if (UNIV_UNLIKELY + (trx->read_view == NULL + && prebuilt->select_lock_type == LOCK_NONE)) { fputs("InnoDB: Error: MySQL is trying to" " perform a consistent read\n" @@ -4265,6 +4342,16 @@ no_gap_lock: if (UNIV_LIKELY(trx->wait_lock != NULL)) { lock_cancel_waiting_and_release( trx->wait_lock); + mutex_exit(&kernel_mutex); + + if (old_vers == NULL) { + /* The row was not yet committed */ + + goto next_rec; + } + + did_semi_consistent_read = TRUE; + rec = old_vers; } else { mutex_exit(&kernel_mutex); @@ -4275,19 +4362,7 @@ no_gap_lock: offsets = rec_get_offsets(rec, index, offsets, ULINT_UNDEFINED, &heap); - err = DB_SUCCESS; - break; } - mutex_exit(&kernel_mutex); - - if (old_vers == NULL) { - /* The row was not yet committed */ - - goto next_rec; - } - - did_semi_consistent_read = TRUE; - rec = old_vers; break; default: @@ -4346,8 +4421,27 @@ no_gap_lock: if (!lock_sec_rec_cons_read_sees( rec, trx->read_view)) { - get_clust_rec = TRUE; - goto idx_cond_check; + /* We should look at the clustered index. + However, as this is a non-locking read, + we can skip the clustered index lookup if + the condition does not match the secondary + index entry. */ + switch (row_search_idx_cond_check( + buf, prebuilt, rec, offsets)) { + case ICP_NO_MATCH: + goto next_rec; + case ICP_OUT_OF_RANGE: + err = DB_RECORD_NOT_FOUND; + goto idx_cond_failed; + case ICP_ABORTED_BY_USER: + err = DB_SEARCH_ABORTED_BY_USER; + goto idx_cond_failed; + case ICP_MATCH: + goto requires_clust_rec; + default: ; + } + + ut_error; } } } @@ -4392,38 +4486,31 @@ no_gap_lock: goto next_rec; } - -idx_cond_check: - if (prebuilt->idx_cond_func) { - int res; - ibool ib_res __attribute__ ((unused)); - ut_ad(prebuilt->template_type != ROW_MYSQL_DUMMY_TEMPLATE); - offsets = rec_get_offsets(rec, index, offsets, ULINT_UNDEFINED, &heap); - ib_res= row_sel_store_mysql_rec(buf, prebuilt, rec, FALSE, - offsets, 0, prebuilt->n_index_fields); - /* - The above call will fail and return FALSE when requested to - store an "externally stored column" (afaiu, a blob). Index - Condition Pushdown is not supported for indexes with blob - columns, so we should never get this error. - */ - ut_ad(ib_res); - res= prebuilt->idx_cond_func(prebuilt->idx_cond_func_arg); - if (res == XTRADB_ICP_NO_MATCH) - goto next_rec; - else if (res != XTRADB_ICP_MATCH) { - err= (res == XTRADB_ICP_ABORTED_BY_USER ? - DB_SEARCH_ABORTED_BY_USER : - DB_RECORD_NOT_FOUND); - goto idx_cond_failed; - } - /* res == XTRADB_ICP_MATCH */ - } + /* Check if the record matches the index condition. */ + switch (row_search_idx_cond_check(buf, prebuilt, rec, offsets)) { + case ICP_NO_MATCH: + if (did_semi_consistent_read) { + row_unlock_for_mysql(prebuilt, TRUE); + } + goto next_rec; + case ICP_ABORTED_BY_USER: + err = DB_SEARCH_ABORTED_BY_USER; + goto idx_cond_failed; + case ICP_OUT_OF_RANGE: + err = DB_RECORD_NOT_FOUND; + goto idx_cond_failed; + case ICP_MATCH: + break; + default: + ut_error; + } /* Get the clustered index record if needed, if we did not do the search using the clustered index. */ - if (get_clust_rec || (index != clust_index - && prebuilt->need_to_access_clustered)) { + if (index != clust_index && prebuilt->need_to_access_clustered) { + +requires_clust_rec: + ut_ad(index != clust_index); /* We use a 'goto' to the preceding label if a consistent read of a secondary index record requires us to look up old @@ -4487,6 +4574,19 @@ idx_cond_check: result_rec = clust_rec; ut_ad(rec_offs_validate(result_rec, clust_index, offsets)); + + if (prebuilt->idx_cond) { + /* Convert the remaining fields to + MySQL format. We were unable to do + this in row_search_idx_cond_check(), + because the condition is on the + secondary index and the requested + column is in the clustered index. */ + if (!row_sel_store_mysql_rec(buf, prebuilt, result_rec, + TRUE, offsets)) { + goto next_rec; + } + } } else { result_rec = rec; } @@ -4520,15 +4620,10 @@ idx_cond_check: are BLOBs in the fields to be fetched. In HANDLER we do not cache rows because there the cursor is a scrollable cursor. */ - some_fields_in_buffer = (index != clust_index - && prebuilt->idx_cond_func); - - if (!row_sel_push_cache_row_for_mysql(prebuilt, result_rec, - result_rec != rec, - offsets, - some_fields_in_buffer? - prebuilt->n_index_fields : 0, - buf)) { + + if (!prebuilt->idx_cond + && !row_sel_store_mysql_rec(buf, prebuilt, result_rec, + result_rec != rec, offsets)) { /* Only fresh inserts may contain incomplete externally stored columns. Pretend that such records do not exist. Such records may only be @@ -4536,14 +4631,10 @@ idx_cond_check: level or when rolling back a recovered transaction. Rollback happens at a lower level, not here. */ - ut_a(trx->isolation_level == TRX_ISO_READ_UNCOMMITTED); - } else if (prebuilt->n_fetch_cached - == MYSQL_FETCH_CACHE_SIZE) { - - goto got_row; + goto next_rec; + } else if (row_sel_push_cache_row_for_mysql(buf, prebuilt)) { + goto next_rec; } - - goto next_rec; } else { if (UNIV_UNLIKELY (prebuilt->template_type == ROW_MYSQL_DUMMY_TEMPLATE)) { @@ -4564,15 +4655,11 @@ idx_cond_check: rec_offs_size(offsets)); mach_write_to_4(buf, rec_offs_extra_size(offsets) + 4); - } else { - /* Returning a row to MySQL */ - - if (!row_sel_store_mysql_rec(buf, prebuilt, result_rec, - result_rec != rec, - offsets, - prebuilt->idx_cond_func? - prebuilt->n_index_fields: 0, - prebuilt->n_template)) { + } else if (!prebuilt->idx_cond) { + /* The record was not yet converted to MySQL format. */ + if (!row_sel_store_mysql_rec( + buf, prebuilt, + result_rec, result_rec != rec, offsets)) { /* Only fresh inserts may contain incomplete externally stored columns. Pretend that such records do @@ -4581,8 +4668,6 @@ idx_cond_check: isolation level or when rolling back a recovered transaction. Rollback happens at a lower level, not here. */ - ut_a(trx->isolation_level - == TRX_ISO_READ_UNCOMMITTED); goto next_rec; } } @@ -4600,7 +4685,6 @@ idx_cond_check: /* From this point on, 'offsets' are invalid. */ -got_row: /* We have an optimization to save CPU time: if this is a consistent read on a unique condition on the clustered index, then we do not store the pcur position, because any fetch next or prev will anyway @@ -4624,7 +4708,6 @@ idx_cond_failed: next_rec: /* Reset the old and new "did semi-consistent read" flags. */ - get_clust_rec = FALSE; if (UNIV_UNLIKELY(prebuilt->row_read_type == ROW_READ_DID_SEMI_CONSISTENT)) { prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT; @@ -4635,6 +4718,7 @@ next_rec: /*-------------------------------------------------------------*/ /* PHASE 5: Move the cursor to the next index record */ + /*TODO: with ICP, do this when switching pages, every N pages */ if (UNIV_UNLIKELY(mtr_has_extra_clust_latch)) { /* We must commit mtr if we are moving to the next non-clustered index record, because we could break the |