[Commits] Rev 4545: Fixed bug mdev-7316. in file:///home/igor/maria/maria-10.0/

Igor Babaev igor at askmonty.org
Fri Feb 6 06:09:09 EET 2015


At file:///home/igor/maria/maria-10.0/

------------------------------------------------------------
revno: 4545
revision-id: igor at askmonty.org-20150206040908-23e1eht6sj59qiz3
parent: knielsen at knielsen-hq.org-20141218105908-7n3szfd3xb5bm8gs
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-10.0
timestamp: Thu 2015-02-05 20:09:08 -0800
message:
  Fixed bug mdev-7316.
  The function table_cond_selectivity() should take into account that condition selectivity
  for some fields can be set to 0.
-------------- next part --------------
=== modified file 'mysql-test/r/selectivity.result'
--- a/mysql-test/r/selectivity.result	2014-11-11 16:59:31 +0000
+++ b/mysql-test/r/selectivity.result	2015-02-06 04:09:08 +0000
@@ -1409,4 +1409,40 @@
 set histogram_size=@save_histogram_size;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t0,t1,t2;
+#
+# Bug mdev-7316: a conjunct in WHERE with selectivity == 0
+#
+CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b)) ENGINE=INNODB;
+Warnings:
+Warning	1286	Unknown storage engine 'INNODB'
+Warning	1266	Using storage engine MyISAM for table 't1'
+INSERT INTO t1 VALUES
+('USAChinese',10), ('USAEnglish',20), ('USAFrench',30);
+CREATE TABLE t2 (i int) ENGINE=INNODB;
+Warnings:
+Warning	1286	Unknown storage engine 'INNODB'
+Warning	1266	Using storage engine MyISAM for table 't2'
+INSERT INTO t2 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
+ANALYZE TABLE t1, t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=3;
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+WHERE  a <> 'USARussian' AND b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	PRIMARY,b	b	5	const	1	100.00	Using index condition; Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	14	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` <> 'USARussian') and isnull(`test`.`t1`.`b`))
+SELECT * FROM t1, t2
+WHERE a <> 'USARussian' AND b IS NULL;
+a	b	i
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1,t2;
 set use_stat_tables=@save_use_stat_tables;

=== modified file 'mysql-test/r/selectivity_innodb.result'
--- a/mysql-test/r/selectivity_innodb.result	2014-11-11 16:59:31 +0000
+++ b/mysql-test/r/selectivity_innodb.result	2015-02-06 04:09:08 +0000
@@ -1419,6 +1419,36 @@
 set histogram_size=@save_histogram_size;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t0,t1,t2;
+#
+# Bug mdev-7316: a conjunct in WHERE with selectivity == 0
+#
+CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b)) ENGINE=INNODB;
+INSERT INTO t1 VALUES
+('USAChinese',10), ('USAEnglish',20), ('USAFrench',30);
+CREATE TABLE t2 (i int) ENGINE=INNODB;
+INSERT INTO t2 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
+ANALYZE TABLE t1, t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=3;
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+WHERE  a <> 'USARussian' AND b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	PRIMARY,b	b	5	const	2	66.67	Using where; Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	14	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` <> 'USARussian') and isnull(`test`.`t1`.`b`))
+SELECT * FROM t1, t2
+WHERE a <> 'USARussian' AND b IS NULL;
+a	b	i
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE 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;

=== modified file 'mysql-test/t/selectivity.test'
--- a/mysql-test/t/selectivity.test	2014-11-11 16:59:31 +0000
+++ b/mysql-test/t/selectivity.test	2015-02-06 04:09:08 +0000
@@ -942,5 +942,34 @@
 
 drop table t0,t1,t2;
 
+--echo #
+--echo # Bug mdev-7316: a conjunct in WHERE with selectivity == 0
+--echo #
+
+CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b)) ENGINE=INNODB;
+INSERT INTO t1 VALUES
+  ('USAChinese',10), ('USAEnglish',20), ('USAFrench',30);
+
+CREATE TABLE t2 (i int) ENGINE=INNODB;
+INSERT INTO t2 VALUES
+  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
+
+ANALYZE TABLE t1, t2;
+
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=3;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+  WHERE  a <> 'USARussian' AND b IS NULL;
+
+SELECT * FROM t1, t2
+  WHERE a <> 'USARussian' AND b IS NULL;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2;
+
+
 set use_stat_tables=@save_use_stat_tables;
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2014-12-03 14:49:31 +0000
+++ b/sql/sql_select.cc	2015-02-06 04:09:08 +0000
@@ -7449,8 +7449,12 @@
             else
               fldno= table->key_info[key].key_part[keyparts-1].fieldnr - 1;
             if (keyuse->val->const_item())
-            {              
-              sel /= table->field[fldno]->cond_selectivity;
+            { 
+              if (table->field[fldno]->cond_selectivity > 0)
+	      {            
+                sel /= table->field[fldno]->cond_selectivity;
+                set_if_smaller(sel, 1.0);
+              }
               /* 
                TODO: we could do better here:
                  1. cond_selectivity might be =1 (the default) because quick 
@@ -7504,7 +7508,10 @@
         if (!(next_field->table->map & rem_tables) && next_field->table != table)
         { 
           if (field->cond_selectivity > 0)
+	  {
             sel/= field->cond_selectivity;
+            set_if_smaller(sel, 1.0);
+          }
           break;
         }
       }



More information about the commits mailing list