Re: execute incorect query and get data

PostgreSQL : 8.2
Operating system: fedora RC4

--------

create table a (a1 integer);
create table b (b1 integer);

insert into a (a1) values (1);
insert into a (a1) values (2);
select a1 from b;

ERROR: column "a1" does not exist at character 8

but :
select a1 from a where a1 not in (select a1 from b);

a1
----
1
2
(2 rows)

when b is not empty :

insert into b (b1) values (1);

select a1 from b;
ERROR: column "a1" does not exist at character 8

but :
select a1 from a where a1 not in (select a1 from b);
a1
----
(0 rows)

select a1 from a where a1 in (select a1 from b);
a1
----
1
2
(2 rows)

=?ISO-8859-2?Q?Marek_Wr=F3bel?= <mwrobel(at)icentrum(dot)pl> writes:
> create table a (a1 integer);
> create table b (b1 integer);
> insert into a (a1) values (1);
> insert into a (a1) values (2);
> select a1 from b;
> ERROR: column "a1" does not exist at character 8
> but :
> select a1 from a where a1 not in (select a1 from b);

This is not a bug, it's an outer reference. Read any SQL book ...

regards, tom lane

Him,

Tom Lane napisał(a):
> =?ISO-8859-2?Q?Marek_Wr=F3bel?= <mwrobel(at)icentrum(dot)pl> writes:
>> create table a (a1 integer);
>> create table b (b1 integer);
>> insert into a (a1) values (1);
>> insert into a (a1) values (2);
>> select a1 from b;
>> ERROR: column "a1" does not exist at character 8
>> but :
>> select a1 from a where a1 not in (select a1 from b);
>
> This is not a bug, it's an outer reference. Read any SQL book ...

of course... so sorry... I was such startled off result off this query,
that I can't see that...