|
Knut Anders Hatlen made changes - 06/Apr/09 06:23 AM
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. 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.
Rick Hillegas made changes - 07/Apr/09 07:37 PM
The patch looks fine to me, and I've verified that it fixes the reported problem. +1 to commit.
Rick Hillegas made changes - 08/Apr/09 12:59 PM
Tests ran cleanly for me. Committed derby-4146-01-aa-addColumnDescriptor.diff at subversion revision 763230.
Ported 763230 from trunk to 10.5 branch at subversion revision 763235. Thanks for the quick review, Knut.
Rick Hillegas made changes - 08/Apr/09 01:12 PM
Knut Anders Hatlen made changes - 08/Apr/09 01:20 PM
Thanks, Rick! Verified that it works on trunk and 10.5. Closing the issue.
Knut Anders Hatlen made changes - 08/Apr/09 01:21 PM
Myrna van Lunteren made changes - 04/May/09 06:24 PM
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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)