From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: =?UTF-8?Q?=E7=8E=8B=E3=80=80=E5=8D=9A?= <bo.wang@infortech.co.jp>,
        "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>,
        =?UTF-8?Q?=E6=9D=8E=E3=80=80=E6=B5=A9?= <hao.li@infortech.co.jp>
Subject: Re: Request for official clarification on SQL parameter parsing changes in PostgreSQL 15 and 16
In-reply-to: <237d58a65d887376ded17a67e7b99af5936fa9a3.camel@cybertec.at>
References: <TYCP286MB3620D4D19E3793CFA106CCBFB6BC2@TYCP286MB3620.JPNP286.PROD.OUTLOOK.COM> <237d58a65d887376ded17a67e7b99af5936fa9a3.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Thu, 17 Apr 2025 15:41:19 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2194845.1744899352.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 17 Apr 2025 10:15:52 -0400
Message-ID: <2194846.1744899352@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2194846.1744899352%40sss.pgh.pa.us>
Precedence: bulk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Thu, 2025-04-17 at 05:17 +0000, =E7=8E=8B=E3=80=80=E5=8D=9A wrote:
>> 1. In PostgreSQL 15 and later:
>> =C2=A0=C2=A0 The following SQL causes a syntax error unless a space is =
added after the `?`:
>> =C2=A0=C2=A0=C2=A0=C2=A0 SELECT * FROM table WHERE a =3D ?AND b =3D 123=
;
>> =C2=A0=C2=A0 =E2=86=92 Adding a space (`? AND`) resolves the issue.

> I'd say it is this change:
> https://postgr.es/c/2549f0661bd28571d7200d6f82f752a7ee5d47e1

Yeah.  This looks like "?" ought to be parsable as a separate token
... but as Dominique noted, it's not actually legal syntax in any
version of Postgres.  Something in your client stack must be
translating "?" to "$1", "$2", etc, and so the new prohibition
against junk trailing a number applies.

You could fix this without application-level changes if you fixed
whatever is making that substitution to add spaces around the
parameter symbol.  It's really a bug that it didn't do so already,
since closely-adjacent cases like digits immediately after the
"?" would already have caused failures.

			regards, tom lane