diff options
Diffstat (limited to 'mysql-test/main/join_cache.test')
| -rw-r--r-- | mysql-test/main/join_cache.test | 280 |
1 files changed, 136 insertions, 144 deletions
diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index ba5afafcf8b..9576d598125 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -2,15 +2,22 @@ DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; DROP DATABASE IF EXISTS world; --enable_warnings +--source include/default_optimizer_switch.inc +--source include/default_charset.inc + +set @org_optimizer_switch=@@optimizer_switch; +set @save_join_cache_level=@@join_cache_level; +set @save_join_buffer_space_limit=@@join_buffer_space_limit; +set @save_join_buffer_size=@@join_buffer_size; +set @save_expensive_subquery_limit=@@expensive_subquery_limit; -set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='optimize_join_buffer_size=on'; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set @@optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set @@optimizer_switch='semijoin_with_cache=on'; set @@optimizer_switch='outer_join_with_cache=on'; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @local_optimizer_switch=@@optimizer_switch; -set @local_join_cache_test_optimizer_switch_default=@@optimizer_switch; set names utf8; CREATE DATABASE world; @@ -318,11 +325,8 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; -set join_cache_level=default; -set join_buffer_size=default; - -show variables like 'join_buffer_size'; -show variables like 'join_cache_level'; +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; DROP DATABASE world; @@ -911,11 +915,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; -set join_cache_level=default; -set join_buffer_size=default; - -show variables like 'join_buffer_size'; -show variables like 'join_cache_level'; +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; set join_cache_level=1; @@ -934,7 +935,7 @@ SELECT City.Name, Country.Name FROM City,Country SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; -set join_buffer_size=default; +set join_buffer_size=@save_join_buffer_size; set join_cache_level=6; @@ -964,8 +965,8 @@ SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; -set join_cache_level=default; -set join_buffer_size=default; +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; --echo # @@ -999,7 +1000,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables=@save_use_stat_tables; -set join_cache_level=default; +set @@join_cache_level=@save_join_cache_level; DROP DATABASE world; @@ -1272,8 +1273,8 @@ DROP TABLE t1,t2; --echo # Bug #40134: outer join with not exists optimization and join buffer --echo # -set join_cache_level=default; -set join_buffer_size=default; +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; CREATE TABLE t1 (a int NOT NULL); INSERT INTO t1 VALUES (2), (4), (3), (5), (1); @@ -1292,8 +1293,8 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; DROP TABLE t1, t2; -set join_cache_level=default; -set join_buffer_size=default; +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; --echo # --echo # BUG#40136: Group by is ignored when join buffer is used for an outer join @@ -1335,7 +1336,7 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b WHERE t3.a+2<t2.a OR t3.c IS NULL; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; DROP TABLE t2, t3, t4; --echo # @@ -1353,7 +1354,7 @@ select * from t1 left join t2 on t1.a=t2.a; explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1, t2; --echo # @@ -1374,7 +1375,7 @@ select * from t1 left join t2 on (1=0) where a=40; set join_cache_level=0; explain select * from t1 left join t2 on (1=0); -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1, t2; --echo # @@ -1418,8 +1419,8 @@ set join_buffer_size=1024; EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; -set join_buffer_size=default; -set join_cache_level=default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1, t2; @@ -1465,8 +1466,8 @@ SELECT COUNT(*) FROM t1,t2,t3 WHERE t1.a=t2.a AND t2.a=t3.a AND t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; -set join_buffer_size=default; -set join_cache_level=default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3; @@ -1536,8 +1537,8 @@ SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) WHERE t1.a=t2.a; -set join_buffer_size=default; -set join_cache_level=default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3; @@ -1594,7 +1595,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; show status like "Handler_icp%"; drop table t1,t2; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; --echo # --echo # Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled @@ -1628,7 +1629,7 @@ select t1.id1 from t1 join t2 on t1.id1=t2.id1 select t1.id1 from t1 join t2 on t1.id1=t2.id1 where t1.d=3 and t2.id2 > 200 order by t1.id1; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1,t2; @@ -1692,7 +1693,7 @@ select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1,t2,t3,t4; @@ -2081,8 +2082,8 @@ SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; -set join_buffer_size=default; -set join_cache_level=default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4,t5; @@ -2153,7 +2154,7 @@ ORDER BY t1.int_key; DROP TABLE t1,t2; -SET join_cache_level=default; +SET join_cache_level=@save_join_cache_level; --echo # --echo # Regression test for @@ -2216,7 +2217,7 @@ set join_cache_level=6; SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2 WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1); -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; @@ -2242,7 +2243,7 @@ set join_cache_level=2; explain select t1.* from t1,t2,t3; select t1.* from t1,t2,t3; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; drop table t1,t2,t3; @@ -2271,7 +2272,7 @@ SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b; DROP TABLE t1,t2,t3; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; --echo # --echo # Bug #51084: Batched key access crashes for SELECT with @@ -2313,7 +2314,7 @@ SELECT COUNT(*) FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) ON t3.carrier = t1.carrier; -SET join_cache_level=default; +SET join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4; @@ -2367,7 +2368,7 @@ explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; --sorted_result SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; -set join_cache_level = default; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; --echo # @@ -2395,7 +2396,7 @@ SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx) SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx) WHERE s.pk AND s.a >= t.pk AND s.b = t.c; -set join_cache_level = default; +set join_cache_level=@save_join_cache_level; DROP TABLE t1; --echo # @@ -2426,7 +2427,7 @@ SELECT t1.a FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0 WHERE t1.a OR t4.a; -set join_cache_level = default; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4; --echo # @@ -2484,8 +2485,8 @@ SELECT t1.i, t1.d, t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3 DROP TABLE t1,t2,t3; -SET SESSION join_cache_level=DEFAULT; -SET SESSION join_buffer_size=DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; --echo # @@ -2593,7 +2594,7 @@ WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 DROP TABLE t1,t2,t3; -SET SESSION join_cache_level=DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; --echo # --echo # Bug #668290: hash join with non-binary collations @@ -2634,7 +2635,7 @@ EXPLAIN SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; @@ -2662,8 +2663,8 @@ EXPLAIN SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; -SET SESSION join_cache_level = DEFAULT; -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; DROP TABLE t1,t2; @@ -2712,7 +2713,7 @@ EXPLAIN SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; @@ -2785,8 +2786,8 @@ SELECT t3.i FROM t1,t2,t3 SELECT t3.i FROM t1,t2,t3 WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; -SET SESSION join_cache_level = DEFAULT; -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; DROP TABLE t1,t2,t3; @@ -2822,7 +2823,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a; SELECT * FROM t1,t2 WHERE t2.a=t1.a; -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; @@ -2844,8 +2845,8 @@ SELECT t3.a (t2 LEFT OUTER JOIN t3 ON t2.b = t3.b) ON t2.a = t1.b WHERE t3.a BETWEEN 3 AND 11 OR t1.a <= t2.c; -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3; @@ -2868,8 +2869,8 @@ SELECT * FROM t2 LEFT JOIN ((t1 JOIN t3 ON t1.a = t3.pk) LEFT JOIN t4 ON 1) ON 1; -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4; @@ -2895,8 +2896,8 @@ EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9; SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9; -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; @@ -2955,8 +2956,8 @@ SELECT * FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4,t5; @@ -3001,8 +3002,8 @@ SELECT * FROM ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 WHERE t3.a3 IS NULL; -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4; @@ -3031,8 +3032,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0; SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0; -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3; @@ -3076,8 +3077,8 @@ SELECT t4.a4, t5.b5 FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; -SET SESSION optimizer_switch = 'outer_join_with_cache=off'; -SET SESSION join_cache_level = DEFAULT; +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2,t3,t4,t5; @@ -3104,7 +3105,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a; SELECT * FROM t1,t2 WHERE t1.a=t2.a; -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; @@ -3138,8 +3139,8 @@ SELECT * FROM t1,t2 SELECT * FROM t1,t2 WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); -SET SESSION join_cache_level = DEFAULT; -SET SESSION optimizer_switch = @local_join_cache_test_optimizer_switch_default; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2; @@ -3165,7 +3166,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0, 100) ORDER BY t1.f2 LIMIT 1; SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; -SET SESSION optimizer_switch = @local_join_cache_test_optimizer_switch_default; +SET SESSION optimizer_switch=@local_optimizer_switch; SET SESSION optimizer_switch = 'index_condition_pushdown=on'; EXPLAIN SELECT * FROM t1,t2 @@ -3173,7 +3174,7 @@ EXPLAIN SELECT * FROM t1,t2 SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; -SET SESSION optimizer_switch = @local_join_cache_test_optimizer_switch_default; +SET SESSION optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2; @@ -3202,8 +3203,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; - -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; @@ -3229,7 +3229,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; -SET SESSION join_cache_level = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; @@ -3259,8 +3259,8 @@ EXPLAIN SELECT a FROM t1,t2 WHERE t2.v = t1.v ; SELECT a FROM t1,t2 WHERE t2.v = t1.v ; -SET SESSION join_cache_level = DEFAULT; -SET SESSION join_buffer_size = DEFAULT; +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; DROP TABLE t1,t2; @@ -3293,10 +3293,12 @@ SELECT * FROM (SELECT DISTINCT * FROM t1) t SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); -SET SESSION join_cache_level = DEFAULT; - DROP TABLE t1, t2; +SET SESSION join_cache_level=@save_join_cache_level; + +# Note that next tests are run with same optimizer_switch as previous one! + --echo # --echo # Bug #887479: join_cache_level=3 + semijoin=on --echo # @@ -3307,7 +3309,7 @@ INSERT INTO t1 VALUES (3914,17), (3710,5), (3888,20); CREATE TABLE t2 (c int, KEY (c)); INSERT INTO t2 VALUES (27), (17), (33), (20), (3), (7), (18), (2); -SET @tmp887479_optimizer_switch=@@optimizer_switch; +SET @tmp_optimizer_switch=@@optimizer_switch; SET SESSION optimizer_switch='semijoin=on'; SET SESSION optimizer_switch='semijoin_with_cache=on'; @@ -3321,8 +3323,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); -SET SESSION join_cache_level = DEFAULT; -SET optimizer_switch=@tmp887479_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2; @@ -3361,8 +3363,8 @@ SELECT t1.* FROM t1,t2 WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) AND t1.a = 1; -SET SESSION join_cache_level = DEFAULT; -SET optimizer_switch=@tmp_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3,t4; @@ -3377,11 +3379,9 @@ INSERT INTO t2 VALUES (8), (7); CREATE TABLE t3 (a int); INSERT INTO t3 VALUES (8), (7); -SET @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch=default; set @@optimizer_switch='semijoin_with_cache=off'; set @@optimizer_switch='outer_join_with_cache=off'; -SET optimizer_switch='derived_merge=off,derived_with_keys=off'; +set @@optimizer_switch='derived_merge=off,derived_with_keys=off'; SET join_cache_level=0; EXPLAIN @@ -3390,8 +3390,8 @@ SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); SELECT * FROM ( SELECT ta.* FROM t1 AS ta, t1 ) tb WHERE a IN ( SELECT * FROM t1 ); -SET SESSION join_cache_level = DEFAULT; -SET optimizer_switch=@tmp_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; @@ -3410,8 +3410,8 @@ CREATE TABLE t3 (c int, d int); INSERT INTO t3 VALUES (8,10); INSERT INTO t3 VALUES (9,11); -SET @tmp_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='semijoin_with_cache=on'; +set @@optimizer_switch='firstmatch=off'; SET join_cache_level=1; EXPLAIN @@ -3428,8 +3428,8 @@ EXPLAIN SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; -SET SESSION join_cache_level = DEFAULT; -SET optimizer_switch=@tmp_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; @@ -3451,7 +3451,6 @@ INSERT INTO t4 VALUES (19,11,10), (17,12,18), (12,13,15), (14,12,19), (18,13,18), (13,14,11), (15,15,14); -SET @tmp_optimizer_switch=@@optimizer_switch; SET @@optimizer_switch='semijoin=on'; SET @@optimizer_switch='firstmatch=off'; SET @@optimizer_switch='mrr=off'; @@ -3491,8 +3490,8 @@ SELECT * FROM t1,t2 t2.a BETWEEN 4 and 5 ORDER BY t2.b; -SET join_cache_level = DEFAULT; -SET optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3,t4; @@ -3517,11 +3516,10 @@ insert into t3 values (2,5), (2,6), (2,7), (2,8), (3,1), (3,2), (3,9); -set @tmp_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='semijoin=on'; -set optimizer_switch='materialization=off'; -set optimizer_switch='loosescan=off,firstmatch=off'; -set optimizer_switch='mrr_sort_keys=off'; +set @@optimizer_switch='semijoin=on'; +set @@optimizer_switch='materialization=off'; +set @@optimizer_switch='loosescan=off,firstmatch=off'; +set @@optimizer_switch='mrr_sort_keys=off'; set join_cache_level=7; create table t4 (uid int primary key, name varchar(128), index(name)); @@ -3538,8 +3536,8 @@ select name from t2, t1 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) and t2.uid=t1.fid; -set join_cache_level = default; -set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; drop table t1,t2,t3,t4; @@ -3555,9 +3553,8 @@ INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (6); INSERT INTO t3 VALUES (1), (2); -set @tmp_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='semijoin=on'; -set optimizer_switch='materialization=on'; +set @@optimizer_switch='semijoin=on'; +set @@optimizer_switch='materialization=on'; set join_cache_level=0; EXPLAIN @@ -3573,8 +3570,8 @@ SELECT * FROM t1 WHERE t1.i IN SELECT * FROM t1 WHERE t1.i IN (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); -set join_cache_level = default; -set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; @@ -3592,9 +3589,8 @@ INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6); CREATE TABLE t3 (b int, c int); INSERT INTO t3 VALUES (4,4); -set @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch='semijoin=off'; -set optimizer_switch='materialization=off'; +set @@optimizer_switch='semijoin=off'; +set @@optimizer_switch='materialization=off'; set join_cache_level=0; EXPLAIN @@ -3615,8 +3611,8 @@ SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b WHERE t2.c < 10 OR t3.c > 1); -set join_cache_level = default; -set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; @@ -3637,21 +3633,20 @@ INSERT INTO t2 VALUES CREATE TABLE t3 (b int, c int); INSERT INTO t3 VALUES (32, 302), (42,400), (30,300); -set @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch='optimize_join_buffer_size=off'; +set @@optimizer_switch='optimize_join_buffer_size=off'; +set @@optimizer_switch='outer_join_with_cache=on'; set join_buffer_space_limit=4096; set join_buffer_size=4096*2; set join_cache_level=2; -set optimizer_switch='outer_join_with_cache=on'; EXPLAIN SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; -set join_buffer_space_limit=default; -set join_buffer_size=default; -set join_cache_level=default; -set optimizer_switch=@tmp_optimizer_switch; +set join_buffer_space_limit=@save_join_buffer_space_limit; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t2,t3; @@ -3688,8 +3683,7 @@ INSERT INTO t2 VALUES (NULL); CREATE TABLE t3 (c int) ENGINE=Aria; INSERT INTO t3 VALUES (NULL); -set @tmp_optimizer_switch=@@optimizer_switch; -set optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on'; +set @@optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on'; set join_buffer_size=128; EXPLAIN @@ -3699,12 +3693,14 @@ SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 GROUP BY elt(t1.col282,1,t1.col280); -set join_buffer_size=default; -set optimizer_switch=@tmp_optimizer_switch; - DROP table t1,t2,t3; -set join_buffer_size= default; -set @@optimizer_switch=@save_optimizer_switch; +set join_buffer_size=@save_join_buffer_size; + +# +# --echo switch to use orginal test suite optimizer switch +# + +set @@optimizer_switch=@org_optimizer_switch,@local_optimizer_switch= @org_optimizer_switch; --echo # @@ -3853,8 +3849,8 @@ FROM ORDER BY col1; -set join_buffer_size=default; -set join_cache_level = default; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; @@ -3877,11 +3873,10 @@ INSERT INTO t2 SELECT * FROM t1; INSERT INTO t4 SELECT * FROM t1; INSERT INTO t5 SELECT * FROM t1; -set @save_optimizer_switch= @@optimizer_switch; SET join_cache_level = 6; SET join_buffer_size=4096; SET join_buffer_space_limit=4096; -SET optimizer_switch = 'join_cache_hashed=on,optimize_join_buffer_size=on'; +set @@optimizer_switch = 'join_cache_hashed=on,optimize_join_buffer_size=on'; let $q= SELECT * FROM t1 @@ -3896,10 +3891,10 @@ WHERE eval EXPLAIN $q; eval $q; -SET join_cache_level = default; -SET join_buffer_size = default; -SET join_buffer_space_limit= default; -set optimizer_switch=@save_optimizer_switch; +set join_cache_level=@save_join_cache_level; +SET join_buffer_size=@save_join_buffer_size; +SET join_buffer_space_limit=@save_join_buffer_space_limit; +set @@optimizer_switch=@local_optimizer_switch; DROP TABLE t1,t4,t5,t2; @@ -3932,13 +3927,12 @@ eval EXPLAIN EXTENDED $q; DROP TABLE t1,t2,temp; -SET join_cache_level = default; +set join_cache_level=@save_join_cache_level; --echo # --echo # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. --echo # -set join_cache_level=default; set expensive_subquery_limit=0; create table t1 (c1 int); @@ -3976,7 +3970,7 @@ where c1 = c2-0 and select @counter; drop table t1,t2,t3; -set expensive_subquery_limit=default; +set expensive_subquery_limit=@save_expensive_subquery_limit; --echo # --echo # MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down @@ -3989,12 +3983,10 @@ insert into t1 values explain select count(*) from t1, t1 t2 where t1.a=t2.a; set join_buffer_space_limit=1024*8; -select @@join_buffer_space_limit; -select @@join_buffer_size; explain select count(*) from t1, t1 t2 where t1.a=t2.a; -set join_buffer_space_limit=default; +set join_buffer_space_limit=@save_join_buffer_space_limit; drop table t1; @@ -4008,7 +4000,7 @@ SET join_cache_level = 3; explain SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = PAGE_FAULTS_MINOR; -set join_cache_level=default; +set join_cache_level=@save_join_cache_level; # # MDEV-12580 Wrong query result in join when using an index (Version > "10.2.3") @@ -4020,7 +4012,7 @@ insert t2 values (4,1,1), (6,1,1); set join_buffer_size = 222222208; select f2 from t2,t1 where f2 = 0; drop table t1, t2; -set join_buffer_size = default; +set join_buffer_size=@save_join_buffer_size; -# The following command must be the last one the file +# The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; |