[
https://issues.apache.org/jira/browse/CALCITE-1498?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15687640#comment-15687640
]
Maryann Xue commented on CALCITE-1498:
--------------------------------------
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.
> Allow LIMIT with trivial ORDER BY to be pushed through JOIN
> -----------------------------------------------------------
>
> Key: CALCITE-1498
> URL: https://issues.apache.org/jira/browse/CALCITE-1498
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.10.0
> Reporter: Maryann Xue
> Assignee: Maryann Xue
> Priority: Minor
>
> Allow LIMIT with trivial ORDER BY to be pushed through a JOIN that does not
> affect the number of rows. Currently it cannot, because
> {{RelMdUtil.checkInputForCollationAndLimit}} does not know that an sort on
> zero keys is trivially satisfied (without requiring a Sort) by any relational
> expression:
> {code}
> // Check if the input is already sorted
> boolean alreadySorted = false;
> if (!alreadySorted) {
> for (RelCollation inputCollation : mq.collations(input)) {
> if (inputCollation.satisfies(collation)) {
> alreadySorted = true;
> break;
> }
> }
> }
> {code}
> if {{mq.collations(input)}} returns an empty array, {{alreadySorted}} will
> always be false even if the required {{collation}} is an empty collation
> (which indicates there's no need to sort).
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
