Duplicate values can occur for the sequence generator identity column

[Joanie Cooper]

While running the SQL regression test, seabase/TEST025, the T025T006 "upsert using load" DML statement succeeds, but can contain duplicate values in the sequence generator identity column.

These values should always be unique.

Multiple ESP's are writing to the sequence generator table in parallel. Each write should have DTM transaction protection.

We need to determine which area of the Trafodion stack is experiencing the problem and resolve the issue.

[Joanie Cooper (joanie-cooper)]

We are currently tracking this problem as a possible duplicate of a SQL regression introduced from a delivery
in the nov27_5c3ab6a build for delivery https://review.trafodion.org/#/c/741/.

This testcase was tried on a build prior to the nov27_5c3ab6a build and the problem did not reproduce.

However, this problem does not reproduce every time. But, several attempts were made and no duplicate values
were generated for the identity column.

We are continuing to work this problem and will provide additional status.

[Joanie Cooper (joanie-cooper)]

A SQL plan is being created that indicates that the update to the sequence generator table row managing the value for the identity column is being executed without a DTM transaction. This allows serial ESP plans all updating the identity column to update the hbase rows outside of a DTM transaction. This can lead to duplicates.

I am testing a change to the SQL compiler generator to ensure we indicate this sequence generator update should be executed within a DTM transaction.

[Joanie Cooper (joanie-cooper)]

SQL regressions passed.
The fix was submitted and merged into core.