Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-5531

Scalar subquery with correlated inequality predicate returns wrong results

    Details

    • Epic Color:
      ghx-label-3

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

        Attachments

          Activity

            People

            • Assignee:
              dtsirogiannis Dimitris Tsirogiannis
              Reporter:
              mkempanna Mala Chikka Kempanna
            • Votes:
              0 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: