From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | 逗比请来的猴子 <xh2432(at)vip(dot)qq(dot)com> |
Cc: | pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: SELECT * FROM a WHERE id IN (sub query) . When sub query is incorrect . return all rows of table a |
Date: | 2020-06-17 15:37:49 |
Message-ID: | 1446960.1592408269@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
"=?gb18030?B?trqxyMfrwLS1xLrv19M=?=" <xh2432(at)vip(dot)qq(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Thompson, Shon | 2020-06-17 18:24:53 | Invalid memory alloc querying field type bytea |
Previous Message | Bruce Momjian | 2020-06-17 15:10:50 | Re: BUG #16496: can't move to next line on Query Editor |