You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2013/01/11 06:12:12 UTC
[jira] [Comment Edited] (DERBY-6040) Incorrect row order returned
for an ORDER BY on a join of two metadata table functions
[ 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