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
