[
https://issues.apache.org/jira/browse/DERBY-6040?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13550827#comment-13550827
]
Dag H. Wanvik edited comment on DERBY-6040 at 1/11/13 5:10 AM:
---------------------------------------------------------------
The first column gets eliminated from the OrderByList in SelectNode#preprocess
-> OrderByList#removeConstantColumns because the code thinks this column
references is "TABLE_TYPE", which is part of one of the where expressions that
has a constant.
This mis-identification happens in BinaryRelationOperatorNode#getOperand in
these lines:
if (cr.getSource().getColumnPosition() ==
cRef.getColumnNumber())
{
both sides evaluate to 3. "cr" should point to "T.TABLE_TYPE", and "cRef"
should point to C.TABLE_NAME. So, I believe the wrong number here is assigned
to "cr"; it should be 4. Possibly because FromVTI#genProjectRestrict removes
column from the result set that are not used, cf. his code:
/* Project out any unreferenced columns. If there are no referenced
* columns, generate and bind a single ResultColumn whose expression is 1.
*/
prRCList.doProjection();
I'll see if referencing it makes a difference. [Update: adding ", t.table_cat
c4" to the select list made the query work.]
was (Author: dagw):
The first column gets eliminated from the OrderByList in
SelectNode#preprocess -> OrderByList#removeConstantColumns because the code
thinks this column references is "TABLE_TYPE", which is part of one of the
where expressions that has a constant.
This mis-identification happens in BinaryRelationOperatorNode#getOperand in
these lines:
if (cr.getSource().getColumnPosition() ==
cRef.getColumnNumber())
{
both sides evaluate to 3. "cr" should point to "T.TABLE_TYPE", and "cRef"
should point to C.TABLE_NAME. So, I believe the wrong number here is assigned
to "cr"; it should be 4. Possibly because FromVTI#genProjectRestrict removes
column from the result set that are not used, cf. his code:
/* Project out any unreferenced columns. If there are no referenced
* columns, generate and bind a single ResultColumn whose expression is 1.
*/
prRCList.doProjection();
I'll see if referencing it makes a difference.
> 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
