--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;