summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/include/icp_tests.inc33
-rw-r--r--mysql-test/r/innodb_icp.result39
-rw-r--r--mysql-test/r/maria_icp.result39
-rw-r--r--mysql-test/r/myisam_icp.result39
4 files changed, 150 insertions, 0 deletions
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc
index 4b28faa8e41..ebf0b7620fe 100644
--- a/mysql-test/include/icp_tests.inc
+++ b/mysql-test/include/icp_tests.inc
@@ -200,6 +200,39 @@ 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 #
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 42d53f5cfde..8937a9761ff 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -186,6 +186,45 @@ 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 (
diff --git a/mysql-test/r/maria_icp.result b/mysql-test/r/maria_icp.result
index 39edaf67029..ff99f99d79c 100644
--- a/mysql-test/r/maria_icp.result
+++ b/mysql-test/r/maria_icp.result
@@ -186,6 +186,45 @@ 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 (
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index 68f18da3f50..eefa4f795a1 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -184,6 +184,45 @@ 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 (
View Lorry Controller Status