[
https://issues.apache.org/jira/browse/CALCITE-5315?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17615206#comment-17615206
]
Viliam Durina edited comment on CALCITE-5315 at 10/10/22 4:40 PM:
------------------------------------------------------------------
There are more operators that can have data-related failures. All arithmetic
operators can overflow, JSON/XML function can fail on parsing errors if those
calls are pushed down etc.
I actually encountered this problem many years ago in Oracle where a query
failed due to an invalid number for a value that wasn't a part of the result. I
thought that's the nature or SQL - it's declarative, any actual execution order
is valid, including the order of WHERE and FROM clauses.
I concur with the comments of the Postgresql folks who argued that many
real-world queries will suffer a lot if we don't allow push-down of these
operators. My estimate is that practically all Calcite users will disable the
"strict" flag, so it would be a waste of time implementing it.
was (Author: vilo):
There are more operators that can have data-related failures. All arithmetic
operators can overflow, JSON/XML function can fail on parsing errors if those
calls are pushed down etc.
I actually encountered this problem many years ago in Oracle where a query
failed due to an invalid number for a value that wasn't a part of the result. I
thought that's the nature or SQL - it's declarative, any actual execution order
is valid, including the order of WHERE and FROM clauses. I concur with the
comments of the Postgresql folks who argued that many real-world queries will
suffer a lot if we don't allow push-down of these operators.
> Error when pushing filters with operations that may throw (CAST/DIVISION)
> below joins
> -------------------------------------------------------------------------------------
>
> Key: CALCITE-5315
> URL: https://issues.apache.org/jira/browse/CALCITE-5315
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.32.0
> Reporter: Stamatis Zampetakis
> Priority: Major
>
> +Steps to reproduce:+
> {code:sql}
> CREATE TABLE emp (empno INT, name VARCHAR, deptno INT);
> INSERT INTO emp VALUES (0, 'Alex', 0);
> INSERT INTO emp VALUES (10, 'Bob', 1);
> CREATE TABLE dept (deptno INT);
> INSERT INTO dept VALUES (1);
> SELECT e.name
> FROM emp e
> INNER JOIN dept d ON e.deptno = d.deptno
> WHERE (10 / e.empno) = 1
> {code}
> *Expected output:*
> Bob
> *Actual output:*
> ERROR: division by zero
> The error is caused when the filter condition in the WHERE clause is
> evaluated before the join. Filter push-down is a very common and powerful
> optimization but when there are operators in the WHERE clause that may throw
> (such as division, cast, etc) this optimization is unsafe and can lead to
> runtime errors.
> The SQL standard (Section 7.4 general rule 1) mandates that WHERE should be
> applied to the result of FROM so in the case above pushing filters below the
> join seems to violate the standard.
> +Citing the standard:+
> "If all optional clauses are omitted, then the result of the <table
> expression> is the same as the result of the
> <from clause>. Otherwise, each specified clause is applied to the result of
> the previously specified clause
> and the result of the <table expression> is the result of the application of
> the last specified clause."
> One of the optional clauses mentioned in the previous paragraph is the
> <where clause>. There seems to be a clearly defined order between the <from
> clause>, which includes inner joins, and the <where clause>.
> Note that this problem is *not* the same as the evaluation order of
> predicates in the WHERE clause, which is implementation specific. This is
> about evaluation order of WHERE clause and FROM clause that is not
> implementation specific.
> Original discussion:
> https://lists.apache.org/thread/cp7h28k1yfxv421q12y1wopbwgrzdzrx
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
