[
https://issues.apache.org/jira/browse/DERBY-6040?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6040:
---------------------------------
Attachment: derbyAST.xml
Dag has identified why the first column is being stripped from the OrderByList:
it has been confused with the column which is guaranteed to be constant by this
predicate: l.a3 = 'Z'. If you run a similar query against base tables rather
than table functions, the confusion does not occur. The difference between the
behavior of base tables and table functions is contained in
ResultColumn.getColumnPosition():
public int getColumnPosition()
{
if (columnDescriptor!=null)
return columnDescriptor.getPosition();
else
return virtualColumnId;
}
For both the base table and table function cases, the virtualColumnIds are the
same. But the ResultColumn for a base table has a columnDescriptor, and that
causes getColumnPosition() to return a different identifying number for the
ResultColumn. For base tables, the returned number is the column position in
the base table. For table functions, the returned number is the
virtualColumnId, which is the ResultColumn's index into the ResultColumnList
which contains it. The column position and virtualColumnId are different
because the first column of the base table (and table function) has been
removed by the ProjectRestrict logic. That, in turn, is because it is not
referenced anywhere in the query. Some approaches to fixing the problem might
be:
1) Fabricate a ColumnDescriptor for the table function ResultColumn.
2) Change the value of virtualColumnId for table functions.
3) Add another case to ResultColumn.getColumnPosition() to handle ResultColumns
in table functions.
I am attaching derbyAST.xml. This shows the parse, bind, and optimize ASTs for
the base table and table function queries. I generated this file using the
following script and the ASTInspector tool attached to DERBY-4415:
connect 'jdbc:derby:memory:db;create=true';
create function leftTable
(
columnNames varchar( 32672 ),
rowContents varchar( 32672 ) ...
)
returns table
(
a0 varchar( 5 ),
a1 varchar( 5 ),
a2 varchar( 5 ),
a3 varchar( 5 )
)
language java parameter style derby_jdbc_result_set no sql
external name
'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.stringArrayTable';
create function rightTable
(
columnNames varchar( 32672 ),
rowContents varchar( 32672 ) ...
)
returns table
(
b1 varchar( 5 ),
b2 varchar( 5 ),
b3 varchar( 5 )
)
language java parameter style derby_jdbc_result_set no sql
external name
'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.stringArrayTable';
create table leftTable
(
a0 varchar( 5 ),
a1 varchar( 5 ),
a2 varchar( 5 ),
a3 varchar( 5 )
);
create table rightTable
(
b1 varchar( 5 ),
b2 varchar( 5 ),
b3 varchar( 5 )
);
insert into leftTable values
( 'X', 'APP', 'T', 'Z' ),
( 'X', 'APP', 'S', 'Z' );
insert into rightTable values
( 'APP', 'T', 'A' ),
( 'APP', 'T', 'B' ),
( 'APP', 'S', 'A' ),
( 'APP', 'S', 'B' );
call syscs_util.syscs_register_tool( 'customTool', true, 'ASTInspector',
'XmlASTPrinter' );
select l.a2 c2, r.b3 c3
from leftTable l, rightTable r
where r.b2 = l.a2
and l.a3 = 'Z'
and r.b1 = l.a1
order by c2, c3;
select l.a2 c2, r.b3 c3
from
table( leftTable
(
'A0 A1 A2 A3',
'X APP T Z',
'X APP S Z'
) ) l,
table( rightTable
(
'B1 B2 B3',
'APP T A',
'APP T B',
'APP S A',
'APP S B'
) ) r
where r.b2 = l.a2
and l.a3 = 'Z'
and r.b1 = l.a1
order by c2, c3;
call syscs_util.syscs_register_tool( 'customTool', false, 'ASTInspector' );
> 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.0.0
> Reporter: Rick Hillegas
> Attachments: 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
