[
https://issues.apache.org/jira/browse/CALCITE-5315?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17614132#comment-17614132
]
Stamatis Zampetakis commented on CALCITE-5315:
----------------------------------------------
The same "bug" exists in various databases such as DERBY-6358,
[Postgres|https://www.postgresql.org/message-id/[email protected]],
and others but the optimization is so common that it is unlikely to be fixed.
> 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)
