[jira] [Comment Edited] (CALCITE-5315) Error when pushing filters with operations that may throw (CAST/DIVISION) below joins

["Viliam Durina (Jira)" <ji...@apache.org>]

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