Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
> What percent of our users cares about auto-generated domains? Percent? Too small to measure (less than 20 people in the world!) Sean -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
On 04/03/15 12:21, Dimitry Sibiryakov wrote: > 03.04.2015 11:15, Alex Peshkoff wrote: >> Agree with this logic, but it will be useful to add a warning, i.e. >> >> SQL> create table t(a notnull); >> SQL> alter table t alter a drop not null; >> Warning: Column a based on not null domain >> SQL> >> >> Or something like this, may be text is not ideal. > Column is always based on domain. If it is auto-generated domain, ALTER > TABLE can drop > constraint from it without problem. > What percent of our users cares about auto-generated domains? On my mind presence of them is just implementation detail. -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
03.04.2015 11:15, Alex Peshkoff wrote: > Agree with this logic, but it will be useful to add a warning, i.e. > > SQL> create table t(a notnull); > SQL> alter table t alter a drop not null; > Warning: Column a based on not null domain > SQL> > > Or something like this, may be text is not ideal. Column is always based on domain. If it is auto-generated domain, ALTER TABLE can drop constraint from it without problem. -- WBR, SD. -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
On 04/03/15 03:38, Adriano dos Santos Fernandes wrote: > On 02-04-2015 22:13, Claudio Valderrama C. wrote: >>> -Original Message- >>> From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com] >>> Sent: Miércoles, 01 de Abril de 2015 22:29 >>> >>> On 31-03-2015 07:41, Alex Peshkoff wrote: I.e. we can not override NOT NULL constraint set in the domain. >>> Changed. Thanks. >> Should we see an error message? >> >> F:\fb3dev\fbbuild\firebird30\output_Win32>isql -user sysdba -pass masterkey >> Use CONNECT or CREATE DATABASE to specify a database >> SQL> create database "nulls.fdb" user "sysdba" password "masterkey"; >> SQL> create domain notnull int not null; >> SQL> create table t(a notnull); >> SQL> alter table t alter a drop not null; >> SQL> alter table t alter a drop not null; >> SQL> show table t; >> A (NOTNULL) INTEGER Not Null >> SQL> >> >> The not nullable condition is not overriden, but the engine simply ignores >> the command. >> > If an error is missing, I think it's just about dropping a non existing > NOT NULL constraint of the field, like this: > > SQL> create table t1 (n1 integer); > SQL> alter table t1 alter n1 drop default; > Statement failed, SQLSTATE = 42000 > unsuccessful metadata update > -ALTER TABLE T1 failed > -Local column N1 doesn't have a default > > Otherwise, it's just as we talked. You can drop the column not null if > it's based on a not null domain, but it will still be not null via the > domain. > > But you can then change the domain after it. > Agree with this logic, but it will be useful to add a warning, i.e. SQL> create table t(a notnull); SQL> alter table t alter a drop not null; Warning: Column a based on not null domain SQL> Or something like this, may be text is not ideal. -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
On 02-04-2015 22:13, Claudio Valderrama C. wrote: >> -Original Message- >> From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com] >> Sent: Miércoles, 01 de Abril de 2015 22:29 >> >> On 31-03-2015 07:41, Alex Peshkoff wrote: >>> >>> I.e. we can not override NOT NULL constraint set in the domain. >>> >> >> Changed. Thanks. > > Should we see an error message? > > F:\fb3dev\fbbuild\firebird30\output_Win32>isql -user sysdba -pass masterkey > Use CONNECT or CREATE DATABASE to specify a database > SQL> create database "nulls.fdb" user "sysdba" password "masterkey"; > SQL> create domain notnull int not null; > SQL> create table t(a notnull); > SQL> alter table t alter a drop not null; > SQL> alter table t alter a drop not null; > SQL> show table t; > A (NOTNULL) INTEGER Not Null > SQL> > > The not nullable condition is not overriden, but the engine simply ignores > the command. > If an error is missing, I think it's just about dropping a non existing NOT NULL constraint of the field, like this: SQL> create table t1 (n1 integer); SQL> alter table t1 alter n1 drop default; Statement failed, SQLSTATE = 42000 unsuccessful metadata update -ALTER TABLE T1 failed -Local column N1 doesn't have a default Otherwise, it's just as we talked. You can drop the column not null if it's based on a not null domain, but it will still be not null via the domain. But you can then change the domain after it. Adriano -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
> -Original Message- > From: Adriano dos Santos Fernandes [mailto:adrian...@gmail.com] > Sent: Miércoles, 01 de Abril de 2015 22:29 > > On 31-03-2015 07:41, Alex Peshkoff wrote: > > > > I.e. we can not override NOT NULL constraint set in the domain. > > > > Changed. Thanks. Should we see an error message? F:\fb3dev\fbbuild\firebird30\output_Win32>isql -user sysdba -pass masterkey Use CONNECT or CREATE DATABASE to specify a database SQL> create database "nulls.fdb" user "sysdba" password "masterkey"; SQL> create domain notnull int not null; SQL> create table t(a notnull); SQL> alter table t alter a drop not null; SQL> alter table t alter a drop not null; SQL> show table t; A (NOTNULL) INTEGER Not Null SQL> The not nullable condition is not overriden, but the engine simply ignores the command. C. -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
On 31-03-2015 07:41, Alex Peshkoff wrote: > > I.e. we can not override NOT NULL constraint set in the domain. > Changed. Thanks. Adriano -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
31.03.2015 13:41, Alex Peshkoff wrote: > According to SQL specification: > > Let C be a column of a base table T. C is known not nullable if and only > if _at least one_ of the following is true: > > — C is based on a domain that has a domain constraint that is not > deferrable and that simply contains a condition> that is a that is a > readily-known-not-null condition for VALUE. > Thanks, I was looking for this definition but failed to find it. In this case, I'd suggest to follow the specification. Dmitry -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
On 03/31/15 12:56, Dmitry Yemanov wrote:
> 31.03.2015 07:20, Adriano dos Santos Fernandes wrote:
>> Currently there is inconsistencies in fields [NOT] NULL flag on its
>> (field) flag does not matches the domain.
>>
>> Use CONNECT or CREATE DATABASE to specify a database
>> SQL> create database 'z.fdb';
>> SQL> create domain d1n integer not null;
>> SQL> create table t1 (n1 d1n);
>> SQL>
>> SQL> insert into t1 values (null); -- ok
>> Statement failed, SQLSTATE = 23000
>> validation error for column "T1"."N1", value "*** null ***"
>> SQL>
>> SQL> alter table t1 alter n1 null;
>> SQL>
>> SQL> show table t1;
>> N1 (D1N) INTEGER Not Null
>> SQL>
>> SQL> insert into t1 values (null);
> Before discussing the problem, I think we should change the syntax. The
> SQL spec suggests ALTER [COLUMN] N1 {SET | DROP} NOT NULL instead of our
> current ALTER [COLUMN] N1 [NOT] NULL. And I like this, as formally NOT
> NULL is a constraint. BTW, we seem to miss a DDL ability to change the
> column-level CHECK constraint.
>
> With SET NOT NULL and DROP NOT NULL, we don't just set the field flag
> but explicitly declare that we want a constraint or we don't want a
> constraint. So even if a constraint was inherited from a domain, I think
> this should mean to change the constraint. In other words, the engine is
> correct in treating the new definition as an override.
According to SQL specification:
Let C be a column of a base table T. C is known not nullable if and only
if _at least one_ of the following is true:
— C is based on a domain that has a domain constraint that is not
deferrable and that simply contains a that is a that is a
readily-known-not-null condition for VALUE.
I.e. we can not override NOT NULL constraint set in the domain.
--
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
31.03.2015 07:20, Adriano dos Santos Fernandes wrote:
>
> Currently there is inconsistencies in fields [NOT] NULL flag on its
> (field) flag does not matches the domain.
>
> Use CONNECT or CREATE DATABASE to specify a database
> SQL> create database 'z.fdb';
> SQL> create domain d1n integer not null;
> SQL> create table t1 (n1 d1n);
> SQL>
> SQL> insert into t1 values (null); -- ok
> Statement failed, SQLSTATE = 23000
> validation error for column "T1"."N1", value "*** null ***"
> SQL>
> SQL> alter table t1 alter n1 null;
> SQL>
> SQL> show table t1;
> N1 (D1N) INTEGER Not Null
> SQL>
> SQL> insert into t1 values (null);
Before discussing the problem, I think we should change the syntax. The
SQL spec suggests ALTER [COLUMN] N1 {SET | DROP} NOT NULL instead of our
current ALTER [COLUMN] N1 [NOT] NULL. And I like this, as formally NOT
NULL is a constraint. BTW, we seem to miss a DDL ability to change the
column-level CHECK constraint.
With SET NOT NULL and DROP NOT NULL, we don't just set the field flag
but explicitly declare that we want a constraint or we don't want a
constraint. So even if a constraint was inherited from a domain, I think
this should mean to change the constraint. In other words, the engine is
correct in treating the new definition as an override.
Dmitry
--
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Semantics of field [NOT] NULL flag based on domains
On Tue, 31 Mar 2015 01:20:39 -0300, Adriano dos Santos Fernandes wrote: > Hi! > > Currently there is inconsistencies in fields [NOT] NULL flag on its > (field) flag does not matches the domain. > > Use CONNECT or CREATE DATABASE to specify a database > SQL> create database 'z.fdb'; > SQL> create domain d1n integer not null; > SQL> create table t1 (n1 d1n); > SQL> > SQL> insert into t1 values (null); -- ok > Statement failed, SQLSTATE = 23000 > validation error for column "T1"."N1", value "*** null ***" > SQL> > SQL> alter table t1 alter n1 null; > SQL> > SQL> show table t1; > N1 (D1N) INTEGER Not Null > SQL> > SQL> insert into t1 values (null); > > So after the field flag is changed, ISQL thinks a field reported as NULL > will use the domain's flag. > > But the engine treats the field flag as an override. > > Whatever you choose who is correct, we have problems. > > If setting a field to NULLable means setting its > RDB$RELATION_FIELDS.RDB$NULL_FLAG to NULL and let the domain > (RDB$FIELDS.RDB$NULL_FLAG) flag be used, the engine must be fixed. > > If setting a field to NULLable means setting its > RDB$RELATION_FIELDS.RDB$NULL_FLAG to say it's NULLable, then ISQL must > be fixed and more. Then how one would revert a field to use the domain's > flag? Would need a new command? ALTER TABLE T1 ALTER N1 DOMAIN_NULL_FLAG > (argh!!) > > Decision of this has more implications, like when changing the flag of a > domain used in a primary key. I'd say a field is only allowed to further restrict its domain definition, not relax its constraints. So if the domain is NOT NULL, then the field can never allow NULL, but if the domain allows NULL, then the field can restrict to NOT NULL. Mark -- Dive into the World of Parallel Programming The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
