[
https://issues.apache.org/jira/browse/IMPALA-13018?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17843304#comment-17843304
]
Wenzhe Zhou edited comment on IMPALA-13018 at 5/3/24 7:42 PM:
--------------------------------------------------------------
In TPCDS table 'date_dim', column 'd_date' is defined to save date as string,
like "1900-01-0".
In the where clause of query q80a, 'd_date' is used to compare with timestamp
type of value as:
{code:java}
d_date between cast('2000-08-23' as timestamp) and (cast('2000-08-23' as
timestamp) + interval 30 days)
{code}
This cause RDBMS or remote Impala cluster return wrong data.
When changing the where clause of query q80a as following format:
{code:java}
cast(d_date as timestamp) between cast('2000-08-23' as timestamp) and
(cast('2000-08-23' as timestamp) + interval 30 days)
{code}
the query returns expected results. Except q80a, 'd_date' is casted as
timestamp in other queries (q12, q16, q20, q32, q37, q77a, q82, q92, q94, q95,
q98) when comparing with timestamp type of values.
Another way is to cast constants as date as below (like q80).
{code:java}
d_date between cast('2000-08-23' as date) and (cast('2000-08-23' as date) +
interval 30 days)
{code}
It seems Impala can implicitly cast 'd_date' as timestamp string for non JDBC
tables.
was (Author: wzhou):
In TPCDS table 'date_dim', column 'd_date' is defined to save date as string,
like "1900-01-0".
In the where clause of query q80a, 'd_date' is used to compare with timestamp
type of value as:
{code:java}
d_date between cast('2000-08-23' as timestamp) and (cast('2000-08-23' as
timestamp) + interval 30 days)
{code}
This cause RDBMS or remote Impala cluster return wrong data.
When changing the where clause of query q80a as following format:
{code:java}
cast(d_date as timestamp) between cast('2000-08-23' as timestamp) and
(cast('2000-08-23' as timestamp) + interval 30 days)
{code}
the query returns expected results. Except q80a, 'd_date' is casted as
timestamp in other queries (q12, q16, q20, q32, q37, q77a, q82, q92, q94, q95,
q98) when comparing with timestamp type values.
Another way is to cast constants as date as below (q80).
{code:java}
d_date between cast('2000-08-23' as date) and (cast('2000-08-23' as date) +
interval 30 days)
{code}
It seems Impala can implicitly cast 'd_date' as timestamp string for non JDBC
tables.
> Fix
> test_tpcds_queries.py/TestTpcdsQueryForJdbcTables.test_tpcdstpcds-decimal_v2-q80a
> failure
> ---------------------------------------------------------------------------------------------
>
> Key: IMPALA-13018
> URL: https://issues.apache.org/jira/browse/IMPALA-13018
> Project: IMPALA
> Issue Type: Sub-task
> Components: Backend, Frontend
> Reporter: Wenzhe Zhou
> Assignee: Pranav Yogi Lodha
> Priority: Major
>
> The returned rows are not matching expected results for some decimal type of
> columns.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
