Derby fails inserting a join into a table with a generated column

• Key: DERBY-1089
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Bryan Pendleton
• Reporter: Mark Boylan
• Component/s: SQL
• Affects Version/s: 10.1.2.1
• Fix Version/s: 10.2.2.0, 10.3.1.4

Description

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
----------------------------------------------------------------

Comments

[Michael Hackett]

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.

[Tim hodson]

I also came across this problem today. A related but slightly easier way to get round it, certainly for my case, was to go for something like:

insert into table (a, b, c)
select * from (
select d, e, calced_f from somewhere
) X

ie to wrap the SQL generating the data with "select * from (...) x". This seems to avoid the bug and also avoids having to create and then drop another table.

Thank you both for helping me identify and get round this!

[Bryan Pendleton]

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!

[Bryan Pendleton]

I think DERBY-2015 is a duplicate of this issue.

[Kristian Waagan]

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?

[Bryan Pendleton]

Thanks Kristian for the review and the testing, it is very helpful. I will fix the tab/space issue prior to committing the change.

Your suggestion about merging the fix to prior branches is good; I'll at least merge it to 10.2 for sure.

I'll wait a bit longer to see if there are other comments on the patch. If not, I'll submit it later this week.

Bryan, there are more places in ResultColumnList.java where
  x.getExpression().y()
is called without testing whether x.getExpression() is null.

These places are at the following lines in ResultColumnList.java:
 1616 sourceRC.getExpression().setType(resultColumn.getTypeServices());
 1875: resultColumn.getExpression().getClass().getName());
 1879 ((VirtualColumnNode) resultColumn.getExpression()).columnId += adjust;
 2848 if (! rc.getExpression().isCloneable())
 2871 rc.setExpression(rc.getExpression().remapColumnReferencesToExpressions());
     (this is fixed by your patch)

Maybe it would be reasonable to fix these too?

[Bryan Pendleton]

Thanks Christian! I will investigate these usages.

[Bryan Pendleton]

Hi Christian,

I spent some time studying the code that you identified, and I set up various experiments to learn how those various lines of code get executed. In all the cases that I could construct, there was no way that I could see to arrive at these lines with a column which is GENERATED ALWAYS.

I agree with you that the unguarded reference to rc.getExpression() is a concern since we know that in some cases (namely GENERATED ALWAYS columns) it can be null.

However, without a way to actually cause the execution path to reach one of these lines of code with a NULL ResultColumn.expression, I am reluctant to change them.

My preference for now is to leave those lines as they are.

[Bryan Pendleton]

Committed remapSkipNullExpressions_v1.diff to the trunk as revision 478622.

I intend to merge this change to 10.2 and commit it there as well.

I also intend to mark DERBY-2015 as a duplicate of this issue.

[Bryan Pendleton]

Merged the trunk patch to the 10.2 branch as revision 478658.

Marked the issue resolved and cleared Patch Available.

[Andrew McIntyre]

This issue has been resolved for over a year with no further movement. Closing.