pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SELECT * FROM a WHERE id IN (sub query) .   When sub query is incorrect . return all rows of table a
2+ messages / 2 participants
[nested] [flat]

* SELECT * FROM a WHERE id IN (sub query) .   When sub query is incorrect . return all rows of table a
@ 2020-06-17 14:56  逗比请来的猴子 <xh2432@vip.qq.com>
  0 siblings, 1 reply; 2+ messages in thread

From: 逗比请来的猴子 @ 2020-06-17 14:56 UTC (permalink / raw)
  To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>

When I have 2 tables like this&nbsp;
create table test
(
id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int4,
shapeid&nbsp; &nbsp; &nbsp; &nbsp; int4
);


table testb
(
id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;int4
);





table test has 4 records and table has 1 recors.


sql&nbsp; " select shapeid from testb; "&nbsp; &nbsp; &nbsp;Failed.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;


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


sql like this delete all records of my talbe !

Attachments:

2FFAC80B@56932806.0A2FEA5E.png.jpg (image/jpeg, 17.6K) download view image
0006A22B@0BD9BF6D.0A2FEA5E.png.jpg (image/jpeg, 14.8K) download view image
pg_test1.png (application/octet-stream, 17.6K) download pg_test2.png (application/octet-stream, 8.8K) download pg_test3.png (application/octet-stream, 7.4K) download pg_test4.png (application/octet-stream, 14.8K) download ^ permalink raw reply [nested|flat] 2+ messages in thread

* Re: SELECT * FROM a WHERE id IN (sub query) . When sub query is incorrect . return all rows of table a
@ 2020-06-17 15:37  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 逗比请来的猴子 <xh2432@vip.qq.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2020-06-17 15:37 UTC (permalink / raw)
  To: 逗比请来的猴子 <xh2432@vip.qq.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>

"=?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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-06-17 15:37 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-17 14:56 SELECT * FROM a WHERE id IN (sub query) .   When sub query is incorrect . return all rows of table a 逗比请来的猴子 <xh2432@vip.qq.com>
2020-06-17 15:37 ` Re: SELECT * FROM a WHERE id IN (sub query) . When sub query is incorrect . return all rows of table a Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox