You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Benchao Li <li...@apache.org> on 2022/04/09 12:51:28 UTC
Re: Different behavior bewteen '>' and '='
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 <za...@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 <zo...@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 <pp...@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 <jh...@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 <zo...@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
Re: Different behavior bewteen '>' and '='
Posted by Benchao Li <li...@apache.org>.
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 <jh...@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 <li...@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 <no...@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 <li...@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 <za...@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 <zo...@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 <pp...@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 <jh...@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 <zo...@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
Re: Different behavior bewteen '>' and '='
Posted by Julian Hyde <jh...@gmail.com>.
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 <li...@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 <no...@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 <li...@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 <za...@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 <zo...@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 <pp...@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 <jh...@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 <zo...@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
Re: Different behavior bewteen '>' and '='
Posted by Benchao Li <li...@apache.org>.
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 <no...@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 <li...@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 <za...@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 <zo...@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 <pp...@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 <jh...@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 <zo...@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
Re: Different behavior bewteen '>' and '='
Posted by xiong duan <no...@gmail.com>.
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 <li...@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 <za...@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 <zo...@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 <pp...@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 <jh...@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 <zo...@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
>