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