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