diff options
Diffstat (limited to 'mysql-test/r/selectivity.result')
| -rw-r--r-- | mysql-test/r/selectivity.result | 37 |
1 files changed, 37 insertions, 0 deletions
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index ac8771bd90a..3704812eb04 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -1129,6 +1129,43 @@ set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivit 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; +# +# Bug mdev-5415: query over an information schema table +# when optimizer_use_condition_selectivity=3 +# +set optimizer_use_condition_selectivity = 3; +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE SQL_MODE != ''; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# # Bug mdev-4429: join with range condition whose selectivity == 0 # when optimizer_use_condition_selectivity=3 # |