[IMPALA-10382] Predicate with coalesce on both sides of LOJ isn't NULL filtering

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: Impala 4.0.0
• Component/s: Frontend
• Labels: correctness
• Target Version: Impala 4.0.0
• Epic Color: ghx-label-2
• Assignee: Xianqing He
• Reporter: Shant Hovsepian
• Created: 07/Dec/20 22:30
• Updated: 12/Apr/21 00:53
• Resolved: 12/Apr/21 00:53

Description

A query like the below will have the outer join simplified to an inner join when the predicate with coalesce isn't always NULL filtering.

select t1.int_col from alltypestiny t1 left outer join alltypesagg t2 on t1.tinyint_col = t2.tinyint_col left outer join alltypes t3 on t1.int_col = t3.int_col where t2.tinyint_col >= coalesce(t1.int_col, t2.int_col);

functional> set ENABLE_OUTER_JOIN_TO_INNER_TRANSFORMATION=true;
functional> explain select t1.int_col from alltypestiny t1 left outer join alltypesagg t2 on t1.tinyint_col = t2.tinyint_col left outer join alltypes t3 on t1.int_col = t3.int_col where t2.tinyint_col >= coalesce(t1.int_col, t2.int_col);
Query: explain select t1.int_col from alltypestiny t1 left outer join alltypesagg t2 on t1.tinyint_col = t2.tinyint_col left outer join alltypes t3 on t1.int_col = t3.int_col where t2.tinyint_col >= coalesce(t1.int_col, t2.int_col)
+------------------------------------------------------------+
| Explain String                                             |
+------------------------------------------------------------+
| Max Per-Host Resource Reservation: Memory=5.04MB Threads=8 |
| Per-Host Resource Estimates: Memory=287MB                  |
|                                                            |
| PLAN-ROOT SINK                                             |
| 08:EXCHANGE [UNPARTITIONED]                                |
| 04:HASH JOIN [LEFT OUTER JOIN, PARTITIONED]                |
| |--07:EXCHANGE [HASH(t3.int_col)]                          |
| |  02:SCAN HDFS [functional.alltypes t3]                   |
| 06:EXCHANGE [HASH(t1.int_col)]                             |
| 03:HASH JOIN [INNER JOIN, BROADCAST]                       |
| |--05:EXCHANGE [BROADCAST]                                 |
| |  00:SCAN HDFS [functional.alltypestiny t1]               |
| 01:SCAN HDFS [functional.alltypesagg t2]                   |
+------------------------------------------------------------+
Fetched 13 row(s) in 0.02s

Issue Links

• is related to: IMPALA-4373 Wrong results with correlated WHERE-clause subquery inside a NULL-checking conditional function.
• is related to: IMPALA-5022 Outer join simplification
• relates to: IMPALA-10252 Query returns less number of rows with run-time filtering on integer column in a subquery against functional_parquet schema