From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dave Bothwell <dbothwell@primepoint.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: CTE subquery referencing phantom records
Date: Tue, 28 Mar 2023 15:31:42 -0400
Message-ID: <3655676.1680031902@sss.pgh.pa.us> (raw)
In-Reply-To: <CAEX8mgvn-dgrxwbZ-rCdeTsrj_=kUZTa0smAK2XCipkULmZ8Fg@mail.gmail.com>
References: <CAEX8mgvn-dgrxwbZ-rCdeTsrj_=kUZTa0smAK2XCipkULmZ8Fg@mail.gmail.com>
Dave Bothwell <dbothwell@primepoint.com> writes:
> Common Table Expression issue in PostgreSQL 15
> The final WHERE statement in our CTE appears to be referencing more
> records, then the first subquery of our CTE is returning. It works when the
> first subquery is filtered by the primary key (We left a commented out
> example in the WHERE statement). It fails when the first subquery is
> filtered by a string. We have provided a complete example of the problem
> below (The following example works in PostgreSQL 11):
I don't think this is a Postgres bug: your query is making unwarranted
assumptions about the order in which different WHERE clauses will be
evaluated. You could return to the PG 11 behavior by marking the
CTE as "materialized":
with
phase_in_date as materialized ( ...
but it'd be better to make the data structure more normalized so that
you don't have hazards like applying date() to fields for which it
would fail on some rows.
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, dbothwell@primepoint.com, pgsql-bugs@lists.postgresql.org
Subject: Re: CTE subquery referencing phantom records
In-Reply-To: <3655676.1680031902@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