pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / 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 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:2FFAC80B@56932806.0A2FEA5E.png.jpg (image/jpeg, 17.6K) download
view imagepg_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 thread0006A22B@0BD9BF6D.0A2FEA5E.png.jpg (image/jpeg, 14.8K) download
view image
* 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