Re: Make subquery alias optional in FROM clause

On Thu, Feb 23, 2017 at 01:27:29PM +0000, Greg Stark wrote:
> On 22 February 2017 at 15:08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Indeed. When I wrote the comment you're referring to, quite a few years
> > ago now, I thought that popular demand might force us to allow omitted
> > aliases. But the demand never materialized. At this point it seems
> > clear to me that there isn't really good reason to exceed the spec here.
> > It just encourages people to write unportable SQL code.
>
>
> Oh my. This bothers me all the time. I always assumed the reason it
> was like this was because the grammar would be ambiguous without it
> and it would require extreme measures to hack the grammar to work. If
> it's this easy I would totally be for it.
>
> Offhand I think there are plenty of solutions for the problem of
> inventing names and I suspect any of them would work fine:
>
> 1) Don't assign a name -- I would guess this would require some
> adjustments in the rule deparsing (i.e. views).
>
> 2) Assign a name but add a flag indicating the name is autogenerated
> and shouldn't be used for resolving references and shouldn't be
> dumped. Then it shouldn't really matter if there's a conflict since
> the name is only used for things like error messages, not resolving
> references.
>
> 3) thumb through all the names in the query and pick one that doesn't conflict.
>
> For what it's worth while it wouldn't be a *bad* thing to avoid
> conflicts I think this is being held to an inconsistent standard here.
> It's not like there aren't similar situations elsewhere in the
> codebase where we just don't worry about this kind of thing:
>
> => SELECT "?column"? FROM (select 1+1 as "?column?", 1+1) AS x;
> ERROR: 42703: column "?column" does not exist
> LINE 2: SELECT "?column"? FROM (select 1+1 as "?column?", 1+1) AS x;
> ^
> HINT: Perhaps you meant to reference the column "x.?column?" or the
> column "x.?column?".

That's because you transposed the two characters after column in your
target list:
XX
SELECT "?column"? FROM (select 1+1 as "?column?", 1+1) AS x;
SELECT "?column?" FROM (select 1+1 as "?column?", 1+1) AS x;

This is what you get when you do the second, which I'm assuming is
what you meant to do:

ERROR: column reference "?column?" is ambiguous
LINE 1: SELECT "?column?" FROM (select 1+1 as "?column?", 1+1) AS x;

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate