[jira] [Created] (DRILL-1951) Can't cast numeric value with decimal point read from CSV file into integer data type

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

Victoria Markman created DRILL-1951:
---------------------------------------

             Summary: Can't cast numeric value with decimal point read from CSV file into integer data type
                 Key: DRILL-1951
                 URL: https://issues.apache.org/jira/browse/DRILL-1951
             Project: Apache Drill
          Issue Type: Bug
          Components: Execution - Data Types
    Affects Versions: 0.8.0
            Reporter: Victoria Markman
            Assignee: Daniel Barclay (Drill/MapR)


sales.csv file:

{code}
997,Ford,ME350,3000.00, comment#1
1999,Chevy,Venture,4900.00, comment#2
1999,Chevy,Venture,5000.00, comment#3
1996,Jeep,Cherokee,10000.01, comment#4

0: jdbc:drill:schema=dfs> select cast(columns[3] as decimal(18,2))  from `sales.csv`;
+------------+
|   EXPR$0   |
+------------+
| 3000.00    |
| 4900.00    |
| 5000.00    |
| 10000.01   |
+------------+
4 rows selected (0.093 seconds)
{code}

-- Can cast to decimal
{code}
0: jdbc:drill:schema=dfs> select cast(columns[3] as decimal(18,2))  from `sales.csv`;
+------------+
|   EXPR$0   |
+------------+
| 3000.00    |
| 4900.00    |
| 5000.00    |
| 10000.01   |
+------------+
4 rows selected (0.095 seconds)
{code}

-- Can cast to float
{code}
0: jdbc:drill:schema=dfs> select cast(columns[3] as float)  from `sales.csv`;
+------------+
|   EXPR$0   |
+------------+
| 3000.0     |
| 4900.0     |
| 5000.0     |
| 10000.01   |
+------------+
4 rows selected (0.112 seconds)
{code}-- Can't cast to INT/BIGINT
{code}
0: jdbc:drill:schema=dfs> select cast(columns[3] as bigint)  from `sales.csv`;
Query failed: Query failed: Failure while running fragment., 3000.00 [ 4818451a-c731-48a9-9992-1e81ab1d520d on atsqa4-134.qa.lab:31010 ]
[ 4818451a-c731-48a9-9992-1e81ab1d520d on atsqa4-134.qa.lab:31010 ]
Error: exception while executing query: Failure while executing query. (state=,code=0)
{code}

-- Same works with json/parquet files
{code}
0: jdbc:drill:schema=dfs> select a1  from `t1.json`;
+------------+
|     a1     |
+------------+
| 10.01      |
+------------+
1 row selected (0.077 seconds)

0: jdbc:drill:schema=dfs> select cast(a1 as int)  from `t1.json`;
+------------+
|   EXPR$0   |
+------------+
| 10         |
+------------+

0: jdbc:drill:schema=dfs> select * from test_cast;
+------------+
|     a1     |
+------------+
| 10.0100    |
+------------+
1 row selected (0.06 seconds)

0: jdbc:drill:schema=dfs> select cast(a1 as int) from test_cast;
+------------+
|   EXPR$0   |
+------------+
| 10         |
+------------+
1 row selected (0.094 seconds)
{code}




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