[Commits] Rev 4072: 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
Thu Mar 20 22:53:43 EET 2014


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

------------------------------------------------------------
revno: 4072
revision-id: psergey at askmonty.org-20140320205341-um5vxqsh6ljoer4r
parent: svoj at mariadb.org-20140320071113-ql1nx7dc9h22odx3
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0
timestamp: Fri 2014-03-21 00:53:41 +0400
message:
  MDEV-5917: EITS: different order of predicates in IN (...) causes different estimates
  - Save range key before making field->pos_in_interval() call (like we do for non-equality ranges)
=== modified file 'mysql-test/r/selectivity.result'
--- a/mysql-test/r/selectivity.result	2014-03-19 21:58:29 +0000
+++ b/mysql-test/r/selectivity.result	2014-03-20 20:53:41 +0000
@@ -1,3 +1,4 @@
+drop table if exists t1,t2,t3;
 select @@global.use_stat_tables;
 @@global.use_stat_tables
 COMPLEMENTARY
@@ -1288,4 +1289,35 @@ 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;

=== modified file 'mysql-test/t/selectivity.test'
--- a/mysql-test/t/selectivity.test	2014-03-19 21:58:29 +0000
+++ b/mysql-test/t/selectivity.test	2014-03-20 20:53:41 +0000
@@ -1,5 +1,9 @@
 --source include/have_stat_tables.inc
 
+--disable_warnings
+drop table if exists t1,t2,t3;
+--enable_warnings
+
 select @@global.use_stat_tables;
 select @@session.use_stat_tables;
 
@@ -858,4 +862,31 @@ set optimizer_use_condition_selectivity=
 
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-5917: EITS: different order of predicates in IN (...) causes different estimates
+--echo #
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t2 (col1 int);
+--echo # one value in 1..100 range
+insert into t2 select A.a + B.a*10 from t1 A, t1 B;
+--echo # 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;
+
+--echo # The following two must have the same in 'Extra' column:
+explain extended select * from t2 where col1 IN (20, 180);
+explain extended select * from t2 where 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;

=== modified file 'sql/sql_statistics.cc'
--- a/sql/sql_statistics.cc	2014-03-19 16:05:54 +0000
+++ b/sql/sql_statistics.cc	2014-03-20 20:53:41 +0000
@@ -3514,6 +3514,8 @@ double get_column_range_cardinality(Fiel
         Histogram *hist= &col_stats->histogram;
         if (hist->is_available())
         {
+          store_key_image_to_rec(field, (uchar *) min_endp->key,
+                                 min_endp->length);
           double pos= field->pos_in_interval(col_stats->min_value,
                                              col_stats->max_value);
           res= col_non_nulls * 



More information about the commits mailing list