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

2017-01-19 Thread 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

2017-01-19 Thread 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

2017-01-19 Thread 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

2017-01-19 Thread 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

2017-01-19 Thread 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

[Firebird-devel] -std=c++11 added to CXXFLAGS in 3.0 on linux

2017-01-19 Thread Michal Kubecek 
Hello,

commit 52d9a05a0f3d ("Backport from master: Optimized hash function for
lock manager and hash join") in B3_0_Release branch adds "-std=c++11" to
CXXFLAGS on Linux.

Is this change intentional? Is it something we want? It doesn't seem to
be necessary, as far as I can say, the build succeeds without it, both
with gcc 4.3 and gcc6.

 Michal Kubecek


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

2017-01-19 Thread 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

2017-01-19 Thread 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

2017-01-19 Thread 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

[Firebird-devel] [FB-Tracker] Created: (CORE-5463) Support GENERATED ALWAYS identity columns

2017-01-19 Thread Adriano dos Santos Fernandes (JIRA) 
Support GENERATED ALWAYS identity columns
-

 Key: CORE-5463
 URL: http://tracker.firebirdsql.org/browse/CORE-5463
 Project: Firebird Core
  Issue Type: New Feature
  Components: Engine
Reporter: Adriano dos Santos Fernandes


Quoting SQL standard of INSERT command:

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.

10) If  CTTVC is specified, then 
every  simply contained in CTTVC whose positionally 
corresponding 
in  references a column of which some underlying column is 
a generated column shall
be a .

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

Case:
i) If  is specified, then  shall be specified.

iii) If the n-th  simply 
contained in any  simply contained in the  is not a , then  shall be 
specified.

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.

e) Otherwise,  shall not be specified.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

   

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

[Firebird-devel] [FB-Tracker] Created: (CORE-5464) AV in fbclient when reading blob stored in incompatible encoding

2017-01-19 Thread Vlad Khorsun (JIRA) 
AV in fbclient when reading blob stored in incompatible encoding


 Key: CORE-5464
 URL: http://tracker.firebirdsql.org/browse/CORE-5464
 Project: Firebird Core
  Issue Type: Bug
  Components: API / Client Library
Affects Versions: 3.0.1, 4.0 Initial
Reporter: Vlad Khorsun


firebird>chcp 1251
Active code page: 1251

firebird>isql -ch win1251
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database 'inet://c:\temp\blf.fdb' default character set win1251;
SQL> create domain d_int int;
SQL> comment on domain d_int is 'я пишу кириллицей';
SQL> commit;
SQL> SELECT RDB$FIELD_NAME, RDB$SYSTEM_FLAG, RDB$DESCRIPTION  FROM RDB$FIELDS 
where RDB$DESCRIPTION is not null;

RDB$FIELD_NAME  RDB$SYSTEM_FLAG   RDB$DESCRIPTION
=== === =
D_INT 0   0:3
==
RDB$DESCRIPTION:
я пишу кириллицей
==

SQL> exit;

Now connect using incompatible with WIN1251 encoding and try to read domain 
description:

firebird>isql inet://c:\temp\blf.fdb -ch win1250
Database: inet://c:\temp\blf.fdb, User: SYSDBA
SQL> SELECT RDB$FIELD_NAME, RDB$SYSTEM_FLAG, RDB$DESCRIPTION  FROM RDB$FIELDS 
where RDB$DESCRIPTION is not null;

isql is crashed at this point


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

   

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

[Firebird-devel] CORE-782, CORE-742, Partial CORE-679

2017-01-19 Thread Si Carter 
Hi All,

Hopefully I have the right audience, apologies if not,

Some years ago I submitted the above suggestions.  I now have compiled and
tested a solution to most (excluding windows event log) which would still
satisfy my need in ISP for Firebird.

What is the best way to attempt to get these submitted into the current
build?

The easiest solution I have is minor change to gds__log and #define
FB_LOGFILENAME, coupled with changes to inline strings and some constants.

A more complex solutions, which I believe should have minimum impact, would
be to change inline strings, this involves changes to approximately 146
inline strings in multiple files.  But excludes debug string defined in the
following #ifdef's

VIO_trace
DEBUG
DEBUG_PROCS
DEBUG_VAL_VERBOSE
DEBUG_SIMILAR

The solution includes logging successful and unsuccessful login attempts,
which I also believe could be an option within the firebird.conf to prevent
any degradation of speed by adding a new option LogLevel (0 as is
currently, 1 includes login success and failure attempts).

any help appreciated.

regards

Si
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdotFirebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] CORE-782, CORE-742, Partial CORE-679

2017-01-19 Thread Roman Simakov 
2017-01-20 2:12 GMT+03:00 Si Carter :
> What is the best way to attempt to get these submitted into the current
> build?


I guess the best option to share your work is to make a fork of Fb git
repo and prepare pull request (one per feature).

-- 
Roman Simakov
Red Soft

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