[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16646100#comment-16646100
]
Hongze Zhang commented on CALCITE-525:
--------------------------------------
AFAIK, MySQL has [sql
mode|https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict]
ERROR_FOR_DIVISION_BY_ZERO to handle "/ 0" problem, also, many implementations
has NULLIF function that a lot of users used to handle "/ 0".
The problem is not only occurred on division operator, E.g. MSSQL Server
supports
[TRY_CONVERT|https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-2017]
since version 2012.
Putting the exception handler into EnumerableCalc (or the root enumerable)
could not let the function return a default value when error occurred, It
actually drop the whole row (ExceptionHandlerEnum.LOG,
ExceptionHandlerEnum.DISCARD).
I am not sure if any SQL implementation provides a option that discard a row on
error, so this patch is tentative. But this dose provide a possibility to make
the query not to be aborted, especially in large ad-hoc queries, etl tasks or
stream queries.
I have another idea (just a imagination) that we could invent a kind of "error
handling" operator, something like *CATCH_ERROR(1 / 0 EMPTY ON ERROR) or*
*CATCH_ERROR(1 / 0)* *EMPTY* *ON ERROR*. I know in SQL 2016, there is a common
"error behavior" clause inside some of the JSON functions. E.g. JSON_VALUE(...
DEFAULT "foo" ON ERROR), JSON_QUERY(... ERROR ON ERROR), JSON_QUERY(... EMPTY
ON ERROR). By using this way users could have better control to there SQL, say
if user has a SQL including multiple operators, and one operator should return
empty value on error, anther should throw the error directly, Changing
connection level option is not possible to support that.
> Exception-handling in built-in functions
> ----------------------------------------
>
> Key: CALCITE-525
> URL: https://issues.apache.org/jira/browse/CALCITE-525
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Hongze Zhang
> Priority: Major
>
> The standard calls for certain built-in functions to throw exceptions.
> Examples:
> * 1 / 0
> * MOD(1, 0)
> * OVERLAY('foo' PLACING 'x' FROM -1)
> * 'x' NOT LIKE 'x' ESCAPE 'x'
> First, these exceptions should occur at run time. They should cause the
> current value to become null, or the row to be omitted, but should not abort
> the query. (Actual behavior TBD.)
> Second, EnumerableCalc does constant reduction and generates code like
> 'static final int X = 0 / 0'. This code blows up when the class is loaded. It
> should not. The code should give errors for each row, as described above.
> While fixing this bug, see SqlOperatorBaseTest.testArgumentBounds and remove
> restrictions related to /, MOD and OVERLAY, LIKE.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
