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

["Khurram Faraaz (JIRA)" <ji...@apache.org>]

Khurram Faraaz created DRILL-5345:
-------------------------------------

             Summary: USING(<column-name>) should return error, when column in not present in the right table
                 Key: DRILL-5345
                 URL: https://issues.apache.org/jira/browse/DRILL-5345
             Project: Apache Drill
          Issue Type: Bug
          Components: Query Planning & Optimization
    Affects Versions: 1.10.0
            Reporter: Khurram Faraaz


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.

{noformat}
<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>
{noformat}

Postgres 9.3 reports an error for such a scenario, because column c1 is not present in the other table in the join.
{noformat}
postgres=# select * from tbl_l JOIN tbl_r USING (c1);
ERROR:  column "c1" specified in USING clause does not exist in right table
{noformat}

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.

{noformat}
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)
{noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)