Обсуждение: 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
> 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
This error would be clearer with something as simple as putting the ^ in the right place and extremely clear with the above "HINT".
--
--Dan