Обсуждение: SELECT * FROM a WHERE id IN (sub query) . When sub query is incorrect . return all rows of table a

Поиск
Список
Период
Сортировка

SELECT * FROM a WHERE id IN (sub query) . When sub query is incorrect . return all rows of table a

От
"逗比请来的猴子"
Дата:
When I have 2 tables like this 

create table test
(
id                int4,
shapeid        int4
);

table testb
(
id                 int4
);


table test has 4 records and table has 1 recors.

sql  " select shapeid from testb; "     Failed.                                                    

but  sql  " select * from test where shapeid in ( SELECT shapeid FROM testb ); "  return all records of table test                

sql like this delete all records of my talbe !

Вложения

Re: SELECT * FROM a WHERE id IN (sub query) . When sub query is incorrect . return all rows of table a

От
Tom Lane
Дата:
"=?gb18030?B?trqxyMfrwLS1xLrv19M=?=" <xh2432@vip.qq.com> writes:
> When I have 2 tables like this
> create table test
> (
> id                int4,
> shapeid        int4
> );

> table testb
> (
> id                 int4
> );

> sql  " select shapeid from testb; "     Failed.
> but  sql  " select * from test where shapeid in ( SELECT shapeid FROM testb ); "  return all records of table test
            

This is not a bug, it is behavior required by the SQL standard.
Since shapeid doesn't exist in testb, shapeid in the sub-SELECT is
just an outer reference to the column in the outer table.

> sql like this delete all records of my talbe !

Yeah, it's a common trap for SQL newbies :-(.  The standard
advice is to always qualify column names in sub-selects, to
be sure of where they are coming from.  If you'd written
... where shapeid in ( SELECT testb.shapeid FROM testb ) ...
then you'd have gotten an error message.

            regards, tom lane