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 !
Attachments:
"=?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