[Commits] Rev 4170: MDEV-5980: EITS: if condition is used for REF access, its selectivity is still in filtered% in file:///home/psergey/dev2/10.0-cp/

Sergey Petrunya psergey at askmonty.org
Mon Apr 28 20:54:38 EEST 2014


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

------------------------------------------------------------
revno: 4170
revision-id: psergey at askmonty.org-20140425151206-7lmovg7fngdyjdsy
parent: psergey at askmonty.org-20140425150454-dsk6kba2vn13gw50
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0-cp
timestamp: Fri 2014-04-25 19:12:06 +0400
message:
  MDEV-5980: EITS: if condition is used for REF access, its selectivity is still in filtered%
  - Testcase. The bug is fixed by commit for MDEV-6003
=== modified file 'mysql-test/r/selectivity_no_engine.result'
--- a/mysql-test/r/selectivity_no_engine.result	2014-04-25 15:04:54 +0000
+++ b/mysql-test/r/selectivity_no_engine.result	2014-04-25 15:12:06 +0000
@@ -139,6 +139,32 @@ id	select_type	table	type	possible_keys
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col2` AS `col2` from `test`.`t2` where ((`test`.`t2`.`a` in (1,2,3)) and (`test`.`t2`.`b` in (1,2,3)))
 drop table t2, t1;
 # 
+# MDEV-5980: EITS: if condition is used for REF access, its selectivity is still in filtered%
+# 
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(key1 int, col1 int, key(key1));
+insert into t1 select A.a, A.a from t0 A, t0 B, t0 C;
+set histogram_size=100;
+set use_stat_tables='preferably';
+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	Table is already up to date
+# 10% is ok
+explain extended select * from t1 where col1=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	9.90	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` = 2)
+# Must show 100%, not 10%
+explain extended select * from t1 where key1=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	key1	key1	5	const	98	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`key1` = 2)
+drop table t0, t1;
 # MDEV-6003: EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value
 # 
 create table t0(a int);

=== modified file 'mysql-test/t/selectivity_no_engine.test'
--- a/mysql-test/t/selectivity_no_engine.test	2014-04-25 15:04:54 +0000
+++ b/mysql-test/t/selectivity_no_engine.test	2014-04-25 15:12:06 +0000
@@ -103,6 +103,23 @@ explain extended select * from t2 where
 drop table t2, t1;
 
 --echo # 
+--echo # MDEV-5980: EITS: if condition is used for REF access, its selectivity is still in filtered%
+--echo # 
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(key1 int, col1 int, key(key1));
+insert into t1 select A.a, A.a from t0 A, t0 B, t0 C;
+
+set histogram_size=100;
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=4;
+analyze table t1 persistent for all;
+--echo # 10% is ok
+explain extended select * from t1 where col1=2;
+--echo # Must show 100%, not 10%
+explain extended select * from t1 where key1=2;
+drop table t0, t1;
+
 --echo # MDEV-6003: EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value
 --echo # 
 create table t0(a int);



More information about the commits mailing list