Обсуждение: Query will execute when inner query have issue

Поиск
Список
Период
Сортировка

Query will execute when inner query have issue

От
selva kumar
Дата:

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”   

 

Sent from Mail for Windows 10

 

Re: Query will execute when inner query have issue

От
Daniel Gustafsson
Дата:
> 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

Re: Query will execute when inner query have issue

От
Tom Lane
Дата:
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

RE: Query will execute when inner query have issue

От
selva kumar
Дата:

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 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

RE: Query will execute when inner query have issue

От
selva kumar
Дата:

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 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

Re: Query will execute when inner query have issue

От
"David G. Johnston"
Дата:
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.