[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16651281#comment-16651281
]
Hongze Zhang commented on CALCITE-525:
--
[~vladimirsitnikov]
The link is here: https://issues.apache.org/jira/browse/HIVE-5438
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16651272#comment-16651272
]
Vladimir Sitnikov commented on CALCITE-525:
---
{quote}Also, you can see an issue from HIVE.{quote}
Which one do you mean?
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16651127#comment-16651127
]
Hongze Zhang commented on CALCITE-525:
--
Hi [~julianhyde] and [~vladimirsitnikov],
Thanks for the concern about this issue, and I have read all your comments.
I wonder if we could start from "catch error from a RexCall", because if we
want to drop a row when error happens, it is not that easy to judge which row
should be dropped and which row should be reserved in Calcite.
I personally prefer to make Calcite return an empty or specified value when a
RexCall failed, like what I have described about a "CATCH_ERROR" function. I
hope you could take a look at [a nother
work|https://github.com/zhztheplayer/calcite/commits/525-3]. The work contains
following:
1. A 'CATCH_ERROR(... (EMPTY | ERROR) ON ERROR)' function and implementation;
2. A 'ERROR_MODE' option for Calcite connection: if the 'ERROR_MODE' is
'THROW_ERROR', Calcite's converter will automatically wrap every rex call with
a 'CATCH_ERROR' call.
By having this feature, calcite adaptors can also implement 'CATCH_ERROR' in
their own rex implementation to use 'ERROR_MODE' feature. The cost is to add a
non-standard operator.
Also, you can see an issue from HIVE.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16649112#comment-16649112
]
Vladimir Sitnikov commented on CALCITE-525:
---
{quote}Calcite should provide a facility that allows a handler to choose how to
handle each kind of error{quote}
It should not. None of the major DBs provide that ability, so there's no point
to implement weird data-corruption layer in Calcite.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16649087#comment-16649087
]
Julian Hyde commented on CALCITE-525:
-
It's immaterial which kinds of errors Oracle catches. Calcite should provide a
facility that allows a handler to choose how to handle each kind of error. We
are not forcing people to use it.
I answered your question. Now please stop pursuing me.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16649004#comment-16649004
]
Vladimir Sitnikov commented on CALCITE-525:
---
That does not catch 0/0 kind of errors.
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables004.htm#sthref1787
{quote}Oracle Database logs the following errors during DML operations:
Column values that are too large
Constraint violations (NOT NULL, unique, referential, and check constraints)
Errors raised during trigger execution
Errors resulting from type conversion between a column in a subquery and the
corresponding column of the table
Partition mapping errors
Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows
for MERGE operation.){quote}
{{log errors into}} does NOT catch 0/0 errors.
Here you go:
{code:sql}Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options
SQL> create table julian_error_logging(x number);
Table created.
SQL> exec DBMS_ERRLOG.CREATE_ERROR_LOG('julian_error_logging', 'julian_err');
PL/SQL procedure successfully completed.
SQL> insert into julian_error_logging(x) select 0/0 from dual log errors into
julian_err;
insert into julian_error_logging(x) select 0/0 from dual log errors into
julian_err
*
ERROR at line 1:
ORA-01476: divisor is equal to zero{code}
Just in case:
{code:sql}SQL> insert into julian_error_logging(x) select 1 from dual union all
select 0/0 from dual log errors into julian_err;
insert into julian_error_logging(x) select 1 from dual union all select 0/0
from dual log errors into julian_err
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SQL> select * from julian_error_logging;
no rows selected{code}
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16648996#comment-16648996
]
Julian Hyde commented on CALCITE-525:
-
Here: https://oracle-base.com/articles/10g/dml-error-logging-10gr2
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16648796#comment-16648796
]
Vladimir Sitnikov commented on CALCITE-525:
---
{quote}ability to ignore errors improves usability. That's why it is in every
major DB{quote}
Would you please care to provide a reference how "major DB" provides an ability
to ignore errors in the middle of the query.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16648644#comment-16648644
]
Julian Hyde commented on CALCITE-525:
-
If consistency is paramount for someone, they can continue to run in "fail
everything" mode. It will remain the default.
But we should do this feature, because the ability to ignore errors improves
usability. That's why it is in every major DBMS.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16646233#comment-16646233
]
Hongze Zhang commented on CALCITE-525:
--
Thanks for the example, and I know simply dropping rows does cause some
counter-intuitive effects. That is why I imagined that we could provide a way
to make "value-level" handling, but how to do this is worth to be disscussed:
now developer should catch errors inside the SqlFunctions.java or in the
adaptor rex implementations. Maybe like what I said, providing a operator to do
error handling is also a way.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16646168#comment-16646168
]
Vladimir Sitnikov commented on CALCITE-525:
---
{quote}The row will be emitted, since the second input does not emit its
row.{quote}
I find that very disturbing, and I find lack of consistency a bad thing.
Consider
Q1)
{code:sql}select * from orders where order_id not in (1, 2, 0/0){quote}
Q2)
{code:sql}select * from orders o where order_id not in (select * from
values(1),(2),(0/0)){quote}
As you say, Q1 would return 0 rows, and Q2 would return all rows from orders
table, however the queries are pretty much "the same".
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16646160#comment-16646160
]
Hongze Zhang commented on CALCITE-525:
--
{quote}Suppose there's SEMI-ANTI-JOIN. Suppose you drop the row from the second
input of the join. What should be the overall result? Should the row from the
first input be emitted? Should it be discarded?
{quote}
The row will be emitted, since the second input does not emit its row.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16646114#comment-16646114
]
Vladimir Sitnikov commented on CALCITE-525:
---
{quote} It actually drop the whole row (ExceptionHandlerEnum.LOG,
ExceptionHandlerEnum.DISCARD).{quote}
How does it work for JOINS?
Suppose there's SEMI-ANTI-JOIN. Suppose you drop the row from the second input
of the join.
What should be the overall result? Should the row from the first input be
emitted? Should it be discarded?
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16646032#comment-16646032
]
Vladimir Sitnikov commented on CALCITE-525:
---
{quote} As I said, the SQL standard calls for certain built-in functions to
throw exceptions{quote}
Should those functions be isolated/catched instead?
For instance, we could implement "mysql mode" where 0/0 returns 0, and '30
February' is an acceptable date.
Just joking.
[~julianhyde], do you know other SQL implementations that implement that
"tolerant to division by zero" kind of mode?
I know OracleDB has "insert into DST select ... LOG ERRORS INTO ...", however
it works for errors like "trigger error on table DST" and/or "wrong datatype of
DST's column".
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16645499#comment-16645499
]
Julian Hyde commented on CALCITE-525:
-
bq. What is the practical use of the feature you implement? (I mean "exception
handler" feature)
As I said, the SQL standard calls for certain built-in functions to throw
exceptions. And people want to run SQL in different modes; it they are running
a large ETL process and one of the function calls gets an error, they would
rather log exceptions and skip a row than abort the whole process.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=1661#comment-1661
]
Hongze Zhang commented on CALCITE-525:
--
Hi [~julianhyde] and [~vlsi], thank you for the suggestions.
In the newest commit, I have made all new classes @Experimental, and made Util
class to be compliance with spec, and some other changes.
bq. You seem to handle each and every EnumerableCalc, however a single query
might have multiple EnumerableCalc instances. Does it really make sense to
handle all of them?
IMO, It is a little tricky to choose a place in which we put the
catch-and-handle logic, there are some possible choices:
1. Decorate the root enumerable returned from root bindable;
2. Decorate the inner enumerables (Just like the PR does);
3. Directly change the code generated by RexToLixTranslator#translateCall
(Maybe use a lambda to wrap the implemented expression).
1 is OK but some exceptions could not be caught in this way (E.g. when use
group by query);
3 needs a lot of test work since it changes generated code wildly, and we could
not easily implement "drop a row" only by changing the RexToLix code.
So I choose 2 in the PR.
bq. What sense log action means? What is the result of SQL when exception is
logged and/or ignored?
ExceptionHandlerEnum.LOG indicates "log the error and discard it". And
ExceptionHandlerEnum.DISCARD means "discard it". If the names are confusing, I
can give them a longer name maybe (E.g. ExceptionHandlerEnum.LOG_AND_DISCARD).
bq. Some of the strategy can occur at code-generation time (and could therefore
apply to engines other than Enumerable).
In the newest commit, I have made changes to ExceptionHandler to make it handle
exceptions only, rather than handling enumerable directly, so that the handler
could be used in anywhere.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16643796#comment-16643796
]
Julian Hyde commented on CALCITE-525:
-
I saw [~vlsi]'s comments on the PR, but let's bring the discussion to this JIRA.
I do see why you want to add a public API - people need to customize how
exceptions are handled. One person (or engine) might want to abort a query on
divide-by-zero, another might want to convert it to null.
Some of the strategy can occur at code-generation time (and could therefore
apply to engines other than Enumerable) but you are correct that we need some
runtime support.
I like how you have created an ExceptionHandler class and added it to
DataContext, and how you have created some default implementations in an enum.
I would mark these APIs experimental using annotations, as [~vlsi] suggests.
Can you rename ExceptionHandlerUtil to ExceptionHandlers, per current
conventions for companion objects.
Thanks for the comprehensive tests.
I'll do a detailed review when we have achieved consensus on the specification.
> 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)
[jira] [Commented] (CALCITE-525) Exception-handling in built-in functions
[
https://issues.apache.org/jira/browse/CALCITE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
