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