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

Igor Babaev igor at askmonty.org
Sat Mar 22 21:44:40 EET 2014


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

------------------------------------------------------------
revno: 4077
revision-id: igor at askmonty.org-20140322194439-2b3ygjmgdm5q379c
parent: knielsen at knielsen-hq.org-20140321123055-8ch69qymsj1xqmw4
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-10.0
timestamp: Sat 2014-03-22 12:44:39 -0700
message:
  Fixed bug mdev-5931.
  After constant table row substitution the where condition may be converted
  to always true. The function calculate_cond_selectivity_for_table() should
  take into account this possibility.
-------------- next part --------------
=== modified file 'mysql-test/r/selectivity.result'
--- a/mysql-test/r/selectivity.result	2014-03-20 20:53:41 +0000
+++ b/mysql-test/r/selectivity.result	2014-03-22 19:44:39 +0000
@@ -1320,4 +1320,26 @@
 set histogram_type=@save_histogram_type;
 set histogram_size=@save_histogram_size;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# Bug mdev-5931: no where condition after constant table row substitution 
+#                with optimizer_use_condition_selectivity=3
+#
+CREATE TABLE t1 (a varchar(3), b varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo', 'foo');
+CREATE TABLE t2 (c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1), (2);
+set optimizer_use_condition_selectivity=3;
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select 'foo' AS `a`,'foo' AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 1
+SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ;
+a	b	c
+foo	foo	1
+foo	foo	2
+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-03-21 11:42:37 +0000
+++ b/mysql-test/r/selectivity_innodb.result	2014-03-22 19:44:39 +0000
@@ -1330,6 +1330,28 @@
 set histogram_type=@save_histogram_type;
 set histogram_size=@save_histogram_size;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# Bug mdev-5931: no where condition after constant table row substitution 
+#                with optimizer_use_condition_selectivity=3
+#
+CREATE TABLE t1 (a varchar(3), b varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo', 'foo');
+CREATE TABLE t2 (c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1), (2);
+set optimizer_use_condition_selectivity=3;
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select 'foo' AS `a`,'foo' AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 1
+SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ;
+a	b	c
+foo	foo	1
+foo	foo	2
+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 SESSION STORAGE_ENGINE=DEFAULT;

=== modified file 'mysql-test/t/selectivity.test'
--- a/mysql-test/t/selectivity.test	2014-03-20 20:53:41 +0000
+++ b/mysql-test/t/selectivity.test	2014-03-22 19:44:39 +0000
@@ -889,4 +889,27 @@
 set histogram_size=@save_histogram_size;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 
+
+--echo #
+--echo # Bug mdev-5931: no where condition after constant table row substitution 
+--echo #                with optimizer_use_condition_selectivity=3
+--echo #
+
+CREATE TABLE t1 (a varchar(3), b varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo', 'foo');
+
+CREATE TABLE t2 (c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1), (2);
+
+set optimizer_use_condition_selectivity=3;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ;
+
+SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ;
+
+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/opt_range.cc'
--- a/sql/opt_range.cc	2014-03-14 17:52:16 +0000
+++ b/sql/opt_range.cc	2014-03-22 19:44:39 +0000
@@ -3409,7 +3409,7 @@
 
   table->cond_selectivity= 1.0;
 
-  if (table_records == 0)
+  if (!cond || table_records == 0)
     DBUG_RETURN(FALSE);
 
   if (table->pos_in_table_list->schema_table)



More information about the commits mailing list