MIME-Version: 1.0
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 3 Dec 2024 17:56:17 +0900
Message-ID: <CAMbWs4-nQF9io2WL2SkD0eXvfPdyBc9Q=hRwfQHCGV2usa0jyA@mail.gmail.com>
Subject: Wrong results with right-semi-joins
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000003cafaf062859d7ee"
Archived-At: <https://www.postgresql.org/message-id/CAMbWs4-nQF9io2WL2SkD0eXvfPdyBc9Q%3DhRwfQHCGV2usa0jyA%40mail.gmail.com>
Precedence: bulk

--0000000000003cafaf062859d7ee
Content-Type: text/plain; charset="UTF-8"

I ran into $subject and it can be reproduced with the query below.

create temp table tbl_rs(a int, b int);
insert into tbl_rs select i, i from generate_series(1,10)i;
analyze tbl_rs;

set enable_nestloop to off;
set enable_hashagg to off;

select * from tbl_rs t1
where (select a from tbl_rs t2
       where exists (select 1 from
                     (select (b in (select b from tbl_rs t3)) as c
                      from tbl_rs t4 where t4.a = 1) s
                     where c in
                      (select t1.a = 1 from tbl_rs t5 union all select true))
       order by a limit 1) >= 0;
 a | b
---+---
 1 | 1
(1 row)

The expected output should be 10 rows, not 1.

I've traced the root cause to ExecReScanHashJoin, where we neglect to
reset the inner-tuple match flags in the hash table for right-semi
joins when reusing the hash table.  It was my oversight in commit
aa86129e1.  Attached is patch to fix it.

Thanks
Richard

--0000000000003cafaf062859d7ee
Content-Type: application/octet-stream; 
	name="v1-0001-Fix-right-semi-joins-in-HashJoin-rescans.patch"
Content-Disposition: attachment; 
	filename="v1-0001-Fix-right-semi-joins-in-HashJoin-rescans.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m4885a630>
X-Attachment-Id: f_m4885a630

RnJvbSA5ZTI4ODNlYWUxYjg4ZjMxOTlhMjdiYTRlOTA4NTc5ODhmZjYyZDJiIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBSaWNoYXJkIEd1byA8Z3VvZmVuZ2xpbnV4QGdtYWlsLmNvbT4K
RGF0ZTogVHVlLCAzIERlYyAyMDI0IDE3OjI3OjM1ICswOTAwClN1YmplY3Q6IFtQQVRDSCB2MV0g
Rml4IHJpZ2h0LXNlbWktam9pbnMgaW4gSGFzaEpvaW4gcmVzY2FucwoKV2hlbiByZXNldHRpbmcg
YSBIYXNoSm9pbiBub2RlIGZvciByZXNjYW5zLCBpZiBpdCBpcyBhIHNpbmdsZS1iYXRjaApqb2lu
IGFuZCB0aGVyZSBhcmUgbm8gcGFyYW1ldGVyIGNoYW5nZXMgZm9yIHRoZSBpbm5lciBzdWJub2Rl
LCB3ZSBjYW4KanVzdCByZXVzZSB0aGUgZXhpc3RpbmcgaGFzaCB0YWJsZSB3aXRob3V0IHJlYnVp
bGRpbmcgaXQuICBIb3dldmVyLApmb3Igam9pbiB0eXBlcyB0aGF0IGRlcGVuZCBvbiB0aGUgaW5u
ZXItdHVwbGUgbWF0Y2ggZmxhZ3MgaW4gdGhlIGhhc2gKdGFibGUsIHdlIG5lZWQgdG8gcmVzZXQg
dGhlc2UgbWF0Y2ggZmxhZ3MgdG8gYXZvaWQgaW5jb3JyZWN0IHJlc3VsdHMuClRoaXMgYXBwbGll
cyB0byByaWdodCwgcmlnaHQtYW50aSwgcmlnaHQtc2VtaSwgYW5kIGZ1bGwgam9pbnMuCgpXaGVu
IEkgaW50cm9kdWNlZCAiUmlnaHQgU2VtaSBKb2luIiBwbGFuIHNoYXBlcyBpbiBhYTg2MTI5ZTEs
IEkgZmFpbGVkCnRvIHJlc2V0IHRoZSBtYXRjaCBmbGFncyBpbiB0aGUgaGFzaCB0YWJsZSBmb3Ig
cmlnaHQtc2VtaSBqb2lucyBpbgpyZXNjYW5zLiAgVGhpcyBvdmVyc2lnaHQgaGFzIGJlZW4gc2hv
d24gdG8gcHJvZHVjZSBpbmNvcnJlY3QgcmVzdWx0cy4KVGhpcyBwYXRjaCBmaXhlcyBpdC4KLS0t
CiBzcmMvYmFja2VuZC9leGVjdXRvci9ub2RlSGFzaGpvaW4uYyB8ICA3ICsrLS0KIHNyYy90ZXN0
L3JlZ3Jlc3MvZXhwZWN0ZWQvam9pbi5vdXQgIHwgNjMgKysrKysrKysrKysrKysrKysrKysrKysr
KysrKysKIHNyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2pvaW4uc3FsICAgICAgIHwgMzAgKysrKysrKysr
KysrKysKIDMgZmlsZXMgY2hhbmdlZCwgOTcgaW5zZXJ0aW9ucygrKSwgMyBkZWxldGlvbnMoLSkK
CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlSGFzaGpvaW4uYyBiL3NyYy9i
YWNrZW5kL2V4ZWN1dG9yL25vZGVIYXNoam9pbi5jCmluZGV4IDZjMzAwOWZiYTAuLmVhMDA0NWJj
MGYgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVIYXNoam9pbi5jCisrKyBi
L3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVIYXNoam9pbi5jCkBAIC0xNTExLDEwICsxNTExLDEx
IEBAIEV4ZWNSZVNjYW5IYXNoSm9pbihIYXNoSm9pblN0YXRlICpub2RlKQogCQkJLyoKIAkJCSAq
IE9rYXkgdG8gcmV1c2UgdGhlIGhhc2ggdGFibGU7IG5lZWRuJ3QgcmVzY2FuIGlubmVyLCBlaXRo
ZXIuCiAJCQkgKgotCQkJICogSG93ZXZlciwgaWYgaXQncyBhIHJpZ2h0L3JpZ2h0LWFudGkvZnVs
bCBqb2luLCB3ZSdkIGJldHRlcgotCQkJICogcmVzZXQgdGhlIGlubmVyLXR1cGxlIG1hdGNoIGZs
YWdzIGNvbnRhaW5lZCBpbiB0aGUgdGFibGUuCisJCQkgKiBIb3dldmVyLCBpZiBpdCdzIGEgcmln
aHQvcmlnaHQtYW50aS9yaWdodC1zZW1pL2Z1bGwgam9pbiwgd2UnZAorCQkJICogYmV0dGVyIHJl
c2V0IHRoZSBpbm5lci10dXBsZSBtYXRjaCBmbGFncyBjb250YWluZWQgaW4gdGhlCisJCQkgKiB0
YWJsZS4KIAkJCSAqLwotCQkJaWYgKEhKX0ZJTExfSU5ORVIobm9kZSkpCisJCQlpZiAoSEpfRklM
TF9JTk5FUihub2RlKSB8fCBub2RlLT5qcy5qb2ludHlwZSA9PSBKT0lOX1JJR0hUX1NFTUkpCiAJ
CQkJRXhlY0hhc2hUYWJsZVJlc2V0TWF0Y2hGbGFncyhub2RlLT5oal9IYXNoVGFibGUpOwogCiAJ
CQkvKgpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9qb2luLm91dCBiL3Ny
Yy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvam9pbi5vdXQKaW5kZXggZWJmMmUzZjg1MS4uNTFhZWIx
ZGFlNiAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9qb2luLm91dAorKysg
Yi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2pvaW4ub3V0CkBAIC0zMDM2LDYgKzMwMzYsNjkg
QEAgd2hlcmUgbm90IGV4aXN0cyAoc2VsZWN0IDEgZnJvbSB0YmxfcmEgdDIgd2hlcmUgdDIuYiA9
IHQxLmEpIGFuZCB0MS5iIDwgMjsKIHJlc2V0IGVuYWJsZV9oYXNoam9pbjsKIHJlc2V0IGVuYWJs
ZV9uZXN0bG9vcDsKIC0tCistLSByZWdyZXNzaW9uIHRlc3QgZm9yIGJ1ZyB3aXRoIGhhc2gtcmln
aHQtc2VtaSBqb2luCistLQorY3JlYXRlIHRlbXAgdGFibGUgdGJsX3JzKGEgaW50LCBiIGludCk7
CitpbnNlcnQgaW50byB0YmxfcnMgc2VsZWN0IGksIGkgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwx
MClpOworYW5hbHl6ZSB0YmxfcnM7CitzZXQgZW5hYmxlX25lc3Rsb29wIHRvIG9mZjsKK3NldCBl
bmFibGVfaGFzaGFnZyB0byBvZmY7CistLSBlbnN1cmUgd2UgZ2V0IGEgaGFzaCByaWdodCBzZW1p
IGpvaW4gd2l0aCBTdWJQbGFuIGluIGhhc2ggY2xhdXNlcworZXhwbGFpbiAoY29zdHMgb2ZmKQor
c2VsZWN0ICogZnJvbSB0YmxfcnMgdDEKK3doZXJlIChzZWxlY3QgYSBmcm9tIHRibF9ycyB0Mgor
ICAgICAgIHdoZXJlIGV4aXN0cyAoc2VsZWN0IDEgZnJvbQorICAgICAgICAgICAgICAgICAgICAg
KHNlbGVjdCAoYiBpbiAoc2VsZWN0IGIgZnJvbSB0YmxfcnMgdDMpKSBhcyBjIGZyb20gdGJsX3Jz
IHQ0IHdoZXJlIHQ0LmEgPSAxKSBzCisgICAgICAgICAgICAgICAgICAgICB3aGVyZSBjIGluIChz
ZWxlY3QgdDEuYSA9IDEgZnJvbSB0YmxfcnMgdDUgdW5pb24gYWxsIHNlbGVjdCB0cnVlKSkKKyAg
ICAgICBvcmRlciBieSBhIGxpbWl0IDEpID49IDA7CisgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgIFFVRVJZIFBMQU4gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgIAorLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KKyBTZXEgU2NhbiBvbiB0YmxfcnMg
dDEKKyAgIEZpbHRlcjogKChTdWJQbGFuIDMpID49IDApCisgICBTdWJQbGFuIDMKKyAgICAgLT4g
IExpbWl0CisgICAgICAgICAgIEluaXRQbGFuIDIKKyAgICAgICAgICAgICAtPiAgSGFzaCBSaWdo
dCBTZW1pIEpvaW4KKyAgICAgICAgICAgICAgICAgICBIYXNoIENvbmQ6ICgoKHQxLmEgPSAxKSkg
PSAoQU5ZICh0NC5iID0gKGhhc2hlZCBTdWJQbGFuIDEpLmNvbDEpKSkKKyAgICAgICAgICAgICAg
ICAgICAtPiAgQXBwZW5kCisgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9u
IHRibF9ycyB0NQorICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBSZXN1bHQKKyAgICAgICAg
ICAgICAgICAgICAtPiAgSGFzaAorICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2Nh
biBvbiB0YmxfcnMgdDQKKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6IChh
ID0gMSkKKyAgICAgICAgICAgICAgICAgICAgICAgICBTdWJQbGFuIDEKKyAgICAgICAgICAgICAg
ICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiB0YmxfcnMgdDMKKyAgICAgICAgICAgLT4gIFNv
cnQKKyAgICAgICAgICAgICAgICAgU29ydCBLZXk6IHQyLmEKKyAgICAgICAgICAgICAgICAgLT4g
IFJlc3VsdAorICAgICAgICAgICAgICAgICAgICAgICBPbmUtVGltZSBGaWx0ZXI6IChJbml0UGxh
biAyKS5jb2wxCisgICAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiB0YmxfcnMg
dDIKKygyMCByb3dzKQorCistLSBhbmQgY2hlY2sgd2UgZ2V0IHRoZSBleHBlY3RlZCByZXN1bHRz
CitzZWxlY3QgKiBmcm9tIHRibF9ycyB0MQord2hlcmUgKHNlbGVjdCBhIGZyb20gdGJsX3JzIHQy
CisgICAgICAgd2hlcmUgZXhpc3RzIChzZWxlY3QgMSBmcm9tCisgICAgICAgICAgICAgICAgICAg
ICAoc2VsZWN0IChiIGluIChzZWxlY3QgYiBmcm9tIHRibF9ycyB0MykpIGFzIGMgZnJvbSB0Ymxf
cnMgdDQgd2hlcmUgdDQuYSA9IDEpIHMKKyAgICAgICAgICAgICAgICAgICAgIHdoZXJlIGMgaW4g
KHNlbGVjdCB0MS5hID0gMSBmcm9tIHRibF9ycyB0NSB1bmlvbiBhbGwgc2VsZWN0IHRydWUpKQor
ICAgICAgIG9yZGVyIGJ5IGEgbGltaXQgMSkgPj0gMDsKKyBhICB8IGIgIAorLS0tLSstLS0tCisg
IDEgfCAgMQorICAyIHwgIDIKKyAgMyB8ICAzCisgIDQgfCAgNAorICA1IHwgIDUKKyAgNiB8ICA2
CisgIDcgfCAgNworICA4IHwgIDgKKyAgOSB8ICA5CisgMTAgfCAxMAorKDEwIHJvd3MpCisKK3Jl
c2V0IGVuYWJsZV9uZXN0bG9vcDsKK3Jlc2V0IGVuYWJsZV9oYXNoYWdnOworLS0KIC0tIHJlZ3Jl
c3Npb24gdGVzdCBmb3IgYnVnICMxMzkwOCAoaGFzaCBqb2luIHdpdGggc2tldyB0dXBsZXMgJiBu
YmF0Y2ggaW5jcmVhc2UpCiAtLQogc2V0IHdvcmtfbWVtIHRvICc2NGtCJzsKZGlmZiAtLWdpdCBh
L3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2pvaW4uc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvam9p
bi5zcWwKaW5kZXggMTAwNGZjMDM1NS4uMWU5ZGFmY2E1NyAxMDA2NDQKLS0tIGEvc3JjL3Rlc3Qv
cmVncmVzcy9zcWwvam9pbi5zcWwKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvam9pbi5zcWwK
QEAgLTczOCw2ICs3MzgsMzYgQEAgd2hlcmUgbm90IGV4aXN0cyAoc2VsZWN0IDEgZnJvbSB0Ymxf
cmEgdDIgd2hlcmUgdDIuYiA9IHQxLmEpIGFuZCB0MS5iIDwgMjsKIHJlc2V0IGVuYWJsZV9oYXNo
am9pbjsKIHJlc2V0IGVuYWJsZV9uZXN0bG9vcDsKIAorLS0KKy0tIHJlZ3Jlc3Npb24gdGVzdCBm
b3IgYnVnIHdpdGggaGFzaC1yaWdodC1zZW1pIGpvaW4KKy0tCitjcmVhdGUgdGVtcCB0YWJsZSB0
YmxfcnMoYSBpbnQsIGIgaW50KTsKK2luc2VydCBpbnRvIHRibF9ycyBzZWxlY3QgaSwgaSBmcm9t
IGdlbmVyYXRlX3NlcmllcygxLDEwKWk7CithbmFseXplIHRibF9yczsKKworc2V0IGVuYWJsZV9u
ZXN0bG9vcCB0byBvZmY7CitzZXQgZW5hYmxlX2hhc2hhZ2cgdG8gb2ZmOworCistLSBlbnN1cmUg
d2UgZ2V0IGEgaGFzaCByaWdodCBzZW1pIGpvaW4gd2l0aCBTdWJQbGFuIGluIGhhc2ggY2xhdXNl
cworZXhwbGFpbiAoY29zdHMgb2ZmKQorc2VsZWN0ICogZnJvbSB0YmxfcnMgdDEKK3doZXJlIChz
ZWxlY3QgYSBmcm9tIHRibF9ycyB0MgorICAgICAgIHdoZXJlIGV4aXN0cyAoc2VsZWN0IDEgZnJv
bQorICAgICAgICAgICAgICAgICAgICAgKHNlbGVjdCAoYiBpbiAoc2VsZWN0IGIgZnJvbSB0Ymxf
cnMgdDMpKSBhcyBjIGZyb20gdGJsX3JzIHQ0IHdoZXJlIHQ0LmEgPSAxKSBzCisgICAgICAgICAg
ICAgICAgICAgICB3aGVyZSBjIGluIChzZWxlY3QgdDEuYSA9IDEgZnJvbSB0YmxfcnMgdDUgdW5p
b24gYWxsIHNlbGVjdCB0cnVlKSkKKyAgICAgICBvcmRlciBieSBhIGxpbWl0IDEpID49IDA7CisK
Ky0tIGFuZCBjaGVjayB3ZSBnZXQgdGhlIGV4cGVjdGVkIHJlc3VsdHMKK3NlbGVjdCAqIGZyb20g
dGJsX3JzIHQxCit3aGVyZSAoc2VsZWN0IGEgZnJvbSB0YmxfcnMgdDIKKyAgICAgICB3aGVyZSBl
eGlzdHMgKHNlbGVjdCAxIGZyb20KKyAgICAgICAgICAgICAgICAgICAgIChzZWxlY3QgKGIgaW4g
KHNlbGVjdCBiIGZyb20gdGJsX3JzIHQzKSkgYXMgYyBmcm9tIHRibF9ycyB0NCB3aGVyZSB0NC5h
ID0gMSkgcworICAgICAgICAgICAgICAgICAgICAgd2hlcmUgYyBpbiAoc2VsZWN0IHQxLmEgPSAx
IGZyb20gdGJsX3JzIHQ1IHVuaW9uIGFsbCBzZWxlY3QgdHJ1ZSkpCisgICAgICAgb3JkZXIgYnkg
YSBsaW1pdCAxKSA+PSAwOworCityZXNldCBlbmFibGVfbmVzdGxvb3A7CityZXNldCBlbmFibGVf
aGFzaGFnZzsKKwogLS0KIC0tIHJlZ3Jlc3Npb24gdGVzdCBmb3IgYnVnICMxMzkwOCAoaGFzaCBq
b2luIHdpdGggc2tldyB0dXBsZXMgJiBuYmF0Y2ggaW5jcmVhc2UpCiAtLQotLSAKMi40My4wCgo=
--0000000000003cafaf062859d7ee--