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

Commitc0c12ce

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 parentde0dc0b commitc0c12ce

File tree

3 files changed

+50
-5
lines changed

3 files changed

+50
-5
lines changed

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

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1437,8 +1437,9 @@ regex_selectivity(const char *patt, int pattlen, bool case_insensitive,
14371437
* Check whether char is a letter (and, hence, subject to case-folding)
14381438
*
14391439
* In multibyte character sets or with ICU, we can't use isalpha, and it does
1440-
* not seem worth trying to convert to wchar_t to use iswalpha. Instead, just
1441-
* assume any multibyte char is potentially case-varying.
1440+
* not seem worth trying to convert to wchar_t to use iswalpha or u_isalpha.
1441+
* Instead, just assume any non-ASCII char is potentially case-varying, and
1442+
* hard-wire knowledge of which ASCII chars are letters.
14421443
*/
14431444
staticint
14441445
pattern_char_isalpha(charc,boolis_multibyte,
@@ -1449,7 +1450,8 @@ pattern_char_isalpha(char c, bool is_multibyte,
14491450
elseif (is_multibyte&&IS_HIGHBIT_SET(c))
14501451
return true;
14511452
elseif (locale&&locale->provider==COLLPROVIDER_ICU)
1452-
returnIS_HIGHBIT_SET(c) ? true : false;
1453+
returnIS_HIGHBIT_SET(c)||
1454+
(c >='A'&&c <='Z')|| (c >='a'&&c <='z');
14531455
#ifdefHAVE_LOCALE_T
14541456
elseif (locale&&locale->provider==COLLPROVIDER_LIBC)
14551457
returnisalpha_l((unsignedchar)c,locale->info.lt);

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

Lines changed: 34 additions & 1 deletion
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;
@@ -1860,8 +1892,9 @@ SELECT (SELECT count(*) FROM test33_0) <> (SELECT count(*) FROM test33_1);
18601892
(1 row)
18611893

18621894
-- cleanup
1895+
RESET search_path;
18631896
SET client_min_messages TO warning;
18641897
DROP SCHEMA collate_tests CASCADE;
1865-
RESETsearch_path;
1898+
RESETclient_min_messages;
18661899
-- leave a collation for pg_upgrade test
18671900
CREATE COLLATION coll_icu_upgrade FROM "und-x-icu";

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

Lines changed: 11 additions & 1 deletion
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

@@ -704,9 +713,10 @@ SELECT (SELECT count(*) FROM test33_0) <> (SELECT count(*) FROM test33_1);
704713

705714

706715
-- cleanup
716+
RESET search_path;
707717
SET client_min_messages TO warning;
708718
DROPSCHEMA collate_tests CASCADE;
709-
RESETsearch_path;
719+
RESETclient_min_messages;
710720

711721
-- leave a collation for pg_upgrade test
712722
CREATE COLLATION coll_icu_upgradeFROM"und-x-icu";

0 commit comments

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