Обсуждение: BUG #14919: Invalid column in sub select is still a valid select
The following bug has been logged on the website:
Bug reference: 14919
Logged by: Tanes Sriviroolchai
Email address: tanes@siamscan.net
PostgreSQL version: 9.3.18
Operating system: Ubuntu 14.04
Description:
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';
^
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.)
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:
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