From c914e74d2dee0ecf372c1d40f87499d94d591935 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 12 Aug 2019 13:15:48 -0400 Subject: [PATCH] Fix planner's test for case-foldable characters in ILIKE with ICU. As coded, the ICU-collation path in pattern_char_isalpha() failed to consider regular ASCII letters to be case-varying. This led to like_fixed_prefix treating too much of an ILIKE pattern as being a fixed prefix, so that indexscans derived from an ILIKE clause might miss entries that they should find. Per bug #15892 from James Inform. This is an oversight in the original ICU patch (commit eccfef81e), so back-patch to v10 where that came in. Discussion: https://postgr.es/m/15892-e5d2bea3e8a04a1b@postgresql.org --- src/backend/utils/adt/selfuncs.c | 10 +++--- .../regress/expected/collate.icu.utf8.out | 32 +++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 9 ++++++ 3 files changed, 47 insertions(+), 4 deletions(-) diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 8592e6cb87..7622edb9dd 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -5815,9 +5815,10 @@ find_join_input_rel(PlannerInfo *root, Relids relids) /* * Check whether char is a letter (and, hence, subject to case-folding) * - * In multibyte character sets or with ICU, we can't use isalpha, and it does not seem - * worth trying to convert to wchar_t to use iswalpha. Instead, just assume - * any multibyte char is potentially case-varying. + * In multibyte character sets or with ICU, we can't use isalpha, and it does + * not seem worth trying to convert to wchar_t to use iswalpha or u_isalpha. + * Instead, just assume any non-ASCII char is potentially case-varying, and + * hard-wire knowledge of which ASCII chars are letters. */ static int pattern_char_isalpha(char c, bool is_multibyte, @@ -5828,7 +5829,8 @@ pattern_char_isalpha(char c, bool is_multibyte, else if (is_multibyte && IS_HIGHBIT_SET(c)) return true; else if (locale && locale->provider == COLLPROVIDER_ICU) - return IS_HIGHBIT_SET(c) ? true : false; + return IS_HIGHBIT_SET(c) || + (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z'); #ifdef HAVE_LOCALE_T else if (locale && locale->provider == COLLPROVIDER_LIBC) return isalpha_l((unsigned char) c, locale->info.lt); diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index f485b5c330..1e9807f206 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -976,6 +976,38 @@ SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_t collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_tests.collate_test1 USING btree (((b || 'foo'::text)) COLLATE "POSIX") (4 rows) +set enable_seqscan = off; +explain (costs off) +select * from collate_test1 where b ilike 'abc'; + QUERY PLAN +------------------------------- + Seq Scan on collate_test1 + Filter: (b ~~* 'abc'::text) +(2 rows) + +select * from collate_test1 where b ilike 'abc'; + a | b +---+----- + 1 | abc + 4 | ABC +(2 rows) + +explain (costs off) +select * from collate_test1 where b ilike 'ABC'; + QUERY PLAN +------------------------------- + Seq Scan on collate_test1 + Filter: (b ~~* 'ABC'::text) +(2 rows) + +select * from collate_test1 where b ilike 'ABC'; + a | b +---+----- + 1 | abc + 4 | ABC +(2 rows) + +reset enable_seqscan; -- schema manipulation commands CREATE ROLE regress_test_role; CREATE SCHEMA test_schema; diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index ef39445b30..501b3c306f 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -333,6 +333,15 @@ CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "C")); -- fail SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx%' ORDER BY 1; +set enable_seqscan = off; +explain (costs off) +select * from collate_test1 where b ilike 'abc'; +select * from collate_test1 where b ilike 'abc'; +explain (costs off) +select * from collate_test1 where b ilike 'ABC'; +select * from collate_test1 where b ilike 'ABC'; +reset enable_seqscan; + -- schema manipulation commands -- 2.40.0