Currently, attempts to specify OVERRIDING USER VALUE is disallowed if the identity column is of type GENERATED ALWAYS. This is in conflict with Syntax rule 11 b of 14.11 <insert statement> from SQL:2016-2. It seems rule 11 c has been interpreted as disallowing OVERRIDING USER VALUE for GENERATED ALWAYS, while its actual intent is to disallow OVERRIDING SYSTEM VALUE for GENERATED BY DEFAULT.
Rule 11 b specifies that the override-clause must occur (and so implies that both SYSTEM or USER are allowed), if the identity column is referenced in the insert column list, and it is generated always.
Rule 11 c specifies that the override-clause can occur, and only OVERRIDING USER VALUE is allowed, if the identity column is referenced in the insert column list, and it is generated by default.
Test case:
recreate table IDENTITY_ALWAYS (
ID integer generated always as identity constraint pk_identity_always primary key,
VAL varchar(10)
);
commit;
insert into IDENTITY_ALWAYS (ID, VAL) overriding user value values (100, 'A') returning ID, VAL;
Expected result: row returned with (1, 'A')
Actual result: error:
Statement failed, SQLSTATE = 42000
OVERRIDING USER VALUE can be used only for identity column defined as 'GENERATED BY DEFAULT' in INSERT for table/view IDENTITY_ALWAYS
In other words, it should behave the same as:
recreate table IDENTITY_DEFAULT (
ID integer generated by default as identity constraint pk_identity_default primary key,
VAL varchar(50)
);
commit;
insert into IDENTITY_DEFAULT (ID, VAL) overriding user value values (100, 'A') returning ID, VAL;
Expected and actual result:
ID VAL
============ ==================================================
1 A
For completeness rules 11 b and c:
11) Case:
a) [...]
b) If, for some n, some underlying column of the column referenced by the CN contained in the n-th ordinal position in <insert column list> is an identity column, system-time period start column, or system-time period end column whose descriptor includes an indication that values are always generated, then
Case:
i) If <from subquery> is specified, then <override clause> shall be specified.
ii) If any <contextually typed row value expression> simply contained in the <contextually typed table value constructor> is a <row value special case>, then <override clause> shall be specified.
iii) If the n-th <contextually typed row value constructor element> simply contained in any <contextually typed row value constructor> simply contained in the <contextually typed table value constructor> is not a <default specification>, then <override clause> shall be specified.
NOTE 656 — The preceding subrule does not cover all possibilities. The remaining possibilities are where <default specification> is specified for every identity column, or for a system-time period start column or system-time period end column, in which case it is immaterial whether <override clause> is specified or not.
c) If for some n, some underlying column of the column referenced by the <column name> CN contained in the n-th ordinal position in <insert column list> is an identity column whose descriptor includes an indication that values are generated by default, then if <override clause> is specified, then <override clause> shall specify OVERRIDING USER VALUE.
Currently, attempts to specify
OVERRIDING USER VALUEis disallowed if the identity column is of typeGENERATED ALWAYS. This is in conflict with Syntax rule 11 b of 14.11 <insert statement> from SQL:2016-2. It seems rule 11 c has been interpreted as disallowingOVERRIDING USER VALUEforGENERATED ALWAYS, while its actual intent is to disallowOVERRIDING SYSTEM VALUEforGENERATED BY DEFAULT.Rule 11 b specifies that the override-clause must occur (and so implies that both
SYSTEMorUSERare allowed), if the identity column is referenced in the insert column list, and it is generated always.Rule 11 c specifies that the override-clause can occur, and only
OVERRIDING USER VALUEis allowed, if the identity column is referenced in the insert column list, and it is generated by default.Test case:
Expected result: row returned with (1, 'A')
Actual result: error:
In other words, it should behave the same as:
Expected and actual result:
For completeness rules 11 b and c: