Re: [jira] Updated: (DERBY-353) It is desirable to have IDENTITY_VAL_LOCAL() function return last recent user specified value or system generated value for BY DEFAULT identity columns.

[Satheesh Bandaram]

This patch is committed. Thanks Narayanan for the patch and Rick for the reviews. Satheesh Sending        java\engine\org\apache\derby\impl\sql\execute\InsertResultSet.java Sending        java\testing\org\apache\derbyTesting\functionTests\master\DerbyNet\autoGeneratedJdbc30.out Sending        java\testing\org\apache\derbyTesting\functionTests\master\DerbyNetClient\autoGeneratedJdbc30.out Sending        java\testing\org\apache\derbyTesting\functionTests\master\autoGeneratedJdbc30.out Sending        java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out Transmitting file data ..... Committed revision 267331. V.Narayanan (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-353?page=all ] V.Narayanan updated DERBY-353: ------------------------------ Attachment: patch353.diff Hi, As Rick has pointed out the problem was because of a missed cannon only. The patch now includes the missed changes. Narayanan It is desirable to have IDENTITY_VAL_LOCAL() function return last recent user specified value or system generated value for BY DEFAULT identity columns. -------------------------------------------------------------------------------------------------------------------------------------------------------- Key: DERBY-353 URL: http://issues.apache.org/jira/browse/DERBY-353 Project: Derby Type: Bug Components: SQL Versions: 10.1.1.0 Environment: Generic Reporter: Satheesh Bandaram Attachments: patch353.diff Derby was recently enhanced to support BY DEFAULT identity column. While the behavior of this feature is not documented yet, I think, it is desirable for IDENTITY_VAL_LOCAL() function, that is used to retrieve last single statement insert value for identity column, to return user specified value for the default column. For GENERATED ALWAYS identity columns, this issue doesn't apply, since users can't provide a value. But for GENERATED BY DEFAULT identity columns, users can optionally specify a value. IDENTITY_VAL_LOCAL() function should return this value. Derby currently doesn't support this behavior. ij> create table tauto ( i int generated by default as identity, j int, k int); 0 rows inserted/updated/deleted ij> insert into tauto (j,k) values (1,1); 1 row inserted/updated/deleted ij> values identity_val_local(); 1 ------------------------------- 1 1 row selected ij> insert into tauto (j,k) values (1,1); 1 row inserted/updated/deleted ij> values identity_val_local(); 1 ------------------------------- 2 1 row selected ij> insert into tauto values (5,1,1); 1 row inserted/updated/deleted ij> values identity_val_local(); 1 ------------------------------- 2 <<<<<<<<<<<<<<============= This needs be '5' 1 row selected ij> select * from tauto; I |J |K ----------------------------------- 1 |1 |1 2 |1 |1 5 |1 |1 3 rows selected