Re: CTE subquery referencing phantom records

Hi Tom

Thank you for your quick response. I have done some additional research and
I understand now that adding the materialized key word simply returns the
query to its default behavior prior to PostgreSQL 12.

Thank you for your help.
Dave

On Tue, Mar 28, 2023 at 3:31 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dave Bothwell <dbothwell(at)primepoint(dot)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
>

--

<https://www.primepoint.com/>

David Bothwell

Chairman of the Board, Chief technology Officer | Primepoint, LLC

Address: 2 Springside Road, Westampton, NJ 08060

Phone: 800-600-5257

<https://www.primepoint.com/>-
<https://www.facebook.com/Primepoint.Payroll.HR/>-
<https://www.instagram.com/primepointllc/>-
<https://www.linkedin.com/company/505608?trk=tyah&trkInfo=clickedVertical%3Acompany%2CclickedEntityId%3A505608%2Cidx%3A2-2-3%2CtarId%3A1447790586761%2Ctas%3Aprimepoint>
- <https://twitter.com/primepoint>- <https://vimeo.com/user55649759>

--
This communication, and any information or attachments contained within,
may contain privileged or confidential information that is intended for the
sole use of the recipient or recipients named above. If the reader of this
message is not an intended recipient, or authorized to receive such
messages for an intended recipient, you are hereby notified that any
review, use, dissemination, copying, or distribution of this communication,
or any of its contents is strictly prohibited. If you have received this
message in error, please notify us immediately of the error by return email
and permanently remove the original message, its contents, and any copies
from your system. Thank you.