[
https://issues.apache.org/jira/browse/DRILL-1951?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14268027#comment-14268027
]
Victoria Markman commented on DRILL-1951:
-----------------------------------------
It does work indeed:
{code}
0: jdbc:drill:schema=dfs> select cast(cast(columns[3] as varchar(10)) as
decimal(12,4)) from `sales.csv`;
+------------+
| EXPR$0 |
+------------+
| 3000.1200 |
| 4900.0000 |
| 5000.0000 |
| 0.0000 |
+------------+
4 rows selected (0.116 seconds)
{code}
However, if you reminded me in the error message, that I'm converting varchar
to int, I would have come up with workaround myself and would not have called
"technical support" :)
And yes, I get the same weird error, if json data type is a string:
{code}
0: jdbc:drill:schema=dfs> select cast(a1 as int) from `t1.json`;
Query failed: Query failed: Failure while running fragment., 10.01 [
6005d13d-4f57-4934-a7d5-2d7617c7e45c on atsqa4-134.qa.lab:31010 ]
[ 6005d13d-4f57-4934-a7d5-2d7617c7e45c on atsqa4-134.qa.lab:31010 ]
Error: exception while executing query: Failure while executing query.
(state=,code=0)
{code}
Postgres, for the future reference:
{code}
postgres=# select cast(c2 as bigint) from t2;
ERROR: invalid input syntax for integer: "10.02"
{code}
> 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)
