You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Oleksandr Alesinskyy (JIRA)" <ji...@apache.org> on 2008/04/09 11:13:24 UTC
[jira] Created: (DERBY-3609) Wrong functionality of auto-generated
keys support
Wrong functionality of auto-generated keys support
--------------------------------------------------
Key: DERBY-3609
URL: https://issues.apache.org/jira/browse/DERBY-3609
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.3.2.1
Environment: N/A
Reporter: Oleksandr Alesinskyy
Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3609) Wrong functionality of
auto-generated keys support
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603980#action_12603980 ]
Rick Hillegas commented on DERBY-3609:
--------------------------------------
The correct behavior (if DatabaseMetaData.supportsGetGeneratedKeys() returns true) is defined in the JDBC3 spec, section 13.6 (Retrieving Auto Generated Keys).
> Wrong functionality of auto-generated keys support
> --------------------------------------------------
>
> Key: DERBY-3609
> URL: https://issues.apache.org/jira/browse/DERBY-3609
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.3.2.1
> Environment: N/A
> Reporter: Oleksandr Alesinskyy
> Attachments: TestGeneratedKeys.java
>
>
> Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
> Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3609) Wrong functionality of auto-generated
keys support
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3609:
----------------------------------
Derby Categories: [High Value Fix]
> Wrong functionality of auto-generated keys support
> --------------------------------------------------
>
> Key: DERBY-3609
> URL: https://issues.apache.org/jira/browse/DERBY-3609
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.3.2.1
> Environment: N/A
> Reporter: Oleksandr Alesinskyy
> Attachments: TestGeneratedKeys.java
>
>
> Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
> Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3609) Wrong functionality of
auto-generated keys support
Posted by "Bernt M. Johnsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12587198#action_12587198 ]
Bernt M. Johnsen commented on DERBY-3609:
-----------------------------------------
Formally, Derby does not support getGeneratedKeys since DatabaseMetaData.supportsGetGeneratedKeys() returns false. However, Statement.getGeneratedKeys() is partially implemented, and behaves very similar to IDENTITY_VAL_LOCAL() since it will only return a meaningful result when an single row insert is done with INSERT...VALUES. However, you have to specify Statement.RETURN_GENERATED_KEYS, e.g. like this
s.executeUpdate("insert into t(i) values (1)", Statement.RETURN_GENERATED_KEYS);
I agree that returning null from getGeneratedKeys is not according to spec and that an ResultSet with no rows would be more appropriate. Also, the docs for "Autogenerated keys" in the ref.man. would need a rewrite to be comprehendable. Another quirk is that getGeneratedKeys() will return a resultset with one row with an SQL null-value if the statement was an insert of multiple rows, and not like DENTITY_VAL_LOCAL() which returns the value of the generated key from the last single-row insert.
> Wrong functionality of auto-generated keys support
> --------------------------------------------------
>
> Key: DERBY-3609
> URL: https://issues.apache.org/jira/browse/DERBY-3609
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.3.2.1
> Environment: N/A
> Reporter: Oleksandr Alesinskyy
>
> Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
> Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3609) Wrong functionality of auto-generated
keys support
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-3609:
--------------------------------------
Issue & fix info: [High Value Fix, Repro attached] (was: [High Value Fix])
Urgency: Normal
Bug behavior facts: [Deviation from standard]
Triaged for 10.5.2.
> Wrong functionality of auto-generated keys support
> --------------------------------------------------
>
> Key: DERBY-3609
> URL: https://issues.apache.org/jira/browse/DERBY-3609
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.3.2.1
> Environment: N/A
> Reporter: Oleksandr Alesinskyy
> Attachments: TestGeneratedKeys.java
>
>
> Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
> Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3609) Wrong functionality of auto-generated
keys support
Posted by "Oleksandr Alesinskyy (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Oleksandr Alesinskyy updated DERBY-3609:
----------------------------------------
Attachment: TestGeneratedKeys.java
Source of above mentioned test programm
> Wrong functionality of auto-generated keys support
> --------------------------------------------------
>
> Key: DERBY-3609
> URL: https://issues.apache.org/jira/browse/DERBY-3609
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.3.2.1
> Environment: N/A
> Reporter: Oleksandr Alesinskyy
> Attachments: TestGeneratedKeys.java
>
>
> Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
> Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3609) Wrong functionality of
auto-generated keys support
Posted by "Oleksandr Alesinskyy (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12587233#action_12587233 ]
Oleksandr Alesinskyy commented on DERBY-3609:
---------------------------------------------
Regrettably, after INSERT...SELECT returned resultset contains not null as you stated, but key generated by last single row INSERT..VALUES on the same table.
This behavior is completely misleading. It is bad enough for IDENTITY_VAL_LOCAL(), but (IMHO) absolutely unacceptable for getGeneratedKeys().
And here is log from test programm that proves my point:
---------------------------------------------------------------------------
insert values has generated id 1, is null false
insert as select same table has inserted 1 rows
insert as select from same table has generated id 1, is null false
insert as select from another table has inserted 1 rows
insert as select from another has generated id 1, is null false
MUMU contents id 1, is null false
MUMU contents id 2, is null false
MUMU contents id 3, is null false
Done.
------------------------------------------------------------------------------
Progam source is attached
> Wrong functionality of auto-generated keys support
> --------------------------------------------------
>
> Key: DERBY-3609
> URL: https://issues.apache.org/jira/browse/DERBY-3609
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.3.2.1
> Environment: N/A
> Reporter: Oleksandr Alesinskyy
>
> Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
> Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3609) Wrong functionality of
auto-generated keys support
Posted by "Oleksandr Alesinskyy (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603985#action_12603985 ]
Oleksandr Alesinskyy commented on DERBY-3609:
---------------------------------------------
Exactly,. and its requires support for multirow inserts, namely
"In some cases, such as in an insert select statement, more than one key may be
returned. The ResultSet object returned by getGeneratedKeys will contain a
row for each key that a statement generated. If no keys are generated, an empty
result set will be returned."
> Wrong functionality of auto-generated keys support
> --------------------------------------------------
>
> Key: DERBY-3609
> URL: https://issues.apache.org/jira/browse/DERBY-3609
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.3.2.1
> Environment: N/A
> Reporter: Oleksandr Alesinskyy
> Attachments: TestGeneratedKeys.java
>
>
> Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
> Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3609) Wrong functionality of
auto-generated keys support
Posted by "shlomi v. (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12688783#action_12688783 ]
shlomi v. commented on DERBY-3609:
----------------------------------
Hey,
is there any progress on this issue?
is there any way around this? like perhaps get the next n identity numbers for the next n inserts?
> Wrong functionality of auto-generated keys support
> --------------------------------------------------
>
> Key: DERBY-3609
> URL: https://issues.apache.org/jira/browse/DERBY-3609
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.3.2.1
> Environment: N/A
> Reporter: Oleksandr Alesinskyy
> Attachments: TestGeneratedKeys.java
>
>
> Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
> Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3609) Wrong functionality of
auto-generated keys support
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12688982#action_12688982 ]
Kathey Marsden commented on DERBY-3609:
---------------------------------------
>is there any progress on this issue?
No, I'm sorry to say and I don't know of a work around.
> Wrong functionality of auto-generated keys support
> --------------------------------------------------
>
> Key: DERBY-3609
> URL: https://issues.apache.org/jira/browse/DERBY-3609
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.3.2.1
> Environment: N/A
> Reporter: Oleksandr Alesinskyy
> Attachments: TestGeneratedKeys.java
>
>
> Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.").
> Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported").
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.