summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect_partial_match.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/subselect_partial_match.result')
-rw-r--r--mysql-test/r/subselect_partial_match.result50
1 files changed, 25 insertions, 25 deletions
diff --git a/mysql-test/r/subselect_partial_match.result b/mysql-test/r/subselect_partial_match.result
index a01d6375064..4e37b831326 100644
--- a/mysql-test/r/subselect_partial_match.result
+++ b/mysql-test/r/subselect_partial_match.result
@@ -74,7 +74,7 @@ EXPLAIN
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 3
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
a1 a2 a3 in_res
c NULL a NULL
@@ -82,7 +82,7 @@ EXPLAIN
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 3
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
a1 a2 a3
set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
@@ -90,7 +90,7 @@ EXPLAIN
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 3
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
a1 a2 a3 in_res
c NULL a NULL
@@ -98,7 +98,7 @@ EXPLAIN
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 3
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
a1 a2 a3
set @@optimizer_switch=@in_exists;
@@ -129,7 +129,7 @@ EXPLAIN
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
a1 a2 a3 in_res
NULL y NULL NULL
@@ -137,7 +137,7 @@ EXPLAIN
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
a1 a2 a3
set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
@@ -145,7 +145,7 @@ EXPLAIN
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
a1 a2 a3 in_res
NULL y NULL NULL
@@ -153,7 +153,7 @@ EXPLAIN
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
a1 a2 a3
set @@optimizer_switch=@in_exists;
@@ -188,7 +188,7 @@ EXPLAIN
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
a1 a2 a3 in_res
NULL g NULL NULL
@@ -196,7 +196,7 @@ EXPLAIN
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
a1 a2 a3
set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on';
@@ -204,7 +204,7 @@ EXPLAIN
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
a1 a2 a3 in_res
NULL g NULL NULL
@@ -212,7 +212,7 @@ EXPLAIN
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
a1 a2 a3
set @@optimizer_switch=@in_exists;
@@ -247,7 +247,7 @@ EXPLAIN
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
a1 a2 a3 in_res
b g NULL 0
@@ -255,7 +255,7 @@ EXPLAIN
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
a1 a2 a3
b g NULL
@@ -264,7 +264,7 @@ EXPLAIN
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1;
a1 a2 a3 in_res
b g NULL 0
@@ -272,7 +272,7 @@ EXPLAIN
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2);
a1 a2 a3
b g NULL
@@ -773,7 +773,7 @@ SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using where
-3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
+3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `table1`.`a1` AS `a1`,`table1`.`a2` AS `a2` from (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b2` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery3>`.`b2`)))))))) `table1`
set optimizer_switch=@tmp_optimizer_switch;
@@ -787,7 +787,7 @@ insert into t1 values (NULL, 'a21'), (NULL, 'a22');
explain select * from t1 where (a1, a2) not in (select a1, a2 from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2
select * from t1 where (a1, a2) not in (select a1, a2 from t1);
a1 a2
drop table t1;
@@ -821,8 +821,8 @@ SELECT t3.d , t2.c
FROM t3 LEFT JOIN t2 ON t3.a = t2.a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t3 system NULL NULL NULL NULL 1
-2 SUBQUERY t2 system NULL NULL NULL NULL 1
+2 MATERIALIZED t3 system NULL NULL NULL NULL 1
+2 MATERIALIZED t2 system NULL NULL NULL NULL 1
SELECT *
FROM t1
WHERE (t1.d , t1.d) NOT IN (
@@ -860,7 +860,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2);
c
0
@@ -869,7 +869,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2);
c
0
@@ -905,7 +905,7 @@ set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_
EXPLAIN SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
+2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3
SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 );
f3
5
@@ -962,7 +962,7 @@ set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_
EXPLAIN SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 3
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3
SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2);
a b
0 NULL
@@ -973,7 +973,7 @@ a b subq_res
EXPLAIN SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL);
a b
0 NULL
View Lorry Controller Status