diff options
Diffstat (limited to 'mysql-test/r/derived_view.result')
| -rw-r--r-- | mysql-test/r/derived_view.result | 58 |
1 files changed, 54 insertions, 4 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 587a1dd9e31..4756daaf687 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -80,6 +80,7 @@ Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 flush status; select * from (select * from t1 where f1 in (2,3)) tt where f11=2; @@ -93,6 +94,7 @@ Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 12 for merged views create view v1 as select * from t1; @@ -165,6 +167,7 @@ Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 flush status; select * from v4 where f2 in (1,3); @@ -178,6 +181,7 @@ Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 12 for materialized derived tables explain for simple derived @@ -229,6 +233,7 @@ Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 flush status; select * from t1 join (select * from t2 group by f2) tt on f1=f2; @@ -244,6 +249,7 @@ Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 Handler_read_rnd 11 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 36 for materialized views drop view v1,v2,v3; @@ -318,6 +324,7 @@ Handler_read_last 0 Handler_read_next 22 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 60 explain showing late materialization flush status; @@ -334,6 +341,7 @@ Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 flush status; select * from t1 join v2 on f1=f2; @@ -349,6 +357,7 @@ Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 Handler_read_rnd 11 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 36 explain extended select * from v1 join v4 on f1=f2; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -443,6 +452,7 @@ Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 Handler_read_rnd 8 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 39 flush status; merged in merged derived join merged in merged derived @@ -1089,7 +1099,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 @@ -1103,7 +1113,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 @@ -1117,7 +1127,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5)))))))) DROP VIEW v1; @@ -1800,6 +1810,7 @@ INSERT INTO t2 VALUES (7), (4); CREATE TABLE t1 (b int NOT NULL); INSERT INTO t1 VALUES (5), (7); CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +SET @save_optimizer_switch=@@optimizer_switch; SET SESSION optimizer_switch='derived_merge=off'; PREPARE st1 FROM 'SELECT * FROM (SELECT * FROM t2 LEFT JOIN v1 ON t2.a = v1.b) AS t'; @@ -1812,9 +1823,9 @@ a b 7 7 4 NULL DEALLOCATE PREPARE st1; +set SESSION optimizer_switch= @save_optimizer_switch; DROP VIEW v1; DROP TABLE t1,t2; -SET SESSION optimizer_switch='derived_merge=on'; # # LP bug #879939: assertion in ha_maria::enable_indexes # with derived_with_keys=on @@ -1832,6 +1843,7 @@ INSERT INTO t1 VALUES ('USA','Mesquite'), ('USA','Metairie'), ('USA','Miami'); CREATE TABLE t3 (a varchar(35)); INSERT INTO t3 VALUES ('Miami'); +SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch = 'derived_with_keys=on'; SET @@tmp_table_size=1024*4; explain SELECT * FROM (SELECT t1.* FROM t1, t2) AS t JOIN t3 ON t3.a = t.b; @@ -1855,6 +1867,7 @@ USA Miami Miami USA Miami Miami USA Miami Miami SET @@tmp_table_size=default; +set SESSION optimizer_switch= @save_optimizer_switch; drop table t1,t2,t3; # # BUG#882994: Crash in QUICK_RANGE_SELECT::reset with derived_with_keys @@ -1889,5 +1902,42 @@ ORDER BY CONCAT(alias2.col_varchar_nokey); col_varchar_key pk col_varchar_key col_varchar_nokey set max_heap_table_size= @tmp_882994; drop table t1,t2,t3; +# +# LP bug #917990: Bad estimate of #rows for derived table with LIMIT +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES +(8), (3), (4), (7), (9), (5), (1), (2); +SELECT * FROM (SELECT * FROM t1 LIMIT 3) t; +a +8 +3 +4 +EXPLAIN +SELECT * FROM (SELECT * FROM t1 LIMIT 3) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 +DROP TABLE t1; +# +# LP BUG#921878 incorrect check of items during columns union types +# aggregation for merged derived tables +# +SET @save_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='derived_merge=on'; +CREATE TABLE t1 ( a ENUM( 'x', 'y' ) ); +insert into t1 values ('x'); +CREATE TABLE t2 LIKE t1; +insert into t1 values ('y'); +CREATE TABLE t3 LIKE t1; +INSERT INTO t3 +SELECT * FROM ( SELECT * FROM t1 ) AS A +UNION SELECT * FROM t2; +select * from t3; +a +x +y +drop table t1,t2,t3; +set SESSION optimizer_switch= @save_optimizer_switch; set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; |