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

Qgen: Flat version of deeply nested query may be missing a join condition

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      The nested query generator finds result mismatches that are actually due to improper translation of a nested impala query into a flat postgres query.

      Ex

      Impala

      SELECT
      t7.char_col AS char_col
      FROM table_0 t1
      INNER JOIN (
      SELECT
      CAST(-915 AS STRING) AS char_col,
      CAST(GREATEST(COALESCE(COALESCE(-457, 337, COALESCE(585, MIN(439))), 474), 167) AS STRING) AS char_col_t5,
      CAST(-817 AS STRING) AS char_col_t6
      FROM t1.field_21.value t2
      INNER JOIN t1.field_21 t3 ON (((((t3.key) = (t2.value)) AND ((t3.key) = (t2.key))) AND ((t3.key) = (t2.key))) AND ((t3.key) = (t2.value))) AND ((t3.key) = (t2.key))
      LEFT JOIN t1.field_21.value t4 ON (((t4.key) = (t3.key)) AND ((t4.key) = (t3.key))) AND ((t4.key) = (t3.key))
      GROUP BY
      CAST(-915 AS STRING),
      CAST(-817 AS STRING)
      ) t7
      WHERE
      (t1.field_3) <= (-578.72)
      

      Postgres

      SELECT
      t7.char_col AS char_col
      FROM table_0 t1
      INNER JOIN LATERAL (
      SELECT
      CAST(-915 AS VARCHAR(1000)) AS char_col,
      CAST(GREATEST(COALESCE(COALESCE(-457, 337, COALESCE(585, MIN(439))), 474), 167) AS VARCHAR(1000)) AS char_col_t5,
      CAST(-817 AS VARCHAR(1000)) AS char_col_t6
      FROM table_0_field_21__values t2
      INNER JOIN  table_0_field_21 t3 ON (((((t3.key) = (t2.value)) AND ((t3.key) = (t2.key))) AND ((t3.key) = (t2.key))) AND ((t3.key) = (t2.value))) AND ((t3.key) = (t2.key))
      LEFT JOIN  table_0_field_21__values t4 ON (((t4.key) = (t3.key)) AND ((t4.key) = (t3.key))) AND ((t4.key) = (t3.key))
      WHERE
      (t1.id) = (t3.table_0_id)
      GROUP BY
      CAST(-915 AS VARCHAR(1000)),
      CAST(-817 AS VARCHAR(1000))
      ) t7 ON True
      WHERE
      (t1.field_3) <= (-578.72)
      

      The important parts of the queries are

      Impala

      SELECT ...
      FROM table_0 t1
      INNER JOIN (
      SELECT ...
      FROM t1.field_21.value t2
      ...
      

      Postgres

      SELECT ...
      FROM table_0 t1
      INNER JOIN LATERAL (
      SELECT ...
      FROM table_0_field_21__values t2
      ...
      

      The problem is the flat version is missing the relation between t2 to t1.

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            tarasbob Taras Bobrovytsky
            caseyc casey
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment