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 1lXLLS-0004Ga-6t for pgsql-bugs@arkaria.postgresql.org; Fri, 16 Apr 2021 10:05:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lXLLQ-0006kQ-OV for pgsql-bugs@arkaria.postgresql.org; Fri, 16 Apr 2021 10:05:00 +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 1lX9JR-0001rX-5w for pgsql-bugs@lists.postgresql.org; Thu, 15 Apr 2021 21:14:09 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lX9JO-0007yG-5B for pgsql-bugs@lists.postgresql.org; Thu, 15 Apr 2021 21:14:08 +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=RJ8GJXwyIx5NVsfn2KeZuKA4Nrw3YZzUapeudn1pvUM=; b=TmJUIzJNUxWgFSxiecJzH7NDil HLyqGXpl/GcSRwaQJlQiBTVsS2IMPXoGSrWb7k4LXJHolJft+QKR4jKJByxLsNjdHRxFgpAPI8Jps q1F0gbtbgJ8R9dFnB86BYdYFBSCrQQ0RxkmPTVWMG429nJDujF41t0+L2FgQHbtLn22+eJKehd7I2 g143lmYr02Y6jwGzJ6mNPoUaGOP/qW2TwXj/NUVWJXnr3Q56SmewBLSb2dFXIZUOs0kW/0BkRCOqb jUNgUNhZQcsgs1w89S1swt8o65LCov0c3SUzk8lfz6R2xM+fFm4VauAXL0nGEgAtYV3rNEks/vnHz N6hBzq1g==; 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 1lX9JM-0007zh-Nc for pgsql-bugs@lists.postgresql.org; Thu, 15 Apr 2021 21:14:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lX9JK-0007WC-9M for pgsql-bugs@lists.postgresql.org; Thu, 15 Apr 2021 21:14:02 +0000 Content-Type: multipart/mixed; boundary="===============0922353892185469098==" MIME-Version: 1.0 Subject: BUG #16966: Nested loop joining across tables with varchar -> bpchar cast always scans varchar table first To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: zebburkeconte@gmail.com Reply-To: zebburkeconte@gmail.com, pgsql-bugs@lists.postgresql.org Date: Thu, 15 Apr 2021 21:13:03 +0000 Message-ID: <16966-f3ebf098ec2889b7@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 --===============0922353892185469098== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNjk2NgpMb2dnZWQgYnk6ICAgICAgICAgIFplYiBCdXJrZS1Db250ZQpF bWFpbCBhZGRyZXNzOiAgICAgIHplYmJ1cmtlY29udGVAZ21haWwuY29tClBvc3RncmVTUUwgdmVy c2lvbjogMTMuMgpPcGVyYXRpbmcgc3lzdGVtOiAgIFVidW50dSAyMC4wNCBMVFMKRGVzY3JpcHRp b246ICAgICAgICAKCkknbSBzZWVpbmcgYSBwZXJmb3JtYW5jZSBpc3N1ZSB3aGVuIGpvaW5pbmcg YWNyb3NzIHR3byB0YWJsZXMgb24gY29sdW1ucwp0aGF0IHJlcXVpcmUgYSBjYXN0IGZyb20gdmFy Y2hhciB0byBicGNoYXIuIE5vIG1hdHRlciBob3cgc2VsZWN0aXZlIHRoZQpjb25kaXRpb24gaXMg b24gdGhlIGJwY2hhciB0YWJsZSwgdGhlIG91dGVyIHNjYW4gd2lsbCBiZSBvbiB0aGUgdGFibGUg d2l0aAp0aGUgdmFyY2hhciBjb2x1bW4uIEl0J3MgcG9zc2libGUgdGhhdCB0aGUgaXNzdWUgY2Fz ZSBpcyBtb3JlIHNwZWNpZmljIHRoYW4KdGhhdCBidXQgeW91IGNhbiBzZWUgZm9yIHlvdXJzZWx2 ZXMgd2l0aCB0aGUgZXhhbXBsZSBiZWxvdyAod2hpY2ggaXMgcHJldHR5Cm1pbmltYWwpLiBJdCBl bmRzIHdpdGggdHdvIHF1ZXJpZXMgdGhhdCBzaG91bGQgYmUgcGxhbm5lZCBuZWFybHkKaWRlbnRp Y2FsbHksIGJ1dCBhcmVuJ3Q7IGFzIGEgcmVzdWx0LCBvbmUgaXMgMTAweCBzbG93ZXIuDQoNCk5v dGU6IFRoaXMgaXMgbm90IGFuIGlzc3VlIG9mIGluYWNjdXJhdGUgc3RhdGlzdGljcy9zZWxlY3Rp dml0eSBlc3RpbWF0ZXMuClRoZSBlc3RpbWF0ZXMgYXJlIHNwb3Qgb24sIGJ1dCB0aGUgbW9yZSBz ZWxlY3RpdmUgY29uZGl0aW9uIGlzIG5vdCBiZWluZwp1c2VkIGFzIHRoZSBvdXRlciBzY2FuLg0K DQpFeGFtcGxlIFNRTDoNCg0KZHJvcCB0YWJsZSBpZiBleGlzdHMgcHVibGljLmE7DQpkcm9wIHRh YmxlIGlmIGV4aXN0cyBwdWJsaWMuYjsNCg0KY3JlYXRlIHRhYmxlIHB1YmxpYy5iICgNCglpZCBi cGNoYXIoMTYpIG5vdCBudWxsLA0KCWNvbnN0cmFpbnQgYl9wayBwcmltYXJ5IGtleSAoaWQpDQop Ow0KDQpjcmVhdGUgdGFibGUgcHVibGljLmEgKA0KCWlkIHZhcmNoYXIgbm90IG51bGwsDQoJY29u c3RyYWludCBhX3BrIHByaW1hcnkga2V5IChpZCkNCik7DQoNCmluc2VydCBpbnRvIGENCgkoaWQp DQoJc2VsZWN0IChvbmVzLm4gKyAxMCp0ZW5zLm4gKyAxMDAqaHVuZHJlZHMubiArIDEwMDAqdGhv dXNhbmRzLm4gKwoxMDAwMCp0ZW5fdGhvdXNhbmRzLm4gKyAxMDAwMDAqaHVuZHJlZF90aG91c2Fu ZHMubik6OnZhcmNoYXINCglmcm9tICh2YWx1ZXMoMCksKDEpLCgyKSwoMyksKDQpLCg1KSwoNiks KDcpLCg4KSwoOSkpIG9uZXMobiksDQoJICAgICAodmFsdWVzKDApLCgxKSwoMiksKDMpLCg0KSwo NSksKDYpLCg3KSwoOCksKDkpKSB0ZW5zKG4pLA0KCSAgICAgKHZhbHVlcygwKSwoMSksKDIpLCgz KSwoNCksKDUpLCg2KSwoNyksKDgpLCg5KSkgaHVuZHJlZHMobiksDQoJICAgICAodmFsdWVzKDAp LCgxKSwoMiksKDMpLCg0KSwoNSksKDYpLCg3KSwoOCksKDkpKSB0aG91c2FuZHMobiksDQoJICAg ICAodmFsdWVzKDApLCgxKSwoMiksKDMpLCg0KSwoNSksKDYpLCg3KSwoOCksKDkpKSB0ZW5fdGhv dXNhbmRzKG4pLA0KCSAgICAgKHZhbHVlcygwKSwoMSksKDIpLCgzKSwoNCksKDUpLCg2KSwoNyks KDgpLCg5KSkgaHVuZHJlZF90aG91c2FuZHMobikNCglvcmRlciBieSAxOw0KDQppbnNlcnQgaW50 byBiDQoJKGlkKQ0KCXNlbGVjdCAob25lcy5uICsgMTAqdGVucy5uICsgMTAwKmh1bmRyZWRzLm4g KyAxMDAwKnRob3VzYW5kcy5uICsKMTAwMDAqdGVuX3Rob3VzYW5kcy5uICsgMTAwMDAwKmh1bmRy ZWRfdGhvdXNhbmRzLm4pOjp2YXJjaGFyDQoJZnJvbSAodmFsdWVzKDApLCgxKSwoMiksKDMpLCg0 KSwoNSksKDYpLCg3KSwoOCksKDkpKSBvbmVzKG4pLA0KCSAgICAgKHZhbHVlcygwKSwoMSksKDIp LCgzKSwoNCksKDUpLCg2KSwoNyksKDgpLCg5KSkgdGVucyhuKSwNCgkgICAgICh2YWx1ZXMoMCks KDEpLCgyKSwoMyksKDQpLCg1KSwoNiksKDcpLCg4KSwoOSkpIGh1bmRyZWRzKG4pLA0KCSAgICAg KHZhbHVlcygwKSwoMSksKDIpLCgzKSwoNCksKDUpLCg2KSwoNyksKDgpLCg5KSkgdGhvdXNhbmRz KG4pLA0KCSAgICAgKHZhbHVlcygwKSwoMSksKDIpLCgzKSwoNCksKDUpLCg2KSwoNyksKDgpLCg5 KSkgdGVuX3Rob3VzYW5kcyhuKSwNCgkgICAgICh2YWx1ZXMoMCksKDEpLCgyKSwoMyksKDQpLCg1 KSwoNiksKDcpLCg4KSwoOSkpIGh1bmRyZWRfdGhvdXNhbmRzKG4pDQoJb3JkZXIgYnkgMTsNCg0K YW5hbHl6ZSBhOw0KYW5hbHl6ZSBiOw0KDQpzZXQgZW5hYmxlX2hhc2hqb2luID0gb2ZmOw0Kc2V0 IGVuYWJsZV9tZXJnZWpvaW4gPSBvZmY7DQpzZXQgZW5hYmxlX3NlcXNjYW4gPSBvZmY7DQoNCmV4 cGxhaW4gYW5hbHl6ZSBzZWxlY3QgKiBmcm9tIGEgam9pbiBiIG9uIGEuaWQgPSBiLmlkIHdoZXJl IGEuaWQgaW4KKCcxMDkyNDQnLCAnNzIxMzQ1JywgJzg5MTEnKTsNCmV4cGxhaW4gYW5hbHl6ZSBz ZWxlY3QgKiBmcm9tIGEgam9pbiBiIG9uIGEuaWQgPSBiLmlkIHdoZXJlIGIuaWQgaW4KKCcxMDky NDQnLCAnNzIxMzQ1JywgJzg5MTEnKTsKCg== --===============0922353892185469098==--