db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-6040) Incorrect row order returned for an ORDER BY on a join of two metadata table functions
Date Thu, 10 Jan 2013 21:00:13 GMT
Rick Hillegas created DERBY-6040:
------------------------------------

             Summary: Incorrect row order returned for an ORDER BY on a join of two metadata
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.0.0
            Reporter: Rick Hillegas


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