[jira] [Commented] (NIFI-8759) ExecuteSQL and ExecuteSQLRecord unnecessarily fall back to default decimal scale

["Matt Burgess (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/NIFI-8759?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17376004#comment-17376004 ] 

Matt Burgess commented on NIFI-8759:
------------------------------------

The comments in the original Jira NIFI-3958 seem to explain why the check > 0 is used instead of >= 0:

// Oracle returns precision=0, scale=-127 for variable scale value such as ROWNUM or function result.
// Specifying 'oracle.jdbc.J2EE13Compliant' SystemProperty makes it to return scale=0 instead.
// Queries for example, 'SELECT 1.23 as v from DUAL' can be problematic because it can't be mapped with decimal with scale=0.
// Default scale is used to preserve decimals in such case.

So it seems like Oracle can step on itself if the system property is applied. If setting the property to be compliant also causes an issue with some queries, IMO that's an Oracle issue and not something we should allow for at the expense of other compliant DBs. [~ijokarumawak] Do you have thoughts here?

Is there a use case where scale=0 but the default scale should be something other than 0? If not the solution is just direct the user to set the default scale to zero; otherwise I think we should change the check to >= 0 before using the default scale. This line got copied from the negative-precision clause in NIFI-3958 to the positive-precision case in NIFI-6775, so would likely need to be changed in both places.

> ExecuteSQL and ExecuteSQLRecord unnecessarily fall back to default decimal scale
> --------------------------------------------------------------------------------
>
>                 Key: NIFI-8759
>                 URL: https://issues.apache.org/jira/browse/NIFI-8759
>             Project: Apache NiFi
>          Issue Type: Bug
>            Reporter: Denes Arvay
>            Assignee: Denes Arvay
>            Priority: Major
>
> If the database returns 0 as scale of a decimal field ExecuteSQL and ExecuteSQLRecord processors fall back to the default scale even though 0 should be a valid scale.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)