[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16646100#comment-16646100
]
Hongze Zhang edited comment on CALCITE-525 at 10/11/18 8:13 AM:
----------------------------------------------------------------
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 discards 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 their SQLs, 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.
was (Author: zhztheplayer):
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)
