[#MDEV-26885] Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE

create or replace table t (a int);

insert into t values

  (17),(6),(14),(3),(24),(6),(23),(15),(24),(21),

  (25),(17),(6),(2),(21),(16),(19),(9),(10),(3);

select * from t where a = 23 or a = 20;

set histogram_type= JSON_HB;

analyze table t persistent for all;

explain format=json select * from t where a = 23 or a = 20;

set histogram_type= DOUBLE_PREC_HB;

analyze table t persistent for all;

explain format=json select * from t where a = 23 or a = 20;

# Cleanup

drop table t;

preview-10.7-MDEV-26519-json-histograms 508f5f3f11e
select * from t where a = 23 or a = 20;
a
23

JSON histogram
"query_block": {
"select_id": 1,
"table": {
"table_name": "t",
"access_type": "ALL",
"rows": 20,
"filtered": 12.14299965,
"attached_condition": "t.a = 23 or t.a = 20"
}
}

DOUBLE_PREC histogram
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t",
"access_type": "ALL",
"rows": 20,
"filtered": 5.329219818,
"attached_condition": "t.a = 23 or t.a = 20"
}
}
}

So, the actual result set contains 1 row out of 20, DOUBLE_PREC gives filtered=5.3 which is almost correct, JSON gives filtered=12.1 which is further off.

Reproducible with MyISAM and InnoDB alike.

[MDEV-26885] Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#2) Created: 2021-10-22 Updated: 2021-10-25 Resolved: 2021-10-22
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	N/A
Fix Version/s:	N/A

[MDEV-26885] Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#2) Created: 2021-10-22 Updated: 2021-10-25 Resolved: 2021-10-22