Re: bytea operator bugs (was Re: [GENERAL] BYTEA, indexes

Patch applied. Thanks.

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Joe Conway <mail(at)joeconway(dot)com> writes:
> >>OK. I'll look at both options and make another diff -c proposal ;-) Once
> >>that's resolved I'll go back to original issue Alvar raised.
> >
> > Okay. When you get back to the original issue, the gold is hidden in
> > src/backend/optimizer/path/indxpath.c; see the "special indexable
> > operators" stuff near the bottom of that file. (It's a bit of a crock
> > that this code is hardwired there, and not somehow accessed through a
> > system catalog, but it's what we've got at the moment.)
>
> The attached patch re-enables a bytea right hand argument (as compared
> to a text right hand argument), and enables index usage, for bytea LIKE
> -- e.g.:
>
>
> parts=# explain select * from bombytea where parent_part like '05-05%';
> QUERY PLAN
> -------------------------------------------------------------------------------------
> Index Scan using bombytea_idx1 on bombytea (cost=0.00..3479.67
> rows=1118 width=34)
> Index Cond: ((parent_part >= '05-05'::bytea) AND (parent_part <
> '05-06'::bytea))
> Filter: (parent_part ~~ '05-05%'::bytea)
> (3 rows)
>
>
> Passes all regression tests, and as far as I can tell does not break or
> change the behavior of anything else. Please review and apply if there
> are no objections (I'd like to see this applied for 7.3, before the
> freeze, if possible, but I'll certainly understand if I'm told there's
> not enough time left).
>
> Thanks,
>
> Joe

> Index: src/backend/optimizer/path/indxpath.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/optimizer/path/indxpath.c,v
> retrieving revision 1.120
> diff -c -r1.120 indxpath.c
> *** src/backend/optimizer/path/indxpath.c 13 Jul 2002 19:20:34 -0000 1.120
> --- src/backend/optimizer/path/indxpath.c 1 Sep 2002 22:19:16 -0000
> ***************
> *** 97,103 ****
> static bool match_special_index_operator(Expr *clause, Oid opclass,
> bool indexkey_on_left);
> static List *prefix_quals(Var *leftop, Oid expr_op,
> ! char *prefix, Pattern_Prefix_Status pstatus);
> static List *network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop);
> static Oid find_operator(const char *opname, Oid datatype);
> static Datum string_to_datum(const char *str, Oid datatype);
> --- 97,103 ----
> static bool match_special_index_operator(Expr *clause, Oid opclass,
> bool indexkey_on_left);
> static List *prefix_quals(Var *leftop, Oid expr_op,
> ! Const *prefix, Pattern_Prefix_Status pstatus);
> static List *network_prefix_quals(Var *leftop, Oid expr_op, Datum rightop);
> static Oid find_operator(const char *opname, Oid datatype);
> static Datum string_to_datum(const char *str, Oid datatype);
> ***************
> *** 1675,1684 ****
> Var *leftop,
> *rightop;
> Oid expr_op;
> ! Datum constvalue;
> ! char *patt;
> ! char *prefix;
> ! char *rest;
>
> /*
> * Currently, all known special operators require the indexkey on the
> --- 1675,1683 ----
> Var *leftop,
> *rightop;
> Oid expr_op;
> ! Const *patt = NULL;
> ! Const *prefix = NULL;
> ! Const *rest = NULL;
>
> /*
> * Currently, all known special operators require the indexkey on the
> ***************
> *** 1697,1703 ****
> if (!IsA(rightop, Const) ||
> ((Const *) rightop)->constisnull)
> return false;
> ! constvalue = ((Const *) rightop)->constvalue;
>
> switch (expr_op)
> {
> --- 1696,1702 ----
> if (!IsA(rightop, Const) ||
> ((Const *) rightop)->constisnull)
> return false;
> ! patt = (Const *) rightop;
>
> switch (expr_op)
> {
> ***************
> *** 1705,1772 ****
> case OID_BPCHAR_LIKE_OP:
> case OID_VARCHAR_LIKE_OP:
> case OID_NAME_LIKE_OP:
> if (locale_is_like_safe())
> - {
> - /* the right-hand const is type text for all of these */
> - patt = DatumGetCString(DirectFunctionCall1(textout,
> - constvalue));
> isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like,
> &prefix, &rest) != Pattern_Prefix_None;
> ! if (prefix)
> ! pfree(prefix);
> ! pfree(patt);
> ! }
> break;
>
> case OID_TEXT_ICLIKE_OP:
> case OID_BPCHAR_ICLIKE_OP:
> case OID_VARCHAR_ICLIKE_OP:
> case OID_NAME_ICLIKE_OP:
> if (locale_is_like_safe())
> - {
> - /* the right-hand const is type text for all of these */
> - patt = DatumGetCString(DirectFunctionCall1(textout,
> - constvalue));
> isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC,
> &prefix, &rest) != Pattern_Prefix_None;
> - if (prefix)
> - pfree(prefix);
> - pfree(patt);
> - }
> break;
>
> case OID_TEXT_REGEXEQ_OP:
> case OID_BPCHAR_REGEXEQ_OP:
> case OID_VARCHAR_REGEXEQ_OP:
> case OID_NAME_REGEXEQ_OP:
> if (locale_is_like_safe())
> - {
> - /* the right-hand const is type text for all of these */
> - patt = DatumGetCString(DirectFunctionCall1(textout,
> - constvalue));
> isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex,
> &prefix, &rest) != Pattern_Prefix_None;
> - if (prefix)
> - pfree(prefix);
> - pfree(patt);
> - }
> break;
>
> case OID_TEXT_ICREGEXEQ_OP:
> case OID_BPCHAR_ICREGEXEQ_OP:
> case OID_VARCHAR_ICREGEXEQ_OP:
> case OID_NAME_ICREGEXEQ_OP:
> if (locale_is_like_safe())
> - {
> - /* the right-hand const is type text for all of these */
> - patt = DatumGetCString(DirectFunctionCall1(textout,
> - constvalue));
> isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC,
> &prefix, &rest) != Pattern_Prefix_None;
> - if (prefix)
> - pfree(prefix);
> - pfree(patt);
> - }
> break;
>
> case OID_INET_SUB_OP:
> --- 1704,1748 ----
> case OID_BPCHAR_LIKE_OP:
> case OID_VARCHAR_LIKE_OP:
> case OID_NAME_LIKE_OP:
> + /* the right-hand const is type text for all of these */
> if (locale_is_like_safe())
> isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like,
> &prefix, &rest) != Pattern_Prefix_None;
> ! break;
> !
> ! case OID_BYTEA_LIKE_OP:
> ! isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like,
> ! &prefix, &rest) != Pattern_Prefix_None;
> break;
>
> case OID_TEXT_ICLIKE_OP:
> case OID_BPCHAR_ICLIKE_OP:
> case OID_VARCHAR_ICLIKE_OP:
> case OID_NAME_ICLIKE_OP:
> + /* the right-hand const is type text for all of these */
> if (locale_is_like_safe())
> isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC,
> &prefix, &rest) != Pattern_Prefix_None;
> break;
>
> case OID_TEXT_REGEXEQ_OP:
> case OID_BPCHAR_REGEXEQ_OP:
> case OID_VARCHAR_REGEXEQ_OP:
> case OID_NAME_REGEXEQ_OP:
> + /* the right-hand const is type text for all of these */
> if (locale_is_like_safe())
> isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex,
> &prefix, &rest) != Pattern_Prefix_None;
> break;
>
> case OID_TEXT_ICREGEXEQ_OP:
> case OID_BPCHAR_ICREGEXEQ_OP:
> case OID_VARCHAR_ICREGEXEQ_OP:
> case OID_NAME_ICREGEXEQ_OP:
> + /* the right-hand const is type text for all of these */
> if (locale_is_like_safe())
> isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC,
> &prefix, &rest) != Pattern_Prefix_None;
> break;
>
> case OID_INET_SUB_OP:
> ***************
> *** 1777,1782 ****
> --- 1753,1764 ----
> break;
> }
>
> + if (prefix)
> + {
> + pfree(DatumGetPointer(prefix->constvalue));
> + pfree(prefix);
> + }
> +
> /* done if the expression doesn't look indexable */
> if (!isIndexable)
> return false;
> ***************
> *** 1798,1803 ****
> --- 1780,1791 ----
> isIndexable = false;
> break;
>
> + case OID_BYTEA_LIKE_OP:
> + if (!op_in_opclass(find_operator(">=", BYTEAOID), opclass) ||
> + !op_in_opclass(find_operator("<", BYTEAOID), opclass))
> + isIndexable = false;
> + break;
> +
> case OID_BPCHAR_LIKE_OP:
> case OID_BPCHAR_ICLIKE_OP:
> case OID_BPCHAR_REGEXEQ_OP:
> ***************
> *** 1867,1876 ****
> Var *leftop = get_leftop(clause);
> Var *rightop = get_rightop(clause);
> Oid expr_op = ((Oper *) clause->oper)->opno;
> ! Datum constvalue;
> ! char *patt;
> ! char *prefix;
> ! char *rest;
> Pattern_Prefix_Status pstatus;
>
> switch (expr_op)
> --- 1855,1863 ----
> Var *leftop = get_leftop(clause);
> Var *rightop = get_rightop(clause);
> Oid expr_op = ((Oper *) clause->oper)->opno;
> ! Const *patt = (Const *) rightop;
> ! Const *prefix = NULL;
> ! Const *rest = NULL;
> Pattern_Prefix_Status pstatus;
>
> switch (expr_op)
> ***************
> *** 1885,1902 ****
> case OID_BPCHAR_LIKE_OP:
> case OID_VARCHAR_LIKE_OP:
> case OID_NAME_LIKE_OP:
> ! /* the right-hand const is type text for all of these */
> ! constvalue = ((Const *) rightop)->constvalue;
> ! patt = DatumGetCString(DirectFunctionCall1(textout,
> ! constvalue));
> pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like,
> &prefix, &rest);
> resultquals = nconc(resultquals,
> prefix_quals(leftop, expr_op,
> prefix, pstatus));
> - if (prefix)
> - pfree(prefix);
> - pfree(patt);
> break;
>
> case OID_TEXT_ICLIKE_OP:
> --- 1872,1883 ----
> case OID_BPCHAR_LIKE_OP:
> case OID_VARCHAR_LIKE_OP:
> case OID_NAME_LIKE_OP:
> ! case OID_BYTEA_LIKE_OP:
> pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like,
> &prefix, &rest);
> resultquals = nconc(resultquals,
> prefix_quals(leftop, expr_op,
> prefix, pstatus));
> break;
>
> case OID_TEXT_ICLIKE_OP:
> ***************
> *** 1904,1920 ****
> case OID_VARCHAR_ICLIKE_OP:
> case OID_NAME_ICLIKE_OP:
> /* the right-hand const is type text for all of these */
> - constvalue = ((Const *) rightop)->constvalue;
> - patt = DatumGetCString(DirectFunctionCall1(textout,
> - constvalue));
> pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like_IC,
> &prefix, &rest);
> resultquals = nconc(resultquals,
> prefix_quals(leftop, expr_op,
> prefix, pstatus));
> - if (prefix)
> - pfree(prefix);
> - pfree(patt);
> break;
>
> case OID_TEXT_REGEXEQ_OP:
> --- 1885,1895 ----
> ***************
> *** 1922,1938 ****
> case OID_VARCHAR_REGEXEQ_OP:
> case OID_NAME_REGEXEQ_OP:
> /* the right-hand const is type text for all of these */
> - constvalue = ((Const *) rightop)->constvalue;
> - patt = DatumGetCString(DirectFunctionCall1(textout,
> - constvalue));
> pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex,
> &prefix, &rest);
> resultquals = nconc(resultquals,
> prefix_quals(leftop, expr_op,
> prefix, pstatus));
> - if (prefix)
> - pfree(prefix);
> - pfree(patt);
> break;
>
> case OID_TEXT_ICREGEXEQ_OP:
> --- 1897,1907 ----
> ***************
> *** 1940,1966 ****
> case OID_VARCHAR_ICREGEXEQ_OP:
> case OID_NAME_ICREGEXEQ_OP:
> /* the right-hand const is type text for all of these */
> - constvalue = ((Const *) rightop)->constvalue;
> - patt = DatumGetCString(DirectFunctionCall1(textout,
> - constvalue));
> pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC,
> &prefix, &rest);
> resultquals = nconc(resultquals,
> prefix_quals(leftop, expr_op,
> prefix, pstatus));
> - if (prefix)
> - pfree(prefix);
> - pfree(patt);
> break;
>
> case OID_INET_SUB_OP:
> case OID_INET_SUBEQ_OP:
> case OID_CIDR_SUB_OP:
> case OID_CIDR_SUBEQ_OP:
> - constvalue = ((Const *) rightop)->constvalue;
> resultquals = nconc(resultquals,
> network_prefix_quals(leftop, expr_op,
> ! constvalue));
> break;
>
> default:
> --- 1909,1928 ----
> case OID_VARCHAR_ICREGEXEQ_OP:
> case OID_NAME_ICREGEXEQ_OP:
> /* the right-hand const is type text for all of these */
> pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC,
> &prefix, &rest);
> resultquals = nconc(resultquals,
> prefix_quals(leftop, expr_op,
> prefix, pstatus));
> break;
>
> case OID_INET_SUB_OP:
> case OID_INET_SUBEQ_OP:
> case OID_CIDR_SUB_OP:
> case OID_CIDR_SUBEQ_OP:
> resultquals = nconc(resultquals,
> network_prefix_quals(leftop, expr_op,
> ! patt->constvalue));
> break;
>
> default:
> ***************
> *** 1980,1994 ****
> */
> static List *
> prefix_quals(Var *leftop, Oid expr_op,
> ! char *prefix, Pattern_Prefix_Status pstatus)
> {
> List *result;
> Oid datatype;
> Oid oproid;
> Const *con;
> Oper *op;
> Expr *expr;
> ! char *greaterstr;
>
> Assert(pstatus != Pattern_Prefix_None);
>
> --- 1942,1957 ----
> */
> static List *
> prefix_quals(Var *leftop, Oid expr_op,
> ! Const *prefix_const, Pattern_Prefix_Status pstatus)
> {
> List *result;
> Oid datatype;
> Oid oproid;
> + char *prefix;
> Const *con;
> Oper *op;
> Expr *expr;
> ! Const *greaterstr = NULL;
>
> Assert(pstatus != Pattern_Prefix_None);
>
> ***************
> *** 2001,2006 ****
> --- 1964,1973 ----
> datatype = TEXTOID;
> break;
>
> + case OID_BYTEA_LIKE_OP:
> + datatype = BYTEAOID;
> + break;
> +
> case OID_BPCHAR_LIKE_OP:
> case OID_BPCHAR_ICLIKE_OP:
> case OID_BPCHAR_REGEXEQ_OP:
> ***************
> *** 2027,2032 ****
> --- 1994,2004 ----
> return NIL;
> }
>
> + if (prefix_const->consttype != BYTEAOID)
> + prefix = DatumGetCString(DirectFunctionCall1(textout, prefix_const->constvalue));
> + else
> + prefix = DatumGetCString(DirectFunctionCall1(byteaout, prefix_const->constvalue));
> +
> /*
> * If we found an exact-match pattern, generate an "=" indexqual.
> */
> ***************
> *** 2060,2076 ****
> * "x < greaterstr".
> *-------
> */
> ! greaterstr = make_greater_string(prefix, datatype);
> if (greaterstr)
> {
> oproid = find_operator("<", datatype);
> if (oproid == InvalidOid)
> elog(ERROR, "prefix_quals: no < operator for type %u", datatype);
> - con = string_to_const(greaterstr, datatype);
> op = makeOper(oproid, InvalidOid, BOOLOID, false);
> ! expr = make_opclause(op, leftop, (Var *) con);
> result = lappend(result, expr);
> - pfree(greaterstr);
> }
>
> return result;
> --- 2032,2046 ----
> * "x < greaterstr".
> *-------
> */
> ! greaterstr = make_greater_string(con);
> if (greaterstr)
> {
> oproid = find_operator("<", datatype);
> if (oproid == InvalidOid)
> elog(ERROR, "prefix_quals: no < operator for type %u", datatype);
> op = makeOper(oproid, InvalidOid, BOOLOID, false);
> ! expr = make_opclause(op, leftop, (Var *) greaterstr);
> result = lappend(result, expr);
> }
>
> return result;
> ***************
> *** 2186,2191 ****
> --- 2156,2163 ----
> */
> if (datatype == NAMEOID)
> return DirectFunctionCall1(namein, CStringGetDatum(str));
> + else if (datatype == BYTEAOID)
> + return DirectFunctionCall1(byteain, CStringGetDatum(str));
> else
> return DirectFunctionCall1(textin, CStringGetDatum(str));
> }
> Index: src/backend/utils/adt/like.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/like.c,v
> retrieving revision 1.51
> diff -c -r1.51 like.c
> *** src/backend/utils/adt/like.c 29 Aug 2002 07:22:26 -0000 1.51
> --- src/backend/utils/adt/like.c 1 Sep 2002 21:46:27 -0000
> ***************
> *** 242,248 ****
> bytealike(PG_FUNCTION_ARGS)
> {
> bytea *str = PG_GETARG_BYTEA_P(0);
> ! text *pat = PG_GETARG_TEXT_P(1);
> bool result;
> unsigned char *s,
> *p;
> --- 242,248 ----
> bytealike(PG_FUNCTION_ARGS)
> {
> bytea *str = PG_GETARG_BYTEA_P(0);
> ! bytea *pat = PG_GETARG_BYTEA_P(1);
> bool result;
> unsigned char *s,
> *p;
> ***************
> *** 263,269 ****
> byteanlike(PG_FUNCTION_ARGS)
> {
> bytea *str = PG_GETARG_BYTEA_P(0);
> ! text *pat = PG_GETARG_TEXT_P(1);
> bool result;
> unsigned char *s,
> *p;
> --- 263,269 ----
> byteanlike(PG_FUNCTION_ARGS)
> {
> bytea *str = PG_GETARG_BYTEA_P(0);
> ! bytea *pat = PG_GETARG_BYTEA_P(1);
> bool result;
> unsigned char *s,
> *p;
> Index: src/backend/utils/adt/selfuncs.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/adt/selfuncs.c,v
> retrieving revision 1.114
> diff -c -r1.114 selfuncs.c
> *** src/backend/utils/adt/selfuncs.c 29 Aug 2002 07:22:27 -0000 1.114
> --- src/backend/utils/adt/selfuncs.c 1 Sep 2002 23:14:57 -0000
> ***************
> *** 73,78 ****
> --- 73,79 ----
> #include <locale.h>
>
> #include "access/heapam.h"
> + #include "access/tuptoaster.h"
> #include "catalog/catname.h"
> #include "catalog/pg_namespace.h"
> #include "catalog/pg_operator.h"
> ***************
> *** 168,175 ****
> Var **var, Node **other,
> bool *varonleft);
> static void get_join_vars(List *args, Var **var1, Var **var2);
> ! static Selectivity prefix_selectivity(Query *root, Var *var, char *prefix);
> ! static Selectivity pattern_selectivity(char *patt, Pattern_Type ptype);
> static bool string_lessthan(const char *str1, const char *str2,
> Oid datatype);
> static Oid find_operator(const char *opname, Oid datatype);
> --- 169,176 ----
> Var **var, Node **other,
> bool *varonleft);
> static void get_join_vars(List *args, Var **var1, Var **var2);
> ! static Selectivity prefix_selectivity(Query *root, Var *var, Const *prefix);
> ! static Selectivity pattern_selectivity(Const *patt, Pattern_Type ptype);
> static bool string_lessthan(const char *str1, const char *str2,
> Oid datatype);
> static Oid find_operator(const char *opname, Oid datatype);
> ***************
> *** 826,835 ****
> bool varonleft;
> Oid relid;
> Datum constval;
> - char *patt;
> Pattern_Prefix_Status pstatus;
> ! char *prefix;
> ! char *rest;
> double result;
>
> /*
> --- 827,836 ----
> bool varonleft;
> Oid relid;
> Datum constval;
> Pattern_Prefix_Status pstatus;
> ! Const *patt = NULL;
> ! Const *prefix = NULL;
> ! Const *rest = NULL;
> double result;
>
> /*
> ***************
> *** 853,863 ****
> if (((Const *) other)->constisnull)
> return 0.0;
> constval = ((Const *) other)->constvalue;
> ! /* the right-hand const is type text for all supported operators */
> ! Assert(((Const *) other)->consttype == TEXTOID);
> ! patt = DatumGetCString(DirectFunctionCall1(textout, constval));
>
> /* divide pattern into fixed prefix and remainder */
> pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest);
>
> if (pstatus == Pattern_Prefix_Exact)
> --- 854,866 ----
> if (((Const *) other)->constisnull)
> return 0.0;
> constval = ((Const *) other)->constvalue;
> !
> ! /* the right-hand const is type text or bytea for all supported operators */
> ! Assert(((Const *) other)->consttype == TEXTOID ||
> ! ((Const *) other)->consttype == BYTEAOID);
>
> /* divide pattern into fixed prefix and remainder */
> + patt = (Const *) other;
> pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest);
>
> if (pstatus == Pattern_Prefix_Exact)
> ***************
> *** 866,879 ****
> * Pattern specifies an exact match, so pretend operator is '='
> */
> Oid eqopr = find_operator("=", var->vartype);
> - Const *eqcon;
> List *eqargs;
>
> if (eqopr == InvalidOid)
> elog(ERROR, "patternsel: no = operator for type %u",
> var->vartype);
> ! eqcon = string_to_const(prefix, var->vartype);
> ! eqargs = makeList2(var, eqcon);
> result = DatumGetFloat8(DirectFunctionCall4(eqsel,
> PointerGetDatum(root),
> ObjectIdGetDatum(eqopr),
> --- 869,880 ----
> * Pattern specifies an exact match, so pretend operator is '='
> */
> Oid eqopr = find_operator("=", var->vartype);
> List *eqargs;
>
> if (eqopr == InvalidOid)
> elog(ERROR, "patternsel: no = operator for type %u",
> var->vartype);
> ! eqargs = makeList2(var, prefix);
> result = DatumGetFloat8(DirectFunctionCall4(eqsel,
> PointerGetDatum(root),
> ObjectIdGetDatum(eqopr),
> ***************
> *** 903,910 ****
> }
>
> if (prefix)
> pfree(prefix);
> ! pfree(patt);
>
> return result;
> }
> --- 904,913 ----
> }
>
> if (prefix)
> + {
> + pfree(DatumGetPointer(prefix->constvalue));
> pfree(prefix);
> ! }
>
> return result;
> }
> ***************
> *** 2693,2709 ****
> */
>
> static Pattern_Prefix_Status
> ! like_fixed_prefix(char *patt, bool case_insensitive,
> ! char **prefix, char **rest)
> {
> char *match;
> int pos,
> match_pos;
>
> ! *prefix = match = palloc(strlen(patt) + 1);
> match_pos = 0;
>
> ! for (pos = 0; patt[pos]; pos++)
> {
> /* % and _ are wildcard characters in LIKE */
> if (patt[pos] == '%' ||
> --- 2696,2734 ----
> */
>
> static Pattern_Prefix_Status
> ! like_fixed_prefix(Const *patt_const, bool case_insensitive,
> ! Const **prefix_const, Const **rest_const)
> {
> char *match;
> + char *patt;
> + int pattlen;
> + char *prefix;
> + char *rest;
> + Oid typeid = patt_const->consttype;
> int pos,
> match_pos;
>
> ! /* the right-hand const is type text or bytea */
> ! Assert(typeid == BYTEAOID || typeid == TEXTOID);
> !
> ! if (typeid == BYTEAOID && case_insensitive)
> ! elog(ERROR, "Cannot perform case insensitive matching on type BYTEA");
> !
> ! if (typeid != BYTEAOID)
> ! {
> ! patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue));
> ! pattlen = strlen(patt);
> ! }
> ! else
> ! {
> ! patt = DatumGetCString(DirectFunctionCall1(byteaout, patt_const->constvalue));
> ! pattlen = toast_raw_datum_size(patt_const->constvalue) - VARHDRSZ;
> ! }
> !
> ! prefix = match = palloc(pattlen + 1);
> match_pos = 0;
>
> ! for (pos = 0; pos < pattlen; pos++)
> {
> /* % and _ are wildcard characters in LIKE */
> if (patt[pos] == '%' ||
> ***************
> *** 2713,2719 ****
> if (patt[pos] == '\\')
> {
> pos++;
> ! if (patt[pos] == '\0')
> break;
> }
>
> --- 2738,2744 ----
> if (patt[pos] == '\\')
> {
> pos++;
> ! if (patt[pos] == '\0' && typeid != BYTEAOID)
> break;
> }
>
> ***************
> *** 2733,2767 ****
> }
>
> match[match_pos] = '\0';
> ! *rest = &patt[pos];
>
> /* in LIKE, an empty pattern is an exact match! */
> ! if (patt[pos] == '\0')
> return Pattern_Prefix_Exact; /* reached end of pattern, so
> * exact */
>
> if (match_pos > 0)
> return Pattern_Prefix_Partial;
>
> - pfree(match);
> - *prefix = NULL;
> return Pattern_Prefix_None;
> }
>
> static Pattern_Prefix_Status
> ! regex_fixed_prefix(char *patt, bool case_insensitive,
> ! char **prefix, char **rest)
> {
> char *match;
> int pos,
> match_pos,
> paren_depth;
>
> /* Pattern must be anchored left */
> if (patt[0] != '^')
> {
> ! *prefix = NULL;
> ! *rest = patt;
> return Pattern_Prefix_None;
> }
>
> --- 2758,2815 ----
> }
>
> match[match_pos] = '\0';
> ! rest = &patt[pos];
> !
> ! *prefix_const = string_to_const(prefix, typeid);
> ! *rest_const = string_to_const(rest, typeid);
> !
> ! pfree(patt);
> ! pfree(match);
> ! prefix = NULL;
>
> /* in LIKE, an empty pattern is an exact match! */
> ! if (pos == pattlen)
> return Pattern_Prefix_Exact; /* reached end of pattern, so
> * exact */
>
> if (match_pos > 0)
> return Pattern_Prefix_Partial;
>
> return Pattern_Prefix_None;
> }
>
> static Pattern_Prefix_Status
> ! regex_fixed_prefix(Const *patt_const, bool case_insensitive,
> ! Const **prefix_const, Const **rest_const)
> {
> char *match;
> int pos,
> match_pos,
> paren_depth;
> + char *patt;
> + char *prefix;
> + char *rest;
> + Oid typeid = patt_const->consttype;
> +
> + /*
> + * Should be unnecessary, there are no bytea regex operators defined.
> + * As such, it should be noted that the rest of this function has *not*
> + * been made safe for binary (possibly NULL containing) strings.
> + */
> + if (typeid == BYTEAOID)
> + elog(ERROR, "Regex matching not supported on type BYTEA");
> +
> + /* the right-hand const is type text for all of these */
> + patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue));
>
> /* Pattern must be anchored left */
> if (patt[0] != '^')
> {
> ! rest = patt;
> !
> ! *prefix_const = NULL;
> ! *rest_const = string_to_const(rest, typeid);
> !
> return Pattern_Prefix_None;
> }
>
> ***************
> *** 2774,2781 ****
> {
> if (patt[pos] == '|' && paren_depth == 0)
> {
> ! *prefix = NULL;
> ! *rest = patt;
> return Pattern_Prefix_None;
> }
> else if (patt[pos] == '(')
> --- 2822,2832 ----
> {
> if (patt[pos] == '|' && paren_depth == 0)
> {
> ! rest = patt;
> !
> ! *prefix_const = NULL;
> ! *rest_const = string_to_const(rest, typeid);
> !
> return Pattern_Prefix_None;
> }
> else if (patt[pos] == '(')
> ***************
> *** 2792,2798 ****
> }
>
> /* OK, allocate space for pattern */
> ! *prefix = match = palloc(strlen(patt) + 1);
> match_pos = 0;
>
> /* note start at pos 1 to skip leading ^ */
> --- 2843,2849 ----
> }
>
> /* OK, allocate space for pattern */
> ! prefix = match = palloc(strlen(patt) + 1);
> match_pos = 0;
>
> /* note start at pos 1 to skip leading ^ */
> ***************
> *** 2841,2865 ****
> }
>
> match[match_pos] = '\0';
> ! *rest = &patt[pos];
>
> if (patt[pos] == '$' && patt[pos + 1] == '\0')
> {
> ! *rest = &patt[pos + 1];
> return Pattern_Prefix_Exact; /* pattern specifies exact match */
> }
>
> if (match_pos > 0)
> return Pattern_Prefix_Partial;
>
> - pfree(match);
> - *prefix = NULL;
> return Pattern_Prefix_None;
> }
>
> Pattern_Prefix_Status
> ! pattern_fixed_prefix(char *patt, Pattern_Type ptype,
> ! char **prefix, char **rest)
> {
> Pattern_Prefix_Status result;
>
> --- 2892,2925 ----
> }
>
> match[match_pos] = '\0';
> ! rest = &patt[pos];
>
> if (patt[pos] == '$' && patt[pos + 1] == '\0')
> {
> ! rest = &patt[pos + 1];
> !
> ! *prefix_const = string_to_const(prefix, typeid);
> ! *rest_const = string_to_const(rest, typeid);
> !
> return Pattern_Prefix_Exact; /* pattern specifies exact match */
> }
>
> + *prefix_const = string_to_const(prefix, typeid);
> + *rest_const = string_to_const(rest, typeid);
> +
> + pfree(patt);
> + pfree(match);
> + prefix = NULL;
> +
> if (match_pos > 0)
> return Pattern_Prefix_Partial;
>
> return Pattern_Prefix_None;
> }
>
> Pattern_Prefix_Status
> ! pattern_fixed_prefix(Const *patt, Pattern_Type ptype,
> ! Const **prefix, Const **rest)
> {
> Pattern_Prefix_Status result;
>
> ***************
> *** 2897,2915 ****
> * more useful to use the upper-bound code than not.
> */
> static Selectivity
> ! prefix_selectivity(Query *root, Var *var, char *prefix)
> {
> Selectivity prefixsel;
> Oid cmpopr;
> ! Const *prefixcon;
> List *cmpargs;
> ! char *greaterstr;
>
> cmpopr = find_operator(">=", var->vartype);
> if (cmpopr == InvalidOid)
> elog(ERROR, "prefix_selectivity: no >= operator for type %u",
> var->vartype);
> ! prefixcon = string_to_const(prefix, var->vartype);
> cmpargs = makeList2(var, prefixcon);
> /* Assume scalargtsel is appropriate for all supported types */
> prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel,
> --- 2957,2979 ----
> * more useful to use the upper-bound code than not.
> */
> static Selectivity
> ! prefix_selectivity(Query *root, Var *var, Const *prefixcon)
> {
> Selectivity prefixsel;
> Oid cmpopr;
> ! char *prefix;
> List *cmpargs;
> ! Const *greaterstrcon;
>
> cmpopr = find_operator(">=", var->vartype);
> if (cmpopr == InvalidOid)
> elog(ERROR, "prefix_selectivity: no >= operator for type %u",
> var->vartype);
> ! if (prefixcon->consttype != BYTEAOID)
> ! prefix = DatumGetCString(DirectFunctionCall1(textout, prefixcon->constvalue));
> ! else
> ! prefix = DatumGetCString(DirectFunctionCall1(byteaout, prefixcon->constvalue));
> !
> cmpargs = makeList2(var, prefixcon);
> /* Assume scalargtsel is appropriate for all supported types */
> prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel,
> ***************
> *** 2923,2930 ****
> * "x < greaterstr".
> *-------
> */
> ! greaterstr = make_greater_string(prefix, var->vartype);
> ! if (greaterstr)
> {
> Selectivity topsel;
>
> --- 2987,2994 ----
> * "x < greaterstr".
> *-------
> */
> ! greaterstrcon = make_greater_string(prefixcon);
> ! if (greaterstrcon)
> {
> Selectivity topsel;
>
> ***************
> *** 2932,2939 ****
> if (cmpopr == InvalidOid)
> elog(ERROR, "prefix_selectivity: no < operator for type %u",
> var->vartype);
> ! prefixcon = string_to_const(greaterstr, var->vartype);
> ! cmpargs = makeList2(var, prefixcon);
> /* Assume scalarltsel is appropriate for all supported types */
> topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel,
> PointerGetDatum(root),
> --- 2996,3002 ----
> if (cmpopr == InvalidOid)
> elog(ERROR, "prefix_selectivity: no < operator for type %u",
> var->vartype);
> ! cmpargs = makeList2(var, greaterstrcon);
> /* Assume scalarltsel is appropriate for all supported types */
> topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel,
> PointerGetDatum(root),
> ***************
> *** 2997,3010 ****
> #define PARTIAL_WILDCARD_SEL 2.0
>
> static Selectivity
> ! like_selectivity(char *patt, bool case_insensitive)
> {
> Selectivity sel = 1.0;
> int pos;
>
> /* Skip any leading %; it's already factored into initial sel */
> ! pos = (*patt == '%') ? 1 : 0;
> ! for (; patt[pos]; pos++)
> {
> /* % and _ are wildcard characters in LIKE */
> if (patt[pos] == '%')
> --- 3060,3094 ----
> #define PARTIAL_WILDCARD_SEL 2.0
>
> static Selectivity
> ! like_selectivity(Const *patt_const, bool case_insensitive)
> {
> Selectivity sel = 1.0;
> int pos;
> + int start;
> + Oid typeid = patt_const->consttype;
> + char *patt;
> + int pattlen;
> +
> + /* the right-hand const is type text or bytea */
> + Assert(typeid == BYTEAOID || typeid == TEXTOID);
> +
> + if (typeid == BYTEAOID && case_insensitive)
> + elog(ERROR, "Cannot perform case insensitive matching on type BYTEA");
> +
> + if (typeid != BYTEAOID)
> + {
> + patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue));
> + pattlen = strlen(patt);
> + }
> + else
> + {
> + patt = DatumGetCString(DirectFunctionCall1(byteaout, patt_const->constvalue));
> + pattlen = toast_raw_datum_size(patt_const->constvalue) - VARHDRSZ;
> + }
>
> /* Skip any leading %; it's already factored into initial sel */
> ! start = (*patt == '%') ? 1 : 0;
> ! for (pos = start; pos < pattlen; pos++)
> {
> /* % and _ are wildcard characters in LIKE */
> if (patt[pos] == '%')
> ***************
> *** 3015,3021 ****
> {
> /* Backslash quotes the next character */
> pos++;
> ! if (patt[pos] == '\0')
> break;
> sel *= FIXED_CHAR_SEL;
> }
> --- 3099,3105 ----
> {
> /* Backslash quotes the next character */
> pos++;
> ! if (patt[pos] == '\0' && typeid != BYTEAOID)
> break;
> sel *= FIXED_CHAR_SEL;
> }
> ***************
> *** 3122,3131 ****
> }
>
> static Selectivity
> ! regex_selectivity(char *patt, bool case_insensitive)
> {
> Selectivity sel;
> ! int pattlen = strlen(patt);
>
> /* If patt doesn't end with $, consider it to have a trailing wildcard */
> if (pattlen > 0 && patt[pattlen - 1] == '$' &&
> --- 3206,3229 ----
> }
>
> static Selectivity
> ! regex_selectivity(Const *patt_const, bool case_insensitive)
> {
> Selectivity sel;
> ! char *patt;
> ! int pattlen;
> ! Oid typeid = patt_const->consttype;
> !
> ! /*
> ! * Should be unnecessary, there are no bytea regex operators defined.
> ! * As such, it should be noted that the rest of this function has *not*
> ! * been made safe for binary (possibly NULL containing) strings.
> ! */
> ! if (typeid == BYTEAOID)
> ! elog(ERROR, "Regex matching not supported on type BYTEA");
> !
> ! /* the right-hand const is type text for all of these */
> ! patt = DatumGetCString(DirectFunctionCall1(textout, patt_const->constvalue));
> ! pattlen = strlen(patt);
>
> /* If patt doesn't end with $, consider it to have a trailing wildcard */
> if (pattlen > 0 && patt[pattlen - 1] == '$' &&
> ***************
> *** 3146,3152 ****
> }
>
> static Selectivity
> ! pattern_selectivity(char *patt, Pattern_Type ptype)
> {
> Selectivity result;
>
> --- 3244,3250 ----
> }
>
> static Selectivity
> ! pattern_selectivity(Const *patt, Pattern_Type ptype)
> {
> Selectivity result;
>
> ***************
> *** 3220,3238 ****
> * sort passes, etc. For now, we just shut down the whole thing in locales
> * that do such things :-(
> */
> ! char *
> ! make_greater_string(const char *str, Oid datatype)
> {
> char *workstr;
> int len;
>
> ! /*
> ! * Make a modifiable copy, which will be our return value if
> ! * successful
> ! */
> ! workstr = pstrdup((char *) str);
>
> ! while ((len = strlen(workstr)) > 0)
> {
> unsigned char *lastchar = (unsigned char *) (workstr + len - 1);
>
> --- 3318,3350 ----
> * sort passes, etc. For now, we just shut down the whole thing in locales
> * that do such things :-(
> */
> ! Const *
> ! make_greater_string(const Const *str_const)
> {
> + Oid datatype = str_const->consttype;
> + char *str;
> char *workstr;
> int len;
>
> ! /* Get the string and a modifiable copy */
> ! if (datatype == NAMEOID)
> ! {
> ! str = DatumGetCString(DirectFunctionCall1(nameout, str_const->constvalue));
> ! len = strlen(str);
> ! }
> ! else if (datatype == BYTEAOID)
> ! {
> ! str = DatumGetCString(DirectFunctionCall1(byteaout, str_const->constvalue));
> ! len = toast_raw_datum_size(str_const->constvalue) - VARHDRSZ;
> ! }
> ! else
> ! {
> ! str = DatumGetCString(DirectFunctionCall1(textout, str_const->constvalue));
> ! len = strlen(str);
> ! }
> ! workstr = pstrdup(str);
>
> ! while (len > 0)
> {
> unsigned char *lastchar = (unsigned char *) (workstr + len - 1);
>
> ***************
> *** 3243,3262 ****
> {
> (*lastchar)++;
> if (string_lessthan(str, workstr, datatype))
> ! return workstr; /* Success! */
> }
>
> /*
> * Truncate off the last character, which might be more than 1
> * byte in MULTIBYTE case.
> */
> ! len = pg_mbcliplen((const unsigned char *) workstr, len, len - 1);
> ! workstr[len] = '\0';
> }
>
> /* Failed... */
> pfree(workstr);
> ! return NULL;
> }
>
> /*
> --- 3355,3388 ----
> {
> (*lastchar)++;
> if (string_lessthan(str, workstr, datatype))
> ! {
> ! /* Success! */
> ! Const *workstr_const = string_to_const(workstr, datatype);
> !
> ! pfree(str);
> ! pfree(workstr);
> ! return workstr_const;
> ! }
> }
>
> /*
> * Truncate off the last character, which might be more than 1
> * byte in MULTIBYTE case.
> */
> ! if (datatype != BYTEAOID && pg_database_encoding_max_length() > 1)
> ! len = pg_mbcliplen((const unsigned char *) workstr, len, len - 1);
> ! else
> ! len -= - 1;
> !
> ! if (datatype != BYTEAOID)
> ! workstr[len] = '\0';
> }
>
> /* Failed... */
> + pfree(str);
> pfree(workstr);
> !
> ! return (Const *) NULL;
> }
>
> /*
> ***************
> *** 3330,3341 ****
> --- 3456,3471 ----
> static Datum
> string_to_datum(const char *str, Oid datatype)
> {
> + Assert(str != NULL);
> +
> /*
> * We cheat a little by assuming that textin() will do for bpchar and
> * varchar constants too...
> */
> if (datatype == NAMEOID)
> return DirectFunctionCall1(namein, CStringGetDatum(str));
> + else if (datatype == BYTEAOID)
> + return DirectFunctionCall1(byteain, CStringGetDatum(str));
> else
> return DirectFunctionCall1(textin, CStringGetDatum(str));
> }
> Index: src/include/catalog/pg_operator.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_operator.h,v
> retrieving revision 1.107
> diff -c -r1.107 pg_operator.h
> *** src/include/catalog/pg_operator.h 22 Aug 2002 04:45:11 -0000 1.107
> --- src/include/catalog/pg_operator.h 1 Sep 2002 21:46:27 -0000
> ***************
> *** 827,835 ****
> DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel ));
> DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel ));
> DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel ));
> ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 25 16 0 2017 0 0 0 0 bytealike likesel likejoinsel ));
> #define OID_BYTEA_LIKE_OP 2016
> ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 25 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel ));
> DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - ));
>
> /* timestamp operators */
> --- 827,835 ----
> DATA(insert OID = 1958 ( "<=" PGNSP PGUID b f 17 17 16 1960 1959 0 0 0 0 byteale scalarltsel scalarltjoinsel ));
> DATA(insert OID = 1959 ( ">" PGNSP PGUID b f 17 17 16 1957 1958 0 0 0 0 byteagt scalargtsel scalargtjoinsel ));
> DATA(insert OID = 1960 ( ">=" PGNSP PGUID b f 17 17 16 1958 1957 0 0 0 0 byteage scalargtsel scalargtjoinsel ));
> ! DATA(insert OID = 2016 ( "~~" PGNSP PGUID b f 17 17 16 0 2017 0 0 0 0 bytealike likesel likejoinsel ));
> #define OID_BYTEA_LIKE_OP 2016
> ! DATA(insert OID = 2017 ( "!~~" PGNSP PGUID b f 17 17 16 0 2016 0 0 0 0 byteanlike nlikesel nlikejoinsel ));
> DATA(insert OID = 2018 ( "||" PGNSP PGUID b f 17 17 17 0 0 0 0 0 0 byteacat - - ));
>
> /* timestamp operators */
> Index: src/include/catalog/pg_proc.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_proc.h,v
> retrieving revision 1.267
> diff -c -r1.267 pg_proc.h
> *** src/include/catalog/pg_proc.h 1 Sep 2002 00:58:06 -0000 1.267
> --- src/include/catalog/pg_proc.h 1 Sep 2002 21:46:27 -0000
> ***************
> *** 2770,2782 ****
> DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ ));
> DESCR("adjust time with time zone precision");
>
> ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ ));
> DESCR("matches LIKE expression");
> ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ ));
> DESCR("does not match LIKE expression");
> ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 25" bytealike - _null_ ));
> DESCR("matches LIKE expression");
> ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 25" byteanlike - _null_ ));
> DESCR("does not match LIKE expression");
> DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ ));
> DESCR("convert match pattern to use backslash escapes");
> --- 2770,2782 ----
> DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ ));
> DESCR("adjust time with time zone precision");
>
> ! DATA(insert OID = 2005 ( bytealike PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ ));
> DESCR("matches LIKE expression");
> ! DATA(insert OID = 2006 ( byteanlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ ));
> DESCR("does not match LIKE expression");
> ! DATA(insert OID = 2007 ( like PGNSP PGUID 12 f f t f i 2 16 "17 17" bytealike - _null_ ));
> DESCR("matches LIKE expression");
> ! DATA(insert OID = 2008 ( notlike PGNSP PGUID 12 f f t f i 2 16 "17 17" byteanlike - _null_ ));
> DESCR("does not match LIKE expression");
> DATA(insert OID = 2009 ( like_escape PGNSP PGUID 12 f f t f i 2 17 "17 17" like_escape_bytea - _null_ ));
> DESCR("convert match pattern to use backslash escapes");
> Index: src/include/utils/selfuncs.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/utils/selfuncs.h,v
> retrieving revision 1.6
> diff -c -r1.6 selfuncs.h
> *** src/include/utils/selfuncs.h 20 Jun 2002 20:29:53 -0000 1.6
> --- src/include/utils/selfuncs.h 1 Sep 2002 21:46:27 -0000
> ***************
> *** 33,44 ****
>
> /* selfuncs.c */
>
> ! extern Pattern_Prefix_Status pattern_fixed_prefix(char *patt,
> Pattern_Type ptype,
> ! char **prefix,
> ! char **rest);
> extern bool locale_is_like_safe(void);
> ! extern char *make_greater_string(const char *str, Oid datatype);
>
> extern Datum eqsel(PG_FUNCTION_ARGS);
> extern Datum neqsel(PG_FUNCTION_ARGS);
> --- 33,44 ----
>
> /* selfuncs.c */
>
> ! extern Pattern_Prefix_Status pattern_fixed_prefix(Const *patt,
> Pattern_Type ptype,
> ! Const **prefix,
> ! Const **rest);
> extern bool locale_is_like_safe(void);
> ! extern Const *make_greater_string(const Const *str_const);
>
> extern Datum eqsel(PG_FUNCTION_ARGS);
> extern Datum neqsel(PG_FUNCTION_ARGS);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073