[IMPALA-1118] Incorrect plan after reordering predicates (inner join following outer join)

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: Impala 2.0
• Fix Version/s: Impala 2.0
• Labels: correctness
• Assignee: Alexander Behm
• Reporter: casey
• Created: 28/Jul/14 21:00
• Updated: 14/May/15 22:33
• Resolved: 24/Sep/14 15:49

Description

The plan for the query below has the join conditions refactored but the result is not correct. Alex looked at this and thinks its a new bug.

The query should return 56 rows, but it returns 40,733.

SELECT
1 AS int_col_1
FROM table_3 AS t4
LEFT JOIN table_4 AS t5 ON t5.smallint_col_8 = t4.smallint_col_3
INNER JOIN table_9 AS t6 ON t6.int_col_5 = t4.smallint_col_3 AND t6.int_col_5 = t5.bigint_col_3

[localhost:21000] > explain SELECT 1 AS int_col_1 FROM table_3 AS t4 LEFT JOIN table_4 AS t5 ON t5.smallint_col_8 = t4.smallint_col_3 INNER JOIN table_9 AS t6 ON t6.int_col_5 = t4.smallint_col_3 AND t6.int_col_5 = t5.bigint_col_3;
Query: explain SELECT 1 AS int_col_1 FROM table_3 AS t4 LEFT JOIN table_4 AS t5 ON t5.smallint_col_8 = t4.smallint_col_3 INNER JOIN table_9 AS t6 ON t6.int_col_5 = t4.smallint_col_3 AND t6.int_col_5 = t5.bigint_col_3
+------------------------------------------------------------------------------------+
| Explain String                                                                     |
+------------------------------------------------------------------------------------+
| Estimated Per-Host Requirements: Memory=4.75GB VCores=3                            |
| WARNING: The following tables are missing relevant table and/or column statistics. |
| randomness.table_3, randomness.table_4, randomness.table_9                         |
|                                                                                    |
| 07:EXCHANGE [UNPARTITIONED]                                                        |
| |                                                                                  |
| 04:HASH JOIN [INNER JOIN, BROADCAST]                                               |
| |  hash predicates: t4.smallint_col_3 = t6.int_col_5                               |
| |                                                                                  |
| |--06:EXCHANGE [BROADCAST]                                                         |
| |  |                                                                               |
| |  02:SCAN HDFS [randomness.table_9 t6]                                            |
| |     partitions=1/1 size=355.38KB                                                 |
| |                                                                                  |
| 03:HASH JOIN [LEFT OUTER JOIN, BROADCAST]                                          |
| |  hash predicates: t4.smallint_col_3 = t5.smallint_col_8                          |
| |                                                                                  |
| |--05:EXCHANGE [BROADCAST]                                                         |
| |  |                                                                               |
| |  01:SCAN HDFS [randomness.table_4 t5]                                            |
| |     partitions=1/1 size=1.49MB                                                   |
| |     predicates: t5.smallint_col_8 = t5.bigint_col_3                              |
| |                                                                                  |
| 00:SCAN HDFS [randomness.table_3 t4]                                               |
|    partitions=1/1 size=998.90KB                                                    |
+------------------------------------------------------------------------------------+
Returned 25 row(s) in 0.05s