[
https://issues.apache.org/jira/browse/CALCITE-4742?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17406301#comment-17406301
]
Julian Hyde commented on CALCITE-4742:
--------------------------------------
Rewrites of this kind are non-trivial.
For instance, your {code} select distinct(e.*) from "scott".emp as e inner
join (select 10 as empno) as q on e.empno <> q.empno;{code}rewrite clearly
doesn't work in general because it eliminates duplicates.
The query needs to return UNKNOWN as distinct from FALSE. So, write tests where
the sub-query is in the SELECT clause.
Also write tests where the inner relation is empty, or contains only nulls, or
contains some nulls.
For the NOT IN rewrite, which is of a similar complexity, we had to count the
rows in the sub-query and also count the number of nulls, in order to correctly
distinguish FALSE from UNKNOWN.
> Implement NOT EQUALS in some subquery
> -------------------------------------
>
> Key: CALCITE-4742
> URL: https://issues.apache.org/jira/browse/CALCITE-4742
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Ivan Daschinsky
> Assignee: duan xiong
> Priority: Major
> Fix For: 1.28.0
>
>
> When executing
> {code}
> select * from "scott".emp where empno <> any (select 10);
> {code}
> with assertions, got assertion error
> {code}
> ---
> > java.lang.AssertionError
> > at
> > org.apache.calcite.rel.rules.SubQueryRemoveRule.rewriteSome(SubQueryRemoveRule.java:170)
> > at
> > org.apache.calcite.rel.rules.SubQueryRemoveRule.apply(SubQueryRemoveRule.java:92)
> > at
> > org.apache.calcite.rel.rules.SubQueryRemoveRule.matchFilter(SubQueryRemoveRule.java:637)
> > at
> > org.apache.calcite.rel.rules.SubQueryRemoveRule.access$100(SubQueryRemoveRule.java:71)
> > at
> > org.apache.calcite.rel.rules.SubQueryRemoveRule$Config.lambda$static$3(SubQueryRemoveRule.java:701)
> > at
> > org.apache.calcite.rel.rules.SubQueryRemoveRule.onMatch(SubQueryRemoveRule.java:82)
> > at
> > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:341)
> > at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:565)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:428)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:282)
> > at
> > org.apache.calcite.plan.hep.HepInstruction$RuleCollection.execute(HepInstruction.java:77)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:208)
> > at
> > org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:195)
> > at org.apache.calcite.tools.Programs.lambda$of$0(Programs.java:176)
> > at
> > org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:335)
> > at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:172)
> > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:306)
> > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:215)
> > at
> > org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:647)
> > at
> > org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513)
> > at
> > org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483)
> > at
> > org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249)
> > at
> > org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:623)
> {code}
> This query cannot be rewritten as IN query, so it goes to
> {{SubQueryRemoveRule.rewriteSome}}. Current logic in this method doesn't work
> on this case and produce wrong plan.
> This query should be rewritten to something like this:
> {code}
> select distinct(e.*) from "scott".emp as e inner join (select 10 as empno)
> as q on e.empno <> q.empno;
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
