Обсуждение: Surprisingly forgiving behavior when a case expression is terminated with "end case"
Surprisingly forgiving behavior when a case expression is terminated with "end case"
От
Bryn Llewellyn
Дата:
The account of the CASE expression here:
https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE
says that it's terminated with the keyword END (just as I've always understood)—i.e. not with the PL/pgSQL CASE statement's END CASE.
Moreover CASE is a reserved word—as a "create table case(…)" attempt shows. Yet CASE is tolerated (using PG 14.4) here:
select 1 as case;
In fact, any reserved word that I try (like IF, THEN, and so on) is accepted as an alias. This seems to me to be wrong. What do you (all) think?
https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE
says that it's terminated with the keyword END (just as I've always understood)—i.e. not with the PL/pgSQL CASE statement's END CASE.
Moreover CASE is a reserved word—as a "create table case(…)" attempt shows. Yet CASE is tolerated (using PG 14.4) here:
select 1 as case;
In fact, any reserved word that I try (like IF, THEN, and so on) is accepted as an alias. This seems to me to be wrong. What do you (all) think?
This outcome inspired this test:
create table t(k serial primary key, v text);
insert into t(v) values ('a'), (null);
select
k,
case
when v is null then '<NULL>'
else v
end case
from t order by k;
I suppose that this is showing nothing more than what I already did. Everything that I've shown so far behaves the same if PG 11.
create table t(k serial primary key, v text);
insert into t(v) values ('a'), (null);
select
k,
case
when v is null then '<NULL>'
else v
end case
from t order by k;
I suppose that this is showing nothing more than what I already did. Everything that I've shown so far behaves the same if PG 11.
So then I tried the "typo" in a PL/pgSQL subprogram:
create function f(arr in text[])
returns text
language plpgsql
as $body$
declare
a text;
r text := '';
begin
foreach a in array arr loop
a := case
when a is null then '<NULL>'
else a
end case;
r := r||a||', ';
end loop;
return r;
end;
$body$;
select f(array['a', null::text, 'b']);
The "create function" succeeds. And the "select f()" executes without error to produce the result that I expect. In PG 14.4.
returns text
language plpgsql
as $body$
declare
a text;
r text := '';
begin
foreach a in array arr loop
a := case
when a is null then '<NULL>'
else a
end case;
r := r||a||', ';
end loop;
return r;
end;
$body$;
select f(array['a', null::text, 'b']);
The "create function" succeeds. And the "select f()" executes without error to produce the result that I expect. In PG 14.4.
But in PG 11.9, the "create function" causes this error:
ERROR: syntax error at or near "case"
LINE 13: end case;
LINE 13: end case;
It seems, then, that at some version boundary between PG 11 and PG 14, forgiveness was introduced in this secnario, too.
Was this change to forgive what seems to be to be a straight syntax error deliberate? After all, you (all) thought it to be a syntax error in some scenarios in PG 11—but just not so in all scenarios.
Was it that the original sin of forgiveness in some scenarios could not be corrected because of the stronger requirement not to break existing code? And so this led to a "bug" fix to forgive that sin more uniformly? If so, then I suppose that you might say something in the doc. But there is still a wrinkle. This:
select
k,
case
when v is null then '<NULL>'
else v
end dog
from t order by k;
k,
case
when v is null then '<NULL>'
else v
end dog
from t order by k;
runs without error. But this (in PG 14.4)
select
k,
case
when v is null then '<NULL>'
else v
end case dog
from t order by k;
k,
case
when v is null then '<NULL>'
else v
end case dog
from t order by k;
still fails with a syntax error:
ERROR: syntax error at or near "dog"
LINE 6: end case dog
LINE 6: end case dog
So even in "current", the "end case" type isn't forgiven in all scenarios.
p.s. You can guess that I stumbled on this in the context of a fairly large demo app where just one of the subprograms had the "end case" typo that I showed above. Nothing prompted me to spot my mistake until I tested my code using YugabyteDB. I'm embarrassed to say that our current version still uses the PG 11 SQL processing code. But a soon-to-be-published new YB version will use PG 13. And "soon" after that, we hope to remain current with the current PG.
Bryn Llewellyn <bryn@yugabyte.com> writes:
> Moreover CASE is a reserved word—as a "create table case(…)" attempt shows. Yet CASE is tolerated (using PG 14.4)
here:
> select 1 as case;
> In fact, any reserved word that I try (like IF, THEN, and so on) is accepted as an alias. This seems to me to be
wrong.What do you (all) think?
I think we've spent a great deal of blood, sweat, and tears
making that so, or as nearly so as we could. We will in
fact take any keyword after "AS", and in recent versions many
non-reserved keywords will work that way without "AS".
(Mind you, I think the SQL spec made a serious design error
in allowing "AS" to be optional. But we have to live with that
as best we can.)
regards, tom lane
Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"
От
Adrian Klaver
Дата:
On 8/10/22 11:59, Bryn Llewellyn wrote: > The account of the CASE expression here: > > https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE > <https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE> > > says that it's terminated with the keyword END (just as I've always > understood)—i.e. not with the PL/pgSQL CASE statement's END CASE. > > Moreover CASE is a reserved word—as a "create table case(…)" attempt > shows. Yet CASE is tolerated (using PG 14.4) here: > > *select 1 as case; > * > In fact, any reserved word that I try (like IF, THEN, and so on) is > accepted as an alias. This seems to me to be wrong. What do you (all) think? But documented: https://www.postgresql.org/docs/current/sql-keywords-appendix.html " Even reserved key words are not completely reserved in PostgreSQL, but can be used as column labels (for example, SELECT 55 AS CHECK, even though CHECK is a reserved key word)." -- Adrian Klaver adrian.klaver@aklaver.com
Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"
От
Bryn Llewellyn
Дата:
I think we've spent a great deal of blood, sweat, and tears making that so, or as nearly so as we could. We will in fact take any keyword after "AS", and in recent versions many non-reserved keywords will work that way without "AS".
(Mind you, I think the SQL spec made a serious design error in allowing "AS" to be optional. But we have to live with that as best we can.)
Also, adrian.klaver@aklaver.com wrote:
Even reserved key words are not completely reserved in PostgreSQL, but can be used as column labels (for example, SELECT 55 AS CHECK, even though CHECK is a reserved key word):
Thank you both. I never would have guessed that a word with "reserved" status could be used as a column alias (with or without preceding it with AS). "not completely reserved in PostgreSQL" makes this sound like a PG special.
So I was caught out yet again. And I fear that I'll continue to be caught out with other things—and maybe this one too, at some later date, when I've forgotten the present exchanges…
Anyway, I believe that I have the answer to my question. And my new mental model allowed me to predict that, as presented, this would work:
create function f()
returns text
language plpgsql
as $body$
declare
a constant int := 3;
b constant int := 5;
c constant int := 7;
begin
return a + b case /* + c */;
end;
$body$;
select f();
returns text
language plpgsql
as $body$
declare
a constant int := 3;
b constant int := 5;
c constant int := 7;
begin
return a + b case /* + c */;
end;
$body$;
select f();
It does! It predicted, too, that when "+ c" is uncommented, "create function" would fail with a syntax error. And that prediction also held out.