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

Commitb3c265d

Browse files
committed
Fix corner-case failure in match_pattern_prefix().
The planner's optimization code for LIKE and regex operators coulderror out with a complaint like "no = operator for opfamily NNN"if someone created a binary-compatible index (for example, abpchar_ops index on a text column) on the LIKE's left argument.This is a consequence of careless refactoring in commit74dfe58.The old code in match_special_index_operator only accepted specificcombinations of the pattern operator and the index opclass, therebyindirectly guaranteeing that the opclass would have a comparisonoperator with the same LHS input type as the pattern operator.While moving the logic out to a planner support function, I simplifiedthat test in a way that no longer guarantees that. Really though we'dlike an altogether weaker dependency on the opclass, so rather thanput back exactly the old code, just allow lookup failure. I have inmind now to rewrite this logic completely, but this is the minimumchange needed to fix the bug in v12.Per report from Manuel Rigger. Back-patch to v12 where the mistakecame in.Discussion:https://postgr.es/m/CA+u7OA7nnGYy8rY0vdTe811NuA+Frr9nbcBO9u2Z+JxqNaud+g@mail.gmail.com
1 parentb107140 commitb3c265d

File tree

3 files changed

+95
-4
lines changed

3 files changed

+95
-4
lines changed

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

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -265,7 +265,7 @@ match_pattern_prefix(Node *leftop,
265265
* pattern-matching is not supported with nondeterministic collations. (We
266266
* could also error out here, but by doing it later we get more precise
267267
* error messages.) (It should be possible to support at least
268-
* Pattern_Prefix_Exact, but no point asalong as the actual
268+
* Pattern_Prefix_Exact, but no point aslong as the actual
269269
* pattern-matching implementations don't support it.)
270270
*
271271
* expr_coll is not set for a non-collation-aware data type such as bytea.
@@ -357,13 +357,16 @@ match_pattern_prefix(Node *leftop,
357357

358358
/*
359359
* If we found an exact-match pattern, generate an "=" indexqual.
360+
*
361+
* (Despite the checks above, we might fail to find a suitable operator in
362+
* some cases with binary-compatible opclasses. Just punt if so.)
360363
*/
361364
if (pstatus==Pattern_Prefix_Exact)
362365
{
363366
oproid=get_opfamily_member(opfamily,ldatatype,rdatatype,
364367
BTEqualStrategyNumber);
365368
if (oproid==InvalidOid)
366-
elog(ERROR,"no = operator for opfamily %u",opfamily);
369+
returnNIL;
367370
expr=make_opclause(oproid,BOOLOID, false,
368371
(Expr*)leftop, (Expr*)prefix,
369372
InvalidOid,indexcollation);
@@ -379,7 +382,7 @@ match_pattern_prefix(Node *leftop,
379382
oproid=get_opfamily_member(opfamily,ldatatype,rdatatype,
380383
BTGreaterEqualStrategyNumber);
381384
if (oproid==InvalidOid)
382-
elog(ERROR,"no >= operator for opfamily %u",opfamily);
385+
returnNIL;
383386
expr=make_opclause(oproid,BOOLOID, false,
384387
(Expr*)leftop, (Expr*)prefix,
385388
InvalidOid,indexcollation);
@@ -396,7 +399,7 @@ match_pattern_prefix(Node *leftop,
396399
oproid=get_opfamily_member(opfamily,ldatatype,rdatatype,
397400
BTLessStrategyNumber);
398401
if (oproid==InvalidOid)
399-
elog(ERROR,"no < operator for opfamily %u",opfamily);
402+
returnresult;
400403
fmgr_info(get_opcode(oproid),&ltproc);
401404
greaterstr=make_greater_string(prefix,&ltproc,indexcollation);
402405
if (greaterstr)

‎src/test/regress/expected/btree_index.out

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -198,6 +198,74 @@ select proname from pg_proc where proname ilike 'ri%foo' order by 1;
198198
reset enable_seqscan;
199199
reset enable_indexscan;
200200
reset enable_bitmapscan;
201+
-- Also check LIKE optimization with binary-compatible cases
202+
create temp table btree_bpchar (f1 text collate "C");
203+
create index on btree_bpchar(f1 bpchar_ops);
204+
insert into btree_bpchar values ('foo'), ('fool'), ('bar'), ('quux');
205+
-- doesn't match index:
206+
explain (costs off)
207+
select * from btree_bpchar where f1 like 'foo';
208+
QUERY PLAN
209+
-------------------------------
210+
Seq Scan on btree_bpchar
211+
Filter: (f1 ~~ 'foo'::text)
212+
(2 rows)
213+
214+
select * from btree_bpchar where f1 like 'foo';
215+
f1
216+
-----
217+
foo
218+
(1 row)
219+
220+
explain (costs off)
221+
select * from btree_bpchar where f1 like 'foo%';
222+
QUERY PLAN
223+
--------------------------------
224+
Seq Scan on btree_bpchar
225+
Filter: (f1 ~~ 'foo%'::text)
226+
(2 rows)
227+
228+
select * from btree_bpchar where f1 like 'foo%';
229+
f1
230+
------
231+
foo
232+
fool
233+
(2 rows)
234+
235+
-- these do match the index:
236+
explain (costs off)
237+
select * from btree_bpchar where f1::bpchar like 'foo';
238+
QUERY PLAN
239+
----------------------------------------------------
240+
Bitmap Heap Scan on btree_bpchar
241+
Filter: ((f1)::bpchar ~~ 'foo'::text)
242+
-> Bitmap Index Scan on btree_bpchar_f1_idx
243+
Index Cond: ((f1)::bpchar = 'foo'::bpchar)
244+
(4 rows)
245+
246+
select * from btree_bpchar where f1::bpchar like 'foo';
247+
f1
248+
-----
249+
foo
250+
(1 row)
251+
252+
explain (costs off)
253+
select * from btree_bpchar where f1::bpchar like 'foo%';
254+
QUERY PLAN
255+
------------------------------------------------------------------------------------------
256+
Bitmap Heap Scan on btree_bpchar
257+
Filter: ((f1)::bpchar ~~ 'foo%'::text)
258+
-> Bitmap Index Scan on btree_bpchar_f1_idx
259+
Index Cond: (((f1)::bpchar >= 'foo'::bpchar) AND ((f1)::bpchar < 'fop'::bpchar))
260+
(4 rows)
261+
262+
select * from btree_bpchar where f1::bpchar like 'foo%';
263+
f1
264+
------
265+
foo
266+
fool
267+
(2 rows)
268+
201269
--
202270
-- Test B-tree fast path (cache rightmost leaf page) optimization.
203271
--

‎src/test/regress/sql/btree_index.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,26 @@ reset enable_seqscan;
8383
reset enable_indexscan;
8484
reset enable_bitmapscan;
8585

86+
-- Also check LIKE optimization with binary-compatible cases
87+
88+
create temp table btree_bpchar (f1text collate"C");
89+
createindexon btree_bpchar(f1 bpchar_ops);
90+
insert into btree_bpcharvalues ('foo'), ('fool'), ('bar'), ('quux');
91+
-- doesn't match index:
92+
explain (costs off)
93+
select*from btree_bpcharwhere f1like'foo';
94+
select*from btree_bpcharwhere f1like'foo';
95+
explain (costs off)
96+
select*from btree_bpcharwhere f1like'foo%';
97+
select*from btree_bpcharwhere f1like'foo%';
98+
-- these do match the index:
99+
explain (costs off)
100+
select*from btree_bpcharwhere f1::bpcharlike'foo';
101+
select*from btree_bpcharwhere f1::bpcharlike'foo';
102+
explain (costs off)
103+
select*from btree_bpcharwhere f1::bpcharlike'foo%';
104+
select*from btree_bpcharwhere f1::bpcharlike'foo%';
105+
86106
--
87107
-- Test B-tree fast path (cache rightmost leaf page) optimization.
88108
--

0 commit comments

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