mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
7e4233746e
Allows index condition pushdown for reverse ordered scans, a previously disabled feature due to poor performance. This patch adds a new API to the handler class called set_end_range which allows callers to tell the handler what the end of the index range will be when scanning. Combined with a pushed index condition, the handler can scan the index efficiently and not read beyond the end of the given range. When checking if the pushed index condition matches, the handler will also check if scanning has reached the end of the provided range and stop if so. If we instead only enabled ICP for reverse ordered scans without also calling this new API, then the handler would perform unnecessary index condition checks. In fact this would continue until the end of the index is reached. These changes are agnostic of storage engine. That is, any storage engine that supports index condition pushdown will inhereit this new behavior as it is implemented in the SQL and storage engine API layers. The partitioned tables storage meta-engine (ha_partition) adds an override of set_end_range which recursively calls set_end_range on its child storage engine (handler) implementations. This commit updates the test made in an earlier commit to show that ICP matches happen for the reverse ordered case. This patch is based on changes written by Olav Sandstaa in MySQL commit da1d92fd46071cd86de61058b6ea39fd9affcd87
935 lines
27 KiB
Text
935 lines
27 KiB
Text
set @save_storage_engine= @@default_storage_engine;
|
|
set default_storage_engine=Maria;
|
|
set @maria_icp_tmp=@@optimizer_switch;
|
|
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
|
|
#
|
|
# Bug#36981 - "innodb crash when selecting for update"
|
|
#
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(1),
|
|
c2 CHAR(10),
|
|
KEY (c1)
|
|
);
|
|
INSERT INTO t1 VALUES ('3', null);
|
|
SELECT * FROM t1 WHERE c1='3' FOR UPDATE;
|
|
c1 c2
|
|
3 NULL
|
|
DROP TABLE t1;
|
|
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 + 10*C.a) FROM t1 A, t1 B, t1 C;
|
|
CREATE TABLE t3 (
|
|
c1 CHAR(10) NOT NULL,
|
|
c2 CHAR(10) NOT NULL,
|
|
c3 CHAR(200) NOT NULL,
|
|
KEY (c1)
|
|
);
|
|
INSERT INTO t3
|
|
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',1000+ t2.a,'=w'), 'filler'
|
|
FROM t2;
|
|
INSERT INTO t3
|
|
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',2000+t2.a,'=w'), 'filler-1'
|
|
FROM t2;
|
|
INSERT INTO t3
|
|
SELECT CONCAT('c-',1000+t2.a,'=w'), CONCAT('c-',3000+t2.a,'=w'), 'filler-2'
|
|
FROM t2;
|
|
SELECT c1,c3 FROM t3 WHERE c1 >= 'c-1994=w' and c1 != 'c-1996=w' FOR UPDATE;
|
|
c1 c3
|
|
c-1994=w filler
|
|
c-1994=w filler-1
|
|
c-1994=w filler-2
|
|
c-1995=w filler
|
|
c-1995=w filler-1
|
|
c-1995=w filler-2
|
|
c-1997=w filler
|
|
c-1997=w filler-1
|
|
c-1997=w filler-2
|
|
c-1998=w filler
|
|
c-1998=w filler-1
|
|
c-1998=w filler-2
|
|
c-1999=w filler
|
|
c-1999=w filler-1
|
|
c-1999=w filler-2
|
|
DROP TABLE t1,t2,t3;
|
|
#
|
|
# Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
|
|
# null-safe operator <=> NULL
|
|
#
|
|
CREATE TABLE t1(
|
|
c1 DATE NOT NULL,
|
|
c2 DATE NULL,
|
|
c3 DATETIME,
|
|
c4 TIMESTAMP,
|
|
PRIMARY KEY(c1),
|
|
UNIQUE(c2)
|
|
);
|
|
|
|
INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
|
|
INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
|
|
INSERT INTO t1 VALUES('2008-01-01', NULL , '2008-01-02', '2008-01-03');
|
|
INSERT INTO t1 VALUES('2008-01-17', NULL , NULL , '2009-01-29');
|
|
INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');
|
|
|
|
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
|
|
c1 c2 c3 c4
|
|
2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
|
|
2008-01-17 NULL NULL 2009-01-29 00:00:00
|
|
|
|
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
|
|
c1 c2 c3 c4
|
|
2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
|
|
2008-01-17 NULL NULL 2009-01-29 00:00:00
|
|
|
|
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 FORCE INDEX(PRIMARY)
|
|
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 FORCE INDEX(PRIMARY)
|
|
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)
|
|
) CHARSET=latin1;
|
|
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 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 (
|
|
dummy INT PRIMARY KEY,
|
|
a INT UNIQUE,
|
|
b INT
|
|
);
|
|
INSERT INTO t VALUES (1,1,1),(3,3,3),(5,5,5);
|
|
SELECT * FROM t WHERE a > 2 FOR UPDATE;
|
|
dummy a b
|
|
3 3 3
|
|
5 5 5
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#35080 - Innodb crash at mem_block_get_len line 72
|
|
#
|
|
CREATE TABLE t1 (
|
|
t1_autoinc INT(11) NOT NULL AUTO_INCREMENT,
|
|
uuid VARCHAR(36) DEFAULT NULL,
|
|
PRIMARY KEY (t1_autoinc),
|
|
KEY k (uuid)
|
|
);
|
|
CREATE TABLE t2 (
|
|
t2_autoinc INT(11) NOT NULL AUTO_INCREMENT,
|
|
uuid VARCHAR(36) DEFAULT NULL,
|
|
date DATETIME DEFAULT NULL,
|
|
PRIMARY KEY (t2_autoinc),
|
|
KEY k (uuid)
|
|
);
|
|
CREATE VIEW v1 AS
|
|
SELECT t1_autoinc, uuid
|
|
FROM t1
|
|
WHERE (ISNULL(uuid) OR (uuid like '%-%'));
|
|
CREATE VIEW v2 AS
|
|
SELECT t2_autoinc, uuid, date
|
|
FROM t2
|
|
WHERE (ISNULL(uuid) OR (LENGTH(uuid) = 36));
|
|
CREATE PROCEDURE delete_multi (IN uuid CHAR(36))
|
|
DELETE v1, v2 FROM v1 INNER JOIN v2
|
|
ON v1.uuid = v2.uuid
|
|
WHERE v1.uuid = @uuid;
|
|
SET @uuid = UUID();
|
|
INSERT INTO v1 (uuid) VALUES (@uuid);
|
|
INSERT INTO v2 (uuid, date) VALUES (@uuid, '2009-09-09');
|
|
CALL delete_multi(@uuid);
|
|
DROP procedure delete_multi;
|
|
DROP table t1,t2;
|
|
DROP view v1,v2;
|
|
#
|
|
# Bug#41996 - multi-table delete crashes server (InnoDB table)
|
|
#
|
|
CREATE TABLE t1 (
|
|
b BIGINT,
|
|
i INT,
|
|
KEY (b)
|
|
);
|
|
INSERT INTO t1 VALUES (2, 2);
|
|
DELETE t1 FROM t1 a, t1 WHERE a.i=t1.b;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#43448 - Server crashes on multi table delete with Innodb
|
|
#
|
|
CREATE TABLE t1 (
|
|
id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
t CHAR(12)
|
|
);
|
|
CREATE TABLE t2 (
|
|
id2 INT NOT NULL,
|
|
t CHAR(12)
|
|
);
|
|
CREATE TABLE t3(
|
|
id3 INT NOT NULL,
|
|
t CHAR(12),
|
|
INDEX(id3)
|
|
);
|
|
CREATE PROCEDURE insert_data ()
|
|
BEGIN
|
|
DECLARE i1 INT DEFAULT 20;
|
|
DECLARE i2 INT;
|
|
DECLARE i3 INT;
|
|
WHILE (i1 > 0) DO
|
|
INSERT INTO t1(t) VALUES (i1);
|
|
SET i2 = 2;
|
|
WHILE (i2 > 0) DO
|
|
INSERT INTO t2(id2, t) VALUES (i1, i2);
|
|
SET i3 = 2;
|
|
WHILE (i3 > 0) DO
|
|
INSERT INTO t3(id3, t) VALUES (i1, i2);
|
|
SET i3 = i3 -1;
|
|
END WHILE;
|
|
SET i2 = i2 -1;
|
|
END WHILE;
|
|
SET i1 = i1 - 1;
|
|
END WHILE;
|
|
END |
|
|
CALL insert_data();
|
|
SELECT COUNT(*) FROM t1 WHERE id1 > 10;
|
|
COUNT(*)
|
|
10
|
|
SELECT COUNT(*) FROM t2 WHERE id2 > 10;
|
|
COUNT(*)
|
|
20
|
|
SELECT COUNT(*) FROM t3 WHERE id3 > 10;
|
|
COUNT(*)
|
|
40
|
|
DELETE t1, t2, t3
|
|
FROM t1, t2, t3
|
|
WHERE t1.id1 = t2.id2 AND t2.id2 = t3.id3 AND t1.id1 > 3;
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
3
|
|
SELECT COUNT(*) FROM t2;
|
|
COUNT(*)
|
|
6
|
|
SELECT COUNT(*) FROM t3;
|
|
COUNT(*)
|
|
12
|
|
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 index condition; 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);
|
|
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);
|
|
INSERT INTO t2 VALUES (11,1);
|
|
INSERT INTO t2 VALUES (12,2);
|
|
INSERT INTO t2 VALUES (15,4);
|
|
analyze table t1,t2 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
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 # Using where
|
|
2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func # Using index condition
|
|
2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL # Using index; Using join buffer (flat, BNL join)
|
|
SELECT * FROM t1
|
|
WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10);
|
|
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);
|
|
insert into t1 select seq,seq from seq_100_to_110;
|
|
EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 16 Using index condition; Rowid-ordered scan
|
|
SET SESSION optimizer_switch='index_condition_pushdown=off';
|
|
SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3);
|
|
pk c1
|
|
1 9
|
|
2 7
|
|
4 3
|
|
5 1
|
|
100 100
|
|
101 101
|
|
102 102
|
|
103 103
|
|
104 104
|
|
105 105
|
|
106 106
|
|
107 107
|
|
108 108
|
|
109 109
|
|
110 110
|
|
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 IGNORE 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; 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 IGNORE 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);
|
|
ANALYZE TABLE t1,t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
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
|
|
EXPLAIN
|
|
SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1
|
|
WHERE t1.d1 <> t2.pk 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 t1.d1 <> t2.pk 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) ;
|
|
INSERT IGNORE INTO t1 VALUES (0);
|
|
CREATE TABLE t2 ( f10 int) ;
|
|
INSERT IGNORE INTO t2 VALUES (0);
|
|
CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
|
|
INSERT IGNORE INTO t3 VALUES (6,0),(10,0);
|
|
CREATE TABLE t4 ( f11 int) ;
|
|
INSERT IGNORE INTO t4 VALUES
|
|
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
|
|
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
|
|
set @tmp_778434=@@optimizer_switch;
|
|
SET optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';
|
|
SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
|
|
WHERE (6, 234) IN (
|
|
SELECT t3.f1, t3.f1
|
|
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)) CHARSET=latin1;
|
|
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 # 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 # Using index condition; 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)) CHARSET=latin1;
|
|
INSERT INTO t1 VALUES (1,4,'Ill');
|
|
CREATE TABLE t2 (a varchar(1024), KEY (a(512))) CHARSET=latin1;
|
|
INSERT INTO t2 VALUES
|
|
('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w');
|
|
insert into t2 select seq from seq_1_to_100;
|
|
analyze table t1,t2 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
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 OR 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 SIMPLE t2 ref a a 515 const # Using where
|
|
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR 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 OR 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 SIMPLE t2 ref a a 515 const # Using where
|
|
SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0)
|
|
HAVING t1.c != 5 ORDER BY t1.c;
|
|
b c
|
|
1 4
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# Bug#879871: InnoDB: possible ICP + GROUP BY primary index
|
|
#
|
|
CREATE TABLE t1 (
|
|
a int NOT NULL, b int, c varchar(1), d varchar(1),
|
|
PRIMARY KEY (a), KEY c (c,b)
|
|
);
|
|
INSERT INTO t1 VALUES (10,8,'g','g');
|
|
SET SESSION optimizer_switch='index_condition_pushdown=off';
|
|
SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
|
|
a
|
|
SET SESSION optimizer_switch='index_condition_pushdown=on';
|
|
SELECT a FROM t1 WHERE c IS NULL AND d IS NOT NULL GROUP BY 1;
|
|
a
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#904832: an attempt to perform an index condition pushdown
|
|
# of a condition containing a subquery
|
|
#
|
|
CREATE TABLE t1 (
|
|
a INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
|
|
b INT, c INT, d DATE NOT NULL, e VARCHAR(1),
|
|
KEY (c), KEY (d), KEY k2(b), KEY k3(b), KEY k4(b)
|
|
);
|
|
Warnings:
|
|
Note 1831 Duplicate index `k3`. This is deprecated and will be disallowed in a future release
|
|
Note 1831 Duplicate index `k4`. This is deprecated and will be disallowed in a future release
|
|
INSERT INTO t1 (b,c,d,e) VALUES
|
|
(6,5,'2006-05-25','y'),(1,5,'2008-01-23','t'),
|
|
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
|
|
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
|
|
(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
|
|
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
|
|
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
|
|
(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
|
|
(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
|
|
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
|
|
(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
|
|
(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
|
|
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','f'),
|
|
(4,5,'2001-06-05','x'),(8,5,'1900-01-01','m'),
|
|
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
|
|
(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
|
|
(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
|
|
(4,5,'2006-03-09','b'),(6,5,'2007-06-18','d'),
|
|
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
|
|
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
|
|
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
|
|
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
|
|
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
|
|
(1,5,'2008-01-23','t'),(6,5,'2007-06-18','d'),
|
|
(4,5,'1900-01-01','r'),(8,5,'1900-01-01','m'),
|
|
(4,5,'2006-03-09','b'),(4,5,'2001-06-05','x'),
|
|
(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
|
|
(6,5,'2007-06-18','d'),(4,5,'1900-01-01','r'),
|
|
(8,5,'1900-01-01','m'),(4,5,'2006-03-09','b'),
|
|
(4,5,'2001-06-05','x'),(1,5,'2008-01-23','t'),
|
|
(6,5,'2007-06-18','d'),(4,1,'1900-01-01','r'),
|
|
(8,8,'1900-01-01','m'),(4,1,'2006-03-09','b'),
|
|
(4,1,'2001-06-05','x'),(7,1,'2006-05-28','g');
|
|
CREATE TABLE t2 (
|
|
f INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
|
|
g INT,
|
|
h VARCHAR(1),
|
|
KEY (g)
|
|
);
|
|
INSERT INTO t2 (g,h) VALUES
|
|
(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
|
|
(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),
|
|
(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),
|
|
(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),
|
|
(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
|
|
(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
|
|
(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
|
|
(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
|
|
(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
|
|
(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
|
|
(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),(0,'p'),
|
|
(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
|
|
(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),
|
|
(8,'y'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
|
|
(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
|
|
(1,'x'),(5,'l'),(8,'y'),(0,'p'),(0,'f'),(0,'p'),
|
|
(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),
|
|
(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),(7,'d'),
|
|
(7,'f'),(5,'j'),(3,'e'),(1,'u'),(0,'f'),(0,'p'),
|
|
(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),
|
|
(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),(0,'p'),
|
|
(0,'f'),(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),
|
|
(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),
|
|
(1,'x'),(7,'d'),(7,'f'),(5,'j'),(3,'e'),(1,'u'),
|
|
(4,'v'),(9,'u'),(6,'i'),(1,'x'),(5,'l'),(8,'y'),
|
|
(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
|
|
(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
|
|
(5,'l'),(6,'q'),(2,'n'),(4,'r'),(4,'b'),(8,'y'),
|
|
(0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'),
|
|
(3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'),
|
|
(7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u');
|
|
ANALYZE TABLE t1,t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status Table is already up to date
|
|
SET @save_optimize_switch=@@optimizer_switch;
|
|
SET optimizer_switch='materialization=on';
|
|
set @save_optimizer_where_cost=@@optimizer_where_cost;
|
|
set @@optimizer_where_cost=1;
|
|
EXPLAIN
|
|
SELECT COUNT(*) FROM t1 AS t, t2
|
|
WHERE c = g
|
|
AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b)
|
|
OR a = 0 AND h < 'z' );
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where
|
|
1 PRIMARY t2 ref g g 5 test.t.c 18 Using where
|
|
2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
|
|
SELECT COUNT(*) FROM t1 AS t, t2
|
|
WHERE c = g
|
|
AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b)
|
|
OR a = 0 AND h < 'z' );
|
|
COUNT(*)
|
|
1478
|
|
SELECT COUNT(*) FROM t1 AS t, t2
|
|
WHERE c = g
|
|
AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b)
|
|
OR a = 0 AND h < 'z' );
|
|
COUNT(*)
|
|
1478
|
|
SET optimizer_switch=@save_optimizer_switch;
|
|
set @@optimizer_where_cost=@save_optimizer_where_cost;
|
|
DROP TABLE t1,t2;
|
|
# check "Handler_pushed" status varuiables
|
|
CREATE TABLE t1 (
|
|
c1 CHAR(1),
|
|
c2 CHAR(1),
|
|
KEY (c1)
|
|
);
|
|
INSERT INTO t1 VALUES ('3', '3'),('4','4'),('5','5');
|
|
flush status;
|
|
show status like "Handler_icp%";
|
|
Variable_name Value
|
|
Handler_icp_attempts 0
|
|
Handler_icp_match 0
|
|
SELECT * FROM t1 FORCE INDEX(c1) WHERE (c1='3' or c1='4') and c1 % 2 = 0 ;
|
|
c1 c2
|
|
4 4
|
|
show status like "Handler_icp%";
|
|
Variable_name Value
|
|
Handler_icp_attempts 2
|
|
Handler_icp_match 1
|
|
SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ;
|
|
c1 c2
|
|
4 4
|
|
show status like "Handler_icp%";
|
|
Variable_name Value
|
|
Handler_icp_attempts 2
|
|
Handler_icp_match 1
|
|
DROP TABLE t1;
|
|
create table t1 (a int,b char(5),primary key (a), key (b(1)));
|
|
insert ignore into t1 values ('a','b');
|
|
Warnings:
|
|
Warning 1366 Incorrect integer value: 'a' for column `test`.`t1`.`a` at row 1
|
|
select 1 from t1 where a and b >= 'aa';
|
|
1
|
|
drop table t1;
|
|
#
|
|
# Bug#58015 "Assert in row_sel_field_store_in_mysql_format
|
|
# when running innodb_mrr_icp test"
|
|
#
|
|
create table t1 (a char(2) charset utf8,b double, primary key (a(1)),key (b));
|
|
insert into t1 values ('',1);
|
|
select 1 from t1 where b <= 1 and a <> '';
|
|
1
|
|
drop table t1;
|
|
#
|
|
# MDEV-4778: Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column
|
|
#
|
|
CREATE TABLE t1 (
|
|
c1 TEXT ,
|
|
c2 VARCHAR(2) ,
|
|
INDEX idx1 (c2,c1(2)),
|
|
INDEX idx2 (c2,c1(1))
|
|
) CHARSET=latin1;
|
|
INSERT INTO t1 (c1,c2) VALUES ('aa','x'), ('a' ,'y');
|
|
SELECT * FROM t1 IGNORE INDEX(idx1,idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y');
|
|
c1 c2
|
|
aa x
|
|
a y
|
|
EXPLAIN
|
|
SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using where
|
|
SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y');
|
|
c1 c2
|
|
aa x
|
|
a y
|
|
SELECT * FROM t1 FORCE INDEX(idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y');
|
|
c1 c2
|
|
aa x
|
|
a y
|
|
DROP TABLE t1;
|
|
set default_storage_engine= @save_storage_engine;
|
|
set optimizer_switch=@maria_icp_tmp;
|