Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q6UKu-0001Kb-VM for pgsql-bugs@arkaria.postgresql.org; Tue, 06 Jun 2023 10:54:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q6UKt-0000YO-RS for pgsql-bugs@arkaria.postgresql.org; Tue, 06 Jun 2023 10:54:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q6S7s-0007yP-EG for pgsql-bugs@lists.postgresql.org; Tue, 06 Jun 2023 08:33:12 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q6S7o-000XZ8-GQ for pgsql-bugs@lists.postgresql.org; Tue, 06 Jun 2023 08:33:11 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: MIME-Version:Content-Type:Sender:Content-Transfer-Encoding:Content-ID: Content-Description:In-Reply-To:References; bh=SrSpOFyOQ2c1rCMp3LGac7C6s7ofqSFgVh2GthhH+Kg=; b=jpZJlwnHe12nQSpkBq7aXc+p1n m8c+A/fkDqkK2kc06pnF8Bfv+bFd+co5TTW2eDhw3pAY0z0wyp9tYcSz4hww52+syavZmfnZPhNhs vITGMaoVUwkr5ZJju/+oXwwZ/HO1LRCIw+pX8lNA/v1VHcz6ZIdW+3OBmCJULEab8lWihUpvYis2s NGex+WNkHv52gkyx944bFRuOBdlaKA4zf4vaYP+2Jre65b89AkICAB+da9DTfDNF7AYZT6Zk2SSWb ND7KhyTX4nHXlhaEtEH+ij7BQChVpSnZp++mBbeDUNJ7yJO9CWSVVO3Kx/6Q/I4l9B5RVfzd6m8p1 Due1dFaw==; Received: from wrigleys.postgresql.org ([2a02:c0:301:0:ffff::34]) by mahout.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q6S7l-0000hI-8U for pgsql-bugs@lists.postgresql.org; Tue, 06 Jun 2023 08:33:07 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1q6S7h-001ThB-6t for pgsql-bugs@lists.postgresql.org; Tue, 06 Jun 2023 08:33:02 +0000 Content-Type: multipart/mixed; boundary="===============6986282944303636643==" MIME-Version: 1.0 Subject: BUG #17963: Recursive table cannot be referenced when using LEFT JOIN instead of LATERAL in recursive term To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: jiangshan.liu@tju.edu.cn Reply-To: jiangshan.liu@tju.edu.cn, pgsql-bugs@lists.postgresql.org Date: Tue, 06 Jun 2023 08:32:44 +0000 Message-ID: <17963-94341a7d6b29ce47@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --===============6986282944303636643== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNzk2MwpMb2dnZWQgYnk6ICAgICAgICAgIEppYW5nc2hhbiBMaXUKRW1h aWwgYWRkcmVzczogICAgICBqaWFuZ3NoYW4ubGl1QHRqdS5lZHUuY24KUG9zdGdyZVNRTCB2ZXJz aW9uOiAxNS4zCk9wZXJhdGluZyBzeXN0ZW06ICAgVWJ1bnR1CkRlc2NyaXB0aW9uOiAgICAgICAg CgpJIGdvdCB0aGUgZXhwZWN0ZWQgb3V0cHV0IHdoZW4gSSBleGVjdXRlZCB0aGUgZm9sbG93aW5n IHJlY3Vyc2l2ZSBxdWVyeTogDQoNCi0tIFNRTCBDb2RlIDENCldJVEggUkVDVVJTSVZFIHJ1bihl bmRfZmxhZywgdGFnKSBBUyAoDQogIFNFTEVDVCB0cnVlLCAnbm9uLXJlYy10ZXJtJw0KICAgIFVO SU9OIEFMTA0KICBTRUxFQ1QgcmVzdWx0LiogRlJPTSBydW4sDQogICAgTEFURVJBTCAoDQogICAg ICBTRUxFQ1QgdHJ1ZSwgJ3JlYy10ZXJtLWJyYW5jaC1BJyBXSEVSRSBydW4udGFnPSdub24tcmVj LXRlcm0nDQogICAgICAgIFVOSU9OIEFMTA0KICAgICAgU0VMRUNUIHRydWUsICdyZWMtdGVybS1i cmFuY2gtQicgV0hFUkUgcnVuLnRhZz0ncmVjLXRlcm0tYnJhbmNoLUEnDQogICAgICAgIFVOSU9O IEFMTA0KICAgICAgU0VMRUNUIGZhbHNlLCAncmVjLXRlcm0tYnJhbmNoLUMnIFdIRVJFIHJ1bi50 YWc9J3JlYy10ZXJtLWJyYW5jaC1CJw0KICAgICkgQVMgcmVzdWx0DQogICAgV0hFUkUgcnVuLmVu ZF9mbGFnDQopDQpTRUxFQ1QgKiBGUk9NIHJ1bjsNCg0KLS0gcmVzdWx0IG9mIFNRTCBDb2RlIDEN CiBlbmRfZmxhZyB8ICAgICAgICB0YWcgICAgICAgIA0KLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0t LS0tLS0tDQogdCAgICAgICAgfCBub24tcmVjLXRlcm0NCiB0ICAgICAgICB8IHJlYy10ZXJtLWJy YW5jaC1BDQogdCAgICAgICAgfCByZWMtdGVybS1icmFuY2gtQg0KIGYgICAgICAgIHwgcmVjLXRl cm0tYnJhbmNoLUMNCig0IHJvd3MpDQoNCg0KU2luY2UgdGhlIExBVEVSQUwga2V5d29yZCBpcyBu b3QgaW4gdGhlIFNRTCBzdGFuZGFyZCwgSSBuZWVkIHRvIGVxdWl2YWxlbnRseQpyZXdyaXRlIHRo aXMgc3RhdGVtZW50IHRvIGFsc28gYWRhcHQgdG8gYSBEQk1TIHRoYXQgZG9lcyBub3Qgc3VwcG9y dCB0aGUKTEFURVJBTCBrZXl3b3JkIChjb2RlIDIgYW5kIGNvZGUgMyBhcmUgbXkgdHdvIGF0dGVt cHRzKSwgaG93ZXZlciwgSSBnb3QgdGhlCmVycm9yIG9uIHBvc3RncmVzcWw6IA0KDQotLSBTUUwg Q29kZSAyDQpXSVRIIFJFQ1VSU0lWRSBydW4oZW5kX2ZsYWcsIHRhZykgQVMgKA0KICBTRUxFQ1Qg dHJ1ZSwgJ25vbi1yZWMtdGVybScNCiAgICBVTklPTiBBTEwNCiAgU0VMRUNUIHJlc3VsdC4qIEZS T00gcnVuDQogICAgTEVGVCBKT0lOICgNCiAgICAgIFNFTEVDVCB0cnVlLCAncmVjLXRlcm0tYnJh bmNoLUEnIFdIRVJFIHJ1bi50YWc9J25vbi1yZWMtdGVybScNCiAgICAgICAgVU5JT04gQUxMDQog ICAgICBTRUxFQ1QgdHJ1ZSwgJ3JlYy10ZXJtLWJyYW5jaC1CJyBXSEVSRSBydW4udGFnPSdyZWMt dGVybS1icmFuY2gtQScNCiAgICAgICAgVU5JT04gQUxMDQogICAgICBTRUxFQ1QgZmFsc2UsICdy ZWMtdGVybS1icmFuY2gtQycgV0hFUkUgcnVuLnRhZz0ncmVjLXRlcm0tYnJhbmNoLUInDQogICAg KSBBUyByZXN1bHQgT04gdHJ1ZQ0KICAgIFdIRVJFIHJ1bi5lbmRfZmxhZw0KKQ0KU0VMRUNUICog RlJPTSBydW47DQoNCi0tIFNRTCBDb2RlIDMNCldJVEggUkVDVVJTSVZFIHJ1bihlbmRfZmxhZywg dGFnKSBBUyAoDQogIFNFTEVDVCB0cnVlLCAnbm9uLXJlYy10ZXJtJw0KICAgIFVOSU9OIEFMTA0K ICBTRUxFQ1QgcmVzdWx0LiogRlJPTSBydW4sDQogICAgKFNFTEVDVCBDQVNFDQogICAgICAgIFdI RU4gcnVuLnRhZz0nbm9uLXJlYy10ZXJtJyBUSEVOIChTRUxFQ1QgdHJ1ZSwKJ3JlYy10ZXJtLWJy YW5jaC1BJykNCiAgICAgICAgV0hFTiBydW4udGFnPSdyZWMtdGVybS1icmFuY2gtQScgVEhFTiAo U0VMRUNUIHRydWUsCidyZWMtdGVybS1icmFuY2gtQicpDQogICAgICAgIFdIRU4gcnVuLnRhZz0n cmVjLXRlcm0tYnJhbmNoLUInIFRIRU4gKFNFTEVDVCBmYWxzZSwKJ3JlYy10ZXJtLWJyYW5jaC1D JykNCiAgICBFTkQpIEFTIHJlc3VsdA0KICAgIFdIRVJFIHJ1bi5lbmRfZmxhZw0KKQ0KU0VMRUNU ICogRlJPTSBydW47DQoNCi0tIHNhbWUgZXJyb3Igb2YgU1FMIENvZGUgMiBhbmQgQ29kZSAzDQo+ IEVSUk9SOiAgaW52YWxpZCByZWZlcmVuY2UgdG8gRlJPTS1jbGF1c2UgZW50cnkgZm9yIHRhYmxl ICJydW4iDQpMSU5FIDY6ICAgICAgIFNFTEVDVCB0cnVlLCAncmVjLXRlcm0tYnJhbmNoLUEnIFdI RVJFIHJ1bi50YWc9J25vbi1yZS4uLg0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICBeDQpISU5UOiAgVGhlcmUgaXMgYW4gZW50cnkgZm9yIHRhYmxl ICJydW4iLCBidXQgaXQgY2Fubm90IGJlIHJlZmVyZW5jZWQgZnJvbQp0aGlzIHBhcnQgb2YgdGhl IHF1ZXJ5Lg0KDQoNClRvIHZlcmlmeSB0aGF0IHJlZmVyZW5jZXMgdG8gcmVjdXJzaXZlIHRhYmxl ICgicnVuIikgYXJlIGFsbG93ZWQgaW4gdGhlCnJlY3Vyc2l2ZSB0ZXJtIG9mIGEgcmVjdXJzaXZl IHF1ZXJ5LCBJIHRyaWVkIHRoZSBmb2xsb3dpbmcgcXVlcnkgYW5kIGdvdCB0aGUKZXhwZWN0ZWQg cmVzdWx0czogDQoNCi0tIFNRTCBDb2RlIDQNCldJVEggUkVDVVJTSVZFIHJ1bihlbmRfZmxhZywg dGFnKSBBUyAoDQogIFNFTEVDVCB0cnVlLCAnbm9uLXJlYy10ZXJtJw0KICAgIFVOSU9OIEFMTA0K ICBTRUxFQ1QgcmVzdWx0LiogRlJPTSBydW4NCiAgICBMRUZUIEpPSU4gKA0KICAgICAgU0VMRUNU IGZhbHNlLCAncmVjLXRlcm0tYnJhbmNoLUEnDQogICAgKSBBUyByZXN1bHQgT04gcnVuLnRhZz0n bm9uLXJlYy10ZXJtJw0KICAgIFdIRVJFIHJ1bi5lbmRfZmxhZw0KKQ0KU0VMRUNUICogRlJPTSBy dW47DQoNCi0tIHJlc3VsdCBvZiBTUUwgQ29kZSA0DQogZW5kX2ZsYWcgfCAgICAgICAgdGFnICAg ICAgICANCi0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIHQgICAgICAgIHwgbm9uLXJl Yy10ZXJtDQogZiAgICAgICAgfCByZWMtdGVybS1icmFuY2gtQQ0KKDIgcm93cykNCg0KQnV0IHdo ZW4gSSBwdXQgdGhlIHJlZmVyZW5jZSB0byB0aGUgcmVjdXJzaXZlIHRhYmxlICh0YWJsZSAicnVu IikgaW4gdGhlIE9OCmNsYXVzZSBvZiBTUUwgY29kZSA0LCBJIGNhbid0IHNlZW0gdG8gd3JpdGUg bXVsdGlwbGUgYnJhbmNoZXMgaW4gdGhlCnJlY3Vyc2l2ZSB0ZXJtIHRvIGEgdGFibGUgd2l0aCBh biBhbGlhcyAobGlrZSAiQVMgcmVzdWx0IiBpbiBjb2RlIDIpIHNvIHRoYXQKaXQgY2FuIGJlIGNh bGxlZCB1bmlmb3JtbHkgYXQgcnVudGltZSB2aWEgU0VMRUNUIHJlc3VsdC4qLg0KDQpUaGUgdW5y ZWZlcmVuY2luZyBpbiBTUUwgQ29kZSAyIGFuZCBDb2RlIDMgaXMgdW5leHBlY3RlZCwgaXMgdGhp cyBhIGJ1ZyBpbgpSZWN1cnNpdmUgUXVlcnkgb3IgSk9JTiBvcGVyYXRpb24/IElmIHRoZXJlIGFy ZSBubyBlcnJvcnMgaW4gdGhlIGRlc2lnbiBhbmQKaW1wbGVtZW50YXRpb24gaGVyZSwgaXMgdGhl cmUgYW55IHdheSB0byByZXdyaXRlIFNRTCBDb2RlIDEgd2l0aG91dCB1c2luZwp0aGUgTEFURVJB TCBrZXl3b3JkPyBUaGFuayB5b3UgZm9yIHlvdXIgaGVscCEKCg== --===============6986282944303636643==--