Thomas Rebele created HIVE-29364:
------------------------------------
Summary: Use histogram statistics for range predicates with a CAST
Key: HIVE-29364
URL: https://issues.apache.org/jira/browse/HIVE-29364
Project: Hive
Issue Type: Improvement
Reporter: Thomas Rebele
Consider the following queries executed within the context of
[{{stats_histogram.q}}|https://github.com/apache/hive/blob/fe70ee9efcdf06a5580d51bb2be9698289e791ce/ql/src/test/queries/clientpositive/stats_histogram.q].
The histogram statistics are used for the predicates without a CAST:
{code:java}
EXPLAIN SELECT COUNT(*) FROM test_stats WHERE b < 6;
SELECT COUNT(*) FROM test_stats WHERE b < 6;
EXPLAIN SELECT COUNT(*) FROM test_stats WHERE cast(b as BIGINT) < 6;
SELECT COUNT(*) FROM test_stats WHERE cast(b as BIGINT) < 6;{code}
The rowcount estimate for the filter {{b<6}} is 11, for the filter {{cast(b as
BIGINT)}} is 5; the {{COUNT(*)}} result is 11 for both.
(I didn't check {{{}b BETWEEN ...{}}}, though I think the same issue occurs
there as well)
{code:java}
EXPLAIN SELECT COUNT(*) FROM test_stats WHERE g >= "2020-11-7";
SELECT COUNT(*) FROM test_stats WHERE g >= "2020-11-7";
EXPLAIN SELECT COUNT(*) FROM test_stats WHERE cast(g as timestamp) >=
"2020-11-7 00:00:00";
SELECT COUNT(*) FROM test_stats WHERE cast(g as timestamp) >= "2020-11-7
00:00:00";
{code}
The rowcount estimate for the filter {{g >= "2020-11-7"}} is 1, for the filter
{{cast(g as timestamp) >= "2020-11-7 00:00:00"}} is 5; the {{COUNT(*)}} result
is 1 for both.
{code:java}
EXPLAIN SELECT COUNT(*) FROM test_stats WHERE g BETWEEN "2020-11-01" AND
"2020-11-06";
SELECT COUNT(*) FROM test_stats WHERE g BETWEEN "2020-11-01" AND "2020-11-06";
EXPLAIN SELECT COUNT(*) FROM test_stats WHERE cast(g as timestamp) BETWEEN
"2020-11-01 00:00:00" AND "2020-11-06 00:00:00";
SELECT COUNT(*) FROM test_stats WHERE cast(g as timestamp) BETWEEN "2020-11-01
00:00:00" AND "2020-11-06 00:00:00"; {code}
The rowcount estimate for the filter {{g BETWEEN "2020-11-01" AND
"2020-11-06"}} is 11, for the filter {{cast(g as timestamp) BETWEEN "2020-11-01
00:00:00" AND "2020-11-06 00:00:00"}} is 1; the {{COUNT(*)}} result is 12 for
both.
----
Some pathological cases to be avoided: if the CAST throws away information, the
histogram statistics should not be used. E.g., {{{}CAST(128 as TINYINT) =
-128{}}}, so a {{CAST(intField as TINYINT) > 42}} should not be treated the
same as {{{}intField > 42{}}}, if the intField is outside of the range of
TINYINT.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
