On 2017-01-19 11:03, Adriano dos Santos Fernandes wrote: > On 19/01/2017 07:07, Mark Rotteveel wrote: >> Does the standard actually allow having an insert with null (or any >> other value for that matter) for a GENERATED ALWAYS AS IDENTITY (I >> don't >> have a copy of the SQL standard at hand right now)? I would expected >> an >> error to be raised; as far as I am aware, that is only supported with >> DEFAULT (see CORE-5449). >> >> > <insert statement> > > 7) If the <insert column list> is omitted, then an <insert column list> > that identifies all columns of T in the > ascending sequence of their ordinal positions within T is implicit. > > (I understand that if column list is omitted, values for identity > columns "generated always" should be given by the user - see clause 10)
Yes, but the SQL standard then assume the user will use DEFAULT, and not any other value, unless the override clause is supported. > 10) If <contextually typed table value constructor> CTTVC is specified, > then every <contextually typed row > value constructor element> simply contained in CTTVC whose positionally > corresponding <column name> > in <insert column list> references > > (I understand that GENERATED ALWAYS column, if implicit by empty field > list, or explicit, should be passed with DEFAULT - but see clause 11.b) > > 11) Case: > > b) 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, system-time period start > column, or system-time period end column whose descriptor includes an > indication that values are > always generated, then > > 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. > > i) If <from subquery> is specified, then <override clause> shall be > specified. > > (I understand that even GENERATED ALWAYS can be overriden if <override > clause> is used with) But Firebird doesn't have an override clause (specifically overriding system value), so it will not be possible to override an always generated value. > > NOTE 516 — 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. > > (???) This means that when the DEFAULT pseudo-value (CORE-5449) is specified for a generated column, then the generated value is used, but it is not necessary to have an override clause > 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. > > (Seems our GENERATED BY DEFAULT is compliant) If I understand it correctly, this means that if you have a generated by default and use "insert into sometable (generated_as_default_column, other_column) overriding user value (values (513, 'some value'))" (not sure of the exact syntax, not even sure if the override clause is allowed with values), then instead of 513 the column generated_as_default_column will get the generated identity value anyway. I might be wrong though, because I don't see why you would even need that (just exclude the column). > e) Otherwise, <override clause> shall not be specified. I'll need to dig into it a little bit more, but as far as I can tell, nothing in this suggests that doing "insert into sometable(generated_always_column, other_column) values (null, 'some value')" would be allowed unless "overriding user value" has been specified to explicitly indicate that you are aware that your null will be ignored. See for example http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/sqlp/rbafyinsertidentity.htm : "In this case, OVERRIDING USER VALUE tells the system to ignore the value provided for the identity column from the SELECT and to generate a new value for the identity column. OVERRIDING USER VALUE must be used if the identity column was created with the GENERATED ALWAYS clause; it is optional for GENERATED BY DEFAULT. If OVERRIDING USER VALUE is not specified for a GENERATED BY DEFAULT identity column, the value provided for the column in the SELECT is inserted." The only valid value without an override clause for a generated always column is DEFAULT, or not having it in the values (or select) list. Mark ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, SlashDot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
