[
https://issues.apache.org/jira/browse/HIVE-29364?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas Rebele reassigned HIVE-29364:
------------------------------------
Assignee: Thomas Rebele
> 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
> Assignee: Thomas Rebele
> Priority: Major
>
> 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)
