pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: 逗比请来的猴子 <xh2432@vip.qq.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: SELECT * FROM a WHERE id IN (sub query) . When sub query is incorrect . return all rows of table a
Date: Wed, 17 Jun 2020 11:37:49 -0400
Message-ID: <1446960.1592408269@sss.pgh.pa.us> (raw)
In-Reply-To: <tencent_C931F3C82E827FD2FC5E9E544124FF202E05@qq.com>
References: <tencent_C931F3C82E827FD2FC5E9E544124FF202E05@qq.com>

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





view thread (2+ messages)

Message-ID: <1446960.1592408269@sss.pgh.pa.us>
Permalink:  ../1446960.1592408269@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1446960.1592408269@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, xh2432@vip.qq.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: SELECT * FROM a WHERE id IN (sub query) . When sub query is incorrect . return all rows of table a
  In-Reply-To: <1446960.1592408269@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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