From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ken McClaren <ken.mcclaren@kipuhealth.com>
Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: John Hall <john.hall@kipuhealth.com>
Subject: Re: Order of operations in postgreSQL.
Date: Thu, 01 Jun 2023 09:51:37 -0400
Message-ID: <3845.1685627497@sss.pgh.pa.us> (raw)
In-Reply-To: <SJ0PR04MB75994DC45BDB0159CA120278F9499@SJ0PR04MB7599.namprd04.prod.outlook.com>
References: <SJ0PR04MB7599D1DD3E866CF2D2F42012F9489@SJ0PR04MB7599.namprd04.prod.outlook.com>
<668989cd22e8457ca7a85d0e74c34bde@express-scripts.com>
<SJ0PR04MB75994DC45BDB0159CA120278F9499@SJ0PR04MB7599.namprd04.prod.outlook.com>
"Ken McClaren" <ken.mcclaren@kipuhealth.com> writes:
> Yes, the error is correct, but it should never have occurred.
> The join should have eliminated the error condition before it was evaluated by the where clause. That is the case when this type of statement is executed in SQL Server.
Postgres does not promise that JOIN conditions are evaluated before WHERE
conditions. I rather doubt that SQL Server does either, because it'd
cripple performance for a lot of real-world queries.
If you need something like that, you should restructure the query into two
levels with an optimization fence between them. One way is
SELECT ... FROM
(SELECT ... FROM t1 JOIN t2 ON join-condition OFFSET 0) ss
WHERE risky-where-condition
regards, tom lane
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: tgl@sss.pgh.pa.us, ken.mcclaren@kipuhealth.com, Matthew.Wetmore@express-scripts.com, pgsql-bugs@lists.postgresql.org, john.hall@kipuhealth.com
Subject: Re: Order of operations in postgreSQL.
In-Reply-To: <3845.1685627497@sss.pgh.pa.us>
* 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