[
https://issues.apache.org/jira/browse/DERBY-4013?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12665153#action_12665153
]
Dag H. Wanvik commented on DERBY-4013:
--------------------------------------
Thanks for checking the standard on the semantics here, Knut. Derby
does treat no default the same as a default of null, I find.
If this is strictly according to the standard, I am not sure. At
least, if there is no defined default, the null value should be used,
according to section 11.5 default clause, General Rule 3 e):
"Otherwise, S is set to the null value."
The way I read it, this is not technically a default, but rather the
null is used in lieu of a defined default. By that reasoning, the DROP
default should probably have failed if there is no defined default on
that column. Up to now, Derby makes no such distinction, since setting
a default back to "null" is the only way to get rid of another defined
default [*].
But I suggest we be lenient here, as is Postgres in your experiment,
and let DROP DEFAULT be a no-op if there is no default defined for a
column, otherwise we would have to start distinguishing between an
explicitly created "null" default (whose dropping would be legal) and
an implicit null default (by rule 3e above, in which case dropping the
default would be an error).
[*]
This can be seen by first making an explicit non-null default, and
then changing its value to null:
ij> alter table t alter i DEFAULT 1;
ij> select columndefault, columndefaultid from sys.syscolumns where
columnname='I';
COLUMNDEFAULT |COLUMNDEFAULTID
----------------------------------------------------
1 |286cc01e-011e-efd9-cb02-000003ffe058
ij> alter table t alter i DEFAULT null;
ij> select columndefault, columndefaultid from sys.syscolumns where
columnname='I';
COLUMNDEFAULT |COLUMNDEFAULTID
----------------------------------------------------
NULL |NULL
There no longer a UUID identifying to a defined default (i.e. it has been
dropped).
> Allow standard SQL syntax: ALTER TABLE ALTER [COLUMN] <col> SET DEFAULT
> <default>
> ---------------------------------------------------------------------------------
>
> Key: DERBY-4013
> URL: https://issues.apache.org/jira/browse/DERBY-4013
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1,
> 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Priority: Minor
> Fix For: 10.5.0.0
>
> Attachments: derby-4013.diff, derby-4013.stat, derby-4013b.diff,
> derby-4013b.stat, derby-4013c.diff, derby-4013c.stat
>
>
> Presently, the Derby syntax is ALTER TABLE ALTER [COLUMN] <col> [WITH]
> DEFAULT <default>.
> The "SET" keyword is not accepted, only an optional "WITH". It would be good
> to accept the standard syntax here as well.
> Cf. SQL 2003, section 11.12 <alter column definition> and section 11.13 <set
> column default clause>.
> Also, DROP DEFAULT is standard syntax not supported, so we should add that,
> too.
> Repro on trunk:
> $ java org.apache.derby.tools.ij
> ij version 10.5
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table t(i int default 0, j int);
> 0 rows inserted/updated/deleted
> ij> alter table t alter column j with default 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> 1 row inserted/updated/deleted
> ij> select * from t;
> I |J
> -----------------------
> 0 |1
> 1 row selected
> ij> alter table t alter column j default 2;
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> 1 row inserted/updated/deleted
> ij> select * from t;
> I |J
> -----------------------
> 0 |1
> 0 |2
> 2 rows selected
> ij> alter table t alter column j set default 3;
> ERROR 42X01: Syntax error: Encountered "set" at line 1, column 30.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
