[
https://issues.apache.org/jira/browse/CALCITE-1507?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15723361#comment-15723361
]
Maryann Xue commented on CALCITE-1507:
--------------------------------------
Could you please kindly review my PR for this issue
(https://github.com/apache/calcite/pull/333)?
In order to verify the count-preserving case in {{testSortJoinTranspose6()}}, I
added unique keys into SALES tables. As a result, AggregateFilterTransposeRule
and AggregateJoinTransposeRule were not fired in a few tests in RelOptRuleTest,
since they bail out for already unique group keys. Also, change of uniqueness
will affect row count number in RelMetadataTest. So I made some adjustment to
the affected tests by replacing unique key columns with non-unique key columns.
> OFFSET cannot be pushed through a JOIN if the non-preserved side of outer
> join is not count-preserving
> ------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-1507
> URL: https://issues.apache.org/jira/browse/CALCITE-1507
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.10.0
> Reporter: Maryann Xue
> Assignee: Maryann Xue
> Priority: Minor
>
> If the non-preserved side of the outer join is not count-preserving, for each
> row from the preserved side, there can be zero, one or multiple matches from
> the non-preserved side, which means the join can produce one or multiple
> rows. So it is safe to push a LIMIT through, but it is invalid to push an
> OFFSET through.
> Take this query as an example:
> {code}
> select d.deptno, empno
> from sales.dept d
> left join sales.emp e using (deptno)
> order by d.deptno offset 1
> {code}
> And rows from "dept" and "emp" tables are like:
> {code}
> "dept"
> deptno
> 10
> 20
> 30
> "emp"
> empno deptno
> 101 10
> 102 10
> 105 30
> {code}
> The expected output is:
> {code}
> d.deptno e.empno
> 10 102
> 20 null
> 30 105
> {code}
> While after applying SortJoinTransposeRule, the rel becomes:
> {code}
> LogicalProject(DEPTNO=[$0], EMPNO=[$2])
> LogicalSort(sort0=[$0], dir0=[ASC], offset=[1])
> LogicalJoin(condition=[=($0, $9)], joinType=[left])
> LogicalSort(sort0=[$0], dir0=[ASC], offset=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> And the output will now be:
> {code}
> d.deptno e.empno
> 20 null
> 30 105
> {code}
> because deptno "10" has been skipped from the left relation by the pushed
> through Sort node.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
