[ http://issues.apache.org/jira/browse/DERBY-1089?page=comments#action_12369957 ]
Michael Hackett commented on DERBY-1089: ---------------------------------------- I think I ran into the same issue today, and when I searched and found this entry, your explanation of the problem helped me come up with a work-around, at least for my situation. If you can split your insert into two stages, first writing the joined data to a temporary table (which doesn't have a generated column), and then copying it out to the destination, that may work. Not an ideal solution by any means, but at least it gets past the roadblock. Thanks, Mark, for isolating the conditions so well! I hope that will allow someone to track down the problem. > Derby fails inserting a join into a table with a generated column > ----------------------------------------------------------------- > > Key: DERBY-1089 > URL: http://issues.apache.org/jira/browse/DERBY-1089 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.1.2.1 > Environment: WinXP > Reporter: Mark Boylan > > I've been having a problem inserting the result of a join into a table with a > generated column. If I rephrase the join clause into a where clause, the > problem goes away. And it only seems to happen if the target table has a > generated column. Unfortunately, the join that I want to do in my application > is pretty complex so I don't think I can rephrase it. But here's a very > simplified example of what I'm talking about: > ij version 10.1 > ij> connect 'jdbc:derby:test;create=true'; > ij> create table source ( > source_id int not null primary key > ); > 0 rows inserted/updated/deleted > ij> insert into source values (0); > insert into source values (1); > insert into source values (2); > insert into source values (3); > insert into source values (4); > insert into source values (5); > insert into source values (6); > insert into source values (7); > insert into source values (8); > insert into source values (9); > 1 row inserted/updated/deleted > ij> 1 row inserted/updated/deleted > ij> 1 row inserted/updated/deleted > ij> 1 row inserted/updated/deleted > ij> 1 row inserted/updated/deleted > ij> 1 row inserted/updated/deleted > ij> 1 row inserted/updated/deleted > ij> 1 row inserted/updated/deleted > ij> 1 row inserted/updated/deleted > ij> 1 row inserted/updated/deleted > ij> create table dest ( > dest_id int not null primary key > generated always as identity, > source_id_1 int not null, > source_id_2 int not null > ); > 0 rows inserted/updated/deleted > ij> select s1.source_id, s2.source_id > from source as s1 > join source as s2 > on 1 = 1; > SOURCE_ID |SOURCE_ID > ----------------------- > 0 |0 > 0 |1 > 0 |2 > 0 |3 > 0 |4 > 0 |5 > 0 |6 > 0 |7 > 0 |8 > 0 |9 > 1 |0 > 1 |1 > 1 |2 > 1 |3 > 1 |4 > 1 |5 > 1 |6 > 1 |7 > 1 |8 > 1 |9 > 2 |0 > 2 |1 > 2 |2 > 2 |3 > 2 |4 > 2 |5 > 2 |6 > 2 |7 > 2 |8 > 2 |9 > 3 |0 > 3 |1 > 3 |2 > 3 |3 > 3 |4 > 3 |5 > 3 |6 > 3 |7 > 3 |8 > 3 |9 > 4 |0 > 4 |1 > 4 |2 > 4 |3 > 4 |4 > 4 |5 > 4 |6 > 4 |7 > 4 |8 > 4 |9 > 5 |0 > 5 |1 > 5 |2 > 5 |3 > 5 |4 > 5 |5 > 5 |6 > 5 |7 > 5 |8 > 5 |9 > 6 |0 > 6 |1 > 6 |2 > 6 |3 > 6 |4 > 6 |5 > 6 |6 > 6 |7 > 6 |8 > 6 |9 > 7 |0 > 7 |1 > 7 |2 > 7 |3 > 7 |4 > 7 |5 > 7 |6 > 7 |7 > 7 |8 > 7 |9 > 8 |0 > 8 |1 > 8 |2 > 8 |3 > 8 |4 > 8 |5 > 8 |6 > 8 |7 > 8 |8 > 8 |9 > 9 |0 > 9 |1 > 9 |2 > 9 |3 > 9 |4 > 9 |5 > 9 |6 > 9 |7 > 9 |8 > 9 |9 > 100 rows selected > ij> insert into dest (source_id_1, source_id_2) > select s1.source_id, s2.source_id > from source as s1 > join source as s2 > on 1 = 1; > ERROR XJ001: Java exception: ': java.lang.NullPointerException'. > ij> > derby.log: > ---------------------------------------------------------------- > 2006-03-07 20:01:12.152 GMT: > Booting Derby version The Apache Software Foundation - Apache Derby - > 10.1.2.1 - (330608): instance c013800d-0109-d64c-5067-000000172958 > on database directory D:\Documents and Settings\***\My Documents\test > Database Class Loader started - derby.database.classpath='' > 2006-03-07 20:01:52.671 GMT Thread[main,5,main] (XID = 124), (SESSIONID = 0), > (DATABASE = test), (DRDAID = null), Cleanup action starting > 2006-03-07 20:01:52.671 GMT Thread[main,5,main] (XID = 124), (SESSIONID = 0), > (DATABASE = test), (DRDAID = null), Failed Statement is: insert into dest > (source_id_1, source_id_2) > select s1.source_id, s2.source_id > from source as s1 > join source as s2 > on 1 = 1 > java.lang.NullPointerException > at > org.apache.derby.impl.sql.compile.ResultColumnList.remapColumnReferencesToExpressions(Unknown > Source) > at org.apache.derby.impl.sql.compile.JoinNode.flatten(Unknown Source) > at org.apache.derby.impl.sql.compile.FromList.flattenFromTables(Unknown > Source) > at org.apache.derby.impl.sql.compile.SelectNode.preprocess(Unknown Source) > at > org.apache.derby.impl.sql.compile.SingleChildResultSetNode.preprocess(Unknown > Source) > at org.apache.derby.impl.sql.compile.DMLStatementNode.optimize(Unknown Source) > at org.apache.derby.impl.sql.compile.DMLModStatementNode.optimize(Unknown > Source) > at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) > at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) > at > org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source) > at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source) > at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source) > at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source) > at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source) > at org.apache.derby.impl.tools.ij.Main.go(Unknown Source) > at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source) > at org.apache.derby.impl.tools.ij.Main14.main(Unknown Source) > at org.apache.derby.tools.ij.main(Unknown Source) > Cleanup action completed > 2006-03-07 20:43:03.759 GMT: > Shutting down instance c013800d-0109-d64c-5067-000000172958 > ---------------------------------------------------------------- -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
