[
https://issues.apache.org/jira/browse/NIFI-8493?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian updated NIFI-8493:
-------------------------
Description:
When there is a column where the value is "auto increment/identity", the type
resolution fails.
This is similar to NIFI-8244 and was not resolved by NIFI-8237.
When we INSERT new data we get this error message: error: Operandtypecollision:
datetime2 is incompatible with smallint.
The problem is, that the method is using indexes and not names. In the record
the first value is '_a_' but the method uses '_id_' as first value. Therefore
everything is off by one.
Our table looks like this:
{color:#cc7832}create table {color}t
(
{color:#9876aa}id {color}{color:#cc7832}bigint identity
{color}({color:#6897bb}0{color}{color:#cc7832}, {color}{color:#6897bb}1{color})
{color:#cc7832}constraint {color}t_PK
{color:#cc7832}primary key,
{color} {color:#9876aa}a {color}{color:#cc7832}int not null,
{color} {color:#9876aa}b {color}{color:#cc7832}bigint not null,
{color} {color:#9876aa}c {color}{color:#cc7832}float not null,
{color} {color:#9876aa}d {color}{color:#cc7832}datetime not null,
{color} {color:#9876aa}e {color}{color:#cc7832}smallint not null,
{color} {color:#9876aa}f {color}{color:#cc7832}float,
{color} {color:#9876aa}g {color}{color:#cc7832}float,
{color} {color:#9876aa}h {color}{color:#cc7832}datetime default
{color}{color:#ffc66d}getdate{color}()
)
Record:
{
"d": 1619503081000,
"c": 0,
"a": 34,
"b": 34,
"e": 0,
"f": "1.1",
"g": "1.2",
"h": 1619503095159
{{}}}
*What worked for us was changing this line in PutDatabaseRecord.executeDML():*
Before:
{color:#cc7832}final {color}ColumnDescription column =
columns.get(currentFieldIndex
After:
{color:#cc7832}final {color}ColumnDescription column =
tableSchema.getColumns().get(normalizeColumnName(recordReader.getSchema().getField(i).getFieldName(){color:#cc7832},{color}
settings.{color:#9876aa}translateFieldNames{color})){color:#cc7832};{color}
This change also has another benefit. The order of the fields in RecordReader
doesn't matter any more.
was:
When there is a column where the value is "auto increment/identity", the type
resolution fails.
This is similar to NIFI-8244 and was not resolved by NIFI-8237.
When we INSERT new data we get this error message: error: Operandtypecollision:
datetime2 is incompatible with smallint.
The problem is, that the method is using indexes and not names. In the record
the first value is '_a_' but the method uses '_id_' as first value. Therefore
everything is off by one.
Our table looks like this:
{{create table t}}
{{(}}
\{{ id bigint identity (0, 1) }}{{}}{{primary key,}}
\{{ a int not null,}}
\{{ b bigint not null,}}
\{{ c float not null,}}
\{{ d datetime not null,}}
\{{ e smallint not null,}}
\{{ f float,}}
\{{ g float,}}
\{{ h datetime default getdate()}}
{{)}}
Record:
{\{{}}
\{{ "d": 1619503081000,}}
\{{ "c": 0,}}
\{{ "a": 34,}}
\{{ "b": 34,}}
\{{ "e": 0,}}
\{{ "f": "1.1",}}
\{{ "g": "1.2",}}
\{{ "h": 1619503095159}}
{{}}}
*What worked for us was changing this line in PutDatabaseRecord.executeDML():*
Before:
{color:#cc7832}final {color}ColumnDescription column =
columns.get(currentFieldIndex
After:
{color:#cc7832}final {color}ColumnDescription column =
tableSchema.getColumns().get(normalizeColumnName(recordReader.getSchema().getField(i).getFieldName(){color:#cc7832},{color}
settings.{color:#9876aa}translateFieldNames{color})){color:#cc7832};{color}
This change also has another benefit. The order of the fields in RecordReader
doesn't matter any more.
> PutDatabaseRecord incorrect type resolution for auto increment columns
> ----------------------------------------------------------------------
>
> Key: NIFI-8493
> URL: https://issues.apache.org/jira/browse/NIFI-8493
> Project: Apache NiFi
> Issue Type: Bug
> Components: Core Framework
> Affects Versions: 1.13.2
> Environment: Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) -
> 15.0.2080.9 (X64)
> Nov 6 2020 16:50:01
> Copyright (C) 2019 Microsoft Corporation
> Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64>
> (Build 17763: ) (Hypervisor)
> Reporter: Julian
> Priority: Major
> Fix For: 1.13.3
>
> Original Estimate: 4h
> Remaining Estimate: 4h
>
> When there is a column where the value is "auto increment/identity", the type
> resolution fails.
> This is similar to NIFI-8244 and was not resolved by NIFI-8237.
> When we INSERT new data we get this error message: error:
> Operandtypecollision: datetime2 is incompatible with smallint.
> The problem is, that the method is using indexes and not names. In the record
> the first value is '_a_' but the method uses '_id_' as first value. Therefore
> everything is off by one.
> Our table looks like this:
> {color:#cc7832}create table {color}t
> (
> {color:#9876aa}id {color}{color:#cc7832}bigint identity
> {color}({color:#6897bb}0{color}{color:#cc7832},
> {color}{color:#6897bb}1{color})
> {color:#cc7832}constraint {color}t_PK
> {color:#cc7832}primary key,
> {color} {color:#9876aa}a {color}{color:#cc7832}int not null,
> {color} {color:#9876aa}b {color}{color:#cc7832}bigint not null,
> {color} {color:#9876aa}c {color}{color:#cc7832}float not null,
> {color} {color:#9876aa}d {color}{color:#cc7832}datetime not null,
> {color} {color:#9876aa}e {color}{color:#cc7832}smallint not null,
> {color} {color:#9876aa}f {color}{color:#cc7832}float,
> {color} {color:#9876aa}g {color}{color:#cc7832}float,
> {color} {color:#9876aa}h {color}{color:#cc7832}datetime default
> {color}{color:#ffc66d}getdate{color}()
> )
>
> Record:
> {
> "d": 1619503081000,
> "c": 0,
> "a": 34,
> "b": 34,
> "e": 0,
> "f": "1.1",
> "g": "1.2",
> "h": 1619503095159
> {{}}}
>
> *What worked for us was changing this line in PutDatabaseRecord.executeDML():*
> Before:
> {color:#cc7832}final {color}ColumnDescription column =
> columns.get(currentFieldIndex
> After:
> {color:#cc7832}final {color}ColumnDescription column =
> tableSchema.getColumns().get(normalizeColumnName(recordReader.getSchema().getField(i).getFieldName(){color:#cc7832},{color}
> settings.{color:#9876aa}translateFieldNames{color})){color:#cc7832};{color}
>
> This change also has another benefit. The order of the fields in RecordReader
> doesn't matter any more.
>
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
