[
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15932724#comment-15932724
]
chenglei edited comment on PHOENIX-3745 at 3/20/17 2:34 PM:
------------------------------------------------------------
The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the
RHS subquery's OrderBy.
When we look into following line 421 in QueryCompiler.compileJoinQuery
method,we can see QueryCompiler.compileJoinQuery method is invoked to compile
the above test case's RHS subquery: (select bid,code from merge2 order by
code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by
b.bid asc.
{code}
420 Scan rhsScan = ScanUtil.newScan(originalScan);
421 StatementContext rhsCtx = new StatementContext(statement,
context.getResolver(), rhsScan, new SequenceManager(statement));
422 QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true,
true, rhsOrderBy);
{code}
QueryCompiler.compileJoinQuery then invokes following
SubselectRewriter.applyOrderBy to apply the OrderBy which is order by b.bid asc
to RHS subquery statement which is (select bid,code from merge2 order by code
limit 1), in line 259 we can see "order by code" in subquery is overrided by
"order by b.bid", and the subquery becomes:
(select bid,code from merge2 order by bid limit 1).
{code}
252 private SelectStatement applyOrderBy(SelectStatement statement,
List<OrderByNode> orderBy) throws SQLException {
253 List<OrderByNode> orderByRewrite = Lists.<OrderByNode>
newArrayListWithExpectedSize(orderBy.size());
254 for (OrderByNode orderByNode : orderBy) {
255 ParseNode node = orderByNode.getNode();
256 orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this),
orderByNode.isNullsLast(), orderByNode.isAscending()));
257 }
258
259 return NODE_FACTORY.select(statement, orderByRewrite);
260 }
{code}
Obviously ,because there is limit statement in subquery,we can not simply
override OrderBy in subquery, and the subquery should be :
select bid,code (select bid,code from merge2 order by code limit 1) order by
bid
was (Author: comnetwork):
The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the
RHS subquery's OrderBy.
When we look into following line 421 in QueryCompiler.compileJoinQuery
method,we can see QueryCompiler.compileJoinQuery method is invoked to compile
the above test case's RHS subquery: (select bid,code from merge2 order by
code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by
b.bid asc.
{code}
420 Scan rhsScan = ScanUtil.newScan(originalScan);
421 StatementContext rhsCtx = new StatementContext(statement,
context.getResolver(), rhsScan, new SequenceManager(statement));
422 QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true,
true, rhsOrderBy);
{code}
QueryCompiler.compileJoinQuery then invokes following
SubselectRewriter.applyOrderBy to apply the OrderBy which is order by b.bid asc
to RHS subquery statement which is (select bid,code from merge2 order by code
limit 1), in line 259 we can see "order by code" in subquery is overrided by
"order by b.bid", and the subquery becomes:
(select bid,code from merge2 order by bid limit 1).
{code}
252 private SelectStatement applyOrderBy(SelectStatement statement,
List<OrderByNode> orderBy) throws SQLException {
253 List<OrderByNode> orderByRewrite = Lists.<OrderByNode>
newArrayListWithExpectedSize(orderBy.size());
254 for (OrderByNode orderByNode : orderBy) {
255 ParseNode node = orderByNode.getNode();
256 orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this),
orderByNode.isNullsLast(), orderByNode.isAscending()));
257 }
258
259 return NODE_FACTORY.select(statement, orderByRewrite);
260 }
{code}
Obviously ,because there is limit statement in subquery,we can not simply
override OrderBy in subquery, and the subquery should be :
select bid,code (select bid,code from merge2 order by code limit 1) order by
bid
> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> ------------------------------------------------------------------
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.9.0
> Reporter: chenglei
>
> Let us look a simple test case:
> h4. 1. Create two tables
> {noformat}
> CREATE TABLE IF NOT EXISTS MERGE1 (
> AID INTEGER PRIMARY KEY
> AGE INTEGER
> );
> CREATE TABLE IF NOT EXISTS MERGE2 (
> BID INTEGER PRIMARY KEY,
> CODE INTEGER
> );
> {noformat}
> h4. 2. Upsert values
> {noformat}
> UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
> UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
> UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
> UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
> UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
> UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
> {noformat}
> h4. 3. Execute query
> {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
> (select aid,age from merge1 where age >=11 and age<=33) a inner
> join
> (select bid,code from merge2 order by code limit 1) b on
> a.aid=b.bid
> {noformat}
> h4. (/) Expected result
> {noformat}
> 3,44
> {noformat}
> h4. (!) Incorrect actual result
> {noformat}
> 1,66
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
