[
https://issues.apache.org/jira/browse/NIFI-1613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15188454#comment-15188454
]
Matt Burgess commented on NIFI-1613:
------------------------------------
[~aaronst] is on the right track here. We use the reported column width to
truncate the value coming in from JSON, which works for strings but is mostly
incorrect for other types. For the given example of boolean, we get a JSON
value of "true", but the column width is 1 (as it is a binary/boolean field),
so the value is truncated to 't' which is not valid SQL in most/all cases. As a
workaround, the user would have to ensure boolean values in the JSON are 0 and
1 rather than true and false.
For floats/doubles, the reported column width (such as the default) could cause
truncation or error. For example in MySQL the default for FLOAT is (10,2) or up
to 8 digits followed by 2 decimals
(http://www.tutorialspoint.com/mysql/mysql-data-types.htm), plus the period and
possible sign (+/-), so a column width of 12. Anything more will be truncated,
which might actually be default behavior if you try a longer float value in the
query. However if a longer float would normally cause a SQL error, the
processor would truncate and probably continue on its way, with the user not
knowing that the JSON contained an incompatible float, and data errors may
occur.
Bottom line is, the processor needs to do a more accurate job of converting
JSON values of the expected type into values ready for SQL.
> ConvertJSONToSQL Drops Type Information
> ---------------------------------------
>
> Key: NIFI-1613
> URL: https://issues.apache.org/jira/browse/NIFI-1613
> Project: Apache NiFi
> Issue Type: Bug
> Components: Core Framework
> Affects Versions: 0.4.1, 0.5.1
> Environment: Ubuntu 14.04 LTS
> Reporter: Aaron Stephens
> Labels: ConvertJSONToSQL, Phoenix, SQL
>
> It appears that the ConvertJSONToSQL processor is turning Boolean (and
> possibly Integer and Float) values into Strings. This is okay for some
> drivers (like PostgreSQL) which can coerce a String back into a Boolean, but
> it causes issues for others (specifically Phoenix in my case).
> {noformat}
> org.apache.phoenix.schema.ConstraintViolationException:
> org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type
> mismatch. VARCHAR cannot be coerced to BOOLEAN
> at
> org.apache.phoenix.schema.types.PDataType.throwConstraintViolationException(PDataType.java:282)
> ~[na:na]
> at
> org.apache.phoenix.schema.types.PBoolean.toObject(PBoolean.java:136) ~[na:na]
> at
> org.apache.phoenix.jdbc.PhoenixPreparedStatement.setObject(PhoenixPreparedStatement.java:442)
> ~[na:na]
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
> ~[na:na]
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
> ~[na:na]
> at
> org.apache.nifi.processors.standard.PutSQL.setParameter(PutSQL.java:728)
> ~[na:na]
> at
> org.apache.nifi.processors.standard.PutSQL.setParameters(PutSQL.java:606)
> ~[na:na]
> at
> org.apache.nifi.processors.standard.PutSQL.onTrigger(PutSQL.java:223) ~[na:na]
> at
> org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
> ~[nifi-api-0.4.1.jar:0.4.1]
> at
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1146)
> ~[nifi-framework-core-0.4.1.jar:0.4.1]
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:139)
> [nifi-framework-core-0.4.1.jar:0.4.1]
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:49)
> [nifi-framework-core-0.4.1.jar:0.4.1]
> at
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:119)
> [nifi-framework-core-0.4.1.jar:0.4.1]
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
> [na:1.7.0_79]
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
> [na:1.7.0_79]
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
> [na:1.7.0_79]
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
> [na:1.7.0_79]
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> [na:1.7.0_79]
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> [na:1.7.0_79]
> at java.lang.Thread.run(Thread.java:745) [na:1.7.0_79]
> Caused by: org.apache.phoenix.schema.TypeMismatchException: ERROR 203
> (22005): Type mismatch. VARCHAR cannot be coerced to BOOLEAN
> at
> org.apache.phoenix.exception.SQLExceptionCode$1.newException(SQLExceptionCode.java:71)
> ~[na:na]
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
> ~[na:na]
> ... 20 common frames omitted
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
