[jira] [Commented] (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=16643086#comment-16643086 ] 

Hongze Zhang commented on CALCITE-525:
--------------------------------------

Hi [~julianhyde], I have filed a [PR|https://github.com/apache/calcite/pull/881] for this.

*As a explanation:*

Developers may want to handle these exceptions in following methods:
* Method 1. Throws the error
* Method 2. Ommits the row (Log the error detail if necessary)
* Method 3. Makes the related call return null / return default value depending on the return type (Log the error detail if necessary)
* Method 4. ...

Correct me if wrong, I personally think that there is a difference between Method 1/2 and Method 3: It is not possible to implement Method 1/2 just by changing the logic inside function implementation(from SqlFunctions.java), Actually it needs changes to outside processing logic. Whereas developers could do Method 3 inside the function implementation(from SqlFunctions.java).

The PR is a tentative work: I have implemented Method 1 and Method 2 by decorating the enumerator generated from EnumerableCalc, which means, the rex calls from other places(such as aggregate, window... ) are not considered yet. So if the direction is OK, this could be an experimental feature for a period of time.

And the fix could cause a problem that is a little counter-intuitive:
Say if user has SQL: 

{code:sql}
select x / y from (values (1, 0), (2, 1), (3, 0)) as t(X, Y)
{code}

When ExceptionHandlerEnum.DISCARD is enabled, Calcite returns a single row because 1 / 0 and 3 / 0 are ommitted.

However if user executes:

{code:sql}
select count(*) from (select x / y from (values (1, 0), (2, 1), (3, 0)) as t(X, Y))
{code}

Calcite returns 3, but not 1. The reason is that optimizer dropped the concrete division call because count\(*\) does not need that.

I have added JdbcTest#testExceptionHandler8 for this case.

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