db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6040) Incorrect row order returned for an ORDER BY on a join of two table functions
Date Thu, 11 Jul 2013 19:15:49 GMT

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

ASF subversion and git services commented on DERBY-6040:
--------------------------------------------------------

Commit 1502329 from [~mamtas]
[ https://svn.apache.org/r1502329 ]

DERBY-6040(Incorrect row order returned for an ORDER BY on a join of two table functions)

Backporting to 10.8 from 10.9. Original fix contributed by Rick
                
> 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

Mime
View raw message