[CALCITE-4742] Implement "<> SOME" sub-query

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Ivan Daschinsky
• Created: 17/Aug/21 11:37
• Updated: 20/Oct/21 05:25
• Resolved: 17/Sep/21 21:45

Description

When executing

select * from "scott".emp where empno <> any (select 10);

with assertions, got assertion error

---
> 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)

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:

select distinct(e.*)  from "scott".emp as e inner join (select 10 as empno) as q on e.empno <> q.empno;

Issue Links

• Blocked: CALCITE-4756 When subquery include NULL value, Calcite should return the right result
• Blocked: CALCITE-4748 If there are duplicate GROUPING SETS, Calcite should return duplicate rows
• is related to: IGNITE-15239 Calcite. AssertionError SubQueryRemoveRule.rewriteSome
• links to: GitHub Pull Request #2512
• links to: THE CALCITE-4742 PR