summaryrefslogtreecommitdiff
path: root/mysql-test/r/selectivity_innodb.result
diff options
context:
space:
mode:
authorIgor Babaev <igor@askmonty.org>2013-11-26 15:04:21 -0800
committerIgor Babaev <igor@askmonty.org>2013-11-26 15:04:21 -0800
commitacc539a2c4a8ac591a641ad47c699305839997b8 (patch)
tree6fcefd36db6200a93090cad0303ac592efb348ac /mysql-test/r/selectivity_innodb.result
parent6774e86d836f83bf0c7f3a9f2facf15ad89f394e (diff)
downloadmariadb-git-acc539a2c4a8ac591a641ad47c699305839997b8.tar.gz
Added the test case for bug mdev-5200.
The bug was fixed by the patch applied to the 5.3 tree in the revision 3727.
Diffstat (limited to 'mysql-test/r/selectivity_innodb.result')
-rw-r--r--mysql-test/r/selectivity_innodb.result29
1 files changed, 29 insertions, 0 deletions
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 9607bb98765..1dbd9b56079 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1136,6 +1136,35 @@ Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
drop table t1;
set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-5200: impossible where with a semijoin subquery
+# when optimizer_use_condition_selectivity=2
+#
+set use_stat_tables = 'preferably';
+set optimizer_use_condition_selectivity = 2;
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0), (1);
+CREATE TABLE t2 (pk2 int, i2 int, c2 char(1), PRIMARY KEY(pk2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,8,'m'), (2,9,'b');
+CREATE TABLE t3 (c3 char(1), INDEX(c3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('v'), ('c');
+ANALYZE TABLE t1,t2,t3;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+test.t3 analyze status OK
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+i1
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+Warnings:
+Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` left join `test`.`t3` on((`test`.`t3`.`c3` = 'b'))) where 0
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1,t2,t3;
set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
SET SESSION STORAGE_ENGINE=DEFAULT;