syntax for generated-column-spec: bug in doc or code?

["Dag H. Wanvik" <Da...@Sun.COM>]

Hi,

The 10.2 reference manual states
(http://db.apache.org/derby/docs/10.2/ref/rrefsqlj37836.html):

> GENERATED ALWAYS
> 
>     An identity column that is GENERATED ALWAYS will increment the
>     default value on every insertion and will store the incremented
>     value into the column. Unlike other defaults, you cannot insert a
>     value directly into or update an identity column that is GENERATED
>     ALWAYS. Instead, either specify the DEFAULT keyword when inserting
>     into the identity column, or leave the identity column out of the
>     insertion column list altogether. For example:
> 
>     create table greetings
>     	(i int generated always as identity, ch char(50));
>     insert into greetings values (DEFAULT, 'hello');
>     insert into greetings(ch) values ('bonjour');

However, when I try this I get an error:

ij> create table greetings ( i int generated always as identity, ch char(50));
0 rows inserted/updated/deleted
ij> insert into greetings values (DEFAULT, 'hello');
1 row inserted/updated/deleted
ij> insert into greetings values ('bonjour');
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.

Is this a documentation error or is this a bug?  Did not find any open
issue on this.

Dag

Re: syntax for generated-column-spec: bug in doc or code?

[Bryan Pendleton <bp...@amberpoint.com>]

> When I try it, 0 is flagged as illegal, as I would expect:
> 
> ij> create table f (i int generated by default as identity (start with 1, increment by 0), j int);
> 
> ERROR 42Z21: Invalid increment specified for identity for column 'I'. Increment cannot be zero.  

This behavior seems reasonable to me. I can't think of a reason that
I would want to set INCREMENT BY to 0.

I would prefer to change the docs in this case, than to change the code.

thanks,

bryan

Re: syntax for generated-column-spec: bug in doc or code?

["Dag H. Wanvik" <Da...@Sun.COM>]

Bryan Pendleton <bp...@amberpoint.com> writes:

>>>     insert into greetings(ch) values ('bonjour');
>> ij> insert into greetings values ('bonjour');
>
> These two don't look identical. The second one is missing (ch).

Thanks Bryan! I think I was blind-sided by my expectation here.

I am puzzled by the documentation's mention of 0 as a legal increment
value:

> By default, the initial value of an identity column is 1, and the
> amount of the increment is 1. You can specify non-default values for
> both the initial value and the interval amount when you define the
> column with the key words STARTS WITH and INCREMENT BY. And if you
> specify a negative number for the increment value, Derby decrements
> the value with each insert. If this value is 0, or positive, Derby
                              ******************
> increments the value with each insert.

When I try it, 0 is flagged as illegal, as I would expect:

ij> create table f (i int generated by default as identity (start with 1, increment by 0), j int);

ERROR 42Z21: Invalid increment specified for identity for column 'I'. Increment cannot be zero.  

A bug, or am I missing something?

Dag

Re: syntax for generated-column-spec: bug in doc or code?

[Bryan Pendleton <bp...@amberpoint.com>]

Dag H. Wanvik wrote:
>>     insert into greetings(ch) values ('bonjour');

> ij> insert into greetings values ('bonjour');

These two don't look identical. The second one is missing (ch).

thanks,

bryan