From derby-dev-return-81182-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Sat Sep 11 15:16:40 2010 Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 95390 invoked from network); 11 Sep 2010 15:16:40 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 11 Sep 2010 15:16:40 -0000 Received: (qmail 95695 invoked by uid 500); 11 Sep 2010 15:16:40 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 95494 invoked by uid 500); 11 Sep 2010 15:16:38 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 95487 invoked by uid 99); 11 Sep 2010 15:16:37 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 11 Sep 2010 15:16:37 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.22] (HELO thor.apache.org) (140.211.11.22) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 11 Sep 2010 15:16:34 +0000 Received: from thor (localhost [127.0.0.1]) by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id o8BFGCVj027295 for ; Sat, 11 Sep 2010 15:16:12 GMT Message-ID: <15970395.133471284218172383.JavaMail.jira@thor> Date: Sat, 11 Sep 2010 11:16:12 -0400 (EDT) From: "Dag H. Wanvik (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Commented: (DERBY-4712) Complex nested joins problems In-Reply-To: <21603908.43311277403233537.JavaMail.jira@thor> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-4712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12908328#action_12908328 ] Dag H. Wanvik commented on DERBY-4712: -------------------------------------- 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! > 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.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 > Reporter: Thomas Mueller > Assignee: Dag H. Wanvik > Priority: Minor > Fix For: 10.7.0.0 > > Attachments: assert-bind-opt-trees.log, assert-bind-opt-trees.txt, derby.log.analyzed, derby.log.simpler, derby4712a.diff, derby4712a.stat, drawing.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.