You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Thomas Rebele (Jira)" <ji...@apache.org> on 2026/02/27 15:55:00 UTC
[jira] [Updated] (HIVE-29364) EXPLAIN should use histogram statistics for range predicates with a CAST
[ https://issues.apache.org/jira/browse/HIVE-29364?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Rebele updated HIVE-29364:
---------------------------------
Description:
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. (_Update:_ this only considers casts from an integer field to an integer type
was:
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.
> EXPLAIN should 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. (_Update:_ this only considers casts from an integer field to an integer type
--
This message was sent by Atlassian Jira
(v8.20.10#820010)