INSERT into a Datalake table which is partitioned on an expression results in an unexpected error

When inserting into a Datalake table which is partitioned on an expression, an error will occur if the data that is inserted would cause the expression used for the partitioning key to be invalid.

Symptoms

For example, the following scenario will result in an error:

CREATE datalake table t1  (c1 varchar(20), c2 decimal(10,5)) partitioned by (trunc(10,c1) as mypart) TBLPROPERTIES ('external.table.purge'='true') location 'db2remote://odfdefault//default/t1'

DB20000I  The SQL command completed successfully.

insert into t1 (c1,c2) values ('abc',10)

DB21034E  The command was processed as an SQL statement because it was not a 

valid Command Line Processor command.  During SQL processing it returned:

SQL0420N  Invalid character found in a character string argument of the 

function "INTEGER".  SQLSTATE=22018

Causes

When a Datalake table is defined to be partitioned on an expression, the expression is evaluated for each insert row, and if the expression is invalid, an error will be returned. In the above example, an error is returned because the TRUNC function is expecting an numeric argument for c1, and ‘abc’ was used as input.

Resolving the problem

Change the data for the insert so that the expression can be successfully processed. Foe example:

insert into t1 (c1,c2) values ('1234567890',10)

DB20000I  The SQL command completed successfully