Re: Schema variables - new implementation for Postgres 15

On Tue, Aug 23, 2022 at 11:27:45AM +0200, Pavel Stehule wrote:
> út 23. 8. 2022 v 7:56 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)com> napsal:
>
> >
> > I've been thinking a bit more about the shadowing, and one scenario we
> > didn't
> > discuss is something like this naive example:
> >
> > CREATE TABLE tt(a text, b text);
> >
> > CREATE TYPE abc AS (a text, b text, c text);
> > CREATE VARIABLE tt AS abc;
> >
> > INSERT INTO tt SELECT 'a', 'b';
> > LET tt = ('x', 'y', 'z');
> >
> > SELECT tt.a, tt.b, tt.c FROM tt;
> >
> > Which, with the default configuration, currently returns
> >
> > a | b | c
> > ---+---+---
> > a | b | z
> > (1 row)
> >
> > I feel a bit uncomfortable that the system allows mixing variable
> > attributes
> > and relation columns for the same relation name. This is even worse here
> > as
> > part of the variable attributes are shadowed.
> >
> > It feels like a good way to write valid queries that clearly won't do what
> > you
> > think they do, a bit like the correlated sub-query trap, so maybe we should
> > have a way to prevent it.
> >
> > What do you think?
> >
>
> I thought about it before. I think valid RTE (but with the missing column)
> can shadow the variable too.
>
> With this change your query fails:
>
> (2022-08-23 11:05:55) postgres=# SELECT tt.a, tt.b, tt.c FROM tt;
> ERROR: column tt.c does not exist
> LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;
> ^
> (2022-08-23 11:06:03) postgres=# set session_variables_ambiguity_warning to
> on;
> SET
> (2022-08-23 11:06:19) postgres=# SELECT tt.a, tt.b, tt.c FROM tt;
> WARNING: session variable "tt.a" is shadowed
> LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;
> ^
> DETAIL: Session variables can be shadowed by columns, routine's variables
> and routine's arguments with the same name.
> WARNING: session variable "tt.b" is shadowed
> LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;
> ^
> DETAIL: Session variables can be shadowed by columns, routine's variables
> and routine's arguments with the same name.
> WARNING: session variable "public.tt" is shadowed
> LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;
> ^
> DETAIL: Session variables can be shadowed by tables or table's aliases
> with the same name.
> ERROR: column tt.c does not exist
> LINE 1: SELECT tt.a, tt.b, tt.c FROM tt;

Great, thanks a lot!

Could you add some regression tests for that scenario in the next version,
since this is handled by some new code? It will also probably be useful to
remind any possible committer about that choice.