diff options
Diffstat (limited to 'mysql-test/main/derived_view.test')
| -rw-r--r-- | mysql-test/main/derived_view.test | 2824 |
1 files changed, 2824 insertions, 0 deletions
diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test new file mode 100644 index 00000000..03565ae0 --- /dev/null +++ b/mysql-test/main/derived_view.test @@ -0,0 +1,2824 @@ +--source include/no_view_protocol.inc + +--source include/default_optimizer_switch.inc + +--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 @exit_join_cache_level=@@join_cache_level; +set @exit_join_buffer_size=@@join_buffer_size; +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; +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); +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; + +--disable_ps2_protocol +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%'; +--enable_ps2_protocol + +--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; + +--disable_ps2_protocol +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%'; +--enable_ps2_protocol + +--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; +--disable_ps2_protocol +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%'; +--enable_ps2_protocol + +explain extended select * from v1 join v4 on f1=f2; +explain format=json 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; +explain format=json 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; +explain format=json 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; + +--disable_ps2_protocol +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; +--enable_ps2_protocol + +--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; +explain format=json 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; +explain format=json +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; +explain format=json 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,materialization=off'; + +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,materialization=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 @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; +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); +SET optimizer_switch=@save_optimizer_switch; + +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'); +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 ( + 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 = @exit_join_cache_level; +SET SESSION join_buffer_size = @exit_join_buffer_size; + +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); + +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 + 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; + +SET optimizer_switch=@save_optimizer_switch; + +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 @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 +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 = @exit_join_cache_level; + +SET optimizer_switch=@save_optimizer_switch; + +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 @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'; +EXECUTE st1; +EXECUTE st1; +DEALLOCATE PREPARE st1; + +set SESSION optimizer_switch= @save_optimizer_switch; + +DROP VIEW v1; +DROP TABLE t1,t2; + + + +--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 @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; +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 # +--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; + +--echo # +--echo # LP bug #917990: Bad estimate of #rows for derived table with LIMIT +--echo # + +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; +EXPLAIN +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; + +--echo # +--echo # LP BUG#944782: derived table from an information schema table +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='derived_merge=on'; +SET SESSION optimizer_switch='derived_with_keys=on'; + +CREATE TABLE t1 (c1 int PRIMARY KEY, c2 char(5)); + +EXPLAIN +SELECT COUNT(*) > 0 + FROM INFORMATION_SCHEMA.COLUMNS + INNER JOIN + (SELECT TABLE_SCHEMA, + GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES + FROM INFORMATION_SCHEMA.STATISTICS + GROUP BY TABLE_SCHEMA) AS UNIQUES + ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); + +# this query crashed in mariadb-5.5.20 +SELECT COUNT(*) > 0 + FROM INFORMATION_SCHEMA.COLUMNS + INNER JOIN + (SELECT TABLE_SCHEMA, + GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES + FROM INFORMATION_SCHEMA.STATISTICS + GROUP BY TABLE_SCHEMA) AS UNIQUES + ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); + +DROP TABLE t1; + +SET SESSION optimizer_switch= @save_optimizer_switch; + +--echo # +--echo # LP BUG#953649: crash when estimating the cost of a look-up +--echo # into a derived table to be materialized +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (132); + +CREATE TABLE t2 (b int, c varchar(256)); +INSERT INTO t2 VALUES (132,'test1'), (120,'text2'), (132,'text3'); + +CREATE VIEW v AS + SELECT b, GROUP_CONCAT(c) AS gc FROM t2 GROUP BY b; + +SET @save_optimizer_switch=@@optimizer_switch; + +SET SESSION optimizer_switch='derived_merge=off'; +SET SESSION optimizer_switch='derived_with_keys=off'; +EXPLAIN +SELECT * FROM t1, v WHERE a = b; +SELECT * FROM t1, v WHERE a = b; + +SET SESSION optimizer_switch='derived_merge=on'; +SET SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t1, v WHERE a = b; +SELECT * FROM t1, v WHERE a = b; + +SET SESSION optimizer_switch= @save_optimizer_switch; + +DROP VIEW v; +DROP TABLE t1,t2; + +--echo # +--echo # LP BUG#968720 crash due to converting to materialized and +--echo # natural join made only once +--echo # + +SET @save968720_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; + +CREATE TABLE t1 (a int, INDEX(a)); +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (a int, INDEX(a)); +INSERT INTO t2 VALUES (1), (2); + +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +SELECT * FROM t1; + +DELETE FROM t1; +INSERT INTO t1 VALUES (1); + +PREPARE stmt FROM " +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +"; +EXECUTE stmt; +SELECT * FROM t1; +EXECUTE stmt; +SELECT * FROM t1; + +drop table t1,t2; +set optimizer_switch=@save968720_optimizer_switch; + +--echo # +--echo # LP BUG#978847 Server crashes in Item_ref::real_item on +--echo # INSERT .. SELECT with FROM subquery and derived_merge=ON +SET @save978847_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; + +CREATE TABLE t1 ( a INT, b INT ); +INSERT INTO t1 VALUES (2,1),(3,2); + +select * from t1; +INSERT INTO t1 SELECT * FROM + ( SELECT * FROM t1 ) AS alias; +select * from t1; +prepare stmt1 from 'INSERT INTO t1 SELECT SQL_BIG_RESULT * FROM + ( SELECT * FROM t1 ) AS alias'; +execute stmt1; +select * from t1; +execute stmt1; +select * from t1; + +drop table t1; + +set optimizer_switch=@save978847_optimizer_switch; + + +--echo # +--echo # LP bug998516 Server hangs on INSERT .. SELECT with derived_merge, +--echo # FROM subquery, UNION +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +INSERT INTO t1 SELECT * FROM ( SELECT * FROM t1 ) AS alias UNION SELECT * FROM t2; +select * from t1; +drop table t1,t2; + +--echo # +--echo # MDEV-3873: Wrong result (extra rows) with NOT IN and +--echo # a subquery from a MERGE view +--echo # + +CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(7),(0); + +CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(2); + +CREATE TABLE t3 (c INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t3 VALUES (4),(6),(3); + +CREATE TABLE t4 (d INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t4 VALUES (4),(5),(3); + +CREATE TABLE tv (e INT NOT NULL) ENGINE=MyISAM; +INSERT INTO tv VALUES (1),(3); + +CREATE ALGORITHM=TEMPTABLE VIEW v_temptable AS SELECT * FROM tv; +CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM tv; + +SELECT * FROM t1, t2 +WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_temptable ON (c = e) WHERE c <> b ) AND a < b; + +SELECT * FROM t1, t2 +WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_merge ON (c = e) WHERE c <> b ) AND a < b; + +SELECT * FROM t1, t2 +WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN (SELECT * FROM tv) as derived ON (c = e) WHERE c <> b ) AND a < b; + +drop view v_temptable, v_merge; +drop table t1,t2,t3,t4,tv; + +--echo # +--echo # MDEV-3912: Wrong result (extra rows) with FROM subquery inside +--echo # ALL subquery, LEFT JOIN, derived_merge. +--echo # (duplicate of MDEV-3873 (above)) +--echo # + +SET @save3912_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on,in_to_exists=on'; + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(8); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7),(0); + +CREATE TABLE t3 (c INT, d INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t3 VALUES (0,4),(8,6); + +SELECT * FROM t1 +WHERE a >= ALL ( +SELECT d FROM t2 LEFT JOIN ( SELECT * FROM t3 ) AS alias ON ( c = b ) +WHERE b >= a +); +set optimizer_switch=@save3912_optimizer_switch; +drop table t1, t2, t3; + +--echo # +--echo # MDEV-4209: equi-join on BLOB column from materialized view +--echo # or derived table +--echo # + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_with_keys=on'; + +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +EXPLAIN EXTENDED +SELECT v1.c1, v1.c2 FROM v1, t2 WHERE v1.c1=t2.c1 AND v1.c2=t2.c2; +SELECT v1.c1, v1.c2 FROM v1, t2 WHERE v1.c1=t2.c1 AND v1.c2=t2.c2; + +EXPLAIN EXTENDED +SELECT t2.c1, t2.c2 FROM (SELECT c1 g, MAX(c2) m FROM t1 GROUP BY c1) t, t2 + WHERE t.g=t2.c1 AND t.m=t2.c2; +SELECT t2.c1, t2.c2 FROM (SELECT c1 g, MAX(c2) m FROM t1 GROUP BY c1) t, t2 + WHERE t.g=t2.c1 AND t.m=t2.c2; + +EXPLAIN EXTENDED +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; + +DROP VIEW v1; +DROP TABLE t1,t2; + +set optimizer_switch=@save_optimizer_switch; + + +--echo # +--echo # mdev-5078: sum over a view/derived table +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 (a) VALUES (1), (2); + +CREATE TABLE t2 (b int(11)); +INSERT INTO t2 (b) VALUES (1), (2); + +CREATE VIEW v AS SELECT b as c FROM t2; + +SELECT a, (SELECT SUM(a + c) FROM v) FROM t1; + +SELECT a, (SELECT SUM(a + c) FROM (SELECT b as c FROM t2) AS v1) FROM t1; + +DROP VIEW v; +DROP TABLE t1,t2; + +--echo # +--echo # mdev-5105: memory overwrite in multi-table update +--echo # using natural join with a view +--echo # + +create table t1(a int,b tinyint,c tinyint)engine=myisam; +create table t2(a tinyint,b float,c int, d int, e int, f int, key (b), key(c), key(d), key(e), key(f))engine=myisam; +create table t3(a int,b int,c int, d int, e int, f int, key(a), key(b), key(c), key(d), key(e), key(f))engine=myisam; +create view v1 as select t2.b a, t1.b b, t2.c c, t2.d d, t2.e e, t2.f f from t1,t2 where t1.a=t2.a; + +update t3 natural join v1 set a:=1; +drop view v1; +drop table t1,t2,t3; + +--echo # +--echo # mdev-5288: assertion failure for query over a view with ORDER BY +--echo # + +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4,1); + +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +EXPLAIN EXTENDED SELECT a FROM v1 WHERE a > 100 ORDER BY b; + +DROP VIEW v1; +DROP TABLE t1; + +# +# MDEV-5414: RAND() in a subselect : different behavior in MariaDB and MySQL +# +CREATE TABLE IF NOT EXISTS `galleries` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(100) NOT NULL, + `year` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS `pictures` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(100) NOT NULL, + `width` float DEFAULT NULL, + `height` float DEFAULT NULL, + `year` int(4) DEFAULT NULL, + `technique` varchar(50) DEFAULT NULL, + `comment` varchar(2000) DEFAULT NULL, + `gallery_id` int(11) NOT NULL, + `type` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `gallery_id` (`gallery_id`) +) DEFAULT CHARSET=utf8 ; + +ALTER TABLE `pictures` + ADD CONSTRAINT `pictures_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`); + +INSERT INTO `galleries` (`id`, `name`, `year`) VALUES +(1, 'Quand le noir et blanc invite le taupe', 2013), +(2, 'Une touche de couleur', 2012), +(3, 'Éclats', 2011), +(4, 'Gris béton', 2010), +(5, 'Expression du spalter', 2010), +(6, 'Zénitude', 2009), +(7, 'La force du rouge', 2008), +(8, 'Sphères', NULL), +(9, 'Centre', 2009), +(10, 'Nébuleuse', NULL); + +INSERT INTO `pictures` (`id`, `name`, `width`, `height`, `year`, `technique`, `comment`, `gallery_id`, `type`) VALUES +(1, 'Éclaircie', 72.5, 100, NULL, NULL, NULL, 1, 1), +(2, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1), +(3, 'Nouveau souffle', 72.5, 100, NULL, NULL, NULL, 1, 1), +(4, 'Échanges (2)', 89, 116, NULL, NULL, NULL, 1, 1), +(5, 'Échanges', 89, 116, NULL, NULL, NULL, 1, 1), +(6, 'Fenêtre de vie', 81, 116, NULL, NULL, NULL, 1, 1), +(7, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1), +(8, 'Nouveau souffle (2)', 72.5, 100, NULL, NULL, NULL, 1, 1), +(9, 'Fluidité', 89, 116, NULL, NULL, NULL, 1, 1), +(10, 'Nouveau Monde', 89, 125, NULL, NULL, NULL, 1, 1), +(11, 'Mirage', 73, 100, NULL, NULL, NULL, 1, 1), +(12, 'Équilibre', 72.5, 116, NULL, NULL, NULL, 2, 1), +(13, 'Fusion', 72.5, 116, NULL, NULL, NULL, 2, 1), +(14, 'Étincelles', NULL, NULL, NULL, NULL, NULL, 3, 1), +(15, 'Régénérescence', NULL, NULL, NULL, NULL, NULL, 3, 1), +(16, 'Chaleur', 80, 80, NULL, NULL, NULL, 4, 1), +(17, 'Création', 90, 90, NULL, NULL, NULL, 4, 1), +(18, 'Horizon', 92, 73, NULL, NULL, NULL, 4, 1), +(19, 'Labyrinthe', 81, 100, NULL, NULL, NULL, 4, 1), +(20, 'Miroir', 80, 116, NULL, NULL, NULL, 5, 1), +(21, 'Libération', 81, 116, NULL, NULL, NULL, 5, 1), +(22, 'Éclats', 81, 116, NULL, NULL, NULL, 5, 1), +(23, 'Zénitude', 116, 89, NULL, NULL, NULL, 6, 1), +(24, 'Écritures lointaines', 90, 90, NULL, NULL, NULL, 7, 1), +(25, 'Émergence', 80, 80, NULL, NULL, NULL, 7, 1), +(26, 'Liberté', 50, 50, NULL, NULL, NULL, 7, 1), +(27, 'Silhouettes amérindiennes', 701, 70, NULL, NULL, NULL, 7, 1), +(28, 'Puissance', 81, 100, NULL, NULL, NULL, 8, 1), +(29, 'Source', 73, 116, NULL, NULL, NULL, 8, 1), +(30, 'Comme une ville qui prend vie', 50, 100, 2008, NULL, NULL, 9, 1), +(31, 'Suspension azur', 80, 80, NULL, NULL, NULL, 9, 1), +(32, 'Nébuleuse', 70, 70, NULL, NULL, NULL, 10, 1), +(33, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(34, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(35, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(36, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(37, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2), +(38, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2); + +# Now we only lest explain to be sure that table materialized. If +# in the future merged derived table will be processed in a way that +# rand() can be called only once then other way of testing correctness +# of this query should be put here. +explain +SELECT g.id AS gallery_id, + g.name AS gallery_name, + p.id AS picture_id, + p.name AS picture_name, + g.p_random AS r1, + g.p_random AS r2, + g.p_random AS r3 +FROM +( + SELECT gal.id, + gal.name, + ( + SELECT pi.id + FROM pictures pi + WHERE pi.gallery_id = gal.id + ORDER BY RAND() + LIMIT 1 + ) AS p_random + FROM galleries gal +) g +LEFT JOIN pictures p + ON p.id = g.p_random +ORDER BY gallery_name ASC +; + +drop table galleries, pictures; + +--echo # +--echo # MDEV-5740: Assertion +--echo #`!derived->first_select()->exclude_from_table_unique_test || +--echo #derived->outer_select()-> exclude_from_table_unique_test' +--echo #failed on 2nd execution of PS with derived_merge +--echo # + +set @save_optimizer_switch5740=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); + +PREPARE stmt FROM ' + INSERT INTO t1 SELECT * FROM t2 UNION SELECT * FROM (SELECT * FROM t1) AS sq +'; +EXECUTE stmt; +select * from t1; +EXECUTE stmt; +select * from t1; +deallocate prepare stmt; + +drop table t1,t2; +set optimizer_switch=@save_optimizer_switch5740; + +--echo # +--echo # Bug mdev-5721: possible long key access to a materialized derived table +--echo # (see also the test case for Bug#13261277 that is actually the same bug) +--echo # + +CREATE TABLE t1 ( + id varchar(255) NOT NULL DEFAULT '', + familyid int(11) DEFAULT NULL, + withdrawndate date DEFAULT NULL, + KEY index_td_familyid_id (familyid,id) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +CREATE TABLE t2 ( + id int(11) NOT NULL AUTO_INCREMENT, + activefromts datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + shortdescription text, + useraccessfamily varchar(512) DEFAULT NULL, + serialized longtext, + PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +insert into t1 values ('picture/89/1369722032695.pmd',89,NULL); +insert into t1 values ('picture/90/1369832057370.pmd',90,NULL); +insert into t2 values (38,'2013-03-04 07:49:22','desc','CODE','string'); + +EXPLAIN +SELECT * FROM t2 x, +(SELECT t2.useraccessfamily, t2.serialized AS picturesubuser, COUNT(*) + FROM t2, t1 GROUP BY t2.useraccessfamily, picturesubuser) y +WHERE x.useraccessfamily = y.useraccessfamily; + +SELECT * FROM t2 x, +(SELECT t2.useraccessfamily, t2.serialized AS picturesubuser, COUNT(*) + FROM t2, t1 GROUP BY t2.useraccessfamily, picturesubuser) y +WHERE x.useraccessfamily = y.useraccessfamily; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug#13261277: Unchecked key length caused missing records. +--echo # + +CREATE TABLE t1 ( + col_varchar varchar(1024) CHARACTER SET utf8 DEFAULT NULL, + stub1 varchar(1024) CHARACTER SET utf8 DEFAULT NULL, + stub2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL, + stub3 varchar(1024) CHARACTER SET utf8 DEFAULT NULL +); + +INSERT INTO t1 VALUES + ('d','d','l','ther'), + (NULL,'s','NJBIQ','trzetuchv'), + (-715390976,'coul','MYWFB','cfhtrzetu'), + (1696792576,'f','i\'s','c'), + (1,'i','ltpemcfhtr','gsltpemcf'), + (-663027712,'mgsltpemcf','sa','amgsltpem'), + (-1686700032,'JPRVK','i','vamgsltpe'), + (NULL,'STUNB','UNVJV','u'), + (5,'oka','qyihvamgsl','AXSMD'), + (NULL,'tqwmqyihva','h','yntqwmqyi'), + (3,'EGMJN','e','e'); + +CREATE TABLE t2 ( + col_varchar varchar(10) DEFAULT NULL, + col_int INT DEFAULT NULL +); + +INSERT INTO t2 VALUES ('d',9); + +set optimizer_switch='derived_merge=off,derived_with_keys=on'; + +SET @save_heap_size= @@max_heap_table_size; +SET @@max_heap_table_size= 16384; + +SELECT t2.col_int +FROM t2 + RIGHT JOIN ( SELECT * FROM t1 ) AS dt + ON t2.col_varchar = dt.col_varchar +WHERE t2.col_int IS NOT NULL ; + +--echo # Shouldn't use auto_key0 for derived table +EXPLAIN +SELECT t2.col_int +FROM t2 + RIGHT JOIN ( SELECT * FROM t1 ) AS dt + ON t2.col_varchar = dt.col_varchar +WHERE t2.col_int IS NOT NULL ; + +SET @@max_heap_table_size= @save_heap_size; +SET optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1,t2; + +--echo # +--echo # end of 5.3 tests +--echo # + +--echo # +--echo # Bug mdev-11161: The second execution of prepared statement +--echo # does not use generated key for materialized +--echo # derived table / view +--echo # (actually this is a 5.3 bug.) +--echo # + +create table t1 ( + mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, + matintnum CHAR(6) NOT NULL, + test MEDIUMINT UNSIGNED NULL +); +create table t2 ( + mat_id MEDIUMINT UNSIGNED NOT NULL, + pla_id MEDIUMINT UNSIGNED NOT NULL +); +insert into t1 values + (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), + (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), + (NULL, 'i', 9); +insert into t2 values + (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), + (3, 101), (3, 102), (3, 105); + +explain + SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id + FROM t1 m2 + INNER JOIN + (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum + FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id + GROUP BY mp.pla_id) d + ON d.matintnum=m2.matintnum; + +prepare stmt1 from +"SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id + FROM t1 m2 + INNER JOIN + (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum + FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id + GROUP BY mp.pla_id) d + ON d.matintnum=m2.matintnum"; + +flush status; +execute stmt1; +show status like '%Handler_read%'; + +flush status; +execute stmt1; +show status like '%Handler_read%'; + +deallocate prepare stmt1; + +drop table t1,t2; + +--echo # +--echo # Bug mdev-12670: mergeable derived / view with subqueries +--echo # subject to semi-join optimizations +--echo # (actually this is a 5.3 bug.) +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (5),(3),(2),(7),(2),(5),(1); +create table t2 (b int, index idx(b)) engine=myisam; +insert into t2 values (2),(3),(2),(1),(3),(4); +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +insert into t2 select b+10 from t2; +analyze table t1,t2; + +explain select a from t1 where a in (select b from t2); +explain select * from (select a from t1 where a in (select b from t2)) t; +create view v1 as select a from t1 where a in (select b from t2); +explain select * from v1; + +drop view v1; +drop table t1,t2; + +--echo # +--echo # Bug mdev-12812: mergeable derived / view with subqueries +--echo # NOT subject to semi-join optimizations +--echo # + +CREATE TABLE t1 (c1 varchar(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),('foo'); + +CREATE TABLE t2 (c2 varchar(3)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('bar'),('qux'),('foo'); + +let $q= +SELECT STRAIGHT_JOIN * + FROM ( SELECT * FROM t1 WHERE c1 IN ( SELECT c2 FROM t2 ) ) AS sq; + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug mdev-16420: materialized view that renames columns +--echo # in inner part of outer join +--echo # + +CREATE TABLE t1 (id int, PRIMARY KEY (id)); +INSERT INTO t1 VALUES (2), (3), (7), (1); + +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT v1.id AS order_pk FROM v1 GROUP BY v1.id; +CREATE VIEW v3 AS +SELECT t.id AS order_pk FROM (SELECT * FROM t1) AS t GROUP BY t.id; + +SELECT * FROM t1 LEFT JOIN v2 ON t1.id=v2.order_pk; +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN v2 ON t1.id=v2.order_pk; + +SELECT * FROM t1 LEFT JOIN v3 ON t1.id=v3.order_pk; +EXPLAIN EXTENDED +SELECT * FROM t1 LEFT JOIN v3 ON t1.id=v3.order_pk; + +DROP VIEW v1,v2,v3; +DROP TABLE t1; + +# The following command must be the last one the file +set optimizer_switch=@exit_optimizer_switch; +set join_cache_level=@exit_join_cache_level; + +--echo # +--echo # Bug mdev-18479: EXPLAIN for query with many expensive derived +--echo # + +CREATE TABLE t1 +(id int auto_increment primary key, + uid int NOT NULL, + gp_id int NOT NULL, + r int NOT NULL +); + +INSERT INTO t1(uid,gp_id,r) VALUES +(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1), +(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1), +(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1), +(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1), +(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,1,1), +(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1), +(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1), +(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1), +(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1), +(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1),(1,2,1), +(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1), +(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1), +(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1), +(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1), +(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1),(1,3,1), +(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1), +(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1), +(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1), +(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1), +(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1),(1,4,1), +(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1), +(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1), +(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1), +(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1), +(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1),(1,5,1), +(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1), +(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1), +(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1), +(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1), +(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1),(1,6,1), +(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1), +(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1), +(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1), +(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1), +(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1),(1,7,1), +(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1), +(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1), +(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1), +(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1), +(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1),(1,8,1), +(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1), +(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1), +(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1), +(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1), +(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1),(1,9,1), +(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1), +(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1), +(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1), +(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1), +(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1), +(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,10,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1), +(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1), +(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1), +(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1), +(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1), +(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1),(1,11,1), +(1,11,1); + +CREATE TABLE t2 (id int) ; +INSERT INTO t2 VALUES (1); + +explain SELECT 1 FROM t2 JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_1 ON gp_1.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_2 ON gp_2.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id + JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_3 ON gp_3.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id + JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_4 ON gp_4.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id + JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_5 ON gp_5.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id + JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_6 ON gp_6.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p1 ON p1.r=1 AND p1.uid=t2.id + JOIN t1 p3 ON p3.r=3 AND p3.uid=t2.id + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + WHERE p1.gp_id=7) gp_7 ON gp_7.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_8 ON gp_8.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_9 ON gp_9.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_14 ON gp_14.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_15 ON gp_15.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + ) gp_16 ON gp_16.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + )gp_17 ON gp_17.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + )gp_18 ON gp_18.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + JOIN t1 p9 ON p9.r=9 AND p9.uid=t2.id + JOIN t1 p10 ON p10.r=10 AND p10.uid=t2.id + )gp_19 ON gp_19.id=t2.id +JOIN +(SELECT t2.id + FROM t2 + JOIN t1 p4 ON p4.r=4 AND p4.uid=t2.id + JOIN t1 p5 ON p5.r=5 AND p5.uid=t2.id + JOIN t1 p6 ON p6.r=6 AND p6.uid=t2.id + JOIN t1 p7 ON p7.r=7 AND p7.uid=t2.id + JOIN t1 p8 ON p8.r=8 AND p8.uid=t2.id + ) gp_20 ON gp_20.id=t2.id ; + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-19778: equality condition for mergeable view returning constants +--echo # in its columns and used as inner table of outer join +--echo # + +create table t1 (pk int, a int); +insert into t1 values (1,7), (2,3), (3,2), (4,3); +create table t2 (b int); +insert into t2 values (5), (1), (NULL), (3); +create table t3 (c int); +insert into t3 values (1), (8); + +create view v1 as +select 3 as d, t2.b from t2; + +let $q= +select * from t1 left join v1 on t1.pk <= 2 where t1.a=v1.d; +eval $q; +eval explain extended $q; + +let $q= +select * from t1 left join (select 3 as d, t2.b from t2) dt on t1.pk <= 2 + where t1.a=dt.d; +eval $q; +eval explain extended $q; + +let $q= +select * from t1 left join (v1,t3) on t1.pk <= 2 where t1.a=v1.d; +eval $q; +eval explain extended $q; + +drop view v1; +drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # +--echo # MDEV-24454 Second execution of SELECT containing set function +--echo # MDEV-25086: whose only argument is an outer reference to a column +--echo # of mergeable view/derived/table/CTE +--echo # + +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (1), (3); +insert into t2 values (70), (30), (70); +create view v1 as select * from t2; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from v1; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from v1 where b > 50) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from (select * from t2) dt1 where b > 50) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +with cte as (select * from (select * from t2) dt1 where b > 50) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--delimiter | +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from v1; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +--delimiter | +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +--delimiter | +create procedure sp1() +begin +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +drop view v1; +drop table t1,t2; + +CREATE TABLE t1(f0 INT); +INSERT INTO t1 VALUES (3); +CREATE VIEW v1 AS SELECT f0 AS f1 FROM t1; +CREATE VIEW v2 AS +SELECT + (SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') FROM v1 n) AS f2, + GROUP_CONCAT('aa' SEPARATOR ', ') AS f3 +FROM v1; +CREATE VIEW v3 AS SELECT * FROM v2; + +CREATE PROCEDURE p1() + SELECT * FROM v3; +CALL p1(); +CALL p1(); + +DROP PROCEDURE p1; +DROP VIEW v1,v2,v3; +DROP TABLE t1; + +--echo # +--echo # MDEV-27212: 2-nd execution of PS for select with embedded derived tables +--echo # and correlated subquery in select list of outer derived +--echo # +create table t1 ( id int, id2 int ) engine=myisam; +create table t2 ( x3 int , x1 int , x2 int, a1 int) engine=myisam; +insert into t1 values (3, 2), (4, 2), (3, 4); +insert into t2 values (1, 2, 2, 1), (1, 3, 3, 2), (2, 3, 3, 1); + +let $q= +select id from t1 + join + ( select dt2.x1, + ( select sum(a1) from t2 where t2.x1 = dt2.x1 ) m + from ( select x1 from t2 u where x3 = 1 ) dt2 + ) dt + on t1.id = dt.x1 +where t1.id2 < dt.m; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval create procedure sp1() $q; +call sp1(); +call sp1(); + +create view v2 as select x1 from t2 u where x3 = 1; +create view v as +select v2.x1, + ( select sum(a1) from t2 where t2.x1 = v2.x1 ) m from v2; + +let $q= +select id from t1 join v on t1.id = v.x1 where t1.id2 < v.m; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval create procedure sp2() $q; +call sp2(); +call sp2(); + +drop procedure sp1; +drop procedure sp2; + +drop view v, v2; + +drop table t1,t2; + +--echo # End of 10.2 tests + +--echo # +--echo # MDEV-30706: view defined as select with implicit grouping and +--echo # a set function used in a subquery +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +CREATE TABLE t2 (a INT PRIMARY KEY, b INT); +INSERT INTO t2 VALUES (1,1), (3,3); +CREATE TABLE t3 (a INT PRIMARY KEY, b INT); +INSERT INTO t3 VALUES (2,2), (4,4), (7,7); +CREATE TABLE t4 (a INT PRIMARY KEY, b INT); +INSERT INTO t4 VALUES (2,2), (5,5), (7,7); + +let $q= +SELECT + (SELECT SUM(t4.b) FROM t1, t2 WHERE t1.a = t2.b GROUP BY t1.a) AS m +FROM t3, t4 + WHERE t3.a = t4.b; + +eval CREATE VIEW v AS $q; + +eval $q; +SELECT * FROM v; +eval WITH cte AS ( $q ) SELECT * FROM cte; + +eval EXPLAIN $q; +EXPLAIN SELECT * FROM v; +eval EXPLAIN WITH cte AS ( $q ) SELECT * FROM cte; + +eval PREPARE stmt FROM "$q"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "SELECT * FROM v"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "WITH cte AS ( $q ) SELECT * FROM cte"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +DROP VIEW v; +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # MDEV-29224: view defined as select with implicit grouping and +--echo # a set function used in a subquery +--echo # + +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 int); +INSERT INTO t2 VALUES (3); + +let $q= +SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1; + +eval CREATE VIEW v AS $q; + +eval $q; +SELECT * FROM v; +eval WITH cte AS ( $q ) SELECT * FROM cte; + +eval EXPLAIN $q; +EXPLAIN SELECT * FROM v; +eval EXPLAIN WITH cte AS ( $q ) SELECT * FROM cte; + +eval PREPARE stmt FROM "$q"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "SELECT * FROM v"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "WITH cte AS ( $q ) SELECT * FROM cte"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +DROP VIEW v; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-28573: view defined as select with implicit grouping and +--echo # a set function used in a subquery +--echo # + +CREATE TABLE t1 (a INTEGER, b INTEGER); +CREATE TABLE t2 (c INTEGER); +INSERT INTO t1 VALUES (1,11), (2,22), (2,22); +INSERT INTO t2 VALUES (1), (2); + +let $q1= +SELECT (SELECT COUNT(b) FROM t2) FROM t1; +let $q2= +SELECT (SELECT COUNT(b) FROM t2 WHERE c > 1) FROM t1; + +eval CREATE VIEW v1 AS $q1; +eval CREATE VIEW v2 AS $q2; + +--error ER_SUBQUERY_NO_1_ROW +eval $q1; +--error ER_SUBQUERY_NO_1_ROW +SELECT * FROM v1; +--error ER_SUBQUERY_NO_1_ROW +eval WITH cte AS ( $q1 ) SELECT * FROM cte; +eval $q2; +SELECT * FROM v2; +eval WITH cte AS ( $q2 ) SELECT * FROM cte; + +eval EXPLAIN $q1; +EXPLAIN SELECT * FROM v1; +eval EXPLAIN WITH cte AS ( $q1 ) SELECT * FROM cte; + +eval PREPARE stmt FROM "$q1"; +--error ER_SUBQUERY_NO_1_ROW +execute stmt; +--error ER_SUBQUERY_NO_1_ROW +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "SELECT * FROM v1"; +--error ER_SUBQUERY_NO_1_ROW +execute stmt; +--error ER_SUBQUERY_NO_1_ROW +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "WITH cte AS ( $q1 ) SELECT * FROM cte"; +--error ER_SUBQUERY_NO_1_ROW +execute stmt; +--error ER_SUBQUERY_NO_1_ROW +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "$q2"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "SELECT * FROM v2"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "WITH cte AS ( $q2 ) SELECT * FROM cte"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +DROP VIEW v1,v2; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-28570: VIEW with WHERE containing subquery +--echo # with set function aggregated in query +--echo # + +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (c int, d int); + +INSERT INTO t1 VALUES + (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40); +INSERT INTO t2 VALUES + (2,10), (2,20), (4,10), (5,10), (3,20), (2,40); + +let $q= +SELECT a FROM t1 GROUP BY a + HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20); + +eval CREATE VIEW v AS $q; + +eval $q; +SELECT * FROM v; +eval WITH cte AS ( $q ) SELECT * FROM cte; + +eval EXPLAIN $q; +EXPLAIN SELECT * FROM v; +eval EXPLAIN WITH cte AS ( $q ) SELECT * FROM cte; + +eval PREPARE stmt FROM "$q"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "SELECT * FROM v"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "WITH cte AS ( $q ) SELECT * FROM cte"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +DROP VIEW v; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-28571: VIEW with select list containing subquery +--echo # with set function aggregated in query +--echo # + +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (m int, n int); +INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4); +INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44); + +let $q= +SELECT (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a) AS c +FROM t1 +GROUP BY a; + +eval CREATE VIEW v AS $q; + +eval $q; +SELECT * FROM v; +eval WITH cte AS ( $q ) SELECT * FROM cte; + +eval EXPLAIN $q; +EXPLAIN SELECT * FROM v; +eval EXPLAIN WITH cte AS ( $q ) SELECT * FROM cte; + +eval PREPARE stmt FROM "$q"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "SELECT * FROM v"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +eval PREPARE stmt FROM "WITH cte AS ( $q ) SELECT * FROM cte"; +execute stmt; +execute stmt; +DEALLOCATE PREPARE stmt; + +DROP VIEW v; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-30668: VIEW with WHERE containing nested subquery +--echo # with set function aggregated in outer subquery +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create table t2 (b int); +insert into t2 values (2), (1), (4), (7); + +create table t3 (a int, b int); +insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40); + +let $q= +select * from t1 + where t1.a in (select t3.a from t3 group by t3.a + having t3.a > any (select t2.b from t2 + where t2.b*10 < sum(t3.b))); +eval create view v as $q; + +eval $q; +eval select * from v; +eval with cte as ( $q ) select * from cte; + +eval explain $q; +eval explain select * from v; +eval explain with cte as ( $q ) select * from cte; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval prepare stmt from "select * from v"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval prepare stmt from "with cte as ( $q ) select * from cte"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v; +drop table t1,t2,t3; + +--echo # End of 10.4 tests + +--echo # +--echo # MDEV-31143: view with ORDER BY used in query with rownum() in WHERE +--echo # + +create table t1 (id int primary key); +insert into t1 values (3), (7), (1); + +create table t2 (a int); +insert into t2 values (2), (4); + +create view v as select a from t2 order by a; + +set big_tables= 1; +select t1.id from v, t1 where rownum() = 1 group by t1.id; + +set big_tables=default; + +drop view v; +drop table t1, t2; + +--echo # +--echo # MDEV-31162: multi-table mergeable view with ORDER BY used +--echo # in query with rownum() in WHERE +--echo # + +create table t1 (a INT) engine=MyISAM; +insert into t1 values (1),(2); + +create table t2 (b INT) engine=MyISAM; +insert into t2 values (3),(4); + +create view v1 AS select * from t1 join t2 order by b; +let $q1= +select * from v1 where rownum() <= 2; + +eval explain $q1; +--sorted_result +eval $q1; + +eval prepare stmt from "$q1"; +--sorted_result +execute stmt; +--sorted_result +execute stmt; +deallocate prepare stmt; + +create view v2 AS select * from t1 join t2 order by b/a; +let $q2= +select * from v2 where rownum() <= 2; + +eval explain $q2; +eval $q2; +eval prepare stmt from "$q2"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v1,v2; +drop table t1,t2; + +--echo # End of 10.6 tests |