[
https://issues.apache.org/jira/browse/CALCITE-7232?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18030398#comment-18030398
]
Steve Carlin commented on CALCITE-7232:
---------------------------------------
Just to add a little more color to the downside of adding the IN operator:
While I don't know the code well, I would imagine this downside already exists
for certain operators like BETWEEN and ">", among others. But I do get that IN
is more likely to be confused with SEARCH as opposed to BETWEEN.
My vote would be that we need an IN operator to keep the symmetry with
everything else. The RexUtil.expandSearch() method was created because, as you
mentioned, most databases don't have the ability to handle SEARCH directly.
And there can only be one expansion (well, more if we have config parameters or
an "expandSearch2", but that gets ugly really quickly) of SEARCH into more
common database functions. But IN is the only common case I am aware of where
the user specified an O(1) type search in the SQL (a hash table can be easily
created with the IN literals) but changed to an O(m) search when it comes out
of SEARCH as a variety of linked ORs.
> Restore use of IN operator in RexCall
> -------------------------------------
>
> Key: CALCITE-7232
> URL: https://issues.apache.org/jira/browse/CALCITE-7232
> Project: Calcite
> Issue Type: Task
> Reporter: Stamatis Zampetakis
> Priority: Major
>
> The use of {{IN}} operator in {{RexCall}} was superseded by the introduction
> of the {{SEARCH}} operator (CALCITE-4173) and its use is strictly forbidden
> through
> [assertions|https://github.com/apache/calcite/blob/6cbbf560b721cb88354c33751aa72b16a58ded23/core/src/main/java/org/apache/calcite/rex/RexCall.java#L94].
> The {{SEARCH}} operator is more general and powerful than {{IN}} so it's a
> perfect abstraction to use during the optimization phase.
> However, most databases don't have a {{SEARCH}} operator so the latter needs
> to be transformed back to {{IN}} (or something else) at some point in time.
> For instance, Apache Hive has two ways of generating an executable plan:
> * take a {{RelNode}} and generate an AST tree
> * take a {{RelNode}} and generate a Hive Operator tree
> both of which are eventually going to be executed.
> *If we don't allow* IN in a RexCall, then it means that we need to create
> special code to handle SEARCH in both code paths that differ only slightly in
> each case. (In reality the situation is more complicated for Hive because
> there are at least two more places where we need to do a SEARCH to IN
> transformation).
> *If we allow IN* in a RexCall, then at the end of the RelNode optimization
> phase we can "expand" {{SEARCH}} to {{IN}} so the transformation logic only
> appears in one place and it remains a {{RelNode}} to {{RelNode}} conversion.
> In fact, the same transformation logic could be exploited in
> [SqlImplementor|https://github.com/apache/calcite/blob/6cbbf560b721cb88354c33751aa72b16a58ded23/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L815]
> that does another {{RelNode}} to "something" conversion.
> The obvious downside with this proposal is that if people start mixing the IN
> operator in various optimization rules/phases it can certainly affect the
> quality of the plans and the planning time.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
