[Commits] Rev 3683: Fixed bug mdev-4428. in file:///home/tsk/mprog/src/10.0-md83-lp/

timour at askmonty.org timour at askmonty.org
Thu Aug 22 13:46:18 EEST 2013


At file:///home/tsk/mprog/src/10.0-md83-lp/

------------------------------------------------------------
revno: 3683
revision-id: timour at askmonty.org-20130426020530-979oo1s89qw7winv
parent: knielsen at knielsen-hq.org-20130822103642-24tao1ik0dp0bvqd
author: Igor Babaev <igor at askmonty.org>
committer: timour at askmonty.org
branch nick: maria-10.0-mwl253-mdev4428
timestamp: Thu 2013-04-25 19:05:30 -0700
message:
  Fixed bug mdev-4428.
  Do not try to estimate the selectivity of a column range if min/max
  values for the column are not provided and there is no supporting index. 
-------------- next part --------------
=== modified file 'mysql-test/r/selectivity.result'
--- a/mysql-test/r/selectivity.result	2013-04-20 09:16:55 +0000
+++ b/mysql-test/r/selectivity.result	2013-04-26 02:05:30 +0000
@@ -788,6 +788,8 @@ create table t1 (a int);
 insert into t1 values (1);
 create table t2 (b int);
 insert into t2 values (2),(3);
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=off';
 explain extended 
 select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
@@ -796,6 +798,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select 1 AS `a` from (`test`.`t2`) where 0
 select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
 a
+set optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 #
@@ -1128,4 +1131,25 @@ Note	1003	select `test`.`t1`.`a` AS `a`
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-4428: join with range condition  without statistics 
+#                when optimizer_use_condition_selectivity=3
+#
+set use_stat_tables='preferably';
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,1), (8,8);
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES (1,1), (8,8);
+CREATE TABLE t3 (c INT);
+INSERT INTO t3 VALUES (5), (6), (4), (2);
+set optimizer_use_condition_selectivity=3;
+SELECT * FROM t1, t2, t3 WHERE t2.b = t1.a AND t1.a between 5 and 10;
+a       b       a       b       c
+8       8       8       8       5
+8       8       8       8       6
+8       8       8       8       4
+8       8       8       8       2
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1, t2, t3;
+set use_stat_tables=@save_use_stat_tables;
 set use_stat_tables=@save_use_stat_tables;

=== modified file 'mysql-test/r/selectivity_innodb.result'
--- a/mysql-test/r/selectivity_innodb.result	2013-04-20 09:16:55 +0000
+++ b/mysql-test/r/selectivity_innodb.result	2013-04-26 02:05:30 +0000
@@ -794,15 +794,18 @@ create table t1 (a int);
 insert into t1 values (1);
 create table t2 (b int);
 insert into t2 values (2),(3);
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=off';
 explain extended 
 select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    1       0.00    Using where
-1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       0       Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    1       100.00  Using where
+1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 Warnings:
 Note    1003    select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 3))
 select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
 a
+set optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 #
@@ -1136,6 +1139,27 @@ Note	1003	select `test`.`t1`.`a` AS `a`
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-4428: join with range condition  without statistics 
+#                when optimizer_use_condition_selectivity=3
+#
+set use_stat_tables='preferably';
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,1), (8,8);
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES (1,1), (8,8);
+CREATE TABLE t3 (c INT);
+INSERT INTO t3 VALUES (5), (6), (4), (2);
+set optimizer_use_condition_selectivity=3;
+SELECT * FROM t1, t2, t3 WHERE t2.b = t1.a AND t1.a between 5 and 10;
+a       b       a       b       c
+8       8       8       8       5
+8       8       8       8       6
+8       8       8       8       4
+8       8       8       8       2
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1, t2, t3;
+set use_stat_tables=@save_use_stat_tables;
 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	2013-04-20 09:16:55 +0000
+++ b/mysql-test/t/selectivity.test	2013-04-26 02:05:30 +0000
@@ -352,10 +352,15 @@ insert into t1 values (1);
 create table t2 (b int);
 insert into t2 values (2),(3);
 
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=off';
+
 explain extended 
 select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
 select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
 
+set optimizer_switch=@save_optimizer_switch;
+
 drop table t1,t2;
 
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
@@ -707,5 +712,31 @@ drop table t1;
 
 set use_stat_tables=@save_use_stat_tables;
 
+--echo #
+--echo # Bug mdev-4428: join with range condition  without statistics 
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables='preferably';
+
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,1), (8,8);
+
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES (1,1), (8,8);
+
+CREATE TABLE t3 (c INT);
+INSERT INTO t3 VALUES (5), (6), (4), (2);
+
+set optimizer_use_condition_selectivity=3;
+
+SELECT * FROM t1, t2, t3 WHERE t2.b = t1.a AND t1.a between 5 and 10;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1, t2, t3;
+
+set use_stat_tables=@save_use_stat_tables;
 
 set use_stat_tables=@save_use_stat_tables;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2013-08-21 07:51:08 +0000
+++ b/sql/opt_range.cc	2013-04-26 02:05:30 +0000
@@ -3337,6 +3337,11 @@ double records_in_column_ranges(PARAM *p
 
   field= tree->field;
 
+  if (!(field->read_stats &&
+        field->read_stats->get_min_value() &&
+        field->read_stats->get_max_value()))
+    return HA_POS_ERROR;
+
   seq.keyno= idx;
   seq.real_keyno= MAX_KEY;
   seq.param= param;
@@ -3460,7 +3465,7 @@ bool calculate_cond_selectivity_for_tabl
           table->reginfo.impossible_range= 1;
           goto free_alloc;
         }          
-        else
+        else 
         {
           rows= records_in_column_ranges(&param, idx, *key);
           if (rows != HA_POS_ERROR)

=== modified file 'sql/sql_statistics.h'
--- a/sql/sql_statistics.h	2013-04-16 05:43:07 +0000
+++ b/sql/sql_statistics.h	2013-04-26 02:05:30 +0000
@@ -376,6 +376,16 @@ class Column_statistics
     avg_frequency= (ulong) (val * Scale_factor_avg_frequency);
   }
 
+  Field *get_min_value()
+  {
+    return is_null(COLUMN_STAT_MIN_VALUE) ? NULL : min_value;
+  }
+    
+  Field *get_max_value()
+  {
+    return is_null(COLUMN_STAT_MAX_VALUE) ? NULL : max_value;
+  }
+
 };
 
 



More information about the commits mailing list