[
https://issues.apache.org/jira/browse/DERBY-6852?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15390737#comment-15390737
]
Bryan Pendleton commented on DERBY-6852:
----------------------------------------
Rick, can you please help us with the SQL Standard grammar a bit more? As I
read the SQL 2003
spec, in section 11.4 <column definition> it says:
{code}
<identity column specification> ::=
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ <left paren> <common sequence generator options> <right paren> ]
{code}
where "<common sequence generator options>", I believe, come from section 11.62
<sequence generator definition>.
The "<common sequence generator options>" seem to closely match the work you
did in DERBY-712, as you described in the SequenceGenerator.html that is
attached
to DERBY-712.
BUT, here's where I get confused.
The current (10.12) Derby generated column spec at
{quote}
https://db.apache.org/derby/docs/10.12/ref/rrefsqlj37836.html
{quote}
gives our current syntax as:
{code}
[ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ ( START WITH integerConstant
[ , INCREMENT BY integerConstant ] ) ] ] ]
{code}
which doesn't seem to match the standard, due to the presence of that
**comma**, and due to the (apparent) fact that, if you supply both START WITH
and INCREMENT BY, then they have to be in that order.
That is, that
{code}
CREATE TABLE t1 (c1 INT GENERATED BY DEFAULT AS IDENTITY
( INCREMENT BY 2
START WITH 3 ));
{code}
would be an illegal table definition (and, indeed, Derby 10.12 rejects it, with
or without a comma between the two clauses).
But, by the SQL 2003 standard, isn't that supposed to be legal?
The root of my question is that it seems like the answer to Danoja's current
parsing problem is that the parsing of the auto-increment information in the
column definition in sqlgrammar.jj
(that is, the autoIncrementBeginEnd() and generationClause() code) should be
more-or-less-directly sharing the same parsing code as the CREATE SEQUENCE
parsing logic in the sequenceDefinition() code in sqlgrammar.jj.
But, it seems like that would be an incompatible, breaking change to our
column definition syntax parser, because right now the two syntaxes in Derby
are not compatible, though we want them to be.
Is this making any sense?
> Allow identity columns to cycle (as defined in SQL:2003)
> --------------------------------------------------------
>
> Key: DERBY-6852
> URL: https://issues.apache.org/jira/browse/DERBY-6852
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Peter Hansson
> Assignee: Danoja Dias
> Attachments: derby-6852_1.diff, derby_6852_2.diff, script.sql
>
>
> Currently when an IDENTITY column reaches its maximum value it will produce
> an error.
> For tables that are used as 'transaction logs' or 'event logs' it often makes
> sense to let the table automatically start over with the first identity value
> again when the max is reached. This would be similar to the CYCLE option on
> Oracle's SEQUENCE and as defined in SQL:2003. And Derby is probably used
> quite often for this purpose, I guess, perhaps even more than other RDBMSs.
> At the moment every developer have to program their own logic for this.
> I propose to introduce the CYCLE option.
> The idea of CYCLE is based on the assumption that there's been a prior
> cleanup in the table rows so that it will be possible to re-use ids that have
> been used previously. If that is not the case - and a rollover happens - then
> a duplicate value error will occur. In this sense it can be argued that the
> CYCLE option will trade a _certain_ error for a _potential_ error. Most Derby
> users would possibly gladly accept such a bargain. In other words: This
> option will greatly enhance the usability of IDENTITY columns.
> The current implementation of IDENTITY columns SQL grammar in Derby is a
> subset of the SQL:2003 standard which is the first of the SQL standards to
> define IDENTITY columns. Interestingly the standard also defines the CYCLE
> option but this was never implemented in Derby. Also see [SQL-99 and SQL-2003
> features mapped to Derby|https://wiki.apache.org/db-derby/SQLvsDerbyFeatures]
> (scroll to T174).
> In other words: The proposal is simply to implement CYCLE as defined in
> SQL:2003.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
