From acc539a2c4a8ac591a641ad47c699305839997b8 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 26 Nov 2013 15:04:21 -0800 Subject: 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. --- mysql-test/t/selectivity.test | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) (limited to 'mysql-test/t/selectivity.test') diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 47f62365816..2b44d5157df 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -708,4 +708,34 @@ drop table t1; set use_stat_tables=@save_use_stat_tables; +--echo # +--echo # Bug mdev-5200: impossible where with a semijoin subquery +--echo # when optimizer_use_condition_selectivity=2 +--echo # + +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; + +SELECT * FROM t1 + WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 ); +EXPLAIN EXTENDED +SELECT * FROM t1 + WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 ); + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +DROP TABLE t1,t2,t3; + set use_stat_tables=@save_use_stat_tables; + -- cgit v1.2.1 From 8efaff4959efe32ff9bc47c1bf1710cfe9ab21b5 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 14 Dec 2013 19:13:37 -0800 Subject: Fixed bug mdev-5415. Do not calculate selectivity of conditions for the tables of the information schema. --- mysql-test/t/selectivity.test | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'mysql-test/t/selectivity.test') diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 2b44d5157df..82baf7cf1af 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -739,3 +739,14 @@ DROP TABLE t1,t2,t3; set use_stat_tables=@save_use_stat_tables; +--echo # +--echo # Bug mdev-5415: query over an information schema table +--echo # when optimizer_use_condition_selectivity=3 +--echo # + +set optimizer_use_condition_selectivity = 3; + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE SQL_MODE != ''; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + -- cgit v1.2.1