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

Commit8ae9ad1

Browse files
committed
Reimplement LIKE/ESCAPE as operators so that indexscan optimization
can still work, per recent discussion on pghackers. Correct some bugsin ILIKE implementation.
1 parent148f905 commit8ae9ad1

File tree

9 files changed

+379
-382
lines changed

9 files changed

+379
-382
lines changed

‎src/backend/optimizer/path/indxpath.c

Lines changed: 41 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v 1.95 2000/09/12 21:06:53 tgl Exp $
12+
* $Header: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v 1.96 2000/09/15 18:45:25 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -1755,6 +1755,20 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam,
17551755
pfree(patt);
17561756
break;
17571757

1758+
caseOID_TEXT_ICLIKE_OP:
1759+
caseOID_BPCHAR_ICLIKE_OP:
1760+
caseOID_VARCHAR_ICLIKE_OP:
1761+
caseOID_NAME_ICLIKE_OP:
1762+
/* the right-hand const is type text for all of these */
1763+
patt=DatumGetCString(DirectFunctionCall1(textout,
1764+
constvalue));
1765+
isIndexable=pattern_fixed_prefix(patt,Pattern_Type_Like_IC,
1766+
&prefix,&rest)!=Pattern_Prefix_None;
1767+
if (prefix)
1768+
pfree(prefix);
1769+
pfree(patt);
1770+
break;
1771+
17581772
caseOID_TEXT_REGEXEQ_OP:
17591773
caseOID_BPCHAR_REGEXEQ_OP:
17601774
caseOID_VARCHAR_REGEXEQ_OP:
@@ -1797,6 +1811,7 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam,
17971811
switch (expr_op)
17981812
{
17991813
caseOID_TEXT_LIKE_OP:
1814+
caseOID_TEXT_ICLIKE_OP:
18001815
caseOID_TEXT_REGEXEQ_OP:
18011816
caseOID_TEXT_ICREGEXEQ_OP:
18021817
if (!op_class(find_operator(">=",TEXTOID),opclass,relam)||
@@ -1805,6 +1820,7 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam,
18051820
break;
18061821

18071822
caseOID_BPCHAR_LIKE_OP:
1823+
caseOID_BPCHAR_ICLIKE_OP:
18081824
caseOID_BPCHAR_REGEXEQ_OP:
18091825
caseOID_BPCHAR_ICREGEXEQ_OP:
18101826
if (!op_class(find_operator(">=",BPCHAROID),opclass,relam)||
@@ -1813,6 +1829,7 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam,
18131829
break;
18141830

18151831
caseOID_VARCHAR_LIKE_OP:
1832+
caseOID_VARCHAR_ICLIKE_OP:
18161833
caseOID_VARCHAR_REGEXEQ_OP:
18171834
caseOID_VARCHAR_ICREGEXEQ_OP:
18181835
if (!op_class(find_operator(">=",VARCHAROID),opclass,relam)||
@@ -1821,6 +1838,7 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam,
18211838
break;
18221839

18231840
caseOID_NAME_LIKE_OP:
1841+
caseOID_NAME_ICLIKE_OP:
18241842
caseOID_NAME_REGEXEQ_OP:
18251843
caseOID_NAME_ICREGEXEQ_OP:
18261844
if (!op_class(find_operator(">=",NAMEOID),opclass,relam)||
@@ -1887,6 +1905,24 @@ expand_indexqual_conditions(List *indexquals)
18871905
pfree(patt);
18881906
break;
18891907

1908+
caseOID_TEXT_ICLIKE_OP:
1909+
caseOID_BPCHAR_ICLIKE_OP:
1910+
caseOID_VARCHAR_ICLIKE_OP:
1911+
caseOID_NAME_ICLIKE_OP:
1912+
/* the right-hand const is type text for all of these */
1913+
constvalue= ((Const*)rightop)->constvalue;
1914+
patt=DatumGetCString(DirectFunctionCall1(textout,
1915+
constvalue));
1916+
pstatus=pattern_fixed_prefix(patt,Pattern_Type_Like_IC,
1917+
&prefix,&rest);
1918+
resultquals=nconc(resultquals,
1919+
prefix_quals(leftop,expr_op,
1920+
prefix,pstatus));
1921+
if (prefix)
1922+
pfree(prefix);
1923+
pfree(patt);
1924+
break;
1925+
18901926
caseOID_TEXT_REGEXEQ_OP:
18911927
caseOID_BPCHAR_REGEXEQ_OP:
18921928
caseOID_VARCHAR_REGEXEQ_OP:
@@ -1955,24 +1991,28 @@ prefix_quals(Var *leftop, Oid expr_op,
19551991
switch (expr_op)
19561992
{
19571993
caseOID_TEXT_LIKE_OP:
1994+
caseOID_TEXT_ICLIKE_OP:
19581995
caseOID_TEXT_REGEXEQ_OP:
19591996
caseOID_TEXT_ICREGEXEQ_OP:
19601997
datatype=TEXTOID;
19611998
break;
19621999

19632000
caseOID_BPCHAR_LIKE_OP:
2001+
caseOID_BPCHAR_ICLIKE_OP:
19642002
caseOID_BPCHAR_REGEXEQ_OP:
19652003
caseOID_BPCHAR_ICREGEXEQ_OP:
19662004
datatype=BPCHAROID;
19672005
break;
19682006

19692007
caseOID_VARCHAR_LIKE_OP:
2008+
caseOID_VARCHAR_ICLIKE_OP:
19702009
caseOID_VARCHAR_REGEXEQ_OP:
19712010
caseOID_VARCHAR_ICREGEXEQ_OP:
19722011
datatype=VARCHAROID;
19732012
break;
19742013

19752014
caseOID_NAME_LIKE_OP:
2015+
caseOID_NAME_ICLIKE_OP:
19762016
caseOID_NAME_REGEXEQ_OP:
19772017
caseOID_NAME_ICREGEXEQ_OP:
19782018
datatype=NAMEOID;

‎src/backend/parser/gram.y

Lines changed: 18 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.189 2000/09/12 21:07:01 tgl Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.190 2000/09/15 18:45:30 tgl Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -365,6 +365,7 @@ static void doNegateFloat(Value *v);
365365
%right'='
366366
%nonassoc'<''>'
367367
%nonassocLIKEILIKE
368+
%nonassocESCAPE
368369
%nonassocOVERLAPS
369370
%nonassocBETWEEN
370371
%nonassocIN
@@ -382,7 +383,6 @@ static void doNegateFloat(Value *v);
382383
%left'.'
383384
%left'['']'
384385
%leftTYPECAST
385-
%leftESCAPE
386386
%%
387387

388388
/*
@@ -4522,76 +4522,48 @@ a_expr: c_expr
45224522
{$$ = makeA_Expr(NOT,NULL,NULL,$2); }
45234523

45244524
|a_exprLIKEa_expr
4525-
{
4526-
FuncCall *n = makeNode(FuncCall);
4527-
n->funcname ="like";
4528-
n->args = makeList($1,$3, -1);
4529-
n->agg_star =FALSE;
4530-
n->agg_distinct =FALSE;
4531-
$$ = (Node *)n;
4532-
}
4525+
{$$ = makeA_Expr(OP,"~~",$1,$3); }
45334526
|a_exprLIKEa_exprESCAPEa_expr
45344527
{
45354528
FuncCall *n = makeNode(FuncCall);
4536-
n->funcname ="like";
4537-
n->args = makeList($1,$3,$5, -1);
4529+
n->funcname ="like_escape";
4530+
n->args = makeList($3,$5, -1);
45384531
n->agg_star =FALSE;
45394532
n->agg_distinct =FALSE;
4540-
$$ =(Node *)n;
4533+
$$ =makeA_Expr(OP,"~~",$1, (Node *) n);
45414534
}
45424535
|a_exprNOTLIKEa_expr
4543-
{
4544-
FuncCall *n = makeNode(FuncCall);
4545-
n->funcname ="notlike";
4546-
n->args = makeList($1,$4, -1);
4547-
n->agg_star =FALSE;
4548-
n->agg_distinct =FALSE;
4549-
$$ = (Node *)n;
4550-
}
4536+
{$$ = makeA_Expr(OP,"!~~",$1,$4); }
45514537
|a_exprNOTLIKEa_exprESCAPEa_expr
45524538
{
45534539
FuncCall *n = makeNode(FuncCall);
4554-
n->funcname ="notlike";
4555-
n->args = makeList($1,$4,$6, -1);
4540+
n->funcname ="like_escape";
4541+
n->args = makeList($4,$6, -1);
45564542
n->agg_star =FALSE;
45574543
n->agg_distinct =FALSE;
4558-
$$ =(Node *)n;
4544+
$$ =makeA_Expr(OP,"!~~",$1, (Node *) n);
45594545
}
45604546
|a_exprILIKEa_expr
4561-
{
4562-
FuncCall *n = makeNode(FuncCall);
4563-
n->funcname ="ilike";
4564-
n->args = makeList($1,$3, -1);
4565-
n->agg_star =FALSE;
4566-
n->agg_distinct =FALSE;
4567-
$$ = (Node *)n;
4568-
}
4547+
{$$ = makeA_Expr(OP,"~~*",$1,$3); }
45694548
|a_exprILIKEa_exprESCAPEa_expr
45704549
{
45714550
FuncCall *n = makeNode(FuncCall);
4572-
n->funcname ="ilike";
4573-
n->args = makeList($1,$3,$5, -1);
4551+
n->funcname ="like_escape";
4552+
n->args = makeList($3,$5, -1);
45744553
n->agg_star =FALSE;
45754554
n->agg_distinct =FALSE;
4576-
$$ =(Node *)n;
4555+
$$ =makeA_Expr(OP,"~~*",$1, (Node *) n);
45774556
}
45784557
|a_exprNOTILIKEa_expr
4579-
{
4580-
FuncCall *n = makeNode(FuncCall);
4581-
n->funcname ="inotlike";
4582-
n->args = makeList($1,$4, -1);
4583-
n->agg_star =FALSE;
4584-
n->agg_distinct =FALSE;
4585-
$$ = (Node *)n;
4586-
}
4558+
{$$ = makeA_Expr(OP,"!~~*",$1,$4); }
45874559
|a_exprNOTILIKEa_exprESCAPEa_expr
45884560
{
45894561
FuncCall *n = makeNode(FuncCall);
4590-
n->funcname ="inotlike";
4591-
n->args = makeList($1,$4,$6, -1);
4562+
n->funcname ="like_escape";
4563+
n->args = makeList($4,$6, -1);
45924564
n->agg_star =FALSE;
45934565
n->agg_distinct =FALSE;
4594-
$$ =(Node *)n;
4566+
$$ =makeA_Expr(OP,"!~~*",$1, (Node *) n);
45954567
}
45964568

45974569
|a_exprISNULL

0 commit comments

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