From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: zabetak@gmail.com
Subject: BUG #17623: WHERE should be evaluated after FROM clause when operators may throw
Date: Wed, 28 Sep 2022 12:22:50 +0000
Message-ID: <17623-2bef2f4ff7fe8051@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17623
Logged by: Stamatis Zampetakis
Email address: zabetak@gmail.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.
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>.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, zabetak@gmail.com
Subject: Re: BUG #17623: WHERE should be evaluated after FROM clause when operators may throw
In-Reply-To: <17623-2bef2f4ff7fe8051@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox