#At file:///home/rl136806/mysql/repo/mysql-6.0-opt/
2700 Roy Lyseng 2008-11-17
Bug#40667: subselect.test switches connection at line 1464, forgets optimization settings
One test case requires a "new" connection. When that test case is finished, close the new connection
and switch back to the default one.
modified:
mysql-test/r/subselect_no_mat.result
mysql-test/r/subselect_no_opts.result
mysql-test/r/subselect_no_semijoin.result
mysql-test/t/subselect.test
per-file messages:
mysql-test/t/subselect.test
Make sure that all test cases use the default connection, unless explicitly asked for another.
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2008-08-27 16:48:24 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2008-11-17 13:46:35 +0000
@@ -3411,7 +3411,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
a b
@@ -3422,7 +3422,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort
DROP TABLE t1;
create table t1( f1 int,f2 int);
insert into t1 values (1,1),(2,2);
@@ -4368,16 +4368,16 @@ CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
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 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
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 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
DROP TABLE t1;
End of 5.0 tests.
create table t_out (subcase char(3),
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2008-08-27 16:48:24 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2008-11-17 13:46:35 +0000
@@ -2822,10 +2822,10 @@ Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = 'N') and (<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
@@ -3411,7 +3411,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
a b
@@ -3422,7 +3422,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 index NULL a 8 NULL 1 Using filesort
DROP TABLE t1;
create table t1( f1 int,f2 int);
insert into t1 values (1,1),(2,2);
@@ -4219,8 +4219,8 @@ CREATE INDEX I1 ON t1 (a);
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
@@ -4229,15 +4229,15 @@ CREATE INDEX I1 ON t2 (a);
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
-1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 2 Using index; Using where
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
a b
EXPLAIN
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
DROP TABLE t1,t2;
@@ -4368,16 +4368,16 @@ CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
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 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
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 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1))))
DROP TABLE t1;
End of 5.0 tests.
create table t_out (subcase char(3),
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2008-08-27 16:48:24 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2008-11-17 13:46:35 +0000
@@ -2822,10 +2822,10 @@ Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key)))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
@@ -4165,14 +4165,26 @@ CREATE TABLE t1 (s1 BINARY(5), s2 VARBIN
INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
s1 s2
+A+B+C SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
s1 s2
+A+B+C CREATE INDEX I1 ON t1 (s1);
CREATE INDEX I2 ON t1 (s2);
SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
s1 s2
+A+B+C SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
s1 s2
+A+B+C TRUNCATE t1;
INSERT INTO t1 VALUES (0x41,0x41);
SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
@@ -4209,37 +4221,47 @@ LEFT(t1.a1,1)
3
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
a2
+2
+3
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (a CHAR(1), b VARCHAR(10));
INSERT INTO t1 VALUES ('a', 'aa');
INSERT INTO t1 VALUES ('a', 'aaa');
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
+a aa
+a aaa
CREATE INDEX I1 ON t1 (a);
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 SUBQUERY t1 index NULL I1 2 NULL 2 Using index
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
+a aa
+a aaa
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
INSERT INTO t2 SELECT * FROM t1;
CREATE INDEX I1 ON t2 (a);
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
-1 PRIMARY t2 ref I2 I2 13 test.t2.a 2 Using index condition
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 SUBQUERY t2 index NULL I1 4 NULL 2 Using index
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
a b
+a aa
+a aaa
EXPLAIN
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index I1 I1 2 NULL 2 Using where; Using index; LooseScan
-1 PRIMARY t1 ref I2 I2 13 test.t1.a 2 Using index condition
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 SUBQUERY t1 index NULL I1 2 NULL 2 Using where; Using index
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
+a aa
+a aaa
DROP TABLE t1,t2;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (1,1), (1,2), (2,3), (2,4);
=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test 2008-10-20 09:16:47 +0000
+++ b/mysql-test/t/subselect.test 2008-11-17 13:46:35 +0000
@@ -1460,10 +1460,13 @@ drop table t1;
#
# Subselect in non-select command just after connection
+# Disconnect new connection and switch back when test is finished
#
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connection root;
set @got_val= (SELECT 1 FROM (SELECT 'A' as my_col) as T1 ) ;
+disconnect root;
+connection default;
#
# primary query with temporary table and subquery with groupping
