[DRILL-7391] Wrong result when doing left outer join on CSV table

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.16.0
• Fix Version/s: 1.17.0
• Component/s: Query Planning & Optimization
• Labels: ready-to-commit
• Assignee: Vova Vysotskyi
• Reporter: Aman Sinha
• Reviewer: Igor Guzenko
• Created: 30/Sep/19 22:15
• Updated: 05/Nov/19 08:56
• Resolved: 05/Nov/19 08:56

Description

The following query shows 1 row that is incorrect. For the non-null rows, both columns should have the same value. This is on CSV sample data (I will attach the files).

apache drill (dfs.tmp)> select tt5.columns[0], tt6.columns[0] from tt5 left outer join tt6  on tt5.columns[0] = tt6.columns[0];
+--------+--------+
| EXPR$0 | EXPR$1 |
+--------+--------+
| 455    | null   |
| 455    | null   |
| 555    | null   |
| 1414   | 1414   |
| 455    | null   |
| 580    | null   |
| 1111   | null   |
| 555    | null   |
| 455    | null   |
| 455    | null   |
| 455    | null   |
| 455    | null   |
| 455    | null   |
| 555    | null   |
| 455    | null   |
| 455    | null   |
| 455    | null   |
| 580    | null   |
| 6767   | null   |
| 455    | null   |
| 555    | null   |
| 455    | null   |
| 555    | null   |
| 555    | null   |
| 555    | null   |
| 455    | null   |
| 555    | null   |
| 455    | null   |
| 455    | null   |
| 455    | null   |
| 6767   | null   |
| 555    | null   |
| 555    | null   |
| 455    | null   |
| 555    | null   |
| 555    | null   |
| 1414   | 1414   |
| 455    | null   |
| 555    | null   |
| 555    | null   |
| 455    | null   |
| 455    | null   |
| 555    | null   |
| 455    | null   |
| 555    | null   |
| 555    | null   |
| 455    | null   |
| 455    | null   |
| 9669   | 1414   |          <--- Wrong result
| 555    | null   |
| 455    | null   |
| 455    | null   |
| 455    | null   |
| 555    | null   |
| 580    | null   |
| 455    | null   |
| 555    | null   |
| 455    | null   |
| 555    | null   |
| 455    | null   |
| 455    | null   |
| 409    | null   |
| 455    | null   |
| 555    | null   |
| 555    | null   |
| 455    | null   |
| 455    | null   |
| 555    | null   |
| 455    | null   |
| 555    | null   |
| 1414   | 1414   |
| 455    | null   |
| 555    | null   |
| 555    | null   |
| 555    | null   |
+--------+--------+
75 rows selected 

Issue Links

• depends upon: CALCITE-3390 ITEM expression does not get pushed to the right input of left-outer-join
• links to: GitHub Pull Request #1890