agora inbox for pgsql-committers@postgresql.org
help / color / mirror / Atom feedFrom: Alexander Korotkov <akorotkov@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Use the join collation when unique-ifying a semijoin's RHS
Date: Wed, 16 Sep 2026 10:10:43 +0000
Message-ID: <E1x6mbD-00000000ZXn-2PH0@gemulon.postgresql.org> (raw)
Use the join collation when unique-ifying a semijoin's RHS
A semijoin whose RHS is unique-ified groups the RHS on the expressions in
SpecialJoinInfo.semi_rhs_exprs. Those were recorded with whatever collation
the RHS expression itself exposes, which need not be the collation the join
compares with. Neither SortGroupClause nor the pathkey machinery carries a
collation of its own, so both Unique-over-Sort and HashAggregate then grouped
by the wrong equality: values the join considers equal survived, and the
following inner join emitted the outer row once per survivor.
With a non-deterministic collation on one side, "SELECT count(*) FROM t WHERE
c IN (SELECT c0 FROM t2)" therefore counted more rows than the same predicate
reports for the rows of t.
Label each RHS expression with the operator's input collation, the same
treatment process_equivalence() gives to equivalence class members. Every
consumer of semi_rhs_exprs reads the collation off the expression, so this
fixes the sort-based and hash-based paths together; in the branches where
create_unique_path() also passes these expressions to
relation_has_unique_index_for(), it likewise stops a unique index built with a
different collation from being taken as proof that unique-ification can be
skipped. The same goes for a subquery's DISTINCT computed under a different
collation, since translate_sub_tlist() punts on the relabeled expressions.
Reported-by: Suyang Zhong <syzhong16@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com>
Discussion: https://postgr.es/m/19633-647cd4c73a84b085%40postgresql.org
Backpatch-through: 14
Branch
------
REL_15_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/db0208bfcdcf499b3179d879e478e8779c00308a
Modified Files
--------------
src/backend/optimizer/plan/initsplan.c | 11 +++++-
src/test/regress/expected/collate.icu.utf8.out | 54 ++++++++++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 27 +++++++++++++
3 files changed, 91 insertions(+), 1 deletion(-)
view thread (7+ messages) latest in thread
Message-ID: <E1x6mbD-00000000ZXn-2PH0@gemulon.postgresql.org>
Permalink: ../E1x6mbD-00000000ZXn-2PH0@gemulon.postgresql.org/
Also on: postgresql.org/message-id/E1x6mbD-00000000ZXn-2PH0@gemulon.postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: akorotkov@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Use the join collation when unique-ifying a semijoin's RHS
In-Reply-To: <E1x6mbD-00000000ZXn-2PH0@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox