Hi Julian, Sorry to make you confused about this.
Let me try to simplify this discussion. What we are facing now, is the implicit type coercion problem, which is introduced in CALCITE-613 [1] and CALCITE-2302 [2]. These two features behaves differently in the following case: # SQL "select ename = empno from emp" # plan using CALCITE-2302 LogicalProject(EXPR$0=[=(CAST($1):INTEGER NOT NULL, $0)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) # plan using CALCITE-613 LogicalProject(EXPR$0=[=($1, $0)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) Hence what I'm proposing here is about how do we deal with these two feature: #1, Keep CALCITE-2302 only, remove (or deprecate now and remove later) CALCITE-613 #2, Keep them both, but align CALCITE-613's behavior with CALCITE-2302 #3, Keep them both, and doesn't change anything. If this still confuses you, I can open another dedicated discussion for this. [1] https://issues.apache.org/jira/browse/CALCITE-613 [2] https://issues.apache.org/jira/browse/CALCITE-2302 Julian Hyde <jhyde.apa...@gmail.com> 于2022年4月11日周一 20:35写道： > I’m confused. Is this thread about type derivation, coercion, > simplification, or query execution? Those are orthogonal topics, so > discussing more than one at a time adds confusion. > > > On Apr 11, 2022, at 4:54 AM, Benchao Li <libenc...@apache.org> wrote: > > > > Hi xiong, > > > > Thanks for the input. Yes, CALCITE-4993 is related to this issue. > > And if we choose #1 or #2, CALCITE-4993 would be solved too. > > > > xiong duan <nobigo...@gmail.com> 于2022年4月10日周日 16:03写道： > > > >> Hi BenChao, > >> Thanks to bring this up again. I find another relative issue > ISSUE-4993 > >> <https://issues.apache.org/jira/browse/CALCITE-4993> about the > >> RexSimplify. Because > >> the EQUALS and NOT-EQUALS Operator uses the *LEAST_RESTRICTIVE* > strategy to > >> validate the parameter. Other comparators use the *COMPARE* strategy*. > >> *Please > >> check Jira for details. > >> > >> Benchao Li <libenc...@apache.org> 于2022年4月9日周六 20:51写道： > >> > >>> Hi all, > >>> > >>> Sorry to bring this up again. > >>> > >>> I kind of agree with Stamatis. The behavior for '=' is not only > different > >>> from '>', > >>> but also different from the CALCITE-2302's implementation. > >>> > >>> '=' in CALCITE-613 do not add 'cast' operator, and this will complicate > >>> physical implementation. > >>> Even more, Calcite's own enumerable convention cannot handle this. > >>> > >>> From my perspective, there's something we can do: > >>> #1, remove CALCITE-613's implementation, only keep CALCITE-2302. > >>> #2, keep CALCITE-613, but make it consistent with CALCITE-2302 for '=' > >>> (Also control CALCITE-613 via > >>> SqlValidator#Config#typeCoercionEnabled). > >>> #3, leave it as it is, but we need to fix the enumerable convention for > >>> this case. > >>> > >>> And the list is also my preference, WDYT? > >>> > >>> > >>> Stamatis Zampetakis <zabe...@gmail.com> 于2022年1月13日周四 22:00写道： > >>> > >>>> Hi all, > >>>> > >>>> Actually I find it very confusing the fact that > and = behave > >>> differently > >>>> and I would consider this a bug. > >>>> > >>>> From the SQL standard perspective I don't think this is a valid query > >> and > >>>> as others mentioned it fails in the category of implicit type > >>> conversions. > >>>> My take is that if implicit type conversions are disabled both should > >>> raise > >>>> validation errors. > >>>> > >>>> From an implementation perspective the > >>>> SqlOperandTypeChecker.Consistency enumeration was added by CALCITE-613 > >>> [1] > >>>> to handle some common cases of implicit conversions. > >>>> However, CALCITE-2302 [2] went one step further to deal with many more > >>>> cases of implicit conversions. > >>>> I don't have the full picture in mind but from my perspective the code > >>>> around the Consistency enumeration should be removed/unified with the > >> new > >>>> type conversion APIS. > >>>> > >>>> Best, > >>>> Stamatis > >>>> > >>>> [1] https://issues.apache.org/jira/browse/CALCITE-613 > >>>> [2] https://issues.apache.org/jira/browse/CALCITE-2302 > >>>> > >>>> > >>>>> On Thu, Jan 13, 2022 at 2:58 AM Zou Dan <zoud...@163.com> wrote: > >>>>> > >>>>>> Thank you both for your replies, I will find if there is a better > way > >>> to > >>>>> solve my problem. > >>>>> > >>>>> Best, > >>>>> Dan Zou > >>>>> > >>>>>> 2022年1月11日 20:33，Vladimir Ozerov <ppoze...@gmail.com> 写道： > >>>>>> > >>>>>> Hi, > >>>>>> > >>>>>> If I recall correctly, the SQL standard is mostly silent on how one > >>>>> should > >>>>>> coerce operands. Therefore different systems implement the coercion > >>> at > >>>>>> their discretion. Moreover, the type inference might be influenced > >>> not > >>>>> only > >>>>>> by operands types but by their nature as well. For example, a > >> target > >>>>> system > >>>>>> may be ok with "intCol = '1'", but fail for "intCol = strCol". > >>>>>> > >>>>>> If you are not satisfied with the default Apache Calcite behavior, > >>> you > >>>>> want > >>>>>> to provide a custom function definition in your own > >> SqlOperatorTable, > >>>>> that > >>>>>> would override functions from the SqlStdOperatorTable. The > >> interfaces > >>>>> that > >>>>>> govern type inference are relatively straightforward to implement > >>>>>> (SqlOperandTypeChecker, SqlOperandTypeInference, > >>>> SqlReturnTypeInference). > >>>>>> You may possibly face a surprising behavior in some cases. E.g., if > >>> you > >>>>>> override a base function (e.g. EQUALS), the parser might ignore > >> your > >>>>> custom > >>>>>> definition and use the one from the SqlStdOperatorTable, as it is > >>>>>> hard-coded into the parser's code. In this case, you may need to > >>>>> implement > >>>>>> a custom visitor that would forcefully rewrite Calcite functions to > >>>> your > >>>>>> custom ones. In more complicated cases, you may need to override > >>> parts > >>>> of > >>>>>> validator/converter/coercion, but hopefully, your problem is not > >> that > >>>>>> complex. > >>>>>> > >>>>>> Regards, > >>>>>> Vladimir. > >>>>>> > >>>>>> вт, 11 янв. 2022 г. в 07:43, Julian Hyde <jhyde.apa...@gmail.com>: > >>>>>> > >>>>>>> Yes, this is by design.I believe that the SQL standard set the > >>> rules. > >>>>>>> > >>>>>>> It’s not that surprising that ‘=‘ has different behavior than > >>>>>>> ordering-based comparisons such as ‘>’. Consider: given a DATE > >> value > >>>> d, > >>>>> and > >>>>>>> a TIMESTAMP value t, it is reasonable to ask ‘is t > d?’ but less > >>>>>>> reasonable to ask ‘does t = d?' > >>>>>>> > >>>>>>>> On Jan 10, 2022, at 6:35 PM, Zou Dan <zoud...@163.com> wrote: > >>>>>>>> > >>>>>>>> Hi community, > >>>>>>>> I recently ran into a problem that when we disable type coercion > >> by > >>>>>>> SqlValidator#setEnableTypeCoercion(false), > >>>>>>>> there will be two different behaviors between '>' and '=': > >>>>>>>> 1. '>' between character and numeric (e.g. '1' > 1), the > >> character > >>>> will > >>>>>>> be implicitly converted to numeric > >>>>>>>> 2. '=' between character and numeric (e.g. '1' = 1), the > >> character > >>>> will > >>>>>>> `not` be implicitly converted to numeric > >>>>>>>> I find the reason is that the SqlOperandTypeChecker.Consistency > >> for > >>>>>>> SqlStdOperatorTable.GREATER_THAN is `COMPARE` while > >>>>>>>> SqlStdOperatorTable.EQUALS is `LEAST_RESTRICTIVE`. > >>>>>>>> Is this by design? > >>>>>>> > >>>>>>> > >>>>> > >>>>> > >>>> > >>> > >>> > >>> -- > >>> > >>> Best, > >>> Benchao Li > >>> > >> > > > > > > -- > > > > Best, > > Benchao Li > -- Best, Benchao Li
