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 <[email protected]> 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 <[email protected]> 于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 <[email protected]> 于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 <[email protected]> 于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 <[email protected]> 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 <[email protected]> 写道： >>>>>> >>>>>> 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 <[email protected]>: >>>>>> >>>>>>> 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 <[email protected]> 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
