From derby-dev-return-71894-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Wed Aug 05 20:15:30 2009 Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 3791 invoked from network); 5 Aug 2009 20:15:30 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 5 Aug 2009 20:15:30 -0000 Received: (qmail 16985 invoked by uid 500); 5 Aug 2009 20:15:37 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 16955 invoked by uid 500); 5 Aug 2009 20:15:37 -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 16947 invoked by uid 99); 5 Aug 2009 20:15:37 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 05 Aug 2009 20:15: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.140] (HELO brutus.apache.org) (140.211.11.140) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 05 Aug 2009 20:15:35 +0000 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id C966629A0011 for ; Wed, 5 Aug 2009 13:15:14 -0700 (PDT) Message-ID: <848744126.1249503314810.JavaMail.jira@brutus> Date: Wed, 5 Aug 2009 13:15:14 -0700 (PDT) From: "Mike Matrigali (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Updated: (DERBY-4331) Join returns results in wrong order In-Reply-To: <1441768431.1249168454800.JavaMail.jira@brutus> 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-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mike Matrigali updated DERBY-4331: ---------------------------------- my plan for this fix is going to be the proposed patch along with adding the new test cases for 4331 to the test suite. I am running full set of tests against trunk now and analysing the wisconsin diffs. I've posted the wisconsin diffs which as is the case for query plan diffs are quite extensive (15k lines) - but so far nothing surprising. If all goes well I would first check into trunk and then subsequently would like to backport to 10.5 and then hopefully a new 10.5 apache release could be made (along with whatever we are allowed to do to make it unlikely apache users will use the previous one). I plan on checking in even if there are a few cases where we use to do sort avoidance and now we don't. If I do this I would file a new JIRA issue outlining these cases, which could be addressed later. Note these would only represent performance enhancements, the queries would be returning correct results. I would appreciate it if anyone thinks this is a bad idea to let me know sooner than later. > Join returns results in wrong order > ----------------------------------- > > Key: DERBY-4331 > URL: https://issues.apache.org/jira/browse/DERBY-4331 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.5.2.0, 10.6.0.0 > Reporter: Knut Anders Hatlen > Assignee: Mike Matrigali > Attachments: createDBsimpler.txt, createDBsimplerVer2.txt, derby4331_do_not_commit.diff, notorderby4331.zip, orderby4331.zip, repro.sql, repro2.sql, repro2_qryplan.txt, wisconsin.diff > > > In Derby 10.5.2.0, the query below started returning results in wrong order (note the ORDER BY clause). Derby 10.5.1.1 gives the expected ordering. > ij> SELECT CS.ID > FROM > CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, AUTHORS A > WHERE > R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND > CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND > A.ID = CS.AUTHOR AND > EXISTS ( > SELECT 1 > FROM FILES F2 > WHERE > F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND > F2.PATH LIKE '/%' ESCAPE '#') > ORDER BY CS.ID DESC; > ID > ----------- > 1 > 2 > 3 > 2 > 2 > 3 > 6 rows selected -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.