[Commits] Rev 4596: Merge in file:///home/igor/maria/maria-10.0-trunk/

Igor Babaev igor at askmonty.org
Fri Feb 6 07:46:15 EET 2015


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

------------------------------------------------------------
revno: 4596 [merge]
revision-id: igor at askmonty.org-20150206054604-jr5oed0omvhje9iq
parent: sergii at pisem.net-20150130202439-2n9yc6kjhz2irei9
parent: igor at askmonty.org-20150206040908-23e1eht6sj59qiz3
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-10.0-trunk
timestamp: Thu 2015-02-05 21:46:04 -0800
message:
  Merge
modified:
  mysql-test/r/selectivity.result selectivity.result-20130311144201-36z3tcytmnpk3qyb-1
  mysql-test/r/selectivity_innodb.result selectivity_innodb.r-20130311144212-0eszgglp35wcovk0-1
  mysql-test/t/selectivity.test  selectivity.test-20130311144148-mplnx924f3hdzqzd-1
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
-------------- 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	2015-01-21 11:03:02 +0000
+++ b/sql/sql_select.cc	2015-02-06 05:46:04 +0000
@@ -7451,8 +7451,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 
@@ -7506,7 +7510,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