diff options
Diffstat (limited to 'mysql-test/r/derived_view.result')
| -rw-r--r-- | mysql-test/r/derived_view.result | 49 |
1 files changed, 34 insertions, 15 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 591aaded9f1..587a1dd9e31 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1,8 +1,10 @@ drop table if exists t1,t2; drop view if exists v1,v2,v3,v4; set @exit_optimizer_switch=@@optimizer_switch; +set @exit_join_cache_level=@@join_cache_level; set optimizer_switch='derived_merge=on,derived_with_keys=on'; set @save_optimizer_switch=@@optimizer_switch; +set join_cache_level=1; create table t1(f1 int, f11 int); create table t2(f2 int, f22 int); insert into t1 values(1,1),(2,2),(3,3),(5,5),(9,9),(7,7); @@ -1482,6 +1484,8 @@ INSERT INTO t1 VALUES (1,NULL,NULL), (5,'r','r'), (7,'y','y'); CREATE TABLE t2 (a int NOT NULL , b int, c varchar(1)); INSERT INTO t2 VALUES (4,3,'r'); CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; SET SESSION optimizer_switch='derived_with_keys=off'; EXPLAIN SELECT * FROM t3 @@ -1489,8 +1493,8 @@ WHERE t3.b IN (SELECT v1.b FROM v1, t2 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; End temporary +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; Start temporary; End temporary 3 DERIVED t1 ALL NULL NULL NULL NULL 3 SELECT * FROM t3 WHERE t3.b IN (SELECT v1.b FROM v1, t2 @@ -1512,6 +1516,7 @@ WHERE t3.b IN (SELECT v1.b FROM v1, t2 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); a b c 20 r r +SET optimizer_switch=@save_optimizer_switch; DROP VIEW v1; DROP TABLE t1,t2,t3; # @@ -1597,6 +1602,7 @@ CREATE TABLE t3 ( b int NOT NULL, e varchar(1) NOT NULL, d varchar(1) NOT NULL, KEY (e,b) ); INSERT INTO t3 VALUES (4,'x','x'),(9,'w','w'),(4,'d','d'),(8,'e','e'); +INSERT INTO t3 VALUES (14,'a','a'),(19,'b','b'),(14,'c','c'),(18,'d','d'); CREATE TABLE t4 (i int NOT NULL, m varchar(1) NOT NULL) ; INSERT INTO t4 VALUES (8,'m'),(9,'d'),(2,'s'),(4,'r'),(8,'m'); CREATE TABLE t5 ( @@ -1612,30 +1618,36 @@ SET SESSION join_buffer_size = 512; EXPLAIN SELECT t2.d FROM t1,t2,v3 WHERE v3.e = t2.d AND v3.i < 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 -1 SIMPLE t3 ref e e 3 test.t2.d 1 Using index -1 SIMPLE t5 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 SIMPLE t5 ALL NULL NULL NULL NULL 2 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +1 SIMPLE t3 ref e e 3 test.t2.d 1 Using index 1 SIMPLE t4 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) SELECT t2.d FROM t1,t2,v3 WHERE v3.e = t2.d AND v3.i < 3; d w -d -e w -d -e w -d -e w -d -e w -d -e w d +d +d +d +d +d +d +d +d +d +d +d +e +e +e +e +e e SET SESSION join_cache_level = DEFAULT; SET SESSION join_buffer_size = DEFAULT; @@ -1649,6 +1661,8 @@ INSERT INTO t1 VALUES ('c'), ('a'); CREATE TABLE t2 (a int, b int, c varchar(1)); INSERT INTO t2 VALUES (29,8,'c'), (39,7,'b'); CREATE TABLE t3 (b int); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; EXPLAIN EXTENDED SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t @@ -1710,6 +1724,7 @@ FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b <> 0 AND t.c = t1.a; b c a 8 c c +SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3; # # Bug #880724: materialized const view as inner table of outer join @@ -1719,6 +1734,8 @@ INSERT INTO t1 VALUES (9,NULL), (6,'r'), (7,'c'); CREATE TABLE t2 (a int); INSERT INTO t2 VALUES (6); CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; SET SESSION optimizer_switch = 'derived_with_keys=on'; SET SESSION join_cache_level = 4; EXPLAIN @@ -1749,6 +1766,7 @@ a b 5 r 9 y SET SESSION join_cache_level = default; +SET optimizer_switch=@save_optimizer_switch; DROP VIEW v2; DROP TABLE t1,t2,t3; # @@ -1872,3 +1890,4 @@ col_varchar_key pk col_varchar_key col_varchar_nokey set max_heap_table_size= @tmp_882994; drop table t1,t2,t3; set optimizer_switch=@exit_optimizer_switch; +set join_cache_level=@exit_join_cache_level; |