db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Hackett (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1089) Derby fails inserting a join into a table with a generated column
Date Sat, 11 Mar 2006 03:49:01 GMT
    [ 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


Mime
View raw message