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-0003wq-3b 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-Ke for pgsql-bugs@arkaria.postgresql.org; Thu, 23 Jan 2020 16:19:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iuem8-0006EF-Js for pgsql-bugs@lists.postgresql.org; Thu, 23 Jan 2020 15:52:08 +0000 Received: from mail-oln040092253043.outbound.protection.outlook.com ([40.92.253.43] helo=APC01-SG2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iuem0-0003UZ-9x for pgsql-bugs@lists.postgresql.org; Thu, 23 Jan 2020 15:52:07 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=hDS2jBVKlYJf9iu+SswA8f+p4fmaOpFI3Vsy/aTDpTuFW4VPBCyWHjBvODumYG9aqt5j77tslDHzsxdDpjMBz2dYv4dcVYoT7JguE0/m1BwlSo6R8K8CS61TNrfdzhuNzEM0MCtBEWhf6vHpR+jIK57+fwGCAAjNkzYamb90UEOjiG6Dcs2TqSNAZz9frO0lMiD/Axheul4V1MFnHU7r1B6nfQcZKwBqesYZ5yQ/m7JwtGHp4hhF0kvZU5T45i4dcB9r7CSRpE0EiPOQC+J8DyVCjSj2uwAQrqS9iGFx7YY88WZjtJ/72+AN8n0d6It/yXoiq3OCBMMdPaF4+U3gHQ== 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=i6yi+dzowak7K4HPHNnyTebhVDuBzpc6J/FD0y72CP4=; b=dkfD4mjA1fKDMN8nvK+yYkj0DdvDPPZvSmKk+JRNAOLtS8dv7RP8w/myXsQiXqGIzGgtloeKVgPwdwR6ZMrnTblN0XIsbWl+X5qliG+YsoxXr9567YkUjgWsoWD3K/OV5gCWsnAfkGLIZTZTBd2JCyHd35gnP8do+G9Bc7i0aEFl9Ty2b1vZbMUb2CAut6nzrjSDPDc6lI2AYUvQRIo9gXbd5b5kaFjaMChD/SpFIXk0AjRiY5BUkSNc+Ar7q2dZZYM6JVWJieYoVOz4eAsevyHMj+V3C5rpxqjr4gVXKr4mduDJXGgKEWeoa2lP0wOJRCJ9etGiltCXYX0E28734Q== 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=i6yi+dzowak7K4HPHNnyTebhVDuBzpc6J/FD0y72CP4=; b=ScOfHmSORTM+ZvG4BzAppU0Pb36R+jYiaC7pVVmF1Ec3CJT5qTjZiOU9uTo+5E/9BHg4/d4pN9ZzWuQ3C1Wu7ZGVrq3HiD8GpXAnVUuDzm6TD7fyoLODB4wP13qVgmmeHUVVK8FCOTCA5k+GhmLtkehgapI9Cgcp1j2FkLMJ++2XnzSbst3XmkLqOrc7eUCGvVzSQaJh4JpfrLQDfYJatpyTZ8Smeni1SDEi7To+FZm1GqRYWczfal7AlPacvcwZcSMEwtu8yCfOKRxLG0F/9W8TatGVhPXGKxLEsK5NC3hxcpX8LSVORDRwEn3zAsFJerYYkVA618Gg8seV0w663g== Received: from PU1APC01FT056.eop-APC01.prod.protection.outlook.com (10.152.252.56) by PU1APC01HT226.eop-APC01.prod.protection.outlook.com (10.152.252.164) 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:51:56 +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:51:56 +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:51:56 +0000 From: selva kumar To: Daniel Gustafsson 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+SOsI98Pg3haf4AxqAgABjdE4= Date: Thu, 23 Jan 2020 15:51:56 +0000 Message-ID: References: ,<35DEDE9B-385E-49BA-A872-8D43279784B2@yesql.se> In-Reply-To: <35DEDE9B-385E-49BA-A872-8D43279784B2@yesql.se> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-incomingtopheadermarker: OriginalChecksum:F0E6CCA30392368B4CB6542CF138A03F3CEB4E621D9A04B823A5D417214736DE;UpperCasedChecksum:4E5D8537CFCC351F3B5C71C27C4A6F63006F85CAC7C4E9F6989070C73E03FF58;SizeAsReceived:7035;Count:46 x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [v/E2Vm6ywViTifAJN6K4e0jzMzJ6tkM0] x-ms-publictraffictype: Email x-incomingheadercount: 46 x-eopattributedmessage: 0 x-ms-office365-filtering-correlation-id: a3ee9767-f9b3-4f4d-a5cf-08d7a01c2c8b x-ms-traffictypediagnostic: PU1APC01HT226: x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: Y5lIf/XxLk+FaIIan6hSL9wgD9+IGXKCkMx+t67mMWCRoQ8gIMivqQIllLjkCzaVi2iyPPWsr7DyqLgLhHQVw0rvkcoiQyzNLwtN9ei0QIrD+Pscs0diGUXPn/HnXk+Q+PHRcdGPEFVh/SYJS3uCw0sMosym1VfDKAlZy1c4t0wY2a5rrI1E/2+CfcpzWwDlkkTJE3sgbbbpJnr5xbBUWeqTao0+IxalemrfNYvxMio= x-ms-exchange-antispam-messagedata: j3bxUtfQPtQ453IRju82Dmj642cJmojIazDkTfOBB+an1SnioxyG/oVoExlqLLeU9gD/bl2YJZMsPsOa7hbxgHrbuQhscxfjlk7CcAB9Zm3suz9PLomxar12XOWxP/+ZPikeGnzOIRlBcvNMucYmAw== x-ms-exchange-transport-forked: True Content-Type: multipart/alternative; boundary="_000_MAXPR01MB21577DB26C2E9CBF67314BB5E20F0MAXPR01MB2157INDP_" 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: a3ee9767-f9b3-4f4d-a5cf-08d7a01c2c8b X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Jan 2020 15:51:56.2043 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Internet X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-Transport-CrossTenantHeadersStamped: PU1APC01HT226 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_MAXPR01MB21577DB26C2E9CBF67314BB5E20F0MAXPR01MB2157INDP_ Content-Type: text/plain; charset="Windows-1252" 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: Daniel Gustafsson Sent: Thursday, January 23, 2020 3:25:53 PM To: selva kumar Cc: pgsql-bugs@lists.postgresql.org Subject: Re: Query will execute when inner query have issue > On 23 Jan 2020, at 08:34, selva kumar 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 =93Field is not = found=94 This is not reproducible for me, it fails as expected: db=3D# 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 quer= y? cheers ./daniel --_000_MAXPR01MB21577DB26C2E9CBF67314BB5E20F0MAXPR01MB2157INDP_ Content-Type: text/html; charset="Windows-1252" 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: 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 <sel= va.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 =93Field is n= ot found=94  

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

db=3D# 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);
            &nb= sp;            =             &nb= sp;        ^
HINT:  Perhaps you meant to reference the column "a.id".

Which version of postgres are you using, and how are you executing the quer= y?

cheers ./daniel
--_000_MAXPR01MB21577DB26C2E9CBF67314BB5E20F0MAXPR01MB2157INDP_--