[DRILL-5345] USING(<column-name>) should return error, when column in not present in the right table

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.10.0
• Component/s: Query Planning & Optimization
• Assignee: Unassigned
• Reporter: Khurram Faraaz
• Created: 10/Mar/17 07:14
• Updated: 10/Mar/17 07:14

Description

When the column used in, USING(column) is not present in the right table of the JOIN, we show report an error to user instead of returning zero rows.

<named columns join> ::=
  USING <left paren> <join column list> <right paren>
<join column list> ::=
  <column name list>
<column name list> ::=
  <column name> [ { <comma> <column name> }... ]
<column name> ::=
  <identifier>

Postgres 9.3 reports an error for such a scenario, because column c1 is not present in the other table in the join.

postgres=# select * from tbl_l JOIN tbl_r USING (c1);
ERROR:  column "c1" specified in USING clause does not exist in right table

Drill 1.10.0 returns zero rows in one case and RuntimeException in the other case. In both cases Drill should return a meaning error message, like the one reported by Postgres.

0: jdbc:drill:schema=dfs.tmp> create table tbl_l as select cast(columns[0] as integer) c1, cast(columns[1] as varchar(25)) c2 from `tbl_l.txt`;
+-----------+----------------------------+
| Fragment  | Number of records written  |
+-----------+----------------------------+
| 0_0       | 10                         |
+-----------+----------------------------+
1 row selected (0.197 seconds)

0: jdbc:drill:schema=dfs.tmp> create table tbl_r as select cast(columns[0] as integer) c3, cast(columns[1] as varchar(25)) c4 from `tbl_r.txt`;
+-----------+----------------------------+
| Fragment  | Number of records written  |
+-----------+----------------------------+
| 0_0       | 10                         |
+-----------+----------------------------+
1 row selected (0.221 seconds)

## this is not right, we should report error for below query.

0: jdbc:drill:schema=dfs.tmp> select * from tbl_l JOIN tbl_r USING (c1);
+-----+-----+-----+-----+
| c1  | c2  | c3  | c4  |
+-----+-----+-----+-----+
+-----+-----+-----+-----+
No rows selected (0.222 seconds)

## we should return a meaningful error message

0: jdbc:drill:schema=dfs.tmp> select * from tbl_l JOIN tbl_r USING (c2);
Error: SYSTEM ERROR: DrillRuntimeException: Join only supports implicit casts between 1. Numeric data
 2. Varchar, Varbinary data 3. Date, Timestamp data Left type: INT, Right type: VARCHAR. Add explicit casts to avoid this error

Fragment 0:0

[Error Id: 56107e6e-54b5-4905-9aa4-b0af2aea4dd9 on centos-01.qa.lab:31010] (state=,code=0)