Stamatis, When you say "generate semi/anti joins directly” do you mean generate RexSubQuery expressions? I hope so. In the past SqlToRelConverter tried to convert sub-queries to RelNodes but things got too complex. RexSubQuery expressions (dealt with by SubQueryRemoveRule during planning) is the way to go.
Also, it is important to keep in mind the disjunctive case (e.g. “WHERE … OR … IN (SELECT …)”) and value generation for correlating variables. It’s like juggling - we have to keep all of these balls in the air simultaneously, otherwise we are not solving the problem. Julian > On Apr 13, 2020, at 7:36 PM, Fan Liya <[email protected]> wrote: > > Hi Stamatis, > > Thanks a lot for the information. > If nobody is working on it, may I start to fix it? It is blocking an issue > we are working on. > > Best, > Liya Fan > > On Mon, Apr 13, 2020 at 9:51 PM Stamatis Zampetakis <[email protected]> > wrote: > >> Hi Liya, >> >> Regarding this choice I guess you can have a look in the following >> discussions [1, 2]. >> I think there is consensus that we should change this behavior in >> SqlToRelConverter and generate semi/anti joins directly when this is >> possible but I think that nobody is working on it at the moment. >> >> Best, >> Stamatis >> >> [1] >> >> https://lists.apache.org/thread.html/ra7d7a309592d327a5e912ded7d541c79a3c587fa2daba950cc463762%40%3Cdev.calcite.apache.org%3E >> [2] >> >> https://lists.apache.org/thread.html/f77bf1f946b8c026989163294a2134e485fe7897201c479bbb528c6d%40%3Cdev.calcite.apache.org%3E >> >> On Mon, Apr 13, 2020 at 2:11 PM Fan Liya <[email protected]> wrote: >> >>> Hi all, >>> >>> Given a SQL query with an in subquery: >>> >>> select a.name >>> from dept a >>> where a.deptno in ( >>> select b.deptno * 2 >>> from dept b) >>> >>> Calcite translates it to an inner join, but the correct way should be >>> translating it to a left semi join. >>> >>> According to the comments in the code, it seems this behavior is a design >>> choice. >>> So can you please help explain the rationale behind this? Do we have a >> plan >>> to fix it? >>> >>> Best, >>> Liya Fan >>> >>
