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

Scalar subquery with correlated inequality predicate returns wrong results

    XMLWordPrintableJSON

Details

    • 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

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

            Dates

              Created:
              Updated:
              Resolved: