Обсуждение: CTE containing ambiguous columns

Suppose you do this:

create table animals (id serial primary key, name varchar not null);

Then you can do this:

with beings as (select * from animals) select * from beings where id = 1;

But not this:

with beings as (select * from animals a1, animals a2) select * from
beings where id = 1;

Because:

ERROR:  column reference "id" is ambiguous at character 82
STATEMENT:  with beings as (select * from animals a1, animals a2)
select * from beings where id = 1;
ERROR:  column reference "id" is ambiguous
LINE 1: ...m animals a1, animals a2) select * from beings where id = 1;
             ^
 
My email program will probably mangle this, so the error cursor here
is point to "id = 1", at the end, and saying that's ambiguous.  Which
is sorta kinda true, but the usual remedy of qualifying it with a
relation name (here, beings.id) fails.  And you can't quantify it with
a1.id or a2.id either, they're out of scope.  In some sense, the real
problem is with "select *", because that is what is expanding into a
non-unique list of column names.  But you don't actually trigger an
error unless you try to reference one; the same query works fine
without the where clause.

I'm not sure if there's anything useful we can do about this, but it
definitely threw me for a loop.

...Robert

Robert Haas <robertmhaas@gmail.com> writes:
> Suppose you do this:
> create table animals (id serial primary key, name varchar not null);

> Then you can do this:

> with beings as (select * from animals) select * from beings where id = 1;

> But not this:

> with beings as (select * from animals a1, animals a2) select * from
> beings where id = 1;

No different from

regression=# select * from (animals a1 cross join animals a2) x where id = 1;
ERROR:  column reference "id" is ambiguous
LINE 1: ...ct * from (animals a1 cross join animals a2) x where id = 1;
             ^
 

There's no way to access the a1/a2 aliases here, either; and that rule
goes back to SQL92 or maybe further.
        regards, tom lane

Robert Haas wrote:
> create table animals (id serial primary key, name varchar not null);
>
>
>   
...
> with beings as (select * from animals a1, animals a2) select * from
> beings where id = 1;
>
>
>   

"Doctor, it hurts when I do this."
"So stop doing that."

Can't you disambiguate it using a column list on beings?

cheers

andrew

On Fri, Nov 13, 2009 at 8:32 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
>
> Robert Haas wrote:
>>
>> create table animals (id serial primary key, name varchar not null);
>>
>>
>>
>
> ...
>>
>> with beings as (select * from animals a1, animals a2) select * from
>> beings where id = 1;
>>
>>
>>
>
> "Doctor, it hurts when I do this."
> "So stop doing that."
>
> Can't you disambiguate it using a column list on beings?

Sure, after I figured out what the real problem was.  Maybe I'm a
dope, but when I get an error cursor pointed at an ambiguous column
reference, my thought is "oh, I need to qualify that reference" - not
"oh, some completely unrelated part of the query has an *-expansion
that contains duplicate columns".  Something like:

HINT: <alias> contains multiple columns named <colname>

...would help a lot.  I don't feel strongly about it, I just thought
it was confusing.

...Robert