Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iufCf-0003wr-3i for pgsql-bugs@arkaria.postgresql.org; Thu, 23 Jan 2020 16:19:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iufCd-0005Ru-L0 for pgsql-bugs@arkaria.postgresql.org; Thu, 23 Jan 2020 16:19:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iuemL-0006ER-3L for pgsql-bugs@lists.postgresql.org; Thu, 23 Jan 2020 15:52:21 +0000 Received: from mail-oln040092255107.outbound.protection.outlook.com ([40.92.255.107] helo=APC01-HK2-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iuemG-000429-Pp for pgsql-bugs@lists.postgresql.org; Thu, 23 Jan 2020 15:52:19 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=NszuY2p9HzGp6VKpYUQn2GbxQS/zi2vTO5+xXmcGTGuplrIw1ZQCDr6uTtuwGGRgDP9+y9oliR+pYv/dgMPQLE8AzwQdbuIYSZor0Vmwd6v6Df9cBg2lSZDt1R1tgbpjTX9S5cL93jNGGMgGOh0px91j9zS0R0AhyZZZ4OVHwRpuqMFdUGVi7WODbmEDIO0+lIZNpiACfgciIBwpNSewOYrrpCe8iOK9812a+TbDfWoXhwaFnLCkOetOsXJoaYT82mHkLs5S+PhdWVwjhWAN9p7Q6b5P+hbYCiB0OY62blVWpbgoPysp/zXcL2w3gSM9WLXk2FsPnxk8aA/rZhEXYg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=3p9VbaWBmFVCM8/mQ42Gva/YGgCziDCaJ0t9p6KSElU=; b=mwNdX36U9i0fVTpDoMVkY1UC9PkQsNLiJF0D4NsKwG86RSWIz/gf+NXRrqeercnqBQJObL6d2BJLJViM9grhFeZOhMFcCyhe60jtj4k0IQPlxPE11tk0YHUlVRH49/WsSzd7DDx1FGvtB4lCfO7GXNjhKtgqq3mSaUrPXj7xqP4baJPo0KGO8ZugCXaJPa1cgTb5U5rAdRaPNZlJvO2Lks3/SEj7EScD29TJk1+U6sdK3pFkp6ovIKXf/RW2JeA0+RboPGwMKuigQ1bFUuzbiJWwLElS3tq8xiSEcJXMVvzC3ojMOog4IerGam7SbEum9xY7qgo06R4Tkm/osFKKiQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=3p9VbaWBmFVCM8/mQ42Gva/YGgCziDCaJ0t9p6KSElU=; b=DvYriZGYqd+uaHOQU8gvXJckADmXZezJkhsmmMniRoRg8oIxqbnh7hqarRvqNHQWbAPQA2hAHVJiMYzxHROKq28uYawb+iJmD0Ykt9gLZWrsTo5Ts9hGmwU4NSP9gb2F/HEcy6iKM6DtcmpHsBp580/4KsRjuc/xNq4gcJaSbv7wgzdQzUSX4XEXExTUcoiTlIss8GuEJrvMQrq9VrwfQJBvuTwOEsseFFkqBKc1QH0F6zef53bpdsXwr25TKWcxGVmYz5afTWeeRpALPvnwvToGE9Zmyk9zZrZEVSH0Lv5yFF5jltsGrctY1AZG3zL0JSE2FMqaMmDF5KLm7PXFOw== Received: from PU1APC01FT056.eop-APC01.prod.protection.outlook.com (10.152.252.51) by PU1APC01HT121.eop-APC01.prod.protection.outlook.com (10.152.253.60) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2665.18; Thu, 23 Jan 2020 15:52:12 +0000 Received: from MAXPR01MB2157.INDPRD01.PROD.OUTLOOK.COM (10.152.252.53) by PU1APC01FT056.mail.protection.outlook.com (10.152.253.98) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2665.18 via Frontend Transport; Thu, 23 Jan 2020 15:52:12 +0000 Received: from MAXPR01MB2157.INDPRD01.PROD.OUTLOOK.COM ([fe80::6109:8ab6:d9a0:2a27]) by MAXPR01MB2157.INDPRD01.PROD.OUTLOOK.COM ([fe80::6109:8ab6:d9a0:2a27%6]) with mapi id 15.20.2644.028; Thu, 23 Jan 2020 15:52:12 +0000 From: selva kumar To: Tom Lane CC: "pgsql-bugs@lists.postgresql.org" Subject: RE: Query will execute when inner query have issue Thread-Topic: Query will execute when inner query have issue Thread-Index: AQHV0b8t5z/HQhEw4k+SOsI98Pg3haf4U3qAgAATJ60= Date: Thu, 23 Jan 2020 15:52:12 +0000 Message-ID: References: ,<5259.1579790613@sss.pgh.pa.us> In-Reply-To: <5259.1579790613@sss.pgh.pa.us> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-incomingtopheadermarker: OriginalChecksum:083512302F080438D81211A6CEDFCBC3A1E0E242F6A8ECB496518938C48E3A7A;UpperCasedChecksum:55FB9172AE5F9EA07A331DDBBE421709E1CB0C98B7767B3E0C1C4322289E67A0;SizeAsReceived:6995;Count:46 x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [bvHrVVYnmrfuL4pqr5ZeK6QvhGZEV9M3] x-ms-publictraffictype: Email x-incomingheadercount: 46 x-eopattributedmessage: 0 x-ms-office365-filtering-correlation-id: b10c10c5-4964-460e-92bb-08d7a01c360b x-ms-traffictypediagnostic: PU1APC01HT121: x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: 2Oo75XWO+ycC8k2e8GLcEz1evU+IY0AtoJJ5ttS3iIlU8h7rz2/YxuDq42zl7XZrv3DxBNLTB8t/NPj+4C4TDgVleV96P1ROnSfJZox5J/kJA0r3qDdLB+x/if9tmxZt6jfbE8iTGfNZlTMEUuKidvZ/+79i1jy1jgGjuoZXUx1cAN1vZ5CYUH1c0+TtoCqxL207pkZfy0DByyHs4NZktIsNOduHf/RJ6SJXMT3QLSI= x-ms-exchange-antispam-messagedata: mfKdEUAcApUVC0WLPkGNS1luqJHYOymqWYJCrKOrJXS2s0M5B3TKyBzh/hHxZmAg2Bk4k4D9liJo5Osg2JLof+Dub/SsD1npm59sgA2kBx5ChgFEGnTIwq9OLUxf88vZaNux2IXUXedbXXak9+sjFg== x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_MAXPR01MB2157CA93DACAD45CEBE77973E20F0MAXPR01MB2157INDP_" MIME-Version: 1.0 X-OriginatorOrg: hotmail.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: b10c10c5-4964-460e-92bb-08d7a01c360b X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Jan 2020 15:52:12.1731 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Internet X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-Transport-CrossTenantHeadersStamped: PU1APC01HT121 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_MAXPR01MB2157CA93DACAD45CEBE77973E20F0MAXPR01MB2157INDP_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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=3D'2020-01-22'; ERROR: column "productid" does not exist LINE 1: select productid from sales where salesdate=3D'2020-01-22' But the following query will executed select * from product where productid in (select productid from sales where= salesdate=3D'2020-01-22') Sent from Mail for Window= s 10 ________________________________ From: Tom Lane Sent: Thursday, January 23, 2020 8:13:33 PM To: selva kumar Cc: pgsql-bugs@lists.postgresql.org Subject: Re: Query will execute when inner query have issue selva kumar 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 --_000_MAXPR01MB2157CA93DACAD45CEBE77973E20F0MAXPR01MB2157INDP_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Greetings

 

I did the following excise.

 

CREATE TABLE product(

productid int PRIMARY KEY,

productname CHARACTER VARYING NOT NULL);<= /p>

 

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

INSERT INTO product values (2,'Pencil');<= /p>

 

 

CREATE TABLE sales(

salesdate date,

product int,

qyt int);

 

INSERT INTO sales VALUES  ('2020-01-22',1,10);<= o:p>

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

 

 

select productid from sales where salesdate=3D'2020-= 01-22';

ERROR:  column "productid" does not e= xist

LINE 1: select productid from sales where salesdate= =3D'2020-01-22'

 

 

But the following query will executed

select * from product where productid in (select pro= ductid from sales where salesdate=3D'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, interprete= d 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.

            &nb= sp;           regards, to= m lane
--_000_MAXPR01MB2157CA93DACAD45CEBE77973E20F0MAXPR01MB2157INDP_--