[IMPALA-3321] Introduce an option for throwing error instead of returning null when CAST fails

• Type: New Feature
• Status: Resolved
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: Impala 2.2.4
• Component/s: Backend
• Labels: impala
• Epic Link: Numeric Datatype Conversions
• Assignee: Unassigned
• Reporter: Adrian Kalaszi
• Created: 08/Apr/16 15:21
• Updated: 21/Mar/17 00:40
• Resolved: 21/Mar/17 00:40

Description

As per the documentation of the cast operator here:
http://www.cloudera.com/documentation/enterprise/latest/topics/impala_conversion_functions.html
http://www.cloudera.com/documentation/archive/impala/2-x/2-1-x/topics/impala_timestamp.html

"If the expression value is of a type that cannot be converted to the target type, the result is NULL."

During casting a string to a specific type, there should be an option to either ignore insert errors and/or log them someplace or throw an error if something went wrong.

Similarly as Oracle does it:
SELECT CAST( '03/24/2016' AS TIMESTAMP ) FROM DUAL;

Error returned
ORA-01843: not a valid month
01843. 00000 - "not a valid month"
*Cause:
*Action:

create table xyz
(
column_name timestamp not null
);

insert into xyz values ( '03/24/2016' );

Error Returned
Error starting at line : 6 in command -
insert into xyz values ( '03/24/2016' )
Error report -
SQL Error: ORA-01843: not a valid month
01843. 00000 - "not a valid month"
*Cause:
*Action:

In the same time Impala behaves as below:
> select cast( '2015-13-28 03:30:10' as timestamp) ;
Query: select cast( '2015-13-28 03:30:10' as timestamp)
NULL

Issue Links

• duplicates: IMPALA-3216 Provide an option to fail a query if casting fails