Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vdAQj-00EmDO-01 for pgsql-general@arkaria.postgresql.org; Tue, 06 Jan 2026 17:01:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vdAQh-009OMz-3D for pgsql-general@arkaria.postgresql.org; Tue, 06 Jan 2026 17:01:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vdAQh-009OMq-1x for pgsql-general@lists.postgresql.org; Tue, 06 Jan 2026 17:01:12 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vdAQf-004viy-2O for pgsql-general@postgresql.org; Tue, 06 Jan 2026 17:01:12 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-65e9430489eso429967eaf.0 for ; Tue, 06 Jan 2026 09:01:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1767718867; x=1768323667; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=6x3uX3C6pBZU0axEND2Nk3cm6TK+ZXrsgqAbCWAxVI4=; b=chDeU03DQJQoujCXrkVXSzap0K3CjrrOUwmX8DO/nFarClaTihsc++yTMXpgPH3rkC BQeAgx+WVNjNjvm/GJ1e1g3ue5qPit6R0SGJIVFNDGjwa6oHgpmCoaITplHAbjkfp7JW e9pKLGSgIJXhkJeCkj+oidkw14bPJoE2b1Y0nyS9XDm/qe8iSFoDaawTRhIIB4d7vbcL RTW90+GAxcARRLB/AUfUjUgabRnqBQcsu9lcrch1BYebyltnqxXaBWsU5iI9EcW0xmxc Q8PiM2Udjy+s522Dwmevs8WC6lvo5LwPpeksUjuXJ9Ialmofh/ZgsHbd15SYSZkqCzI0 fSxg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1767718867; x=1768323667; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=6x3uX3C6pBZU0axEND2Nk3cm6TK+ZXrsgqAbCWAxVI4=; b=LnZrcLXtasZWorErW3Z4hBr2/SmNLaVHZEz9q+yUyi0r5mqf1x0ZNGvlQ2nWLom9Dc LIO1HYX/Ddt69ZnODeGUZ4Y4DsCpT3qqwYdo5Un9M6fV4qAavWebFJAudwWF6YGxOqIu pq4nnO6VXZpmT0FYnuNbwgRVxgFIq2co4t1uZh4jFPOUw3LsQ1ykgkkXuokA1aGYG4LH TPi9HAG6sDg/yRv5+Gdz3IjfqotHQLk+hkB/tKZ6KMWtotkw5beoFhnY9avryDRYkP9J AUbD1u2RYDe0s/+ERAfZVA8U4dlAEN118uiAcS3Yw2Pmwgh1dqkIKEFbX+y330jSXMvc hU0w== X-Gm-Message-State: AOJu0YxsdfSJNliFwMP+PH4LkAlllBVorFfyRJXzp2tx9XwoHrXe3Y9S QV9o2KnMsX6mZfcY12007ZbnjpX7ez0jQgFyj4z+pPidv+DQlOWZ3UEwVmYqewXzNRE3nVJ/qUO r6d0O2iPFRH141d4nV7K4EngPZ0zT11o= X-Gm-Gg: AY/fxX5N1Ji9vZnVh9g12R9piUZxfrSUBx33uE4h96hXsHVOTPAeRw5RFKZFGyvu5Xz 97RReN9Em457QjaYiRXocgevXwbev3cgINkf2+c6xU7/N9FluRoNBRcN9GtbDiF3yk+Iv4Y0Dwh eelnLD2KqaiPFLta/tSovLESs1qlqRtoBTE38/IKqQ/nzo9F1vD+uSdCRMLW2+iUaaxWWcwqypN 39oYSCH8rudGKE+Hr3EUtLGTgCCjpZPZQ/X9Ktxxwj8q9FT2pn+EzYgxj4NvZkgUZY2bM/f X-Google-Smtp-Source: AGHT+IHfQemlZJTALQGeSYFDe/31LcTtzmbaJOOjFPq4YvFoz/AlbmNUKO3u491FUibz/i7iUFv6IpLl7HYSrosmL5g= X-Received: by 2002:a05:6820:8389:b0:659:9a49:9090 with SMTP id 006d021491bc7-65f479a2578mr1938331eaf.15.1767718865828; Tue, 06 Jan 2026 09:01:05 -0800 (PST) MIME-Version: 1.0 References: <7900964C-F99E-481E-BEE5-4338774CEB9F@gmail.com> <11E075EF-20F5-42CD-A014-16172FF05CCF@gmail.com> In-Reply-To: <11E075EF-20F5-42CD-A014-16172FF05CCF@gmail.com> From: "David G. Johnston" Date: Tue, 6 Jan 2026 10:00:29 -0700 X-Gm-Features: AQt7F2rWiFSk7UovoL1uUoBxkL1XcpqDx8H3oASiS0dA7z2ehgJE92Zn643XBNk Message-ID: Subject: Re: pg18 bug? SELECT query doesn't work To: Eric Ridge Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000000f80090647bb1fb9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000f80090647bb1fb9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Jan 6, 2026 at 9:16=E2=80=AFAM Eric Ridge wrote: > On Jan 6, 2026, at 11:04=E2=80=AFAM, David G. Johnston > wrote: > > > It was an intentional change. You now should be putting set-producing > functions into the FROM clause of a query or subquery. A lateral join is > often required. > > > I'm willing to accept that, but I can't find this called out in the > release notes between 15 and 18.1. I could have overlooked it, of course= . > > It is very surprising to me that Postgres would intentionally break > previously-working SELECT statements and that the CTE version is > inconsistent between "AS MATERIALIZED". The WITH MATERIALIZED docs don't > mention anything about certain query shapes being incompatible. > > While I haven't dug into the actual specifics of this report in detail, the change in question happened back in v10. https://www.postgresql.org/docs/10/release-10.html The failure to emit an error when it probably should have is likely a bug in older versions since fixed. Or, it may be an actual bug. But we did tighten things up here and encourage/require a non-problematic query form (place set-returning constructs in the from clause) in some situations now that we did not before. So I'm willing to presume the error being reported here is valid. That the behavior depends on the chosen plan and plans differ when you do and do not materialize a CTE is likewise not surprising. Though as a practical matter it would be nice if the test was more resilient in face of different syntactic forms; so bug or not, maybe something could be done to make the failure more consistent. David J. --0000000000000f80090647bb1fb9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Jan 6, 2026 at 9:16=E2=80=AFAM Eric Ridge <eebbrr@gmail.com> wrote:

While I= haven't dug into the actual specifics of this report in detail, the ch= ange in question happened back in v10.

https://www.postgresql.org/= docs/10/release-10.html

The failure to emit an err= or when it probably should have is likely a bug in older versions since fix= ed.

Or, it may be an actual bug.=C2=A0 But we did tigh= ten things up here and encourage/require a non-problematic query form (plac= e set-returning constructs in the from=C2=A0clause) in some situations now = that we did not before.=C2=A0 So I'm willing to presume the error being= reported here is valid.

That the behavior depends on = the chosen plan and plans differ when you do and do not materialize a CTE i= s likewise not surprising.=C2=A0 Though as a practical matter it would be n= ice if the test was more resilient in face of different syntactic forms; so= bug=C2=A0or not, maybe something could be done to make the failure more co= nsistent.

David J.

--0000000000000f80090647bb1fb9--