diff options
author | Oleksandr Byelkin <sanja@mariadb.com> | 2023-05-04 08:05:40 +0200 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2023-05-04 11:20:35 +0200 |
commit | 62ec258f100ee8fd3072275decd03e39d81fe286 (patch) | |
tree | 3226d79d301df355f1145d0a701bde23e4fcc046 | |
parent | ed3e6f66a265952afded33fb134f6f8bcc31aa89 (diff) | |
download | mariadb-git-62ec258f100ee8fd3072275decd03e39d81fe286.tar.gz |
Fix of selectivity test to behave correctly with embedded and view protocols.
-rw-r--r-- | mysql-test/main/selectivity.result | 69 | ||||
-rw-r--r-- | mysql-test/main/selectivity.test | 84 | ||||
-rw-r--r-- | mysql-test/main/selectivity_innodb.result | 67 | ||||
-rw-r--r-- | mysql-test/main/selectivity_innodb_notembedded.result | 100 | ||||
-rw-r--r-- | mysql-test/main/selectivity_innodb_notembedded.test | 16 | ||||
-rw-r--r-- | mysql-test/main/selectivity_notembedded.result | 95 | ||||
-rw-r--r-- | mysql-test/main/selectivity_notembedded.test | 121 |
7 files changed, 335 insertions, 217 deletions
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 7d7343847cd..a6866e55b13 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1937,75 +1937,6 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 DROP TABLE t1; # End of 10.2 tests -# -# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram -# -create table t0(a int); -insert into t0 select 1 from seq_1_to_78; -create table t1(a int); -insert into t1 select 1 from seq_1_to_26; -create table t10 (a int); -insert into t10 select 0 from t0, seq_1_to_4; -insert into t10 select 8693 from t1; -insert into t10 select 8694 from t1; -insert into t10 select 8695 from t1; -insert into t10 select 34783 from t1; -insert into t10 select 34784 from t1; -insert into t10 select 34785 from t1; -insert into t10 select 34785 from t0, seq_1_to_8; -insert into t10 select 65214 from t1; -insert into t10 select 65215 from t1; -insert into t10 select 65216 from t1; -insert into t10 select 65216 from t0, seq_1_to_52; -insert into t10 select 65217 from t1; -insert into t10 select 65218 from t1; -insert into t10 select 65219 from t1; -insert into t10 select 65219 from t0; -insert into t10 select 73913 from t1; -insert into t10 select 73914 from t1; -insert into t10 select 73915 from t1; -insert into t10 select 73915 from t0, seq_1_to_40; -insert into t10 select 78257 from t1; -insert into t10 select 78258 from t1; -insert into t10 select 78259 from t1; -insert into t10 select 91300 from t1; -insert into t10 select 91301 from t1; -insert into t10 select 91302 from t1; -insert into t10 select 91302 from t0, seq_1_to_6; -insert into t10 select 91303 from t1; -insert into t10 select 91304 from t1; -insert into t10 select 91305 from t1; -insert into t10 select 91305 from t0, seq_1_to_8; -insert into t10 select 99998 from t1; -insert into t10 select 99999 from t1; -insert into t10 select 100000 from t1; -set use_stat_tables=preferably; -analyze table t10 persistent for all; -Table Op Msg_type Msg_text -test.t10 analyze status Engine-independent statistics collected -test.t10 analyze status OK -flush tables; -set @tmp=@@optimizer_trace; -set optimizer_trace=1; -explain select * from t10 where a in (91303); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where -# Must have selectivity_from_histogram <= 1.0: -select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -from information_schema.optimizer_trace; -json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -[ - [ - { - "column_name": "a", - "ranges": - ["91303 <= a <= 91303"], - "selectivity_from_histogram": 0.0357 - } - ] -] -set optimizer_trace=@tmp; -drop table t0,t1,t10; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index 06a3e32da95..b0c440718d1 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1324,90 +1324,12 @@ DROP TABLE t1; --echo # End of 10.2 tests ---echo # ---echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram ---echo # -create table t0(a int); # This holds how many rows we hold in a bucket. -insert into t0 select 1 from seq_1_to_78; - -create table t1(a int); # one-third of a bucket -insert into t1 select 1 from seq_1_to_26; - -create table t10 (a int); -insert into t10 select 0 from t0, seq_1_to_4; - -insert into t10 select 8693 from t1; -insert into t10 select 8694 from t1; -insert into t10 select 8695 from t1; - - -insert into t10 select 34783 from t1; -insert into t10 select 34784 from t1; -insert into t10 select 34785 from t1; - - -insert into t10 select 34785 from t0, seq_1_to_8; - -insert into t10 select 65214 from t1; -insert into t10 select 65215 from t1; -insert into t10 select 65216 from t1; - -insert into t10 select 65216 from t0, seq_1_to_52; - -insert into t10 select 65217 from t1; -insert into t10 select 65218 from t1; -insert into t10 select 65219 from t1; - -insert into t10 select 65219 from t0; - - -insert into t10 select 73913 from t1; -insert into t10 select 73914 from t1; -insert into t10 select 73915 from t1; - -insert into t10 select 73915 from t0, seq_1_to_40; - -insert into t10 select 78257 from t1; -insert into t10 select 78258 from t1; -insert into t10 select 78259 from t1; - -insert into t10 select 91300 from t1; -insert into t10 select 91301 from t1; -insert into t10 select 91302 from t1; - -insert into t10 select 91302 from t0, seq_1_to_6; - -insert into t10 select 91303 from t1; # Only 1/3rd of bucket matches the search tuple -insert into t10 select 91304 from t1; -insert into t10 select 91305 from t1; - -insert into t10 select 91305 from t0, seq_1_to_8; - -insert into t10 select 99998 from t1; -insert into t10 select 99999 from t1; -insert into t10 select 100000 from t1; - -set use_stat_tables=preferably; -analyze table t10 persistent for all; -flush tables; - -set @tmp=@@optimizer_trace; -set optimizer_trace=1; -explain select * from t10 where a in (91303); - ---echo # Must have selectivity_from_histogram <= 1.0: -select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -from information_schema.optimizer_trace; - -set optimizer_trace=@tmp; -drop table t0,t1,t10; - -set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; -set histogram_size=@save_histogram_size; -set use_stat_tables= @save_use_stat_tables; # # Clean up # +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; --source include/restore_charset.inc set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index dfba12f2b05..13816f8185c 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1947,73 +1947,6 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 DROP TABLE t1; # End of 10.2 tests -# -# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram -# -create table t0(a int); -insert into t0 select 1 from seq_1_to_78; -create table t1(a int); -insert into t1 select 1 from seq_1_to_26; -create table t10 (a int); -insert into t10 select 0 from t0, seq_1_to_4; -insert into t10 select 8693 from t1; -insert into t10 select 8694 from t1; -insert into t10 select 8695 from t1; -insert into t10 select 34783 from t1; -insert into t10 select 34784 from t1; -insert into t10 select 34785 from t1; -insert into t10 select 34785 from t0, seq_1_to_8; -insert into t10 select 65214 from t1; -insert into t10 select 65215 from t1; -insert into t10 select 65216 from t1; -insert into t10 select 65216 from t0, seq_1_to_52; -insert into t10 select 65217 from t1; -insert into t10 select 65218 from t1; -insert into t10 select 65219 from t1; -insert into t10 select 65219 from t0; -insert into t10 select 73913 from t1; -insert into t10 select 73914 from t1; -insert into t10 select 73915 from t1; -insert into t10 select 73915 from t0, seq_1_to_40; -insert into t10 select 78257 from t1; -insert into t10 select 78258 from t1; -insert into t10 select 78259 from t1; -insert into t10 select 91300 from t1; -insert into t10 select 91301 from t1; -insert into t10 select 91302 from t1; -insert into t10 select 91302 from t0, seq_1_to_6; -insert into t10 select 91303 from t1; -insert into t10 select 91304 from t1; -insert into t10 select 91305 from t1; -insert into t10 select 91305 from t0, seq_1_to_8; -insert into t10 select 99998 from t1; -insert into t10 select 99999 from t1; -insert into t10 select 100000 from t1; -set use_stat_tables=preferably; -analyze table t10 persistent for all; -Table Op Msg_type Msg_text -test.t10 analyze status Engine-independent statistics collected -test.t10 analyze status OK -flush tables; -set statement optimizer_trace=1 for -explain select * from t10 where a in (91303); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where -# Must have selectivity_from_histogram <= 1.0: -select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -from information_schema.optimizer_trace; -json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -[ - [ - { - "column_name": "a", - "ranges": - ["91303 <= a <= 91303"], - "selectivity_from_histogram": 0.035714283 - } - ] -] -drop table t0,t1,t10; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; diff --git a/mysql-test/main/selectivity_innodb_notembedded.result b/mysql-test/main/selectivity_innodb_notembedded.result new file mode 100644 index 00000000000..013f9196031 --- /dev/null +++ b/mysql-test/main/selectivity_innodb_notembedded.result @@ -0,0 +1,100 @@ +SET SESSION STORAGE_ENGINE='InnoDB'; +set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch; +set optimizer_switch='extended_keys=on'; +drop table if exists t0,t1,t2,t3; +select @@global.use_stat_tables; +@@global.use_stat_tables +COMPLEMENTARY +select @@session.use_stat_tables; +@@session.use_stat_tables +COMPLEMENTARY +set @save_use_stat_tables=@@use_stat_tables; +set use_stat_tables='preferably'; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set join_cache_level=2; +set @@global.histogram_size=0,@@local.histogram_size=0; +set histogram_type='single_prec_hb'; +set optimizer_use_condition_selectivity=3; +# +# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +# +create table t0(a int); +insert into t0 select 1 from seq_1_to_78; +create table t1(a int); +insert into t1 select 1 from seq_1_to_26; +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; +insert into t10 select 34785 from t0, seq_1_to_8; +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; +insert into t10 select 65216 from t0, seq_1_to_52; +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; +insert into t10 select 65219 from t0; +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; +insert into t10 select 73915 from t0, seq_1_to_40; +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; +insert into t10 select 91302 from t0, seq_1_to_6; +insert into t10 select 91303 from t1; +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; +insert into t10 select 91305 from t0, seq_1_to_8; +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; +set use_stat_tables=preferably; +analyze table t10 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status OK +flush tables; +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where +# Must have selectivity_from_histogram <= 1.0: +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel +from information_schema.optimizer_trace; +sel +[ + [ + { + "column_name": "a", + "ranges": + ["91303 <= a <= 91303"], + "selectivity_from_histogram": 0.0357 + } + ] +] +set optimizer_trace=@tmp; +drop table t0,t1,t10; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; +# +# End of 10.4 tests +# +# +# Clean up +# +set @@global.histogram_size=@save_histogram_size; +set optimizer_switch=@save_optimizer_switch_for_selectivity_test; +SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/selectivity_innodb_notembedded.test b/mysql-test/main/selectivity_innodb_notembedded.test new file mode 100644 index 00000000000..387f7dcb7de --- /dev/null +++ b/mysql-test/main/selectivity_innodb_notembedded.test @@ -0,0 +1,16 @@ +--source include/have_innodb.inc +# This test is slow on buildbot. +--source include/big_test.inc +--source include/default_optimizer_switch.inc +--source include/not_embedded.inc + +SET SESSION STORAGE_ENGINE='InnoDB'; + +set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch; +set optimizer_switch='extended_keys=on'; + +--source selectivity_notembedded.test + +set optimizer_switch=@save_optimizer_switch_for_selectivity_test; + +SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/selectivity_notembedded.result b/mysql-test/main/selectivity_notembedded.result new file mode 100644 index 00000000000..7388c17e991 --- /dev/null +++ b/mysql-test/main/selectivity_notembedded.result @@ -0,0 +1,95 @@ +drop table if exists t0,t1,t2,t3; +select @@global.use_stat_tables; +@@global.use_stat_tables +COMPLEMENTARY +select @@session.use_stat_tables; +@@session.use_stat_tables +COMPLEMENTARY +set @save_use_stat_tables=@@use_stat_tables; +set use_stat_tables='preferably'; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set join_cache_level=2; +set @@global.histogram_size=0,@@local.histogram_size=0; +set histogram_type='single_prec_hb'; +set optimizer_use_condition_selectivity=3; +# +# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +# +create table t0(a int); +insert into t0 select 1 from seq_1_to_78; +create table t1(a int); +insert into t1 select 1 from seq_1_to_26; +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; +insert into t10 select 34785 from t0, seq_1_to_8; +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; +insert into t10 select 65216 from t0, seq_1_to_52; +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; +insert into t10 select 65219 from t0; +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; +insert into t10 select 73915 from t0, seq_1_to_40; +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; +insert into t10 select 91302 from t0, seq_1_to_6; +insert into t10 select 91303 from t1; +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; +insert into t10 select 91305 from t0, seq_1_to_8; +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; +set use_stat_tables=preferably; +analyze table t10 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status OK +flush tables; +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where +# Must have selectivity_from_histogram <= 1.0: +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel +from information_schema.optimizer_trace; +sel +[ + [ + { + "column_name": "a", + "ranges": + ["91303 <= a <= 91303"], + "selectivity_from_histogram": 0.0357 + } + ] +] +set optimizer_trace=@tmp; +drop table t0,t1,t10; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; +# +# End of 10.4 tests +# +# +# Clean up +# +set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity_notembedded.test b/mysql-test/main/selectivity_notembedded.test new file mode 100644 index 00000000000..6752bd3c7e1 --- /dev/null +++ b/mysql-test/main/selectivity_notembedded.test @@ -0,0 +1,121 @@ +--source include/no_valgrind_without_big.inc +--source include/have_stat_tables.inc +--source include/have_sequence.inc +--source include/default_charset.inc +--source include/not_embedded.inc + +--disable_warnings +drop table if exists t0,t1,t2,t3; +--enable_warnings + +select @@global.use_stat_tables; +select @@session.use_stat_tables; + +set @save_use_stat_tables=@@use_stat_tables; +set use_stat_tables='preferably'; + +--source include/default_optimizer_switch.inc +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set join_cache_level=2; +set @@global.histogram_size=0,@@local.histogram_size=0; +set histogram_type='single_prec_hb'; + +# check that statistics on nulls is used + +set optimizer_use_condition_selectivity=3; + +--echo # +--echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +--echo # +create table t0(a int); # This holds how many rows we hold in a bucket. +insert into t0 select 1 from seq_1_to_78; + +create table t1(a int); # one-third of a bucket +insert into t1 select 1 from seq_1_to_26; + +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; + +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; + + +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; + + +insert into t10 select 34785 from t0, seq_1_to_8; + +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; + +insert into t10 select 65216 from t0, seq_1_to_52; + +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; + +insert into t10 select 65219 from t0; + + +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; + +insert into t10 select 73915 from t0, seq_1_to_40; + + +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; + +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; + +insert into t10 select 91302 from t0, seq_1_to_6; + +insert into t10 select 91303 from t1; # Only 1/3rd of bucket matches the search tuple +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; + +insert into t10 select 91305 from t0, seq_1_to_8; + +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; + +set use_stat_tables=preferably; +analyze table t10 persistent for all; +flush tables; + +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); + +--echo # Must have selectivity_from_histogram <= 1.0: +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel +from information_schema.optimizer_trace; + +set optimizer_trace=@tmp; +drop table t0,t1,t10; + +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; + + +--echo # +--echo # End of 10.4 tests +--echo # + +--echo # +--echo # Clean up +--echo # +--source include/restore_charset.inc +set @@global.histogram_size=@save_histogram_size; |