[jira] [Created] (DRILL-3895) Can not join on int96 column coming from two different sources: hive and impala

["Victoria Markman (JIRA)" <ji...@apache.org>]

Victoria Markman created DRILL-3895:
---------------------------------------

             Summary: Can not join on int96 column coming from two different sources: hive and impala
                 Key: DRILL-3895
                 URL: https://issues.apache.org/jira/browse/DRILL-3895
             Project: Apache Drill
          Issue Type: Bug
          Components: Execution - Data Types
    Affects Versions: 1.2.0
            Reporter: Victoria Markman


I would think that cross source join on a column of int96 type should work in drill, since we convert it to varbinary internally. It is very possible that I'm hitting a bug related to varbinary data type ...

{code}
Hive generated parquet: dfs.`test/type_test`

[Mon Oct 05 09:37:37] # ~/parquet-tools/parquet-schema 000000_0
message hive_schema {
  optional int32 num;
  optional binary word (UTF8);
  optional int96 dtg;
  optional double dollar;
}

Implala generated parquet: dfs.`drill/testdata/subqueries/imp_t1`
[Mon Oct 05 09:38:40 ] # ~/parquet-tools/parquet-schema 243293260064ba0-808af32a4ab4e487_393209663_data.0.parq
message schema {
  optional binary c_varchar (UTF8);
  optional int32 c_integer;
  optional int64 c_bigint;
  optional float c_float;
  optional double c_double;
  optional binary c_date (UTF8);
  optional binary c_time (UTF8);
  optional int96 c_timestamp;
  optional boolean c_boolean;
  optional double d9;
  optional double d18;
  optional double d28;
  optional double d38;
}

0: jdbc:drill:schema=dfs> select count(*) from `test/type_test`;
+---------+
| EXPR$0  |
+---------+
| 2       |
+---------+
1 row selected (0.3 seconds)


0: jdbc:drill:schema=dfs> select count(*) from `drill/testdata/subqueries/imp_t1`;
+---------+
| EXPR$0  |
+---------+
| 10000   |
+---------+
1 row selected (0.259 seconds)
{code}

*Join 'AS IS' on int96 column results in an error*

-- IN clause
{code}
0: jdbc:drill:schema=dfs> select count(*) from `drill/testdata/subqueries/imp_t1` where c_timestamp IN ( select c_timestamp from `test/type_test`);
Error: SYSTEM ERROR: DrillRuntimeException: Join only supports implicit casts between 1. Numeric data
 2. Varchar, Varbinary data 3. Date, Timestamp data Left type: VARBINARY, Right type: INT. Add explicit casts to avoid this error
Fragment 0:0
[Error Id: 8bce65e1-0e8f-45fe-9990-eb980aeae53e on atsqa4-133.qa.lab:31010] (state=,code=0)
{code}

-- NOT IN clause
{code}
0: jdbc:drill:schema=dfs> select count(*) from `drill/testdata/subqueries/imp_t1` where c_timestamp NOT IN ( select c_timestamp from `test/type_test`);
Error: SYSTEM ERROR: DrillRuntimeException: Join only supports implicit casts between 1. Numeric data
 2. Varchar, Varbinary data 3. Date, Timestamp data Left type: VARBINARY, Right type: INT. Add explicit casts to avoid this error
Fragment 0:0
[Error Id: 4307937f-fbc0-40c7-b2d4-8e4835e79ae8 on atsqa4-133.qa.lab:31010] (state=,code=0)
{code}

-- JOIN
{code}
0: jdbc:drill:schema=dfs> select count(*) from dfs.`test/type_test` a, dfs.`drill/testdata/subqueries/imp_t1` b where a.c_timestamp = b.c_timestamp;
Error: SYSTEM ERROR: DrillRuntimeException: Join only supports implicit casts between 1. Numeric data
 2. Varchar, Varbinary data 3. Date, Timestamp data Left type: VARBINARY, Right type: INT. Add explicit casts to avoid this error
Fragment 0:0
[Error Id: e80225a3-eeb6-4c5b-bda1-a1f0d13d7edf on atsqa4-133.qa.lab:31010] (state=,code=0)
{code}

*Attempt to explicitly cast to varbinary type*(one of these queries should have returned non zero row count)

{code}
0: jdbc:drill:schema=dfs> select count(*) from dfs.`drill/testdata/subqueries/imp_t1` where cast(c_timestamp as varbinary(10)) NOT IN ( select cast(c_timestamp as varbinary(10)) from dfs.`test/type_test`);
+---------+
| EXPR$0  |
+---------+
| 0       |
+---------+
1 row selected (0.422 seconds)

0: jdbc:drill:schema=dfs> select count(*) from dfs.`drill/testdata/subqueries/imp_t1` where cast(c_timestamp as varbinary(10)) IN ( select cast(c_timestamp as varbinary(10)) from dfs.`test/type_test`);
+---------+
| EXPR$0  |
+---------+
| 0       |
+---------+
{code}

*Use CONVERT_FROM function* (one of these queries should have returned non zero row count)
{code}
0: jdbc:drill:schema=dfs> select count(*) from dfs.`drill/testdata/subqueries/imp_t1` where CONVERT_FROM(c_timestamp, 'TIMESTAMP_IMPALA') IN ( select CONVERT_FROM(c_timestamp, 'TIMESTAMP_IMPALA') from dfs.`test/type_test`);
+---------+
| EXPR$0  |
+---------+
| 0       |
+---------+
1 row selected (0.683 seconds)

0: jdbc:drill:schema=dfs> select count(*) from dfs.`drill/testdata/subqueries/imp_t1` where CONVERT_FROM(c_timestamp, 'TIMESTAMP_IMPALA') NOT IN ( select CONVERT_FROM(c_timestamp, 'TIMESTAMP_IMPALA') from dfs.`test/type_test`);
+---------+
| EXPR$0  |
+---------+
| 0       |
+---------+
1 row selected (0.858 seconds)
{code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)