Below is the list of changes that have just been committed into a local
6.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-11-21 18:17:17+02:00, gkodinov@stripped +5 -0
merge of bug 30788 to 6.0-opt : updated tests
mysql-test/r/subselect.result@stripped, 2007-11-21 18:17:15+02:00, gkodinov@stripped +7 -7
merge of bug 30788 to 6.0-opt : updated tests
mysql-test/r/subselect_mat.result@stripped, 2007-11-21 18:17:15+02:00, gkodinov@stripped +2 -2
merge of bug 30788 to 6.0-opt : updated tests
mysql-test/r/subselect_no_mat.result@stripped, 2007-11-21 18:17:15+02:00, gkodinov@stripped +33 -2
merge of bug 30788 to 6.0-opt : updated tests
mysql-test/r/subselect_no_opts.result@stripped, 2007-11-21 18:17:15+02:00, gkodinov@stripped +39 -8
merge of bug 30788 to 6.0-opt : updated tests
mysql-test/r/subselect_no_semijoin.result@stripped, 2007-11-21 18:17:15+02:00, gkodinov@stripped +33 -2
merge of bug 30788 to 6.0-opt : updated tests
diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result 2007-11-21 16:05:50 +02:00
+++ b/mysql-test/r/subselect.result 2007-11-21 18:17:15 +02:00
@@ -904,7 +904,7 @@ a t1.a in (select t2.a from t2,t3 where
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
@@ -4199,8 +4199,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 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where
+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
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
@@ -4209,15 +4209,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 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t2 index_subquery I1 I1 4 func 2 Using index; Using where
+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
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 ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t1 index_subquery I1 I1 2 func 2 Using index; Using where
+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
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
DROP TABLE t1,t2;
diff -Nrup a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
--- a/mysql-test/r/subselect_mat.result 2007-11-07 12:43:36 +02:00
+++ b/mysql-test/r/subselect_mat.result 2007-11-21 18:17:15 +02:00
@@ -523,9 +523,9 @@ explain extended
select * from t1 group by (a1 in (select col from columns));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort
-2 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Full scan on NULL key
+2 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where trigcond((<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`)))))
select * from t1 group by (a1 in (select col from columns));
a1 a2
1 - 00 2 - 00
diff -Nrup a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
--- a/mysql-test/r/subselect_no_mat.result 2007-11-15 10:06:58 +02:00
+++ b/mysql-test/r/subselect_no_mat.result 2007-11-21 18:17:15 +02:00
@@ -908,7 +908,7 @@ a t1.a in (select t2.a from t2,t3 where
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
@@ -1732,7 +1732,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
Warnings:
-Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))))
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
@@ -4194,6 +4194,37 @@ LEFT(t1.a1,1)
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
a2
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
+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
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
+a b
+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
+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
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
+a b
+DROP TABLE t1,t2;
End of 5.0 tests.
create table t_out (subcase char(3),
a1 char(2), b1 char(2), c1 char(2));
diff -Nrup a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
--- a/mysql-test/r/subselect_no_opts.result 2007-11-15 10:06:58 +02:00
+++ b/mysql-test/r/subselect_no_opts.result 2007-11-21 18:17:15 +02:00
@@ -908,7 +908,7 @@ a t1.a in (select t2.a from t2,t3 where
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
@@ -1311,7 +1311,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where (`test`.`t1`.`b` <> 30))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
@@ -1319,8 +1319,8 @@ a
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using index condition
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
drop table t1, t2, t3;
@@ -1350,7 +1350,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
@@ -1358,7 +1358,7 @@ a
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
-2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using index
2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
@@ -1377,7 +1377,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where (`test`.`t1`.`b` <> 30))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))
drop table t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
@@ -1732,7 +1732,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
Warnings:
-Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))))
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
@@ -4194,6 +4194,37 @@ LEFT(t1.a1,1)
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
a2
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
+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
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
+a b
+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
+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
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
+a b
+DROP TABLE t1,t2;
End of 5.0 tests.
create table t_out (subcase char(3),
a1 char(2), b1 char(2), c1 char(2));
diff -Nrup a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
--- a/mysql-test/r/subselect_no_semijoin.result 2007-11-15 10:06:58 +02:00
+++ b/mysql-test/r/subselect_no_semijoin.result 2007-11-21 18:17:15 +02:00
@@ -908,7 +908,7 @@ a t1.a in (select t2.a from t2,t3 where
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
@@ -1732,7 +1732,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where
Warnings:
-Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))))
+Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
@@ -4194,6 +4194,37 @@ LEFT(t1.a1,1)
SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
a2
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
+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
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
+a b
+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
+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
+SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
+a b
+DROP TABLE t1,t2;
End of 5.0 tests.
create table t_out (subcase char(3),
a1 char(2), b1 char(2), c1 char(2));
| Thread |
|---|
| • bk commit into 6.0 tree (gkodinov:1.2683) | kgeorge | 21 Nov |
