Cannot insert row when a generated column references an identity column

• Key: DERBY-4146
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Rick Hillegas
• Reporter: Knut Anders Hatlen
• Component/s: SQL
• Fix Version/s: 10.5.1.1, 10.6.0.0

Description

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.

Comments

[Knut Anders Hatlen]

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)

[Rick Hillegas]

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.

[Rick Hillegas]

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.

[Knut Anders Hatlen]

The patch looks fine to me, and I've verified that it fixes the reported problem. +1 to commit.

[Rick Hillegas]

Tests ran cleanly for me. Committed derby-4146-01-aa-addColumnDescriptor.diff at subversion revision 763230.

[Rick Hillegas]

Ported 763230 from trunk to 10.5 branch at subversion revision 763235. Thanks for the quick review, Knut.

[Knut Anders Hatlen]

Thanks, Rick! Verified that it works on trunk and 10.5. Closing the issue.