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 1qBE92-0004a8-Sq for pgsql-bugs@arkaria.postgresql.org; Mon, 19 Jun 2023 12:38:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qBE91-0002ZW-RK for pgsql-bugs@arkaria.postgresql.org; Mon, 19 Jun 2023 12:38:07 +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 1qBDg2-00023K-6K for pgsql-bugs@lists.postgresql.org; Mon, 19 Jun 2023 12:08:10 +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 1qBDfy-003Q3z-Rz for pgsql-bugs@lists.postgresql.org; Mon, 19 Jun 2023 12:08:09 +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=8M63z1lqxsHX02VNtrQ1b+sFd9Dsik9w194siqnp7PY=; b=Q6tXhMYBcmIa0Fi9kfI23E7qIw WgdXA6U4J/ZV4RX3nR+/h+/abY+AY/0NXJpqGVF6Ru3Ak4CEN07Aryk7Ipd44ooM3NTnGYcIpQaNg T5IYqu0CeE1byPjDg16g+2gF75WcbHf4/IwxOym/RlU97GO+Yr68PQpAOr0K65Z235s+qpS/sAvoE IoOHMinrTzzg84ZtgJDwoa2k02t1MFLCBtQDWwn2Qok3H3+f/M+C8rTQNixPuNURrVLgyz1h5iYbe JVL4SDdxgdxABM5i1xuRb29xsgnTTqBa3VpzGWZeAJNdiV0l6vMzZRq2FDotnx8J6mlwFavfGT+Rg vl3Yf/Yg==; 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 1qBDfw-0003Sz-Cf for pgsql-bugs@lists.postgresql.org; Mon, 19 Jun 2023 12:08:05 +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 1qBDft-008pXm-UN for pgsql-bugs@lists.postgresql.org; Mon, 19 Jun 2023 12:08:03 +0000 Content-Type: multipart/mixed; boundary="===============5174167136099804835==" MIME-Version: 1.0 Subject: BUG #17982: Inconsistent results of SELECT with CTE caused by subquery comparison To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: zuming.jiang@inf.ethz.ch Reply-To: zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org Date: Mon, 19 Jun 2023 12:07:42 +0000 Message-ID: <17982-3fa239feecd6c1b3@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 --===============5174167136099804835== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNzk4MgpMb2dnZWQgYnk6ICAgICAgICAgIFp1bWluZyBKaWFuZwpFbWFp bCBhZGRyZXNzOiAgICAgIHp1bWluZy5qaWFuZ0BpbmYuZXRoei5jaApQb3N0Z3JlU1FMIHZlcnNp b246IDE2YmV0YTEKT3BlcmF0aW5nIHN5c3RlbTogICBVYnVudHUgMjAuMDQKRGVzY3JpcHRpb246 ICAgICAgICAKCk15IGZ1enplciBmaW5kcyBhIGNvcnJlY3RuZXNzIGJ1ZyBpbiBQb3N0Z3Jlcywg d2hpY2ggbWFrZXMgUG9zdGdyZXMgcmV0dXJuCmluY29uc2lzdGVudCByZXN1bHRzLiBUaGlzIGJ1 ZyBzdGlsbCBjYW4gYmUgdHJpZ2dlcmVkIGFmdGVyIHRoaXMgZml4aW5nCihodHRwczovL2dpdGh1 Yi5jb20vcG9zdGdyZXMvcG9zdGdyZXMvY29tbWl0L2Y0YzAwZDEzOGY2ZGVhNGM5ZDhhZjhlYzI4 MGI3ZWRjOWIwYTI5ZTEpDQoNCi0tLSBTZXQgdXAgZGF0YWJhc2UgLS0tDQpjcmVhdGUgdGFibGUg dDAgKHZrZXkgaW50NCwgYzAgZmxvYXQ4LCBjMSB0aW1lc3RhbXAsIGMzIHRleHQpOw0KY3JlYXRl IHRhYmxlIHQxICh2a2V5IGludDQsIHBrZXkgaW50NCwgYzQgdGltZXN0YW1wKTsNCmNyZWF0ZSB2 aWV3IHQzIGFzIHNlbGVjdCB0cnVlIGFzIGNfMCBmcm9tIChzZWxlY3QgZGlzdGluY3QgdHJ1ZSBh cyBjXzQpIGFzCnN1YnFfMTsNCmluc2VydCBpbnRvIHQwIHZhbHVlcyAoMiwgMy40MSwgbWFrZV90 aW1lc3RhbXAoMjAzMSwgOCwgNiwgMTMsIDMzLCAzNSksCiczJyk7DQotLS0NCg0KVGhlIGZ1enpl ciBnZW5lcmF0ZXMgVGVzdCBjYXNlIDE6DQoNCi0tLSBUZXN0IGNhc2UgMSAtLS0NCldJVEggY3Rl XzMgQVMgKHNlbGVjdA0KICAgIGJ0dGV4dGNtcChyZWZfMjQuYzMsIHJlZl8yNC5jMykgYXMgY18w LA0KICAgIChyZWZfMjUuYzQgPD4gcmVmXzI0LmMxKSBhcyBjXzYsDQpjYXNlIHdoZW4gKCgocmVm XzI1LnZrZXkgPiAoc2VsZWN0IHJlZl8yNS5wa2V5IGFzIGNfMCBmcm9tIHQwIGFzIHJlZl8xODMK d2hlcmUgZmFsc2Ugb3JkZXIgYnkgY18wIGFzYyBsaW1pdCAxKSkNCiAgICAgICAgb3IgKG5vdCAo cmVmXzI1LnZrZXkgPiAoc2VsZWN0IHJlZl8yNS5wa2V5IGFzIGNfMCBmcm9tIHQwIGFzIHJlZl8x ODMKd2hlcmUgZmFsc2Ugb3JkZXIgYnkgY18wIGFzYyBsaW1pdCAxKSkpKQ0KICAgICAgICBvciAo KHJlZl8yNS52a2V5ID4gKHNlbGVjdCByZWZfMjUucGtleSBhcyBjXzAgZnJvbSB0MCBhcyByZWZf MTgzCndoZXJlIGZhbHNlIG9yZGVyIGJ5IGNfMCBhc2MgbGltaXQgMSkpIGlzIG51bGwpKQ0KICAg ICB0aGVuIHJlZl8yNC5jMCBlbHNlIHRhbmgocmVmXzI0LmMwKSBlbmQgYXMgY183DQogICBmcm9t DQogICAgKHQwIGFzIHJlZl8yNA0KICAgICAgZnVsbCBvdXRlciBqb2luIHQxIGFzIHJlZl8yNQ0K ICAgICAgb24gKHJlZl8yNC52a2V5ID0gcmVmXzI1LnZrZXkpKQ0KICB3aGVyZSAobnVsbDo6Ym9v bCkNCikNCnNlbGVjdA0KICAgIHJlZl8zMS5jMSBhcyBjXzAsDQogICAgcmVmXzMwLmNfNyBhcyBj XzENCiAgZnJvbQ0KICAgKCgoc2VsZWN0DQogICAgICAgICAgICAgIHJlZl8yOC5jXzAgYXMgY18w LA0KICAgICAgICAgICAgICByZWZfMjguY18wIGFzIGNfMSwNCiAgICAgICAgICAgICAgcmVmXzI4 LmNfMCBhcyBjXzIsDQogICAgICAgICAgICAgIHJlZl8yOC5jXzAgYXMgY18zLA0KICAgICAgICAg ICAgICByZWZfMjguY18wIGFzIGNfNA0KICAgICAgICAgICAgZnJvbQ0KICAgICAgICAgICAgICB0 MyBhcyByZWZfMjgpIGFzIHN1YnFfNA0KICAgICAgICByaWdodCBvdXRlciBqb2luIGN0ZV8zIGFz IHJlZl8zMA0KICAgICAgICBvbiAoc3VicV80LmNfMSA9IHJlZl8zMC5jXzYpKQ0KICAgICAgcmln aHQgb3V0ZXIgam9pbiB0MCBhcyByZWZfMzENCiAgICAgIG9uIChyZWZfMzAuY18wID0gcmVmXzMx LnZrZXkpKQ0KICB3aGVyZSByZWZfMzEuYzEgaXMgbm90IG51bGw7DQotLS0NCg0KQmVjYXVzZSBg KHJlZl8yNS52a2V5ID4gKHNlbGVjdCByZWZfMjUucGtleSBhcyBjXzAgZnJvbSB0MCBhcyByZWZf MTgzIHdoZXJlCmZhbHNlIG9yZGVyIGJ5IGNfMCBhc2MgbGltaXQgMSkpYCBjb3VsZCBvbmx5IGJl IFRSVUUsIEZBTFNFLCBvciBOVUxMLApgKCgocmVmXzI1LnZrZXkgPiAoc2VsZWN0IHJlZl8yNS5w a2V5IGFzIGNfMCBmcm9tIHQwIGFzIHJlZl8xODMgd2hlcmUgZmFsc2UKb3JkZXIgYnkgY18wIGFz YyBsaW1pdCAxKSkgb3IgKG5vdCAocmVmXzI1LnZrZXkgPiAoc2VsZWN0IHJlZl8yNS5wa2V5IGFz IGNfMApmcm9tIHQwIGFzIHJlZl8xODMgd2hlcmUgZmFsc2Ugb3JkZXIgYnkgY18wIGFzYyBsaW1p dCAxKSkpKSBvciAoKHJlZl8yNS52a2V5Cj4gKHNlbGVjdCByZWZfMjUucGtleSBhcyBjXzAgZnJv bSB0MCBhcyByZWZfMTgzIHdoZXJlIGZhbHNlIG9yZGVyIGJ5IGNfMCBhc2MKbGltaXQgMSkpIGlz IG51bGwpKWAgbXVzdCBiZSBUUlVFLiBUaGVyZWZvcmUsIEkgcmVwbGFjZSBgKCgocmVmXzI1LnZr ZXkgPgooc2VsZWN0IHJlZl8yNS5wa2V5IGFzIGNfMCBmcm9tIHQwIGFzIHJlZl8xODMgd2hlcmUg ZmFsc2Ugb3JkZXIgYnkgY18wIGFzYwpsaW1pdCAxKSkgb3IgKG5vdCAocmVmXzI1LnZrZXkgPiAo c2VsZWN0IHJlZl8yNS5wa2V5IGFzIGNfMCBmcm9tIHQwIGFzCnJlZl8xODMgd2hlcmUgZmFsc2Ug b3JkZXIgYnkgY18wIGFzYyBsaW1pdCAxKSkpKSBvciAoKHJlZl8yNS52a2V5ID4gKHNlbGVjdApy ZWZfMjUucGtleSBhcyBjXzAgZnJvbSB0MCBhcyByZWZfMTgzIHdoZXJlIGZhbHNlIG9yZGVyIGJ5 IGNfMCBhc2MgbGltaXQgMSkpCmlzIG51bGwpKWAgd2l0aCBUUlVFLCBhbmQgZ2V0IFRlc3QgY2Fz ZSAyOg0KDQotLS0gVGVzdCBjYXNlIDIgLS0tDQpXSVRIIGN0ZV8zIEFTIChzZWxlY3QNCiAgICBi dHRleHRjbXAocmVmXzI0LmMzLCByZWZfMjQuYzMpIGFzIGNfMCwNCiAgICAocmVmXzI1LmM0IDw+ IHJlZl8yNC5jMSkgYXMgY182LA0KY2FzZSB3aGVuIHRydWUgdGhlbiByZWZfMjQuYzAgZWxzZSB0 YW5oKHJlZl8yNC5jMCkgZW5kIGFzIGNfNw0KICAgZnJvbQ0KICAgICh0MCBhcyByZWZfMjQNCiAg ICAgIGZ1bGwgb3V0ZXIgam9pbiB0MSBhcyByZWZfMjUNCiAgICAgIG9uIChyZWZfMjQudmtleSA9 IHJlZl8yNS52a2V5KSkNCiAgd2hlcmUgKG51bGw6OmJvb2wpDQopDQpzZWxlY3QNCiAgICByZWZf MzEuYzEgYXMgY18wLA0KICAgIHJlZl8zMC5jXzcgYXMgY18xDQogIGZyb20NCiAgICgoKHNlbGVj dA0KICAgICAgICAgICAgICByZWZfMjguY18wIGFzIGNfMCwNCiAgICAgICAgICAgICAgcmVmXzI4 LmNfMCBhcyBjXzEsDQogICAgICAgICAgICAgIHJlZl8yOC5jXzAgYXMgY18yLA0KICAgICAgICAg ICAgICByZWZfMjguY18wIGFzIGNfMywNCiAgICAgICAgICAgICAgcmVmXzI4LmNfMCBhcyBjXzQN CiAgICAgICAgICAgIGZyb20NCiAgICAgICAgICAgICAgdDMgYXMgcmVmXzI4KSBhcyBzdWJxXzQN CiAgICAgICAgcmlnaHQgb3V0ZXIgam9pbiBjdGVfMyBhcyByZWZfMzANCiAgICAgICAgb24gKHN1 YnFfNC5jXzEgPSByZWZfMzAuY182KSkNCiAgICAgIHJpZ2h0IG91dGVyIGpvaW4gdDAgYXMgcmVm XzMxDQogICAgICBvbiAocmVmXzMwLmNfMCA9IHJlZl8zMS52a2V5KSkNCiAgd2hlcmUgcmVmXzMx LmMxIGlzIG5vdCBudWxsOw0KLS0tDQoNCi0tLSBFeHBlY3RlZCBiZWhhdmlvciAtLS0NClRlc3Qg Y2FzZSAxIGFuZCBUZXN0IGNhc2UgMiByZXR1cm4gdGhlIHNhbWUgcmVzdWx0cy4NCg0KLS0tIEFj dHVhbCBiZWhhdmlvciAtLS0NClRlc3QgY2FzZSAxIHJldHVybnMgMSByb3cgKHsyMDMxLTA4LTA2 IDEzOjMzOjM1fH0pLCB3aGlsZSBUZXN0IGNhc2UgcmV0dXJucwowIHJvd3MuDQoNCi0tLSBQb3N0 Z3JlcyB2ZXJzaW9uIC0tLQ0KR2l0aHViIGNvbW1pdDogN2ZjZDdlZjJhOWMzNzJiNzg5Zjk1YjQw MDQzZWRmZmRjNjExYzU2Ng0KVmVyc2lvbjogUG9zdGdyZVNRTCAxNmJldGExIG9uIHg4Nl82NC1w Yy1saW51eC1nbnUsIGNvbXBpbGVkIGJ5IGdjYyAoVWJ1bnR1CjkuNC4wLTF1YnVudHUxfjIwLjA0 LjEpIDkuNC4wLCA2NC1iaXQNCg0KLS0tIFBsYXRmb3JtIGluZm9ybWF0aW9uIC0tLQ0KUGxhdGZv cm06IFVidW50dSAyMC4wNA0KS2VybmVsOiBMaW51eCA1LjQuMC0xNDctZ2VuZXJpYwoK --===============5174167136099804835==--