summaryrefslogtreecommitdiffstats
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.result83
1 files changed, 40 insertions, 43 deletions
diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result
index 1f20e1bd..e964413d 100644
--- a/mysql-test/main/subselect_mat.result
+++ b/mysql-test/main/subselect_mat.result
@@ -1142,7 +1142,7 @@ a
explain extended
select a from t1 group by a having a in (select c from t2 where d >= 20);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 range NULL it1a 4 NULL 8 100.00 Using index for group-by
+1 PRIMARY t1 range NULL it1a 4 NULL 7 100.00 Using index for group-by
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where `test`.`t2`.`d` >= 20 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`c`))))
@@ -1154,7 +1154,7 @@ create index iab on t1(a, b);
explain extended
select a from t1 group by a having a in (select c from t2 where d >= 20);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 range NULL it1a 4 NULL 8 100.00 Using index for group-by
+1 PRIMARY t1 range NULL it1a 4 NULL 7 100.00 Using index for group-by
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 7 100.00 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where `test`.`t2`.`d` >= 20 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`c`))))
@@ -1166,7 +1166,7 @@ explain extended
select a from t1 group by a
having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 range NULL iab 4 NULL 8 100.00 Using index for group-by
+1 PRIMARY t1 range NULL iab 4 NULL 7 100.00 Using index for group-by
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
@@ -1510,13 +1510,15 @@ SET @@optimizer_switch='semijoin=on,materialization=on';
EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1
-2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
COUNT(*)
2
set @@optimizer_switch= @local_optimizer_switch;
DROP TABLE t1, t2;
+#
+# BUG#46548 IN-subqueries return 0 rows with materialization=on
+#
CREATE TABLE t1 (
pk int,
a varchar(1),
@@ -1526,16 +1528,19 @@ d varchar(4),
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
+insert into t1 select seq,'x','xxxx','xxxx','xxxx' from seq_10_to_40;
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
+insert into t2 select -seq,'a','aaaa','aaaa','aaaa' from seq_1_to_20;
+insert into t2 select seq,'b','bbbb','bbbb','bbbb' from seq_100_to_200;
set @local_optimizer_switch=@@optimizer_switch;
set @@optimizer_switch=@optimizer_switch_local_default;
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 t1 ALL NULL NULL NULL NULL 33
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
+2 MATERIALIZED t2 ALL PRIMARY NULL NULL NULL 123 Using where
SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
pk
2
@@ -1890,19 +1895,20 @@ 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 alias4 ref c c 11 test.alias3.b 1 Using where; Using index
DROP TABLE t1,t2;
#
# BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result
#
create table t1 (a int, b int);
insert into t1 values (7,5), (3,3), (5,4), (9,3);
+insert into t1 select seq,seq from seq_100_to_200;
create table t2 (a int, b int, index i_a(a));
insert into t2 values
(4,2), (7,9), (7,4), (3,1), (5,3), (3,1), (9,4), (8,1);
explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 105 Using where
2 MATERIALIZED t2 ALL i_a NULL NULL NULL 8 Using where
select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);
a b
@@ -2194,9 +2200,8 @@ mysqltest1
EXPLAIN EXTENDED
SELECT db FROM t1 WHERE db IN (SELECT SCHEMA_NAME FROM information_schema.schemata) ORDER BY db DESC;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort
-1 PRIMARY t1 eq_ref db db 764 information_schema.schemata.SCHEMA_NAME 1 100.00 Using where; Using index
-2 MATERIALIZED schemata ALL NULL NULL NULL NULL NULL NULL
+1 PRIMARY t1 index db db 764 NULL 4 100.00 Using index; Using temporary; Using filesort
+1 PRIMARY schemata ALL NULL NULL NULL NULL NULL NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`db` AS `db` from `test`.`t1` semi join (`information_schema`.`schemata`) where `test`.`t1`.`db` = `information_schema`.`schemata`.`SCHEMA_NAME` order by `test`.`t1`.`db` desc
drop table t1;
@@ -2228,8 +2233,10 @@ drop table t1;
CREATE TABLE t1 (
pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
+create table t2 like t1;
+insert into t2 select * from t1;
SELECT sq1.f2 FROM t1 AS sq1
-WHERE EXISTS ( SELECT * FROM t1 AS sq2
+WHERE EXISTS ( SELECT * FROM t2 AS sq2
WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
f2
foo
@@ -2241,18 +2248,17 @@ 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 <subquery3> eq_ref distinct_key distinct_key 4 func 1
+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)
-3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2
# this checks the result set above
set optimizer_switch= 'materialization=off,semijoin=off';
SELECT sq1.f2 FROM t1 AS sq1
-WHERE EXISTS ( SELECT * FROM t1 AS sq2
+WHERE EXISTS ( SELECT * FROM t2 AS sq2
WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
f2
foo
set optimizer_switch= @local_optimizer_switch;
-DROP TABLE t1;
+DROP TABLE t1,t2;
#
# MDEV-12145: IN subquery used in WHERE of EXISTS subquery
#
@@ -2275,10 +2281,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 <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 8 12.50 Using where; FirstMatch
2 DEPENDENT SUBQUERY t2 range 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
+2 DEPENDENT SUBQUERY t3 ref i3 i3 5 test.t2.i2 1 100.00 Using index
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 +2319,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 <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 20.00 Using where; FirstMatch
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join)
-3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00
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 +2403,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 t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where
+1 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary
+1 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where
+1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 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
set statement optimizer_prune_level=1 for explain SELECT t1.assignment_group
FROM t1, t3
WHERE t1.assignment_group = t3.sys_id AND
@@ -2414,11 +2417,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 <subquery3> ALL distinct_key NULL NULL NULL 2
-1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t2.ugroup 2 Using where
+1 PRIMARY t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where; Start temporary
+1 PRIMARY t3_i eq_ref PRIMARY PRIMARY 32 test.t2.ugroup 1 Using index condition; Using where
+1 PRIMARY t1 ref idx1,idx2 idx1 35 test.t3_i.sys_id 2 Using index condition; Using where; End temporary
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 32 test.t1.assignment_group 1 Using where; Using index
-3 MATERIALIZED t2 ref idx3,idx4 idx4 35 const 2 Using index condition; Using where
-3 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
@@ -2450,8 +2452,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 hash_ALL NULL #hash#$hj 8 test.t1.id,test.t1.id 3 Using where; FirstMatch(t1); Using join buffer (flat, BNLH join)
SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2);
1
1
@@ -2463,8 +2464,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 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
+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
@@ -2513,20 +2513,17 @@ drop procedure prepare_data;
set @@optimizer_switch= @local_optimizer_switch;
drop table t1,t2,t3;
CREATE TABLE t1 ( id int NOT NULL, key(id));
-INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19);
+INSERT INTO t1 select seq from seq_11_to_39;
CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL);
-INSERT INTO t2 VALUES (11,11),(12,12),(13,13);
+INSERT INTO t2 select seq,seq+1 from seq_11_to_50;
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 t1 index id id 4 NULL 9 Using index
+1 PRIMARY t1 index id id 4 NULL 29 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
+2 MATERIALIZED t2 ALL NULL NULL NULL NULL 40 Using where
SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1);
1
-1
-1
-1
drop table t1,t2;
drop view v1;
#
@@ -2859,12 +2856,12 @@ 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
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
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; 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
generated by cgit v1.2.3 (git 2.39.1) at 2024-10-08 00:31:18 +0000