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
In-reply-to: 
 <CAEX8mgvn-dgrxwbZ-rCdeTsrj_=kUZTa0smAK2XCipkULmZ8Fg@mail.gmail.com>
References: 
 <CAEX8mgvn-dgrxwbZ-rCdeTsrj_=kUZTa0smAK2XCipkULmZ8Fg@mail.gmail.com>
Comments: In-reply-to Dave Bothwell <dbothwell@primepoint.com>
	message dated "Tue, 28 Mar 2023 13:14:10 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3655675.1680031902.1@sss.pgh.pa.us>
Date: Tue, 28 Mar 2023 15:31:42 -0400
Message-ID: <3655676.1680031902@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3655676.1680031902%40sss.pgh.pa.us>
Precedence: bulk

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