Re: Order of operations in postgreSQL.

On Thu, Jun 1, 2023 at 11:40 PM Ken McClaren <ken(dot)mcclaren(at)kipuhealth(dot)com>
wrote:

> Thanks for your help.
>
> This was a bad assumption on my part. I did not know that Postgres did not
> make that promise.
>
>
>
>
>
> [image: 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
>