Heh, it's funny you say that because Stamatis filed this improvement request in a meeting where I was complaining about things being more complicated and having broken code because of SEARCH :)
My main complaint is this, and perhaps it makes sense to file another Jira for a potential new improvement: I totally get SEARCH and its usefulness. So I have no complaints about the existence of SEARCH, why it is needed, and why it makes sense. My problem with SEARCH is simply that it makes my code more complex. I kinda think of it this way: The SEARCH operator is a superset of the BETWEEN operator. And we either are ok with having a representation for both or we are stuck with it due to legacy reasons. I prefer the former explanation, heh. So why not have an IN_FUNC operator as well? No need to get rid of SEARCH! Just keep it as a superset. And then create a RexUtil method that does the work for me that converts a relevant SEARCH to an IN_FUNC. Perhaps the general issue with IN is this: Like SEARCH, IN also has a broader definition than just a function, but the broader definition is different from SEARCH. In our internal discussion, we talked about how "IN" is always a semi-join of some nature, sometimes of course with a subquery. I believe this is how Calcite treats IN. But in practice, IN has a specialized case as something that fits right into what a RexCall/function does. I think many databases (at least the two I work on) implement this specialized case of IN as a function internally, just like BETWEEN. So to have a 1:1 RexCall operator to function for IN just seems to make intuitive sense for me. Perhaps we don't have a problem with the existence of a BETWEEN operator stems from the fact that BETWEEN doesn't have the same type of overload that IN does. So yeah though: If we don't think that an IN type operator is good for Calcite, I need to develop code local to my database that does a conversion to a function which differs from my normal RexCall conversion. And my intuition tells me that this effort has already been done elsewhere. Which is why I think it belongs in Calcite, where the community would benefit. Anyway, having said all this: the improvement in CALCITE-7226 will definitely help me out because creating all those ORs before generating a SEARCH seems quite inefficient. It might even lead to problems in my code as the multiple ORs generate many layers of RexNodes which I think created some really slow compilations for me during one of my tests. On Tue, Oct 14, 2025 at 11:30 AM Julian Hyde <[email protected]> wrote: > It’s nice to see issues like > https://issues.apache.org/jira/browse/CALCITE-7226 "Convert IN to SEARCH > in SqlToRelConverter” being logged. > > When I introduced SEARCH I got a lot of complaints about how I was making > plans more complicated and generally breaking things. All I was really > doing was bringing the concept of a Sarg [1] into our logical algebra. I’m > glad that people now see that SEARCH leads to more robust, efficient plans. > > Julian > > [1] https://en.wikipedia.org/wiki/Sargable
