db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4712) Complex nested joins problems
Date Mon, 30 Aug 2010 22:06:55 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12904365#action_12904365
] 

Dag H. Wanvik commented on DERBY-4712:
--------------------------------------

The NPE doesn't seem tied to right outer join, although the examples all contained that. The
following slightly simplified query also sees an NPE:
  "select 1 from " +
                "( ((t3 left outer join t2 on t2.x = t3.x ) " +
               "  left outer join " +
                "  (t0 inner join t1 on t0.x = t1.x ) on t0.x = t2.x) " +
                "inner join " +
                "  t4 on t1.x = t4.x)");

The relevant part of the stack trace in derby.log:

Caused by: java.lang.NullPointerException
	at org.apache.derby.impl.sql.execute.BaseActivation.getColumnFromRow(BaseActivation.java:1477)
	at org.apache.derby.exe.acf81e0010x012axc4fex4e37x0000035b489847.e7(Unknown Source)
	at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java:153)
	at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:273)
	at org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(NestedLoopJoinResultSet.java:116)
	at org.apache.derby.impl.sql.execute.NestedLoopLeftOuterJoinResultSet.getNextRowCore(NestedLoopLeftOuterJoinResultSet.java:104)
	at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(JoinResultSet.java:149)
	at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(ProjectRestrictResultSet.java:174)
	at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(BasicNoPutResultSetImpl.java:254)
	

> Complex nested joins problems
> -----------------------------
>
>                 Key: DERBY-4712
>                 URL: https://issues.apache.org/jira/browse/DERBY-4712
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.6.1.0
>            Reporter: Thomas Mueller
>            Assignee: Dag H. Wanvik
>            Priority: Minor
>         Attachments: assert-bind-opt-trees.log, assert-bind-opt-trees.txt
>
>
> I ran a randomized test for nested joins against PostgreSQL, Derby, and H2, and found
some problems with Derby. The queries below throw NullPointerExceptions; the last query an
assertion. The test case is here: http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/test/db/TestNestedJoins.java
. There are probably shorter queries that are problematic, but I can't test it because Derby
closes the connection after the assertion.
> create table t0(x int);
> create table t1(x int);
> create table t2(x int);
> create table t3(x int);
> create table t4(x int);
> insert into t4 values(0);
> insert into t4 values(1);
> insert into t4 values(2);
> insert into t4 values(3);
> create table t5(x int);
> insert into t5 values(0);
> insert into t5 values(1);
> insert into t5 values(2);
> insert into t5 values(3);
> insert into t5 values(4);
> create table t6(x int);
> insert into t6 values(0);
> insert into t6 values(1);
> insert into t6 values(2);
> insert into t6 values(3);
> insert into t6 values(4);
> insert into t6 values(5);
> create table t7(x int);
> insert into t7 values(0);
> insert into t7 values(1);
> insert into t7 values(2);
> insert into t7 values(3);
> insert into t7 values(4);
> insert into t7 values(5);
> insert into t7 values(6);
> create table t8(x int);
> insert into t8 values(0);
> insert into t8 values(1);
> insert into t8 values(2);
> insert into t8 values(3);
> insert into t8 values(4);
> insert into t8 values(5);
> insert into t8 values(6);
> insert into t8 values(7);
> create table t9(x int);
> insert into t9 values(0);
> insert into t9 values(1);
> insert into t9 values(2);
> insert into t9 values(3);
> insert into t9 values(4);
> insert into t9 values(5);
> insert into t9 values(6);
> insert into t9 values(7);
> insert into t9 values(8);
> insert into t0 values(1);
> insert into t1 values(2);
> insert into t0 values(3);
> insert into t1 values(3);
> insert into t2 values(4);
> insert into t0 values(5);
> insert into t2 values(5);
> insert into t1 values(6);
> insert into t2 values(6);
> insert into t0 values(7);
> insert into t1 values(7);
> insert into t2 values(7);
> insert into t3 values(8);
> insert into t0 values(9);
> insert into t3 values(9);
> insert into t1 values(10);
> insert into t3 values(10);
> insert into t0 values(11);
> insert into t1 values(11);
> insert into t3 values(11);
> insert into t2 values(12);
> insert into t3 values(12);
> insert into t0 values(13);
> insert into t2 values(13);
> insert into t3 values(13);
> insert into t1 values(14);
> insert into t2 values(14);
> insert into t3 values(14);
> insert into t0 values(15);
> insert into t1 values(15);
> insert into t2 values(15);
> insert into t3 values(15);
> select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x , t6.x , t7.x , t8.x from (((t0 inner
join ((t1 right outer join (t2 inner join t3 on t2.x = t3.x ) on t1.x = t2.x ) left outer
join (t4 inner join t5 on t4.x = t5.x ) on t1.x = t4.x ) on t0.x = t2.x ) left outer join
(t6 inner join t7 on t6.x = t7.x ) on t1.x = t6.x ) inner join t8 on t5.x = t8.x );
> select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x , t6.x , t7.x from ((t0 right outer join
t1 on t0.x = t1.x ) inner join (((t2 inner join (t3 left outer join t4 on t3.x = t4.x ) on
t2.x = t3.x ) right outer join t5 on t2.x = t5.x ) left outer join (t6 inner join t7 on t6.x
= t7.x ) on t4.x = t6.x ) on t0.x = t5.x );
> select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x , t6.x , t7.x from ((((t0 left outer join
t1 on t0.x = t1.x ) right outer join t2 on t0.x = t2.x ) right outer join t3 on t0.x = t3.x
) inner join ((t4 inner join t5 on t4.x = t5.x ) right outer join (t6 right outer join t7
on t6.x = t7.x ) on t4.x = t6.x ) on t1.x = t4.x );
> select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x from (((t0 inner join t1 on t0.x = t1.x
) right outer join (t2 right outer join t3 on t2.x = t3.x ) on t0.x = t2.x ) inner join (t4
left outer join t5 on t4.x = t5.x ) on t1.x = t4.x );
> select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x , t6.x from ((t0 right outer join (t1
right outer join (t2 left outer join (t3 left outer join t4 on t3.x = t4.x ) on t2.x = t3.x
) on t1.x = t3.x ) on t0.x = t1.x ) left outer join (t5 inner join t6 on t5.x = t6.x ) on
t2.x = t5.x );

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message