agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Use the join collation when unique-ifying a semijoin's RHS 7+ messages / 1 participants [nested] [flat]
* pgsql: Use the join collation when unique-ifying a semijoin's RHS @ 2026-09-16 10:09 Alexander Korotkov <akorotkov@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Alexander Korotkov @ 2026-09-16 10:09 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 ------ master Details ------- https://git.postgresql.org/pg/commitdiff/d237a7a83668bc228f7c58f104ffb25cbb607a1a 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Use the join collation when unique-ifying a semijoin's RHS @ 2026-09-16 10:10 Alexander Korotkov <akorotkov@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Alexander Korotkov @ 2026-09-16 10:10 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/0d5621890583bf64679ad4e4960c850732313472 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Use the join collation when unique-ifying a semijoin's RHS @ 2026-09-16 10:10 Alexander Korotkov <akorotkov@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Alexander Korotkov @ 2026-09-16 10:10 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/1ac292cb1436c788fb6ea29551b0fe459e2cb340 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Use the join collation when unique-ifying a semijoin's RHS @ 2026-09-16 10:10 Alexander Korotkov <akorotkov@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Alexander Korotkov @ 2026-09-16 10:10 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a10d14afca3601f519e8ba219627c8c0e9bf72c7 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Use the join collation when unique-ifying a semijoin's RHS @ 2026-09-16 10:10 Alexander Korotkov <akorotkov@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Alexander Korotkov @ 2026-09-16 10:10 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a9c9c4f76c78719c5692aefbd48c6c54eff36669 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Use the join collation when unique-ifying a semijoin's RHS @ 2026-09-16 10:10 Alexander Korotkov <akorotkov@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Alexander Korotkov @ 2026-09-16 10:10 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Use the join collation when unique-ifying a semijoin's RHS @ 2026-09-16 10:10 Alexander Korotkov <akorotkov@postgresql.org> 0 siblings, 0 replies; 7+ messages in thread From: Alexander Korotkov @ 2026-09-16 10:10 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/b8e0b45abb61ec42de9cb702041902d690629f6d 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-09-16 10:10 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-09-16 10:09 pgsql: Use the join collation when unique-ifying a semijoin's RHS Alexander Korotkov <akorotkov@postgresql.org> 2026-09-16 10:10 pgsql: Use the join collation when unique-ifying a semijoin's RHS Alexander Korotkov <akorotkov@postgresql.org> 2026-09-16 10:10 pgsql: Use the join collation when unique-ifying a semijoin's RHS Alexander Korotkov <akorotkov@postgresql.org> 2026-09-16 10:10 pgsql: Use the join collation when unique-ifying a semijoin's RHS Alexander Korotkov <akorotkov@postgresql.org> 2026-09-16 10:10 pgsql: Use the join collation when unique-ifying a semijoin's RHS Alexander Korotkov <akorotkov@postgresql.org> 2026-09-16 10:10 pgsql: Use the join collation when unique-ifying a semijoin's RHS Alexander Korotkov <akorotkov@postgresql.org> 2026-09-16 10:10 pgsql: Use the join collation when unique-ifying a semijoin's RHS Alexander Korotkov <akorotkov@postgresql.org>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox