[ http://issues.apache.org/jira/browse/DERBY-1089?page=all ]
Bryan Pendleton updated DERBY-1089:
-----------------------------------
Derby Info: [Patch Available]
> 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
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Environment: WinXP
> Reporter: Mark Boylan
> Assigned To: Bryan Pendleton
> Attachments: remapSkipNullExpressions_v1.diff
>
>
> 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
