diff options
Diffstat (limited to 'mysql-test/r/selectivity_innodb.result')
| -rw-r--r-- | mysql-test/r/selectivity_innodb.result | 128 |
1 files changed, 128 insertions, 0 deletions
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 90f6dba83f1..0b7f1c950e5 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -1356,6 +1356,134 @@ foo foo 1 foo foo 2 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; DROP TABLE t1,t2; +# +# Bug mdev-6325: wrong selectivity of a column with ref access +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 (a int, b int, key(a)); +insert into t2 select A.a + 10*B.a, 12345 from t0 A, t0 B, t0 C; +set use_stat_tables='preferably'; +set histogram_size=100; +set optimizer_use_condition_selectivity=4; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t1.a<10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 0.99 Using where +1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10)) +explain extended +select * from t1 straight_join t2 where t1.a=t2.a and t2.a<10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 0.99 Using where +1 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 10)) +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t0,t1,t2; +# +# Bug mdev-6843: col IS NULL in where condition when col is always NULL +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 (a int, b int); +insert into t2 select NULL, a from t1; +set use_stat_tables='preferably'; +set histogram_size=100; +set optimizer_use_condition_selectivity=4; +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain extended +select * from t2 a straight_join t2 b where a.a is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE a ALL NULL NULL NULL NULL 1000 100.00 Using where +1 SIMPLE b ALL NULL NULL NULL NULL 1000 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`a`.`a` AS `a`,`test`.`a`.`b` AS `b`,`test`.`b`.`a` AS `a`,`test`.`b`.`b` AS `b` from `test`.`t2` `a` straight_join `test`.`t2` `b` where isnull(`test`.`a`.`a`) +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t0,t1,t2; set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; +set @tmp_ust= @@use_stat_tables; +set @tmp_oucs= @@optimizer_use_condition_selectivity; +# +# MDEV-6808: MariaDB 10.0.13 crash with optimizer_use_condition_selectivity > 1 +# +set @tmp_mdev6808= @@optimizer_use_condition_selectivity; +SET optimizer_use_condition_selectivity = 2; +CREATE TABLE t1 ( +event_id int(11) unsigned NOT NULL AUTO_INCREMENT, +PRIMARY KEY (event_id) +) ENGINE=InnoDB; +CREATE TABLE t2 ( +repost_id int(11) unsigned NOT NULL AUTO_INCREMENT, +subject_type varchar(24) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, +subject_id int(11) unsigned NOT NULL, +object_type varchar(24) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, +object_id int(11) unsigned NOT NULL, +is_private int(1) NOT NULL DEFAULT '0', +PRIMARY KEY (repost_id), +UNIQUE KEY `BETWEEN` (subject_type,subject_id,object_type,object_id,is_private), +KEY SUBJECT (subject_type,subject_id), +KEY OBJECT (object_type,object_id) +) ENGINE=InnoDB; +SELECT +* +FROM +t2, t1 +WHERE +t2.object_type = 'event' AND +t2.object_id = t1.event_id AND +t2.is_private = 0 AND +t2.subject_id = 127994 AND +t2.subject_type in ('user') +; +repost_id subject_type subject_id object_type object_id is_private event_id +DROP TABLE t1, t2; +set optimizer_use_condition_selectivity=@tmp_mdev6808; +# +# MDEV-6442: Assertion `join->best_read < double(...)' failed with optimizer_use_condition_selectivity >=3, ... +# +SET use_stat_tables = PREFERABLY; +SET optimizer_use_condition_selectivity = 3; +CREATE TABLE t1 ( a VARCHAR(3), b VARCHAR(8), KEY (a,b) ) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('USA','Chinese'),('USA','English'); +CREATE TABLE t2 (i INT) ENGINE=InnoDB; +SELECT * FROM t1, t2 WHERE ( 't', 'o' ) IN ( +SELECT t1_2.b, t1_1.a FROM t1 AS t1_1 STRAIGHT_JOIN t1 AS t1_2 ON ( t1_2.a = t1_1.b ) +); +a b i +DROP TABLE t1,t2; +# +# MDEV-6738: use_stat_table + histograms crashing optimizer +# +set use_stat_tables='preferably'; +set optimizer_use_condition_selectivity=4; +# Need innodb because there is a special kind of field_bit for non-myisam tables +create table t1(col1 int, col2 bit(1) DEFAULT NULL) engine=innodb; +select * from t1 where col2 != true; +col1 col2 +drop table t1; +# +# End of 10.0 tests +# +set use_stat_tables= @tmp_ust; +set optimizer_use_condition_selectivity= @tmp_oucs; SET SESSION STORAGE_ENGINE=DEFAULT; |