[
https://issues.apache.org/jira/browse/CALCITE-7270?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18036192#comment-18036192
]
Stamatis Zampetakis commented on CALCITE-7270:
----------------------------------------------
The term NULLABLE is a bit misleading cause the standard division is also
nullable when one of the inputs is null but I don't have better ideas at the
moment.
I was also thinking about all the possible variants of division and kept
wondering what's the best way to model it. If we consider the two features
(ZERO_NULL/ZERO_ERROR, CHECKED/UNCHECKED) that we identified so far we have
already 4 different operators. It seems that at the moment the mapping would be
something like:
* SQL standard -> UNCHECKED_ZERO_ERROR_DIVIDE
* Hive -> UNCHECKED_ZERO_NULL_DIVIDE
* Postgres -> CHECKED_ZERO_NULL_DIVIDE
* Other -> CHECKED_ZERO_ERROR_DIVIDE
If in the future we discover another feature then the combinations grow
exponentially. Moreover, it is likely that apart from division other operators
(e.g. mod) will require similar handling. So far we have been adding new
operators for each different characteristic but is this gonna scale?
In the context of simplifications (that I have really fresh from the
discussions in CALCITE-7145), I see two elements of the Hive DIVIDE operator
that are important:
* safety; the Hive division never throws which allows for various
simplifications that are otherwise blocked
* Strong.Policy; the Hive division does not adhere to ANY as the standard
DIVIDE operator cause it returns null even when the arguments are not null
Adding a system specific (e.g., Hive, Postgres, Standard) operator is rather
easy but trying to capture and model similarities in the overall behavior is
challenging.
> Add support for a SAFE_DIVIDE operation
> ---------------------------------------
>
> Key: CALCITE-7270
> URL: https://issues.apache.org/jira/browse/CALCITE-7270
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.41.0
> Reporter: Mihai Budiu
> Priority: Minor
>
> SQL dialects treat DIVISION in different ways: in some dialects division by 0
> produces an exception, while in other dialects (e.q. sqlite) it produces NULL.
> These are really two different operators with the same name. I think they
> should be represented by different operations in the IR: e.g., DIVIDE and
> SAFE_DIVIDE.
> This is reminiscent to the CHECKED arithmetic operations introduced in
> [CALCITE-6685], and the solution could be similar: in that issue whether an
> ADD operation is checked or unchecked is a property of SqlConformance.
> Similarly, we could add a boolean flag to SqlConformance indicating whether
> division is safe or not and a visitor that will rewrite DIVIDE to SAFE_DIVIDE
> when necessary.
> Spawn from [CALCITE-7145]
> I am happy to assign this to myself if people agree.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
