summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/index_merge_innodb.result2
-rw-r--r--mysql-test/r/innodb_icp.result41
-rw-r--r--mysql-test/r/innodb_mrr_cpk.result6
-rw-r--r--mysql-test/r/join_cache.result17
-rw-r--r--mysql-test/r/key_cache.result32
-rw-r--r--mysql-test/r/maria_icp.result27
-rw-r--r--mysql-test/r/myisam_icp.result42
-rw-r--r--mysql-test/r/range.result15
-rw-r--r--mysql-test/r/range_mrr_icp.result15
-rw-r--r--mysql-test/r/range_vs_index_merge_innodb.result28
-rw-r--r--mysql-test/r/status.result6
-rw-r--r--mysql-test/r/status_user.result2
-rw-r--r--mysql-test/r/subselect_mat.result13
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result123
-rw-r--r--mysql-test/r/subselect_sj_mat.result13
15 files changed, 340 insertions, 42 deletions
diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result
index 0f9da2ea3b6..50b0147b6ad 100644
--- a/mysql-test/r/index_merge_innodb.result
+++ b/mysql-test/r/index_merge_innodb.result
@@ -549,7 +549,7 @@ primary key (pk1, pk2)
);
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result
index 08238289330..d8e41113f63 100644
--- a/mysql-test/r/innodb_icp.result
+++ b/mysql-test/r/innodb_icp.result
@@ -167,7 +167,7 @@ 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
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where
DROP TABLE t1;
#
@@ -431,7 +431,7 @@ 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 it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition
+2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using where
2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; 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);
@@ -452,7 +452,7 @@ 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
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where
SET SESSION optimizer_switch='index_condition_pushdown=off';
SELECT pk, c1 FROM t1 WHERE pk <> 3;
pk c1
@@ -507,8 +507,8 @@ 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)
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 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);
@@ -680,7 +680,7 @@ 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 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;
@@ -793,7 +793,7 @@ 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 9 Using where
2 DEPENDENT SUBQUERY t1 index PRIMARY d 3 NULL 64 Using where; Using index
-2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index condition; Using where
+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)
@@ -808,5 +808,32 @@ COUNT(*)
1478
SET optimizer_switch=@save_optimizer_switch;
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_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 0
+Handler_pushed_index_cond_filtered 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_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 2
+Handler_pushed_index_cond_filtered 1
+SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ;
+c1 c2
+4 4
+show status like "Handler_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 2
+Handler_pushed_index_cond_filtered 1
+DROP TABLE t1;
set optimizer_switch=@innodb_icp_tmp;
set storage_engine= @save_storage_engine;
diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result
index 81536f2a43b..90f59b96e61 100644
--- a/mysql-test/r/innodb_mrr_cpk.result
+++ b/mysql-test/r/innodb_mrr_cpk.result
@@ -82,7 +82,7 @@ insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020)
explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered scan
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan
select * from t1, t2 where t1.a=t2.a and t1.b=t2.b;
a b filler a b
a-1010=A 1010 filler a-1010=A 1010
@@ -91,7 +91,7 @@ a-1030=A 1030 filler a-1030=A 1030
explain select * from t1, t2 where t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
-1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered scan
+1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan
select * from t1, t2 where t1.a=t2.a;
a b filler a b
a-1010=A 1010 filler a-1010=A 1010
@@ -133,7 +133,7 @@ set join_cache_level=6;
explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
-1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered scan
+1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan
select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100;
a b c filler a b
set optimizer_switch='index_condition_pushdown=off';
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 19de0fb0a5a..db9a3b7090e 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -3506,6 +3506,7 @@ insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
(4,4, 'qwerty');
+flush status;
set join_cache_level=5;
select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
@@ -3519,6 +3520,10 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+show status like "Handler_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 20
+Handler_pushed_index_cond_filtered 16
set join_cache_level=6;
select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
@@ -3532,6 +3537,10 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+show status like "Handler_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 40
+Handler_pushed_index_cond_filtered 32
set join_cache_level=7;
select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
@@ -3545,6 +3554,10 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+show status like "Handler_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 60
+Handler_pushed_index_cond_filtered 48
set join_cache_level=8;
select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
@@ -3558,6 +3571,10 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
+show status like "Handler_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 80
+Handler_pushed_index_cond_filtered 64
drop table t1,t2;
set join_cache_level=default;
#
diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result
index ab07b7b1f5d..fad980c810c 100644
--- a/mysql-test/r/key_cache.result
+++ b/mysql-test/r/key_cache.result
@@ -385,6 +385,22 @@ Variable_name Value
key_cache_block_size 1536
SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size;
DROP TABLE t1;
+#
+# Bug#12361113: crash when load index into cache
+#
+# Note that this creates an empty disabled key cache!
+SET GLOBAL key_cache_none.key_cache_block_size = 1024;
+CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1, 1);
+CACHE INDEX t1 in key_cache_none;
+ERROR HY000: Unknown key cache 'key_cache_none'
+# The bug crashed the server at LOAD INDEX below. Now it will succeed
+# since the default cache is used due to CACHE INDEX failed for
+# key_cache_none.
+LOAD INDEX INTO CACHE t1;
+Table Op Msg_type Msg_text
+test.t1 preload_keys status OK
+DROP TABLE t1;
set global key_buffer_size=@save_key_buffer_size;
set global key_cache_block_size=@save_key_cache_block_size;
select @@key_buffer_size;
@@ -817,19 +833,3 @@ set global keycache1.key_buffer_size=0;
set global keycache2.key_buffer_size=0;
set global key_buffer_size=@save_key_buffer_size;
set global key_cache_segments=@save_key_cache_segments;
-#
-# Bug#12361113: crash when load index into cache
-#
-# Note that this creates an empty disabled key cache!
-SET GLOBAL key_cache_none.key_cache_block_size = 1024;
-CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) ENGINE = MYISAM;
-INSERT INTO t1 VALUES (1, 1);
-CACHE INDEX t1 in key_cache_none;
-ERROR HY000: Unknown key cache 'key_cache_none'
-# The bug crashed the server at LOAD INDEX below. Now it will succeed
-# since the default cache is used due to CACHE INDEX failed for
-# key_cache_none.
-LOAD INDEX INTO CACHE t1;
-Table Op Msg_type Msg_text
-test.t1 preload_keys status OK
-DROP TABLE t1;
diff --git a/mysql-test/r/maria_icp.result b/mysql-test/r/maria_icp.result
index 5739bbaa90c..1b777d63578 100644
--- a/mysql-test/r/maria_icp.result
+++ b/mysql-test/r/maria_icp.result
@@ -814,5 +814,32 @@ COUNT(*)
1478
SET optimizer_switch=@save_optimizer_switch;
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_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 0
+Handler_pushed_index_cond_filtered 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_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 2
+Handler_pushed_index_cond_filtered 1
+SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ;
+c1 c2
+4 4
+show status like "Handler_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 2
+Handler_pushed_index_cond_filtered 1
+DROP TABLE t1;
set storage_engine= @save_storage_engine;
set optimizer_switch=@maria_icp_tmp;
diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result
index e4343a21606..a9b0dba3fb9 100644
--- a/mysql-test/r/myisam_icp.result
+++ b/mysql-test/r/myisam_icp.result
@@ -812,6 +812,33 @@ COUNT(*)
1478
SET optimizer_switch=@save_optimizer_switch;
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_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 0
+Handler_pushed_index_cond_filtered 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_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 2
+Handler_pushed_index_cond_filtered 1
+SELECT * FROM t1 WHERE (c2='3' or c2='4') and c2 % 2 = 0 ;
+c1 c2
+4 4
+show status like "Handler_pushed%";
+Variable_name Value
+Handler_pushed_index_cond_checks 2
+Handler_pushed_index_cond_filtered 1
+DROP TABLE t1;
drop table if exists t0, t1, t1i, t1m;
#
# BUG#826935 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed
@@ -887,4 +914,19 @@ Warning 1292 Truncated incorrect DOUBLE value: 'c'
Warning 1292 Truncated incorrect DOUBLE value: 'c'
SET optimizer_switch=@save_optimizer_switch;
DROP TABLE t1,t2,t3,t4;
+#
+# BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN
+#
+CREATE TABLE t1 (
+b INT,
+c VARCHAR(1) NOT NULL,
+d DATETIME,
+KEY (c, b)
+) ENGINE=MyISAM;
+# INSERT some data
+CREATE TABLE t2 ( a INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(7),(3),(7),(3);
+# Now run a number of ICP queries while trying to kill them
+DROP TABLE t1,t2;
set optimizer_switch=@myisam_icp_tmp;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index a6b48403029..70ab2207e89 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -1769,6 +1769,21 @@ SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
pk i4 pk i4
DROP TABLE t1;
End of 5.1 tests
+#
+# LP Bug #533117: Wrong use_count in SEL_ARG trees
+# (Bug #58731)
+#
+create table t1 (a int, b int, c int, key idx (a,b,c));
+insert into t1 values (0,0,0), (2,2,0), (1,1,1), (2,2,1);
+explain
+select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range idx idx 5 NULL 3 Using where; Using index
+select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1;
+a b c
+2 2 0
+2 2 1
+drop table t1;
create table t1 (f1 datetime, key (f1));
insert into t1 values ('2000-03-09 15:56:59'),('2000-05-05 23:24:28'),('2000-06-13 13:12:06');
select min(f1) from t1 where f1 >= '2006-05-25 07:00:20' and f1 between '2003-11-23 10:00:09' and '2010-01-01 01:01:01' and f1 > '2001-01-01 01:01:01';
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index 5966785c7c3..f52bdbdc587 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -1771,6 +1771,21 @@ SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
pk i4 pk i4
DROP TABLE t1;
End of 5.1 tests
+#
+# LP Bug #533117: Wrong use_count in SEL_ARG trees
+# (Bug #58731)
+#
+create table t1 (a int, b int, c int, key idx (a,b,c));
+insert into t1 values (0,0,0), (2,2,0), (1,1,1), (2,2,1);
+explain
+select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range idx idx 5 NULL 3 Using where; Using index
+select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1;
+a b c
+2 2 0
+2 2 1
+drop table t1;
create table t1 (f1 datetime, key (f1));
insert into t1 values ('2000-03-09 15:56:59'),('2000-05-05 23:24:28'),('2000-06-13 13:12:06');
select min(f1) from t1 where f1 >= '2006-05-25 07:00:20' and f1 between '2003-11-23 10:00:09' and '2010-01-01 01:01:01' and f1 > '2001-01-01 01:01:01';
diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result
index 57862ded4f2..c42f80f0e85 100644
--- a/mysql-test/r/range_vs_index_merge_innodb.result
+++ b/mysql-test/r/range_vs_index_merge_innodb.result
@@ -328,15 +328,15 @@ ID Name Country Population
EXPLAIN
SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using where
EXPLAIN
SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using where
EXPLAIN
SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 1198 Using index condition; Using where
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 1198 Using where
EXPLAIN
SELECT * FROM City WHERE Country > 'A' AND Country < 'ARG';
id select_type table type possible_keys key key_len ref rows Extra
@@ -355,7 +355,7 @@ WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
OR ((ID BETWEEN 100 AND 110) AND
(Name LIKE 'P%' OR (Population > 103000 AND Population < 104000)));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using index condition; Using where
+1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using where
EXPLAIN
SELECT * FROM City
WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG')))
@@ -369,7 +369,7 @@ WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG')))
OR ((ID BETWEEN 100 AND 200) AND
(Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000)));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using index condition; Using where
+1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using where
SELECT * FROM City USE INDEX ()
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
OR ((ID BETWEEN 100 AND 110) AND
@@ -601,11 +601,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 3400 AND 3800;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 400 Using index condition
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 400 Using where
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'P%';
id select_type table type possible_keys key key_len ref rows Extra
@@ -765,27 +765,27 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 4025 AND 4035;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 4028 AND 4032;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using index condition
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using where
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 3500 AND 3800;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 300 Using index condition
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 300 Using where
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 4000 AND 4300;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using index condition
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using where
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 250 and 260 ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition
+1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where
EXPLAIN
SELECT * FROM City WHERE (Population > 101000 AND Population < 102000);
id select_type table type possible_keys key key_len ref rows Extra
@@ -1422,7 +1422,7 @@ SELECT * FROM t1
WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND
(t1.c=0 OR t1.a=500);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using index condition; Using where
+1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using where
SELECT * FROM t1
WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND
(t1.c=0 OR t1.a=500);
diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result
index 0182ffac880..644137a43bd 100644
--- a/mysql-test/r/status.result
+++ b/mysql-test/r/status.result
@@ -276,6 +276,8 @@ Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
+Handler_pushed_index_cond_checks 0
+Handler_pushed_index_cond_filtered 0
Handler_read_first 0
Handler_read_key 4
Handler_read_last 0
@@ -298,7 +300,7 @@ Created_tmp_files 0
Created_tmp_tables 2
Handler_tmp_update 2
Handler_tmp_write 7
-Rows_tmp_read 36
+Rows_tmp_read 38
drop table t1;
CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM;
insert into t1 values (1),(2),(3),(4),(5);
@@ -312,6 +314,8 @@ Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
+Handler_pushed_index_cond_checks 0
+Handler_pushed_index_cond_filtered 0
Handler_read_first 0
Handler_read_key 2
Handler_read_last 0
diff --git a/mysql-test/r/status_user.result b/mysql-test/r/status_user.result
index 96edd1c4207..e6db4ac2403 100644
--- a/mysql-test/r/status_user.result
+++ b/mysql-test/r/status_user.result
@@ -101,6 +101,8 @@ Handler_commit 19
Handler_delete 1
Handler_discover 0
Handler_prepare 18
+Handler_pushed_index_cond_checks 0
+Handler_pushed_index_cond_filtered 0
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 097e65fa94f..b6e31a6ec34 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1848,6 +1848,19 @@ a b
7 5
3 3
drop table t1,t2;
+#
+# BUG#933407: Valgrind warnings in mark_as_null_row with materialization+semijoin, STRAIGHT_JOIN, impossible WHERE
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(8);
+SELECT STRAIGHT_JOIN MIN(a) FROM t1
+WHERE a IN (
+SELECT a FROM t1
+WHERE 'condition'='impossible'
+ );
+MIN(a)
+NULL
+DROP TABLE t1;
# This must be at the end:
set optimizer_switch=@subselect_sj_mat_tmp;
set join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 8bb262af7ca..96fe8819705 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2763,6 +2763,129 @@ DROP table t1, t2;
set @@optimizer_switch= @os_912513;
set @@join_cache_level= @jcl_912513;
# End
+#
+# BUG#934342: outer join + semijoin materialization
+# + join_cache_level > 2
+#
+CREATE TABLE t1 (a varchar(1), b varchar(1), INDEX idx_a(a) );
+INSERT INTO t1 VALUES ('v','v'), ('w','w'), ('t','t');
+CREATE TABLE t2 (c varchar(1), INDEX idx_c(c) );
+INSERT INTO t2 VALUES ('v'), ('v'), ('s'), ('j');
+CREATE TABLE t3 (c varchar(1), d varchar(1), INDEX idx_c(c) );
+INSERT INTO t3 VALUES ('v','v'), ('v','v'), ('s','s'), ('j','j');
+INSERT INTO t3 VALUES ('m','m'), ('d','d'), ('k','k'), ('m','m');
+set @tmp_otimizer_switch= @@optimizer_switch;
+set @tmp_join_cache_level=@@join_cache_level;
+set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on';
+set join_cache_level=0;
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
+WHERE (a, b) IN (SELECT a, b FROM t1 t);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx_a NULL NULL NULL 3
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1
+1 PRIMARY t2 ref idx_c idx_c 4 test.t1.b 2 Using where; Using index
+2 MATERIALIZED t ALL idx_a NULL NULL NULL 3
+SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
+WHERE (a, b) IN (SELECT a, b FROM t1 t);
+a b c
+v v v
+v v v
+w w NULL
+t t NULL
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
+WHERE (a, b) IN (SELECT a, b FROM t1 t);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx_a NULL NULL NULL 3
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1
+1 PRIMARY t3 ref idx_c idx_c 4 test.t1.b 2 Using where
+2 MATERIALIZED t ALL idx_a NULL NULL NULL 3
+SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
+WHERE (a, b) IN (SELECT a, b FROM t1 t);
+a b c d
+v v v v
+v v v v
+w w NULL NULL
+t t NULL NULL
+set join_cache_level=6;
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
+WHERE (a, b) IN (SELECT a, b FROM t1 t);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx_a NULL NULL NULL 3
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1
+1 PRIMARY t2 ref idx_c idx_c 4 test.t1.b 2 Using where; Using index
+2 MATERIALIZED t ALL idx_a NULL NULL NULL 3
+SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
+WHERE (a, b) IN (SELECT a, b FROM t1 t);
+a b c
+v v v
+v v v
+w w NULL
+t t NULL
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
+WHERE (a, b) IN (SELECT a, b FROM t1 t);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL idx_a NULL NULL NULL 3
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1
+1 PRIMARY t3 ref idx_c idx_c 4 test.t1.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+2 MATERIALIZED t ALL idx_a NULL NULL NULL 3
+SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
+WHERE (a, b) IN (SELECT a, b FROM t1 t);
+a b c d
+v v v v
+v v v v
+w w NULL NULL
+t t NULL NULL
+set optimizer_switch=@tmp_optimizer_switch;
+set join_cache_level=@tmp_join_cache_level;
+DROP TABLE t1,t2,t3;
+# End
+#
+# BUG#934348: GROUP BY with HAVING + semijoin materialization
+# + join_cache_level > 2
+#
+CREATE TABLE t1 (a varchar(1), INDEX idx_a(a));
+INSERT INTO t1 VALUES ('c'), ('v'), ('c');
+CREATE TABLE t2 (b varchar(1));
+INSERT INTO t2 VALUES ('v'), ('c');
+set @tmp_otimizer_switch= @@optimizer_switch;
+set @tmp_join_cache_level=@@join_cache_level;
+set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on';
+set join_cache_level=0;
+EXPLAIN
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+GROUP BY a HAVING a != 'z';
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort
+1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where
+2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+GROUP BY a HAVING a != 'z';
+a
+c
+v
+set join_cache_level=6;
+EXPLAIN
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+GROUP BY a HAVING a != 'z';
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort
+1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where
+2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+GROUP BY a HAVING a != 'z';
+a
+c
+v
+set optimizer_switch=@tmp_optimizer_switch;
+set join_cache_level=@tmp_join_cache_level;
+DROP TABLE t1,t2;
+# End
set join_cache_level=default;
show variables like 'join_cache_level';
Variable_name Value
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index b3a205eb071..e3c5926ffee 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -1885,6 +1885,19 @@ a b
7 5
3 3
drop table t1,t2;
+#
+# BUG#933407: Valgrind warnings in mark_as_null_row with materialization+semijoin, STRAIGHT_JOIN, impossible WHERE
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(8);
+SELECT STRAIGHT_JOIN MIN(a) FROM t1
+WHERE a IN (
+SELECT a FROM t1
+WHERE 'condition'='impossible'
+ );
+MIN(a)
+NULL
+DROP TABLE t1;
# This must be at the end:
set optimizer_switch=@subselect_sj_mat_tmp;
set join_cache_level=@save_join_cache_level;
View Lorry Controller Status