=== Applying patches on top of PostgreSQL commit ID 6827de95ee094f8a2cb892a40e6f154491e78496 === /etc/rc.d/jail: WARNING: Per-jail configuration via jail_* variables is obsolete. Please consider migrating to /etc/jail.conf. Thu May 7 07:28:20 UTC 2026 On branch cf/6722 nothing to commit, working tree clean === using 'git am' to apply patch ./v2-0001-Consider-collation-when-proving-uniqueness-from-u.patch === Applying: Consider collation when proving uniqueness from unique indexes Using index info to reconstruct a base tree... M src/backend/optimizer/path/indxpath.c M src/backend/utils/cache/lsyscache.c M src/include/utils/lsyscache.h M src/test/regress/expected/collate.icu.utf8.out M src/test/regress/sql/collate.icu.utf8.sql Falling back to patching base and 3-way merge... Auto-merging src/test/regress/sql/collate.icu.utf8.sql CONFLICT (content): Merge conflict in src/test/regress/sql/collate.icu.utf8.sql Auto-merging src/test/regress/expected/collate.icu.utf8.out CONFLICT (content): Merge conflict in src/test/regress/expected/collate.icu.utf8.out Auto-merging src/include/utils/lsyscache.h CONFLICT (content): Merge conflict in src/include/utils/lsyscache.h Auto-merging src/backend/utils/cache/lsyscache.c CONFLICT (content): Merge conflict in src/backend/utils/cache/lsyscache.c Auto-merging src/backend/optimizer/path/indxpath.c CONFLICT (content): Merge conflict in src/backend/optimizer/path/indxpath.c error: Failed to merge in the changes. hint: Use 'git am --show-current-patch=diff' to see the failed patch Patch failed at 0001 Consider collation when proving uniqueness from unique indexes When you have resolved this problem, run "git am --continue". If you prefer to skip this patch, run "git am --skip" instead. To restore the original branch and stop patching, run "git am --abort". === using patch(1) to apply patch ./v2-0001-Consider-collation-when-proving-uniqueness-from-u.patch === patching file src/backend/optimizer/path/indxpath.c Hunk #1 FAILED at 4226. 1 out of 1 hunk FAILED -- saving rejects to file src/backend/optimizer/path/indxpath.c.rej patching file src/backend/utils/cache/lsyscache.c Hunk #1 succeeded at 898 with fuzz 1 (offset 38 lines). patching file src/include/utils/lsyscache.h Hunk #1 FAILED at 89. 1 out of 1 hunk FAILED -- saving rejects to file src/include/utils/lsyscache.h.rej patching file src/test/regress/expected/collate.icu.utf8.out Hunk #1 succeeded at 1958 with fuzz 2 (offset 304 lines). patching file src/test/regress/sql/collate.icu.utf8.sql Hunk #1 succeeded at 715 with fuzz 2 (offset 103 lines). Unstaged changes after reset: M src/backend/utils/cache/lsyscache.c M src/test/regress/expected/collate.icu.utf8.out M src/test/regress/sql/collate.icu.utf8.sql Removing src/backend/optimizer/path/indxpath.c.rej Removing src/include/utils/lsyscache.h.rej === using 'git apply' to apply patch ./v2-0001-Consider-collation-when-proving-uniqueness-from-u.patch === Applied patch to 'src/backend/optimizer/path/indxpath.c' with conflicts. Applied patch to 'src/backend/utils/cache/lsyscache.c' with conflicts. Applied patch to 'src/include/utils/lsyscache.h' with conflicts. Applied patch to 'src/test/regress/expected/collate.icu.utf8.out' with conflicts. Applied patch to 'src/test/regress/sql/collate.icu.utf8.sql' with conflicts. U src/backend/optimizer/path/indxpath.c U src/backend/utils/cache/lsyscache.c U src/include/utils/lsyscache.h U src/test/regress/expected/collate.icu.utf8.out U src/test/regress/sql/collate.icu.utf8.sql diff --cc src/backend/optimizer/path/indxpath.c index 3f5d4fa3182,11d7ff5a4cb..00000000000 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@@ -4232,12 -4232,12 +4232,21 @@@ relation_has_unique_index_for(PlannerIn continue; /* ++<<<<<<< ours + * The index's collation must agree with the clause's input + * collation on equality, else the index's uniqueness does not + * imply uniqueness under the clause's equality semantics. + */ + if (!collations_agree_on_equality(ind->indexcollations[c], + exprInputCollation((Node *) rinfo->clause))) ++======= + * The index's collation must be compatible with the clause's + * input collation, else the index's uniqueness does not imply + * uniqueness under the clause's equality semantics. + */ + if (!collations_are_compatible(ind->indexcollations[c], + exprInputCollation((Node *) rinfo->clause))) ++>>>>>>> theirs continue; /* OK, see if the condition operand matches the index key */ diff --cc src/backend/utils/cache/lsyscache.c index 3de10d4df7e,df1d06dc7bd..00000000000 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@@ -861,19 -861,16 +861,31 @@@ comparison_ops_are_compatible(Oid opno1 } /* ++<<<<<<< ours + * collations_agree_on_equality ++======= + * collations_are_compatible ++>>>>>>> theirs * Return true if the two collations have equivalent notions of equality, * so that a uniqueness or equality proof established under one side * carries over to a comparison performed under the other side. * ++<<<<<<< ours + * Note: this is equality compatibility only. Do NOT use this to reason + * about ordering. + * + * An InvalidOid on either side denotes the absence of a collation -- that + * side's operation is not collation-sensitive (e.g. a non-collatable column + * type). Absence of a collation cannot conflict with the other side's + * collation, so we treat such pairs as agreeing on equality. This generalizes + * the asymmetric treatment in IndexCollMatchesExprColl(). ++======= + * An InvalidOid on either side denotes the absence of a collation -- that + * side's operation is not collation-sensitive (e.g. a non-collatable column + * type). Absence of a collation cannot conflict with the other side's + * collation, so we treat such pairs as compatible. This generalizes the + * asymmetric treatment in IndexCollMatchesExprColl(). ++>>>>>>> theirs * * Otherwise the collations have equivalent equality if they match, or if both * are deterministic: by definition a deterministic collation treats two @@@ -883,7 -880,7 +895,11 @@@ * relations disagree, and the proof is unsound. */ bool ++<<<<<<< ours +collations_agree_on_equality(Oid coll1, Oid coll2) ++======= + collations_are_compatible(Oid coll1, Oid coll2) ++>>>>>>> theirs { if (!OidIsValid(coll1) || !OidIsValid(coll2)) return true; diff --cc src/include/utils/lsyscache.h index 8d5e92e07be,7679512aaf8..00000000000 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@@ -89,7 -89,7 +89,11 @@@ extern bool get_op_hash_functions(Oid o extern List *get_op_index_interpretation(Oid opno); extern bool equality_ops_are_compatible(Oid opno1, Oid opno2); extern bool comparison_ops_are_compatible(Oid opno1, Oid opno2); ++<<<<<<< ours +extern bool collations_agree_on_equality(Oid coll1, Oid coll2); ++======= + extern bool collations_are_compatible(Oid coll1, Oid coll2); ++>>>>>>> theirs extern bool op_is_safe_index_member(Oid opno); extern Oid get_opfamily_proc(Oid opfamily, Oid lefttype, Oid righttype, int16 procnum); diff --cc src/test/regress/expected/collate.icu.utf8.out index 8c3a369e212,db87062b4a3..00000000000 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@@ -1777,187 -1777,6 +1777,190 @@@ ORDER BY 1 ghi (4 rows) ++<<<<<<< ours +-- +-- A DISTINCT / GROUP BY / set-op on a subquery does not prove uniqueness +-- under a different collation, so the planner must not use such a proof for +-- any optimization. +-- +-- Ensure that we do not use inner-unique join execution +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM test1cs t1, (SELECT DISTINCT x FROM test3cs) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + QUERY PLAN +--------------------------------------------------------------------------- + Sort + Output: t1.x, test3cs.x + Sort Key: t1.x COLLATE case_sensitive, test3cs.x COLLATE case_sensitive + -> Hash Join + Output: t1.x, test3cs.x + Hash Cond: ((t1.x)::text = (test3cs.x)::text) + -> Seq Scan on collate_tests.test1cs t1 + Output: t1.x + -> Hash + Output: test3cs.x + -> HashAggregate + Output: test3cs.x + Group Key: test3cs.x + -> Seq Scan on collate_tests.test3cs + Output: test3cs.x +(15 rows) + +SELECT * FROM test1cs t1, (SELECT DISTINCT x FROM test3cs) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + x | x +-----+----- + abc | abc + abc | ABC + ABC | abc + ABC | ABC + def | def + ghi | ghi +(6 rows) + +-- Same with GROUP BY +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM test1cs t1, (SELECT x FROM test3cs GROUP BY x) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + QUERY PLAN +--------------------------------------------------------------------------- + Sort + Output: t1.x, test3cs.x + Sort Key: t1.x COLLATE case_sensitive, test3cs.x COLLATE case_sensitive + -> Hash Join + Output: t1.x, test3cs.x + Hash Cond: ((t1.x)::text = (test3cs.x)::text) + -> Seq Scan on collate_tests.test1cs t1 + Output: t1.x + -> Hash + Output: test3cs.x + -> HashAggregate + Output: test3cs.x + Group Key: test3cs.x + -> Seq Scan on collate_tests.test3cs + Output: test3cs.x +(15 rows) + +SELECT * FROM test1cs t1, (SELECT x FROM test3cs GROUP BY x) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + x | x +-----+----- + abc | abc + abc | ABC + ABC | abc + ABC | ABC + def | def + ghi | ghi +(6 rows) + +-- Same with set-op +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM test1cs t1, (SELECT x FROM test3cs UNION SELECT x FROM test3cs) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + QUERY PLAN +--------------------------------------------------------------------------- + Sort + Output: t1.x, test3cs.x + Sort Key: t1.x COLLATE case_sensitive, test3cs.x COLLATE case_sensitive + -> Hash Join + Output: t1.x, test3cs.x + Hash Cond: ((test3cs.x)::text = (t1.x)::text) + -> HashAggregate + Output: test3cs.x + Group Key: test3cs.x + -> Append + -> Seq Scan on collate_tests.test3cs + Output: test3cs.x + -> Seq Scan on collate_tests.test3cs test3cs_1 + Output: test3cs_1.x + -> Hash + Output: t1.x + -> Seq Scan on collate_tests.test1cs t1 + Output: t1.x +(18 rows) + +SELECT * FROM test1cs t1, (SELECT x FROM test3cs UNION SELECT x FROM test3cs) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + x | x +-----+----- + abc | abc + abc | ABC + ABC | abc + ABC | ABC + def | def + ghi | ghi +(6 rows) + +-- Ensure that left-join is not removed +EXPLAIN (COSTS OFF) +SELECT t1.* FROM test3cs t1 + LEFT JOIN (SELECT DISTINCT x FROM test3cs) t2 ON t1.x = t2.x COLLATE case_insensitive +ORDER BY 1; + QUERY PLAN +----------------------------------------------- + Sort + Sort Key: t1.x COLLATE case_sensitive + -> Hash Left Join + Hash Cond: (t1.x = (test3cs.x)::text) + -> Seq Scan on test3cs t1 + -> Hash + -> HashAggregate + Group Key: test3cs.x + -> Seq Scan on test3cs +(9 rows) + +SELECT t1.* FROM test3cs t1 + LEFT JOIN (SELECT DISTINCT x FROM test3cs) t2 ON t1.x = t2.x COLLATE case_insensitive +ORDER BY 1; + x +----- + abc + abc + ABC + ABC + def + ghi +(6 rows) + +-- Ensure that semijoin is not reduced to innerjoin +EXPLAIN (COSTS OFF) +SELECT * FROM test3cs t1 + WHERE EXISTS (SELECT 1 FROM (SELECT DISTINCT x FROM test3cs) t2 + WHERE t1.x = t2.x COLLATE case_insensitive) +ORDER BY 1; + QUERY PLAN +------------------------------------------------------- + Sort + Sort Key: t1.x COLLATE case_sensitive + -> Hash Semi Join + Hash Cond: ((t1.x)::text = (test3cs.x)::text) + -> Seq Scan on test3cs t1 + -> Hash + -> HashAggregate + Group Key: test3cs.x + -> Seq Scan on test3cs +(9 rows) + +SELECT * FROM test3cs t1 + WHERE EXISTS (SELECT 1 FROM (SELECT DISTINCT x FROM test3cs) t2 + WHERE t1.x = t2.x COLLATE case_insensitive) +ORDER BY 1; + x +----- + abc + ABC + def + ghi +(4 rows) + ++======= ++>>>>>>> theirs CREATE TABLE test1ci (x text COLLATE case_insensitive); CREATE TABLE test2ci (x text COLLATE case_insensitive); CREATE TABLE test3ci (x text COLLATE case_insensitive); diff --cc src/test/regress/sql/collate.icu.utf8.sql index fdcdb2094f8,95c16859afa..00000000000 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@@ -657,64 -657,6 +657,67 @@@ SELECT * FROM test3cs t WHERE EXISTS (SELECT 1 FROM test3cs t2 WHERE t1.x = t2.x COLLATE case_insensitive) ORDER BY 1; ++<<<<<<< ours +-- +-- A DISTINCT / GROUP BY / set-op on a subquery does not prove uniqueness +-- under a different collation, so the planner must not use such a proof for +-- any optimization. +-- + +-- Ensure that we do not use inner-unique join execution +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM test1cs t1, (SELECT DISTINCT x FROM test3cs) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + +SELECT * FROM test1cs t1, (SELECT DISTINCT x FROM test3cs) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + +-- Same with GROUP BY +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM test1cs t1, (SELECT x FROM test3cs GROUP BY x) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + +SELECT * FROM test1cs t1, (SELECT x FROM test3cs GROUP BY x) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + +-- Same with set-op +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM test1cs t1, (SELECT x FROM test3cs UNION SELECT x FROM test3cs) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + +SELECT * FROM test1cs t1, (SELECT x FROM test3cs UNION SELECT x FROM test3cs) t2 +WHERE t1.x = t2.x COLLATE case_insensitive +ORDER BY 1, 2; + +-- Ensure that left-join is not removed +EXPLAIN (COSTS OFF) +SELECT t1.* FROM test3cs t1 + LEFT JOIN (SELECT DISTINCT x FROM test3cs) t2 ON t1.x = t2.x COLLATE case_insensitive +ORDER BY 1; + +SELECT t1.* FROM test3cs t1 + LEFT JOIN (SELECT DISTINCT x FROM test3cs) t2 ON t1.x = t2.x COLLATE case_insensitive +ORDER BY 1; + +-- Ensure that semijoin is not reduced to innerjoin +EXPLAIN (COSTS OFF) +SELECT * FROM test3cs t1 + WHERE EXISTS (SELECT 1 FROM (SELECT DISTINCT x FROM test3cs) t2 + WHERE t1.x = t2.x COLLATE case_insensitive) +ORDER BY 1; + +SELECT * FROM test3cs t1 + WHERE EXISTS (SELECT 1 FROM (SELECT DISTINCT x FROM test3cs) t2 + WHERE t1.x = t2.x COLLATE case_insensitive) +ORDER BY 1; + ++======= ++>>>>>>> theirs CREATE TABLE test1ci (x text COLLATE case_insensitive); CREATE TABLE test2ci (x text COLLATE case_insensitive); CREATE TABLE test3ci (x text COLLATE case_insensitive);