pvillard31 opened a new pull request, #10295:
URL: https://github.com/apache/nifi/pull/10295
# Summary
NIFI-14958 - Improve Arrays handling in JdbcCommon
`JdbcCommon` is used in `RecordSqlWriter` which is used by
`ExecuteSQLRecord` and `QueryDatabaseTableRecord` processors.
We construct the schema based on the result set metadata retrieved via the
JDBC access when executing the SQL query.
If a column type is currently set as ARRAY type by the driver (value =
2003), we would then create a field using
````java
builder.name(columnName).type().unionOf().nullBuilder().endNull().and().bytesType().endUnion().noDefault();
````
This should not be the case. If we retrieve an array of string for example,
we would cause an error like:
````java
2025-09-11 17:18:44,940 ERROR [Timer-Driven Process Thread-1]
o.a.n.p.standard.ExecuteSQLRecord
ExecuteSQLRecord[id=3832c36d-0199-1000-3439-4bd0a61bef96] Unable to execute
SQL select query [SELECT ['test'] as test;]. No FlowFile to route to failure
org.apache.nifi.processor.exception.ProcessException: java.io.IOException:
java.lang.NumberFormatException: For input string: "test"
at
org.apache.nifi.processors.standard.AbstractExecuteSQL.lambda$onTrigger$4(AbstractExecuteSQL.java:359)
at
org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:3107)
at
org.apache.nifi.processors.standard.AbstractExecuteSQL.onTrigger(AbstractExecuteSQL.java:355)
at
org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
at
org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1274)
at
org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:220)
at
org.apache.nifi.controller.scheduling.AbstractTimeBasedSchedulingAgent.lambda$doScheduleOnce$0(AbstractTimeBasedSchedulingAgent.java:59)
at org.apache.nifi.engine.FlowEngine.lambda$wrap$1(FlowEngine.java:105)
at
java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
at
java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
at
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.io.IOException: java.lang.NumberFormatException: For input
string: "test"
at
org.apache.nifi.processors.standard.sql.RecordSqlWriter.writeResultSet(RecordSqlWriter.java:88)
at
org.apache.nifi.processors.standard.AbstractExecuteSQL.lambda$onTrigger$4(AbstractExecuteSQL.java:357)
... 13 common frames omitted
Caused by: java.lang.NumberFormatException: For input string: "test"
at
java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:67)
at java.base/java.lang.Integer.parseInt(Integer.java:662)
at java.base/java.lang.Byte.parseByte(Byte.java:195)
at java.base/java.lang.Byte.parseByte(Byte.java:221)
at
org.apache.nifi.serialization.record.util.DataTypeUtils.toByte(DataTypeUtils.java:1514)
at
org.apache.nifi.serialization.record.util.DataTypeUtils.convertType(DataTypeUtils.java:197)
at
org.apache.nifi.serialization.record.util.DataTypeUtils.convertType(DataTypeUtils.java:174)
at
org.apache.nifi.json.WriteJsonResult.writeValue(WriteJsonResult.java:349)
at
org.apache.nifi.json.WriteJsonResult.writeArray(WriteJsonResult.java:470)
at
org.apache.nifi.json.WriteJsonResult.writeValue(WriteJsonResult.java:459)
at
org.apache.nifi.json.WriteJsonResult.writeRecord(WriteJsonResult.java:233)
at
org.apache.nifi.json.WriteJsonResult.writeRecord(WriteJsonResult.java:157)
at
org.apache.nifi.serialization.AbstractRecordSetWriter.write(AbstractRecordSetWriter.java:59)
at
org.apache.nifi.serialization.AbstractRecordSetWriter.write(AbstractRecordSetWriter.java:52)
at
java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at
org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:251)
at
org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler$ProxiedReturnObjectInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:237)
at jdk.proxy26/jdk.proxy26.$Proxy192.write(Unknown Source)
at
org.apache.nifi.processors.standard.sql.RecordSqlWriter.writeResultSet(RecordSqlWriter.java:82)
... 14 common frames omitted
````
We should try to use the result set to infer the type of the elements of the
array and, if not possible, default to string.
After this change, when executing (against a Clickhouse instance)
````sql
SELECT ['test'] as testStrings, [1, 2] as testInts;
````
I do not have any error anymore and the flowfile content is:
````json
[ {
"testStrings" : [ "test" ],
"testInts" : [ 1, 2 ]
} ]
````
# Tracking
Please complete the following tracking steps prior to pull request creation.
### Issue Tracking
- [ ] [Apache NiFi Jira](https://issues.apache.org/jira/browse/NIFI) issue
created
### Pull Request Tracking
- [ ] Pull Request title starts with Apache NiFi Jira issue number, such as
`NIFI-00000`
- [ ] Pull Request commit message starts with Apache NiFi Jira issue number,
as such `NIFI-00000`
### Pull Request Formatting
- [ ] Pull Request based on current revision of the `main` branch
- [ ] Pull Request refers to a feature branch with one commit containing
changes
# Verification
Please indicate the verification steps performed prior to pull request
creation.
### Build
- [ ] Build completed using `./mvnw clean install -P contrib-check`
- [ ] JDK 21
### Licensing
- [ ] New dependencies are compatible with the [Apache License
2.0](https://apache.org/licenses/LICENSE-2.0) according to the [License
Policy](https://www.apache.org/legal/resolved.html)
- [ ] New dependencies are documented in applicable `LICENSE` and `NOTICE`
files
### Documentation
- [ ] Documentation formatting appears as expected in rendered files
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
