Let's see how histograms work for CHAR
columns
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
|
create table t1(
|
c_mktsegment char(10)
|
);
|
|
|
insert into t1 select 'AUTOMOBILE' from ten;
|
insert into t1 select 'FURNITURE' from ten;
|
insert into t1 select 'HOUSEHOLD' from ten;
|
insert into t1 select 'MACHINERY' from ten;
|
|
|
set histogram_size=20;
|
set use_stat_tables='preferably';
|
set optimizer_use_condition_selectivity=4;
|
|
|
analyze table t1 persistent for all;
|
Now, put a breakpoint in Field::pos_in_interval_val_str and run:
explain extended select count(*) from t1 where c_mktsegment ='ABCD';
|
Let's see
(gdb) print mp
|
$334 = 4702394920804032544
|
(gdb) print minp
|
$335 = 4707761685061911113
|
(gdb) print maxp
|
$336 = 5566804592153216338
|
(gdb) print mp_prefix
|
$337 = " DCBA"
|
(gdb) print minp_prefix
|
$338 = "IBOMOTUA"
|
(gdb) print maxp_prefix
|
$339 = "RENIHCAM"
|
(gdb) p maxp > minp
|
$340 = true
|
(gdb) p mp < minp
|
$342 = true
|
So, mp < minp < maxp . This is expected. Now , run this line
n= mp - minp;
|
and
(gdb) print n
|
$343 = 1.8441377309451674e+19
|
(gdb) p ((double) mp) - ((double) minp)
|
$344 = -5366764257878016
|
We use unsigned substraction, and end up with the wrong value_position number. The return value of Field::pos_in_interval_val_str() is 1 although it should have been 0.