[
https://issues.apache.org/jira/browse/CALCITE-5343?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17623424#comment-17623424
]
Mihai Budiu commented on CALCITE-5343:
--------------------------------------
https://issues.apache.org/jira/browse/CALCITE-4749 seems related.
Indeed, it looks like there are several possible semantics for integer (or
decimal division), and that different databases make different choices.
Division can
- return a nullable result and NULL at runtime when dividing by 0 (or perhaps
in other overflow conditions as well),
- or it can return a non-nullable result, and then it has to throw an exception
at runtime for division by 0.
- MySQL seems to do both: division by 0 is NULL, division which overflows (by a
very small decimal) gives a runtime exception.
I expected that using SqlConformanceEnum.MYSQL_5 for the validator would
implement this behavior, but that does not seem to be the case.
My workaround around this problem is to replace division with a user-defined
function in the SqlNode tree, and then use custom type inference rules for this
function. I am guessing this could be done by constructing a custom
SqlValidator, but I haven't figured out how to do it yet.
> Type of division operator
> -------------------------
>
> Key: CALCITE-5343
> URL: https://issues.apache.org/jira/browse/CALCITE-5343
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Mihai Budiu
> Priority: Minor
>
> The Calcite type checker assigns a type of INTEGER (not nullable) to the
> DIVIDE RexCall in the following statement: `SELECT 1/0`. Some databases, such
> as MySQL, evaluate this expression to NULL.
> This result is obtained even when using SqlConformanceEnum.MYSQL_5 for the
> parser and the validator.
> My question is whether there is some other way to influence how type
> inference is performed for division.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
