Thanks for sharing Julian! To be honest, I was thinking of generating anti/semi join RelNodes and not subquery expressions but I said that without diving into the code. If you are saying that is complex then I trust your judgment.
Best, Stamatis On Tue, Apr 14, 2020, 8:40 PM Julian Hyde <[email protected]> wrote: > 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 > >>> > >> > >
