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 "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2009/04/05 22:47:13 UTC
[jira] Created: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Cannot insert row when a generated column references an identity column
-----------------------------------------------------------------------
Key: DERBY-4146
URL: https://issues.apache.org/jira/browse/DERBY-4146
Project: Derby
Issue Type: Bug
Affects Versions: 10.5.1.0
Reporter: Knut Anders Hatlen
ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
0 rows inserted/updated/deleted
ij> insert into t values (default, default);
ERROR 23502: Column 'C2' cannot accept a NULL value.
Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12696118#action_12696118 ]
Rick Hillegas commented on DERBY-4146:
--------------------------------------
Thanks for finding this bug, Knut. As a workaround, you can explicitly add the column names to the insert statement. The following works:
insert into t( c1, c2 ) values (default, default);
I think that I agree with you and we should allow tables with this shape. However, I'm a little unclear about how to interpret the SQL Standard, part 2, section 4.14.8, which says that generation expressions may not reference other generated columns. For the purposes of this prohibition, I think that the standard does not mean to include identity columns as a kind of generated column. But if someone else has an opinion, please share it.
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12695913#action_12695913 ]
Knut Anders Hatlen commented on DERBY-4146:
-------------------------------------------
Full stack trace:
ERROR 23502: Column 'C2' cannot accept a NULL value.
at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:286)
at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeColumn(NormalizeResultSet.java:329)
at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeRow(NormalizeResultSet.java:369)
at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java:180)
at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:127)
at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:494)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:505)
at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:347)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:245)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:210)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:177)
at org.apache.derby.impl.tools.ij.Main.main(Main.java:73)
at org.apache.derby.tools.ij.main(ij.java:59)
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12697009#action_12697009 ]
Rick Hillegas commented on DERBY-4146:
--------------------------------------
Ported 763230 from trunk to 10.5 branch at subversion revision 763235. Thanks for the quick review, Knut.
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
> Assignee: Rick Hillegas
> Attachments: derby-4146-01-aa-addColumnDescriptor.diff
>
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4146:
--------------------------------------
Fix Version/s: 10.6.0.0
10.5.1.1
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
> Assignee: Rick Hillegas
> Fix For: 10.5.1.1, 10.6.0.0
>
> Attachments: derby-4146-01-aa-addColumnDescriptor.diff
>
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas resolved DERBY-4146.
----------------------------------
Resolution: Fixed
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
> Assignee: Rick Hillegas
> Attachments: derby-4146-01-aa-addColumnDescriptor.diff
>
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-4146.
-------------------------------------
Thanks, Rick! Verified that it works on trunk and 10.5. Closing the issue.
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
> Assignee: Rick Hillegas
> Fix For: 10.5.1.1, 10.6.0.0
>
> Attachments: derby-4146-01-aa-addColumnDescriptor.diff
>
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4146:
--------------------------------------
Component/s: SQL
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12696964#action_12696964 ]
Knut Anders Hatlen commented on DERBY-4146:
-------------------------------------------
The patch looks fine to me, and I've verified that it fixes the reported problem. +1 to commit.
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
> Attachments: derby-4146-01-aa-addColumnDescriptor.diff
>
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas reassigned DERBY-4146:
------------------------------------
Assignee: Rick Hillegas
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
> Assignee: Rick Hillegas
> Attachments: derby-4146-01-aa-addColumnDescriptor.diff
>
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-4146:
---------------------------------
Attachment: derby-4146-01-aa-addColumnDescriptor.diff
Attaching derby-4146-01-aa-addColumnDescriptor.diff. This fixes the bug.
I compared the bound trees for the following queries. The first query failed and the second succeeded:
insert into t_4146 values ( default, default )
insert into t_4146 (c1, c2) values ( default, default )
On the second query, the generated result column had more information, viz., it was bound to a column descriptor. For the first query, the column descriptor was being bound for identity columns (but not generated columns) in ResultColumnList.forbidOverrides(). The fix was to bind a column descriptor there for generated columns as well.
So far no-one has offered a different interpretation of the SQL Standard. If the tests run cleanly, I intend to commit this patch unless someone objects to my interpretation.
Touches the following files:
M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Fix as described above.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
Added regression tests for this bug.
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
> Attachments: derby-4146-01-aa-addColumnDescriptor.diff
>
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4146) Cannot insert row when a generated
column references an identity column
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4146?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12697006#action_12697006 ]
Rick Hillegas commented on DERBY-4146:
--------------------------------------
Tests ran cleanly for me. Committed derby-4146-01-aa-addColumnDescriptor.diff at subversion revision 763230.
> Cannot insert row when a generated column references an identity column
> -----------------------------------------------------------------------
>
> Key: DERBY-4146
> URL: https://issues.apache.org/jira/browse/DERBY-4146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.0
> Reporter: Knut Anders Hatlen
> Assignee: Rick Hillegas
> Attachments: derby-4146-01-aa-addColumnDescriptor.diff
>
>
> ij> create table t (c1 int generated always as identity, c2 generated always as (c1));
> 0 rows inserted/updated/deleted
> ij> insert into t values (default, default);
> ERROR 23502: Column 'C2' cannot accept a NULL value.
> Preferably, this should work. Alternatively, if it is not supposed to work, it should fail when the table is created and the error message should state that a generated column is not allowed to reference an identity column.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.