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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.32.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Stamatis Zampetakis
• Created: 07/Oct/22 15:46
• Updated: 05/Nov/22 08:44

Description

Steps to reproduce:

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

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

Issue Links

• is related to: CALCITE-2450 RexSimplify: reorder predicates to a canonical form as a part of RexSimplify
• is related to: CALCITE-525 Exception-handling in built-in functions
• relates to: DERBY-6358 WHERE clause should be evaluated after the joins in the FROM clause
• relates to: CALCITE-4982 Do not push 'cast to not null' through Join in ProjectJoinTransposeRule