Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 04B1410559 for ; Wed, 10 Jul 2013 16:03:51 +0000 (UTC) Received: (qmail 66101 invoked by uid 500); 10 Jul 2013 16:03:50 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 66004 invoked by uid 500); 10 Jul 2013 16:03:50 -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 65819 invoked by uid 99); 10 Jul 2013 16:03:49 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 10 Jul 2013 16:03:49 +0000 Date: Wed, 10 Jul 2013 16:03:49 +0000 (UTC) From: "ASF subversion and git services (JIRA)" To: derby-dev@db.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (DERBY-6040) Incorrect row order returned for an ORDER BY on a join of two table functions MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DERBY-6040?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13704695#comment-13704695 ] ASF subversion and git services commented on DERBY-6040: -------------------------------------------------------- Commit 1501816 from [~mamtas] [ https://svn.apache.org/r1501816 ] DERBY-6040(Incorrect row order returned for an ORDER BY on a join of two table functions) Hand backported(svn merge was running into conflicts) revision 1433031 from trunk to 10.9 without junit test since junit test is using feature not available in 10.9 > Incorrect row order returned for an ORDER BY on a join of two table functions > ----------------------------------------------------------------------------- > > Key: DERBY-6040 > URL: https://issues.apache.org/jira/browse/DERBY-6040 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.10.1.1 > Reporter: Rick Hillegas > Assignee: Mamta A. Satoor > Fix For: 10.10.1.1 > > Attachments: derby-6040-01-aa-makeColumnDescriptor.diff, derby-6040.sql, derbyAST.xml > > > Using the metadata table functions introduced by DERBY-6022, I get the wrong row order on this query: > select t.table_name c2, c.column_name c3 > from table( getTables( null, '%', '%' ) ) t, > table( getColumns( null, '%', '%', '%') ) c > where c.table_name = t.table_name > and t.table_type = 'TABLE' > and c.table_schem = t.table_schem > order by c2, c3; > However, I get the correct order on the following query. The good query returns the same rows but in the correct order. The only difference between the queries is that the bad one has an extra, NOP join clause. > Here is the full result of a script showing the problem: > ij version 10.10 > ij> connect 'jdbc:derby:memory:db;create=true'; > ij> create table t( d int, u varchar( 30 ) ); > 0 rows inserted/updated/deleted > ij> create table s( d int, u varchar( 30 ) ); > 0 rows inserted/updated/deleted > ij> call syscs_util.syscs_register_tool( 'databaseMetaData', true ); > 0 rows inserted/updated/deleted > ij> -- columns are ordered correctly > select t.table_name c2, c.column_name c3 > from table( getTables( null, '%', '%' ) ) t, > table( getColumns( null, '%', '%', '%') ) c > where c.table_name = t.table_name > and t.table_type = 'TABLE' > order by c2, c3; > C2 |C3 > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > S |D > S |U > T |D > T |U > 4 rows selected > ij> -- columns are ordered incorrectly > select t.table_name c2, c.column_name c3 > from table( getTables( null, '%', '%' ) ) t, > table( getColumns( null, '%', '%', '%') ) c > where c.table_name = t.table_name > and t.table_type = 'TABLE' > and c.table_schem = t.table_schem > order by c2, c3; > C2 |C3 > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > T |D > S |D > T |U > S |U > 4 rows selected -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira