Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6040

Incorrect row order returned for an ORDER BY on a join of two table functions

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.8.3.3, 10.9.2.2, 10.10.1.1
    • 10.8.3.3, 10.9.2.2, 10.10.1.1
    • SQL
    • None
    • Normal
    • Repro attached
    • Wrong query result

    Description

      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

      Attachments

        1. derby-6040.sql
          2 kB
          Richard N. Hillegas
        2. derby-6040-01-aa-makeColumnDescriptor.diff
          7 kB
          Richard N. Hillegas
        3. derbyAST.xml
          323 kB
          Richard N. Hillegas

        Issue Links

          Activity

            People

              rhillegas Richard N. Hillegas
              rhillegas Richard N. Hillegas
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: