From b4643d75fe065d92403817a1dd8785b198b6fdec Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 1 Feb 2012 17:09:49 +0200 Subject: fix for LP BUG#921878. Problem was in try to check/use Item_direct_ref of derived view when we have to use real Item_field under it. --- mysql-test/r/derived_view.result | 25 ++++++++++++++++++++++++- mysql-test/t/derived_view.test | 30 ++++++++++++++++++++++++++---- 2 files changed, 50 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index e853f5ac526..eceae5cc3d9 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1800,6 +1800,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 +1813,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 +1833,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 +1857,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 @@ -1906,5 +1909,25 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY 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; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index f8ba1f347f3..95412426aa0 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1192,7 +1192,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 @@ -1201,10 +1201,12 @@ EXECUTE st1; EXECUTE st1; DEALLOCATE PREPARE st1; +set SESSION optimizer_switch= @save_optimizer_switch; + DROP VIEW v1; DROP TABLE t1,t2; -SET SESSION optimizer_switch='derived_merge=on'; + --echo # --echo # LP bug #879939: assertion in ha_maria::enable_indexes @@ -1226,7 +1228,7 @@ INSERT INTO t1 VALUES 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; @@ -1234,7 +1236,7 @@ SELECT * FROM (SELECT t1.* FROM t1, t2) AS t JOIN t3 ON t3.a = t.b; SET @@tmp_table_size=1024*1024*16; SELECT * FROM (SELECT t1.* FROM t1, t2) AS t JOIN t3 ON t3.a = t.b; SET @@tmp_table_size=default; - +set SESSION optimizer_switch= @save_optimizer_switch; drop table t1,t2,t3; --echo # @@ -1291,6 +1293,26 @@ SELECT * FROM (SELECT * FROM t1 LIMIT 3) t; DROP TABLE t1; +--echo # +--echo # LP BUG#921878 incorrect check of items during columns union types +--echo # aggregation for merged derived tables +--echo # +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; + +drop table t1,t2,t3; + +set SESSION optimizer_switch= @save_optimizer_switch; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; -- cgit v1.2.1