create table fails when a constraint defintion follows a column default

• Key: DERBY-331
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Dag H. Wanvik
• Reporter: Gin Ng
• Component/s: SQL
• Affects Version/s: 10.0.2.1
• Fix Version/s: 10.1.3.1, 10.2.1.6

Description

The following statement fails with syntax error.

ij> create table t (a int not null, b int default 0, unique (a));
ERROR 42X01: Syntax error: Encountered "0" at line 1, column 47.

And it will work if I remove either default 0 or unique(a).

Comments

[Satheesh Bandaram]

That doesn't seem to be the correct syntax. Derby supports column level constraints, in the format:

ij> create table t ( a int not null unique, b int default 0);
0 rows inserted/updated/deleted

Or table level constraints, like:

ij> create table t (a int not null, b int default 0, constraint tk unique (a));
0 rows inserted/updated/deleted

See documentation at:
http://incubator.apache.org/derby/docs/ref/rrefsqlj13590.html

[Gin Ng]

The constraint name is optional. See http://incubator.apache.org/derby/docs/ref/rrefsqlj16095.html.
And my example is in the same form of the second example in the document above except the default value.

-- the table-level primary key definition allows you to
-- include two columns in the primary key definition:
CREATE TABLE SAMP.SCHED
(
CLASS_CODE CHAR(7) NOT NULL,
DAY SMALLINT NOT NULL,
STARTING TIME,
ENDING TIME,
PRIMARY KEY (CLASS_CODE, DAY)
);

[Dag H. Wanvik]

This seems to be a look-ahead problem in the parser. I think I have a patch for it, just need to run all tests.

[Dag H. Wanvik]

This patch fixes a look-ahead problem in the parser: In the method DB2DefaultOption,
the look-ahead for miscBuiltins is too liberal: it mistakenly thinks it has found a
builtin in this case. I made the look-ahead more specific by adding a check against comma.
 I have extended the test lang/columnDefaults.sql with this case.
I have run derbyall with no errors.

[Dag H. Wanvik]

Updated this patch which additional comments, according to Mamta's suggestion.
Ran the test lang/columnDefaults.sql again to verify.

[Daniel John Debrunner]

Committed revision 209842 (331b.diff)
Sorry for missing this one, noticed it the other day when looking at the release notes, but didn't get back to it.

[Mayuresh Nirhali]

This bug is 10.1 branch porting candidate for 10.1.3 release.

I merged the changes (209842) into my fresh 10.1 branch child. The changes applied cleanly.
I rebuilt the workspace and ran the corresponding test, lang,columnDefaults.sql. The test passed.
derbyall also did not show any relevant failures.

I think the already attached patch can be directly merg/committed to 10.1 branch.

[Knut Anders Hatlen]

This issue is marked as fixed in 10.1.3.0, but it is not. Reopening to merge to the 10.1 branch.

[Knut Anders Hatlen]

Committed into 10.1 with revision 413299.