diff options
author | unknown <gluh@mysql.com/eagle.(none)> | 2007-01-23 14:08:58 +0400 |
---|---|---|
committer | unknown <gluh@mysql.com/eagle.(none)> | 2007-01-23 14:08:58 +0400 |
commit | acef88bee3bb91c2f28af504b69816038fd06977 (patch) | |
tree | ee8fd10d07635f84321d631699151d2b6ed34533 /mysql-test/t/func_in.test | |
parent | 644e6c7fdd75b9e6e869927d956e38964eb95494 (diff) | |
parent | 6447a7b6f2c45a16b24b18d5910036aeb514849c (diff) | |
download | mariadb-git-acef88bee3bb91c2f28af504b69816038fd06977.tar.gz |
Merge mysql.com:/home/gluh/MySQL/Merge/5.0
into mysql.com:/home/gluh/MySQL/Merge/5.0-opt
mysql-test/r/func_in.result:
Auto merged
mysql-test/r/range.result:
Auto merged
mysql-test/r/sp-code.result:
Auto merged
mysql-test/t/func_in.test:
Auto merged
mysql-test/t/range.test:
Auto merged
mysql-test/t/trigger.test:
Auto merged
mysql-test/t/view.test:
Auto merged
sql/item.cc:
Auto merged
sql/item_func.cc:
Auto merged
sql/mysql_priv.h:
Auto merged
sql/sql_insert.cc:
Auto merged
sql/sql_lex.cc:
Auto merged
sql/sql_lex.h:
Auto merged
sql/sql_prepare.cc:
Auto merged
sql/sql_select.cc:
Auto merged
sql/sql_table.cc:
Auto merged
mysql-test/r/select.result:
manual merge
mysql-test/r/view.result:
manual merge
mysql-test/t/select.test:
manual merge
Diffstat (limited to 'mysql-test/t/func_in.test')
-rw-r--r-- | mysql-test/t/func_in.test | 36 |
1 files changed, 36 insertions, 0 deletions
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 86b0268f008..54b81bed133 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -261,5 +261,41 @@ select some_id from t1 where some_id not in('-1', '0'); drop table t1; +# +# BUG#20420: optimizer reports wrong keys on left join with IN +# +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1); + +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t2 VALUES (3,2),(4,2); + +CREATE TABLE t3 (a int PRIMARY KEY); +INSERT INTO t3 VALUES (1),(2),(3),(4); + +CREATE TABLE t4 (a int PRIMARY KEY); +INSERT INTO t4 VALUES (1),(2); + +EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 + JOIN t1 ON t3.a=t1.a + JOIN t2 ON t3.a=t2.a + JOIN t4 WHERE t4.a IN (t1.b, t2.b); + +SELECT STRAIGHT_JOIN * FROM t3 + JOIN t1 ON t3.a=t1.a + JOIN t2 ON t3.a=t2.a + JOIN t4 WHERE t4.a IN (t1.b, t2.b); + +EXPLAIN SELECT STRAIGHT_JOIN + (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) + FROM t3, t1, t2 + WHERE t3.a=t1.a AND t3.a=t2.a; + +SELECT STRAIGHT_JOIN + (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) + FROM t3, t1, t2 + WHERE t3.a=t1.a AND t3.a=t2.a; + +DROP TABLE t1,t2,t3,t4; --echo End of 5.0 tests |