summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/selectivity.result45
-rw-r--r--mysql-test/r/selectivity_innodb.result45
-rw-r--r--mysql-test/t/selectivity.test37
-rw-r--r--sql/sql_statistics.h14
4 files changed, 123 insertions, 18 deletions
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 28a38c11b01..22efaa480c6 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -481,7 +481,7 @@ order by s_name
limit 10;
s_name s_address
Supplier#000000010 Saygah3gYWMp72i PY
-set histogram_size=15;
+set histogram_size=127;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
Table Op Msg_type Msg_text
dbt3_s001.part analyze status Table is already up to date
@@ -508,7 +508,7 @@ limit 10;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00
-1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 6.25 Using where
+1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 7.03 Using where
1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; FirstMatch(supplier)
4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 15.14 Using where
Warnings:
@@ -536,7 +536,7 @@ limit 10;
s_name s_address
Supplier#000000010 Saygah3gYWMp72i PY
set histogram_type='DOUBLE_PREC_HB';
-set histogram_size=30;
+set histogram_size=254;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
Table Op Msg_type Msg_text
dbt3_s001.part analyze status Table is already up to date
@@ -562,7 +562,7 @@ limit 10;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00
-1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 6.25 Using where
+1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 7.03 Using where
1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; FirstMatch(supplier)
4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 15.14 Using where
Warnings:
@@ -616,7 +616,7 @@ limit 10;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00
-1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 6.25 Using where
+1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 7.03 Using where
1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; FirstMatch(supplier)
4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 15.14 Using where
Warnings:
@@ -715,4 +715,39 @@ select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
a
drop table t1,t2;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# Bug mdev-4350: erroneous negative selectivity
+#
+create table t1 (a int);
+insert into t1 values (1), (1);
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 values (0);
+select count(*) from t1;
+count(*)
+1025
+set use_stat_tables='preferably';
+set histogram_size=127;
+set histogram_type='SINGLE_PREC_HB';
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+flush table t1;
+set optimizer_use_condition_selectivity=4;
+explain extended select * from t1 where a=0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1025 49.61 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 0)
+drop table t1;
+set histogram_size=@save_histogram_size;
+set histogram_type=@save_histogram_type;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 02f58dbfebe..10e7b6a03c2 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -484,7 +484,7 @@ order by s_name
limit 10;
s_name s_address
Supplier#000000010 Saygah3gYWMp72i PY
-set histogram_size=15;
+set histogram_size=127;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
Table Op Msg_type Msg_text
dbt3_s001.part analyze status OK
@@ -512,7 +512,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
-2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 6.25 Using where
+2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 7.03 Using where
2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where
4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.37 Using where
Warnings:
@@ -540,7 +540,7 @@ limit 10;
s_name s_address
Supplier#000000010 Saygah3gYWMp72i PY
set histogram_type='DOUBLE_PREC_HB';
-set histogram_size=30;
+set histogram_size=254;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
Table Op Msg_type Msg_text
dbt3_s001.part analyze status OK
@@ -567,7 +567,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
-2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 6.25 Using where
+2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 7.03 Using where
2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where
4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.37 Using where
Warnings:
@@ -622,7 +622,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
-2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 6.25 Using where
+2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 7.03 Using where
2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where
4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 14.37 Using where
Warnings:
@@ -722,6 +722,41 @@ select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
a
drop table t1,t2;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+#
+# Bug mdev-4350: erroneous negative selectivity
+#
+create table t1 (a int);
+insert into t1 values (1), (1);
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 values (0);
+select count(*) from t1;
+count(*)
+1025
+set use_stat_tables='preferably';
+set histogram_size=127;
+set histogram_type='SINGLE_PREC_HB';
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+flush table t1;
+set optimizer_use_condition_selectivity=4;
+explain extended select * from t1 where a=0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 1025 49.61 Using where
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 0)
+drop table t1;
+set histogram_size=@save_histogram_size;
+set histogram_type=@save_histogram_type;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
set use_stat_tables=@save_use_stat_tables;
set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index 15cb886dc8b..31ca9eefdbf 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -200,7 +200,7 @@ set optimizer_use_condition_selectivity=3;
eval EXPLAIN EXTENDED $Q20;
eval $Q20;
-set histogram_size=15;
+set histogram_size=127;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
@@ -211,7 +211,7 @@ eval EXPLAIN EXTENDED $Q20;
eval $Q20;
set histogram_type='DOUBLE_PREC_HB';
-set histogram_size=30;
+set histogram_size=254;
ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
@@ -303,4 +303,37 @@ drop table t1,t2;
set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+--echo #
+--echo # Bug mdev-4350: erroneous negative selectivity
+--echo #
+
+create table t1 (a int);
+insert into t1 values (1), (1);
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 values (0);
+select count(*) from t1;
+
+set use_stat_tables='preferably';
+set histogram_size=127;
+set histogram_type='SINGLE_PREC_HB';
+analyze table t1;
+flush table t1;
+
+set optimizer_use_condition_selectivity=4;
+explain extended select * from t1 where a=0;
+
+drop table t1;
+
+set histogram_size=@save_histogram_size;
+set histogram_type=@save_histogram_type;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
set use_stat_tables=@save_use_stat_tables;
diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h
index b699ec7100a..e005ff86291 100644
--- a/sql/sql_statistics.h
+++ b/sql/sql_statistics.h
@@ -152,10 +152,10 @@ private:
uint val= (uint) (pos * prec_factor());
int lp= 0;
int rp= get_width() - 1;
- uint i= 0;
- for (int d= get_width() / 2 ; d; d= (rp - lp) / 2)
+ int d= get_width() / 2;
+ uint i= lp + d;
+ for ( ; d; d= (rp - lp) / 2, i= lp + d)
{
- i= lp + d;
if (val == get_value(i))
break;
if (val < get_value(i))
@@ -237,9 +237,11 @@ public:
uint max= min;
while (max + 1 < get_width() && get_value(max + 1) == get_value(max))
max++;
- double width= ((max + 1 == get_width() ? 1.0 : get_value(max)) -
- (min == 0 ? 0.0 : get_value(min-1))) *
- ((double) 1.0 / prec_factor());
+ double inv_prec_factor= (double) 1.0 / prec_factor();
+ double width= (max + 1 == get_width() ?
+ 1.0 : get_value(max) * inv_prec_factor) -
+ (min == 0 ?
+ 0.0 : get_value(min-1) * inv_prec_factor);
sel= avg_sel * (bucket_sel * (max + 1 - min)) / width;
return sel;
}
View Lorry Controller Status