Query will execute when inner query have issue

On 23 Jan 2020, at 08:34, selva kumar <selva.logic@hotmail.com> wrote:

Greetings,

We tried query in a following manner.

SELECT * FROM A where A.id IN (SELECT B.id from B);

In the above query Table B does not have id. But this query return all A table records

Note : If we run inner query alone, It throws error like “Field is not found”

This is not reproducible for me, it fails as expected:

db=# select * from a where a.id in (select b.id from b);
ERROR: column b.id does not exist
LINE 1: select * from a where a.id in (select b.id from b);
^
HINT: Perhaps you meant to reference the column "a.id".

Which version of postgres are you using, and how are you executing the query?

cheers ./daniel

selva kumar <selva.logic@hotmail.com> writes:

We tried query in a following manner.
SELECT * FROM A where A.id IN (SELECT B.id from B);
In the above query Table B does not have id. But this query return all A table records

You sure that's *actually* what you wrote? The usual mistake is to
fail to qualify the inner query's column reference at all:

SELECT * FROM A where A.id IN (SELECT id from B);

If B has no "id" column, it's still a legal SQL query, interpreted as

SELECT * FROM A where A.id IN (SELECT A.id from B);

so as long as B has any rows, the IN-test succeeds for every non-null
value of A.id. People get burned by that all the time, but it's
acting as required by the SQL standard.

regards, tom lane

Greetings

I did the following excise.

CREATE TABLE product(
productid int PRIMARY KEY,
productname CHARACTER VARYING NOT NULL);

INSERT INTO product values (1,'Pen');
INSERT INTO product values (2,'Pencil');

CREATE TABLE sales(
salesdate date,
product int,
qyt int);

INSERT INTO sales VALUES ('2020-01-22',1,10);
INSERT INTO sales VALUES ('2020-01-22',2,20);

select productid from sales where salesdate='2020-01-22';
ERROR: column "productid" does not exist
LINE 1: select productid from sales where salesdate='2020-01-22'

But the following query will executed
select * from product where productid in (select productid from sales where salesdate='2020-01-22')

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986&gt; for Windows 10

________________________________
From: Daniel Gustafsson <daniel@yesql.se>
Sent: Thursday, January 23, 2020 3:25:53 PM
To: selva kumar <selva.logic@hotmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Query will execute when inner query have issue

On 23 Jan 2020, at 08:34, selva kumar <selva.logic@hotmail.com> wrote:

Greetings,

We tried query in a following manner.

SELECT * FROM A where A.id IN (SELECT B.id from B);

In the above query Table B does not have id. But this query return all A table records

Note : If we run inner query alone, It throws error like “Field is not found”

This is not reproducible for me, it fails as expected:

db=# select * from a where a.id in (select b.id from b);
ERROR: column b.id does not exist
LINE 1: select * from a where a.id in (select b.id from b);
^
HINT: Perhaps you meant to reference the column "a.id".

Which version of postgres are you using, and how are you executing the query?

cheers ./daniel

Greetings

I did the following excise.

CREATE TABLE product(
productid int PRIMARY KEY,
productname CHARACTER VARYING NOT NULL);

INSERT INTO product values (1,'Pen');
INSERT INTO product values (2,'Pencil');

CREATE TABLE sales(
salesdate date,
product int,
qyt int);

INSERT INTO sales VALUES ('2020-01-22',1,10);
INSERT INTO sales VALUES ('2020-01-22',2,20);

select productid from sales where salesdate='2020-01-22';
ERROR: column "productid" does not exist
LINE 1: select productid from sales where salesdate='2020-01-22'

But the following query will executed
select * from product where productid in (select productid from sales where salesdate='2020-01-22')

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986&gt; for Windows 10

________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, January 23, 2020 8:13:33 PM
To: selva kumar <selva.logic@hotmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Query will execute when inner query have issue

selva kumar <selva.logic@hotmail.com> writes:

We tried query in a following manner.
SELECT * FROM A where A.id IN (SELECT B.id from B);
In the above query Table B does not have id. But this query return all A table records

You sure that's *actually* what you wrote? The usual mistake is to
fail to qualify the inner query's column reference at all:

SELECT * FROM A where A.id IN (SELECT id from B);

If B has no "id" column, it's still a legal SQL query, interpreted as

SELECT * FROM A where A.id IN (SELECT A.id from B);

so as long as B has any rows, the IN-test succeeds for every non-null
value of A.id. People get burned by that all the time, but it's
acting as required by the SQL standard.

regards, tom lane

On Thu, Jan 23, 2020 at 9:19 AM selva kumar <selva.logic@hotmail.com> wrote:

But the following query will executed

select * from product where productid in (select productid from sales
where salesdate='2020-01-22')

Which isn't the same as what you first posted because of the absence of
table qualifications. That single difference, explained by Tom, completely
explains why the original post did in fact provoke and error and this query
does not.

The take-away is make sure to test your queries; and when dealing with
sub-queries its usually a good idea of add a table qualification to your
fields.

David J.