Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Mark Rotteveel]

On 2017-01-19 13:14, Ann Harrison wrote:
>> On Jan 19, 2017, at 1:46 AM, Dmitry Yemanov  
>> wrote:
>> 
>> 19.01.2017 00:51, Ann Harrison wrote:
>>> 
>>> In what universe does that make sense?  The field is NOT NULL.  
>>> You're storing NULL in it.  That's an error.
>> 
>> I'd say it depends. What about a BEFORE trigger converting input NULL 
>> to
>> something valid before storing?
> 
> Sure, a before trigger can fix up bad values and avoid an error.  I
> haven't followed the development of the SQL standard for the past
> decade.   If it now says that assigning NULL to a column that
> disallows NULLs means that you should apply some other value in some
> cases, then I guess I know in what universe that makes sense.

Luckily it doesn't, it has DEFAULT for that.

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Mark Rotteveel]

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).
>> 
>> 
> 
> 
> 7) If the  is omitted, then an 
> 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  CTTVC is specified,
> then every  value constructor element> simply contained in CTTVC whose positionally
> corresponding 
> in  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
>  CN contained
> in the n-th ordinal position in  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  simply contained 
> in
> the  table value constructor> is a , then  clause> shall be specified.
> 
> i) If  is specified, then  shall be
> specified.
> 
> (I understand that even GENERATED ALWAYS can be overriden if  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  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  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
>  CN contained
> in the n-th ordinal position in  is an identity
> column whose descriptor includes an
> indication that values are generated by default, then if  clause> is specified, then  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,  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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Mark Rotteveel]

On 2017-01-19 11:35, Adriano dos Santos Fernandes wrote:
> On 19/01/2017 08:22, Dimitry Sibiryakov wrote:
>> 19.01.2017 11:03, Adriano dos Santos Fernandes wrote:
>> 
>>> c) If for some n, some underlying column of the column referenced by 
>>> the
>>>  CN contained
>>> in the n-th ordinal position in  is an identity
>>> column whose descriptor includes an
>>> indication that values are generated by default, then if >> clause> is specified, then >> clause> shall specify OVERRIDING USER VALUE.
>>> 
>>> (Seems our GENERATED BY DEFAULT is compliant)
>>We have no  in insert statement.
>> 
>> 
> Yes, my point is that this std. clause specifies that with GENERATED BY
> DEFAULT, OVERRIDE is not *required*.

Yes, but doing "insert into (generated_by_default_column, some_column) 
values (null, 'some value')" will assign null to 
generated_by_default_column, not the identity value. That only happens 
with "insert into (generated_by_default_column, some_column) values 
(default, 'some value')"

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Ann Harrison]

> On Jan 19, 2017, at 1:46 AM, Dmitry Yemanov  wrote:
> 
> 19.01.2017 00:51, Ann Harrison wrote:
>> 
>> In what universe does that make sense?  The field is NOT NULL.  You're 
>> storing NULL in it.  That's an error.
> 
> I'd say it depends. What about a BEFORE trigger converting input NULL to 
> something valid before storing?

Sure, a before trigger can fix up bad values and avoid an error.  I haven't 
followed the development of the SQL standard for the past decade.   If it now 
says that assigning NULL to a column that disallows NULLs means that you should 
apply some other value in some cases, then I guess I know in what universe that 
makes sense.

So what does 0-FEB-2017 mean in this brave new world?

> 
> 
> Dmitry
> 
> 
> --
> 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

--
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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Adriano dos Santos Fernandes]

On 19/01/2017 08:22, Dimitry Sibiryakov wrote:
> 19.01.2017 11:03, Adriano dos Santos Fernandes wrote:
>
>> c) If for some n, some underlying column of the column referenced by the
>>  CN contained
>> in the n-th ordinal position in  is an identity
>> column whose descriptor includes an
>> indication that values are generated by default, then if > clause> is specified, then > clause> shall specify OVERRIDING USER VALUE.
>>
>> (Seems our GENERATED BY DEFAULT is compliant)
>We have no  in insert statement.
>
>
Yes, my point is that this std. clause specifies that with GENERATED BY
DEFAULT, OVERRIDE is not *required*.


Adriano


--
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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Dimitry Sibiryakov]

19.01.2017 11:03, Adriano dos Santos Fernandes wrote:

> c) If for some n, some underlying column of the column referenced by the
>  CN contained
> in the n-th ordinal position in  is an identity
> column whose descriptor includes an
> indication that values are generated by default, then if  clause> is specified, then  clause> shall specify OVERRIDING USER VALUE.
>
> (Seems our GENERATED BY DEFAULT is compliant)

   We have no  in insert statement.


-- 
   WBR, SD.

--
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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Adriano dos Santos Fernandes]

On 19/01/2017 07:07, Mark Rotteveel wrote:
> On 2017-01-18 23:38, Adriano dos Santos Fernandes wrote:
>> Ann, I assigned it to me based on Dimitry S. comment.
>>
>> After your comment, I edited the case to reflect the standard.
> 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).
>
>


7) If the  is omitted, then an 
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)

10) If  CTTVC is specified,
then every  simply contained in CTTVC whose positionally
corresponding 
in  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
 CN contained
in the n-th ordinal position in  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  simply contained in
the  is a , then  shall be specified.

i) If  is specified, then  shall be
specified.

(I understand that even GENERATED ALWAYS can be overriden if  is used with)

NOTE 516 — The preceding subrule does not cover all possibilities. The
remaining possibilities are where  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  is
specified or not.

(???)

c) If for some n, some underlying column of the column referenced by the
 CN contained
in the n-th ordinal position in  is an identity
column whose descriptor includes an
indication that values are generated by default, then if  is specified, then  shall specify OVERRIDING USER VALUE.

(Seems our GENERATED BY DEFAULT is compliant)

...

e) Otherwise,  shall not be specified.


Adriano


--
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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Mark Rotteveel]

On 2017-01-18 23:38, Adriano dos Santos Fernandes wrote:
> Ann, I assigned it to me based on Dimitry S. comment.
> 
> After your comment, I edited the case to reflect the standard.

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).

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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Dmitry Yemanov]

19.01.2017 00:51, Ann Harrison wrote:
>
> In what universe does that make sense?  The field is NOT NULL.  You're 
> storing NULL in it.  That's an error.

I'd say it depends. What about a BEFORE trigger converting input NULL to 
something valid before storing?


Dmitry


--
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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Adriano dos Santos Fernandes]

Ann, I assigned it to me based on Dimitry S. comment.

After your comment, I edited the case to reflect the standard.


Adriano


Em 18/01/2017 19:51, Ann Harrison escreveu:
> On Jan 18, 2017, at 11:17 AM, Gerhard S (JIRA)  
> wrote:
>>
>> Insert NULL into identity column with auth generated value
>> --
>>
>> Key: CORE-5460
>> URL: http://tracker.firebirdsql.org/browse/CORE-5460
>> Project: Firebird Core
>>  Issue Type: Improvement
>>Affects Versions: 3.0.0
>> Environment: Windows 10 64bit, LibreOffice 5.3.0RC1
>>Reporter: Gerhard S

--
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

Re: [Firebird-devel] [FB-Tracker] Created: (CORE-5460) Insert NULL into identity column with auth generated value

[Ann Harrison]

On Jan 18, 2017, at 11:17 AM, Gerhard S (JIRA)  wrote:
> 
> Insert NULL into identity column with auth generated value
> --
> 
> Key: CORE-5460
> URL: http://tracker.firebirdsql.org/browse/CORE-5460
> Project: Firebird Core
>  Issue Type: Improvement
>Affects Versions: 3.0.0
> Environment: Windows 10 64bit, LibreOffice 5.3.0RC1
>Reporter: Gerhard S
> 
> 
> Could you support inserting rows where the value for the identity column is 
> passed as NULL in order to increment the value automatically?.
> 
> Example:
> create table testtbl (
> id integer generated by default as identity (START WITH 0) NOT NULL primary 
> key,
> name varchar(15)
> );
> 
> insert into testtbl values (NULL, 'name1');
> 
> This only makes sense, if the column is NOT NULL, I guess. Other database 
> systems such as MySQL, HSQLDB, MariaDB allow that.


In what universe does that make sense?  The field is NOT NULL.  You're storing 
NULL in it.  That's an error.  Not an error only if there's no default value, 
not an error only if there's not a sequence.  It's an ERROR.  And quite typical 
of MySQL which tries to make life easier for developers by not giving an error 
if you store 10 in a 16 bit integer field - it stores 32767. -- you know, 
best try.  Like 0-FEB-2017 matches any day in February.  

Maybe if you assign 'ABC' to integer it should store 123?  Or the RAD50 values?


Death to cute hacks!

Good luck,

Ann
--
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