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

Incorrect results with deeply nested outer joined inline view.

    XMLWordPrintableJSON

Details

    Description

      Readable query to showcase the problem:

      SELECT t1.id, v3.id, v3.int_col, v3.bigint_col
      FROM functional.alltypestiny t1
      LEFT OUTER JOIN
        (SELECT id, int_col, COALESCE(bigint_col, 4000) AS bigint_col
         FROM
          (SELECT id, COALESCE(int_col, 3000) AS int_col, bigint_col
           FROM
             (SELECT COALESCE(id + 100, 2000) AS id, int_col, bigint_col
              FROM functional.alltypestiny t2
             ) v1
           ) v2
         ) v3
      ON t1.id = v3.id
      

      The query above used to not return NULLs on the nullable side of the outer join although no rows from t1 match with those from v3 based on the join condition.

      Original query produced by the query generator:

      WITH with_1 AS (SELECT
      COALESCE(a2.int_col, a2.int_col, a2.int_col) AS int_col
      FROM (
      SELECT
      IF(LAG(True, 21) OVER (ORDER BY a1.double_col ASC, a1.string_col), LAG(-680) OVER (ORDER BY a1.string_col ASC, a1.double_col), -793) AS int_col
      FROM alltypestiny a1
      ) a2)
      SELECT
      a4.int_col, a3.month
      FROM alltypes a3
      LEFT JOIN with_1 a4 ON ((a4.int_col) = (a3.month))
      

      First few lines of the result:

      +---------+-------+
      | int_col | month |
      +---------+-------+
      | -793    | 8     |
      | -793    | 8     |
      | -793    | 8     |
      | -793    | 8     |
      | -793    | 8     |
      | -793    | 8     |
      | -793    | 8     |
      | -793    | 8     |
      | -793    | 8     |
      | -793    | 8     |
      +---------+-------+
      

      Since we are joining on ((a4.int_col) = (a3.month)), both columns in the result set should be the same (or the right column can be NULL).

      Attachments

        Activity

          People

            alex.behm Alexander Behm
            tarasbob Taras Bobrovytsky
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: