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 1qBeHU-0008LG-7K for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Jun 2023 16:32:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qBeHS-0005Lo-V2 for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Jun 2023 16:32:34 +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 1qBdq0-0006KS-9b for pgsql-bugs@lists.postgresql.org; Tue, 20 Jun 2023 16:04: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 1qBdpu-003eZs-9i for pgsql-bugs@lists.postgresql.org; Tue, 20 Jun 2023 16:04: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=ARoFq3O7KgX18U86t0yXp9sROpua3cY00T+KASCaNeQ=; b=ZVXr7afqA15qPLW825P+fxyFRF 9V+9A4u2brv/ZgHpUOjRTltFNhe679PeB2iL3kSce7vO2d/QmVGRAfoWGvlXACdvsPFZgEfXAEeAF gdfdtp9OlexmxgbzTOCkvTiVheKbDFmbPeqYrj5y8E0akxeYWiwbGDWA9pKcfZzYRKm9bbKuHT+03 BJFJGwws7jDdWBtvwWGc6nZy/rP4cadCnrvBpIc41QYhFidOvVApWWB0s6wKs8NeLX4RSHGwbnLCW /H/Fs6lXkohwqg2UEjiABqlE9cf3Ee85lvV9m4D3hMiF9UqrONME2Uz5aEZ8CfiWOg+g4XdrNw5Io y8KvicuQ==; 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 1qBdps-0001oS-38 for pgsql-bugs@lists.postgresql.org; Tue, 20 Jun 2023 16:04: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 1qBdpp-009WMe-L4 for pgsql-bugs@lists.postgresql.org; Tue, 20 Jun 2023 16:04:02 +0000 Content-Type: multipart/mixed; boundary="===============7120393749232771720==" MIME-Version: 1.0 Subject: BUG #17985: Inconsistent results of SELECT comparing two CASE WHEN clause 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: Tue, 20 Jun 2023 16:03:22 +0000 Message-ID: <17985-748b66607acd432e@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 --===============7120393749232771720== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNzk4NQpMb2dnZWQgYnk6ICAgICAgICAgIFp1bWluZyBKaWFuZwpFbWFp bCBhZGRyZXNzOiAgICAgIHp1bWluZy5qaWFuZ0BpbmYuZXRoei5jaApQb3N0Z3JlU1FMIHZlcnNp b246IDE2YmV0YTEKT3BlcmF0aW5nIHN5c3RlbTogICBVYnVudHUgMjAuMDQKRGVzY3JpcHRpb246 ICAgICAgICAKCk15IGZ1enplciBmaW5kcyBhIGNvcnJlY3RuZXNzIGJ1ZyBpbiBQb3N0Z3Jlcywg d2hpY2ggbWFrZXMgUG9zdGdyZXMgcmV0dXJuCmluY29uc2lzdGVudCByZXN1bHRzLiBUaGlzIGJ1 ZyBjYW4gYmUgcmVwcm9kdWNlZCBldmVuIGFmdGVyIGFwcGx5aW5nIHRoZQpmaXhpbmcgcGF0Y2hl cyBmb3IKaHR0cHM6Ly93d3cucG9zdGdyZXNxbC5vcmcvbWVzc2FnZS1pZC9mbGF0LzE3OTc2LTRi NjM4YjUyNWU5YTk4M2IlNDBwb3N0Z3Jlc3FsLm9yZwphbmQKaHR0cHM6Ly93d3cucG9zdGdyZXNx bC5vcmcvbWVzc2FnZS1pZC9mbGF0LzE3OTc4LTEyZjNkOTNhNTUyOTcyNjYlNDBwb3N0Z3Jlc3Fs Lm9yZw0KDQotLS0gU2V0IHVwIGRhdGFiYXNlIC0tLQ0KY3JlYXRlIHRhYmxlIHQwIChjMiB0ZXh0 KTsNCmNyZWF0ZSB0YWJsZSB0MiAoYzEwIHRleHQpOw0KY3JlYXRlIHRhYmxlIHQ1ICh2a2V5IGlu dDQsIHBrZXkgaW50NCwgYzI3IHRleHQsIGMyOCB0ZXh0LCBjMjkgdGV4dCwgYzMwCnRleHQpOw0K aW5zZXJ0IGludG8gdDAgdmFsdWVzICgnJyk7DQppbnNlcnQgaW50byB0MiB2YWx1ZXMgKCcnKTsN Cmluc2VydCBpbnRvIHQ1IHZhbHVlcyAoMSwgMiwgJ2EnLCAnYScsICdhJywgJ2EnKSwgKDAsIDEs ICcnLCAnJywgJ2EnLAonTCcpOw0KLS0tDQoNClRoZSBmdXp6ZXIgZ2VuZXJhdGVzIFRlc3QgY2Fz ZSAxOg0KDQotLS0gVGVzdCBjYXNlIDEgLS0tDQpzZWxlY3QgKiBmcm9tIHQ1DQp3aGVyZSAodDUu cGtleSA+PSB0NS52a2V5KSA8PiAodDUuYzMwID0gKA0KICAgIHNlbGVjdA0KICAgICAgICB0NS5j MjkgYXMgY18wDQogICAgICBmcm9tDQogICAgICAgICh0MiBhcyByZWZfMA0KICAgICAgICAgIGlu bmVyIGpvaW4gdDAgYXMgcmVmXzENCiAgICAgICAgICBvbiAocmVmXzAuYzEwID0gcmVmXzEuYzIp KQ0KICAgICAgd2hlcmUgKChjYXNlIHdoZW4gKCgocmVmXzAuYzEwIGxpa2UgJ3p+JScpDQogICAg ICAgICAgICAgICAgICAgICAgICBhbmQgKG5vdCAocmVmXzAuYzEwIGxpa2UgJ3p+JScpKSkNCiAg ICAgICAgICAgICAgICAgICAgICAgIGFuZCAoKHJlZl8wLmMxMCBsaWtlICd6fiUnKSBpcyBub3Qg bnVsbCkpIHRoZW4KdDUuYzI4IGVsc2UgdDUuYzI4IGVuZCkNCiAgICAgICAgICAgPSAoY2FzZSB3 aGVuICgoKHJlZl8xLmMyIG5vdCBsaWtlICdfJSUnKQ0KICAgICAgICAgICAgICAgICAgICAgICAg YW5kIChub3QgKHJlZl8xLmMyIG5vdCBsaWtlICdfJSUnKSkpDQogICAgICAgICAgICAgICAgICAg ICAgICBhbmQgKChyZWZfMS5jMiBub3QgbGlrZSAnXyUlJykgaXMgbm90IG51bGwpKSB0aGVuCnQ1 LmMyOSBlbHNlIHQ1LmMyNyBlbmQpKQ0KICAgICAgb3JkZXIgYnkgY18wIGRlc2MgbGltaXQgMSkp Ow0KLS0tDQoNCkJlY2F1c2UgdGhlIHRoZW4gYnJhbmNoIGFuZCBlbHNlIGJyYW5jaCBvZiB0aGUg Q0FTRSBXSEVOIGV4cHJlc3Npb24gJygoY2FzZQp3aGVuICgoKHJlZl8wLmMxMCBsaWtlICd6fiUn KSBhbmQgKG5vdCAocmVmXzAuYzEwIGxpa2UgJ3p+JScpKSkgYW5kCigocmVmXzAuYzEwIGxpa2Ug J3p+JScpIGlzIG5vdCBudWxsKSkgdGhlbiB0NS5jMjggZWxzZSB0NS5jMjggZW5kKScgYXJlIHRo ZQpzYW1lIChib3RoIGFyZSB0NS5jMjgpLCBJIHNpbXBsaWZ5IHRoaXMgQ0FTRSBXSEVOIGV4cHJl c3Npb24gYnkgcmVwbGFjaW5nIGl0CndpdGggdDUuYzI4LCBhbmQgZ2V0IFRlc3QgY2FzZSAyOg0K DQotLS0gVGVzdCBjYXNlIDIgLS0tDQpzZWxlY3QgKiBmcm9tIHQ1DQp3aGVyZSAodDUucGtleSA+ PSB0NS52a2V5KSA8PiAodDUuYzMwID0gKA0KICAgIHNlbGVjdA0KICAgICAgICB0NS5jMjkgYXMg Y18wDQogICAgICBmcm9tDQogICAgICAgICh0MiBhcyByZWZfMA0KICAgICAgICAgIGlubmVyIGpv aW4gdDAgYXMgcmVmXzENCiAgICAgICAgICBvbiAocmVmXzAuYzEwID0gcmVmXzEuYzIpKQ0KICAg ICAgd2hlcmUgKHQ1LmMyOA0KICAgICAgICAgICA9IChjYXNlIHdoZW4gKCgocmVmXzEuYzIgbm90 IGxpa2UgJ18lJScpDQogICAgICAgICAgICAgICAgICAgICAgICBhbmQgKG5vdCAocmVmXzEuYzIg bm90IGxpa2UgJ18lJScpKSkNCiAgICAgICAgICAgICAgICAgICAgICAgIGFuZCAoKHJlZl8xLmMy IG5vdCBsaWtlICdfJSUnKSBpcyBub3QgbnVsbCkpIHRoZW4KdDUuYzI5IGVsc2UgdDUuYzI3IGVu ZCkpDQogICAgICBvcmRlciBieSBjXzAgZGVzYyBsaW1pdCAxKSk7DQotLS0NCg0KLS0tIEV4cGVj dGVkIGJlaGF2aW9yIC0tLQ0KVGVzdCBjYXNlIDEgYW5kIFRlc3QgY2FzZSAyIHJldHVybiB0aGUg c2FtZSByZXN1bHRzLg0KDQotLS0gQWN0dWFsIGJlaGF2aW9yIC0tLQ0KVGVzdCBjYXNlIDEgcmV0 dXJucyAwIHJvd3MsIHdoaWxlIFRlc3QgY2FzZSAyIHJldHVybnMgMSByb3cuDQoNCk91dHB1dCBv ZiBUZXN0IGNhc2UgMToNCiB2a2V5IHwgcGtleSB8IGMyNyB8IGMyOCB8IGMyOSB8IGMzMCANCi0t LS0tLSstLS0tLS0rLS0tLS0rLS0tLS0rLS0tLS0rLS0tLS0NCigwIHJvd3MpDQoNCk91dHB1dCBv ZiBUZXN0IGNhc2UgMjoNCiB2a2V5IHwgcGtleSB8IGMyNyB8IGMyOCB8IGMyOSB8IGMzMCANCi0t LS0tLSstLS0tLS0rLS0tLS0rLS0tLS0rLS0tLS0rLS0tLS0NCiAgICAwIHwgICAgMSB8ICAgICB8 ICAgICB8IGEgICB8IEwNCigxIHJvdykNCg0KLS0tIFBvc3RncmVzIHZlcnNpb24gLS0tDQpHaXRo dWIgY29tbWl0OiBlZmViMTJlZjBiZmVmMGI1YWE5NjZhNTZiYjRkYmIxZjkzNmJkYTBjDQpWZXJz aW9uOiBQb3N0Z3JlU1FMIDE2YmV0YTEgb24geDg2XzY0LXBjLWxpbnV4LWdudSwgY29tcGlsZWQg YnkgZ2NjIChVYnVudHUKOS40LjAtMXVidW50dTF+MjAuMDQuMSkgOS40LjAsIDY0LWJpdA0KDQot LS0gUGxhdGZvcm0gaW5mb3JtYXRpb24gLS0tDQpQbGF0Zm9ybTogVWJ1bnR1IDIwLjA0DQpLZXJu ZWw6IExpbnV4IDUuNC4wLTE0Ny1nZW5lcmljCgo= --===============7120393749232771720==--