Thread: Error from Sub-Select, Simple Example.

OK, here's the deal.  It seems that in this particular situation, if you
reference an UNQUALIFIED field name in a subselect, the parser thinks it
is just fine as long as that field name exists SOMEWHERE in the
referenced tables  Then, the error that gets generated gets lost in the
mail.

create table one (
    oneid    int,
    onevalue text not null);

create table two (
    twoid    int,
    twovalue text);

insert into one (oneid, onevalue)
select 1,
    case when two.twovalue is null then (select twovalue from one where
oneid = 1)
        else two.twovalue end
from one left outer join two on one.oneid = two.twoid;

This causes the not null constraint to fire, but if you run the
subselect by itself, you get

select twovalue from one where oneid = 1;
ERROR:  Attribute "twovalue" not found

Is this a known issue?  I know I should qualify field names where
confusion might occur, but where did my error go?

Ian Harding writes:

> create table one (
>     oneid    int,
>     onevalue text not null);
>
> create table two (
>     twoid    int,
>     twovalue text);
>
> insert into one (oneid, onevalue)
> select 1,
>     case when two.twovalue is null then (select twovalue from one where
> oneid = 1)
>         else two.twovalue end
> from one left outer join two on one.oneid = two.twoid;
>
> This causes the not null constraint to fire, but if you run the
> subselect by itself, you get
>
> select twovalue from one where oneid = 1;
> ERROR:  Attribute "twovalue" not found
>
> Is this a known issue?

It's certainly known, but it's not an issue. ;-)

> I know I should qualify field names where
> confusion might occur, but where did my error go?

"twovalue" gets resolved as a reference to the respective column in table
"two", which appears in the FROM clause of your query.  The name space
available to a subquery includes the outer query.

--
Peter Eisentraut   peter_e@gmx.net