[CALCITE-74] JOIN ... USING fails in 3-way join with UnsupportedOperationException

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

Description

Run the query

```SQL
select *
from "foodmart"."sales_fact_1997" as f
join "foodmart"."time_by_day" as t using ("time_id")
join "foodmart"."product" as p using ("product_id")
join "foodmart"."product_class" as pc using ("product_class_id")
where t."the_month" = 10
```

and you get the error

```
Caused by: java.lang.UnsupportedOperationException: not applicable to a join type
at org.eigenbase.reltype.RelCrossType.getField(RelCrossType.java:72)
at org.eigenbase.sql.validate.SqlValidatorImpl.validateUsingCol(SqlValidatorImpl.java:2954)
at org.eigenbase.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:2837)
```

The cause is that after joining f and t, and while validating "... JOIN p USING (product_id)" the result is a type whose fields cannot be sought by name.

Workaround is to use JOIN ... ON.

---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/74
Created by: julianhyde
Labels:
Created at: Thu Oct 31 01:27:13 CET 2013
State: closed