pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement
8+ messages / 4 participants
[nested] [flat]
* BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement
@ 2023-11-14 13:00 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: PG Bug reporting form @ 2023-11-14 13:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: paul.kulakov@systematica.ru
The following bug has been logged on the website:
Bug reference: 18195
Logged by: Pavel Kulakov
Email address: paul.kulakov@systematica.ru
PostgreSQL version: 15.5
Operating system: Debian GNU/Linux 11
Description:
1. The following code is successfully executed although it has incorrect
syntax: there must be comma (,) between _n and _s in 'into' section.
The output is "_n = 1, _s = <NULL>"
2. Documentation
(https://www.postgresql.org/docs/current/plpgsql-statements.html) in 43.5.3.
says "the command's result columns must exactly match the structure of the
target as to number and data types, or else a run-time error occurs". But we
see that number of columns can differ from number of variables.
I think either the documentation or the source code should be fixed.
do $sql$
declare _n int; _s text;
begin
select 1, 'string1', 'string2'
into _n _s;
raise notice '_n = %, _s = %', _n, _s;
end;
$sql$;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement
@ 2023-11-14 15:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 4 replies; 8+ messages in thread
From: Tom Lane @ 2023-11-14 15:31 UTC (permalink / raw)
To: paul.kulakov@systematica.ru; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 1. The following code is successfully executed although it has incorrect
> syntax: there must be comma (,) between _n and _s in 'into' section.
> select 1, 'string1', 'string2'
> into _n _s;
I believe this is being read the same as
select 1, 'string1', 'string2' _s into _n;
That is, the lack of a comma causes the INTO sub-clause to end,
and then _s is taken as an AS-less column label. As the manual
explains, for backwards-compatibility reasons we allow INTO to be
embedded anywhere in the command, even though that leads to
surprising-looking cases like this one.
As for the question of why you don't get an error for the wrong
number of INTO targets, again that's backwards compatibility.
There's a "strict_multi_assignment" check you can turn on to make
it complain about that [1].
regards, tom lane
[1] https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement
@ 2023-11-14 15:52 Pavel Stehule <pavel.stehule@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2023-11-14 15:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: paul.kulakov@systematica.ru; pgsql-bugs@lists.postgresql.org
Hi
út 14. 11. 2023 v 16:31 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > 1. The following code is successfully executed although it has incorrect
> > syntax: there must be comma (,) between _n and _s in 'into' section.
>
> > select 1, 'string1', 'string2'
> > into _n _s;
>
> I believe this is being read the same as
>
> select 1, 'string1', 'string2' _s into _n;
>
> That is, the lack of a comma causes the INTO sub-clause to end,
> and then _s is taken as an AS-less column label. As the manual
> explains, for backwards-compatibility reasons we allow INTO to be
> embedded anywhere in the command, even though that leads to
> surprising-looking cases like this one.
>
> As for the question of why you don't get an error for the wrong
> number of INTO targets, again that's backwards compatibility.
> There's a "strict_multi_assignment" check you can turn on to make
> it complain about that [1].
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
The extension plpgsql_check https://github.com/okbob/plpgsql_check can
raise warnings for these cases too
Regards
Pavel
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement
@ 2023-11-15 07:22 Pavel Kulakov <paul.kulakov@systematica.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 8+ messages in thread
From: Pavel Kulakov @ 2023-11-15 07:22 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Tom,
Thanks for your answer.
I set plpgsql.extra_errors = 'strict_multi_assignment'
Now, how to explain the following working?
do $sql$
declare
_n int; _s text;
begin
select 1 into _n _s;
raise notice '_n = %, _s = %', _n, _s;
end;
$sql$;
Regards,
Pavel
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, November 14, 2023 6:31 PM
To: paul.kulakov@systematica.ru
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO
statement
PG Bug reporting form <noreply@postgresql.org> writes:
> 1. The following code is successfully executed although it has
> incorrect
> syntax: there must be comma (,) between _n and _s in 'into' section.
> select 1, 'string1', 'string2'
> into _n _s;
I believe this is being read the same as
select 1, 'string1', 'string2' _s into _n;
That is, the lack of a comma causes the INTO sub-clause to end, and then _s
is taken as an AS-less column label. As the manual explains, for
backwards-compatibility reasons we allow INTO to be embedded anywhere in the
command, even though that leads to surprising-looking cases like this one.
As for the question of why you don't get an error for the wrong number of
INTO targets, again that's backwards compatibility.
There's a "strict_multi_assignment" check you can turn on to make it
complain about that [1].
regards, tom lane
[1]
https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQ
L-EXTRA-CHECKS
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement
@ 2023-11-15 07:33 Pavel Stehule <pavel.stehule@gmail.com>
parent: Pavel Kulakov <paul.kulakov@systematica.ru>
0 siblings, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2023-11-15 07:33 UTC (permalink / raw)
To: Pavel Kulakov <paul.kulakov@systematica.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
Hi
st 15. 11. 2023 v 8:23 odesílatel Pavel Kulakov <paul.kulakov@systematica.ru>
napsal:
> Hi Tom,
>
> Thanks for your answer.
> I set plpgsql.extra_errors = 'strict_multi_assignment'
>
> Now, how to explain the following working?
>
> do $sql$
> declare
> _n int; _s text;
> begin
> select 1 into _n _s;
> raise notice '_n = %, _s = %', _n, _s;
> end;
> $sql$;
>
looks so parser is not sensitive to garbage after variable name
(2023-11-15 08:27:11) postgres=# do $sql$
declare
_n int; _s text;
begin
select 1 into _n somegarbage;
raise notice '_n = %, _s = %', _n, _s;
end;
$sql$;
NOTICE: _n = 1, _s = <NULL>
DO
>
> Regards,
> Pavel
>
> -----Original Message-----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Sent: Tuesday, November 14, 2023 6:31 PM
> To: paul.kulakov@systematica.ru
> Cc: pgsql-bugs@lists.postgresql.org
> Subject: Re: BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO
> statement
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > 1. The following code is successfully executed although it has
> > incorrect
> > syntax: there must be comma (,) between _n and _s in 'into' section.
>
> > select 1, 'string1', 'string2'
> > into _n _s;
>
> I believe this is being read the same as
>
> select 1, 'string1', 'string2' _s into _n;
>
> That is, the lack of a comma causes the INTO sub-clause to end, and then _s
> is taken as an AS-less column label. As the manual explains, for
> backwards-compatibility reasons we allow INTO to be embedded anywhere in
> the
> command, even though that leads to surprising-looking cases like this one.
>
> As for the question of why you don't get an error for the wrong number of
> INTO targets, again that's backwards compatibility.
> There's a "strict_multi_assignment" check you can turn on to make it
> complain about that [1].
>
> regards, tom lane
>
> [1]
>
> https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQ
> L-EXTRA-CHECKS
> <https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS;
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement
@ 2023-11-15 07:38 Pavel Kulakov <paul.kulakov@systematica.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 8+ messages in thread
From: Pavel Kulakov @ 2023-11-15 07:38 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Pavel,
Now I see what Tom said: this 'garbage' becomes an 'AS'-alias of the column.
Invalid code
select 1 into _n somegarbage;
is treated as
select 1 somegarbage into _n;
which is valid.
Regards,
Pavel
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement
@ 2023-11-15 07:47 Pavel Kulakov <paul.kulakov@systematica.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 8+ messages in thread
From: Pavel Kulakov @ 2023-11-15 07:47 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
This also permits the following syntax in PL/pgSQL:
do $sql$
declare _s text;
begin
_s = '1' somegarbage;
end;
$sql$;
I understand why it works. But it looks really strange.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement
@ 2023-11-15 07:52 Pavel Stehule <pavel.stehule@gmail.com>
parent: Pavel Kulakov <paul.kulakov@systematica.ru>
0 siblings, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2023-11-15 07:52 UTC (permalink / raw)
To: Pavel Kulakov <paul.kulakov@systematica.ru>; +Cc: pgsql-bugs@lists.postgresql.org
st 15. 11. 2023 v 8:47 odesílatel Pavel Kulakov <paul.kulakov@systematica.ru>
napsal:
> This also permits the following syntax in PL/pgSQL:
>
> do $sql$
> declare _s text;
> begin
> _s = '1' somegarbage;
> end;
> $sql$;
>
> I understand why it works. But it looks really strange.
>
yes, it is strange, but PL/pgSQL is a translator from PL/pgSQL to SQL, and
'1 xxx' is the correct expression in SQL. Probably today, where PL/pgSQL
assign statements are supported in SQL parser the better design can be
implemented. But probably it should still be hard for SELECT INTO.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-11-15 07:52 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-14 13:00 BUG #18195: PL/pgSQL: invalid syntax allowed in SELECT INTO statement PG Bug reporting form <noreply@postgresql.org>
2023-11-14 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-14 15:52 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-11-15 07:22 ` Pavel Kulakov <paul.kulakov@systematica.ru>
2023-11-15 07:33 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-11-15 07:38 ` Pavel Kulakov <paul.kulakov@systematica.ru>
2023-11-15 07:47 ` Pavel Kulakov <paul.kulakov@systematica.ru>
2023-11-15 07:52 ` Pavel Stehule <pavel.stehule@gmail.com>
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