pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedOrder of operations in postgreSQL.
6+ messages / 4 participants
[nested] [flat]
* Order of operations in postgreSQL.
@ 2023-05-31 17:49 Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-05-31 20:41 ` RE: Order of operations in postgreSQL. Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ken McClaren @ 2023-05-31 17:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: John Hall <john.hall@kipuhealth.com>
https://stackoverflow.com/questions/76339408/gracefully-handle-datatime-in-postgresql/76345292?nored...
Just a few notes on this issue:
PSQL:
Appears to be applying the filter to all the columns in the table after the join.
TSQL:
Tried to duplicate this behavior and got these results.
select t1.id as t1id,
cast(cast(t1.test_value as varchar(max)) as datetime) as tms
from table1 t1
join table2 t2
on t1.id = t2.id
where cast(cast(t1.test_value as varchar(max)) as datetime) > '2023-05-27'
These statements work as expected in SQL Server.
Let me know if you have any questions.
[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/;
Attachments:
image001.png (image/png, 8.8K) download
view image
* RE: Order of operations in postgreSQL.
2023-05-31 17:49 Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
@ 2023-05-31 20:41 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-01 00:21 ` Re: Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
0 siblings, 1 reply; 6+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-05-31 20:41 UTC (permalink / raw)
To: Ken McClaren <ken.mcclaren@kipuhealth.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: John Hall <john.hall@kipuhealth.com>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Order of operations in postgreSQL.
2023-05-31 17:49 Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-05-31 20:41 ` RE: Order of operations in postgreSQL. Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-06-01 00:21 ` Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-06-01 13:51 ` Re: Order of operations in postgreSQL. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Ken McClaren @ 2023-06-01 00:21 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: John Hall <john.hall@kipuhealth.com>
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.
[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: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Date: Wednesday, May 31, 2023 at 4:42 PM
To: Ken McClaren <ken.mcclaren@kipuhealth.com>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: John Hall <john.hall@kipuhealth.com>
Subject: RE: Order of operations in postgreSQL.
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Order of operations in postgreSQL.
2023-05-31 17:49 Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-05-31 20:41 ` RE: Order of operations in postgreSQL. Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-01 00:21 ` Re: Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
@ 2023-06-01 13:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-01 14:40 ` Re: Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2023-06-01 13:51 UTC (permalink / raw)
To: Ken McClaren <ken.mcclaren@kipuhealth.com>; +Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; John Hall <john.hall@kipuhealth.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Order of operations in postgreSQL.
2023-05-31 17:49 Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-05-31 20:41 ` RE: Order of operations in postgreSQL. Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-01 00:21 ` Re: Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-06-01 13:51 ` Re: Order of operations in postgreSQL. Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-06-01 14:40 ` Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-06-01 14:46 ` Re: Order of operations in postgreSQL. Josh Innis <joshinnis@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ken McClaren @ 2023-06-01 14:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; John Hall <john.hall@kipuhealth.com>
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@sss.pgh.pa.us>
Date: Thursday, June 1, 2023 at 9:51 AM
To: Ken McClaren <ken.mcclaren@kipuhealth.com>
Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>, John Hall <john.hall@kipuhealth.com>
Subject: Re: Order of operations in postgreSQL.
[You don't often get email from tgl@sss.pgh.pa.us. 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.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
Attachments:
image001.png (image/png, 8.8K) download
view image
* Re: Order of operations in postgreSQL.
2023-05-31 17:49 Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-05-31 20:41 ` RE: Order of operations in postgreSQL. Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-01 00:21 ` Re: Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-06-01 13:51 ` Re: Order of operations in postgreSQL. Tom Lane <tgl@sss.pgh.pa.us>
2023-06-01 14:40 ` Re: Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
@ 2023-06-01 14:46 ` Josh Innis <joshinnis@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Innis @ 2023-06-01 14:46 UTC (permalink / raw)
To: Ken McClaren <ken.mcclaren@kipuhealth.com>; +Cc: John Hall <john.hall@kipuhealth.com>; Tom Lane <tgl@sss.pgh.pa.us>; Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Thu, Jun 1, 2023 at 11:40 PM Ken McClaren <ken.mcclaren@kipuhealth.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@sss.pgh.pa.us>
> *Date: *Thursday, June 1, 2023 at 9:51 AM
> *To: *Ken McClaren <ken.mcclaren@kipuhealth.com>
> *Cc: *Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>,
> pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>, John
> Hall <john.hall@kipuhealth.com>
> *Subject: *Re: Order of operations in postgreSQL.
>
> [You don't often get email from tgl@sss.pgh.pa.us. 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.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
>
Attachments:
image001.png (image/png, 8.8K) download
view image
end of thread, other threads:[~2023-06-01 14:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-31 17:49 Order of operations in postgreSQL. Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-05-31 20:41 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-01 00:21 ` Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-06-01 13:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-01 14:40 ` Ken McClaren <ken.mcclaren@kipuhealth.com>
2023-06-01 14:46 ` Josh Innis <joshinnis@gmail.com>
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