Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc914e74

Browse files
committed
Fix planner's test for case-foldable characters in ILIKE with ICU.
As coded, the ICU-collation path in pattern_char_isalpha() failedto consider regular ASCII letters to be case-varying. This led tolike_fixed_prefix treating too much of an ILIKE pattern as being afixed prefix, so that indexscans derived from an ILIKE clause mightmiss entries that they should find.Per bug #15892 from James Inform. This is an oversight in the originalICU patch (commiteccfef8), so back-patch to v10 where that came in.Discussion:https://postgr.es/m/15892-e5d2bea3e8a04a1b@postgresql.org
1 parentceb850d commitc914e74

File tree

3 files changed

+47
-4
lines changed

3 files changed

+47
-4
lines changed

‎src/backend/utils/adt/selfuncs.c

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -5815,9 +5815,10 @@ find_join_input_rel(PlannerInfo *root, Relids relids)
58155815
/*
58165816
* Check whether char is a letter (and, hence, subject to case-folding)
58175817
*
5818-
* In multibyte character sets or with ICU, we can't use isalpha, and it does not seem
5819-
* worth trying to convert to wchar_t to use iswalpha. Instead, just assume
5820-
* any multibyte char is potentially case-varying.
5818+
* In multibyte character sets or with ICU, we can't use isalpha, and it does
5819+
* not seem worth trying to convert to wchar_t to use iswalpha or u_isalpha.
5820+
* Instead, just assume any non-ASCII char is potentially case-varying, and
5821+
* hard-wire knowledge of which ASCII chars are letters.
58215822
*/
58225823
staticint
58235824
pattern_char_isalpha(charc,boolis_multibyte,
@@ -5828,7 +5829,8 @@ pattern_char_isalpha(char c, bool is_multibyte,
58285829
elseif (is_multibyte&&IS_HIGHBIT_SET(c))
58295830
return true;
58305831
elseif (locale&&locale->provider==COLLPROVIDER_ICU)
5831-
returnIS_HIGHBIT_SET(c) ? true : false;
5832+
returnIS_HIGHBIT_SET(c)||
5833+
(c >='A'&&c <='Z')|| (c >='a'&&c <='z');
58325834
#ifdefHAVE_LOCALE_T
58335835
elseif (locale&&locale->provider==COLLPROVIDER_LIBC)
58345836
returnisalpha_l((unsignedchar)c,locale->info.lt);

‎src/test/regress/expected/collate.icu.utf8.out

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -976,6 +976,38 @@ SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_t
976976
collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_tests.collate_test1 USING btree (((b || 'foo'::text)) COLLATE "POSIX")
977977
(4 rows)
978978

979+
set enable_seqscan = off;
980+
explain (costs off)
981+
select * from collate_test1 where b ilike 'abc';
982+
QUERY PLAN
983+
-------------------------------
984+
Seq Scan on collate_test1
985+
Filter: (b ~~* 'abc'::text)
986+
(2 rows)
987+
988+
select * from collate_test1 where b ilike 'abc';
989+
a | b
990+
---+-----
991+
1 | abc
992+
4 | ABC
993+
(2 rows)
994+
995+
explain (costs off)
996+
select * from collate_test1 where b ilike 'ABC';
997+
QUERY PLAN
998+
-------------------------------
999+
Seq Scan on collate_test1
1000+
Filter: (b ~~* 'ABC'::text)
1001+
(2 rows)
1002+
1003+
select * from collate_test1 where b ilike 'ABC';
1004+
a | b
1005+
---+-----
1006+
1 | abc
1007+
4 | ABC
1008+
(2 rows)
1009+
1010+
reset enable_seqscan;
9791011
-- schema manipulation commands
9801012
CREATE ROLE regress_test_role;
9811013
CREATE SCHEMA test_schema;

‎src/test/regress/sql/collate.icu.utf8.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -333,6 +333,15 @@ CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "C")); -- fail
333333

334334
SELECT relname, pg_get_indexdef(oid)FROM pg_classWHERE relnameLIKE'collate_test%_idx%'ORDER BY1;
335335

336+
set enable_seqscan= off;
337+
explain (costs off)
338+
select*from collate_test1where b ilike'abc';
339+
select*from collate_test1where b ilike'abc';
340+
explain (costs off)
341+
select*from collate_test1where b ilike'ABC';
342+
select*from collate_test1where b ilike'ABC';
343+
reset enable_seqscan;
344+
336345

337346
-- schema manipulation commands
338347

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp