[CALCITE-245] Off-by-one translation of ON clause of JOIN

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Labels: github-import
• Assignee: Unassigned
• Reporter: GitHub Import
• Created: 18/Jun/14 08:48
• Updated: 18/Jun/14 08:48
• Resolved: 18/Jun/14 08:48

Description

If joining an expression on the LHS of a join to the first column of the RHS of the join, the join condition is not correctly translated. For instance,

```sql
SELECT * FROM emp JOIN dept on emp.deptno + 0 = dept.deptno
```

becomes

```
JoinRel(condition=[=($9, $9)], joinType=[inner])
```

but should be

```
JoinRel(condition=[=($9, $10)], joinType=[inner])
```

This can occur even if you are apparently just equi-joining columns. If the columns have different types (which might happen if they are from different sources) then even `emp.deptno = dept.deptno` might have an implicit cast.

Problem is in `SqlToRelConverter.pushDownJoinConditions`. A new expression is added to the LHS, and it has the same ID as the expression referencing the RHS.

---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/245
Created by: julianhyde
Labels: bug,
Assignee: julianhyde
Created at: Fri Apr 11 00:21:54 CEST 2014
State: closed