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
>
