[teiid-issues] [JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value

[Steven Hawkins (Jira)]

     [ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-5782:
----------------------------------
    Issue Type: Quality Risk  (was: Bug)
       Summary: When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value  (was: When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer.)
      Priority: Major  (was: Critical)


This unfortunately is not a well defined situation.

Core Teiid does not support the behavior you are describing.  If a column is not null, then null cannot be explicitly inserted even if the column is marked as auto increment.  This is common source behavior, but to my knowledge not guaranteed.  

The zero value is even more troublesome as there are typically source flags that dictate the expected behavior - https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_auto_value_on_zero

See also identity behavior for postgresql - https://www.postgresql.org/docs/10/sql-insert.html if the column is marked as generated always, then you cannot specify a insert value without a special overriding clause.

The best practice would be to not rely on the implicit source behavior, and instead omit the value from the insert when you want to utilize the auto increment value.

> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5782
>                 URL: https://issues.jboss.org/browse/TEIID-5782
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: OData
>    Affects Versions: 12.3
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.



--
This message was sent by Atlassian Jira
(v7.12.1#712002)