[
https://issues.apache.org/jira/browse/IMPALA-5531?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dimitris Tsirogiannis resolved IMPALA-5531.
-------------------------------------------
Resolution: Fixed
Change-Id: I6ca7b60ef0543430d2f5a802285254ebb52db2ab
Reviewed-on: http://gerrit.cloudera.org:8080/7706
Reviewed-by: Dimitris Tsirogiannis <[email protected]>
Tested-by: Impala Public Jenkins
---
M fe/src/main/java/org/apache/impala/analysis/Expr.java
M fe/src/main/java/org/apache/impala/analysis/StmtRewriter.java
M fe/src/test/java/org/apache/impala/analysis/AnalyzeSubqueriesTest.java
3 files changed, 115 insertions(+), 24 deletions(-)
Approvals:
Impala Public Jenkins: Verified
Dimitris Tsirogiannis: Looks good to me, approved
> Scalar subquery with correlated inequality predicate returns wrong results
> --------------------------------------------------------------------------
>
> Key: IMPALA-5531
> URL: https://issues.apache.org/jira/browse/IMPALA-5531
> Project: IMPALA
> Issue Type: Bug
> Components: Frontend
> Affects Versions: Impala 2.7.0, Impala 2.8.0, Impala 2.9.0
> Reporter: Mala Chikka Kempanna
> Assignee: Dimitris Tsirogiannis
> Priority: Critical
> Labels: correctness, ramp-up, sql-language
>
> Why does impala not yield right result set when referencing pfl.currency_code
> or pfl.as_of_date in subquery like below
> Query producing wrong results:
> {code}
> select *
> FROM da_pfl AS pfl
> JOIN da_cer AS cer ON pfl.currency_code = cer.currency_code
> WHERE cer.trade_date = (
> SELECT MAX(cer2.trade_date)
> FROM da_cer AS cer2
> WHERE cer2.currency_code = pfl.currency_code
> AND cer2.trade_date <= pfl.as_of_date
> )
> {code}
> Repro steps:
> {code}
> Query: create TABLE da_pfl (
> cif_number VARCHAR(12),
> as_of_date TIMESTAMP,
> currency_code VARCHAR(3),
> amount DECIMAL(10,2)
> )
> Query: insert INTO da_pfl VALUES (
> (CAST('111111111111' AS VARCHAR(12)), CAST('2017-05-17' AS TIMESTAMP),
> CAST('CHF' AS VARCHAR(3)), 100.00),
> (CAST('111111111111' AS VARCHAR(12)), CAST('2017-05-15' AS TIMESTAMP),
> CAST('CHF' AS VARCHAR(3)), 130.00)
> )
> Query: select * FROM da_pfl
> +--------------+---------------------+---------------+--------+
> | cif_number | as_of_date | currency_code | amount |
> +--------------+---------------------+---------------+--------+
> | 111111111111 | 2017-05-17 00:00:00 | CHF | 100.00 |
> | 111111111111 | 2017-05-15 00:00:00 | CHF | 130.00 |
> +--------------+---------------------+---------------+--------+
> Query: create TABLE da_cer (
> currency_code VARCHAR(3),
> trade_date TIMESTAMP,
> exhange_rate DECIMAL(6,4)
> )
> Query: insert INTO da_cer VALUES (
> (CAST('CHF' AS VARCHAR(3)), CAST('2017-05-17' AS TIMESTAMP), 0.9311),
> (CAST('CHF' AS VARCHAR(3)), CAST('2017-05-16' AS TIMESTAMP), 0.9222),
> (CAST('CHF' AS VARCHAR(3)), CAST('2017-05-15' AS TIMESTAMP), 0.9199),
> (CAST('CHF' AS VARCHAR(3)), CAST('2017-05-14' AS TIMESTAMP), 0.9077)
> )
> Query: select * FROM da_cer
> +---------------+---------------------+--------------+
> | currency_code | trade_date | exhange_rate |
> +---------------+---------------------+--------------+
> | CHF | 2017-05-17 00:00:00 | 0.9311 |
> | CHF | 2017-05-16 00:00:00 | 0.9222 |
> | CHF | 2017-05-15 00:00:00 | 0.9199 |
> | CHF | 2017-05-14 00:00:00 | 0.9077 |
> +---------------+---------------------+--------------+
> Query: select *
> FROM da_pfl AS pfl
> JOIN da_cer AS cer ON pfl.currency_code = cer.currency_code
> WHERE cer.trade_date = (
> SELECT MAX(cer2.trade_date)
> FROM da_cer AS cer2
> WHERE cer2.currency_code = pfl.currency_code
> AND cer2.trade_date <= pfl.as_of_date
> )
> +--------------+---------------------+---------------+--------+---------------+---------------------+--------------+
> | cif_number | as_of_date | currency_code | amount | currency_code
> | trade_date | exhange_rate |
> +--------------+---------------------+---------------+--------+---------------+---------------------+--------------+
> | 111111111111 | 2017-05-17 00:00:00 | CHF | 100.00 | CHF
> | 2017-05-14 00:00:00 | 0.9077 |
> | 111111111111 | 2017-05-17 00:00:00 | CHF | 100.00 | CHF
> | 2017-05-15 00:00:00 | 0.9199 |
> | 111111111111 | 2017-05-17 00:00:00 | CHF | 100.00 | CHF
> | 2017-05-16 00:00:00 | 0.9222 |
> | 111111111111 | 2017-05-17 00:00:00 | CHF | 100.00 | CHF
> | 2017-05-17 00:00:00 | 0.9311 |
> | 111111111111 | 2017-05-15 00:00:00 | CHF | 130.00 | CHF
> | 2017-05-14 00:00:00 | 0.9077 |
> | 111111111111 | 2017-05-15 00:00:00 | CHF | 130.00 | CHF
> | 2017-05-15 00:00:00 | 0.9199 |
> +--------------+---------------------+---------------+--------+---------------+---------------------+--------------+
> But my expected results are this
> +--------------+---------------------+---------------+--------+---------------+---------------------+--------------+
> | cif_number | as_of_date | currency_code | amount | currency_code
> | trade_date | exhange_rate |
> +--------------+---------------------+---------------+--------+---------------+---------------------+--------------+
> | 111111111111 | 2017-05-17 00:00:00 | CHF | 100.00 | CHF
> | 2017-05-17 00:00:00 | 0.9311 |
> | 111111111111 | 2017-05-15 00:00:00 | CHF | 130.00 | CHF
> | 2017-05-17 00:00:00 | 0.9311 |
> +--------------+---------------------+---------------+--------+---------------+---------------------+--------------+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
