summaryrefslogtreecommitdiff
path: root/mysql-test/t/derived_view.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/derived_view.test')
-rw-r--r--mysql-test/t/derived_view.test1265
1 files changed, 1265 insertions, 0 deletions
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
new file mode 100644
index 00000000000..c1a9435ef6c
--- /dev/null
+++ b/mysql-test/t/derived_view.test
@@ -0,0 +1,1265 @@
+--disable_warnings
+drop table if exists t1,t2;
+drop view if exists v1,v2,v3,v4;
+--enable_warnings
+
+set @exit_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='derived_merge=on,derived_with_keys=on';
+# The 'default' value within the scope of this test:
+set @save_optimizer_switch=@@optimizer_switch;
+
+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);
+insert into t1 values(17,17),(13,13),(11,11),(15,15),(19,19);
+insert into t2 values(1,1),(3,3),(2,2),(4,4),(8,8),(6,6);
+insert into t2 values(12,12),(14,14),(10,10),(18,18),(16,16);
+
+--echo Tests:
+
+--echo for merged derived tables
+--echo explain for simple derived
+explain select * from (select * from t1) tt;
+select * from (select * from t1) tt;
+--echo explain for multitable derived
+explain extended select * from (select * from t1 join t2 on f1=f2) tt;
+select * from (select * from t1 join t2 on f1=f2) tt;
+--echo explain for derived with where
+explain extended
+ select * from (select * from t1 where f1 in (2,3)) tt where f11=2;
+select * from (select * from t1 where f1 in (2,3)) tt where f11=2;
+--echo join of derived
+explain extended
+ select * from (select * from t1 where f1 in (2,3)) tt join
+ (select * from t1 where f1 in (1,2)) aa on tt.f1=aa.f1;
+select * from (select * from t1 where f1 in (2,3)) tt join
+ (select * from t1 where f1 in (1,2)) aa on tt.f1=aa.f1;
+
+flush status;
+explain extended
+ select * from (select * from t1 where f1 in (2,3)) tt where f11=2;
+show status like 'Handler_read%';
+flush status;
+select * from (select * from t1 where f1 in (2,3)) tt where f11=2;
+show status like 'Handler_read%';
+
+--echo for merged views
+create view v1 as select * from t1;
+create view v2 as select * from t1 join t2 on f1=f2;
+create view v3 as select * from t1 where f1 in (2,3);
+create view v4 as select * from t2 where f2 in (2,3);
+--echo explain for simple views
+explain extended select * from v1;
+select * from v1;
+--echo explain for multitable views
+explain extended select * from v2;
+select * from v2;
+--echo explain for views with where
+explain extended select * from v3 where f11 in (1,3);
+select * from v3 where f11 in (1,3);
+--echo explain for joined views
+explain extended
+ select * from v3 join v4 on f1=f2;
+select * from v3 join v4 on f1=f2;
+
+flush status;
+explain extended select * from v4 where f2 in (1,3);
+show status like 'Handler_read%';
+flush status;
+select * from v4 where f2 in (1,3);
+show status like 'Handler_read%';
+
+--echo for materialized derived tables
+--echo explain for simple derived
+explain extended select * from (select * from t1 group by f1) tt;
+select * from (select * from t1 having f1=f1) tt;
+--echo explain showing created indexes
+explain extended
+ select * from t1 join (select * from t2 group by f2) tt on f1=f2;
+select * from t1 join (select * from t2 group by f2) tt on f1=f2;
+--echo explain showing late materialization
+flush status;
+explain select * from t1 join (select * from t2 group by f2) tt on f1=f2;
+show status like 'Handler_read%';
+flush status;
+select * from t1 join (select * from t2 group by f2) tt on f1=f2;
+show status like 'Handler_read%';
+
+--echo for materialized views
+drop view v1,v2,v3;
+create view v1 as select * from t1 group by f1;
+create view v2 as select * from t2 group by f2;
+create view v3 as select t1.f1,t1.f11 from t1 join t1 as t11 where t1.f1=t11.f1
+ having t1.f1<100;
+--echo explain for simple derived
+explain extended select * from v1;
+select * from v1;
+--echo explain showing created indexes
+explain extended select * from t1 join v2 on f1=f2;
+select * from t1 join v2 on f1=f2;
+explain extended
+ select * from t1,v3 as v31,v3 where t1.f1=v31.f1 and t1.f1=v3.f1;
+flush status;
+select * from t1,v3 as v31,v3 where t1.f1=v31.f1 and t1.f1=v3.f1;
+show status like 'Handler_read%';
+--echo explain showing late materialization
+flush status;
+explain select * from t1 join v2 on f1=f2;
+show status like 'Handler_read%';
+flush status;
+select * from t1 join v2 on f1=f2;
+show status like 'Handler_read%';
+
+explain extended select * from v1 join v4 on f1=f2;
+select * from v1 join v4 on f1=f2;
+
+--echo merged derived in merged derived
+explain extended select * from (select * from
+ (select * from t1 where f1 < 7) tt where f1 > 2) zz;
+select * from (select * from
+ (select * from t1 where f1 < 7) tt where f1 > 2) zz;
+
+--echo materialized derived in merged derived
+explain extended select * from (select * from
+ (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) zz;
+select * from (select * from
+ (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) zz;
+
+--echo merged derived in materialized derived
+explain extended select * from (select * from
+ (select * from t1 where f1 < 7) tt where f1 > 2 group by f1) zz;
+select * from (select * from
+ (select * from t1 where f1 < 7) tt where f1 > 2 group by f1) zz;
+
+--echo materialized derived in materialized derived
+explain extended select * from (select * from
+ (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) zz;
+select * from (select * from
+ (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) zz;
+
+--echo mat in merged derived join mat in merged derived
+explain extended select * from
+ (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x
+join
+ (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z
+ on x.f1 = z.f1;
+
+flush status;
+select * from
+ (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x
+join
+ (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z
+ on x.f1 = z.f1;
+show status like 'Handler_read%';
+flush status;
+
+--echo merged in merged derived join merged in merged derived
+explain extended select * from
+ (select * from
+ (select * from t1 where f1 < 7 ) tt where f1 > 2 ) x
+join
+ (select * from
+ (select * from t1 where f1 < 7 ) tt where f1 > 2 ) z
+ on x.f1 = z.f1;
+
+select * from
+ (select * from
+ (select * from t1 where f1 < 7 ) tt where f1 > 2 ) x
+join
+ (select * from
+ (select * from t1 where f1 < 7 ) tt where f1 > 2 ) z
+ on x.f1 = z.f1;
+
+--echo materialized in materialized derived join
+--echo materialized in materialized derived
+explain extended select * from
+ (select * from
+ (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) x
+join
+ (select * from
+ (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) z
+ on x.f1 = z.f1;
+
+select * from
+ (select * from
+ (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) x
+join
+ (select * from
+ (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) z
+ on x.f1 = z.f1;
+
+--echo merged view in materialized derived
+explain extended
+select * from (select * from v4 group by 1) tt;
+select * from (select * from v4 group by 1) tt;
+
+--echo materialized view in merged derived
+explain extended
+select * from ( select * from v1 where f1 < 7) tt;
+select * from ( select * from v1 where f1 < 7) tt;
+
+--echo merged view in a merged view in a merged derived
+create view v6 as select * from v4 where f2 < 7;
+explain extended select * from (select * from v6) tt;
+select * from (select * from v6) tt;
+
+--echo materialized view in a merged view in a materialized derived
+create view v7 as select * from v1;
+explain extended select * from (select * from v7 group by 1) tt;
+select * from (select * from v7 group by 1) tt;
+
+--echo join of above two
+explain extended select * from v6 join v7 on f2=f1;
+select * from v6 join v7 on f2=f1;
+
+--echo test two keys
+explain select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1;
+select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1;
+
+
+--echo TODO: Add test with 64 tables mergeable view to test fall back to
+--echo materialization on tables > MAX_TABLES merge
+drop table t1,t2;
+drop view v1,v2,v3,v4,v6,v7;
+
+--echo #
+--echo # LP bug #794909: crash when defining possible keys for
+--echo # a materialized view/derived_table
+--echo #
+
+CREATE TABLE t1 (f1 int) ;
+INSERT INTO t1 VALUES (149), (150), (224), (29);
+
+CREATE TABLE t2 (f1 int, KEY (f1));
+INSERT INTO t2 VALUES (149), (NULL), (224);
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN
+SELECT * FROM v1 JOIN t2 ON v1.f1 = t2.f1;
+SELECT * FROM v1 JOIN t2 ON v1.f1 = t2.f1;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # LP bug #794890: abort failure on multi-update with view
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (20), (7);
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (7), (9), (7);
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT a FROM t1;
+
+CREATE VIEW v2 AS SELECT t2.a FROM t2, v1 WHERE t2.a=t2.a;
+UPDATE v2 SET a = 2;
+SELECT * FROM t2;
+
+UPDATE t1,v2 SET t1.a = 3;
+SELECT * FROM t1;
+
+DELETE t1 FROM t1,v2;
+SELECT * FROM t1;
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # LP bug #802023: MIN/MAX optimization
+--echo # for mergeable derived tables and views
+--echo #
+
+CREATE TABLE t1 (a int, b int, c varchar(32), INDEX idx(a,b));
+INSERT INTO t1 VALUES
+ (7, 74, 'yyyyyyy'), (9, 97, 'aaaaaaaaa'), (2, 23, 'tt'),
+ (5, 55, 'ddddd'), (2, 27, 'ss'), (7, 76, 'xxxxxxx'),
+ (7, 79, 'zzzzzzz'), (9, 92, 'bbbbbbbbb'), (2, 25, 'pp'),
+ (5, 53, 'eeeee'), (2, 23, 'qq'), (7, 76,'wwwwwww'),
+ (7, 74, 'uuuuuuu'), (9, 92, 'ccccccccc'), (2, 25, 'oo');
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT MIN(a) FROM t1 WHERE a >= 5;
+EXPLAIN
+SELECT MIN(a) FROM t1 WHERE a >= 5;
+
+SELECT MIN(a) FROM (SELECT * FROM t1) t WHERE a >= 5;
+EXPLAIN
+SELECT MIN(a) FROM(SELECT * FROM t1) t WHERE a >= 5;
+
+SELECT MIN(a) FROM v1 WHERE a >= 5;
+EXPLAIN
+SELECT MIN(a) FROM v1 WHERE a >= 5;
+
+SELECT MAX(b) FROM t1 WHERE a=7 AND b<75;
+EXPLAIN
+SELECT MAX(b) FROM t1 WHERE a=7 AND b<75;
+
+SELECT MAX(b) FROM (SELECT * FROM t1) t WHERE a=7 AND b<75;
+EXPLAIN
+SELECT MAX(b) FROM (SELECT * FROM t1) t WHERE a=7 AND b<75;
+
+SELECT MAX(b) FROM v1 WHERE a=7 AND b<75;
+EXPLAIN
+SELECT MAX(b) FROM v1 WHERE a=7 AND b<75;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # LP bug #800535: GROUP BY query with nested left join
+--echo # and a derived table in the nest
+--echo #
+
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (1), (2);
+
+CREATE TABLE t2 (a int NOT NULL);
+INSERT INTO t2 VALUES (1), (2);
+
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (3,3), (4,4);
+
+EXPLAIN EXTENDED
+SELECT t.a FROM t1 LEFT JOIN
+ (t2 t JOIN t3 ON t3.b > 5) ON t.a >= 1
+ GROUP BY t.a;
+SELECT t.a FROM t1 LEFT JOIN
+ (t2 t JOIN t3 ON t3.b > 5) ON t.a >= 1
+ GROUP BY t.a;
+
+EXPLAIN EXTENDED
+SELECT t.a FROM t1 LEFT JOIN
+ (( SELECT * FROM t2 ) t JOIN t3 ON t3.b > 5) ON t.a >= 1
+ GROUP BY t.a;
+SELECT t.a FROM t1 LEFT JOIN
+ (( SELECT * FROM t2 ) t JOIN t3 ON t3.b > 5) ON t.a >= 1
+ GROUP BY t.a;
+
+CREATE VIEW v1 AS SELECT * FROM t2;
+
+EXPLAIN EXTENDED
+SELECT t.a FROM t1 LEFT JOIN
+ (v1 t JOIN t3 ON t3.b > 5) ON t.a >= 1
+ GROUP BY t.a;
+SELECT t.a FROM t1 LEFT JOIN
+ (v1 t JOIN t3 ON t3.b > 5) ON t.a >= 1
+ GROUP BY t.a;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #803410: materialized view/dt accessed by two-component key
+--echo #
+
+CREATE TABLE t1 (a varchar(1));
+INSERT INTO t1 VALUES ('c');
+
+CREATE TABLE t2 (a varchar(1) , KEY (a)) ;
+INSERT INTO t2 VALUES ('c'), (NULL), ('r');
+
+CREATE TABLE t3 (a varchar(1), b varchar(1));
+INSERT INTO t3 VALUES
+ ('e', 'c'), ('c', 'c'), ('c', 'r'), ('g', 'a'), ('b', 'x'), ('b', 'y'),
+ ('h', 'w'), ('d', 'z'), ('k', 'v'), ('j', 's'), ('m', 'p'), ('l', 'q');
+
+CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t3 GROUP BY a;
+
+EXPLAIN
+SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b;
+SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+
+--echo #
+--echo # LP bug #802845: select from derived table with limit 0
+--echo #
+
+SELECT * FROM (SELECT 1 LIMIT 0) t;
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (7), (1), (3);
+
+SELECT * FROM (SELECT * FROM t1 LIMIT 0) t;
+
+DROP TABLE t1;
+
+--echo #
+--echo # LP bug #803851: materialized view + IN->EXISTS
+--echo #
+
+SET SESSION optimizer_switch='semijoin=off,derived_with_keys=on';
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (2,2), (3,3), (1,1);
+
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (1), (2), (1);
+
+CREATE TABLE t3 (a int);
+INSERT INTO t3 VALUES (3), (1), (2), (1);
+
+CREATE VIEW v1 AS SELECT a, MAX(b) AS b FROM t1 GROUP BY a;
+
+EXPLAIN EXTENDED
+SELECT * FROM t3
+ WHERE t3.a IN (SELECT v1.a FROM v1, t2 WHERE t2.a = v1.b);
+SELECT * FROM t3
+ WHERE t3.a IN (SELECT v1.a FROM v1, t2 WHERE t2.a = v1.b);
+
+SET SESSION optimizer_switch=@save_optimizer_switch;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #804515: materialized derived + ORDER BY
+--echo #
+
+CREATE TABLE t1 (f1 varchar(1), f2 varchar(1), KEY (f2));
+INSERT INTO t1 VALUES
+ ('r','x'), ('x','d'), ('x','r'), ('r','f'), ('x','x');
+
+CREATE TABLE t2 (f1 varchar(1), f2 varchar(1));
+INSERT INTO t2 VALUES ('s','x');
+
+CREATE TABLE t3 (f1 varchar(1), f2 varchar(1), KEY (f2));
+INSERT INTO t3 VALUES
+ (NULL,'x'), (NULL,'f'), ('t','p'), (NULL,'j'), ('g','c');
+
+CREATE TABLE t4 (f1 int, f2 varchar(1), KEY (f2,f1)) ;
+INSERT INTO t4 VALUES (1,'x'), (5,'r');
+
+EXPLAIN
+SELECT t.f1 AS f
+ FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4
+ WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 ORDER BY f;
+SELECT t.f1 AS f
+ FROM (SELECT DISTINCT t1.* FROM t1,t2 WHERE t2.f2 = t1.f2) t,t3,t4
+ WHERE t4.f2 = t3.f2 AND t4.f2 = t.f1 ORDER BY f;
+
+DROP TABLE t1,t2,t3,t4;
+
+--echo #
+--echo # LP bug #806431: join over materialized derived with key
+--echo #
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (0,0),(3,0),(1,0);
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT a,b FROM t1 ;
+
+SET SESSION optimizer_switch='derived_with_keys=off';
+SELECT * FROM t1 AS t JOIN v1 AS v WHERE t.a = v.b AND t.b = v.b;
+SET SESSION optimizer_switch='derived_with_keys=on';
+EXPLAIN
+SELECT * FROM t1 AS t JOIN v1 AS v WHERE t.a = v.b AND t.b = v.b;
+SELECT * FROM t1 AS t JOIN v1 AS v WHERE t.a = v.b AND t.b = v.b;
+
+SET SESSION optimizer_switch=@save_optimizer_switch;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # LP bug #806477: left join over merged join with
+--echo # where condition containing f=f
+--echo #
+
+CREATE TABLE t1 (a int NOT NULL);
+INSERT INTO t1 VALUES (1), (50), (0);
+
+CREATE TABLE t2 (a int);
+
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (76,2), (1,NULL);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT t3.b, v1.a
+ FROM t3 LEFT JOIN (t2, v1) ON t3.a <> 0
+ WHERE v1.a = v1.a OR t3.b <> 0;
+EXPLAIN EXTENDED
+SELECT t3.b, v1.a
+ FROM t3 LEFT JOIN (t2, v1) ON t3.a <> 0
+ WHERE v1.a = v1.a OR t3.b <> 0;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #806510: subquery with outer reference
+--echo # to a derived_table/view
+--echo #
+
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (4), (NULL);
+
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (8), (0);
+
+CREATE TABLE t3 (a int, b int) ;
+INSERT INTO t3 VALUES (7,8);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT * FROM t1 t
+ WHERE EXISTS (SELECT t3.a FROM t3, t2
+ WHERE t2.a = t3.b AND t.a != 0);
+EXPLAIN
+SELECT * FROM t1 t
+ WHERE EXISTS (SELECT t3.a FROM t3, t2
+ WHERE t2.a = t3.b AND t.a != 0);
+
+SELECT * FROM (SELECT * FROM t1) t
+ WHERE EXISTS (SELECT t3.a FROM t3, t2
+ WHERE t2.a = t3.b AND t.a != 0);
+EXPLAIN
+SELECT * FROM (SELECT * FROM t1) t
+ WHERE EXISTS (SELECT t3.a FROM t3, t2
+ WHERE t2.a = t3.b AND t.a != 0);
+
+SELECT * FROM v1 t
+ WHERE EXISTS (SELECT t3.a FROM t3, t2
+ WHERE t2.a = t3.b AND t.a != 0);
+EXPLAIN
+SELECT * FROM v1 t
+ WHERE EXISTS (SELECT t3.a FROM t3, t2
+ WHERE t2.a = t3.b AND t.a != 0);
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #806097: left join over a view + DISTINCT
+--echo #
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (252,6), (232,0), (174,232);
+
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (232), (174);
+
+CREATE TABLE t3 (c int);
+INSERT INTO t3 VALUES (1), (2);
+
+CREATE VIEW v1 AS SELECT t2.a FROM t3,t2;
+
+SELECT v1.a FROM t1 LEFT JOIN v1 ON t1.b = 0;
+
+SELECT DISTINCT t2.a FROM t1 LEFT JOIN (t3,t2) ON t1.b = 0;
+EXPLAIN
+SELECT DISTINCT t2.a FROM t1 LEFT JOIN (t3,t2) ON t1.b = 0;
+
+SELECT DISTINCT v1.a FROM t1 LEFT JOIN v1 ON t1.b = 0;
+EXPLAIN
+SELECT DISTINCT v1.a FROM t1 LEFT JOIN v1 ON t1.b = 0;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #806504: right join over a view/derived table
+--echo #
+
+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);
+EXPLAIN EXTENDED
+SELECT * FROM t2 RIGHT JOIN (SELECT * FROM t1) AS t ON t.a != 0
+ WHERE t.a IN (SELECT b FROM t1);
+
+SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0
+ WHERE t.a IN (SELECT b FROM t1);
+EXPLAIN EXTENDED
+SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0
+ WHERE t.a IN (SELECT b FROM t1);
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # LP bug #809206: DISTINCT in derived table / view
+--echo #
+
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (0);
+
+CREATE TABLE t2 (a varchar(32), b int, KEY (a)) ;
+INSERT INTO t2 VALUES
+ ('j',28), ('c',29), ('i',26), ('c',29), ('k',27),
+ ('j',28), ('c',29), ('i',25), ('d',26), ('k',27),
+ ('n',28), ('d',29), ('m',26), ('e',29), ('p',27),
+ ('w',28), ('x',29), ('y',25), ('z',26), ('s',27);
+
+
+CREATE TABLE t3 (a varchar(32));
+INSERT INTO t3 VALUES ('j'), ('c');
+
+CREATE VIEW v1 AS SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a;
+
+SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a;
+EXPLAIN
+SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a;
+
+SELECT * FROM (SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a) t;
+EXPLAIN
+SELECT * FROM (SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a) t;
+
+SELECT * FROM v1;
+EXPLAIN
+SELECT * FROM v1;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #809179: right join over a derived table / view
+--echo #
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (6,5);
+
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES (1,0);
+
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (6,5);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT t.a,t.b FROM t3 RIGHT JOIN (t1 AS t, t2) ON t2.b != 0
+ WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
+EXPLAIN EXTENDED
+SELECT t.a,t.b FROM t3 RIGHT JOIN (t1 AS t, t2) ON t2.b != 0
+ WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
+
+SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0
+ WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
+EXPLAIN EXTENDED
+SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0
+ WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
+
+SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0
+ WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
+EXPLAIN EXTENDED
+SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0
+ WHERE (t.a,t.b) NOT IN (SELECT 7, 5);
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #794901: insert into a multi-table view
+--echo #
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int);
+CREATE TABLE t3 (a int);
+
+CREATE VIEW v1 AS SELECT t1.a FROM t1,t2;
+CREATE VIEW v2 AS SELECT a FROM t2 GROUP BY a;
+CREATE VIEW v3 AS SELECT v1.a FROM v1,v2;
+
+-- error ER_NON_INSERTABLE_TABLE
+INSERT INTO v3(a) VALUES (1);
+
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #793448: materialized view accessed by two-component key
+--echo #
+
+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;
+SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1);
+EXPLAIN
+SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1);
+
+SELECT * FROM v2;
+SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2);
+EXPLAIN
+SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2);
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #804686: query over a derived table using a view
+--echo # with a degenerated where condition
+--echo #
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (0,0), (1,0), (0,0), (1,1), (1,0);
+CREATE VIEW v1 AS SELECT a,b FROM t1;
+CREATE VIEW v2 AS SELECT a, MAX(b) AS b FROM t1 GROUP BY a;
+
+SELECT * FROM (SELECT b FROM v1 WHERE b = 0) t WHERE b<>0;
+SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b<>0;
+SELECT * FROM (SELECT b FROM v1 WHERE b = 0) t WHERE b;
+SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b;
+EXPLAIN EXTENDED
+SELECT * FROM (SELECT b FROM v1 WHERE b = 0) t WHERE b;
+EXPLAIN EXTENDED
+SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b;
+
+DROP VIEW v1,v2;
+DROP TABLE t1;
+
+--echo #
+--echo # LP bug #819716: crash with embedded tableless materialized derived
+--echo # with a variable
+--echo #
+
+set optimizer_switch='derived_merge=off';
+EXPLAIN
+SELECT * FROM (SELECT * FROM (SELECT @b) AS t) AS s;
+SELECT * FROM (SELECT * FROM (SELECT @b) AS t) AS s;
+set optimizer_switch='derived_merge=on';
+
+--echo #
+--echo # LP bug #823826: view over join + IS NULL in WHERE
+--echo #
+
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (1), (1);
+
+CREATE TABLE t2 (b int) ;
+INSERT INTO t2 VALUES (9), (NULL), (7);
+
+CREATE VIEW v1 AS SELECT * FROM t1,t2;
+
+EXPLAIN
+SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL;
+SELECT * FROM (SELECT * FROM t1,t2) t WHERE b IS NULL;
+
+EXPLAIN
+SELECT * FROM v1 WHERE b IS NULL;
+SELECT * FROM v1 WHERE b IS NULL;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # LP bug #823835: a duplicate of #823189 with derived table
+--echo #
+
+CREATE TABLE t1 (a varchar(32)) ;
+INSERT INTO t1 VALUES ('r'), ('p');
+
+CREATE TABLE t2 (a int NOT NULL, b varchar(32)) ;
+INSERT INTO t2 VALUES (28,'j');
+
+CREATE TABLE t3 (a int);
+INSERT INTO t3 VALUES (0), (0);
+
+EXPLAIN EXTENDED
+SELECT * FROM (SELECT * FROM t1) AS t
+WHERE EXISTS (SELECT t2.a FROM t3 RIGHT JOIN t2 ON (t3.a = t2.a)
+ WHERE t2.b < t.a);
+SELECT * FROM (SELECT * FROM t1) AS t
+WHERE EXISTS (SELECT t2.a FROM t3 RIGHT JOIN t2 ON (t3.a = t2.a)
+ WHERE t2.b < t.a);
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #824463: nested outer join using a merged view
+--echo # as an inner table
+--echo #
+
+CREATE TABLE t1 (b int, a int) ;
+
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (5), (6);
+
+CREATE TABLE t3 (a int , c int) ;
+INSERT INTO t3 VALUES (22,1), (23,-1);
+
+CREATE TABLE t4 (a int);
+
+CREATE TABLE t5 (d int) ;
+INSERT INTO t5 VALUES (0), (7), (3), (5);
+
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v3 AS SELECT * FROM t3;
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN *
+ FROM ( t2 AS s2
+ JOIN
+ ( t3 AS s3
+ LEFT JOIN
+ ( t4 LEFT JOIN t3 ON t4.a != 0 )
+ ON s3.a != 0)
+ ON s2.a != 0)
+ JOIN t5 ON s3.c != 0 AND t5.d = 0;
+SELECT STRAIGHT_JOIN *
+ FROM ( t2 AS s2
+ JOIN
+ ( t3 AS s3
+ LEFT JOIN
+ ( t4 LEFT JOIN t3 ON t4.a != 0 )
+ ON s3.a != 0)
+ ON s2.a != 0)
+ JOIN t5 ON s3.c != 0 AND t5.d = 0;
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN *
+ FROM t2 AS s2 , t5,
+ (t3 LEFT JOIN (t4 LEFT JOIN t3 AS s3 ON t4.a != 0) ON t3.a != 0)
+ WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+SELECT STRAIGHT_JOIN *
+ FROM t2 AS s2 , t5,
+ (t3 LEFT JOIN (t4 LEFT JOIN t3 AS s3 ON t4.a != 0) ON t3.a != 0)
+ WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN *
+ FROM v2 AS s2 , t5,
+ (t3 LEFT JOIN (t4 LEFT JOIN v3 AS s3 ON t4.a != 0) ON t3.a != 0)
+ WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+SELECT STRAIGHT_JOIN *
+ FROM v2 AS s2 , t5,
+ (t3 LEFT JOIN (t4 LEFT JOIN v3 AS s3 ON t4.a != 0) ON t3.a != 0)
+ WHERE s2.a != 0 AND t3.c != 0 AND t5.d = 0;
+
+SELECT STRAIGHT_JOIN *
+ FROM ( ( t2 AS s2
+ LEFT JOIN
+ ( t3 AS s3
+ LEFT JOIN
+ ( t4 AS s4 JOIN t3 ON s4.a != 0)
+ ON s3.a != 0 )
+ ON s2.a != 0)
+ LEFT JOIN
+ t1 AS s1
+ ON s1.a != 0)
+ JOIN t5 ON s3.c != 0;
+SELECT STRAIGHT_JOIN *
+ FROM ( ( v2 AS s2
+ LEFT JOIN
+ ( v3 AS s3
+ LEFT JOIN
+ ( t4 AS s4 JOIN v3 ON s4.a != 0)
+ ON s3.a != 0 )
+ ON s2.a != 0)
+ LEFT JOIN
+ t1 AS s1
+ ON s1.a != 0)
+ JOIN t5 ON s3.c != 0;
+
+DROP VIEW v2,v3;
+DROP TABLE t1,t2,t3,t4,t5;
+
+--echo #
+--echo # LP bug #872735: derived used in a NOT IN subquery
+--echo #
+
+CREATE TABLE t1 (b int NOT NULL);
+INSERT INTO t1 VALUES (9), (7);
+
+CREATE TABLE t2 (a int NOT NULL) ;
+INSERT INTO t2 VALUES (1), (2);
+
+CREATE TABLE t3 (
+ a int NOT NULL , c int NOT NULL, d varchar(1) NOT NULL,
+ KEY (c,a) , PRIMARY KEY (a)
+);
+INSERT INTO t3 VALUES
+ (14,4,'a'), (15,7,'b'), (16,4,'c'), (17,1,'d'), (18,9,'e'),
+ (19,4,'f'), (20,8,'g');
+
+SET SESSION optimizer_switch='derived_merge=on,subquery_cache=off';
+
+--echo # The following two EXPLAINs must return the same execution plan
+EXPLAIN
+SELECT * FROM t1 , t2
+ WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM t3 t);
+EXPLAIN
+SELECT * FROM t1 , t2
+ WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t);
+
+SELECT * FROM t1 , t2
+ WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a FROM (SELECT * FROM t3) t);
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #874006: materialized view used in IN subquery
+--echo #
+
+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);
+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);
+
+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);
+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);
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #873263: materialized view used in correlated IN subquery
+--echo #
+
+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);
+SELECT * FROM t1 WHERE t1.b IN (SELECT v2.a FROM v2 WHERE v2.b = t1.a);
+
+DROP VIEW v2;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # LP bug #877316: query over a view with correlated subquery in WHERE
+--echo #
+
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)) ;
+INSERT INTO t1 VALUES (18,2), (19,9);
+
+CREATE TABLE t2 (a int, b int) ;
+INSERT INTO t2 VALUES (10,8), (5,10);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT t1.a FROM t1
+ WHERE EXISTS (SELECT t2.a FROM t2 WHERE t2.b < t1.b);
+EXPLAIN
+SELECT t1.a FROM t1
+ WHERE EXISTS (SELECT t2.a FROM t2 WHERE t2.b < t1.b);
+
+SELECT v1.a FROM v1
+ WHERE EXISTS (SELECT t2.a FROM t2 WHERE t2.b < v1.b);
+EXPLAIN
+SELECT v1.a FROM v1
+ WHERE EXISTS (SELECT t2.a FROM t2 WHERE t2.b < v1.b);
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # LP bug #878199: join of two materialized views
+--echo #
+
+CREATE TABLE t1 (a int, b varchar(1)) ;
+INSERT INTO t1 VALUES (7,'c'), (3,'h'), (7,'c');
+
+CREATE TABLE t2 (b varchar(1)) ;
+INSERT INTO t2 VALUES ('p'), ('c'), ('j'), ('c'), ('p');
+
+CREATE VIEW v1 AS SELECT * FROM t1 GROUP BY a,b;
+
+CREATE VIEW v2 AS SELECT * FROM t2 GROUP BY b;
+
+SET SESSION optimizer_switch = 'derived_with_keys=on';
+
+SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1;
+EXPLAIN
+SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1;
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2;
+
+
+--echo #
+--echo # Bug #743378: join over merged view employing BNL
+--echo #
+
+CREATE TABLE t1 ( d varchar(1) NOT NULL) ;
+INSERT INTO t1 VALUES ('j'),('v'),('c');
+
+CREATE TABLE t2 (h time NOT NULL, d varchar(1) NOT NULL) ;
+INSERT INTO t2 VALUES ('05:03:03','w'),('02:59:24','d'),('00:01:58','e');
+
+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');
+
+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 (
+ a int NOT NULL, c int NOT NULL, b int NOT NULL, f date NOT NULL,
+ g date NOT NULL, h time NOT NULL, j time NOT NULL, k datetime NOT NULL
+);
+
+INSERT INTO t5 VALUES
+ (1,4,0,'0000-00-00','0000-00-00','21:22:34','21:22:34','2002-02-13 17:30'),
+ (2,6,8,'2004-09-18','2004-09-18','10:50:38','10:50:38','2008-09-27 00:34');
+
+CREATE VIEW v3 AS SELECT t3.*, t4.i FROM t3, t4, t5;
+
+SET SESSION join_cache_level = 1;
+SET SESSION join_buffer_size = 512;
+
+EXPLAIN
+SELECT t2.d FROM t1,t2,v3 WHERE v3.e = t2.d AND v3.i < 3;
+SELECT t2.d FROM t1,t2,v3 WHERE v3.e = t2.d AND v3.i < 3;
+
+SET SESSION join_cache_level = DEFAULT;
+SET SESSION join_buffer_size = DEFAULT;
+
+DROP VIEW v3;
+DROP TABLE t1,t2,t3,t4,t5;
+
+--echo #
+--echo # Bug #879882: right join within mergeable derived table
+--echo #
+
+CREATE TABLE t1 (a varchar(1));
+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);
+
+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
+ WHERE t.b AND t.c = t1.a;
+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
+ WHERE t.b AND t.c = t1.a;
+
+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
+ WHERE t.b <> 0 AND t.c = t1.a;
+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
+ WHERE t.b <> 0 AND t.c = t1.a;
+
+INSERT INTO t3 VALUES (100), (200);
+
+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
+ WHERE t.b AND t.c = t1.a;
+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
+ WHERE t.b AND t.c = t1.a;
+
+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
+ WHERE t.b <> 0 AND t.c = t1.a;
+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
+ WHERE t.b <> 0 AND t.c = t1.a;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug #880724: materialized const view as inner table of outer join
+--echo #
+
+CREATE TABLE t1 (a int, b varchar(1));
+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 SESSION optimizer_switch = 'derived_with_keys=on';
+SET SESSION join_cache_level = 4;
+
+EXPLAIN
+SELECT t1.b,v2.a FROM t1 LEFT JOIN v2 ON v2.a = t1.a;
+SELECT t1.b,v2.a FROM t1 LEFT JOIN v2 ON v2.a = t1.a;
+
+CREATE TABLE t3 (a int, b varchar(1));
+INSERT INTO t3 VALUES (8,'x'), (5,'r'), (9,'y');
+
+EXPLAIN
+SELECT * FROM t3
+ WHERE t3.b <> ANY (SELECT t1.b FROM t1 LEFT JOIN v2 ON v2.a = t1.a);
+SELECT * FROM t3
+ WHERE t3.b <> ANY (SELECT t1.b FROM t1 LEFT JOIN v2 ON v2.a = t1.a);
+
+SET SESSION join_cache_level = default;
+
+DROP VIEW v2;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug #881449: OUTER JOIN usin a merged view within IN subquery
+--echo #
+
+CREATE TABLE t1 (a varchar(1)) ;
+INSERT INTO t1 VALUES ('y'), ('x');
+
+CREATE TABLE t2 (a int, PRIMARY KEY (a)) ;
+INSERT INTO t2 VALUES (1), (2);
+
+CREATE TABLE t3 (a int, b varchar(1)) ;
+INSERT INTO t3 VALUES (1,'x');
+
+CREATE VIEW v3 AS SELECT * FROM t3;
+
+SET SESSION optimizer_switch='semijoin=on';
+
+EXPLAIN
+SELECT * FROM t1 WHERE a IN (SELECT v3.b FROM t2 RIGHT JOIN v3 ON v3.a = t2.a);
+SELECT * FROM t1 WHERE a IN (SELECT v3.b FROM t2 RIGHT JOIN v3 ON v3.a = t2.a);
+
+set optimizer_switch= @save_optimizer_switch;
+
+DROP VIEW v3;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug #874035: view as an inner table of a materialized derived
+--echo #
+
+CREATE TABLE t2 (a int NOT NULL);
+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 SESSION optimizer_switch='derived_merge=off';
+
+PREPARE st1 FROM
+'SELECT * FROM (SELECT * FROM t2 LEFT JOIN v1 ON t2.a = v1.b) AS t';
+EXECUTE st1;
+EXECUTE st1;
+DEALLOCATE PREPARE st1;
+
+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
+--echo # with derived_with_keys=on
+--echo #
+
+CREATE TABLE t2 (a varchar(3));
+INSERT INTO t2 VALUES ('USA'), ('USA'), ('USA'), ('USA'), ('USA');
+
+CREATE TABLE t1 (a varchar(3), b varchar(35));
+INSERT INTO t1 VALUES
+ ('USA','Lansing'), ('USA','Laredo'), ('USA','Las Vegas'),
+ ('USA','Lexington-Fayett'), ('USA','Lincoln'), ('USA','Little Rock'),
+ ('USA','Livonia'), ('USA','Long Beach'), ('USA','Los Angeles'),
+ ('USA','Louisville'), ('USA','Lowell'), ('USA','Lubbock'),
+ ('USA','Macon'), ('USA','Madison'), ('USA','Manchester'),
+ ('USA','McAllen'), ('USA','Memphis'), ('USA','Mesa'),
+ ('USA','Mesquite'), ('USA','Metairie'), ('USA','Miami');
+
+CREATE TABLE t3 (a varchar(35));
+INSERT INTO t3 VALUES ('Miami');
+
+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;
+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;
+
+drop table t1,t2,t3;
+
+--echo #
+--echo # BUG#882994: Crash in QUICK_RANGE_SELECT::reset with derived_with_keys
+--echo #
+CREATE TABLE t2 (
+ pk varchar(33),
+ col_varchar_key varchar(3) NOT NULL,
+ col_varchar_nokey varchar(52) NOT NULL);
+
+INSERT INTO t2 VALUES ('NICSpanish','NIC','Spanish'),
+ ('NERHausa','NER','Hausa'),('NGAJoruba','NGA','Joruba'),
+ ('NIUNiue','NIU','Niue'),('NFKEnglish','NFK','English'),
+ ('NORNorwegian','NOR','Norwegian'),('CIVAkan','CIV','Akan'),
+ ('OMNArabic','OMN','Arabic'),('PAKPunjabi','PAK','Punjabi'),
+ ('PLWPalau','PLW','Palau'),('PANSpanish','PAN','Spanish'),
+ ('PNGPapuan Langua','PNG','Papuan Languages'), ('PRYSpanish','PRY','Spanish'),
+ ('PERSpanish','PER','Spanish'), ('PCNPitcairnese','PCN','Pitcairnese'),
+ ('MNPPhilippene La','MNP','Philippene Langu'),('PRTPortuguese','PRT','Portuguese'),
+ ('PRISpanish','PRI','Spanish'),('POLPolish','POL','Polish'),('GNQFang','GNQ','Fang');
+
+CREATE TABLE t1 ( col_varchar_nokey varchar(52) NOT NULL ) ;
+INSERT INTO t1 VALUES ('Chinese'),('English'),('French'),('German'),
+ ('Italian'),('Japanese'),('Korean'),('Polish'),('Portuguese'),('Spanish'),
+ ('Tagalog'),('Vietnamese');
+CREATE TABLE t3 ( col_varchar_key varchar(52)) ;
+INSERT INTO t3 VALUES ('United States');
+
+set @tmp_882994= @@max_heap_table_size;
+--disable_warnings
+set max_heap_table_size=1;
+--enable_warnings
+
+SELECT *
+FROM t3 JOIN
+( SELECT t2.* FROM t1, t2 ) AS alias2
+ON ( alias2.col_varchar_nokey = t3.col_varchar_key )
+ORDER BY CONCAT(alias2.col_varchar_nokey);
+
+set max_heap_table_size= @tmp_882994;
+drop table t1,t2,t3;
+
+# The following command must be the last one the file
+set optimizer_switch=@exit_optimizer_switch;
View Lorry Controller Status