Re: Different behavior bewteen '>' and '='

[Benchao Li]

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  于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  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  于2022年4月10日周日 16:03写道：
> >
> >> Hi BenChao,
> >>Thanks to bring this up again. I find another relative issue
> ISSUE-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  于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  于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  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  写道：
> >>
> >> 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 

Re: Different behavior bewteen '>' and '='

[Julian Hyde]

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  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  于2022年4月10日周日 16:03写道：
> 
>> Hi BenChao,
>>Thanks to bring this up again. I find another relative issue ISSUE-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  于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  于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  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  写道：
>> 
>> 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 :
>> 
>>> 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?’ 

Re: Different behavior bewteen '>' and '='

[Benchao Li]

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  于2022年4月10日周日 16:03写道：

> Hi BenChao,
> Thanks to bring this up again. I find another relative issue ISSUE-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  于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  于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  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  写道：
> > > > >
> > > > > 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 :
> > > > >
> > > > >> 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  wrote:
> > > > >>>
> > 

Re: Different behavior bewteen '>' and '='

[xiong duan]

Hi BenChao,
Thanks to bring this up again. I find another relative issue ISSUE-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  于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  于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  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  写道：
> > > >
> > > > 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 :
> > > >
> > > >> 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  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 

Re: Different behavior bewteen '>' and '='

[Benchao Li]

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  于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  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  写道：
> > >
> > > 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 :
> > >
> > >> 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  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 '='

[Stamatis Zampetakis]

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  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  写道：
> >
> > 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 :
> >
> >> 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  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?
> >>
> >>
>
>

Re: Different behavior bewteen '>' and '='

[Zou Dan]

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  写道：
> 
> 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 :
> 
>> 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  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?
>> 
>> 

Re: Different behavior bewteen '>' and '='

[Vladimir Ozerov]

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 :

> 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  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?
>
>

Re: Different behavior bewteen '>' and '='

[Julian Hyde]

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  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?

Different behavior bewteen '>' and '='

[Zou Dan]

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?