Nifi 1.0: ExecuteSQL having issues with UNSIGNED int column (org.apache.avro.UnresolvedUnionException)

[#1]

Hi All,

I tried using ExecuteSQL to select rows from my MySQL table:

CREATE TABLE table_name  ( 
domain_id        mediumint(8) UNSIGNED NOT NULL,
....
run_date         timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
)

And get following error (This error is caused by domain_id column, i.e I get the error only if I include domain_id in the select query, otherwise it works just fine):

09:24:25 PST ERROR fa363402-bbc0-1802-ffff-ffff96aa31f4 <host>:9090

ExecuteSQL[id=fa363402-bbc0-1802-ffff-ffff96aa31f4] ExecuteSQL[id=fa363402-bbc0-1802-ffff-ffff96aa31f4] failed to process session due to org.apache.avro.file.DataFileWriter$AppendWriteException: org.apache.avro.UnresolvedUnionException: Not in union ["null","long"]: 141419: org.apache.avro.file.DataFileWriter$AppendWriteException: org.apache.avro.UnresolvedUnionException: Not in union ["null","long"]: 141419

Is this a bug or I might not be using this correctly? Is there any workaround for this issue?

Thanks

Obaid

[#2 · Accepted solution]

This is a bug, I have filed it under NIFI-3076. Workarounds might include changing the domain_id column to be signed (which is probably not desired), or using a cast() function to convert it into a data type (long, e.g.) that will be handled better for the time being. If you use a cast() or other function, you may want a column alias to ensure the column/field name is the one you want.