v2-0001-Treat-exact-match-LIKE-pattern-as-non-lossy-in-ce.patch

From d3b72b2b4b6a69545cfe33725c5c58063cfb85fc Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <postgres@jeltef.nl>
Date: Mon, 6 Jul 2026 22:08:02 +0200
Subject: [PATCH v2] Treat exact-match LIKE pattern as non-lossy in certain
 cases

---
 src/backend/utils/adt/like_support.c          | 109 +++++++++++++++---
 .../regress/expected/collate.icu.utf8.out     |  31 ++++-
 src/test/regress/expected/collate.out         |  31 ++++-
 src/test/regress/sql/collate.icu.utf8.sql     |  12 ++
 src/test/regress/sql/collate.sql              |  18 +++
 5 files changed, 180 insertions(+), 21 deletions(-)

diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/like_support.c
index 4c8db9147ee..c396b4f4724 100644
--- a/src/backend/utils/adt/like_support.c
+++ b/src/backend/utils/adt/like_support.c
@@ -66,7 +66,10 @@ typedef enum
 
 typedef enum
 {
-	Pattern_Prefix_None, Pattern_Prefix_Partial, Pattern_Prefix_Exact,
+	Pattern_Prefix_None,		/* cannot produce an indexqual */
+	Pattern_Prefix_Partial,		/* maps to an index range restriction */
+	Pattern_Prefix_Exact,		/* maps to a simple equality test */
+	Pattern_Prefix_Exact_Lossy, /* maps to an equality test, but recheck */
 } Pattern_Prefix_Status;
 
 /* non-collatable comparisons, eg for bytea, are always deterministic */
@@ -79,7 +82,8 @@ static List *match_pattern_prefix(Node *leftop,
 								  Pattern_Type ptype,
 								  Oid expr_coll,
 								  Oid opfamily,
-								  Oid indexcollation);
+								  Oid indexcollation,
+								  bool *lossy);
 static double patternsel_common(PlannerInfo *root,
 								Oid oprid,
 								Oid opfuncid,
@@ -215,7 +219,8 @@ like_regex_support(Node *rawreq, Pattern_Type ptype)
 									 ptype,
 									 clause->inputcollid,
 									 req->opfamily,
-									 req->indexcollation);
+									 req->indexcollation,
+									 &req->lossy);
 		}
 		else if (is_funcclause(req->node))	/* be paranoid */
 		{
@@ -228,7 +233,8 @@ like_regex_support(Node *rawreq, Pattern_Type ptype)
 									 ptype,
 									 clause->inputcollid,
 									 req->opfamily,
-									 req->indexcollation);
+									 req->indexcollation,
+									 &req->lossy);
 		}
 	}
 
@@ -238,6 +244,10 @@ like_regex_support(Node *rawreq, Pattern_Type ptype)
 /*
  * match_pattern_prefix
  *	  Try to generate an indexqual for a LIKE or regex operator.
+ *
+ * We return a list of indexqual expressions on success, or NIL on failure.
+ * On success, *lossy is changed to false (from its initial state of true)
+ * if the indexqual expression(s) can fully replace the operator.
  */
 static List *
 match_pattern_prefix(Node *leftop,
@@ -245,7 +255,8 @@ match_pattern_prefix(Node *leftop,
 					 Pattern_Type ptype,
 					 Oid expr_coll,
 					 Oid opfamily,
-					 Oid indexcollation)
+					 Oid indexcollation,
+					 bool *lossy)
 {
 	List	   *result;
 	Const	   *patt;
@@ -396,7 +407,8 @@ match_pattern_prefix(Node *leftop,
 	 * apply the LIKE/regex operator as a recheck, and that will filter out
 	 * any non-matching entries.
 	 */
-	if (pstatus == Pattern_Prefix_Exact)
+	if (pstatus == Pattern_Prefix_Exact ||
+		pstatus == Pattern_Prefix_Exact_Lossy)
 	{
 		if (!op_in_opfamily(eqopr, opfamily))
 			return NIL;
@@ -405,14 +417,51 @@ match_pattern_prefix(Node *leftop,
 		expr = make_opclause(eqopr, BOOLOID, false,
 							 (Expr *) leftop, (Expr *) prefix,
 							 InvalidOid, indexcollation);
+
+		/*
+		 * We can even drop the recheck (mark the indexqual non-lossy) when
+		 * "=" means exactly what the exact-match pattern means.  That
+		 * requires all of the following:
+		 *
+		 * - The pattern is Pattern_Prefix_Exact.
+		 *
+		 * - The left-hand type is not bpchar.  bpchar's "=" ignores trailing
+		 * blanks while LIKE does not, so "= 'abc'" matches a stored 'abc  '
+		 * that "LIKE 'abc'" rejects; the recheck is what removes it.
+		 *
+		 * - The index and expression collations agree on equality.  That
+		 * holds when they're the same collation, or (having passed the guard
+		 * above, which rejected a nondeterministic expression collation that
+		 * differs from the index's) when the index collation is
+		 * deterministic: a deterministic collation treats two strings as
+		 * equal iff they're bytewise equal, so it can't disagree with the
+		 * expression's equality. A nondeterministic index collation that
+		 * differs from the expression's makes "=" match a superset of the
+		 * pattern, so the recheck stays.
+		 *
+		 * - The expression actually has a collation whenever the index does.
+		 * A collatable expression with an indeterminate collation (e.g. a
+		 * concatenation of differently-collated columns) carries InvalidOid,
+		 * and the pattern match is supposed to fail at runtime with "could
+		 * not determine which collation to use".  Dropping the recheck would
+		 * instead let the "=" indexqual silently resolve the comparison under
+		 * the index's collation.  A genuinely non-collatable type (bytea)
+		 * also carries InvalidOid, but then the index collation is InvalidOid
+		 * too, so indexcollation == expr_coll and we still optimize.
+		 */
+		if (pstatus == Pattern_Prefix_Exact &&
+			ldatatype != BPCHAROID &&
+			(indexcollation == expr_coll ||
+			 (OidIsValid(expr_coll) && !NONDETERMINISTIC(indexcollation))))
+			*lossy = false;
 		result = list_make1(expr);
 		return result;
 	}
 
 	/*
-	 * Anything other than Pattern_Prefix_Exact is not supported if the
-	 * expression collation is nondeterministic.  The optimized equality or
-	 * prefix tests use bytewise comparisons, which is not consistent with
+	 * Anything other than Pattern_Prefix_Exact[_Lossy] is not supported if
+	 * the expression collation is nondeterministic.  The optimized equality
+	 * or prefix tests use bytewise comparisons, which is not consistent with
 	 * nondeterministic collations.
 	 */
 	if (NONDETERMINISTIC(expr_coll))
@@ -645,7 +694,8 @@ patternsel_common(PlannerInfo *root,
 		prefix->consttype = rdatatype;
 	}
 
-	if (pstatus == Pattern_Prefix_Exact)
+	if (pstatus == Pattern_Prefix_Exact ||
+		pstatus == Pattern_Prefix_Exact_Lossy)
 	{
 		/*
 		 * Pattern specifies an exact match, so estimate as for '='
@@ -985,7 +1035,7 @@ icnlikejoinsel(PG_FUNCTION_ARGS)
 /*
  * Extract the fixed prefix, if any, for a pattern.
  *
- * *prefix is set to a palloc'd prefix string (in the form of a Const node),
+ * *prefix_const is set to a palloc'd string (in the form of a Const node),
  *	or to NULL if no fixed prefix exists for the pattern.
  * If rest_selec is not NULL, *rest_selec is set to an estimate of the
  *	selectivity of the remainder of the pattern (without any fixed prefix).
@@ -1005,6 +1055,7 @@ like_fixed_prefix(Const *patt_const, Const **prefix_const,
 	Oid			typeid = patt_const->consttype;
 	int			pos,
 				match_pos;
+	bool		badpattern = false;
 
 	/* the right-hand const is type text or bytea */
 	Assert(typeid == BYTEAOID || typeid == TEXTOID);
@@ -1038,7 +1089,10 @@ like_fixed_prefix(Const *patt_const, Const **prefix_const,
 		{
 			pos++;
 			if (pos >= pattlen)
+			{
+				badpattern = true;
 				break;
+			}
 		}
 
 		match[match_pos++] = patt[pos];
@@ -1059,7 +1113,16 @@ like_fixed_prefix(Const *patt_const, Const **prefix_const,
 
 	/* in LIKE, an empty pattern is an exact match! */
 	if (pos == pattlen)
-		return Pattern_Prefix_Exact;	/* reached end of pattern, so exact */
+	{
+		/*
+		 * Reached end of pattern, so exact -- unless the operator would throw
+		 * an error at runtime.  In that case our representation is lossy,
+		 * since we don't want to throw that error here.
+		 */
+		if (badpattern)
+			return Pattern_Prefix_Exact_Lossy;
+		return Pattern_Prefix_Exact;
+	}
 
 	if (match_pos > 0)
 		return Pattern_Prefix_Partial;
@@ -1086,6 +1149,7 @@ like_fixed_prefix_ci(Const *patt_const, Oid collation, Const **prefix_const,
 	char	   *match;
 	int			match_mblen;
 	pg_locale_t locale = 0;
+	bool		badpattern = false;
 
 	/* the right-hand const is type text or bytea */
 	Assert(typeid == BYTEAOID || typeid == TEXTOID);
@@ -1125,7 +1189,10 @@ like_fixed_prefix_ci(Const *patt_const, Oid collation, Const **prefix_const,
 		{
 			wpos++;
 			if (wpos >= wpattlen)
+			{
+				badpattern = true;
 				break;
+			}
 		}
 
 		/*
@@ -1165,7 +1232,16 @@ like_fixed_prefix_ci(Const *patt_const, Oid collation, Const **prefix_const,
 
 	/* in LIKE, an empty pattern is an exact match! */
 	if (wpos == wpattlen)
-		return Pattern_Prefix_Exact;	/* reached end of pattern, so exact */
+	{
+		/*
+		 * Reached end of pattern, so exact -- unless the operator would throw
+		 * an error at runtime.  In that case our representation is lossy,
+		 * since we don't want to throw that error here.
+		 */
+		if (badpattern)
+			return Pattern_Prefix_Exact_Lossy;
+		return Pattern_Prefix_Exact;
+	}
 
 	if (wmatch_pos > 0)
 		return Pattern_Prefix_Partial;
@@ -1235,8 +1311,13 @@ regex_fixed_prefix(Const *patt_const, bool case_insensitive, Oid collation,
 
 	pfree(prefix);
 
+	/*
+	 * Because regexp_fixed_prefix() doesn't analyze the regexp fully, even an
+	 * "exact" match is lossy: we know that a match must be this string, but
+	 * it could still fail to match.
+	 */
 	if (exact)
-		return Pattern_Prefix_Exact;	/* pattern specifies exact match */
+		return Pattern_Prefix_Exact_Lossy;
 	else
 		return Pattern_Prefix_Partial;
 }
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index fcfcc658bea..3b5232df5de 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2128,12 +2128,11 @@ SELECT * FROM test1ci WHERE x ~ '^abc$' COLLATE "C";
 
 EXPLAIN (COSTS OFF)
 SELECT * FROM test1ci WHERE x LIKE 'abc' COLLATE case_insensitive;
-                      QUERY PLAN                       
--------------------------------------------------------
+                QUERY PLAN                 
+-------------------------------------------
  Index Scan using test1ci_x_idx on test1ci
    Index Cond: (x = 'abc'::text)
-   Filter: (x ~~ 'abc'::text COLLATE case_insensitive)
-(3 rows)
+(2 rows)
 
 RESET enable_seqscan;
 RESET enable_indexonlyscan;
@@ -2799,6 +2798,30 @@ SELECT x FROM test4c WHERE x LIKE 'ABC%' COLLATE case_insensitive;  -- ok
  abc
 (1 row)
 
+RESET enable_seqscan;
+-- For text (test4c above) an exact-match LIKE with the index and expression
+-- sharing the collation is non-lossy, so the recheck is dropped.  bpchar is
+-- different: its "=" ignores trailing blanks while LIKE does not, so the
+-- recheck must stay to reject a padded 'abc  ' that "=" would treat as equal
+-- to 'abc'.  The Index Cond therefore still carries a "~~" Filter here.
+SET enable_seqscan = off;
+EXPLAIN (COSTS OFF)
+SELECT x FROM test4c WHERE x LIKE 'abc' COLLATE case_insensitive;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Only Scan using test4c_x_idx on test4c
+   Index Cond: (x = 'abc'::text)
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT x FROM test1bpci WHERE x LIKE 'abc' COLLATE case_insensitive;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Index Only Scan using test1bpci_x_idx on test1bpci
+   Index Cond: (x = 'abc'::bpchar)
+   Filter: (x ~~ 'abc'::text COLLATE case_insensitive)
+(3 rows)
+
 RESET enable_seqscan;
 -- Unicode special case: different variants of Greek lower case sigma.
 -- A naive implementation like citext that just does lower(x) =
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index b17c5abaddc..77616fd297b 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -784,13 +784,38 @@ SELECT * FROM pg_class WHERE relname ~ '^pg_class$' COLLATE "POSIX";
 
 EXPLAIN (COSTS OFF)
 SELECT * FROM pg_class WHERE relname LIKE 'pg\_class' COLLATE "POSIX";
-                        QUERY PLAN                        
-----------------------------------------------------------
+                       QUERY PLAN                        
+---------------------------------------------------------
  Index Scan using pg_class_relname_nsp_index on pg_class
    Index Cond: (relname = 'pg_class'::text)
-   Filter: (relname ~~ 'pg\_class'::text COLLATE "POSIX")
+(2 rows)
+
+-- Conversely, when the expression's collation is indeterminate (here a
+-- concatenation of a "C" and a "POSIX" column) but the index pins a collation,
+-- the exact-match "=" indexqual must stay lossy so the LIKE recheck still runs.
+-- Otherwise the query would silently resolve the comparison under the index's
+-- collation instead of failing with "could not determine which collation to
+-- use", making the result depend on whether an index scan is chosen.
+CREATE INDEX collate_test10_expr_idx ON collate_test10 ((x || y) COLLATE "C");
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+EXPLAIN (COSTS OFF)
+SELECT a FROM collate_test10 WHERE (x || y) LIKE 'hijhij';
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Index Scan using collate_test10_expr_idx on collate_test10
+   Index Cond: ((x || y) = 'hijhij'::text)
+   Filter: ((x || y) ~~ 'hijhij'::text)
 (3 rows)
 
+-- must error (rather than silently return a row via the index) so that the
+-- outcome does not depend on the chosen plan
+SELECT a FROM collate_test10 WHERE (x || y) LIKE 'hijhij';
+ERROR:  could not determine which collation to use for LIKE
+HINT:  Use the COLLATE clause to set the collation explicitly.
+RESET enable_seqscan;
+RESET enable_bitmapscan;
+DROP INDEX collate_test10_expr_idx;
 --
 -- Clean up.  Many of these table names will be re-used if the user is
 -- trying to run any platform-specific collation tests later, so we
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index ce4e2bb3ffd..2660b1cb42d 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -999,6 +999,18 @@ SELECT x FROM test4c WHERE x LIKE 'ABC' COLLATE case_insensitive;  -- ok
 SELECT x FROM test4c WHERE x LIKE 'ABC%' COLLATE case_insensitive;  -- ok
 RESET enable_seqscan;
 
+-- For text (test4c above) an exact-match LIKE with the index and expression
+-- sharing the collation is non-lossy, so the recheck is dropped.  bpchar is
+-- different: its "=" ignores trailing blanks while LIKE does not, so the
+-- recheck must stay to reject a padded 'abc  ' that "=" would treat as equal
+-- to 'abc'.  The Index Cond therefore still carries a "~~" Filter here.
+SET enable_seqscan = off;
+EXPLAIN (COSTS OFF)
+SELECT x FROM test4c WHERE x LIKE 'abc' COLLATE case_insensitive;
+EXPLAIN (COSTS OFF)
+SELECT x FROM test1bpci WHERE x LIKE 'abc' COLLATE case_insensitive;
+RESET enable_seqscan;
+
 -- Unicode special case: different variants of Greek lower case sigma.
 -- A naive implementation like citext that just does lower(x) =
 -- lower(y) will do the wrong thing here, because lower('Σ') is 'σ'
diff --git a/src/test/regress/sql/collate.sql b/src/test/regress/sql/collate.sql
index b018da13f24..791e6af0a71 100644
--- a/src/test/regress/sql/collate.sql
+++ b/src/test/regress/sql/collate.sql
@@ -312,6 +312,24 @@ SELECT * FROM pg_class WHERE relname ~ '^pg_class$' COLLATE "POSIX";
 EXPLAIN (COSTS OFF)
 SELECT * FROM pg_class WHERE relname LIKE 'pg\_class' COLLATE "POSIX";
 
+-- Conversely, when the expression's collation is indeterminate (here a
+-- concatenation of a "C" and a "POSIX" column) but the index pins a collation,
+-- the exact-match "=" indexqual must stay lossy so the LIKE recheck still runs.
+-- Otherwise the query would silently resolve the comparison under the index's
+-- collation instead of failing with "could not determine which collation to
+-- use", making the result depend on whether an index scan is chosen.
+CREATE INDEX collate_test10_expr_idx ON collate_test10 ((x || y) COLLATE "C");
+SET enable_seqscan = off;
+SET enable_bitmapscan = off;
+EXPLAIN (COSTS OFF)
+SELECT a FROM collate_test10 WHERE (x || y) LIKE 'hijhij';
+-- must error (rather than silently return a row via the index) so that the
+-- outcome does not depend on the chosen plan
+SELECT a FROM collate_test10 WHERE (x || y) LIKE 'hijhij';
+RESET enable_seqscan;
+RESET enable_bitmapscan;
+DROP INDEX collate_test10_expr_idx;
+
 --
 -- Clean up.  Many of these table names will be re-used if the user is
 -- trying to run any platform-specific collation tests later, so we

base-commit: 4c84545067822bcc8697b7d8f3082c5cf1937d1b
-- 
2.54.0