diff options
Diffstat (limited to 'mysql-test/t/join_cache.test')
| -rw-r--r-- | mysql-test/t/join_cache.test | 159 |
1 files changed, 159 insertions, 0 deletions
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 4600da9ee17..668d0ca7891 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3423,5 +3423,164 @@ SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8) +--echo # + +create table t1 (uid int, fid int, index(uid)); +insert into t1 values + (1,1), (1,2), (1,3), (1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +create table t2 (uid int primary key, name varchar(128), index(name)); +insert into t2 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +create table t3 (uid int, fid int, index(uid)); +insert into t3 values + (1,1), (1,2), (1,3),(1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +set @tmp_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='semijoin=on'; +set optimizer_switch='materialization=off'; +set optimizer_switch='loosescan=off,firstmatch=off'; +set optimizer_switch='mrr_sort_keys=off'; +set join_cache_level=7; + +create table t4 (uid int primary key, name varchar(128), index(name)); +insert into t4 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +explain select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +--sorted_result +select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; + +drop table t1,t2,t3,t4; + +--echo # +--echo # Bug#50358 - semijoin execution of subquery with outerjoin +--echo # emplying join buffer +--echo # + +CREATE TABLE t1 (i int); +CREATE TABLE t2 (i int); +CREATE TABLE t3 (i int); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (6); +INSERT INTO t3 VALUES (1), (2); + +set @tmp_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='semijoin=on'; +set optimizer_switch='materialization=on'; + +set join_cache_level=0; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); + +set join_cache_level=2; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); + +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #12546542: missing row with semijoin=off + join cache +--echo # (LP bug #922971) +--echo # + +CREATE TABLE t1 (a varchar(1024)); +INSERT INTO t1 VALUES ('v'), ('we'); +CREATE TABLE t2 ( + a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int +); +INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6); +CREATE TABLE t3 (b int, c int); +INSERT INTO t3 VALUES (4,4); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin=off'; +set optimizer_switch='materialization=off'; + +set join_cache_level=0; +EXPLAIN +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); + +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); + +set join_cache_level=2; +EXPLAIN +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); + +set join_cache_level = default; +set optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #925985: LEFT JOIN with optimize_join_buffer_size=off + +--echo # join_buffer_size > join_buffer_space_limit +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (5), (3); + +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES + (3,30), (1,10), (7,70), (2,20), + (3,31), (1,11), (7,71), (2,21), + (3,32), (1,12), (7,72), (2,22); + +CREATE TABLE t3 (b int, c int); +INSERT INTO t3 VALUES (32, 302), (42,400), (30,300); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='optimize_join_buffer_size=off'; +set join_buffer_space_limit=4096; +set join_buffer_size=4096*2; +set join_cache_level=2; +set optimizer_switch='outer_join_with_cache=on'; + +EXPLAIN +SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; +SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; + +set join_buffer_space_limit=default; +set join_buffer_size=default; +set join_cache_level=default; +set optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; |