OVERRIDING USER VALUE should be allowed for GENERATED ALWAYS AS IDENTITY

[mrotteveel]

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.

[mrotteveel]

The SQL:2023-2 standard uses an - in my opinion - better description in 14.11 <insert statement>:

13) In the following circumstances, an <override clause> is permitted or required; if none of the following circumstances pertain, then <override clause> shall not be specified.
[..]
b) If some underlying column of an object column OC is an identity column whose values are always generated, and OC is not defaulted, then <override clause> shall be specified.
c) If some underlying column of an object column is an identity column whose values are generated by default, and <override clause> is specified, then <override clause> shall specify OVERRIDING USER VALUE.
[..]

[mrotteveel]

@asfernandes Can this be backported to 4.0 as well?

[asfernandes]

I can do it if you test the change in v5 and say it's ok.

[pavel-zotov]

I can do it if you test the change in v5 and say it's ok.

I've checked on 5.0.0.1093 (intermediate build from today's artefacts), script:

    recreate table IDENTITY_ALWAYS (
      ID bigint generated always as identity constraint pk_identity_always primary key,
      VAL varchar(10)
    );
    commit;

    insert into IDENTITY_ALWAYS (ID, VAL) overriding user value values (-9223372036854775808, 'B1') returning ID, VAL;
    insert into IDENTITY_ALWAYS (ID, VAL) overriding user value values (null, 'B2') returning ID, VAL;
    insert into IDENTITY_ALWAYS (ID, VAL) overriding user value values (cast(9223372036854775808 as int128), 'B3') returning ID, VAL;

Output:

    ID                              1
    VAL                             B1
    ID                              2
    VAL                             B2
    ID                              3
    VAL                             B3

[mrotteveel]

I can do it if you test the change in v5 and say it's ok.

@asfernandes I've also tested it, and it works OK