diff options
Diffstat (limited to 'mysql-test/r/join_cache.result')
| -rw-r--r-- | mysql-test/r/join_cache.result | 79 |
1 files changed, 79 insertions, 0 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 6dbaa5a93a0..f2fda746e5e 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5313,4 +5313,83 @@ a b c d SET SESSION join_cache_level = DEFAULT; SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3; +# +# Bug #901478: semijoin + ORDER BY + join_cache_level=4|6 +# +CREATE TABLE t1 (a char(1)); +INSERT INTO t1 VALUES ('x'); +CREATE TABLE t2 (a int, b int, c int, KEY(a), KEY(b), KEY(c)); +INSERT INTO t2 VALUES +(9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4); +CREATE TABLE t3 (a CHAR(1)); +INSERT INTO t3 VALUES ('x'); +CREATE TABLE t4 (a int, b int, c int, KEY(b), KEY(c)); +INSERT INTO t4 VALUES +(9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4); +INSERT INTO t4 VALUES +(19,11,10), (17,12,18), (12,13,15), (14,12,19), +(18,13,18), (13,14,11), (15,15,14); +SET @tmp_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch='semijoin=on'; +SET @@optimizer_switch='firstmatch=off'; +SET @@optimizer_switch='mrr=off'; +SET @@optimizer_switch='semijoin_with_cache=off'; +set join_cache_level=1; +EXPLAIN +SELECT * FROM t1,t2 +WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND +t2.a BETWEEN 4 and 5 +ORDER BY t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 Using filesort +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 range a,c a 5 NULL 1 Using index condition; Using where +1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary +SELECT * FROM t1,t2 +WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND +t2.a BETWEEN 4 and 5 +ORDER BY t2.b; +a a b c +x 4 2 9 +x 5 5 4 +set join_cache_level=4; +EXPLAIN +SELECT * FROM t1,t2 +WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND +t2.a BETWEEN 4 and 5 +ORDER BY t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 range a,c a 5 NULL 1 Using index condition; Using where +1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary +SELECT * FROM t1,t2 +WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND +t2.a BETWEEN 4 and 5 +ORDER BY t2.b; +a a b c +x 4 2 9 +x 5 5 4 +SET @@optimizer_switch='semijoin_with_cache=on'; +set join_cache_level=6; +EXPLAIN +SELECT * FROM t1,t2 +WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND +t2.a BETWEEN 4 and 5 +ORDER BY t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 range a,c a 5 NULL 1 Using index condition; Using where +1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary +SELECT * FROM t1,t2 +WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND +t2.a BETWEEN 4 and 5 +ORDER BY t2.b; +a a b c +x 4 2 9 +x 5 5 4 +SET join_cache_level = DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; +DROP TABLE t1,t2,t3,t4; set @@optimizer_switch=@save_optimizer_switch; |