[Commits] Rev 4074: MDEV-5917: EITS: different order of predicates in IN (...) causes different estimates in file:///home/psergey/dev2/10.0/

Sergey Petrunya psergey at askmonty.org
Fri Mar 21 13:42:39 EET 2014


At file:///home/psergey/dev2/10.0/

------------------------------------------------------------
revno: 4074
revision-id: psergey at askmonty.org-20140321114237-8neebgq7jfthre82
parent: jplindst at mariadb.org-20140321063904-vpvrd0l8488ln0mm
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0
timestamp: Fri 2014-03-21 15:42:37 +0400
message:
  MDEV-5917: EITS: different order of predicates in IN (...) causes different estimates
  - Forgot to update one .result file.
=== modified file 'mysql-test/r/selectivity_innodb.result'
--- a/mysql-test/r/selectivity_innodb.result	2014-03-19 21:58:29 +0000
+++ b/mysql-test/r/selectivity_innodb.result	2014-03-21 11:42:37 +0000
@@ -1,6 +1,7 @@
 SET SESSION STORAGE_ENGINE='InnoDB';
 set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch;
 set optimizer_switch='extended_keys=on';
+drop table if exists t1,t2,t3;
 select @@global.use_stat_tables;
 @@global.use_stat_tables
 COMPLEMENTARY
@@ -1298,6 +1299,37 @@ a	b	c	d	a	b
 221	56120	56120	28296	28296	3
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1,t2;
+#
+# MDEV-5917: EITS: different order of predicates in IN (...) causes different estimates
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (col1 int);
+# one value in 1..100 range
+insert into t2 select A.a + B.a*10 from t1 A, t1 B;
+# ten values in 100...200 range
+insert into t2 select 100 + A.a + B.a*10 from t1 A, t1 B, t1 C;
+set histogram_type='SINGLE_PREC_HB';
+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	OK
+# The following two must have the same in 'Extra' column:
+explain extended select * from t2 where col1 IN (20, 180);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1100	1.37	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where (`test`.`t2`.`col1` in (20,180))
+explain extended select * from t2 where col1 IN (180, 20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1100	1.37	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where (`test`.`t2`.`col1` in (180,20))
+drop table t1, t2;
+set histogram_type=@save_histogram_type;
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 SET SESSION STORAGE_ENGINE=DEFAULT;



More information about the commits mailing list