You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@impala.apache.org by "Mala Chikka Kempanna (JIRA)" <ji...@apache.org> on 2017/06/19 17:07:00 UTC
[jira] [Created] (IMPALA-5531) A sub-query referencing column name
of table from outside the subquery yields wrong results
Mala Chikka Kempanna created IMPALA-5531:
--------------------------------------------
Summary: A sub-query referencing column name of table from outside the subquery yields wrong results
Key: IMPALA-5531
URL: https://issues.apache.org/jira/browse/IMPALA-5531
Project: IMPALA
Issue Type: Bug
Affects Versions: Impala 2.8.0, Impala 2.7.0, Impala 2.9.0
Reporter: Mala Chikka Kempanna
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:
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
)
Repro steps:
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 |
+--------------+---------------------+---------------+--------+---------------+---------------------+--------------+
Workaround:
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_pfl AS pfl2, da_cer AS cer2 WHERE pfl2.currency_code = cer2.currency_code
AND cer2.trade_date <= pfl2.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-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 |
+--------------+---------------------+---------------+--------+---------------+---------------------+--------------+
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)