[NIFI-7973] Oracle JDBC - ExecuteSqlRecord and other components fail on zero precision

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.13.0
• Component/s: Extensions
• Labels: AvroSchema AvroTypeUtil ExecuteSqlRecord Oracle
• Environment: Centos 8.2 ; JDK 11 ; Oracle 11.2 ; Oracle JDBC 8
• Assignee: Matt Burgess
• Reporter: Robin Giesen
• Created: 03/Nov/20 07:59
• Updated: 28/Jul/21 10:22
• Resolved: 13/Jan/21 13:41

Description

Hi there,

during the upgrade from 11.4 to 12.1 we noticed that isn't longer possible to reuse the old canvas and settings because the ExecuteSqlRecord Processor cannot handle Oracles Number conversion. We use a ExecuteSqlRecord processor with incoming SQL statements and an AvroRecordWriter where the schema is determined on the fly. We also tested the ExceuteSql Processor which can use default precision and scale. Hence, there was no error. In the 12.0 version, the error doesn't occur.
 
We tested the following combinations:
1. 
SELECT 1 as test from dual => FAIL
2. 

SELECT CAST(1 as number(12,2)) as test from dual => SUCCESS

2020-10-30 18:08:24,677 ERROR [Timer-Driven Process Thread-9] o.a.n.p.standard.ExecuteSQLRecord ExecuteSQLRecord[id=7a50a193-0175-1000-f2e7-78054e6dcba0] Unable to execute SQL select query SELECT cast(1 as number(12,2)) TEST, 1 as test2 FROM DUAL due to org.apache.nifi.processor.exception.ProcessException: java.io.IOException: org.apache.nifi.processor.exception.ProcessException: Could not determine the Avro Schema to use for writing the content. No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: java.io.IOException: org.apache.nifi.processor.exception.ProcessException: Could not determine the Avro Schema to use for writing the content
org.apache.nifi.processor.exception.ProcessException: java.io.IOException: org.apache.nifi.processor.exception.ProcessException: Could not determine the Avro Schema to use for writing the content
    at org.apache.nifi.processors.standard.AbstractExecuteSQL.lambda$onTrigger$1(AbstractExecuteSQL.java:302)
    at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2751)
    at org.apache.nifi.processors.standard.AbstractExecuteSQL.onTrigger(AbstractExecuteSQL.java:298)
    at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
    at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1174)
    at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213)
    at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
    at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
    at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.io.IOException: org.apache.nifi.processor.exception.ProcessException: Could not determine the Avro Schema to use for writing the content
    at org.apache.nifi.processors.standard.sql.RecordSqlWriter.writeResultSet(RecordSqlWriter.java:88)
    at org.apache.nifi.processors.standard.AbstractExecuteSQL.lambda$onTrigger$1(AbstractExecuteSQL.java:300)
    ... 13 common frames omitted
Caused by: org.apache.nifi.processor.exception.ProcessException: Could not determine the Avro Schema to use for writing the content
    at org.apache.nifi.avro.AvroRecordSetWriter.createWriter(AvroRecordSetWriter.java:154)
    at jdk.internal.reflect.GeneratedMethodAccessor410.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)
    at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:105)
    at com.sun.proxy.$Proxy167.createWriter(Unknown Source)
    at org.apache.nifi.processors.standard.sql.RecordSqlWriter.writeResultSet(RecordSqlWriter.java:81)
    ... 14 common frames omitted
Caused by: org.apache.nifi.schema.access.SchemaNotFoundException: Failed to compile Avro Schema
    at org.apache.nifi.avro.AvroRecordSetWriter.createWriter(AvroRecordSetWriter.java:145)
    ... 21 common frames omitted
Caused by: java.lang.IllegalArgumentException: Invalid decimal precision: 0 (must be positive)
    at org.apache.avro.LogicalTypes$Decimal.validate(LogicalTypes.java:206)
    at org.apache.avro.LogicalType.addToSchema(LogicalType.java:70)
    at org.apache.avro.LogicalTypes$Decimal.addToSchema(LogicalTypes.java:182)
    at org.apache.nifi.avro.AvroTypeUtil.buildAvroSchema(AvroTypeUtil.java:263)
    at org.apache.nifi.avro.AvroTypeUtil.buildAvroField(AvroTypeUtil.java:127)
    at org.apache.nifi.avro.AvroTypeUtil.buildAvroSchema(AvroTypeUtil.java:119)
    at org.apache.nifi.avro.AvroTypeUtil.extractAvroSchema(AvroTypeUtil.java:99)
    at org.apache.nifi.avro.AvroRecordSetWriter.createWriter(AvroRecordSetWriter.java:142)
    ... 21 common frames omitted

Issue Links

• causes: NIFI-8951 Precision is used instead of scale in QueryDatabase processors
• is related to: NIFI-3958 Avro decimal logical type fail if database returns Zero precision
• links to: GitHub Pull Request #4682