BUG #14514: Bug in Subquery

On Wed, Jan 25, 2017 at 1:23 AM, <reva.d91@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14514
Logged by: Revathi Dharmalingam
Email address: reva.d91@gmail.com
PostgreSQL version: 9.3.10
Operating system: Windows 7/Unix
Description:

It is ignoring the invalid columns in subquery,Instead of throwing the
error
'Column does not exist'.

(eg):
select * from emp where (empname,empid,empno) in(selec emname,emid,emno
from
em_details)

Note:There is no emno in em_details table.

Note: just user, not developer.

​I tried to duplicate your error​ in PostgreSQL 9.5.5 on Fedora Linux 25,
64 bit. I got the expected "column does not exist". Can you try version 9.5
or even 9.6 to see if the bug is squashed in a later Windows version?

--
There’s no obfuscated Perl contest because it’s pointless.

—Jeff Polk

Maranatha! <><
John McKown

reva.d91@gmail.com writes:

It is ignoring the invalid columns in subquery,Instead of throwing the error
'Column does not exist'.

(eg):
select * from emp where (empname,empid,empno) in(selec emname,emid,emno from
em_details)

Note:There is no emno in em_details table.

Maybe not, but if there is one in emp, then this query is legal per
SQL spec --- emno is an outer reference.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Wed, Jan 25, 2017 at 12:23 AM, <reva.d91@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14514
Logged by: Revathi Dharmalingam
Email address: reva.d91@gmail.com
PostgreSQL version: 9.3.10
Operating system: Windows 7/Unix
Description:

It is ignoring the invalid columns in subquery,Instead of throwing the
error
'Column does not exist'.

(eg):
select * from emp where (empname,empid,empno) in(selec emname,emid,emno
from
em_details)

Note:There is no emno in em_details table.
​

​Working as designed. The reference to "emno" comes from the "emp" table -
this is what is called a correlated subquery.

David J.​

On Wed, Jan 25, 2017 at 3:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

reva.d91@gmail.com writes:

It is ignoring the invalid columns in subquery,Instead of throwing the

error

'Column does not exist'.

(eg):
select * from emp where (empname,empid,empno) in(selec emname,emid,emno

from

em_details)

Note:There is no emno in em_details table.

Maybe not, but if there is one in emp, then this query is legal per
SQL spec --- emno is an outer reference.

regards, tom lane

What Tom says above, the query is legal SQL if the column exists in either
table.

You should use aliases (or just prefix the columns with their table name
and dot):

select e.* from emp as e
where (e.empname, e.empid, e.empno) in
(select ed.emname, ed.emid, ed.emno
from em_details as ed)

This way the query will work as you expected, either give a result (if the
3 columns exist in em_details) or give you an error if not.