[DRILL-1606] Issue with casting to integer from a CSV file

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Fix Version/s: 0.7.0
• Component/s: Execution - Data Types
• Assignee: Unassigned
• Reporter: Norris Lee
• Created: 29/Oct/14 23:39
• Updated: 09/Dec/14 17:47
• Resolved: 29/Oct/14 23:40

Description

I have a CSV file with this data:

0: jdbc:drill:zk=localhost:5181> select columns[0], columns[1] from `dfs.optdrill`.`touchstone/Integer_Table.csv`;
+------------+------------+
|   EXPR$0   |   EXPR$1   |
+------------+------------+
| Zero       | 0          |
| One        | 1          |
| MinusOne   | -1         |
| Two        | 2          |
| MaxTInt    | 127        |
| MinTInt    | -128       |
| MaxUTInt   | 255        |
| MaxTIntP1  | 128        |
| MinTIntM1  | -129       |
| MaxUTIntP1 | 256        |
| MaxSInt    | 32767      |
| MinSInt    | -32768     |
| MaxUSInt   | 65535      |
| MinSIntM1  | -32769     |
| MaxSIntP1  | 32768      |
| MaxUSIntP1 | 65536      |
| MaxInt     | 2147483647 |
| MinInt     | -2147483648 |
+------------+------------+
18 rows selected (0.099 seconds)

When I try to cast it to varchar and integer, it fails:

0: jdbc:drill:zk=localhost:5181> select cast(columns[0] as varchar(20)), cast(columns[1] as integer) from `dfs.optdrill`.`touchstone/Integer_Table.csv`;
+------------+------------+
|   EXPR$0   |   EXPR$1   |
+------------+------------+
Query failed: Failure while running fragment.

java.lang.RuntimeException: java.sql.SQLException: Failure while executing query.
        at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2514)
        at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2148)
        at sqlline.SqlLine.print(SqlLine.java:1809)
        at sqlline.SqlLine$Commands.execute(SqlLine.java:3766)
        at sqlline.SqlLine$Commands.sql(SqlLine.java:3663)
        at sqlline.SqlLine.dispatch(SqlLine.java:889)
        at sqlline.SqlLine.begin(SqlLine.java:763)
        at sqlline.SqlLine.start(SqlLine.java:498)
        at sqlline.SqlLine.main(SqlLine.java:460)

Casting to varchar and varchar or varchar and bigint works perfectly fine.
Using the same data set in a parquet file works fine when casting to varchar and integer as well.

0: jdbc:drill:zk=localhost:5181> select cast(keycolumn as varchar(20)), cast(column1 as integer) from `dfs.optdrill`.`touchstone/integer.parquet`;
+------------+------------+
|   EXPR$0   |   EXPR$1   |
+------------+------------+
| Zero       | 0          |
| One        | 1          |
| MinusOne   | -1         |
| Two        | 2          |
| MaxTInt    | 127        |
| MinTInt    | -128       |
| MaxUTInt   | 255        |
| MaxTIntP1  | 128        |
| MinTIntM1  | -129       |
| MaxUTIntP1 | 256        |
| MaxSInt    | 32767      |
| MinSInt    | -32768     |
| MaxUSInt   | 65535      |
| MinSIntM1  | -32769     |
| MaxSIntP1  | 32768      |
| MaxUSIntP1 | 65536      |
| MaxInt     | 2147483647 |
| MinInt     | -2147483648 |
+------------+------------+
18 rows selected (0.067 seconds)

Issue Links

• is duplicated by: DRILL-1581 tpch100 text queries are failing - regression