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 "Kristian Waagan (JIRA)" <ji...@apache.org> on 2006/11/15 15:02:43 UTC
[jira] Commented: (DERBY-1089) Derby fails inserting a join into a
table with a generated column
[ http://issues.apache.org/jira/browse/DERBY-1089?page=comments#action_12450033 ]
Kristian Waagan commented on DERBY-1089:
----------------------------------------
I tried this patch on 10.1, 10.2 and 10.3. With the exception of offsets in the 10.1 branch, everything applied cleanly and I was able to build Derby.
Test results (all with Java SE 5):
10.3 (475016M): No failures.
10.2: derbyall OK, many failures due to permissions for suites.All.
10.1: derbynet/dataSourcePermissions_net.java, derbynet/testSecMec.java and unit/T_Diagnosticable.unit failed (probably unrelated, have not investigated).
Patch looks good, but my knowledge of the area of the code is very limited. Another opinion would be good.
A little nit: There's a mix of tabs and spaces on two of the lines added by the patch (after the if).
Taken that this patch is a small change to the code, should we merge it to the earlier branches?
> 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