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 2012/09/25 00:34:07 UTC
[jira] [Comment Edited] (DERBY-5933) SQL sorting error
[ https://issues.apache.org/jira/browse/DERBY-5933?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13462203#comment-13462203 ]
Dag H. Wanvik edited comment on DERBY-5933 at 9/25/12 9:33 AM:
---------------------------------------------------------------
Uploading an experimental patch which makes the query work.
I found that the wrong label to the column reference "B1" happened when that expression was pushed into the left outer join node at this stack position:
.sql.compile.ResultColumn.getColumnPosition(ResultColumn.java:447)
.sql.compile.ColumnReference.remapColumnReferences(ColumnReference.java:721)
.sql.compile.RemapCRsVisitor.visit(RemapCRsVisitor.java:75)
.sql.compile.QueryTreeNode.accept(QueryTreeNode.java:718)
.sql.compile.BinaryOperatorNode.acceptChildren(BinaryOperatorNode.java:812)
.sql.compile.QueryTreeNode.accept(QueryTreeNode.java:721)
.sql.compile.BinaryOperatorNode.acceptChildren(BinaryOperatorNode.java:812)
.sql.compile.QueryTreeNode.accept(QueryTreeNode.java:721)
.sql.compile.PredicateList.getPushablePredicates(PredicateList.java:1780)
.sql.compile.ProjectRestrictNode.pushExpressions(ProjectRestrictNode.java:1095)
.sql.compile.FromList.pushPredicates(FromList.java:846)
.sql.compile.SelectNode.preprocess(SelectNode.java:1238)
...
The code in ResultColumn#getColumnPosition looks like this:
public int getColumnPosition()
{
if (columnDescriptor!=null)
return columnDescriptor.getPosition();
else
return virtualColumnId;
}
In this case, there is a columnDescriptor present in the RC (maybe because it is also used in the join condition?), but that refers the the base table "B", in which "B1" has column position 1. However, here we are trying to push into the LOJ, in which the real position is 5, the value of virtualColumnId. So, the patch just uses virtualColumnId iff we are sitting on top of a join.
I tried the patch briefly and it worked for the join and predicate pushdown tests, but I am still very wary of this hack, but I post it now anyway ;-). Running regressions to see what happens.
was (Author: dagw):
Uploading an experimental patch which makes the query work.
I found that the wrong label to the column reference "B1" happened when that expression was pushed into the left outer join node at this stack position:
.sql.compile.ResultColumn.getColumnPosition(ResultColumn.java:447)
.sql.compile.ColumnReference.remapColumnReferences(ColumnReference.java:721)
.sql.compile.RemapCRsVisitor.visit(RemapCRsVisitor.java:75)
.sql.compile.QueryTreeNode.accept(QueryTreeNode.java:718)
.sql.compile.BinaryOperatorNode.acceptChildren(BinaryOperatorNode.java:812)
.sql.compile.QueryTreeNode.accept(QueryTreeNode.java:721)
.sql.compile.BinaryOperatorNode.acceptChildren(BinaryOperatorNode.java:812)
.sql.compile.QueryTreeNode.accept(QueryTreeNode.java:721)
.sql.compile.PredicateList.getPushablePredicates(PredicateList.java:1780)
.sql.compile.ProjectRestrictNode.pushExpressions(ProjectRestrictNode.java:1095)
.sql.compile.FromList.pushPredicates(FromList.java:846)
.sql.compile.SelectNode.preprocess(SelectNode.java:1238)
...
The code in ResultColumn#getColumnPosition looks like this:
public int getColumnPosition()
{
if (columnDescriptor!=null)
return columnDescriptor.getPosition();
else
return virtualColumnId;
}
In this case, there is a columnDescriptor present in the CR (maybe because it is also used in the join condition?), but that refers the the base table "B", in which "B1" has column position 1. However we are here trying to push into the LOJ, in which the real position is 5, the value of virtualColumnId. So, the patch just uses virtualColumnId iff we are sitting on top of a join.
I tried the patch briefly and it worked for the join and predicate pushdown tests, but I am still very wary of this hack, but I post it now anyway ;-). Running regressions to see what happens.
> SQL sorting error
> -----------------
>
> Key: DERBY-5933
> URL: https://issues.apache.org/jira/browse/DERBY-5933
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.1, 10.1.1.0, 10.2.1.6, 10.3.1.4, 10.4.1.3, 10.5.1.1, 10.6.1.0, 10.7.1.1, 10.8.1.2, 10.9.1.0
> Environment: Windows 7 Netbeans JDBC GUI
> Reporter: Vlasov Igor
> Labels: derby_triage10_10
> Attachments: 5933.log, d5933-pof.diff, Helpdesk.zip, repro.sql, right_sorting.png, wrong_sorting.png
>
>
> Hello
> I have a simple database with 100 records.
> I am running a SQL query from Netbeans GUI though JDBC
> This query was generated by Hibernate ORM.
> In certain circumstances the result rowset is not sorting.
> When I use condition morefld2_.mf_id in (5) the result is unsortable.
> When I use condition morefld2_.mf_id in (5,0) the result is sorting properly.
>
>
--
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