db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-1089) Derby fails inserting a join into a table with a generated column
Date Mon, 13 Nov 2006 01:05:39 GMT
     [ http://issues.apache.org/jira/browse/DERBY-1089?page=all ]

Bryan Pendleton updated DERBY-1089:
-----------------------------------

    Attachment: remapSkipNullExpressions_v1.diff

Attached is remapSkipNullExpressions_v1.diff, a proposed patch
for this problem. When an INSERT ... SELECT statement inserts
a GENERATED ALWAYS identity column, the identity column's
column reference is NULL, since that column does not have a
corresponding column in the SELECT list. This NULL expression needs
to be skipped over when remapping column references from the
SELECT column list to the INSERT column list.

The patch includes a test. derbyall and suites.All were successful.

Please have a look and give me any feedback. Thanks!


> 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

        

Mime
View raw message