summaryrefslogtreecommitdiff
path: root/mysql-test/main/subselect_mat.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/subselect_mat.result')
-rw-r--r--mysql-test/main/subselect_mat.result57
1 files changed, 26 insertions, 31 deletions
diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result
index 3d014730c6a..29dcc706444 100644
--- a/mysql-test/main/subselect_mat.result
+++ b/mysql-test/main/subselect_mat.result
@@ -105,7 +105,7 @@ explain extended
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1i index NULL _it1_idx # NULL 3 100.00 Using where;
-2 MATERIALIZED t2i range it2i1,it2i3 it2i1 # NULL 5 100.00 Using where;
+2 MATERIALIZED t2i index it2i1,it2i3 it2i1 # NULL 5 100.00 Using where;
Warnings:
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`>(<in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1` from `test`.`t2i` where `test`.`t2i`.`b1` > '0' ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where `test`.`t1i`.`a1` = `<subquery2>`.`b1`))))
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
@@ -127,7 +127,7 @@ explain extended
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1i index NULL _it1_idx # NULL 3 100.00 Using where;
-2 MATERIALIZED t2i range it2i1,it2i3 it2i3 # NULL 5 100.00 Using where;
+2 MATERIALIZED t2i index it2i1,it2i3 it2i3 # NULL 5 100.00 Using where;
Warnings:
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where `test`.`t2i`.`b1` > '0' ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where `test`.`t1i`.`a1` = `<subquery2>`.`b1` and `test`.`t1i`.`a2` = `<subquery2>`.`b2`))))
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
@@ -340,7 +340,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1i index NULL # # # 3 100.00 #
3 MATERIALIZED t3i index NULL # # # 4 100.00 #
4 MATERIALIZED t2i index it2i2 # # # 5 100.00 #
-2 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 #
+2 MATERIALIZED t2i index it2i1,it2i3 # # # 5 100.00 #
Warnings:
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where `test`.`t2i`.`b1` > '0' ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where `test`.`t1i`.`a1` = `<subquery2>`.`b1` and `test`.`t1i`.`a2` = `<subquery2>`.`b2`)))) and <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (/* select#3 */ select `test`.`t3i`.`c1`,`test`.`t3i`.`c2` from `test`.`t3i` where <expr_cache><`test`.`t3i`.`c1`,`test`.`t3i`.`c2`>(<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (/* select#4 */ select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where `test`.`t2i`.`b2` > '0' ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key where `test`.`t3i`.`c1` = `<subquery4>`.`b1` and `test`.`t3i`.`c2` = `<subquery4>`.`b2`)))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where `test`.`t1i`.`a1` = `<subquery3>`.`c1` and `test`.`t1i`.`a2` = `<subquery3>`.`c2`))))
select * from t1i
@@ -423,7 +423,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
7 UNION t1i index NULL # # # 3 100.00 #
9 MATERIALIZED t3i index NULL # # # 4 100.00 #
10 MATERIALIZED t2i index it2i2 # # # 5 100.00 #
-8 MATERIALIZED t2i range it2i1,it2i3 # # # 5 100.00 #
+8 MATERIALIZED t2i index it2i1,it2i3 # # # 5 100.00 #
NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL #
Warnings:
Note 1003 (/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#2 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#3 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%02' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery3>`.`c2`)))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (/* select#4 */ select `test`.`t3`.`c2` from `test`.`t3` where `test`.`t3`.`c2` like '%03' ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where `test`.`t2`.`b2` = `<subquery4>`.`c2`)))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where `test`.`t1`.`a1` = `<subquery2>`.`b1` and `test`.`t1`.`a2` = `<subquery2>`.`b2`)))) and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (/* select#5 */ select `test`.`t3`.`c1`,`test`.`t3`.`c2` from `test`.`t3` where <expr_cache><`test`.`t3`.`c1`,`test`.`t3`.`c2`>(<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (/* select#6 */ select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where `test`.`t2i`.`b2` > '0' ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where `test`.`t3`.`c1` = `<subquery6>`.`b1` and `test`.`t3`.`c2` = `<subquery6>`.`b2`)))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where `test`.`t1`.`a1` = `<subquery5>`.`c1` and `test`.`t1`.`a2` = `<subquery5>`.`c2`))))) union (/* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (/* select#8 */ select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where `test`.`t2i`.`b1` > '0' ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where `test`.`t1i`.`a1` = `<subquery8>`.`b1` and `test`.`t1i`.`a2` = `<subquery8>`.`b2`)))) and <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (/* select#9 */ select `test`.`t3i`.`c1`,`test`.`t3i`.`c2` from `test`.`t3i` where <expr_cache><`test`.`t3i`.`c1`,`test`.`t3i`.`c2`>(<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (/* select#10 */ select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where `test`.`t2i`.`b2` > '0' ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key where `test`.`t3i`.`c1` = `<subquery10>`.`b1` and `test`.`t3i`.`c2` = `<subquery10>`.`b2`)))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where `test`.`t1i`.`a1` = `<subquery9>`.`c1` and `test`.`t1i`.`a2` = `<subquery9>`.`c2`)))))
@@ -1534,8 +1534,7 @@ SET @@optimizer_switch='semijoin=on,materialization=on';
EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
-2 MATERIALIZED t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan
+1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
pk
2
@@ -1889,8 +1888,8 @@ WHERE alias4.c = alias3.b
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-3 MATERIALIZED alias3 ALL NULL NULL NULL NULL 2 Using where
-3 MATERIALIZED alias4 ref c c 11 test.alias3.b 2 Using where; Using index
+3 MATERIALIZED alias3 ALL NULL NULL NULL NULL 2
+3 MATERIALIZED alias4 index c c 11 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
DROP TABLE t1,t2;
#
# BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result
@@ -1953,11 +1952,11 @@ EXPLAIN EXTENDED
SELECT * FROM t1
WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 Using where
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(/*always not null*/ 1 is null) or `<subquery2>`.`MAX(c)` = 7)
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `<subquery2>`.`MAX(c)` = `test`.`t1`.`a` and (`test`.`t1`.`a` is null or `test`.`t1`.`a` = 7)
SELECT * FROM t1
WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
a b
@@ -1966,8 +1965,8 @@ EXPLAIN
SELECT * FROM t1
WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 Using where
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
SELECT * FROM t1
WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
@@ -2241,9 +2240,8 @@ WHERE EXISTS ( SELECT * FROM t1 AS sq2
WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY sq1 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1
-3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where; FirstMatch
+2 DEPENDENT SUBQUERY sq2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
# this checks the result set above
set optimizer_switch= 'materialization=off,semijoin=off';
SELECT sq1.f2 FROM t1 AS sq1
@@ -2275,10 +2273,9 @@ WHERE EXISTS ( SELECT * FROM t2, t3
WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-2 DEPENDENT SUBQUERY t2 index i2 i2 5 NULL 3 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 8 100.00 Using where; FirstMatch
+2 DEPENDENT SUBQUERY t2 index i2 i2 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join)
2 DEPENDENT SUBQUERY t3 ref i3 i3 5 test.t2.i2 2 100.00 Using index
-3 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 100.00
Warnings:
Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(/* select#2 */ select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`f1` = `test`.`t3`.`f3` limit 1))
@@ -2314,9 +2311,8 @@ SELECT pk, f1, ( SELECT COUNT(*) FROM t2
WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00
-2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
-3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join)
Warnings:
Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((/* select#2 */ select count(0) from `test`.`t2` semi join (`test`.`t2`) where `test`.`t1`.`pk` = `test`.`t2`.`f2`)) AS `sq` from `test`.`t1`
@@ -2399,11 +2395,10 @@ WHERE t2.ugroup = t3_i.sys_id AND
t3_i.type LIKE '59e22fb137032000158bbfc8bcbe5d52' AND
t2.user = '86826bf03710200044e0bfc8bcbe5d79');
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2
+1 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary
1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where
+1 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where; End temporary
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index
-2 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where
-2 MATERIALIZED t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where
SELECT t1.assignment_group
FROM t1, t3
WHERE t1.assignment_group = t3.sys_id AND
@@ -2435,8 +2430,7 @@ explain
SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
-2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
1
1
@@ -2447,8 +2441,8 @@ alter table t1 add key(id);
explain
SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3
-1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index
+1 PRIMARY t1 index id id 4 NULL 9 Using index
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
1
@@ -2504,8 +2498,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13);
CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2;
explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3
-1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index
+1 PRIMARY t1 index id id 4 NULL 9 Using index
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
@@ -2802,6 +2796,7 @@ PRIMARY KEY (pk)
INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
+insert into t2 select -seq,"","","","" from seq_1_to_10;
SET @@optimizer_switch='default,semijoin=on,materialization=on';
EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows Extra
View Lorry Controller Status