Attaching derby-3155-07-ad-insertAction.diff. This patch adds support for the INSERT action of MERGE statements. I am running tests now.
The patch includes tests for the following use-cases:
i) INSERT actions with DEFAULT expressions for identity, generated, and default columns
ii) INSERT actions on tables with CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints.
iii) INSERT actions in MERGE statements fired by triggers.
iv) INSERT actions which fire triggers.
v) MERGE statements with INSERT and DELETE actions operating together.
The following behaviors have not been implemented/tested yet:
a) UPDATE actions
b) Permissions checking
c) Dependency checking and statement invalidation
So far the bulk of the complexity remains concentrated in compilation.
Touches the following files:
Replaced a magic number with a named constant. Temporary tables don't have result set numbers assigned to them. But now they are being used to drive the INSERT/UPDATE/DELETE actions of MERGE statements. In order to get CHECK constraints to work properly, the row being checked is stuffed into slot 0 of the result set row array. That is usually the result set number corresponding to the driving result set of an INSERT/UPDATE/DELETE statement. And it was, coincidentally, the magic result set number of temporary tables. This decision may need to be re-visited if we invent other situations for which temporary tables need result set numbers.
Improved a method header comment.
Added a new way to determine the data type of a ResultColumn. There is now a path through the pinball machine where the old technique didn't work.
Compilation support for the INSERT action of a MERGE statement. The bulk of the new complexity has to do with generating the rows which are stuffed into a temporary table at execution time. There is considerable complexity involved in handling the DEFAULT keyword, which can appear as the value to be inserted into an identity, generated, or default column, as for instance, in this situation:
create table t1_007
c1 int generated always as identity,
c3 int generated always as ( c1 + c2 ),
c5 int default 1000
merge into t1_007
on t1_007.c2 = t2_007.c2
when not matched and t2_007.c5 = 'three'
then insert ( c1, c2, c3, c1_4, c5 ) values ( default, 100 * t2_007.c2, default, t2_007.c3, default );
For an ordinary INSERT statement, much of that complexity is handled by the compilation of the driving SELECT. For a MERGE statement, that SELECT is a dummy statement which is not optimized or generated. That, in turn, short-circuits some of the complex handling of the DEFAULT keyword. So that logic had to be reproduced in MatchingClauseNode.
There is already too much complexity in the handling of these special columns and now I have made the situation even more complicated. In particular, I don't understand why GENERATED BY DEFAULT AS IDENTITY and GENERATED ALWAYS AS IDENTITY take such different trajectories through the pinball machine. And I don't understand why the special columns are compiled with the driving SELECT rather than with the INSERT/UPDATE action itself. This is a source of brittleness which we should be aware of. However, I thought that cleaning this up was outside the scope of this JIRA.
Added a small amount of logic so that INSERT/UPDATE/DELETE actions know at execution time whether they are running under a MERGE statement. If so, then we don't close the Activation at the end of the action. The Activation is closed by the MergeResultSet at the very end after all of the actions have run. This, in turn, makes it possible to run multiple INSERT/DELETE/UPDATE actions in a single MERGE statement.
Added some special processing for generation clauses invoked by actions of MERGE statements. This is more of the fallout from the fact that the dummy SELECTs are not optimized and generated.
Abstracted some logic into a separate method so that it could be called when generating the temporary row for an INSERT action of a MERGE statement.
Changes to support INSERT actions under MERGE statements.
Tests for the new behavior.