Re: Order of operations in postgreSQL.

Thanks for your help.
This was a bad assumption on my part. I did not know that Postgres did not make that promise.

[Logo Description automatically generated]<http://kipuhealth.com/>
Ken McClaren
Database Administrator

864.313.5997
Easley, SC
Instagram<https://www.instagram.com/kipuhealth/> | LinkedIn<https://www.linkedin.com/company/kipuhealth> | Facebook<https://www.facebook.com/KipuSystems/> | Twitter<https://twitter.com/kipuhealth>
kipuhealth.com <http://kipuhealth.com/>

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Thursday, June 1, 2023 at 9:51 AM
To: Ken McClaren <ken(dot)mcclaren(at)kipuhealth(dot)com>
Cc: Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>, John Hall <john(dot)hall(at)kipuhealth(dot)com>
Subject: Re: Order of operations in postgreSQL.
[You don't often get email from tgl(at)sss(dot)pgh(dot)pa(dot)us(dot) Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

CAUTION: This email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

"Ken McClaren" <ken(dot)mcclaren(at)kipuhealth(dot)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