BUG #14919: Invalid column in sub select is still a valid select

On Mon, Nov 20, 2017 at 4:26 PM, <tanes@siamscan.net> wrote:

Not sure if this is a bug or feature but when I query with sub select while
sub select has invalid column the query is still valid and running. For
example:

create temp table a ( id integer, descr text);
create temp table b ( b_id integer, descr text);

# select * from a where id=(select id from b where descr='A');

id | descr
----+-------
(0 rows)

# select id from b where descr='A';

ERROR: column "id" does not exist
LINE 1: select id from b where descr='A';
^

There is no bug here. The subquery should look at the column b_id
which is defined for relation b, so the error is normal.
--
Michael

Really? The fact that 1st statement doen't end in exception throwing is ok?
(While the same sub select executes with exception throwing.)

select * from a where id=(select id from b where descr='A');

On Nov 20, 2017 20:09, "Michael Paquier" <michael.paquier@gmail.com> wrote:

On Mon, Nov 20, 2017 at 4:26 PM, <tanes@siamscan.net> wrote:

Not sure if this is a bug or feature but when I query with sub select

while

sub select has invalid column the query is still valid and running. For
example:

create temp table a ( id integer, descr text);
create temp table b ( b_id integer, descr text);

# select * from a where id=(select id from b where descr='A');

id | descr
----+-------
(0 rows)

# select id from b where descr='A';

ERROR: column "id" does not exist
LINE 1: select id from b where descr='A';
^

There is no bug here. The subquery should look at the column b_id
which is defined for relation b, so the error is normal.
--
Michael

On Mon, Nov 20, 2017 at 3:49 PM, Tanes Sriviroolchai <tanes@siamscan.net>
wrote:

Really? The fact that 1st statement doen't end in exception throwing is
ok? (While the same sub select executes with exception throwing.)

select * from a where id=(select id from b where descr='A');

Because "b" doesn't have a column called "id", this is the same as:

select * from a where id=(select a.id from b where descr='A');

and that's a valid, though slightly silly, query. In other words, you
accidentally wrote a correlated subquery. Some people consider it good
practice to qualify column references with the name of the table when
there's more than one column in scope. If you had written:

select * from a where id=(select b.id from b where b.descr='A');

you would have noticed that you made a mistake, due to the exception this
query raises.

.m

Ok. Got it.

On Nov 20, 2017 21:16, "Marko Tiikkaja" <marko@joh.to> wrote: