diff options
Diffstat (limited to 'mysql-test/r/subselect_extra.result')
| -rw-r--r-- | mysql-test/r/subselect_extra.result | 158 |
1 files changed, 155 insertions, 3 deletions
diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result index 22a8a57e8bc..3dfe2bab4a6 100644 --- a/mysql-test/r/subselect_extra.result +++ b/mysql-test/r/subselect_extra.result @@ -1,6 +1,5 @@ drop table if exists t1,t2,t3,t4; -set @subselect_extra_tmp=@@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +drop view if exists v1,v2,v3; # From explain.test: # # Bug#37870: Usage of uninitialized value caused failed assertion. @@ -320,4 +319,157 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 4 Using index 1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) DROP TABLE t1, t2; -set optimizer_switch= @subselect_extra_tmp; +# +# From derived_view.test +# +set @tmp_subselect_extra_derived=@@optimizer_switch; +set optimizer_switch='derived_merge=on,derived_with_keys=on'; +# +# LP bug #806504: right join over a view/derived table +# +CREATE TABLE t1 (a int, b int) ; +INSERT INTO t1 VALUES (0,0); +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (0), (0); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0 +WHERE t.a IN (SELECT b FROM t1); +a a b +NULL 0 0 +EXPLAIN EXTENDED +SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0 +WHERE t.a IN (SELECT b FROM t1); +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 t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t1` semi join (`test`.`t1`) left join `test`.`t2` on((0 <> 0)) where 1 +SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0 +WHERE t.a IN (SELECT b FROM t1); +a a b +NULL 0 0 +EXPLAIN EXTENDED +SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0 +WHERE t.a IN (SELECT b FROM t1); +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 t1 system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t1` semi join (`test`.`t1`) left join `test`.`t2` on((0 <> 0)) where 1 +DROP VIEW v1; +DROP TABLE t1,t2; +# +# LP bug #793448: materialized view accessed by two-component key +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (9,3), (2,5); +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES (9,3), (3,7), (9,1), (2,5), (2,4), (3,8); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,3), (9,7), (9,1), (2,4); +CREATE VIEW v1(a,b) AS SELECT a, MAX(b) FROM t2 GROUP BY a; +CREATE VIEW v2(a,b) AS SELECT a,b FROM t2 UNION SELECT a,b FROM t3; +SELECT * FROM v1; +a b +2 5 +3 8 +9 3 +SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); +a +9 +2 +EXPLAIN +SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +3 DERIVED t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +SELECT * FROM v2; +a b +9 3 +3 7 +9 1 +2 5 +2 4 +3 8 +10 3 +9 7 +SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); +a +9 +2 +EXPLAIN +SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +3 DERIVED t2 ALL NULL NULL NULL NULL 6 +4 UNION t3 ALL NULL NULL NULL NULL 4 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3; +# +# LP bug #874006: materialized view used in IN subquery +# +CREATE TABLE t3 (a int NOT NULL, b varchar(1), c varchar(1)); +INSERT INTO t3 VALUES (19,NULL,NULL), (20,'r','r'); +CREATE TABLE t1 (a int, b varchar(1) , c varchar(1)); +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 SESSION optimizer_switch='derived_with_keys=off'; +EXPLAIN +SELECT * FROM t3 +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; Using join buffer (flat, BNL join) +3 DERIVED t1 ALL NULL NULL NULL NULL 3 +SELECT * FROM t3 +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 SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t3 +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 <derived3> ref key1 key1 10 const,const 0 Start temporary +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) +3 DERIVED t1 ALL NULL NULL NULL NULL 3 +SELECT * FROM t3 +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 +DROP VIEW v1; +DROP TABLE t1,t2,t3; +# +# LP bug #873263: materialized view used in correlated IN subquery +# +CREATE TABLE t1 (a int, b int) ; +INSERT INTO t1 VALUES (5,4), (9,8); +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES (4,5), (5,1); +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +SET SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived3> ref key0 key0 10 test.t1.b,test.t1.a 2 FirstMatch(t1) +3 DERIVED t2 ALL NULL NULL NULL NULL 2 +SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a); +a b +5 4 +DROP VIEW v2; +DROP TABLE t1,t2; +set optimizer_switch= @tmp_subselect_extra_derived; |