Re: BUG #17623: WHERE should be evaluated after FROM clause when operators may throw

On Wed, Sep 28, 2022 at 5:29 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17623
> Logged by: Stamatis Zampetakis
> Email address: zabetak(at)gmail(dot)com
> PostgreSQL version: 14.5
> Operating system: Debian 10.2.1-6
> 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
>
> Actual output:
> ERROR: division by zero
>
> Expected output:
> Bob
>
> The error is caused since 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.
>
>

> 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.

The failure to document such a deviation from the standard can be
considered a bug but not the deviation itself. That is intentional. In
terms of trade-offs the current behavior seems reasonable. You'd need a
real example motivating a desire to make a change that will likely add
complexity and cost to every query most of which work just fine with
relevant clauses pushed down to restrict the volume of data that needs to
be joined.

David J.