Skip to content

Commit 8ae9ad1

Browse files
tglsfdctglsfdc
committed
Reimplement LIKE/ESCAPE as operators so that indexscan optimization
can still work, per recent discussion on pghackers. Correct some bugs in ILIKE implementation.
1 parent 148f905 commit 8ae9ad1

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+
case OID_TEXT_ICLIKE_OP:
1759+
case OID_BPCHAR_ICLIKE_OP:
1760+
case OID_VARCHAR_ICLIKE_OP:
1761+
case OID_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
case OID_TEXT_REGEXEQ_OP:
17591773
case OID_BPCHAR_REGEXEQ_OP:
17601774
case OID_VARCHAR_REGEXEQ_OP:
@@ -1797,6 +1811,7 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam,
17971811
switch (expr_op)
17981812
{
17991813
case OID_TEXT_LIKE_OP:
1814+
case OID_TEXT_ICLIKE_OP:
18001815
case OID_TEXT_REGEXEQ_OP:
18011816
case OID_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
case OID_BPCHAR_LIKE_OP:
1823+
case OID_BPCHAR_ICLIKE_OP:
18081824
case OID_BPCHAR_REGEXEQ_OP:
18091825
case OID_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
case OID_VARCHAR_LIKE_OP:
1832+
case OID_VARCHAR_ICLIKE_OP:
18161833
case OID_VARCHAR_REGEXEQ_OP:
18171834
case OID_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
case OID_NAME_LIKE_OP:
1841+
case OID_NAME_ICLIKE_OP:
18241842
case OID_NAME_REGEXEQ_OP:
18251843
case OID_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+
case OID_TEXT_ICLIKE_OP:
1909+
case OID_BPCHAR_ICLIKE_OP:
1910+
case OID_VARCHAR_ICLIKE_OP:
1911+
case OID_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
case OID_TEXT_REGEXEQ_OP:
18911927
case OID_BPCHAR_REGEXEQ_OP:
18921928
case OID_VARCHAR_REGEXEQ_OP:
@@ -1955,24 +1991,28 @@ prefix_quals(Var *leftop, Oid expr_op,
19551991
switch (expr_op)
19561992
{
19571993
case OID_TEXT_LIKE_OP:
1994+
case OID_TEXT_ICLIKE_OP:
19581995
case OID_TEXT_REGEXEQ_OP:
19591996
case OID_TEXT_ICREGEXEQ_OP:
19601997
datatype = TEXTOID;
19611998
break;
19621999

19632000
case OID_BPCHAR_LIKE_OP:
2001+
case OID_BPCHAR_ICLIKE_OP:
19642002
case OID_BPCHAR_REGEXEQ_OP:
19652003
case OID_BPCHAR_ICREGEXEQ_OP:
19662004
datatype = BPCHAROID;
19672005
break;
19682006

19692007
case OID_VARCHAR_LIKE_OP:
2008+
case OID_VARCHAR_ICLIKE_OP:
19702009
case OID_VARCHAR_REGEXEQ_OP:
19712010
case OID_VARCHAR_ICREGEXEQ_OP:
19722011
datatype = VARCHAROID;
19732012
break;
19742013

19752014
case OID_NAME_LIKE_OP:
2015+
case OID_NAME_ICLIKE_OP:
19762016
case OID_NAME_REGEXEQ_OP:
19772017
case OID_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
* AUTHOR DATE MAJOR EVENT
@@ -365,6 +365,7 @@ static void doNegateFloat(Value *v);
365365
%right '='
366366
%nonassoc '<' '>'
367367
%nonassoc LIKE ILIKE
368+
%nonassoc ESCAPE
368369
%nonassoc OVERLAPS
369370
%nonassoc BETWEEN
370371
%nonassoc IN
@@ -382,7 +383,6 @@ static void doNegateFloat(Value *v);
382383
%left '.'
383384
%left '[' ']'
384385
%left TYPECAST
385-
%left ESCAPE
386386
%%
387387

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

45244524
| a_expr LIKE a_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_expr LIKE a_expr ESCAPE a_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_expr NOT LIKE a_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_expr NOT LIKE a_expr ESCAPE a_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_expr ILIKE a_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_expr ILIKE a_expr ESCAPE a_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_expr NOT ILIKE a_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_expr NOT ILIKE a_expr ESCAPE a_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_expr ISNULL

0 commit comments

Comments
 (0)