Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
    • Fix Version/s: 10.5.3.2, 10.6.2.1, 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Regression Test Failure, Wrong query result

      Description

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

      1. assert-bind-opt-trees.log
        137 kB
        Dag H. Wanvik
      2. assert-bind-opt-trees.txt
        5 kB
        Dag H. Wanvik
      3. derby.log.analyzed
        87 kB
        Dag H. Wanvik
      4. derby.log.simpler
        44 kB
        Dag H. Wanvik
      5. derby4712a.diff
        67 kB
        Dag H. Wanvik
      6. derby4712a.stat
        0.2 kB
        Dag H. Wanvik
      7. derby4712b.diff
        15 kB
        Dag H. Wanvik
      8. drawing.txt
        3 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Verified that this query fails on trunk too. Setting component to SQL and adding the repro attached flag.

          Show
          Knut Anders Hatlen added a comment - Verified that this query fails on trunk too. Setting component to SQL and adding the repro attached flag.
          Hide
          Dag H. Wanvik added a comment -

          Attaching derby.log which dump of the query tree after bind and optimization of a simplified query which also gives the ASSERT error, and an annotated drawing of the query tree after bind and optimize. The drawing shows that the column reference which gives the ASSERT during code generation points to a result column of a result column list which has been replaced during the optimization phases. This is the immediate cause of the error.

          The column reference is part of a join condition (T2.X=1) on the outermost left join. This has been pushed into the right side of the outermost left join so after optimization, the predicate is part of the PRN above the inner join which is the right side of the outermost left join.

          Show
          Dag H. Wanvik added a comment - Attaching derby.log which dump of the query tree after bind and optimization of a simplified query which also gives the ASSERT error, and an annotated drawing of the query tree after bind and optimize. The drawing shows that the column reference which gives the ASSERT during code generation points to a result column of a result column list which has been replaced during the optimization phases. This is the immediate cause of the error. The column reference is part of a join condition (T2.X=1) on the outermost left join. This has been pushed into the right side of the outermost left join so after optimization, the predicate is part of the PRN above the inner join which is the right side of the outermost left join.
          Hide
          Dag H. Wanvik added a comment -

          Created a sub-issue for the ASSERT error analyzed above since it seems distinct from the NPE: DERBY-4736.

          Show
          Dag H. Wanvik added a comment - Created a sub-issue for the ASSERT error analyzed above since it seems distinct from the NPE: DERBY-4736 .
          Hide
          Dag H. Wanvik added a comment -

          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)

          Show
          Dag H. Wanvik added a comment - 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)
          Hide
          Dag H. Wanvik added a comment -

          Uploading a drawing of the query tree after optimization for the above query that gives NPE, and and even simpler one which seems to work. The simple one is the left side of the top INNER JOIN of the first one, which contains the predicate that leads to the query NPE (x0=x2). In the simplified one that works we can see that the join predicate X0=X2 is located in the PRN above the innermost INNER JOIN. In the NPE example, we can see that the join predicate has been pushed all the way down to the PRN above the base table T1.

          The difference may account for the NPE, but I am not sure. It is unclar to me, how far "across" result set trees column references may point.

          Show
          Dag H. Wanvik added a comment - Uploading a drawing of the query tree after optimization for the above query that gives NPE, and and even simpler one which seems to work. The simple one is the left side of the top INNER JOIN of the first one, which contains the predicate that leads to the query NPE (x0=x2). In the simplified one that works we can see that the join predicate X0=X2 is located in the PRN above the innermost INNER JOIN. In the NPE example, we can see that the join predicate has been pushed all the way down to the PRN above the base table T1. The difference may account for the NPE, but I am not sure. It is unclar to me, how far "across" result set trees column references may point.
          Hide
          Dag H. Wanvik added a comment - - edited

          NPE preliminary analysis:

          Referring to the drawing in the uploading document "drawing.txt", note
          the following:

          In the simpler query that works, after optimization, there is a PRN
          sitting above the JoinNode (labeled "JN@60") which holds the predicate
          X0=X2. In the bad query, there is no such PRN, the JoinNode (labeled
          JN@45) sits directly underneath the Left Outer Join Node (labeled
          LOJ@33).

          This seemed puzzling, since the simpler query is the exact left side of
          the bad query.

          Tracing the creation of PRNs, I found that the PRN in the simpler
          query gets created in TableOperatorNode (superclass of JoinNode) iff
          "isFlattenableJoinNode()" returns false for the JoinNode,
          cf. TableOperatorNode#preprocess.

          Now, by tracing this in the simple, working example, I see that
          isFlattenableJoinNode does in deed return false. However, in the bad
          query, it returns true! Why should that be?

          It turns out the for the working query, FromList#preprocess calls
          transformOuterJoins for each table in the from list. If the top node
          in the from list is a LOJ, which it is in the simplified, working
          query, the method called will be
          HalfOuterJoinNode#transformOuterJoins. Since there is no WHERE
          predicate, the argument "predicateTree" will be null, and
          transformOuterJoins will return "this", but only after calling

          leftResultSet.notFlattenableJoin();
          rightResultSet.notFlattenableJoin();

          on its child result sets. This accounts for the fact that in the good
          query, the PRN gets inserted.

          Now, in the bad query, again FromList#preprocess calls
          transformOuterJoins, but this time on the top plain JoinNode. Also
          here, we see the method return "this" is the predicateTree is null (no
          WHERE clause), but it makes no attempt to propagate this information
          down to any inferior outer join nodes. This accounts for the fact that
          "LOJ@33" in the drawing never gets to call notFlattenableJoin on
          "JN@45", and hence it never gets a PRN above it.

          By adding the following lines to JoinNode#transformOuterJoins, the NPE
          goes away for all the sample queries in DERBY4712 (I have not yet
          verified their correctness though):
          :
          if (predicateTree == null)

          { // Make sure any nested outer joins know we are non flattenable, too, // since they inform their left and right sides. If result sets are // not outer joins, these are no-ops. Cf. DERBY-4712. ((FromTable) leftResultSet).transformOuterJoins(null, numTables); // <<<- added ((FromTable) rightResultSet).transformOuterJoins(null, numTables); // <<<- added return this; }

          Possibly, we need to add these two extra lines to the same place in HalfOuterJoinNode, too. I'll see.

          So, in summary, I think that the NPE results from an query tree
          inconsistency in that the missing PRN indicates that the JoinNode is
          flattenable whereas it is not really, and the join predicate gets
          pushed down to far.

          Show
          Dag H. Wanvik added a comment - - edited NPE preliminary analysis: Referring to the drawing in the uploading document "drawing.txt", note the following: In the simpler query that works, after optimization, there is a PRN sitting above the JoinNode (labeled "JN@60") which holds the predicate X0=X2. In the bad query, there is no such PRN, the JoinNode (labeled JN@45) sits directly underneath the Left Outer Join Node (labeled LOJ@33). This seemed puzzling, since the simpler query is the exact left side of the bad query. Tracing the creation of PRNs, I found that the PRN in the simpler query gets created in TableOperatorNode (superclass of JoinNode) iff "isFlattenableJoinNode()" returns false for the JoinNode, cf. TableOperatorNode#preprocess. Now, by tracing this in the simple, working example, I see that isFlattenableJoinNode does in deed return false. However, in the bad query, it returns true! Why should that be? It turns out the for the working query, FromList#preprocess calls transformOuterJoins for each table in the from list. If the top node in the from list is a LOJ, which it is in the simplified, working query, the method called will be HalfOuterJoinNode#transformOuterJoins. Since there is no WHERE predicate, the argument "predicateTree" will be null, and transformOuterJoins will return "this", but only after calling leftResultSet.notFlattenableJoin(); rightResultSet.notFlattenableJoin(); on its child result sets. This accounts for the fact that in the good query, the PRN gets inserted. Now, in the bad query, again FromList#preprocess calls transformOuterJoins, but this time on the top plain JoinNode. Also here, we see the method return "this" is the predicateTree is null (no WHERE clause), but it makes no attempt to propagate this information down to any inferior outer join nodes. This accounts for the fact that "LOJ@33" in the drawing never gets to call notFlattenableJoin on "JN@45", and hence it never gets a PRN above it. By adding the following lines to JoinNode#transformOuterJoins, the NPE goes away for all the sample queries in DERBY4712 (I have not yet verified their correctness though): : if (predicateTree == null) { // Make sure any nested outer joins know we are non flattenable, too, // since they inform their left and right sides. If result sets are // not outer joins, these are no-ops. Cf. DERBY-4712. ((FromTable) leftResultSet).transformOuterJoins(null, numTables); // <<<- added ((FromTable) rightResultSet).transformOuterJoins(null, numTables); // <<<- added return this; } Possibly, we need to add these two extra lines to the same place in HalfOuterJoinNode, too. I'll see. So, in summary, I think that the NPE results from an query tree inconsistency in that the missing PRN indicates that the JoinNode is flattenable whereas it is not really, and the join predicate gets pushed down to far.
          Hide
          Dag H. Wanvik added a comment -

          I checked the ex-NPE query results against Postgres, and the results were identical, so the fix is sound as far as correctness
          for the NPE queries reported in this issue.

          Show
          Dag H. Wanvik added a comment - I checked the ex-NPE query results against Postgres, and the results were identical, so the fix is sound as far as correctness for the NPE queries reported in this issue.
          Hide
          Dag H. Wanvik added a comment -

          Uploading a patch which embodies the proposed fix, and a new test case fixture in OuterJoinTest; testDerby_4712_NPEs, which contains the original queries that received the NPEs.
          Regressions ran OK. Please review.

          Show
          Dag H. Wanvik added a comment - Uploading a patch which embodies the proposed fix, and a new test case fixture in OuterJoinTest; testDerby_4712_NPEs, which contains the original queries that received the NPEs. Regressions ran OK. Please review.
          Hide
          Dag H. Wanvik added a comment - - edited

          Sadly(? , going back to the original randomized query generator, http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/test/db/TestNestedJoins.java, I have uncovered yet another similar bug (NPE), which is not fixed by the current patch:

          SELECT t0.x0,
          t1.x1,
          t2.x2,
          t3.x3,
          t4.x4,
          t5.x5,
          t6.x6,
          t7.x7
          FROM ((t0
          LEFT OUTER JOIN ((t1
          LEFT OUTER JOIN (t2
          LEFT OUTER JOIN t3
          ON t2.x2 = t3.x3 )
          ON t1.x1 = t2.x2 )
          LEFT OUTER JOIN (t4
          INNER JOIN (t5
          LEFT OUTER JOIN t6
          ON t5.x5 = t6.x6 )
          ON t4.x4 = t5.x5 )
          ON t1.x1 = t5.x5 )
          ON t0.x0 = t5.x5 )
          LEFT OUTER JOIN t7
          ON t3.x3 = t7.x7 );

          This is apparently another bug, filed it as DERBY-4798.

          Show
          Dag H. Wanvik added a comment - - edited Sadly(? , going back to the original randomized query generator, http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/test/db/TestNestedJoins.java , I have uncovered yet another similar bug (NPE), which is not fixed by the current patch: SELECT t0.x0, t1.x1, t2.x2, t3.x3, t4.x4, t5.x5, t6.x6, t7.x7 FROM ((t0 LEFT OUTER JOIN ((t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.x2 = t3.x3 ) ON t1.x1 = t2.x2 ) LEFT OUTER JOIN (t4 INNER JOIN (t5 LEFT OUTER JOIN t6 ON t5.x5 = t6.x6 ) ON t4.x4 = t5.x5 ) ON t1.x1 = t5.x5 ) ON t0.x0 = t5.x5 ) LEFT OUTER JOIN t7 ON t3.x3 = t7.x7 ); This is apparently another bug, filed it as DERBY-4798 .
          Hide
          Dag H. Wanvik added a comment -

          The original NPE fixed by the uploaded patch is visible all way back to 10.0, marking.

          Show
          Dag H. Wanvik added a comment - The original NPE fixed by the uploaded patch is visible all way back to 10.0, marking.
          Hide
          Dag H. Wanvik added a comment - - edited

          The new NPE found above, however, is a regression in 10.5, I will open another issue for this. (edited: DERBY-4798)

          Show
          Dag H. Wanvik added a comment - - edited The new NPE found above, however, is a regression in 10.5, I will open another issue for this. (edited: DERBY-4798 )
          Hide
          Dag H. Wanvik added a comment -

          By applying the current patch, and by backing out the removed lines from BaseActivation#getColumnFromRow (see DERBY-4798), I was able make a successful run with the random generator referenced in the description of this issue (10000 generated queries).

          Show
          Dag H. Wanvik added a comment - By applying the current patch, and by backing out the removed lines from BaseActivation#getColumnFromRow (see DERBY-4798 ), I was able make a successful run with the random generator referenced in the description of this issue (10000 generated queries).
          Hide
          Bryan Pendleton added a comment -

          Thank you very much for working on this, Dag. I've been hoping to find time
          to read through your thorough notes, but time has been scarce recently.

          On the one hand, I'm a bit discouraged that the DERBY-3097 issue has re-arisen.

          But on the other hand, I'm glad: I never really felt that we had completely resolved
          that problem, because there was always that comment in the code about certain
          situations that only arose during deeply-nested joins.

          So, I'm glad that you have been able to provoke the strange DERBY-3097 error
          with your latest work, as hopefully that will let us get a bit farther into uncovering
          what's really going on there.

          I still believe that that basic DERBY-3097 change is, at its core, correct, because
          it just seems wrong to try to fetch a column value from a non-existent row.

          In the other DERBY-3097 scenarios, we were able to find a place higher in the
          call tree, where the result set logic could tell that we were initializing a result
          set which had not yet been opened, and could handle the initialization logic
          more cleanly. Hopefully we can do that again here (and in DERBY-4798).

          Once again, thank you very much for exploring these issues in such detail, and
          for documenting your findings in your comments and writeups. These work products
          will be of tremendous value to future students of this code.

          And, lastly: 10,000 generated queries! That is fabulous! Someday, if I get the time,
          I'd like to use these query generation tools to continue working on the query plan
          visualization features, so I'm very pleased that you've been able to make the tools
          (and the queries) work properly in Derby.

          Show
          Bryan Pendleton added a comment - Thank you very much for working on this, Dag. I've been hoping to find time to read through your thorough notes, but time has been scarce recently. On the one hand, I'm a bit discouraged that the DERBY-3097 issue has re-arisen. But on the other hand, I'm glad: I never really felt that we had completely resolved that problem, because there was always that comment in the code about certain situations that only arose during deeply-nested joins. So, I'm glad that you have been able to provoke the strange DERBY-3097 error with your latest work, as hopefully that will let us get a bit farther into uncovering what's really going on there. I still believe that that basic DERBY-3097 change is, at its core, correct, because it just seems wrong to try to fetch a column value from a non-existent row. In the other DERBY-3097 scenarios, we were able to find a place higher in the call tree, where the result set logic could tell that we were initializing a result set which had not yet been opened, and could handle the initialization logic more cleanly. Hopefully we can do that again here (and in DERBY-4798 ). Once again, thank you very much for exploring these issues in such detail, and for documenting your findings in your comments and writeups. These work products will be of tremendous value to future students of this code. And, lastly: 10,000 generated queries! That is fabulous! Someday, if I get the time, I'd like to use these query generation tools to continue working on the query plan visualization features, so I'm very pleased that you've been able to make the tools (and the queries) work properly in Derby.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for looking at this, Bryan, and for the encouragement. Much appreciated
          I'll read up a bit on DERBY-3097 and see if we can adapt those approaches also for the case of DERBY-4798 (the NPE addressed in the patch of this issue is not related).

          As for the nested joins generator queries, I had to hack the generator a bit since I didn't have the h2 code (not sure if I can post my tweaked version here (it carries an H2 licence), but I can mail you a copy if you like). It doesn't really check the correctness of the queries, just that they execute without error. I ran it 10 more times successfully just to be sure it wasn't a lucky draw.

          Also, thanks to Thomas for alerting us to these issues! Very useful bug report, when this is all in we'll have four bug fixes in place in this area!

          Show
          Dag H. Wanvik added a comment - Thanks for looking at this, Bryan, and for the encouragement. Much appreciated I'll read up a bit on DERBY-3097 and see if we can adapt those approaches also for the case of DERBY-4798 (the NPE addressed in the patch of this issue is not related). As for the nested joins generator queries, I had to hack the generator a bit since I didn't have the h2 code (not sure if I can post my tweaked version here (it carries an H2 licence), but I can mail you a copy if you like). It doesn't really check the correctness of the queries, just that they execute without error. I ran it 10 more times successfully just to be sure it wasn't a lucky draw. Also, thanks to Thomas for alerting us to these issues! Very useful bug report, when this is all in we'll have four bug fixes in place in this area!
          Hide
          Thomas Mueller added a comment -

          Not sure what's the process to 're-license' the code, but you have my permission to use my code in whatever way you want.

          Show
          Thomas Mueller added a comment - Not sure what's the process to 're-license' the code, but you have my permission to use my code in whatever way you want.
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Thomas. I basically did a gross hack to make just the random generator do its thing for Derby.

          I plan to commit this patch (derby-4712a) shortly. When DERBY-4798 also goes in, Derby should be able to handle all the random queries.

          Show
          Dag H. Wanvik added a comment - Thanks, Thomas. I basically did a gross hack to make just the random generator do its thing for Derby. I plan to commit this patch (derby-4712a) shortly. When DERBY-4798 also goes in, Derby should be able to handle all the random queries.
          Hide
          Dag H. Wanvik added a comment -

          Uploading derby4712b, same as "a", except removed some spurious blank diffs.

          Show
          Dag H. Wanvik added a comment - Uploading derby4712b, same as "a", except removed some spurious blank diffs.
          Hide
          Dag H. Wanvik added a comment -

          Committed derby4712b as svn 997325, resolving this issue.
          Note that the related issue DERBY-4798 must be fixed before all the bugs seen by the reporter's repro are fixed.

          Show
          Dag H. Wanvik added a comment - Committed derby4712b as svn 997325, resolving this issue. Note that the related issue DERBY-4798 must be fixed before all the bugs seen by the reporter's repro are fixed.
          Hide
          Dag H. Wanvik added a comment -

          Backported to 10.6 as svn 998163.

          I do not plan any more work on this issue, so Thomas, feel free to close it if you are OK with it.

          Show
          Dag H. Wanvik added a comment - Backported to 10.6 as svn 998163. I do not plan any more work on this issue, so Thomas, feel free to close it if you are OK with it.
          Hide
          Kathey Marsden added a comment -

          Reopen for backport.

          Show
          Kathey Marsden added a comment - Reopen for backport.
          Hide
          Kathey Marsden added a comment -

          Assigning to myself temporarily for backport

          Show
          Kathey Marsden added a comment - Assigning to myself temporarily for backport
          Hide
          Kathey Marsden added a comment -

          In porting this fix to 10.5 I noticed the final query in the reproduction gives an assertion even with the fix on both trunk and 10.5, although the NullPointerExceptions are fixed. Has another issue already been filed for this or should I file one?

          Below is the trace for trunk.

          2011-02-07 20:15:47.640 GMT Thread[main,5,main] (XID = 325), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = null), Cleanup action starting

          2011-02-07 20:15:47.640 GMT Thread[main,5,main] (XID = 325), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = null), Failed Statement is: 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 )

          org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED sourceResultSetNumber expected to be >= 0 for T2.X

          at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162)

          at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147)

          at org.apache.derby.impl.sql.compile.ColumnReference.generateExpression(ColumnReference.java:941)

          at org.apache.derby.impl.sql.compile.BinaryOperatorNode.generateExpression(BinaryOperatorNode.java:600)

          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1529)

          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341)

          at org.apache.derby.impl.sql.compile.JoinNode.getJoinArguments(JoinNode.java:1604)

          at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1580)

          at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1517)

          at org.apache.derby.impl.sql.compile.HalfOuterJoinNode.generate(HalfOuterJoinNode.java:691)

          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1390)

          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341)

          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1485)

          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341)

          at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109)

          at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:629)

          at org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:347)

          at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:449)

          at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)

          at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:909)

          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606)

          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555)

          at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329)

          at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:505)

          at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:347)

          at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:245)

          at org.apache.derby.impl.tools.ij.Main.go(Main.java:217)

          at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)

          at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)

          at org.apache.derby.tools.ij.main(ij.java:59)

          ---------------

          Show
          Kathey Marsden added a comment - In porting this fix to 10.5 I noticed the final query in the reproduction gives an assertion even with the fix on both trunk and 10.5, although the NullPointerExceptions are fixed. Has another issue already been filed for this or should I file one? Below is the trace for trunk. 2011-02-07 20:15:47.640 GMT Thread [main,5,main] (XID = 325), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = null), Cleanup action starting 2011-02-07 20:15:47.640 GMT Thread [main,5,main] (XID = 325), (SESSIONID = 1), (DATABASE = wombat), (DRDAID = null), Failed Statement is: 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 ) org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED sourceResultSetNumber expected to be >= 0 for T2.X at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162) at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147) at org.apache.derby.impl.sql.compile.ColumnReference.generateExpression(ColumnReference.java:941) at org.apache.derby.impl.sql.compile.BinaryOperatorNode.generateExpression(BinaryOperatorNode.java:600) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1529) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341) at org.apache.derby.impl.sql.compile.JoinNode.getJoinArguments(JoinNode.java:1604) at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1580) at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1517) at org.apache.derby.impl.sql.compile.HalfOuterJoinNode.generate(HalfOuterJoinNode.java:691) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1390) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1485) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341) at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109) at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:629) at org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:347) at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:449) at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:909) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555) at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329) at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:505) at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:347) at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:245) at org.apache.derby.impl.tools.ij.Main.go(Main.java:217) at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184) at org.apache.derby.impl.tools.ij.Main.main(Main.java:75) at org.apache.derby.tools.ij.main(ij.java:59) ---------------
          Hide
          Kathey Marsden added a comment -

          Sorry, my error. I now see there is a subtask for the ASSERTION. I was mistaken in my earlier comment that it still occurs on trunk and managed to pick up the 10.5 jars instead of trunk.

          Show
          Kathey Marsden added a comment - Sorry, my error. I now see there is a subtask for the ASSERTION. I was mistaken in my earlier comment that it still occurs on trunk and managed to pick up the 10.5 jars instead of trunk.
          Hide
          Kathey Marsden added a comment -

          Closing after backport to 10.5. Note only code change was backported as OuterJoinTest is not in 10.5 branch.

          Show
          Kathey Marsden added a comment - Closing after backport to 10.5. Note only code change was backported as OuterJoinTest is not in 10.5 branch.

            People

            • Assignee:
              Dag H. Wanvik
              Reporter:
              Thomas Mueller
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development