[IMPALA-5531] Scalar subquery with correlated inequality predicate returns wrong results

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: Impala 2.7.0, Impala 2.8.0, Impala 2.9.0
• Fix Version/s: Impala 2.10.0
• Component/s: Frontend
• Labels: correctness ramp-up sql-language
• Epic Color: ghx-label-3
• Assignee: Dimitris Tsirogiannis
• Reporter: Mala Chikka Kempanna
• Created: 19/Jun/17 17:06
• Updated: 24/Aug/17 16:35
• Resolved: 24/Aug/17 13:58

Description

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       |
+--------------+---------------------+---------------+--------+---------------+---------------------+--------------+