[jira] [Comment Edited] (CALCITE-525) Exception-handling in built-in functions

["Hongze Zhang (JIRA)" <ji...@apache.org>]

    [ 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)