Surprisingly forgiving behavior when a case expression is terminated with "end case"

Поиск
Список
Период
Сортировка
От Bryn Llewellyn
Тема Surprisingly forgiving behavior when a case expression is terminated with "end case"
Дата
Msg-id 5C1A009F-1E4A-4C2B-9142-9B05F2A86225@yugabyte.com
обсуждение исходный текст
Ответы Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
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?

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.

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.

But in PG 11.9, the "create function" causes this error:

ERROR:  syntax error at or near "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;

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;

still fails with a syntax error:

ERROR:  syntax error at or near "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.

В списке pgsql-general по дате отправления:

Предыдущее
От: "Peter J. Holzer"
Дата:
Сообщение: Re: index row size 2720 exceeds btree version 4
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"