From 30bac798066ced860d65bee8b3ef63d7b100e47d Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 7 Apr 2010 01:29:09 +0400 Subject: Always refer to materialized table as "SUBSELECT#%d" where %d is select number - for Item-based materialization, it was "materialized subselect" - for SJ-Materialization it was "subquery%d" --- mysql-test/r/subselect_sj2_jcl6.result | 18 +++++++++--------- 1 file changed, 9 insertions(+), 9 deletions(-) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 67215d1715e..d442e4ebee4 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -36,7 +36,7 @@ a b 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 3 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); @@ -78,7 +78,7 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer 2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; @@ -105,7 +105,7 @@ set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b 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 subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t2 index b b 5 NULL 10 Using index select * from t1; a b @@ -133,7 +133,7 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -167,7 +167,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -201,7 +201,7 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY subselect2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -235,7 +235,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -352,7 +352,7 @@ WHERE t1.Code IN ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY subselect2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( @@ -698,7 +698,7 @@ The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) -- cgit v1.2.1 From ed8aa9868e074e6489f3b71f585606b6eecc97c6 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 11 May 2010 11:53:40 +0400 Subject: - Make subselect_sj2 testcase more stable - Better comments --- mysql-test/r/subselect_sj2_jcl6.result | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index d442e4ebee4..329cbfc6cd4 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -363,6 +363,10 @@ Canada China Czech Republic drop table t1, t2; +drop procedure if exists p1; +drop procedure if exists p2; +drop procedure if exists p3; +drop procedure if exists p4; CREATE TABLE t1(a INT); CREATE TABLE t2(c INT); CREATE PROCEDURE p1(v1 int) -- cgit v1.2.1 From 0cc3724697a90b9d1200563ed7b13daa0bf4be12 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 27 May 2010 16:14:25 +0400 Subject: MWL#90: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE - Change "SUBQUERY#n" to "" in EXPLAIN output. We need to it to be lowercase so that EXPLAIN results do not differ in case between systems with case-sensitive and case-insensitive filesystems. - Remove garbage comments, add better comments. --- mysql-test/r/subselect_sj2_jcl6.result | 18 +++++++++--------- 1 file changed, 9 insertions(+), 9 deletions(-) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 329cbfc6cd4..4a610113446 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -36,7 +36,7 @@ a b 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 3 +1 PRIMARY ALL unique_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); @@ -78,7 +78,7 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 10 +1 PRIMARY ALL unique_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer 2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; @@ -105,7 +105,7 @@ set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b 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 SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t2 index b b 5 NULL 10 Using index select * from t1; a b @@ -133,7 +133,7 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -167,7 +167,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -201,7 +201,7 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY SUBQUERY#2 ALL unique_key NULL NULL NULL 22 +1 PRIMARY ALL unique_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -235,7 +235,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY eq_ref unique_key unique_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -352,7 +352,7 @@ WHERE t1.Code IN ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 3 func 1 +1 PRIMARY eq_ref unique_key unique_key 3 func 1 2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( @@ -702,7 +702,7 @@ The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY SUBQUERY#2 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY eq_ref unique_key unique_key 5 func 1 2 SUBQUERY t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) -- cgit v1.2.1 From b33826e5552d3659d66da70926749bb121c3dce0 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 9 Jun 2010 18:43:52 +0400 Subject: MWL#90, code cleanup - Unify EXPLAIN printout for tables with regular tables - Update test results for tables: s/unique_key/distinct_key/g s/1.0/100.0/ for "filtered" column --- mysql-test/r/subselect_sj2_jcl6.result | 18 +++++++++--------- 1 file changed, 9 insertions(+), 9 deletions(-) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 4a610113446..94fe7f6d821 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -36,7 +36,7 @@ a b 9 5 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL unique_key NULL NULL NULL 3 +1 PRIMARY ALL distinct_key NULL NULL NULL 3 1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); @@ -78,7 +78,7 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL unique_key NULL NULL NULL 10 +1 PRIMARY ALL distinct_key NULL NULL NULL 10 1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer 2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; @@ -105,7 +105,7 @@ set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b 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 eq_ref unique_key unique_key 5 func 1 +1 PRIMARY eq_ref distinct_key distinct_key 5 func 1 2 SUBQUERY t2 index b b 5 NULL 10 Using index select * from t1; a b @@ -133,7 +133,7 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL unique_key NULL NULL NULL 22 +1 PRIMARY ALL distinct_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -167,7 +167,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY eq_ref unique_key unique_key 5 func 1 +1 PRIMARY eq_ref distinct_key distinct_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 32 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -201,7 +201,7 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL unique_key NULL NULL NULL 22 +1 PRIMARY ALL distinct_key NULL NULL NULL 22 1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer 2 SUBQUERY it ALL NULL NULL NULL NULL 22 select @@ -235,7 +235,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) from t2 ot where a in (select a from t1 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY eq_ref unique_key unique_key 5 func 1 +1 PRIMARY eq_ref distinct_key distinct_key 5 func 1 2 SUBQUERY it ALL NULL NULL NULL NULL 52 select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -352,7 +352,7 @@ WHERE t1.Code IN ( SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY eq_ref unique_key unique_key 3 func 1 +1 PRIMARY eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where SELECT Name FROM t1 WHERE t1.Code IN ( @@ -702,7 +702,7 @@ The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY eq_ref unique_key unique_key 5 func 1 +1 PRIMARY eq_ref distinct_key distinct_key 5 func 1 2 SUBQUERY t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) -- cgit v1.2.1 From e69c642920224680528c0c8548ff52c674bee3d9 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 16 Feb 2011 13:59:14 +0300 Subject: Post-merge fixes part 5 --- mysql-test/r/subselect_sj2_jcl6.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index c7e42bde485..4c953c67c65 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -155,6 +155,7 @@ a mid(filler1, 1,10) Z 8 filler1234 1 9 filler1234 1 10 filler1234 1 +2 duplicate 1 11 filler1234 1 12 filler1234 1 13 filler1234 1 @@ -164,7 +165,6 @@ a mid(filler1, 1,10) Z 17 filler1234 1 18 filler1234 1 19 filler1234 1 -2 duplicate 1 18 duplicate 1 explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -223,6 +223,7 @@ a mid(filler1, 1,10) Z 8 filler1234 1 9 filler1234 1 10 filler1234 1 +2 duplicate 1 11 filler1234 1 12 filler1234 1 13 filler1234 1 @@ -232,7 +233,6 @@ a mid(filler1, 1,10) Z 17 filler1234 1 18 filler1234 1 19 filler1234 1 -2 duplicate 1 18 duplicate 1 explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) -- cgit v1.2.1 From 5ed10efc7bff18e7b109202a1be642c486e61aea Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 18 Feb 2011 14:59:50 +0300 Subject: Update the test results back (were changed due to slightly different QEPs produced by the optimizer, which was an error, which was detected and fixed) --- mysql-test/r/subselect_sj2_jcl6.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/r/subselect_sj2_jcl6.result') diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 4c953c67c65..c7e42bde485 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -155,7 +155,6 @@ a mid(filler1, 1,10) Z 8 filler1234 1 9 filler1234 1 10 filler1234 1 -2 duplicate 1 11 filler1234 1 12 filler1234 1 13 filler1234 1 @@ -165,6 +164,7 @@ a mid(filler1, 1,10) Z 17 filler1234 1 18 filler1234 1 19 filler1234 1 +2 duplicate 1 18 duplicate 1 explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) @@ -223,7 +223,6 @@ a mid(filler1, 1,10) Z 8 filler1234 1 9 filler1234 1 10 filler1234 1 -2 duplicate 1 11 filler1234 1 12 filler1234 1 13 filler1234 1 @@ -233,6 +232,7 @@ a mid(filler1, 1,10) Z 17 filler1234 1 18 filler1234 1 19 filler1234 1 +2 duplicate 1 18 duplicate 1 explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) -- cgit v1.2.1