[IMPALA-2584] Qgen: Flat version of deeply nested query may be missing a join condition

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: Impala 2.4.0
• Component/s: Infrastructure
• Labels: query_generator_development
• Assignee: Taras Bobrovytsky
• Reporter: casey
• Created: 22/Oct/15 20:17
• Updated: 04/Feb/16 01:44
• Resolved: 04/Feb/16 01:44

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.