diff options
36 files changed, 1007 insertions, 1555 deletions
diff --git a/contrib/array/array_iterator.c b/contrib/array/array_iterator.c index d2a9a3271e6..9504f752779 100644 --- a/contrib/array/array_iterator.c +++ b/contrib/array/array_iterator.c @@ -146,43 +146,6 @@ array_all_textregexeq(ArrayType *array, void *value) } /* - * Iterator functions for type _varchar. Note that the regexp - * operators take the second argument of type text. - */ - -int32 -array_varchareq(ArrayType *array, void *value) -{ - return array_iterator(F_VARCHAREQ, - 0, /* logical or */ - array, (Datum) value); -} - -int32 -array_all_varchareq(ArrayType *array, void *value) -{ - return array_iterator(F_VARCHAREQ, - 1, /* logical and */ - array, (Datum) value); -} - -int32 -array_varcharregexeq(ArrayType *array, void *value) -{ - return array_iterator(F_TEXTREGEXEQ, - 0, /* logical or */ - array, (Datum) value); -} - -int32 -array_all_varcharregexeq(ArrayType *array, void *value) -{ - return array_iterator(F_TEXTREGEXEQ, - 1, /* logical and */ - array, (Datum) value); -} - -/* * Iterator functions for type _bpchar. Note that the regexp * operators take the second argument of type text. */ diff --git a/contrib/array/array_iterator.h b/contrib/array/array_iterator.h index c85d68f27ac..75cfba07dd7 100644 --- a/contrib/array/array_iterator.h +++ b/contrib/array/array_iterator.h @@ -9,11 +9,6 @@ int32 array_all_texteq(ArrayType *array, void *value); int32 array_textregexeq(ArrayType *array, void *value); int32 array_all_textregexeq(ArrayType *array, void *value); -int32 array_varchareq(ArrayType *array, void *value); -int32 array_all_varchareq(ArrayType *array, void *value); -int32 array_varcharregexeq(ArrayType *array, void *value); -int32 array_all_varcharregexeq(ArrayType *array, void *value); - int32 array_bpchareq(ArrayType *array, void *value); int32 array_all_bpchareq(ArrayType *array, void *value); int32 array_bpcharregexeq(ArrayType *array, void *value); diff --git a/contrib/array/array_iterator.sql.in b/contrib/array/array_iterator.sql.in index 4108a63eafd..2d89f2c9872 100644 --- a/contrib/array/array_iterator.sql.in +++ b/contrib/array/array_iterator.sql.in @@ -55,59 +55,6 @@ CREATE OPERATOR **~ ( ); --- define the array operators *=, **=, *~ and **~ for type _varchar --- --- NOTE: "varchar" is also a reserved word and must be quoted. --- -CREATE OR REPLACE FUNCTION array_varchareq(_varchar, varchar) -RETURNS bool -AS 'MODULE_PATHNAME' -LANGUAGE 'C' IMMUTABLE STRICT; - -CREATE OR REPLACE FUNCTION array_all_varchareq(_varchar, varchar) -RETURNS bool -AS 'MODULE_PATHNAME' -LANGUAGE 'C' IMMUTABLE STRICT; - -CREATE OR REPLACE FUNCTION array_varcharregexeq(_varchar, varchar) -RETURNS bool -AS 'MODULE_PATHNAME' -LANGUAGE 'C' IMMUTABLE STRICT; - -CREATE OR REPLACE FUNCTION array_all_varcharregexeq(_varchar, varchar) -RETURNS bool -AS 'MODULE_PATHNAME' -LANGUAGE 'C' IMMUTABLE STRICT; - -DROP OPERATOR *=(_varchar,"varchar"); -CREATE OPERATOR *= ( - LEFTARG=_varchar, - RIGHTARG="varchar", - PROCEDURE=array_varchareq -); - -DROP OPERATOR **=(_varchar,"varchar"); -CREATE OPERATOR **= ( - LEFTARG=_varchar, - RIGHTARG="varchar", - PROCEDURE=array_all_varchareq -); - -DROP OPERATOR *~(_varchar,"varchar"); -CREATE OPERATOR *~ ( - LEFTARG=_varchar, - RIGHTARG="varchar", - PROCEDURE=array_varcharregexeq -); - -DROP OPERATOR **~(_varchar,"varchar"); -CREATE OPERATOR **~ ( - LEFTARG=_varchar, - RIGHTARG="varchar", - PROCEDURE=array_all_varcharregexeq -); - - -- define the array operators *=, **=, *~ and **~ for type _bpchar -- CREATE OR REPLACE FUNCTION array_bpchareq(_bpchar, bpchar) diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index ea6fe8a7217..32505959644 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.189 2003/05/22 18:31:45 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.190 2003/05/26 00:11:27 tgl Exp $ --> <appendix id="release"> @@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without worries about funny characters. --> <literallayout><![CDATA[ +CHAR(n) to TEXT conversion automatically strips trailing blanks Pattern matching operations can use indexes regardless of locale New frontend/backend protocol supports many long-requested features SET AUTOCOMMIT TO OFF is no longer supported diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml index 0a85ea1230f..fa59aba0fea 100644 --- a/doc/src/sgml/typeconv.sgml +++ b/doc/src/sgml/typeconv.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/typeconv.sgml,v 1.30 2003/03/25 16:15:38 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/typeconv.sgml,v 1.31 2003/05/26 00:11:27 tgl Exp $ --> <chapter Id="typeconv"> @@ -45,7 +45,7 @@ mixed-type expressions to be meaningful even with user-defined types. <para> The <productname>PostgreSQL</productname> scanner/parser decodes lexical elements into only five fundamental categories: integers, floating-point numbers, strings, -names, and key words. Most extended types are first classified as +names, and key words. Constants of most non-numeric types are first classified as strings. The <acronym>SQL</acronym> language definition allows specifying type names with strings, and this mechanism can be used in <productname>PostgreSQL</productname> to start the parser down the correct @@ -134,8 +134,8 @@ The system catalogs store information about which conversions, called perform those conversions. Additional casts can be added by the user with the <command>CREATE CAST</command> command. (This is usually done in conjunction with defining new data types. The set of casts -between the built-in types has been carefully crafted and should not -be altered.) +between the built-in types has been carefully crafted and is best not +altered.) </para> <para> @@ -144,8 +144,8 @@ at proper behavior for <acronym>SQL</acronym> standard types. There are several basic <firstterm>type categories</firstterm> defined: <type>boolean</type>, <type>numeric</type>, <type>string</type>, <type>bitstring</type>, <type>datetime</type>, <type>timespan</type>, <type>geometric</type>, <type>network</type>, and user-defined. Each category, with the exception of user-defined, has -a <firstterm>preferred type</firstterm> which is preferentially selected -when there is ambiguity. +one or more <firstterm>preferred types</firstterm> which are preferentially +selected when there is ambiguity. In the user-defined category, each type is its own preferred type. Ambiguous expressions (those with multiple candidate parsing solutions) can therefore often be resolved when there are multiple possible built-in types, but @@ -175,7 +175,8 @@ be converted to a user-defined type (of course, only if conversion is necessary) <para> User-defined types are not related. Currently, <productname>PostgreSQL</productname> does not have information available to it on relationships between types, other than -hardcoded heuristics for built-in types and implicit relationships based on available functions. +hardcoded heuristics for built-in types and implicit relationships based on +available functions and casts. </para> </listitem> @@ -203,14 +204,15 @@ should use this new function and will no longer do the implicit conversion using <title>Operators</title> <para> - The operand types of an operator invocation are resolved following + The specific operator to be used in an operator invocation is determined + by following the procedure below. Note that this procedure is indirectly affected by the precedence of the involved operators. See <xref linkend="sql-precedence"> for more information. </para> <procedure> -<title>Operand Type Resolution</title> +<title>Operator Type Resolution</title> <step performance="required"> <para> @@ -271,22 +273,16 @@ candidate remains, use it; else continue to the next step. <step performance="required"> <para> Run through all candidates and keep those with the most exact matches -on input types. Keep all candidates if none have any exact matches. +on input types. (Domain types are considered the same as their base type +for this purpose.) Keep all candidates if none have any exact matches. If only one candidate remains, use it; else continue to the next step. </para> </step> <step performance="required"> <para> -Run through all candidates and keep those with the most exact or -binary-compatible matches on input types. Keep all candidates if none have -any exact or binary-compatible matches. -If only one candidate remains, use it; else continue to the next step. -</para> -</step> -<step performance="required"> -<para> -Run through all candidates and keep those that accept preferred types at -the most positions where type conversion will be required. +Run through all candidates and keep those that accept preferred types (of the +input datatype's type category) at the most positions where type conversion +will be required. Keep all candidates if none accept preferred types. If only one candidate remains, use it; else continue to the next step. </para> @@ -295,12 +291,13 @@ If only one candidate remains, use it; else continue to the next step. <para> If any input arguments are <type>unknown</type>, check the type categories accepted at those argument positions by the remaining -candidates. At each position, select the <literal>string</literal> category if any +candidates. At each position, select the <type>string</type> category +if any candidate accepts that category. (This bias towards string is appropriate since an unknown-type literal does look like a string.) Otherwise, if all the remaining candidates accept the same type category, select that category; otherwise fail because the correct choice cannot be deduced -without more clues. Now discard operator +without more clues. Now discard candidates that do not accept the selected type category. Furthermore, if any candidate accepts a preferred type at a given argument position, discard candidates that accept non-preferred types for that argument. @@ -455,12 +452,12 @@ SELECT CAST('20' AS int8) ! AS "factorial"; <title>Functions</title> <para> - The argument types of function calls are resolved according to the - following steps. + The specific function to be used in a function invocation is determined + according to the following steps. </para> <procedure> -<title>Function Argument Type Resolution</title> +<title>Function Type Resolution</title> <step performance="required"> <para> @@ -523,29 +520,24 @@ candidate remains, use it; else continue to the next step. <step performance="required"> <para> Run through all candidates and keep those with the most exact matches -on input types. Keep all candidates if none have any exact matches. -If only one candidate remains, use it; else continue to the next step. -</para> -</step> -<step performance="required"> -<para> -Run through all candidates and keep those with the most exact or -binary-compatible matches on input types. Keep all candidates if none have -any exact or binary-compatible matches. +on input types. (Domain types are considered the same as their base type +for this purpose.) Keep all candidates if none have any exact matches. If only one candidate remains, use it; else continue to the next step. </para> </step> <step performance="required"> <para> -Run through all candidates and keep those that accept preferred types at -the most positions where type conversion will be required. +Run through all candidates and keep those that accept preferred types (of the +input datatype's type category) at the most positions where type conversion +will be required. Keep all candidates if none accept preferred types. If only one candidate remains, use it; else continue to the next step. </para> </step> <step performance="required"> <para> -If any input arguments are <type>unknown</type>, check the type categories accepted +If any input arguments are <type>unknown</type>, check the type categories +accepted at those argument positions by the remaining candidates. At each position, select the <type>string</type> category if any candidate accepts that category. (This bias towards string @@ -553,8 +545,8 @@ is appropriate since an unknown-type literal does look like a string.) Otherwise, if all the remaining candidates accept the same type category, select that category; otherwise fail because the correct choice cannot be deduced without more clues. -Now discard candidates that do not accept the selected type category; -furthermore, if any candidate accepts a preferred type at a given argument +Now discard candidates that do not accept the selected type category. +Furthermore, if any candidate accepts a preferred type at a given argument position, discard candidates that accept non-preferred types for that argument. </para> @@ -571,6 +563,8 @@ then fail. </procedure> <para> +Note that the <quote>best match</> rules are identical for operator and +function type resolution. Some examples follow. </para> @@ -649,7 +643,8 @@ SELECT substr(CAST (varchar '1234' AS text), 3); <para> <note> <para> -The parser is aware that <type>text</type> and <type>varchar</type> +The parser learns from the <structname>pg_cast</> catalog that +<type>text</type> and <type>varchar</type> are binary-compatible, meaning that one can be passed to a function that accepts the other without doing any physical conversion. Therefore, no explicit type conversion call is really inserted in this case. diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index b4da4666f1f..4529c30e626 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v 1.139 2003/05/15 19:34:46 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v 1.140 2003/05/26 00:11:27 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -20,6 +20,7 @@ #include "access/nbtree.h" #include "catalog/pg_amop.h" #include "catalog/pg_namespace.h" +#include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_type.h" #include "executor/executor.h" @@ -30,6 +31,7 @@ #include "optimizer/paths.h" #include "optimizer/restrictinfo.h" #include "optimizer/var.h" +#include "parser/parse_expr.h" #include "rewrite/rewriteManip.h" #include "utils/builtins.h" #include "utils/catcache.h" @@ -80,17 +82,18 @@ static bool pred_test_simple_clause(Expr *predicate, Node *clause); static Relids indexable_outerrelids(RelOptInfo *rel, IndexOptInfo *index); static Path *make_innerjoin_index_path(Query *root, RelOptInfo *rel, IndexOptInfo *index, - List *clausegroup); + List *clausegroups); static bool match_index_to_operand(int indexkey, Node *operand, RelOptInfo *rel, IndexOptInfo *index); static bool function_index_operand(Expr *funcOpnd, RelOptInfo *rel, IndexOptInfo *index); static bool match_special_index_operator(Expr *clause, Oid opclass, bool indexkey_on_left); -static List *prefix_quals(Node *leftop, Oid expr_op, +static List *expand_indexqual_condition(Expr *clause, Oid opclass); +static List *prefix_quals(Node *leftop, Oid opclass, Const *prefix, Pattern_Prefix_Status pstatus); -static List *network_prefix_quals(Node *leftop, Oid expr_op, Datum rightop); -static Oid find_operator(const char *opname, Oid datatype); +static List *network_prefix_quals(Node *leftop, Oid expr_op, Oid opclass, + Datum rightop); static Datum string_to_datum(const char *str, Oid datatype); static Const *string_to_const(const char *str, Oid datatype); @@ -411,7 +414,7 @@ match_or_subclause_to_indexkey(RelOptInfo *rel, * Currently we'll end up rechecking both the OR clause and the transferred * restriction clause as qpquals. FIXME someday.) * - * Also, we apply expand_indexqual_conditions() to convert any special + * Also, we apply expand_indexqual_condition() to convert any special * matching opclauses to indexable operators. * * The passed-in clause is not changed. @@ -430,7 +433,7 @@ extract_or_indexqual_conditions(RelOptInfo *rel, * Extract relevant indexclauses in indexkey order. This is * essentially just like group_clauses_by_indexkey() except that the * input and output are lists of bare clauses, not of RestrictInfo - * nodes. + * nodes, and that we expand special operators immediately. */ do { @@ -448,13 +451,15 @@ extract_or_indexqual_conditions(RelOptInfo *rel, if (match_clause_to_indexkey(rel, index, curIndxKey, curClass, subsubclause)) - clausegroup = lappend(clausegroup, subsubclause); + clausegroup = nconc(clausegroup, + expand_indexqual_condition(subsubclause, + curClass)); } } else if (match_clause_to_indexkey(rel, index, curIndxKey, curClass, orsubclause)) - clausegroup = makeList1(orsubclause); + clausegroup = expand_indexqual_condition(orsubclause, curClass); /* * If we found no clauses for this indexkey in the OR subclause @@ -469,7 +474,9 @@ extract_or_indexqual_conditions(RelOptInfo *rel, if (match_clause_to_indexkey(rel, index, curIndxKey, curClass, rinfo->clause)) - clausegroup = lappend(clausegroup, rinfo->clause); + clausegroup = nconc(clausegroup, + expand_indexqual_condition(rinfo->clause, + curClass)); } } @@ -490,7 +497,7 @@ extract_or_indexqual_conditions(RelOptInfo *rel, if (quals == NIL) elog(ERROR, "extract_or_indexqual_conditions: no matching clause"); - return expand_indexqual_conditions(quals); + return quals; } @@ -501,26 +508,23 @@ extract_or_indexqual_conditions(RelOptInfo *rel, /* * group_clauses_by_indexkey - * Generates a list of restriction clauses that can be used with an index. + * Find restriction clauses that can be used with an index. * * 'rel' is the node of the relation itself. * 'index' is a index on 'rel'. * - * Returns a list of all the RestrictInfo nodes for clauses that can be - * used with this index. - * - * The list is ordered by index key. (This is not depended on by any part - * of the planner, so far as I can tell; but some parts of the executor - * do assume that the indxqual list ultimately delivered to the executor - * is so ordered. One such place is _bt_orderkeys() in the btree support. - * Perhaps that ought to be fixed someday --- tgl 7/00) + * Returns a list of sublists of RestrictInfo nodes for clauses that can be + * used with this index. Each sublist contains clauses that can be used + * with one index key (in no particular order); the top list is ordered by + * index key. (This is depended on by expand_indexqual_conditions().) * * Note that in a multi-key index, we stop if we find a key that cannot be * used with any clause. For example, given an index on (A,B,C), we might - * return (C1 C2 C3 C4) if we find that clauses C1 and C2 use column A, + * return ((C1 C2) (C3 C4)) if we find that clauses C1 and C2 use column A, * clauses C3 and C4 use column B, and no clauses use column C. But if - * no clauses match B we will return (C1 C2), whether or not there are + * no clauses match B we will return ((C1 C2)), whether or not there are * clauses matching column C, because the executor couldn't use them anyway. + * Therefore, there are no empty sublists in the result. */ static List * group_clauses_by_indexkey(RelOptInfo *rel, IndexOptInfo *index) @@ -559,20 +563,19 @@ group_clauses_by_indexkey(RelOptInfo *rel, IndexOptInfo *index) if (clausegroup == NIL) break; - clausegroup_list = nconc(clausegroup_list, clausegroup); + clausegroup_list = lappend(clausegroup_list, clausegroup); indexkeys++; classes++; } while (!DoneMatchingIndexKeys(indexkeys, classes)); - /* clausegroup_list holds all matched clauses ordered by indexkeys */ return clausegroup_list; } /* * group_clauses_by_indexkey_for_join - * Generates a list of clauses that can be used with an index + * Generate a list of sublists of clauses that can be used with an index * to scan the inner side of a nestloop join. * * This is much like group_clauses_by_indexkey(), but we consider both @@ -652,23 +655,20 @@ group_clauses_by_indexkey_for_join(RelOptInfo *rel, IndexOptInfo *index, if (clausegroup == NIL) break; - clausegroup_list = nconc(clausegroup_list, clausegroup); + clausegroup_list = lappend(clausegroup_list, clausegroup); indexkeys++; classes++; } while (!DoneMatchingIndexKeys(indexkeys, classes)); - /* - * if no join clause was matched then forget it, per comments above. - */ + /* if no join clause was matched then forget it, per comments above */ if (!jfound) { freeList(clausegroup_list); return NIL; } - /* clausegroup_list holds all matched clauses ordered by indexkeys */ return clausegroup_list; } @@ -1124,8 +1124,6 @@ pred_test_simple_clause(Expr *predicate, Node *clause) ExprState *test_exprstate; Datum test_result; bool isNull; - HeapTuple test_tuple; - Form_pg_amop test_form; CatCList *catlist; int i; EState *estate; @@ -1241,22 +1239,13 @@ pred_test_simple_clause(Expr *predicate, Node *clause) /* * 3. From the same opclass, find the operator for the test strategy */ - test_tuple = SearchSysCache(AMOPSTRATEGY, - ObjectIdGetDatum(opclass_id), - Int16GetDatum(test_strategy), - 0, 0); - if (!HeapTupleIsValid(test_tuple)) + test_op = get_opclass_member(opclass_id, test_strategy); + if (!OidIsValid(test_op)) { /* This should not fail, else pg_amop entry is missing */ elog(ERROR, "Missing pg_amop entry for opclass %u strategy %d", opclass_id, test_strategy); } - test_form = (Form_pg_amop) GETSTRUCT(test_tuple); - - /* Get the test operator */ - test_op = test_form->amopopr; - - ReleaseSysCache(test_tuple); /* * 4. Evaluate the test. For this we need an EState. @@ -1488,22 +1477,18 @@ best_inner_indexscan(Query *root, RelOptInfo *rel, if (jlist == NIL) /* failed to find a match? */ { - List *clausegroup; + List *clausegroups; /* find useful clauses for this index and outerjoin set */ - clausegroup = group_clauses_by_indexkey_for_join(rel, - index, - index_outer_relids, - isouterjoin); - if (clausegroup) + clausegroups = group_clauses_by_indexkey_for_join(rel, + index, + index_outer_relids, + isouterjoin); + if (clausegroups) { - /* remove duplicate and redundant clauses */ - clausegroup = remove_redundant_join_clauses(root, - clausegroup, - jointype); /* make the path */ path = make_innerjoin_index_path(root, rel, index, - clausegroup); + clausegroups); } /* Cache the result --- whether positive or negative */ @@ -1542,15 +1527,17 @@ best_inner_indexscan(Query *root, RelOptInfo *rel, * relation in a nestloop join. * * 'rel' is the relation for which 'index' is defined - * 'clausegroup' is a list of restrictinfo nodes that can use 'index' + * 'clausegroups' is a list of lists of RestrictInfos that can use 'index' */ static Path * make_innerjoin_index_path(Query *root, RelOptInfo *rel, IndexOptInfo *index, - List *clausegroup) + List *clausegroups) { IndexPath *pathnode = makeNode(IndexPath); - List *indexquals; + List *indexquals, + *allclauses, + *l; /* XXX this code ought to be merged with create_index_path? */ @@ -1564,11 +1551,8 @@ make_innerjoin_index_path(Query *root, */ pathnode->path.pathkeys = NIL; - /* Extract bare indexqual clauses from restrictinfos */ - indexquals = get_actual_clauses(clausegroup); - - /* expand special operators to indexquals the executor can handle */ - indexquals = expand_indexqual_conditions(indexquals); + /* Convert RestrictInfo nodes to indexquals the executor can handle */ + indexquals = expand_indexqual_conditions(index, clausegroups); /* * Note that we are making a pathnode for a single-scan indexscan; @@ -1583,24 +1567,31 @@ make_innerjoin_index_path(Query *root, /* * We must compute the estimated number of output rows for the * indexscan. This is less than rel->rows because of the - * additional selectivity of the join clauses. Since clausegroup + * additional selectivity of the join clauses. Since clausegroups * may contain both restriction and join clauses, we have to do a * set union to get the full set of clauses that must be - * considered to compute the correct selectivity. (We can't just - * nconc the two lists; then we might have some restriction - * clauses appearing twice, which'd mislead - * restrictlist_selectivity into double-counting their + * considered to compute the correct selectivity. (Without the union + * operation, we might have some restriction clauses appearing twice, + * which'd mislead restrictlist_selectivity into double-counting their * selectivity. However, since RestrictInfo nodes aren't copied when * linking them into different lists, it should be sufficient to use * pointer comparison to remove duplicates.) * + * We assume we can destructively modify the input sublists. + * * Always assume the join type is JOIN_INNER; even if some of the * join clauses come from other contexts, that's not our problem. */ + allclauses = NIL; + foreach(l, clausegroups) + { + /* nconc okay here since same clause couldn't be in two sublists */ + allclauses = nconc(allclauses, (List *) lfirst(l)); + } + allclauses = set_ptrUnion(rel->baserestrictinfo, allclauses); pathnode->rows = rel->tuples * restrictlist_selectivity(root, - set_ptrUnion(rel->baserestrictinfo, - clausegroup), + allclauses, rel->relid, JOIN_INNER); /* Like costsize.c, force estimate to be at least one row */ @@ -1741,10 +1732,10 @@ function_index_operand(Expr *funcOpnd, RelOptInfo *rel, IndexOptInfo *index) * the latter fails to recognize a restriction opclause's operator * as a member of an index's opclass, it asks match_special_index_operator() * whether the clause should be considered an indexqual anyway. - * expand_indexqual_conditions() converts a list of "raw" indexqual - * conditions (with implicit AND semantics across list elements) into - * a list that the executor can actually handle. For operators that - * are members of the index's opclass this transformation is a no-op, + * expand_indexqual_conditions() converts a list of lists of RestrictInfo + * nodes (with implicit AND semantics across list elements) into + * a list of clauses that the executor can actually handle. For operators + * that are members of the index's opclass this transformation is a no-op, * but operators recognized by match_special_index_operator() must be * converted into one or more "regular" indexqual conditions. *---------- @@ -1765,10 +1756,9 @@ match_special_index_operator(Expr *clause, Oid opclass, bool indexkey_on_left) { bool isIndexable = false; - Node *leftop, - *rightop; + Node *rightop; Oid expr_op; - Const *patt = NULL; + Const *patt; Const *prefix = NULL; Const *rest = NULL; @@ -1781,7 +1771,6 @@ match_special_index_operator(Expr *clause, Oid opclass, return false; /* we know these will succeed */ - leftop = get_leftop(clause); rightop = get_rightop(clause); expr_op = ((OpExpr *) clause)->opno; @@ -1795,7 +1784,6 @@ match_special_index_operator(Expr *clause, Oid opclass, { case OID_TEXT_LIKE_OP: 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 */ isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like, @@ -1809,7 +1797,6 @@ match_special_index_operator(Expr *clause, Oid opclass, 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 */ isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, @@ -1818,7 +1805,6 @@ match_special_index_operator(Expr *clause, Oid opclass, 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 */ isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex, @@ -1827,7 +1813,6 @@ match_special_index_operator(Expr *clause, Oid opclass, 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 */ isIndexable = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, @@ -1855,8 +1840,11 @@ match_special_index_operator(Expr *clause, Oid opclass, /* * Must also check that index's opclass supports the operators we will * want to apply. (A hash index, for example, will not support ">=".) - * We cheat a little by not checking for availability of "=" ... any - * index type should support "=", methinks. + * Currently, only btree supports the operators we need. + * + * We insist on the opclass being the specific one we expect, + * else we'd do the wrong thing if someone were to make a reverse-sort + * opclass with the same operators. */ switch (expr_op) { @@ -1864,69 +1852,44 @@ match_special_index_operator(Expr *clause, Oid opclass, case OID_TEXT_ICLIKE_OP: case OID_TEXT_REGEXEQ_OP: case OID_TEXT_ICREGEXEQ_OP: - if (lc_collate_is_c()) - isIndexable = (op_in_opclass(find_operator(">=", TEXTOID), opclass) - && op_in_opclass(find_operator("<", TEXTOID), opclass)); - else - isIndexable = (op_in_opclass(find_operator("~>=~", TEXTOID), opclass) - && op_in_opclass(find_operator("~<~", TEXTOID), opclass)); - break; - - case OID_BYTEA_LIKE_OP: - isIndexable = (op_in_opclass(find_operator(">=", BYTEAOID), opclass) - && op_in_opclass(find_operator("<", BYTEAOID), opclass)); + /* text operators will be used for varchar inputs, too */ + isIndexable = + (opclass == TEXT_PATTERN_BTREE_OPS_OID) || + (opclass == TEXT_BTREE_OPS_OID && lc_collate_is_c()) || + (opclass == VARCHAR_PATTERN_BTREE_OPS_OID) || + (opclass == VARCHAR_BTREE_OPS_OID && lc_collate_is_c()); break; case OID_BPCHAR_LIKE_OP: case OID_BPCHAR_ICLIKE_OP: case OID_BPCHAR_REGEXEQ_OP: case OID_BPCHAR_ICREGEXEQ_OP: - if (lc_collate_is_c()) - isIndexable = (op_in_opclass(find_operator(">=", BPCHAROID), opclass) - && op_in_opclass(find_operator("<", BPCHAROID), opclass)); - else - isIndexable = (op_in_opclass(find_operator("~>=~", BPCHAROID), opclass) - && op_in_opclass(find_operator("~<~", BPCHAROID), opclass)); - break; - - case OID_VARCHAR_LIKE_OP: - case OID_VARCHAR_ICLIKE_OP: - case OID_VARCHAR_REGEXEQ_OP: - case OID_VARCHAR_ICREGEXEQ_OP: - if (lc_collate_is_c()) - isIndexable = (op_in_opclass(find_operator(">=", VARCHAROID), opclass) - && op_in_opclass(find_operator("<", VARCHAROID), opclass)); - else - isIndexable = (op_in_opclass(find_operator("~>=~", VARCHAROID), opclass) - && op_in_opclass(find_operator("~<~", VARCHAROID), opclass)); + isIndexable = + (opclass == BPCHAR_PATTERN_BTREE_OPS_OID) || + (opclass == BPCHAR_BTREE_OPS_OID && lc_collate_is_c()); break; case OID_NAME_LIKE_OP: case OID_NAME_ICLIKE_OP: case OID_NAME_REGEXEQ_OP: case OID_NAME_ICREGEXEQ_OP: - if (lc_collate_is_c()) - isIndexable = (op_in_opclass(find_operator(">=", NAMEOID), opclass) - && op_in_opclass(find_operator("<", NAMEOID), opclass)); - else - isIndexable = (op_in_opclass(find_operator("~>=~", NAMEOID), opclass) - && op_in_opclass(find_operator("~<~", NAMEOID), opclass)); + isIndexable = + (opclass == NAME_PATTERN_BTREE_OPS_OID) || + (opclass == NAME_BTREE_OPS_OID && lc_collate_is_c()); + break; + + case OID_BYTEA_LIKE_OP: + isIndexable = (opclass == BYTEA_BTREE_OPS_OID); break; case OID_INET_SUB_OP: case OID_INET_SUBEQ_OP: - /* for SUB we actually need ">" not ">=", but this should do */ - if (!op_in_opclass(find_operator(">=", INETOID), opclass) || - !op_in_opclass(find_operator("<=", INETOID), opclass)) - isIndexable = false; + isIndexable = (opclass == INET_BTREE_OPS_OID); break; case OID_CIDR_SUB_OP: case OID_CIDR_SUBEQ_OP: - /* for SUB we actually need ">" not ">=", but this should do */ - if (!op_in_opclass(find_operator(">=", CIDROID), opclass) || - !op_in_opclass(find_operator("<=", CIDROID), opclass)) - isIndexable = false; + isIndexable = (opclass == CIDR_BTREE_OPS_OID); break; } @@ -1935,185 +1898,217 @@ match_special_index_operator(Expr *clause, Oid opclass, /* * expand_indexqual_conditions - * Given a list of (implicitly ANDed) indexqual clauses, - * expand any "special" index operators into clauses that the indexscan - * machinery will know what to do with. Clauses that were not - * recognized by match_special_index_operator() must be passed through - * unchanged. + * Given a list of sublists of RestrictInfo nodes, produce a flat list + * of index qual clauses. Standard qual clauses (those in the index's + * opclass) are passed through unchanged. "Special" index operators + * are expanded into clauses that the indexscan machinery will know + * what to do with. + * + * The input list is ordered by index key, and so the output list is too. + * (The latter is not depended on by any part of the planner, so far as I can + * tell; but some parts of the executor do assume that the indxqual list + * ultimately delivered to the executor is so ordered. One such place is + * _bt_orderkeys() in the btree support. Perhaps that ought to be fixed + * someday --- tgl 7/00) */ List * -expand_indexqual_conditions(List *indexquals) +expand_indexqual_conditions(IndexOptInfo *index, List *clausegroups) { List *resultquals = NIL; - List *q; + int *indexkeys = index->indexkeys; + Oid *classes = index->classlist; + + if (clausegroups == NIL) + return NIL; - foreach(q, indexquals) + do { - Expr *clause = (Expr *) lfirst(q); - - /* we know these will succeed */ - Node *leftop = get_leftop(clause); - Node *rightop = get_rightop(clause); - Oid expr_op = ((OpExpr *) clause)->opno; - Const *patt = (Const *) rightop; - Const *prefix = NULL; - Const *rest = NULL; - Pattern_Prefix_Status pstatus; - - switch (expr_op) + Oid curClass = classes[0]; + List *i; + + foreach(i, (List *) lfirst(clausegroups)) { - /* - * LIKE and regex operators are not members of any index - * opclass, so if we find one in an indexqual list we can - * assume that it was accepted by - * match_special_index_operator(). - */ - case OID_TEXT_LIKE_OP: - 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; + RestrictInfo *rinfo = (RestrictInfo *) lfirst(i); - 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 */ - pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, - &prefix, &rest); - resultquals = nconc(resultquals, - prefix_quals(leftop, expr_op, - prefix, pstatus)); - break; + resultquals = nconc(resultquals, + expand_indexqual_condition(rinfo->clause, + curClass)); + } - 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 */ - pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex, - &prefix, &rest); - resultquals = nconc(resultquals, - prefix_quals(leftop, expr_op, - prefix, pstatus)); - break; + clausegroups = lnext(clausegroups); - 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 */ - pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex_IC, - &prefix, &rest); - resultquals = nconc(resultquals, - prefix_quals(leftop, expr_op, - prefix, pstatus)); - break; + indexkeys++; + classes++; - 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; + } while (clausegroups != NIL && + !DoneMatchingIndexKeys(indexkeys, classes)); - default: - resultquals = lappend(resultquals, clause); - break; - } - } + Assert(clausegroups == NIL); /* else more groups than indexkeys... */ return resultquals; } /* + * expand_indexqual_condition --- expand a single indexqual condition + */ +static List * +expand_indexqual_condition(Expr *clause, Oid opclass) +{ + /* we know these will succeed */ + Node *leftop = get_leftop(clause); + Node *rightop = get_rightop(clause); + Oid expr_op = ((OpExpr *) clause)->opno; + Const *patt = (Const *) rightop; + Const *prefix = NULL; + Const *rest = NULL; + Pattern_Prefix_Status pstatus; + List *result; + + switch (expr_op) + { + /* + * LIKE and regex operators are not members of any index + * opclass, so if we find one in an indexqual list we can + * assume that it was accepted by match_special_index_operator(). + */ + case OID_TEXT_LIKE_OP: + case OID_BPCHAR_LIKE_OP: + case OID_NAME_LIKE_OP: + case OID_BYTEA_LIKE_OP: + pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like, + &prefix, &rest); + result = prefix_quals(leftop, opclass, prefix, pstatus); + break; + + case OID_TEXT_ICLIKE_OP: + case OID_BPCHAR_ICLIKE_OP: + case OID_NAME_ICLIKE_OP: + /* the right-hand const is type text for all of these */ + pstatus = pattern_fixed_prefix(patt, Pattern_Type_Like_IC, + &prefix, &rest); + result = prefix_quals(leftop, opclass, prefix, pstatus); + break; + + case OID_TEXT_REGEXEQ_OP: + case OID_BPCHAR_REGEXEQ_OP: + case OID_NAME_REGEXEQ_OP: + /* the right-hand const is type text for all of these */ + pstatus = pattern_fixed_prefix(patt, Pattern_Type_Regex, + &prefix, &rest); + result = prefix_quals(leftop, opclass, prefix, pstatus); + break; + + case OID_TEXT_ICREGEXEQ_OP: + case OID_BPCHAR_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); + result = prefix_quals(leftop, opclass, prefix, pstatus); + break; + + case OID_INET_SUB_OP: + case OID_INET_SUBEQ_OP: + case OID_CIDR_SUB_OP: + case OID_CIDR_SUBEQ_OP: + result = network_prefix_quals(leftop, expr_op, opclass, + patt->constvalue); + break; + + default: + result = makeList1(clause); + break; + } + + return result; +} + +/* * Given a fixed prefix that all the "leftop" values must have, - * generate suitable indexqual condition(s). expr_op is the original - * LIKE or regex operator; we use it to deduce the appropriate comparison + * generate suitable indexqual condition(s). opclass is the index + * operator class; we use it to deduce the appropriate comparison * operators and operand datatypes. */ static List * -prefix_quals(Node *leftop, Oid expr_op, +prefix_quals(Node *leftop, Oid opclass, Const *prefix_const, Pattern_Prefix_Status pstatus) { List *result; Oid datatype; Oid oproid; - const char *oprname; - char *prefix; - Const *con; Expr *expr; - Const *greaterstr = NULL; + Const *greaterstr; Assert(pstatus != Pattern_Prefix_None); - switch (expr_op) + switch (opclass) { - case OID_TEXT_LIKE_OP: - case OID_TEXT_ICLIKE_OP: - case OID_TEXT_REGEXEQ_OP: - case OID_TEXT_ICREGEXEQ_OP: + case TEXT_BTREE_OPS_OID: + case TEXT_PATTERN_BTREE_OPS_OID: datatype = TEXTOID; break; - case OID_BYTEA_LIKE_OP: - datatype = BYTEAOID; + case VARCHAR_BTREE_OPS_OID: + case VARCHAR_PATTERN_BTREE_OPS_OID: + datatype = VARCHAROID; break; - case OID_BPCHAR_LIKE_OP: - case OID_BPCHAR_ICLIKE_OP: - case OID_BPCHAR_REGEXEQ_OP: - case OID_BPCHAR_ICREGEXEQ_OP: + case BPCHAR_BTREE_OPS_OID: + case BPCHAR_PATTERN_BTREE_OPS_OID: datatype = BPCHAROID; break; - case OID_VARCHAR_LIKE_OP: - case OID_VARCHAR_ICLIKE_OP: - case OID_VARCHAR_REGEXEQ_OP: - case OID_VARCHAR_ICREGEXEQ_OP: - datatype = VARCHAROID; + case NAME_BTREE_OPS_OID: + case NAME_PATTERN_BTREE_OPS_OID: + datatype = NAMEOID; break; - case OID_NAME_LIKE_OP: - case OID_NAME_ICLIKE_OP: - case OID_NAME_REGEXEQ_OP: - case OID_NAME_ICREGEXEQ_OP: - datatype = NAMEOID; + case BYTEA_BTREE_OPS_OID: + datatype = BYTEAOID; break; default: - elog(ERROR, "prefix_quals: unexpected operator %u", expr_op); + elog(ERROR, "prefix_quals: unexpected opclass %u", opclass); return NIL; } - /* Prefix constant is text for all except BYTEA_LIKE */ - if (datatype != BYTEAOID) - prefix = DatumGetCString(DirectFunctionCall1(textout, - prefix_const->constvalue)); - else - prefix = DatumGetCString(DirectFunctionCall1(byteaout, - prefix_const->constvalue)); + /* + * If necessary, coerce the prefix constant to the right type. + * The given prefix constant is either text or bytea type. + */ + if (prefix_const->consttype != datatype) + { + char *prefix; + + switch (prefix_const->consttype) + { + case TEXTOID: + prefix = DatumGetCString(DirectFunctionCall1(textout, + prefix_const->constvalue)); + break; + case BYTEAOID: + prefix = DatumGetCString(DirectFunctionCall1(byteaout, + prefix_const->constvalue)); + break; + default: + elog(ERROR, "prefix_quals: unexpected consttype %u", + prefix_const->consttype); + return NIL; + } + prefix_const = string_to_const(prefix, datatype); + pfree(prefix); + } /* * If we found an exact-match pattern, generate an "=" indexqual. */ if (pstatus == Pattern_Prefix_Exact) { - oprname = (datatype == BYTEAOID || lc_collate_is_c() ? "=" : "~=~"); - oproid = find_operator(oprname, datatype); + oproid = get_opclass_member(opclass, BTEqualStrategyNumber); if (oproid == InvalidOid) - elog(ERROR, "prefix_quals: no operator %s for type %u", oprname, datatype); - con = string_to_const(prefix, datatype); + elog(ERROR, "prefix_quals: no operator = for opclass %u", opclass); expr = make_opclause(oproid, BOOLOID, false, - (Expr *) leftop, (Expr *) con); + (Expr *) leftop, (Expr *) prefix_const); result = makeList1(expr); return result; } @@ -2123,13 +2118,11 @@ prefix_quals(Node *leftop, Oid expr_op, * * We can always say "x >= prefix". */ - oprname = (datatype == BYTEAOID || lc_collate_is_c() ? ">=" : "~>=~"); - oproid = find_operator(oprname, datatype); + oproid = get_opclass_member(opclass, BTGreaterEqualStrategyNumber); if (oproid == InvalidOid) - elog(ERROR, "prefix_quals: no operator %s for type %u", oprname, datatype); - con = string_to_const(prefix, datatype); + elog(ERROR, "prefix_quals: no operator >= for opclass %u", opclass); expr = make_opclause(oproid, BOOLOID, false, - (Expr *) leftop, (Expr *) con); + (Expr *) leftop, (Expr *) prefix_const); result = makeList1(expr); /*------- @@ -2137,13 +2130,12 @@ prefix_quals(Node *leftop, Oid expr_op, * "x < greaterstr". *------- */ - greaterstr = make_greater_string(con); + greaterstr = make_greater_string(prefix_const); if (greaterstr) { - oprname = (datatype == BYTEAOID || lc_collate_is_c() ? "<" : "~<~"); - oproid = find_operator(oprname, datatype); + oproid = get_opclass_member(opclass, BTLessStrategyNumber); if (oproid == InvalidOid) - elog(ERROR, "prefix_quals: no operator %s for type %u", oprname, datatype); + elog(ERROR, "prefix_quals: no operator < for opclass %u", opclass); expr = make_opclause(oproid, BOOLOID, false, (Expr *) leftop, (Expr *) greaterstr); result = lappend(result, expr); @@ -2155,19 +2147,18 @@ prefix_quals(Node *leftop, Oid expr_op, /* * Given a leftop and a rightop, and a inet-class sup/sub operator, * generate suitable indexqual condition(s). expr_op is the original - * operator. + * operator, and opclass is the index opclass. */ static List * -network_prefix_quals(Node *leftop, Oid expr_op, Datum rightop) +network_prefix_quals(Node *leftop, Oid expr_op, Oid opclass, Datum rightop) { bool is_eq; - char *opr1name; - Datum opr1right; - Datum opr2right; + Oid datatype; Oid opr1oid; Oid opr2oid; + Datum opr1right; + Datum opr2right; List *result; - Oid datatype; Expr *expr; switch (expr_op) @@ -2198,12 +2189,20 @@ network_prefix_quals(Node *leftop, Oid expr_op, Datum rightop) * create clause "key >= network_scan_first( rightop )", or ">" if the * operator disallows equality. */ - - opr1name = is_eq ? ">=" : ">"; - opr1oid = find_operator(opr1name, datatype); - if (opr1oid == InvalidOid) - elog(ERROR, "network_prefix_quals: no %s operator for type %u", - opr1name, datatype); + if (is_eq) + { + opr1oid = get_opclass_member(opclass, BTGreaterEqualStrategyNumber); + if (opr1oid == InvalidOid) + elog(ERROR, "network_prefix_quals: no >= operator for opclass %u", + opclass); + } + else + { + opr1oid = get_opclass_member(opclass, BTGreaterStrategyNumber); + if (opr1oid == InvalidOid) + elog(ERROR, "network_prefix_quals: no > operator for opclass %u", + opclass); + } opr1right = network_scan_first(rightop); @@ -2215,10 +2214,10 @@ network_prefix_quals(Node *leftop, Oid expr_op, Datum rightop) /* create clause "key <= network_scan_last( rightop )" */ - opr2oid = find_operator("<=", datatype); + opr2oid = get_opclass_member(opclass, BTLessEqualStrategyNumber); if (opr2oid == InvalidOid) - elog(ERROR, "network_prefix_quals: no <= operator for type %u", - datatype); + elog(ERROR, "network_prefix_quals: no <= operator for opclass %u", + opclass); opr2right = network_scan_last(rightop); @@ -2235,18 +2234,6 @@ network_prefix_quals(Node *leftop, Oid expr_op, Datum rightop) * Handy subroutines for match_special_index_operator() and friends. */ -/* See if there is a binary op of the given name for the given datatype */ -/* NB: we assume that only built-in system operators are searched for */ -static Oid -find_operator(const char *opname, Oid datatype) -{ - return GetSysCacheOid(OPERNAMENSP, - PointerGetDatum(opname), - ObjectIdGetDatum(datatype), - ObjectIdGetDatum(datatype), - ObjectIdGetDatum(PG_CATALOG_NAMESPACE)); -} - /* * Generate a Datum of the appropriate type from a C string. * Note that all of the supported types are pass-by-ref, so the diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 3984c666f51..25648beed19 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/optimizer/util/pathnode.c,v 1.88 2003/02/15 20:12:40 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/optimizer/util/pathnode.c,v 1.89 2003/05/26 00:11:27 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -328,7 +328,7 @@ create_seqscan_path(Query *root, RelOptInfo *rel) * * 'rel' is the parent rel * 'index' is an index on 'rel' - * 'restriction_clauses' is a list of RestrictInfo nodes + * 'restriction_clauses' is a list of lists of RestrictInfo nodes * to be used as index qual conditions in the scan. * 'pathkeys' describes the ordering of the path. * 'indexscandir' is ForwardScanDirection or BackwardScanDirection @@ -352,9 +352,8 @@ create_index_path(Query *root, pathnode->path.parent = rel; pathnode->path.pathkeys = pathkeys; - indexquals = get_actual_clauses(restriction_clauses); - /* expand special operators to indexquals the executor can handle */ - indexquals = expand_indexqual_conditions(indexquals); + /* Convert RestrictInfo nodes to indexquals the executor can handle */ + indexquals = expand_indexqual_conditions(index, restriction_clauses); /* * We are making a pathnode for a single-scan indexscan; therefore, diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c index 9dc0c7f1c19..fc35c2d6d41 100644 --- a/src/backend/parser/parse_coerce.c +++ b/src/backend/parser/parse_coerce.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/parse_coerce.c,v 2.96 2003/04/29 22:13:10 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/parse_coerce.c,v 2.97 2003/05/26 00:11:27 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -32,7 +32,6 @@ static Node *coerce_type_typmod(Node *node, Oid targetTypeId, int32 targetTypMod, CoercionForm cformat, bool isExplicit); -static Oid PreferredType(CATEGORY category, Oid type); static Node *build_func_call(Oid funcid, Oid rettype, List *args, CoercionForm fformat); @@ -66,28 +65,43 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype, if (can_coerce_type(1, &exprtype, &targettype, ccontext)) expr = coerce_type(pstate, expr, exprtype, targettype, ccontext, cformat); - /* - * String hacks to get transparent conversions for char and varchar: - * if a coercion to text is available, use it for forced coercions to - * char(n) or varchar(n). - * - * This is pretty grotty, but seems easier to maintain than providing - * entries in pg_cast that parallel all the ones for text. - */ - else if (ccontext >= COERCION_ASSIGNMENT && - (targettype == BPCHAROID || targettype == VARCHAROID)) + else if (ccontext >= COERCION_ASSIGNMENT) { - Oid text_id = TEXTOID; + /* + * String hacks to get transparent conversions for char and varchar: + * if a coercion to text is available, use it for forced coercions to + * char(n) or varchar(n) or domains thereof. + * + * This is pretty grotty, but seems easier to maintain than providing + * entries in pg_cast that parallel all the ones for text. + */ + Oid targetbasetype = getBaseType(targettype); - if (can_coerce_type(1, &exprtype, &text_id, ccontext)) + if (targetbasetype == BPCHAROID || targetbasetype == VARCHAROID) { - expr = coerce_type(pstate, expr, exprtype, text_id, - ccontext, cformat); - /* Need a RelabelType if no typmod coercion is performed */ - if (targettypmod < 0) - expr = (Node *) makeRelabelType((Expr *) expr, - targettype, -1, - cformat); + Oid text_id = TEXTOID; + + if (can_coerce_type(1, &exprtype, &text_id, ccontext)) + { + expr = coerce_type(pstate, expr, exprtype, text_id, + ccontext, cformat); + if (targetbasetype != targettype) + { + /* need to coerce to domain over char or varchar */ + expr = coerce_to_domain(expr, targetbasetype, targettype, + cformat); + } + else + { + /* need a RelabelType if no typmod coercion will be performed */ + if (targettypmod < 0) + expr = (Node *) makeRelabelType((Expr *) expr, + targettype, -1, + cformat); + } + } + else + expr = NULL; } else expr = NULL; @@ -923,7 +937,10 @@ enforce_generic_type_consistency(Oid *actual_arg_types, /* TypeCategory() - * Assign a category to the specified OID. + * Assign a category to the specified type OID. + * + * NB: this must not return INVALID_TYPE. + * * XXX This should be moved to system catalog lookups * to allow for better type extensibility. * - thomas 2001-09-30 @@ -1026,7 +1043,11 @@ TypeCategory(Oid inType) /* IsPreferredType() - * Check if this type is a preferred type. + * Check if this type is a preferred type for the given category. + * + * If category is INVALID_TYPE, then we'll return TRUE for preferred types + * of any category; otherwise, only for preferred types of that category. + * * XXX This should be moved to system catalog lookups * to allow for better type extensibility. * - thomas 2001-09-30 @@ -1034,39 +1055,34 @@ TypeCategory(Oid inType) bool IsPreferredType(CATEGORY category, Oid type) { - return (type == PreferredType(category, type)); -} /* IsPreferredType() */ + Oid preftype; + if (category == INVALID_TYPE) + category = TypeCategory(type); + else if (category != TypeCategory(type)) + return false; -/* PreferredType() - * Return the preferred type OID for the specified category. - * XXX This should be moved to system catalog lookups - * to allow for better type extensibility. - * - thomas 2001-09-30 - */ -static Oid -PreferredType(CATEGORY category, Oid type) -{ - Oid result; - + /* + * This switch should agree with TypeCategory(), above. Note that + * at this point, category certainly matches the type. + */ switch (category) { - case (INVALID_TYPE): case (UNKNOWN_TYPE): case (GENERIC_TYPE): - result = UNKNOWNOID; + preftype = UNKNOWNOID; break; case (BOOLEAN_TYPE): - result = BOOLOID; + preftype = BOOLOID; break; case (STRING_TYPE): - result = TEXTOID; + preftype = TEXTOID; break; case (BITSTRING_TYPE): - result = VARBITOID; + preftype = VARBITOID; break; case (NUMERIC_TYPE): @@ -1077,52 +1093,59 @@ PreferredType(CATEGORY category, Oid type) type == REGOPERATOROID || type == REGCLASSOID || type == REGTYPEOID) - result = OIDOID; + preftype = OIDOID; else - result = FLOAT8OID; + preftype = FLOAT8OID; break; case (DATETIME_TYPE): if (type == DATEOID) - result = TIMESTAMPOID; + preftype = TIMESTAMPOID; else - result = TIMESTAMPTZOID; + preftype = TIMESTAMPTZOID; break; case (TIMESPAN_TYPE): - result = INTERVALOID; + preftype = INTERVALOID; break; case (GEOMETRIC_TYPE): - result = type; + preftype = type; break; case (NETWORK_TYPE): - result = INETOID; + preftype = INETOID; break; case (USER_TYPE): - result = type; + preftype = type; break; default: - elog(ERROR, "PreferredType: unknown category"); - result = UNKNOWNOID; + elog(ERROR, "IsPreferredType: unknown category"); + preftype = UNKNOWNOID; break; } - return result; -} /* PreferredType() */ + + return (type == preftype); +} /* IsPreferredType() */ /* IsBinaryCoercible() * Check if srctype is binary-coercible to targettype. * * This notion allows us to cheat and directly exchange values without - * going through the trouble of calling a conversion function. + * going through the trouble of calling a conversion function. Note that + * in general, this should only be an implementation shortcut. Before 7.4, + * this was also used as a heuristic for resolving overloaded functions and + * operators, but that's basically a bad idea. * * As of 7.3, binary coercibility isn't hardwired into the code anymore. * We consider two types binary-coercible if there is an implicitly - * invokable, no-function-needed pg_cast entry. + * invokable, no-function-needed pg_cast entry. Also, a domain is always + * binary-coercible to its base type, though *not* vice versa (in the other + * direction, one must apply domain constraint checks before accepting the + * value as legitimate). * * This function replaces IsBinaryCompatible(), which was an inherently * symmetric test. Since the pg_cast entries aren't necessarily symmetric, @@ -1139,13 +1162,11 @@ IsBinaryCoercible(Oid srctype, Oid targettype) if (srctype == targettype) return true; - /* Perhaps the types are domains; if so, look at their base types */ + /* If srctype is a domain, reduce to its base type */ if (OidIsValid(srctype)) srctype = getBaseType(srctype); - if (OidIsValid(targettype)) - targettype = getBaseType(targettype); - /* Somewhat-fast path if same base type */ + /* Somewhat-fast path for domain -> base type case */ if (srctype == targettype) return true; @@ -1174,8 +1195,13 @@ IsBinaryCoercible(Oid srctype, Oid targettype) * ccontext determines the set of available casts. * * If we find a suitable entry in pg_cast, return TRUE, and set *funcid - * to the castfunc value (which may be InvalidOid for a binary-compatible - * coercion). + * to the castfunc value, which may be InvalidOid for a binary-compatible + * coercion. + * + * NOTE: *funcid == InvalidOid does not necessarily mean that no work is + * needed to do the coercion; if the target is a domain then we may need to + * apply domain constraint checking. If you want to check for a zero-effort + * conversion then use IsBinaryCoercible(). */ bool find_coercion_pathway(Oid targetTypeId, Oid sourceTypeId, @@ -1193,7 +1219,7 @@ find_coercion_pathway(Oid targetTypeId, Oid sourceTypeId, if (OidIsValid(targetTypeId)) targetTypeId = getBaseType(targetTypeId); - /* Domains are automatically binary-compatible with their base type */ + /* Domains are always coercible to and from their base type */ if (sourceTypeId == targetTypeId) return true; diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 058b9aad73d..e9a40e03179 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/parse_func.c,v 1.147 2003/04/29 22:13:10 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/parse_func.c,v 1.148 2003/05/26 00:11:27 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -16,7 +16,6 @@ #include "access/heapam.h" #include "catalog/catname.h" -#include "catalog/namespace.h" #include "catalog/pg_inherits.h" #include "catalog/pg_proc.h" #include "lib/stringinfo.h" @@ -37,13 +36,7 @@ static Oid **argtype_inherit(int nargs, Oid *argtypes); static int find_inheritors(Oid relid, Oid **supervec); static Oid **gen_cross_product(InhPaths *arginh, int nargs); -static int match_argtypes(int nargs, - Oid *input_typeids, - FuncCandidateList function_typeids, - FuncCandidateList *candidates); static FieldSelect *setup_field_select(Node *input, char *attname, Oid relid); -static FuncCandidateList func_select_candidate(int nargs, Oid *input_typeids, - FuncCandidateList candidates); static void unknown_attribute(const char *schemaname, const char *relname, const char *attname); @@ -355,21 +348,24 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, } -/* match_argtypes() +/* func_match_argtypes() * - * Given a list of possible typeid arrays to a function and an array of - * input typeids, produce a shortlist of those function typeid arrays - * that match the input typeids (either exactly or by coercion), and - * return the number of such arrays. + * Given a list of candidate functions (having the right name and number + * of arguments) and an array of input datatype OIDs, produce a shortlist of + * those candidates that actually accept the input datatypes (either exactly + * or by coercion), and return the number of such candidates. + * + * Note that can_coerce_type will assume that UNKNOWN inputs are coercible to + * anything, so candidates will not be eliminated on that basis. * * NB: okay to modify input list structure, as long as we find at least - * one match. + * one match. If no match at all, the list must remain unmodified. */ -static int -match_argtypes(int nargs, - Oid *input_typeids, - FuncCandidateList function_typeids, - FuncCandidateList *candidates) /* return value */ +int +func_match_argtypes(int nargs, + Oid *input_typeids, + FuncCandidateList raw_candidates, + FuncCandidateList *candidates) /* return value */ { FuncCandidateList current_candidate; FuncCandidateList next_candidate; @@ -377,7 +373,7 @@ match_argtypes(int nargs, *candidates = NULL; - for (current_candidate = function_typeids; + for (current_candidate = raw_candidates; current_candidate != NULL; current_candidate = next_candidate) { @@ -392,21 +388,65 @@ match_argtypes(int nargs, } return ncandidates; -} /* match_argtypes() */ +} /* func_match_argtypes() */ /* func_select_candidate() - * Given the input argtype array and more than one candidate - * for the function, attempt to resolve the conflict. + * Given the input argtype array and more than one candidate + * for the function, attempt to resolve the conflict. + * * Returns the selected candidate if the conflict can be resolved, * otherwise returns NULL. * - * By design, this is pretty similar to oper_select_candidate in parse_oper.c. - * However, the calling convention is a little different: we assume the caller - * already pruned away "candidates" that aren't actually coercion-compatible - * with the input types, whereas oper_select_candidate must do that itself. + * Note that the caller has already determined that there is no candidate + * exactly matching the input argtypes, and has pruned away any "candidates" + * that aren't actually coercion-compatible with the input types. + * + * This is also used for resolving ambiguous operator references. Formerly + * parse_oper.c had its own, essentially duplicate code for the purpose. + * The following comments (formerly in parse_oper.c) are kept to record some + * of the history of these heuristics. + * + * OLD COMMENTS: + * + * This routine is new code, replacing binary_oper_select_candidate() + * which dates from v4.2/v1.0.x days. It tries very hard to match up + * operators with types, including allowing type coercions if necessary. + * The important thing is that the code do as much as possible, + * while _never_ doing the wrong thing, where "the wrong thing" would + * be returning an operator when other better choices are available, + * or returning an operator which is a non-intuitive possibility. + * - thomas 1998-05-21 + * + * The comments below came from binary_oper_select_candidate(), and + * illustrate the issues and choices which are possible: + * - thomas 1998-05-20 + * + * current wisdom holds that the default operator should be one in which + * both operands have the same type (there will only be one such + * operator) + * + * 7.27.93 - I have decided not to do this; it's too hard to justify, and + * it's easy enough to typecast explicitly - avi + * [the rest of this routine was commented out since then - ay] + * + * 6/23/95 - I don't complete agree with avi. In particular, casting + * floats is a pain for users. Whatever the rationale behind not doing + * this is, I need the following special case to work. + * + * In the WHERE clause of a query, if a float is specified without + * quotes, we treat it as float8. I added the float48* operators so + * that we can operate on float4 and float8. But now we have more than + * one matching operator if the right arg is unknown (eg. float + * specified with quotes). This break some stuff in the regression + * test where there are floats in quotes not properly casted. Below is + * the solution. In addition to requiring the operator operates on the + * same type for both operands [as in the code Avi originally + * commented out], we also require that the operators be equivalent in + * some sense. (see equivalentOpersAfterPromotion for details.) + * - ay 6/95 */ -static FuncCandidateList +FuncCandidateList func_select_candidate(int nargs, Oid *input_typeids, FuncCandidateList candidates) @@ -419,59 +459,28 @@ func_select_candidate(int nargs, int ncandidates; int nbestMatch, nmatch; + Oid input_base_typeids[FUNC_MAX_ARGS]; CATEGORY slot_category[FUNC_MAX_ARGS], current_category; bool slot_has_preferred_type[FUNC_MAX_ARGS]; bool resolved_unknowns; /* - * Run through all candidates and keep those with the most matches on - * exact types. Keep all candidates if none match. + * If any input types are domains, reduce them to their base types. + * This ensures that we will consider functions on the base type to be + * "exact matches" in the exact-match heuristic; it also makes it possible + * to do something useful with the type-category heuristics. Note that + * this makes it difficult, but not impossible, to use functions declared + * to take a domain as an input datatype. Such a function will be + * selected over the base-type function only if it is an exact match at + * all argument positions, and so was already chosen by our caller. */ - ncandidates = 0; - nbestMatch = 0; - last_candidate = NULL; - for (current_candidate = candidates; - current_candidate != NULL; - current_candidate = current_candidate->next) - { - current_typeids = current_candidate->args; - nmatch = 0; - for (i = 0; i < nargs; i++) - { - if (input_typeids[i] != UNKNOWNOID && - current_typeids[i] == input_typeids[i]) - nmatch++; - } - - /* take this one as the best choice so far? */ - if ((nmatch > nbestMatch) || (last_candidate == NULL)) - { - nbestMatch = nmatch; - candidates = current_candidate; - last_candidate = current_candidate; - ncandidates = 1; - } - /* no worse than the last choice, so keep this one too? */ - else if (nmatch == nbestMatch) - { - last_candidate->next = current_candidate; - last_candidate = current_candidate; - ncandidates++; - } - /* otherwise, don't bother keeping this one... */ - } - - if (last_candidate) /* terminate rebuilt list */ - last_candidate->next = NULL; - - if (ncandidates == 1) - return candidates; + for (i = 0; i < nargs; i++) + input_base_typeids[i] = getBaseType(input_typeids[i]); /* - * Still too many candidates? Run through all candidates and keep - * those with the most matches on exact types + binary-compatible - * types. Keep all candidates if none match. + * Run through all candidates and keep those with the most matches on + * exact types. Keep all candidates if none match. */ ncandidates = 0; nbestMatch = 0; @@ -484,11 +493,9 @@ func_select_candidate(int nargs, nmatch = 0; for (i = 0; i < nargs; i++) { - if (input_typeids[i] != UNKNOWNOID) - { - if (IsBinaryCoercible(input_typeids[i], current_typeids[i])) - nmatch++; - } + if (input_base_typeids[i] != UNKNOWNOID && + current_typeids[i] == input_base_typeids[i]) + nmatch++; } /* take this one as the best choice so far? */ @@ -516,10 +523,14 @@ func_select_candidate(int nargs, return candidates; /* - * Still too many candidates? Now look for candidates which are - * preferred types at the args that will require coercion. Keep all - * candidates if none match. + * Still too many candidates? Now look for candidates which have either + * exact matches or preferred types at the args that will require coercion. + * (Restriction added in 7.4: preferred type must be of same category as + * input type; give no preference to cross-category conversions to + * preferred types.) Keep all candidates if none match. */ + for (i = 0; i < nargs; i++) /* avoid multiple lookups */ + slot_category[i] = TypeCategory(input_base_typeids[i]); ncandidates = 0; nbestMatch = 0; last_candidate = NULL; @@ -531,11 +542,10 @@ func_select_candidate(int nargs, nmatch = 0; for (i = 0; i < nargs; i++) { - if (input_typeids[i] != UNKNOWNOID) + if (input_base_typeids[i] != UNKNOWNOID) { - current_category = TypeCategory(current_typeids[i]); - if (current_typeids[i] == input_typeids[i] || - IsPreferredType(current_category, current_typeids[i])) + if (current_typeids[i] == input_base_typeids[i] || + IsPreferredType(slot_category[i], current_typeids[i])) nmatch++; } } @@ -565,6 +575,11 @@ func_select_candidate(int nargs, * Still too many candidates? Try assigning types for the unknown * columns. * + * NOTE: for a binary operator with one unknown and one non-unknown input, + * we already tried the heuristic of looking for a candidate with the + * known input type on both sides (see binary_oper_exact()). That's + * essentially a special case of the general algorithm we try next. + * * We do this by examining each unknown argument position to see if we * can determine a "type category" for it. If any candidate has an * input datatype of STRING category, use STRING category (this bias @@ -588,7 +603,7 @@ func_select_candidate(int nargs, { bool have_conflict; - if (input_typeids[i] != UNKNOWNOID) + if (input_base_typeids[i] != UNKNOWNOID) continue; resolved_unknowns = true; /* assume we can do it */ slot_category[i] = INVALID_TYPE; @@ -656,7 +671,7 @@ func_select_candidate(int nargs, current_typeids = current_candidate->args; for (i = 0; i < nargs; i++) { - if (input_typeids[i] != UNKNOWNOID) + if (input_base_typeids[i] != UNKNOWNOID) continue; current_type = current_typeids[i]; current_category = TypeCategory(current_type); @@ -694,7 +709,7 @@ func_select_candidate(int nargs, if (ncandidates == 1) return candidates; - return NULL; /* failed to determine a unique candidate */ + return NULL; /* failed to select a best candidate */ } /* func_select_candidate() */ @@ -734,16 +749,17 @@ func_get_detail(List *funcname, bool *retset, /* return value */ Oid **true_typeids) /* return value */ { - FuncCandidateList function_typeids; + FuncCandidateList raw_candidates; FuncCandidateList best_candidate; /* Get list of possible candidates from namespace search */ - function_typeids = FuncnameGetCandidates(funcname, nargs); + raw_candidates = FuncnameGetCandidates(funcname, nargs); /* - * See if there is an exact match + * Quickly check if there is an exact match to the input datatypes + * (there can be only one) */ - for (best_candidate = function_typeids; + for (best_candidate = raw_candidates; best_candidate != NULL; best_candidate = best_candidate->next) { @@ -815,7 +831,7 @@ func_get_detail(List *funcname, * didn't find an exact match, so now try to match up * candidates... */ - if (function_typeids != NULL) + if (raw_candidates != NULL) { Oid **input_typeid_vector = NULL; Oid *current_input_typeids; @@ -829,17 +845,18 @@ func_get_detail(List *funcname, do { - FuncCandidateList current_function_typeids; + FuncCandidateList current_candidates; int ncandidates; - ncandidates = match_argtypes(nargs, current_input_typeids, - function_typeids, - ¤t_function_typeids); + ncandidates = func_match_argtypes(nargs, + current_input_typeids, + raw_candidates, + ¤t_candidates); /* one match only? then run with it... */ if (ncandidates == 1) { - best_candidate = current_function_typeids; + best_candidate = current_candidates; break; } @@ -851,7 +868,7 @@ func_get_detail(List *funcname, { best_candidate = func_select_candidate(nargs, current_input_typeids, - current_function_typeids); + current_candidates); /* * If we were able to choose a best candidate, we're diff --git a/src/backend/parser/parse_oper.c b/src/backend/parser/parse_oper.c index 6238258ed2f..21f73994c42 100644 --- a/src/backend/parser/parse_oper.c +++ b/src/backend/parser/parse_oper.c @@ -8,18 +8,13 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/parse_oper.c,v 1.63 2003/04/29 22:13:10 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/parse_oper.c,v 1.64 2003/05/26 00:11:27 tgl Exp $ * *------------------------------------------------------------------------- */ #include "postgres.h" -#include "access/genam.h" -#include "access/heapam.h" -#include "catalog/catname.h" -#include "catalog/indexing.h" -#include "catalog/namespace.h" #include "catalog/pg_operator.h" #include "parser/parse_coerce.h" #include "parser/parse_expr.h" @@ -289,107 +284,29 @@ binary_oper_exact(Oid arg1, Oid arg2, /* oper_select_candidate() - * Given the input argtype array and one or more candidates - * for the function argtype array, attempt to resolve the conflict. - * Returns the selected argtype array if the conflict can be resolved, - * otherwise returns NULL. + * Given the input argtype array and one or more candidates + * for the operator, attempt to resolve the conflict. * - * By design, this is pretty similar to func_select_candidate in parse_func.c. - * However, we can do a couple of extra things here because we know we can - * have no more than two args to deal with. Also, the calling convention - * is a little different: we must prune away "candidates" that aren't actually - * coercion-compatible with the input types, whereas in parse_func.c that - * gets done by match_argtypes before func_select_candidate is called. + * Returns the OID of the selected operator if the conflict can be resolved, + * otherwise returns InvalidOid. * - * This routine is new code, replacing binary_oper_select_candidate() - * which dates from v4.2/v1.0.x days. It tries very hard to match up - * operators with types, including allowing type coercions if necessary. - * The important thing is that the code do as much as possible, - * while _never_ doing the wrong thing, where "the wrong thing" would - * be returning an operator when other better choices are available, - * or returning an operator which is a non-intuitive possibility. - * - thomas 1998-05-21 - * - * The comments below came from binary_oper_select_candidate(), and - * illustrate the issues and choices which are possible: - * - thomas 1998-05-20 - * - * current wisdom holds that the default operator should be one in which - * both operands have the same type (there will only be one such - * operator) - * - * 7.27.93 - I have decided not to do this; it's too hard to justify, and - * it's easy enough to typecast explicitly - avi - * [the rest of this routine was commented out since then - ay] - * - * 6/23/95 - I don't complete agree with avi. In particular, casting - * floats is a pain for users. Whatever the rationale behind not doing - * this is, I need the following special case to work. - * - * In the WHERE clause of a query, if a float is specified without - * quotes, we treat it as float8. I added the float48* operators so - * that we can operate on float4 and float8. But now we have more than - * one matching operator if the right arg is unknown (eg. float - * specified with quotes). This break some stuff in the regression - * test where there are floats in quotes not properly casted. Below is - * the solution. In addition to requiring the operator operates on the - * same type for both operands [as in the code Avi originally - * commented out], we also require that the operators be equivalent in - * some sense. (see equivalentOpersAfterPromotion for details.) - * - ay 6/95 + * Note that the caller has already determined that there is no candidate + * exactly matching the input argtype(s). Incompatible candidates are not yet + * pruned away, however. */ static Oid oper_select_candidate(int nargs, Oid *input_typeids, FuncCandidateList candidates) { - FuncCandidateList current_candidate; - FuncCandidateList last_candidate; - Oid *current_typeids; - Oid current_type; - int unknownOids; - int i; int ncandidates; - int nbestMatch, - nmatch; - CATEGORY slot_category[FUNC_MAX_ARGS], - current_category; - bool slot_has_preferred_type[FUNC_MAX_ARGS]; - bool resolved_unknowns; /* - * First, delete any candidates that cannot actually accept the given - * input types, whether directly or by coercion. (Note that - * can_coerce_type will assume that UNKNOWN inputs are coercible to - * anything, so candidates will not be eliminated on that basis.) + * Delete any candidates that cannot actually accept the given + * input types, whether directly or by coercion. */ - ncandidates = 0; - last_candidate = NULL; - for (current_candidate = candidates; - current_candidate != NULL; - current_candidate = current_candidate->next) - { - if (can_coerce_type(nargs, input_typeids, current_candidate->args, - COERCION_IMPLICIT)) - { - if (last_candidate == NULL) - { - candidates = current_candidate; - last_candidate = current_candidate; - ncandidates = 1; - } - else - { - last_candidate->next = current_candidate; - last_candidate = current_candidate; - ncandidates++; - } - } - /* otherwise, don't bother keeping this one... */ - } - - if (last_candidate) /* terminate rebuilt list */ - last_candidate->next = NULL; + ncandidates = func_match_argtypes(nargs, input_typeids, + candidates, &candidates); /* Done if no candidate or only one candidate survives */ if (ncandidates == 0) @@ -398,317 +315,15 @@ oper_select_candidate(int nargs, return candidates->oid; /* - * Run through all candidates and keep those with the most matches on - * exact types. Keep all candidates if none match. - */ - ncandidates = 0; - nbestMatch = 0; - last_candidate = NULL; - for (current_candidate = candidates; - current_candidate != NULL; - current_candidate = current_candidate->next) - { - current_typeids = current_candidate->args; - nmatch = 0; - for (i = 0; i < nargs; i++) - { - if (input_typeids[i] != UNKNOWNOID && - current_typeids[i] == input_typeids[i]) - nmatch++; - } - - /* take this one as the best choice so far? */ - if ((nmatch > nbestMatch) || (last_candidate == NULL)) - { - nbestMatch = nmatch; - candidates = current_candidate; - last_candidate = current_candidate; - ncandidates = 1; - } - /* no worse than the last choice, so keep this one too? */ - else if (nmatch == nbestMatch) - { - last_candidate->next = current_candidate; - last_candidate = current_candidate; - ncandidates++; - } - /* otherwise, don't bother keeping this one... */ - } - - if (last_candidate) /* terminate rebuilt list */ - last_candidate->next = NULL; - - if (ncandidates == 1) - return candidates->oid; - - /* - * Still too many candidates? Run through all candidates and keep - * those with the most matches on exact types + binary-compatible - * types. Keep all candidates if none match. - */ - ncandidates = 0; - nbestMatch = 0; - last_candidate = NULL; - for (current_candidate = candidates; - current_candidate != NULL; - current_candidate = current_candidate->next) - { - current_typeids = current_candidate->args; - nmatch = 0; - for (i = 0; i < nargs; i++) - { - if (input_typeids[i] != UNKNOWNOID) - { - if (IsBinaryCoercible(input_typeids[i], current_typeids[i])) - nmatch++; - } - } - - /* take this one as the best choice so far? */ - if ((nmatch > nbestMatch) || (last_candidate == NULL)) - { - nbestMatch = nmatch; - candidates = current_candidate; - last_candidate = current_candidate; - ncandidates = 1; - } - /* no worse than the last choice, so keep this one too? */ - else if (nmatch == nbestMatch) - { - last_candidate->next = current_candidate; - last_candidate = current_candidate; - ncandidates++; - } - /* otherwise, don't bother keeping this one... */ - } - - if (last_candidate) /* terminate rebuilt list */ - last_candidate->next = NULL; - - if (ncandidates == 1) - return candidates->oid; - - /* - * Still too many candidates? Now look for candidates which are - * preferred types at the args that will require coercion. Keep all - * candidates if none match. - */ - ncandidates = 0; - nbestMatch = 0; - last_candidate = NULL; - for (current_candidate = candidates; - current_candidate != NULL; - current_candidate = current_candidate->next) - { - current_typeids = current_candidate->args; - nmatch = 0; - for (i = 0; i < nargs; i++) - { - if (input_typeids[i] != UNKNOWNOID) - { - current_category = TypeCategory(current_typeids[i]); - if (current_typeids[i] == input_typeids[i] || - IsPreferredType(current_category, current_typeids[i])) - nmatch++; - } - } - - if ((nmatch > nbestMatch) || (last_candidate == NULL)) - { - nbestMatch = nmatch; - candidates = current_candidate; - last_candidate = current_candidate; - ncandidates = 1; - } - else if (nmatch == nbestMatch) - { - last_candidate->next = current_candidate; - last_candidate = current_candidate; - ncandidates++; - } - } - - if (last_candidate) /* terminate rebuilt list */ - last_candidate->next = NULL; - - if (ncandidates == 1) - return candidates->oid; - - /* - * Still too many candidates? Try assigning types for the unknown - * columns. - * - * First try: if we have an unknown and a non-unknown input, see whether - * there is a candidate all of whose input types are the same as the - * known input type (there can be at most one such candidate). If so, - * use that candidate. NOTE that this is cool only because operators - * can't have more than 2 args, so taking the last non-unknown as - * current_type can yield only one possibility if there is also an - * unknown. - */ - unknownOids = FALSE; - current_type = UNKNOWNOID; - for (i = 0; i < nargs; i++) - { - if ((input_typeids[i] != UNKNOWNOID) - && (input_typeids[i] != InvalidOid)) - current_type = input_typeids[i]; - else - unknownOids = TRUE; - } - - if (unknownOids && (current_type != UNKNOWNOID)) - { - for (current_candidate = candidates; - current_candidate != NULL; - current_candidate = current_candidate->next) - { - current_typeids = current_candidate->args; - nmatch = 0; - for (i = 0; i < nargs; i++) - { - if (current_type == current_typeids[i]) - nmatch++; - } - if (nmatch == nargs) - return current_candidate->oid; - } - } - - /* - * Second try: same algorithm as for unknown resolution in - * parse_func.c. - * - * We do this by examining each unknown argument position to see if we - * can determine a "type category" for it. If any candidate has an - * input datatype of STRING category, use STRING category (this bias - * towards STRING is appropriate since unknown-type literals look like - * strings). Otherwise, if all the candidates agree on the type - * category of this argument position, use that category. Otherwise, - * fail because we cannot determine a category. - * - * If we are able to determine a type category, also notice whether any - * of the candidates takes a preferred datatype within the category. - * - * Having completed this examination, remove candidates that accept the - * wrong category at any unknown position. Also, if at least one - * candidate accepted a preferred type at a position, remove - * candidates that accept non-preferred types. - * - * If we are down to one candidate at the end, we win. + * Use the same heuristics as for ambiguous functions to resolve + * the conflict. */ - resolved_unknowns = false; - for (i = 0; i < nargs; i++) - { - bool have_conflict; - - if (input_typeids[i] != UNKNOWNOID) - continue; - resolved_unknowns = true; /* assume we can do it */ - slot_category[i] = INVALID_TYPE; - slot_has_preferred_type[i] = false; - have_conflict = false; - for (current_candidate = candidates; - current_candidate != NULL; - current_candidate = current_candidate->next) - { - current_typeids = current_candidate->args; - current_type = current_typeids[i]; - current_category = TypeCategory(current_type); - if (slot_category[i] == INVALID_TYPE) - { - /* first candidate */ - slot_category[i] = current_category; - slot_has_preferred_type[i] = - IsPreferredType(current_category, current_type); - } - else if (current_category == slot_category[i]) - { - /* more candidates in same category */ - slot_has_preferred_type[i] |= - IsPreferredType(current_category, current_type); - } - else - { - /* category conflict! */ - if (current_category == STRING_TYPE) - { - /* STRING always wins if available */ - slot_category[i] = current_category; - slot_has_preferred_type[i] = - IsPreferredType(current_category, current_type); - } - else - { - /* - * Remember conflict, but keep going (might find - * STRING) - */ - have_conflict = true; - } - } - } - if (have_conflict && slot_category[i] != STRING_TYPE) - { - /* Failed to resolve category conflict at this position */ - resolved_unknowns = false; - break; - } - } + candidates = func_select_candidate(nargs, input_typeids, candidates); - if (resolved_unknowns) - { - /* Strip non-matching candidates */ - ncandidates = 0; - last_candidate = NULL; - for (current_candidate = candidates; - current_candidate != NULL; - current_candidate = current_candidate->next) - { - bool keepit = true; - - current_typeids = current_candidate->args; - for (i = 0; i < nargs; i++) - { - if (input_typeids[i] != UNKNOWNOID) - continue; - current_type = current_typeids[i]; - current_category = TypeCategory(current_type); - if (current_category != slot_category[i]) - { - keepit = false; - break; - } - if (slot_has_preferred_type[i] && - !IsPreferredType(current_category, current_type)) - { - keepit = false; - break; - } - } - if (keepit) - { - /* keep this candidate */ - last_candidate = current_candidate; - ncandidates++; - } - else - { - /* forget this candidate */ - if (last_candidate) - last_candidate->next = current_candidate->next; - else - candidates = current_candidate->next; - } - } - if (last_candidate) /* terminate rebuilt list */ - last_candidate->next = NULL; - } - - if (ncandidates == 1) + if (candidates) return candidates->oid; - return InvalidOid; /* failed to determine a unique candidate */ + return InvalidOid; /* failed to select a best candidate */ } /* oper_select_candidate() */ @@ -751,7 +366,7 @@ oper(List *opname, Oid ltypeId, Oid rtypeId, bool noError) /* * Unspecified type for one of the arguments? then use the - * other + * other (XXX this is probably dead code?) */ if (rtypeId == InvalidOid) rtypeId = ltypeId; diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 5ff4b1931da..77ef33b8783 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -15,7 +15,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.137 2003/05/15 15:50:18 petere Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.138 2003/05/26 00:11:27 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -77,9 +77,11 @@ #include <math.h> #include "access/heapam.h" +#include "access/nbtree.h" #include "access/tuptoaster.h" #include "catalog/catname.h" #include "catalog/pg_namespace.h" +#include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_proc.h" #include "catalog/pg_statistic.h" @@ -177,10 +179,9 @@ static bool get_restriction_var(List *args, int varRelid, 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, Oid vartype, - Const *prefix); +static Selectivity prefix_selectivity(Query *root, Var *var, + Oid opclass, Const *prefix); static Selectivity pattern_selectivity(Const *patt, Pattern_Type ptype); -static Oid find_operator(const char *opname, Oid datatype); static Datum string_to_datum(const char *str, Oid datatype); static Const *string_to_const(const char *str, Oid datatype); @@ -837,6 +838,7 @@ patternsel(PG_FUNCTION_ARGS, Pattern_Type ptype) Datum constval; Oid consttype; Oid vartype; + Oid opclass; Pattern_Prefix_Status pstatus; Const *patt = NULL; Const *prefix = NULL; @@ -884,21 +886,77 @@ patternsel(PG_FUNCTION_ARGS, Pattern_Type ptype) if (vartype != consttype) vartype = getBaseType(vartype); + /* + * We should now be able to recognize the var's datatype. Choose the + * index opclass from which we must draw the comparison operators. + * + * NOTE: It would be more correct to use the PATTERN opclasses than + * the simple ones, but at the moment ANALYZE will not generate statistics + * for the PATTERN operators. But our results are so approximate anyway + * that it probably hardly matters. + */ + switch (vartype) + { + case TEXTOID: + opclass = TEXT_BTREE_OPS_OID; + break; + case VARCHAROID: + opclass = VARCHAR_BTREE_OPS_OID; + break; + case BPCHAROID: + opclass = BPCHAR_BTREE_OPS_OID; + break; + case NAMEOID: + opclass = NAME_BTREE_OPS_OID; + break; + case BYTEAOID: + opclass = BYTEA_BTREE_OPS_OID; + break; + default: + return DEFAULT_MATCH_SEL; + } + /* divide pattern into fixed prefix and remainder */ patt = (Const *) other; pstatus = pattern_fixed_prefix(patt, ptype, &prefix, &rest); + /* + * If necessary, coerce the prefix constant to the right type. + * (The "rest" constant need not be changed.) + */ + if (prefix && prefix->consttype != vartype) + { + char *prefixstr; + + switch (prefix->consttype) + { + case TEXTOID: + prefixstr = DatumGetCString(DirectFunctionCall1(textout, + prefix->constvalue)); + break; + case BYTEAOID: + prefixstr = DatumGetCString(DirectFunctionCall1(byteaout, + prefix->constvalue)); + break; + default: + elog(ERROR, "patternsel: unexpected consttype %u", + prefix->consttype); + return DEFAULT_MATCH_SEL; + } + prefix = string_to_const(prefixstr, vartype); + pfree(prefixstr); + } + if (pstatus == Pattern_Prefix_Exact) { /* * Pattern specifies an exact match, so pretend operator is '=' */ - Oid eqopr = find_operator("=", vartype); + Oid eqopr = get_opclass_member(opclass, BTEqualStrategyNumber); List *eqargs; if (eqopr == InvalidOid) - elog(ERROR, "patternsel: no = operator for type %u", - vartype); + elog(ERROR, "patternsel: no = operator for opclass %u", opclass); eqargs = makeList2(var, prefix); result = DatumGetFloat8(DirectFunctionCall4(eqsel, PointerGetDatum(root), @@ -918,7 +976,7 @@ patternsel(PG_FUNCTION_ARGS, Pattern_Type ptype) Selectivity selec; if (pstatus == Pattern_Prefix_Partial) - prefixsel = prefix_selectivity(root, var, vartype, prefix); + prefixsel = prefix_selectivity(root, var, opclass, prefix); else prefixsel = 1.0; restsel = pattern_selectivity(rest, ptype); @@ -3020,10 +3078,13 @@ get_join_vars(List *args, Var **var1, Var **var2) /* * Extract the fixed prefix, if any, for a pattern. - * *prefix is set to a palloc'd prefix string, - * or to NULL if no fixed prefix exists for the pattern. - * *rest is set to point to the remainder of the pattern after the - * portion describing the fixed prefix. + * + * *prefix is set to a palloc'd prefix string (in the form of a Const node), + * or to NULL if no fixed prefix exists for the pattern. + * *rest is set to a palloc'd Const representing the remainder of the pattern + * after the portion describing the fixed prefix. + * Each of these has the same type (TEXT or BYTEA) as the given pattern Const. + * * The return value distinguishes no fixed prefix, a partial prefix, * or an exact-match-only pattern. */ @@ -3035,7 +3096,6 @@ like_fixed_prefix(Const *patt_const, bool case_insensitive, char *match; char *patt; int pattlen; - char *prefix; char *rest; Oid typeid = patt_const->consttype; int pos, @@ -3058,7 +3118,7 @@ like_fixed_prefix(Const *patt_const, bool case_insensitive, pattlen = toast_raw_datum_size(patt_const->constvalue) - VARHDRSZ; } - prefix = match = palloc(pattlen + 1); + match = palloc(pattlen + 1); match_pos = 0; for (pos = 0; pos < pattlen; pos++) @@ -3093,12 +3153,11 @@ like_fixed_prefix(Const *patt_const, bool case_insensitive, match[match_pos] = '\0'; rest = &patt[pos]; - *prefix_const = string_to_const(prefix, typeid); + *prefix_const = string_to_const(match, 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) @@ -3120,7 +3179,6 @@ regex_fixed_prefix(Const *patt_const, bool case_insensitive, match_pos, paren_depth; char *patt; - char *prefix; char *rest; Oid typeid = patt_const->consttype; @@ -3176,7 +3234,7 @@ regex_fixed_prefix(Const *patt_const, bool case_insensitive, } /* OK, allocate space for pattern */ - prefix = match = palloc(strlen(patt) + 1); + match = palloc(strlen(patt) + 1); match_pos = 0; /* note start at pos 1 to skip leading ^ */ @@ -3231,18 +3289,20 @@ regex_fixed_prefix(Const *patt_const, bool case_insensitive, { rest = &patt[pos + 1]; - *prefix_const = string_to_const(prefix, typeid); + *prefix_const = string_to_const(match, typeid); *rest_const = string_to_const(rest, typeid); + pfree(patt); + pfree(match); + return Pattern_Prefix_Exact; /* pattern specifies exact match */ } - *prefix_const = string_to_const(prefix, typeid); + *prefix_const = string_to_const(match, typeid); *rest_const = string_to_const(rest, typeid); pfree(patt); pfree(match); - prefix = NULL; if (match_pos > 0) return Pattern_Prefix_Partial; @@ -3284,10 +3344,8 @@ pattern_fixed_prefix(Const *patt, Pattern_Type ptype, * A fixed prefix "foo" is estimated as the selectivity of the expression * "var >= 'foo' AND var < 'fop'" (see also indxqual.c). * - * Because of constant-folding, we can assume that the prefixcon constant's - * type exactly matches the operator's declared input type; but it's not - * safe to make the same assumption for the Var, so the type to use for the - * Var must be passed in separately. + * We use the >= and < operators from the specified btree opclass to do the + * estimation. The given Var and Const must be of the associated datatype. * * XXX Note: we make use of the upper bound to estimate operator selectivity * even if the locale is such that we cannot rely on the upper-bound string. @@ -3295,27 +3353,17 @@ pattern_fixed_prefix(Const *patt, Pattern_Type ptype, * more useful to use the upper-bound code than not. */ static Selectivity -prefix_selectivity(Query *root, Var *var, Oid vartype, Const *prefixcon) +prefix_selectivity(Query *root, Var *var, Oid opclass, Const *prefixcon) { Selectivity prefixsel; Oid cmpopr; - char *prefix; List *cmpargs; Const *greaterstrcon; - cmpopr = find_operator(">=", vartype); + cmpopr = get_opclass_member(opclass, BTGreaterEqualStrategyNumber); if (cmpopr == InvalidOid) - elog(ERROR, "prefix_selectivity: no >= operator for type %u", - vartype); - if (prefixcon->consttype != BYTEAOID) - prefix = DatumGetCString(DirectFunctionCall1(textout, prefixcon->constvalue)); - else - prefix = DatumGetCString(DirectFunctionCall1(byteaout, prefixcon->constvalue)); - - /* If var is type NAME, must adjust type of comparison constant */ - if (vartype == NAMEOID) - prefixcon = string_to_const(prefix, NAMEOID); - + elog(ERROR, "prefix_selectivity: no >= operator for opclass %u", + opclass); cmpargs = makeList2(var, prefixcon); /* Assume scalargtsel is appropriate for all supported types */ prefixsel = DatumGetFloat8(DirectFunctionCall4(scalargtsel, @@ -3334,10 +3382,10 @@ prefix_selectivity(Query *root, Var *var, Oid vartype, Const *prefixcon) { Selectivity topsel; - cmpopr = find_operator("<", vartype); + cmpopr = get_opclass_member(opclass, BTLessStrategyNumber); if (cmpopr == InvalidOid) - elog(ERROR, "prefix_selectivity: no < operator for type %u", - vartype); + elog(ERROR, "prefix_selectivity: no < operator for opclass %u", + opclass); cmpargs = makeList2(var, greaterstrcon); /* Assume scalarltsel is appropriate for all supported types */ topsel = DatumGetFloat8(DirectFunctionCall4(scalarltsel, @@ -3702,18 +3750,6 @@ make_greater_string(const Const *str_const) return (Const *) NULL; } -/* See if there is a binary op of the given name for the given datatype */ -/* NB: we assume that only built-in system operators are searched for */ -static Oid -find_operator(const char *opname, Oid datatype) -{ - return GetSysCacheOid(OPERNAMENSP, - PointerGetDatum(opname), - ObjectIdGetDatum(datatype), - ObjectIdGetDatum(datatype), - ObjectIdGetDatum(PG_CATALOG_NAMESPACE)); -} - /* * Generate a Datum of the appropriate type from a C string. * Note that all of the supported types are pass-by-ref, so the diff --git a/src/backend/utils/adt/varchar.c b/src/backend/utils/adt/varchar.c index 77fb26a4807..5085c6025c2 100644 --- a/src/backend/utils/adt/varchar.c +++ b/src/backend/utils/adt/varchar.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/adt/varchar.c,v 1.96 2003/05/12 23:08:50 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/varchar.c,v 1.97 2003/05/26 00:11:27 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -343,7 +343,10 @@ name_bpchar(PG_FUNCTION_ARGS) /***************************************************************************** - * varchar - varchar() * + * varchar - varchar(n) + * + * Note: varchar piggybacks on type text for most operations, and so has no + * C-coded functions except for I/O and typmod checking. *****************************************************************************/ /* @@ -700,7 +703,7 @@ bpcharcmp(PG_FUNCTION_ARGS) /* * bpchar needs a specialized hash function because we want to ignore - * trailing blanks in comparisons. (varchar can use plain hashvarlena.) + * trailing blanks in comparisons. */ Datum hashbpchar(PG_FUNCTION_ARGS) @@ -720,187 +723,3 @@ hashbpchar(PG_FUNCTION_ARGS) return result; } - - -/***************************************************************************** - * Functions used for varchar - *****************************************************************************/ - -Datum -varcharlen(PG_FUNCTION_ARGS) -{ - VarChar *arg = PG_GETARG_VARCHAR_P(0); - - /* optimization for single byte encoding */ - if (pg_database_encoding_max_length() <= 1) - PG_RETURN_INT32(VARSIZE(arg) - VARHDRSZ); - - PG_RETURN_INT32( - pg_mbstrlen_with_len(VARDATA(arg), VARSIZE(arg) - VARHDRSZ) - ); -} - -Datum -varcharoctetlen(PG_FUNCTION_ARGS) -{ - VarChar *arg = PG_GETARG_VARCHAR_P(0); - - PG_RETURN_INT32(VARSIZE(arg) - VARHDRSZ); -} - - -/***************************************************************************** - * Comparison Functions used for varchar - * - * Note: btree indexes need these routines not to leak memory; therefore, - * be careful to free working copies of toasted datums. Most places don't - * need to be so careful. - *****************************************************************************/ - -Datum -varchareq(PG_FUNCTION_ARGS) -{ - VarChar *arg1 = PG_GETARG_VARCHAR_P(0); - VarChar *arg2 = PG_GETARG_VARCHAR_P(1); - int len1, - len2; - bool result; - - len1 = VARSIZE(arg1) - VARHDRSZ; - len2 = VARSIZE(arg2) - VARHDRSZ; - - /* fast path for different-length inputs */ - if (len1 != len2) - result = false; - else - result = (varstr_cmp(VARDATA(arg1), len1, VARDATA(arg2), len2) == 0); - - PG_FREE_IF_COPY(arg1, 0); - PG_FREE_IF_COPY(arg2, 1); - - PG_RETURN_BOOL(result); -} - -Datum -varcharne(PG_FUNCTION_ARGS) -{ - VarChar *arg1 = PG_GETARG_VARCHAR_P(0); - VarChar *arg2 = PG_GETARG_VARCHAR_P(1); - int len1, - len2; - bool result; - - len1 = VARSIZE(arg1) - VARHDRSZ; - len2 = VARSIZE(arg2) - VARHDRSZ; - - /* fast path for different-length inputs */ - if (len1 != len2) - result = true; - else - result = (varstr_cmp(VARDATA(arg1), len1, VARDATA(arg2), len2) != 0); - - PG_FREE_IF_COPY(arg1, 0); - PG_FREE_IF_COPY(arg2, 1); - - PG_RETURN_BOOL(result); -} - -Datum -varcharlt(PG_FUNCTION_ARGS) -{ - VarChar *arg1 = PG_GETARG_VARCHAR_P(0); - VarChar *arg2 = PG_GETARG_VARCHAR_P(1); - int len1, - len2; - int cmp; - - len1 = VARSIZE(arg1) - VARHDRSZ; - len2 = VARSIZE(arg2) - VARHDRSZ; - - cmp = varstr_cmp(VARDATA(arg1), len1, VARDATA(arg2), len2); - - PG_FREE_IF_COPY(arg1, 0); - PG_FREE_IF_COPY(arg2, 1); - - PG_RETURN_BOOL(cmp < 0); -} - -Datum -varcharle(PG_FUNCTION_ARGS) -{ - VarChar *arg1 = PG_GETARG_VARCHAR_P(0); - VarChar *arg2 = PG_GETARG_VARCHAR_P(1); - int len1, - len2; - int cmp; - - len1 = VARSIZE(arg1) - VARHDRSZ; - len2 = VARSIZE(arg2) - VARHDRSZ; - - cmp = varstr_cmp(VARDATA(arg1), len1, VARDATA(arg2), len2); - - PG_FREE_IF_COPY(arg1, 0); - PG_FREE_IF_COPY(arg2, 1); - - PG_RETURN_BOOL(cmp <= 0); -} - -Datum -varchargt(PG_FUNCTION_ARGS) -{ - VarChar *arg1 = PG_GETARG_VARCHAR_P(0); - VarChar *arg2 = PG_GETARG_VARCHAR_P(1); - int len1, - len2; - int cmp; - - len1 = VARSIZE(arg1) - VARHDRSZ; - len2 = VARSIZE(arg2) - VARHDRSZ; - - cmp = varstr_cmp(VARDATA(arg1), len1, VARDATA(arg2), len2); - - PG_FREE_IF_COPY(arg1, 0); - PG_FREE_IF_COPY(arg2, 1); - - PG_RETURN_BOOL(cmp > 0); -} - -Datum -varcharge(PG_FUNCTION_ARGS) -{ - VarChar *arg1 = PG_GETARG_VARCHAR_P(0); - VarChar *arg2 = PG_GETARG_VARCHAR_P(1); - int len1, - len2; - int cmp; - - len1 = VARSIZE(arg1) - VARHDRSZ; - len2 = VARSIZE(arg2) - VARHDRSZ; - - cmp = varstr_cmp(VARDATA(arg1), len1, VARDATA(arg2), len2); - - PG_FREE_IF_COPY(arg1, 0); - PG_FREE_IF_COPY(arg2, 1); - - PG_RETURN_BOOL(cmp >= 0); -} - -Datum -varcharcmp(PG_FUNCTION_ARGS) -{ - VarChar *arg1 = PG_GETARG_VARCHAR_P(0); - VarChar *arg2 = PG_GETARG_VARCHAR_P(1); - int len1, - len2; - int cmp; - - len1 = VARSIZE(arg1) - VARHDRSZ; - len2 = VARSIZE(arg2) - VARHDRSZ; - - cmp = varstr_cmp(VARDATA(arg1), len1, VARDATA(arg2), len2); - - PG_FREE_IF_COPY(arg1, 0); - PG_FREE_IF_COPY(arg2, 1); - - PG_RETURN_INT32(cmp); -} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 19178cc5243..fcd9dc2f59b 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -7,7 +7,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/cache/lsyscache.c,v 1.94 2003/05/13 04:38:58 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/cache/lsyscache.c,v 1.95 2003/05/26 00:11:27 tgl Exp $ * * NOTES * Eventually, the index information should go through here, too. @@ -80,6 +80,33 @@ op_requires_recheck(Oid opno, Oid opclass) return result; } +/* + * get_opclass_member + * Get the OID of the operator that implements the specified strategy + * for the specified opclass. + * + * Returns InvalidOid if there is no pg_amop entry for the given keys. + */ +Oid +get_opclass_member(Oid opclass, int16 strategy) +{ + HeapTuple tp; + Form_pg_amop amop_tup; + Oid result; + + tp = SearchSysCache(AMOPSTRATEGY, + ObjectIdGetDatum(opclass), + Int16GetDatum(strategy), + 0, 0); + if (!HeapTupleIsValid(tp)) + return InvalidOid; + amop_tup = (Form_pg_amop) GETSTRUCT(tp); + result = amop_tup->amopopr; + ReleaseSysCache(tp); + return result; +} + + /* ---------- ATTRIBUTE CACHES ---------- */ /* diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index c7848af9f62..2c28ffcabc5 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: catversion.h,v 1.195 2003/05/23 22:33:22 tgl Exp $ + * $Id: catversion.h,v 1.196 2003/05/26 00:11:27 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200305231 +#define CATALOG_VERSION_NO 200305241 #endif diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h index b373ce15a22..dbff38b3c39 100644 --- a/src/include/catalog/pg_amop.h +++ b/src/include/catalog/pg_amop.h @@ -16,7 +16,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_amop.h,v 1.48 2003/05/15 15:50:19 petere Exp $ + * $Id: pg_amop.h,v 1.49 2003/05/26 00:11:27 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -219,14 +219,14 @@ DATA(insert ( 426 4 f 1061 )); DATA(insert ( 426 5 f 1060 )); /* - * btree varchar_ops + * btree varchar_ops (same operators as text_ops) */ -DATA(insert ( 2003 1 f 1066 )); -DATA(insert ( 2003 2 f 1067 )); -DATA(insert ( 2003 3 f 1062 )); -DATA(insert ( 2003 4 f 1069 )); -DATA(insert ( 2003 5 f 1068 )); +DATA(insert ( 2003 1 f 664 )); +DATA(insert ( 2003 2 f 665 )); +DATA(insert ( 2003 3 f 98 )); +DATA(insert ( 2003 4 f 667 )); +DATA(insert ( 2003 5 f 666 )); /* * btree bytea_ops @@ -389,14 +389,14 @@ DATA(insert ( 2095 4 f 2317 )); DATA(insert ( 2095 5 f 2318 )); /* - * btree varchar pattern + * btree varchar pattern (same operators as text) */ -DATA(insert ( 2096 1 f 2320 )); -DATA(insert ( 2096 2 f 2321 )); -DATA(insert ( 2096 3 f 2322 )); -DATA(insert ( 2096 4 f 2323 )); -DATA(insert ( 2096 5 f 2324 )); +DATA(insert ( 2096 1 f 2314 )); +DATA(insert ( 2096 2 f 2315 )); +DATA(insert ( 2096 3 f 2316 )); +DATA(insert ( 2096 4 f 2317 )); +DATA(insert ( 2096 5 f 2318 )); /* * btree bpchar pattern @@ -462,7 +462,7 @@ DATA(insert ( 1999 1 f 1320 )); /* timetz_ops */ DATA(insert ( 2001 1 f 1550 )); /* varchar_ops */ -DATA(insert ( 2004 1 f 1062 )); +DATA(insert ( 2004 1 f 98 )); /* timestamp_ops */ DATA(insert ( 2040 1 f 2060 )); diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h index ba15ac9a660..0048d000cdf 100644 --- a/src/include/catalog/pg_amproc.h +++ b/src/include/catalog/pg_amproc.h @@ -14,7 +14,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_amproc.h,v 1.36 2003/05/15 15:50:19 petere Exp $ + * $Id: pg_amproc.h,v 1.37 2003/05/26 00:11:27 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -103,10 +103,10 @@ DATA(insert ( 1996 1 1107 )); DATA(insert ( 1998 1 1314 )); DATA(insert ( 2000 1 1358 )); DATA(insert ( 2002 1 1672 )); -DATA(insert ( 2003 1 1079 )); -DATA(insert ( 2039 1 1314 )); +DATA(insert ( 2003 1 360 )); +DATA(insert ( 2039 1 2045 )); DATA(insert ( 2095 1 2166 )); -DATA(insert ( 2096 1 2173 )); +DATA(insert ( 2096 1 2166 )); DATA(insert ( 2097 1 2180 )); DATA(insert ( 2098 1 2187 )); diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h index ed6834c6683..6c3d47ba7f1 100644 --- a/src/include/catalog/pg_cast.h +++ b/src/include/catalog/pg_cast.h @@ -7,7 +7,7 @@ * * Copyright (c) 2002, PostgreSQL Global Development Group * - * $Id: pg_cast.h,v 1.6 2003/05/14 18:08:15 tgl Exp $ + * $Id: pg_cast.h,v 1.7 2003/05/26 00:11:27 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -161,8 +161,8 @@ DATA(insert ( 2206 23 0 a )); */ DATA(insert ( 25 1042 0 i )); DATA(insert ( 25 1043 0 i )); -DATA(insert ( 1042 25 0 i )); -DATA(insert ( 1042 1043 0 i )); +DATA(insert ( 1042 25 401 i )); +DATA(insert ( 1042 1043 401 i )); DATA(insert ( 1043 25 0 i )); DATA(insert ( 1043 1042 0 i )); DATA(insert ( 18 25 946 i )); diff --git a/src/include/catalog/pg_opclass.h b/src/include/catalog/pg_opclass.h index 29c92f5ddfb..809cde3da02 100644 --- a/src/include/catalog/pg_opclass.h +++ b/src/include/catalog/pg_opclass.h @@ -9,7 +9,7 @@ * of opclass name and index access method type. This row specifies the * expected input data type for the opclass (the type of the heap column, * or the function output type in the case of a functional index). Note - * that types binary-compatible with the specified type will be accepted too. + * that types binary-coercible to the specified type will be accepted too. * * For a given <opcamid, opcintype> pair, there can be at most one row that * has opcdefault = true; this row is the default opclass for such data in @@ -26,7 +26,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_opclass.h,v 1.48 2003/05/15 15:50:19 petere Exp $ + * $Id: pg_opclass.h,v 1.49 2003/05/26 00:11:27 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -92,11 +92,14 @@ DATA(insert OID = 423 ( 403 bit_ops PGNSP PGUID 1560 t 0 )); DATA(insert OID = 424 ( 403 bool_ops PGNSP PGUID 16 t 0 )); DATA(insert OID = 425 ( 402 box_ops PGNSP PGUID 603 t 0 )); DATA(insert OID = 426 ( 403 bpchar_ops PGNSP PGUID 1042 t 0 )); +#define BPCHAR_BTREE_OPS_OID 426 DATA(insert OID = 427 ( 405 bpchar_ops PGNSP PGUID 1042 t 0 )); DATA(insert OID = 428 ( 403 bytea_ops PGNSP PGUID 17 t 0 )); +#define BYTEA_BTREE_OPS_OID 428 DATA(insert OID = 429 ( 403 char_ops PGNSP PGUID 18 t 0 )); DATA(insert OID = 431 ( 405 char_ops PGNSP PGUID 18 t 0 )); DATA(insert OID = 432 ( 403 cidr_ops PGNSP PGUID 650 t 0 )); +#define CIDR_BTREE_OPS_OID 432 DATA(insert OID = 433 ( 405 cidr_ops PGNSP PGUID 650 t 0 )); DATA(insert OID = 434 ( 403 date_ops PGNSP PGUID 1082 t 0 )); DATA(insert OID = 435 ( 405 date_ops PGNSP PGUID 1082 t 0 )); @@ -105,6 +108,7 @@ DATA(insert OID = 1971 ( 405 float4_ops PGNSP PGUID 700 t 0 )); DATA(insert OID = 1972 ( 403 float8_ops PGNSP PGUID 701 t 0 )); DATA(insert OID = 1973 ( 405 float8_ops PGNSP PGUID 701 t 0 )); DATA(insert OID = 1974 ( 403 inet_ops PGNSP PGUID 869 t 0 )); +#define INET_BTREE_OPS_OID 1974 DATA(insert OID = 1975 ( 405 inet_ops PGNSP PGUID 869 t 0 )); DATA(insert OID = 1976 ( 403 int2_ops PGNSP PGUID 21 t 0 )); #define INT2_BTREE_OPS_OID 1976 @@ -119,6 +123,7 @@ DATA(insert OID = 1983 ( 405 interval_ops PGNSP PGUID 1186 t 0 )); DATA(insert OID = 1984 ( 403 macaddr_ops PGNSP PGUID 829 t 0 )); DATA(insert OID = 1985 ( 405 macaddr_ops PGNSP PGUID 829 t 0 )); DATA(insert OID = 1986 ( 403 name_ops PGNSP PGUID 19 t 0 )); +#define NAME_BTREE_OPS_OID 1986 DATA(insert OID = 1987 ( 405 name_ops PGNSP PGUID 19 t 0 )); DATA(insert OID = 1988 ( 403 numeric_ops PGNSP PGUID 1700 t 0 )); DATA(insert OID = 1989 ( 403 oid_ops PGNSP PGUID 26 t 0 )); @@ -128,6 +133,7 @@ DATA(insert OID = 1991 ( 403 oidvector_ops PGNSP PGUID 30 t 0 )); DATA(insert OID = 1992 ( 405 oidvector_ops PGNSP PGUID 30 t 0 )); DATA(insert OID = 1993 ( 402 poly_ops PGNSP PGUID 604 t 0 )); DATA(insert OID = 1994 ( 403 text_ops PGNSP PGUID 25 t 0 )); +#define TEXT_BTREE_OPS_OID 1994 DATA(insert OID = 1995 ( 405 text_ops PGNSP PGUID 25 t 0 )); DATA(insert OID = 1996 ( 403 time_ops PGNSP PGUID 1083 t 0 )); DATA(insert OID = 1997 ( 405 time_ops PGNSP PGUID 1083 t 0 )); @@ -137,12 +143,17 @@ DATA(insert OID = 2000 ( 403 timetz_ops PGNSP PGUID 1266 t 0 )); DATA(insert OID = 2001 ( 405 timetz_ops PGNSP PGUID 1266 t 0 )); DATA(insert OID = 2002 ( 403 varbit_ops PGNSP PGUID 1562 t 0 )); DATA(insert OID = 2003 ( 403 varchar_ops PGNSP PGUID 1043 t 0 )); +#define VARCHAR_BTREE_OPS_OID 2003 DATA(insert OID = 2004 ( 405 varchar_ops PGNSP PGUID 1043 t 0 )); DATA(insert OID = 2039 ( 403 timestamp_ops PGNSP PGUID 1114 t 0 )); DATA(insert OID = 2040 ( 405 timestamp_ops PGNSP PGUID 1114 t 0 )); DATA(insert OID = 2095 ( 403 text_pattern_ops PGNSP PGUID 25 f 0 )); +#define TEXT_PATTERN_BTREE_OPS_OID 2095 DATA(insert OID = 2096 ( 403 varchar_pattern_ops PGNSP PGUID 1043 f 0 )); +#define VARCHAR_PATTERN_BTREE_OPS_OID 2096 DATA(insert OID = 2097 ( 403 bpchar_pattern_ops PGNSP PGUID 1042 f 0 )); +#define BPCHAR_PATTERN_BTREE_OPS_OID 2097 DATA(insert OID = 2098 ( 403 name_pattern_ops PGNSP PGUID 19 f 0 )); +#define NAME_PATTERN_BTREE_OPS_OID 2098 #endif /* PG_OPCLASS_H */ diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index 2fe0da5fbe9..ae4fb6e04bb 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_operator.h,v 1.113 2003/05/15 15:50:19 petere Exp $ + * $Id: pg_operator.h,v 1.114 2003/05/26 00:11:27 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -122,7 +122,9 @@ DATA(insert OID = 374 ( "||" PGNSP PGUID b f 2283 2277 2277 0 0 0 0 0 0 ar DATA(insert OID = 375 ( "||" PGNSP PGUID b f 2277 2277 2277 0 0 0 0 0 0 array_cat - - )); DATA(insert OID = 352 ( "=" PGNSP PGUID b t 28 28 16 352 0 0 0 0 0 xideq eqsel eqjoinsel )); -DATA(insert OID = 353 ( "=" PGNSP PGUID b t 28 23 16 0 0 0 0 0 0 xideq eqsel eqjoinsel )); +DATA(insert OID = 353 ( "=" PGNSP PGUID b t 28 23 16 0 0 0 0 0 0 xideqint4 eqsel eqjoinsel )); +DATA(insert OID = 385 ( "=" PGNSP PGUID b t 29 29 16 385 0 0 0 0 0 cideq eqsel eqjoinsel )); +DATA(insert OID = 386 ( "=" PGNSP PGUID b t 22 22 16 386 0 0 0 0 0 int2vectoreq eqsel eqjoinsel )); DATA(insert OID = 387 ( "=" PGNSP PGUID b t 27 27 16 387 0 0 0 0 0 tideq eqsel eqjoinsel )); #define TIDEqualOperator 387 DATA(insert OID = 388 ( "!" PGNSP PGUID r f 20 0 20 0 0 0 0 0 0 int8fac - - )); @@ -170,7 +172,7 @@ DATA(insert OID = 506 ( ">^" PGNSP PGUID b f 600 600 16 0 0 0 0 0 0 po DATA(insert OID = 507 ( "<<" PGNSP PGUID b f 600 600 16 0 0 0 0 0 0 point_left positionsel positionjoinsel )); DATA(insert OID = 508 ( ">>" PGNSP PGUID b f 600 600 16 0 0 0 0 0 0 point_right positionsel positionjoinsel )); DATA(insert OID = 509 ( "<^" PGNSP PGUID b f 600 600 16 0 0 0 0 0 0 point_below positionsel positionjoinsel )); -DATA(insert OID = 510 ( "~=" PGNSP PGUID b f 600 600 16 510 0 0 0 0 0 point_eq eqsel eqjoinsel )); +DATA(insert OID = 510 ( "~=" PGNSP PGUID b f 600 600 16 510 713 0 0 0 0 point_eq eqsel eqjoinsel )); DATA(insert OID = 511 ( "@" PGNSP PGUID b f 600 603 16 0 0 0 0 0 0 on_pb - - )); DATA(insert OID = 512 ( "@" PGNSP PGUID b f 600 602 16 755 0 0 0 0 0 on_ppath - - )); DATA(insert OID = 513 ( "@@" PGNSP PGUID l f 0 603 600 0 0 0 0 0 0 box_center - - )); @@ -350,6 +352,8 @@ DATA(insert OID = 708 ( "<->" PGNSP PGUID b f 628 628 701 708 0 0 0 0 DATA(insert OID = 709 ( "<->" PGNSP PGUID b f 601 601 701 709 0 0 0 0 0 lseg_distance - - )); DATA(insert OID = 712 ( "<->" PGNSP PGUID b f 604 604 701 712 0 0 0 0 0 poly_distance - - )); +DATA(insert OID = 713 ( "<>" PGNSP PGUID b f 600 600 16 713 510 0 0 0 0 point_ne neqsel neqjoinsel )); + /* add translation/rotation/scaling operators for geometric types. - thomas 97/05/10 */ DATA(insert OID = 731 ( "+" PGNSP PGUID b f 600 600 600 731 0 0 0 0 0 point_add - - )); DATA(insert OID = 732 ( "-" PGNSP PGUID b f 600 600 600 0 0 0 0 0 0 point_sub - - )); @@ -427,29 +431,16 @@ DATA(insert OID = 969 ( "@@" PGNSP PGUID l f 0 601 600 0 0 0 0 0 0 lse DATA(insert OID = 970 ( "@@" PGNSP PGUID l f 0 602 600 0 0 0 0 0 0 path_center - - )); DATA(insert OID = 971 ( "@@" PGNSP PGUID l f 0 604 600 0 0 0 0 0 0 poly_center - - )); -DATA(insert OID = 974 ( "||" PGNSP PGUID b f 1042 1042 1042 0 0 0 0 0 0 textcat - - )); -DATA(insert OID = 979 ( "||" PGNSP PGUID b f 1043 1043 1043 0 0 0 0 0 0 textcat - - )); - DATA(insert OID = 1054 ( "=" PGNSP PGUID b f 1042 1042 16 1054 1057 1058 1058 1058 1060 bpchareq eqsel eqjoinsel )); -DATA(insert OID = 1055 ( "~" PGNSP PGUID b f 1042 25 16 0 1056 0 0 0 0 textregexeq regexeqsel regexeqjoinsel )); +DATA(insert OID = 1055 ( "~" PGNSP PGUID b f 1042 25 16 0 1056 0 0 0 0 bpcharregexeq regexeqsel regexeqjoinsel )); #define OID_BPCHAR_REGEXEQ_OP 1055 -DATA(insert OID = 1056 ( "!~" PGNSP PGUID b f 1042 25 16 0 1055 0 0 0 0 textregexne regexnesel regexnejoinsel )); +DATA(insert OID = 1056 ( "!~" PGNSP PGUID b f 1042 25 16 0 1055 0 0 0 0 bpcharregexne regexnesel regexnejoinsel )); DATA(insert OID = 1057 ( "<>" PGNSP PGUID b f 1042 1042 16 1057 1054 0 0 0 0 bpcharne neqsel neqjoinsel )); DATA(insert OID = 1058 ( "<" PGNSP PGUID b f 1042 1042 16 1060 1061 0 0 0 0 bpcharlt scalarltsel scalarltjoinsel )); DATA(insert OID = 1059 ( "<=" PGNSP PGUID b f 1042 1042 16 1061 1060 0 0 0 0 bpcharle scalarltsel scalarltjoinsel )); DATA(insert OID = 1060 ( ">" PGNSP PGUID b f 1042 1042 16 1058 1059 0 0 0 0 bpchargt scalargtsel scalargtjoinsel )); DATA(insert OID = 1061 ( ">=" PGNSP PGUID b f 1042 1042 16 1059 1058 0 0 0 0 bpcharge scalargtsel scalargtjoinsel )); -DATA(insert OID = 1062 ( "=" PGNSP PGUID b t 1043 1043 16 1062 1065 1066 1066 1066 1068 varchareq eqsel eqjoinsel )); -DATA(insert OID = 1063 ( "~" PGNSP PGUID b f 1043 25 16 0 1064 0 0 0 0 textregexeq regexeqsel regexeqjoinsel )); -#define OID_VARCHAR_REGEXEQ_OP 1063 -DATA(insert OID = 1064 ( "!~" PGNSP PGUID b f 1043 25 16 0 1063 0 0 0 0 textregexne regexnesel regexnejoinsel )); -DATA(insert OID = 1065 ( "<>" PGNSP PGUID b f 1043 1043 16 1065 1062 0 0 0 0 varcharne neqsel neqjoinsel )); -DATA(insert OID = 1066 ( "<" PGNSP PGUID b f 1043 1043 16 1068 1069 0 0 0 0 varcharlt scalarltsel scalarltjoinsel )); -DATA(insert OID = 1067 ( "<=" PGNSP PGUID b f 1043 1043 16 1069 1068 0 0 0 0 varcharle scalarltsel scalarltjoinsel )); -DATA(insert OID = 1068 ( ">" PGNSP PGUID b f 1043 1043 16 1066 1067 0 0 0 0 varchargt scalargtsel scalargtjoinsel )); -DATA(insert OID = 1069 ( ">=" PGNSP PGUID b f 1043 1043 16 1067 1066 0 0 0 0 varcharge scalargtsel scalargtjoinsel )); - /* date operators */ DATA(insert OID = 1076 ( "+" PGNSP PGUID b f 1082 1186 1114 0 0 0 0 0 0 date_pl_interval - - )); DATA(insert OID = 1077 ( "-" PGNSP PGUID b f 1082 1186 1114 0 0 0 0 0 0 date_mi_interval - - )); @@ -515,12 +506,9 @@ DATA(insert OID = 1208 ( "!~~" PGNSP PGUID b f 19 25 16 0 1207 0 0 0 0 namen DATA(insert OID = 1209 ( "~~" PGNSP PGUID b f 25 25 16 0 1210 0 0 0 0 textlike likesel likejoinsel )); #define OID_TEXT_LIKE_OP 1209 DATA(insert OID = 1210 ( "!~~" PGNSP PGUID b f 25 25 16 0 1209 0 0 0 0 textnlike nlikesel nlikejoinsel )); -DATA(insert OID = 1211 ( "~~" PGNSP PGUID b f 1042 25 16 0 1212 0 0 0 0 textlike likesel likejoinsel )); +DATA(insert OID = 1211 ( "~~" PGNSP PGUID b f 1042 25 16 0 1212 0 0 0 0 bpcharlike likesel likejoinsel )); #define OID_BPCHAR_LIKE_OP 1211 -DATA(insert OID = 1212 ( "!~~" PGNSP PGUID b f 1042 25 16 0 1211 0 0 0 0 textnlike nlikesel nlikejoinsel )); -DATA(insert OID = 1213 ( "~~" PGNSP PGUID b f 1043 25 16 0 1214 0 0 0 0 textlike likesel likejoinsel )); -#define OID_VARCHAR_LIKE_OP 1213 -DATA(insert OID = 1214 ( "!~~" PGNSP PGUID b f 1043 25 16 0 1213 0 0 0 0 textnlike nlikesel nlikejoinsel )); +DATA(insert OID = 1212 ( "!~~" PGNSP PGUID b f 1042 25 16 0 1211 0 0 0 0 bpcharnlike nlikesel nlikejoinsel )); /* case-insensitive regex hacks */ DATA(insert OID = 1226 ( "~*" PGNSP PGUID b f 19 25 16 0 1227 0 0 0 0 nameicregexeq icregexeqsel icregexeqjoinsel )); @@ -529,20 +517,17 @@ DATA(insert OID = 1227 ( "!~*" PGNSP PGUID b f 19 25 16 0 1226 0 0 0 0 namei DATA(insert OID = 1228 ( "~*" PGNSP PGUID b f 25 25 16 0 1229 0 0 0 0 texticregexeq icregexeqsel icregexeqjoinsel )); #define OID_TEXT_ICREGEXEQ_OP 1228 DATA(insert OID = 1229 ( "!~*" PGNSP PGUID b f 25 25 16 0 1228 0 0 0 0 texticregexne icregexnesel icregexnejoinsel )); -DATA(insert OID = 1232 ( "~*" PGNSP PGUID b f 1043 25 16 0 1233 0 0 0 0 texticregexeq icregexeqsel icregexeqjoinsel )); -#define OID_VARCHAR_ICREGEXEQ_OP 1232 -DATA(insert OID = 1233 ( "!~*" PGNSP PGUID b f 1043 25 16 0 1232 0 0 0 0 texticregexne icregexnesel icregexnejoinsel )); -DATA(insert OID = 1234 ( "~*" PGNSP PGUID b f 1042 25 16 0 1235 0 0 0 0 texticregexeq icregexeqsel icregexeqjoinsel )); +DATA(insert OID = 1234 ( "~*" PGNSP PGUID b f 1042 25 16 0 1235 0 0 0 0 bpcharicregexeq icregexeqsel icregexeqjoinsel )); #define OID_BPCHAR_ICREGEXEQ_OP 1234 -DATA(insert OID = 1235 ( "!~*" PGNSP PGUID b f 1042 25 16 0 1234 0 0 0 0 texticregexne icregexnesel icregexnejoinsel )); +DATA(insert OID = 1235 ( "!~*" PGNSP PGUID b f 1042 25 16 0 1234 0 0 0 0 bpcharicregexne icregexnesel icregexnejoinsel )); /* timestamptz operators */ -DATA(insert OID = 1320 ( "=" PGNSP PGUID b f 1184 1184 16 1320 1321 1322 1322 1322 1324 timestamp_eq eqsel eqjoinsel )); -DATA(insert OID = 1321 ( "<>" PGNSP PGUID b f 1184 1184 16 1321 1320 0 0 0 0 timestamp_ne neqsel neqjoinsel )); -DATA(insert OID = 1322 ( "<" PGNSP PGUID b f 1184 1184 16 1324 1325 0 0 0 0 timestamp_lt scalarltsel scalarltjoinsel )); -DATA(insert OID = 1323 ( "<=" PGNSP PGUID b f 1184 1184 16 1325 1324 0 0 0 0 timestamp_le scalarltsel scalarltjoinsel )); -DATA(insert OID = 1324 ( ">" PGNSP PGUID b f 1184 1184 16 1322 1323 0 0 0 0 timestamp_gt scalargtsel scalargtjoinsel )); -DATA(insert OID = 1325 ( ">=" PGNSP PGUID b f 1184 1184 16 1323 1322 0 0 0 0 timestamp_ge scalargtsel scalargtjoinsel )); +DATA(insert OID = 1320 ( "=" PGNSP PGUID b f 1184 1184 16 1320 1321 1322 1322 1322 1324 timestamptz_eq eqsel eqjoinsel )); +DATA(insert OID = 1321 ( "<>" PGNSP PGUID b f 1184 1184 16 1321 1320 0 0 0 0 timestamptz_ne neqsel neqjoinsel )); +DATA(insert OID = 1322 ( "<" PGNSP PGUID b f 1184 1184 16 1324 1325 0 0 0 0 timestamptz_lt scalarltsel scalarltjoinsel )); +DATA(insert OID = 1323 ( "<=" PGNSP PGUID b f 1184 1184 16 1325 1324 0 0 0 0 timestamptz_le scalarltsel scalarltjoinsel )); +DATA(insert OID = 1324 ( ">" PGNSP PGUID b f 1184 1184 16 1322 1323 0 0 0 0 timestamptz_gt scalargtsel scalargtjoinsel )); +DATA(insert OID = 1325 ( ">=" PGNSP PGUID b f 1184 1184 16 1323 1322 0 0 0 0 timestamptz_ge scalargtsel scalargtjoinsel )); DATA(insert OID = 1327 ( "+" PGNSP PGUID b f 1184 1186 1184 0 0 0 0 0 0 timestamptz_pl_span - - )); DATA(insert OID = 1328 ( "-" PGNSP PGUID b f 1184 1184 1186 0 0 0 0 0 0 timestamptz_mi - - )); DATA(insert OID = 1329 ( "-" PGNSP PGUID b f 1184 1186 1184 0 0 0 0 0 0 timestamptz_mi_span - - )); @@ -691,20 +676,9 @@ DATA(insert OID = 1626 ( "!~~*" PGNSP PGUID b f 19 25 16 0 1625 0 0 0 0 namei DATA(insert OID = 1627 ( "~~*" PGNSP PGUID b f 25 25 16 0 1628 0 0 0 0 texticlike iclikesel iclikejoinsel )); #define OID_TEXT_ICLIKE_OP 1627 DATA(insert OID = 1628 ( "!~~*" PGNSP PGUID b f 25 25 16 0 1627 0 0 0 0 texticnlike icnlikesel icnlikejoinsel )); -DATA(insert OID = 1629 ( "~~*" PGNSP PGUID b f 1042 25 16 0 1630 0 0 0 0 texticlike iclikesel iclikejoinsel )); +DATA(insert OID = 1629 ( "~~*" PGNSP PGUID b f 1042 25 16 0 1630 0 0 0 0 bpchariclike iclikesel iclikejoinsel )); #define OID_BPCHAR_ICLIKE_OP 1629 -DATA(insert OID = 1630 ( "!~~*" PGNSP PGUID b f 1042 25 16 0 1629 0 0 0 0 texticnlike icnlikesel icnlikejoinsel )); -DATA(insert OID = 1631 ( "~~*" PGNSP PGUID b f 1043 25 16 0 1632 0 0 0 0 texticlike iclikesel iclikejoinsel )); -#define OID_VARCHAR_ICLIKE_OP 1631 -DATA(insert OID = 1632 ( "!~~*" PGNSP PGUID b f 1043 25 16 0 1631 0 0 0 0 texticnlike icnlikesel icnlikejoinsel )); - -/* regproc comparisons --- use oid (unsigned) comparison */ -DATA(insert OID = 1656 ( "=" PGNSP PGUID b t 24 24 16 1656 1657 1658 1658 1658 1659 oideq eqsel eqjoinsel )); -DATA(insert OID = 1657 ( "<>" PGNSP PGUID b f 24 24 16 1657 1656 0 0 0 0 oidne neqsel neqjoinsel )); -DATA(insert OID = 1658 ( "<" PGNSP PGUID b f 24 24 16 1659 1661 0 0 0 0 oidlt scalarltsel scalarltjoinsel )); -DATA(insert OID = 1659 ( ">" PGNSP PGUID b f 24 24 16 1658 1660 0 0 0 0 oidgt scalargtsel scalargtjoinsel )); -DATA(insert OID = 1660 ( "<=" PGNSP PGUID b f 24 24 16 1661 1659 0 0 0 0 oidle scalarltsel scalarltjoinsel )); -DATA(insert OID = 1661 ( ">=" PGNSP PGUID b f 24 24 16 1660 1658 0 0 0 0 oidge scalargtsel scalargtjoinsel )); +DATA(insert OID = 1630 ( "!~~*" PGNSP PGUID b f 1042 25 16 0 1629 0 0 0 0 bpcharicnlike icnlikesel icnlikejoinsel )); /* NUMERIC type - OID's 1700-1799 */ DATA(insert OID = 1751 ( "-" PGNSP PGUID l f 0 1700 1700 0 0 0 0 0 0 numeric_uminus - - )); @@ -823,13 +797,6 @@ DATA(insert OID = 2317 ( "~>=~" PGNSP PGUID b f 25 25 16 2315 2314 0 0 0 0 text_ DATA(insert OID = 2318 ( "~>~" PGNSP PGUID b f 25 25 16 2314 2315 0 0 0 0 text_pattern_gt scalargtsel scalargtjoinsel )); DATA(insert OID = 2319 ( "~<>~" PGNSP PGUID b f 25 25 16 2319 2316 0 0 0 0 text_pattern_ne neqsel neqjoinsel )); -DATA(insert OID = 2320 ( "~<~" PGNSP PGUID b f 1043 1043 16 2324 2323 0 0 0 0 varchar_pattern_lt scalarltsel scalarltjoinsel )); -DATA(insert OID = 2321 ( "~<=~" PGNSP PGUID b f 1043 1043 16 2323 2324 0 0 0 0 varchar_pattern_le scalarltsel scalarltjoinsel )); -DATA(insert OID = 2322 ( "~=~" PGNSP PGUID b t 1043 1043 16 2322 2325 2320 2320 2320 2324 varchar_pattern_eq eqsel eqjoinsel )); -DATA(insert OID = 2323 ( "~>=~" PGNSP PGUID b f 1043 1043 16 2321 2320 0 0 0 0 varchar_pattern_ge scalargtsel scalargtjoinsel )); -DATA(insert OID = 2324 ( "~>~" PGNSP PGUID b f 1043 1043 16 2320 2321 0 0 0 0 varchar_pattern_gt scalargtsel scalargtjoinsel )); -DATA(insert OID = 2325 ( "~<>~" PGNSP PGUID b f 1043 1043 16 2325 2322 0 0 0 0 varchar_pattern_ne neqsel neqjoinsel )); - DATA(insert OID = 2326 ( "~<~" PGNSP PGUID b f 1042 1042 16 2330 2329 0 0 0 0 bpchar_pattern_lt scalarltsel scalarltjoinsel )); DATA(insert OID = 2327 ( "~<=~" PGNSP PGUID b f 1042 1042 16 2329 2330 0 0 0 0 bpchar_pattern_le scalarltsel scalarltjoinsel )); DATA(insert OID = 2328 ( "~=~" PGNSP PGUID b t 1042 1042 16 2328 2331 2326 2326 2326 2330 bpchar_pattern_eq eqsel eqjoinsel )); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index f2f3e3eff14..7d6ff867962 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_proc.h,v 1.301 2003/05/23 22:33:22 tgl Exp $ + * $Id: pg_proc.h,v 1.302 2003/05/26 00:11:27 tgl Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -788,14 +788,16 @@ DESCR("intersect?"); /* OIDS 400 - 499 */ +DATA(insert OID = 401 ( text PGNSP PGUID 12 f f t f i 1 25 "1042" rtrim1 - _null_ )); +DESCR("convert char(n) to text"); DATA(insert OID = 406 ( text PGNSP PGUID 12 f f t f i 1 25 "19" name_text - _null_ )); DESCR("convert name to text"); DATA(insert OID = 407 ( name PGNSP PGUID 12 f f t f i 1 19 "25" text_name - _null_ )); DESCR("convert text to name"); DATA(insert OID = 408 ( bpchar PGNSP PGUID 12 f f t f i 1 1042 "19" name_bpchar - _null_ )); -DESCR("convert name to char()"); +DESCR("convert name to char(n)"); DATA(insert OID = 409 ( name PGNSP PGUID 12 f f t f i 1 19 "1042" bpchar_name - _null_ )); -DESCR("convert char() to name"); +DESCR("convert char(n) to name"); DATA(insert OID = 440 ( hashgettuple PGNSP PGUID 12 f f t f v 2 16 "2281 2281" hashgettuple - _null_ )); DESCR("hash(internal)"); @@ -1338,22 +1340,8 @@ DATA(insert OID = 1052 ( bpcharge PGNSP PGUID 12 f f t f i 2 16 "1042 1042" DESCR("greater-than-or-equal"); DATA(insert OID = 1053 ( bpcharne PGNSP PGUID 12 f f t f i 2 16 "1042 1042" bpcharne - _null_ )); DESCR("not equal"); -DATA(insert OID = 1070 ( varchareq PGNSP PGUID 12 f f t f i 2 16 "1043 1043" varchareq - _null_ )); -DESCR("equal"); -DATA(insert OID = 1071 ( varcharlt PGNSP PGUID 12 f f t f i 2 16 "1043 1043" varcharlt - _null_ )); -DESCR("less-than"); -DATA(insert OID = 1072 ( varcharle PGNSP PGUID 12 f f t f i 2 16 "1043 1043" varcharle - _null_ )); -DESCR("less-than-or-equal"); -DATA(insert OID = 1073 ( varchargt PGNSP PGUID 12 f f t f i 2 16 "1043 1043" varchargt - _null_ )); -DESCR("greater-than"); -DATA(insert OID = 1074 ( varcharge PGNSP PGUID 12 f f t f i 2 16 "1043 1043" varcharge - _null_ )); -DESCR("greater-than-or-equal"); -DATA(insert OID = 1075 ( varcharne PGNSP PGUID 12 f f t f i 2 16 "1043 1043" varcharne - _null_ )); -DESCR("not equal"); DATA(insert OID = 1078 ( bpcharcmp PGNSP PGUID 12 f f t f i 2 23 "1042 1042" bpcharcmp - _null_ )); DESCR("less-equal-greater"); -DATA(insert OID = 1079 ( varcharcmp PGNSP PGUID 12 f f t f i 2 23 "1043 1043" varcharcmp - _null_ )); -DESCR("less-equal-greater"); DATA(insert OID = 1080 ( hashbpchar PGNSP PGUID 12 f f t f i 1 23 "1042" hashbpchar - _null_ )); DESCR("hash"); DATA(insert OID = 1081 ( format_type PGNSP PGUID 12 f f f f s 2 25 "26 23" format_type - _null_ )); @@ -1488,7 +1476,7 @@ DESCR("minus"); DATA(insert OID = 1191 ( timestamptz PGNSP PGUID 12 f f t f s 1 1184 "25" text_timestamptz - _null_ )); DESCR("convert text to timestamp with time zone"); DATA(insert OID = 1192 ( text PGNSP PGUID 12 f f t f s 1 25 "1184" timestamptz_text - _null_ )); -DESCR("convert timestamp to text"); +DESCR("convert timestamp with time zone to text"); DATA(insert OID = 1193 ( text PGNSP PGUID 12 f f t f i 1 25 "1186" interval_text - _null_ )); DESCR("convert interval to text"); DATA(insert OID = 1194 ( reltime PGNSP PGUID 12 f f t f i 1 703 "1186" interval_reltime - _null_ )); @@ -1594,7 +1582,7 @@ DESCR("convert time and date to timestamp"); DATA(insert OID = 1297 ( datetimetz_pl PGNSP PGUID 12 f f t f i 2 1184 "1082 1266" datetimetz_timestamptz - _null_ )); DESCR("convert date and time with time zone to timestamp with time zone"); DATA(insert OID = 1298 ( timetzdate_pl PGNSP PGUID 14 f f t f i 2 1184 "1266 1082" "select ($2 + $1)" - _null_ )); -DESCR("convert time with time zone and date to timestamp"); +DESCR("convert time with time zone and date to timestamp with time zone"); DATA(insert OID = 1299 ( now PGNSP PGUID 12 f f t f s 0 1184 "" now - _null_ )); DESCR("current transaction time"); @@ -1642,8 +1630,9 @@ DATA(insert OID = 1317 ( length PGNSP PGUID 12 f f t f i 1 23 "25" textlen DESCR("length"); DATA(insert OID = 1318 ( length PGNSP PGUID 12 f f t f i 1 23 "1042" bpcharlen - _null_ )); DESCR("character length"); -DATA(insert OID = 1319 ( length PGNSP PGUID 12 f f t f i 1 23 "1043" varcharlen - _null_ )); -DESCR("character length"); + +DATA(insert OID = 1319 ( xideqint4 PGNSP PGUID 12 f f t f i 2 16 "28 23" xideq - _null_ )); +DESCR("equal"); DATA(insert OID = 1326 ( interval_div PGNSP PGUID 12 f f t f i 2 1186 "1186 701" interval_div - _null_ )); DESCR("divide"); @@ -1703,8 +1692,6 @@ DESCR("convert abstime to time"); DATA(insert OID = 1367 ( character_length PGNSP PGUID 12 f f t f i 1 23 "1042" bpcharlen - _null_ )); DESCR("character length"); -DATA(insert OID = 1368 ( character_length PGNSP PGUID 12 f f t f i 1 23 "1043" varcharlen - _null_ )); -DESCR("character length"); DATA(insert OID = 1369 ( character_length PGNSP PGUID 12 f f t f i 1 23 "25" textlen - _null_ )); DESCR("character length"); @@ -1712,15 +1699,11 @@ DATA(insert OID = 1370 ( interval PGNSP PGUID 12 f f t f i 1 1186 "1083" ti DESCR("convert time to interval"); DATA(insert OID = 1372 ( char_length PGNSP PGUID 12 f f t f i 1 23 "1042" bpcharlen - _null_ )); DESCR("character length"); -DATA(insert OID = 1373 ( char_length PGNSP PGUID 12 f f t f i 1 23 "1043" varcharlen - _null_ )); -DESCR("character length"); DATA(insert OID = 1374 ( octet_length PGNSP PGUID 12 f f t f i 1 23 "25" textoctetlen - _null_ )); DESCR("octet length"); DATA(insert OID = 1375 ( octet_length PGNSP PGUID 12 f f t f i 1 23 "1042" bpcharoctetlen - _null_ )); DESCR("octet length"); -DATA(insert OID = 1376 ( octet_length PGNSP PGUID 12 f f t f i 1 23 "1043" varcharoctetlen - _null_ )); -DESCR("octet length"); DATA(insert OID = 1377 ( time_larger PGNSP PGUID 12 f f t f i 2 1083 "1083 1083" time_larger - _null_ )); DESCR("larger of two"); @@ -2106,6 +2089,11 @@ DESCR("convert SQL99 regexp pattern to POSIX style"); DATA(insert OID = 1624 ( mul_d_interval PGNSP PGUID 12 f f t f i 2 1186 "701 1186" mul_d_interval - _null_ )); +DATA(insert OID = 1631 ( bpcharlike PGNSP PGUID 12 f f t f i 2 16 "1042 25" textlike - _null_ )); +DESCR("matches LIKE expression"); +DATA(insert OID = 1632 ( bpcharnlike PGNSP PGUID 12 f f t f i 2 16 "1042 25" textnlike - _null_ )); +DESCR("does not match LIKE expression"); + DATA(insert OID = 1633 ( texticlike PGNSP PGUID 12 f f t f i 2 16 "25 25" texticlike - _null_ )); DESCR("matches LIKE expression, case-insensitive"); DATA(insert OID = 1634 ( texticnlike PGNSP PGUID 12 f f t f i 2 16 "25 25" texticnlike - _null_ )); @@ -2117,6 +2105,19 @@ DESCR("does not match LIKE expression, case-insensitive"); DATA(insert OID = 1637 ( like_escape PGNSP PGUID 12 f f t f i 2 25 "25 25" like_escape - _null_ )); DESCR("convert LIKE pattern to use backslash escapes"); +DATA(insert OID = 1656 ( bpcharicregexeq PGNSP PGUID 12 f f t f i 2 16 "1042 25" texticregexeq - _null_ )); +DESCR("matches regex., case-insensitive"); +DATA(insert OID = 1657 ( bpcharicregexne PGNSP PGUID 12 f f t f i 2 16 "1042 25" texticregexne - _null_ )); +DESCR("does not match regex., case-insensitive"); +DATA(insert OID = 1658 ( bpcharregexeq PGNSP PGUID 12 f f t f i 2 16 "1042 25" textregexeq - _null_ )); +DESCR("matches regex., case-sensitive"); +DATA(insert OID = 1659 ( bpcharregexne PGNSP PGUID 12 f f t f i 2 16 "1042 25" textregexne - _null_ )); +DESCR("does not match regex., case-sensitive"); +DATA(insert OID = 1660 ( bpchariclike PGNSP PGUID 12 f f t f i 2 16 "1042 25" texticlike - _null_ )); +DESCR("matches LIKE expression, case-insensitive"); +DATA(insert OID = 1661 ( bpcharicnlike PGNSP PGUID 12 f f t f i 2 16 "1042 25" texticnlike - _null_ )); +DESCR("does not match LIKE expression, case-insensitive"); + DATA(insert OID = 1689 ( update_pg_pwd_and_pg_group PGNSP PGUID 12 f f t f v 0 2279 "" update_pg_pwd_and_pg_group - _null_ )); DESCR("update pg_pwd and pg_group files"); @@ -2532,7 +2533,7 @@ DESCR("format float8 to text"); DATA(insert OID = 1777 ( to_number PGNSP PGUID 12 f f t f i 2 1700 "25 25" numeric_to_number - _null_ )); DESCR("convert text to numeric"); DATA(insert OID = 1778 ( to_timestamp PGNSP PGUID 12 f f t f s 2 1184 "25 25" to_timestamp - _null_ )); -DESCR("convert text to timestamp"); +DESCR("convert text to timestamp with time zone"); DATA(insert OID = 1780 ( to_date PGNSP PGUID 12 f f t f i 2 1082 "25 25" to_date - _null_ )); DESCR("convert text to date"); DATA(insert OID = 1768 ( to_char PGNSP PGUID 12 f f t f i 2 25 "1186 25" interval_to_char - _null_ )); @@ -2803,7 +2804,7 @@ DATA(insert OID = 1966 ( oidsmaller PGNSP PGUID 12 f f t f i 2 26 "26 26" oi DESCR("smaller of two"); DATA(insert OID = 1967 ( timestamptz PGNSP PGUID 12 f f t f i 2 1184 "1184 23" timestamptz_scale - _null_ )); -DESCR("adjust timestamp precision"); +DESCR("adjust timestamptz precision"); DATA(insert OID = 1968 ( time PGNSP PGUID 12 f f t f i 2 1083 "1083 23" time_scale - _null_ )); DESCR("adjust time precision"); DATA(insert OID = 1969 ( timetz PGNSP PGUID 12 f f t f i 2 1266 "1266 23" timetz_scale - _null_ )); @@ -2851,9 +2852,9 @@ DESCR("convert date to timestamp"); DATA(insert OID = 2025 ( timestamp PGNSP PGUID 12 f f t f i 2 1114 "1082 1083" datetime_timestamp - _null_ )); DESCR("convert date and time to timestamp"); DATA(insert OID = 2027 ( timestamp PGNSP PGUID 12 f f t f s 1 1114 "1184" timestamptz_timestamp - _null_ )); -DESCR("convert date and time with time zone to timestamp"); +DESCR("convert timestamp with time zone to timestamp"); DATA(insert OID = 2028 ( timestamptz PGNSP PGUID 12 f f t f s 1 1184 "1114" timestamp_timestamptz - _null_ )); -DESCR("convert date and time with time zone to timestamp"); +DESCR("convert timestamp to timestamp with time zone"); DATA(insert OID = 2029 ( date PGNSP PGUID 12 f f t f i 1 1082 "1114" timestamp_date - _null_ )); DESCR("convert timestamp to date"); DATA(insert OID = 2030 ( abstime PGNSP PGUID 12 f f t f s 1 702 "1114" timestamp_abstime - _null_ )); @@ -3033,14 +3034,6 @@ DATA(insert OID = 2165 ( text_pattern_ne PGNSP PGUID 12 f f t f i 2 16 "25 2 DATA(insert OID = 2166 ( bttext_pattern_cmp PGNSP PGUID 12 f f t f i 2 23 "25 25" bttext_pattern_cmp - _null_ )); /* We use the same procedures here as above since the types are binary compatible. */ -DATA(insert OID = 2167 ( varchar_pattern_lt PGNSP PGUID 12 f f t f i 2 16 "1043 1043" text_pattern_lt - _null_ )); -DATA(insert OID = 2168 ( varchar_pattern_le PGNSP PGUID 12 f f t f i 2 16 "1043 1043" text_pattern_le - _null_ )); -DATA(insert OID = 2169 ( varchar_pattern_eq PGNSP PGUID 12 f f t f i 2 16 "1043 1043" text_pattern_eq - _null_ )); -DATA(insert OID = 2170 ( varchar_pattern_ge PGNSP PGUID 12 f f t f i 2 16 "1043 1043" text_pattern_ge - _null_ )); -DATA(insert OID = 2171 ( varchar_pattern_gt PGNSP PGUID 12 f f t f i 2 16 "1043 1043" text_pattern_gt - _null_ )); -DATA(insert OID = 2172 ( varchar_pattern_ne PGNSP PGUID 12 f f t f i 2 16 "1043 1043" text_pattern_ne - _null_ )); -DATA(insert OID = 2173 ( btvarchar_pattern_cmp PGNSP PGUID 12 f f t f i 2 23 "1043 1043" bttext_pattern_cmp - _null_ )); - DATA(insert OID = 2174 ( bpchar_pattern_lt PGNSP PGUID 12 f f t f i 2 16 "1042 1042" text_pattern_lt - _null_ )); DATA(insert OID = 2175 ( bpchar_pattern_le PGNSP PGUID 12 f f t f i 2 16 "1042 1042" text_pattern_le - _null_ )); DATA(insert OID = 2176 ( bpchar_pattern_eq PGNSP PGUID 12 f f t f i 2 16 "1042 1042" text_pattern_eq - _null_ )); diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 768e493e0c9..b36e17f91d7 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: paths.h,v 1.66 2003/02/15 20:12:41 tgl Exp $ + * $Id: paths.h,v 1.67 2003/05/26 00:11:28 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -41,7 +41,8 @@ extern Path *best_inner_indexscan(Query *root, RelOptInfo *rel, extern List *extract_or_indexqual_conditions(RelOptInfo *rel, IndexOptInfo *index, Expr *orsubclause); -extern List *expand_indexqual_conditions(List *indexquals); +extern List *expand_indexqual_conditions(IndexOptInfo *index, + List *clausegroups); /* * orindxpath.c diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h index 2b1a1fad4b9..5f2adf5c4a1 100644 --- a/src/include/parser/parse_func.h +++ b/src/include/parser/parse_func.h @@ -7,13 +7,14 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parse_func.h,v 1.45 2003/04/29 22:13:11 tgl Exp $ + * $Id: parse_func.h,v 1.46 2003/05/26 00:11:28 tgl Exp $ * *------------------------------------------------------------------------- */ #ifndef PARSER_FUNC_H #define PARSER_FUNC_H +#include "catalog/namespace.h" #include "parser/parse_node.h" @@ -48,6 +49,15 @@ extern FuncDetailCode func_get_detail(List *funcname, List *fargs, Oid *funcid, Oid *rettype, bool *retset, Oid **true_typeids); +extern int func_match_argtypes(int nargs, + Oid *input_typeids, + FuncCandidateList raw_candidates, + FuncCandidateList *candidates); + +extern FuncCandidateList func_select_candidate(int nargs, + Oid *input_typeids, + FuncCandidateList candidates); + extern bool typeInheritsFrom(Oid subclassTypeId, Oid superclassTypeId); extern void make_fn_arguments(ParseState *pstate, diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 539dfbc9de7..42e9a84c77b 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: builtins.h,v 1.218 2003/05/23 22:33:23 tgl Exp $ + * $Id: builtins.h,v 1.219 2003/05/26 00:11:28 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -496,15 +496,6 @@ extern Datum varcharout(PG_FUNCTION_ARGS); extern Datum varcharrecv(PG_FUNCTION_ARGS); extern Datum varcharsend(PG_FUNCTION_ARGS); extern Datum varchar(PG_FUNCTION_ARGS); -extern Datum varchareq(PG_FUNCTION_ARGS); -extern Datum varcharne(PG_FUNCTION_ARGS); -extern Datum varcharlt(PG_FUNCTION_ARGS); -extern Datum varcharle(PG_FUNCTION_ARGS); -extern Datum varchargt(PG_FUNCTION_ARGS); -extern Datum varcharge(PG_FUNCTION_ARGS); -extern Datum varcharcmp(PG_FUNCTION_ARGS); -extern Datum varcharlen(PG_FUNCTION_ARGS); -extern Datum varcharoctetlen(PG_FUNCTION_ARGS); /* varlena.c */ extern Datum textin(PG_FUNCTION_ARGS); diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index 848cc9f146e..66b497a98b1 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: lsyscache.h,v 1.69 2003/05/09 18:08:48 tgl Exp $ + * $Id: lsyscache.h,v 1.70 2003/05/26 00:11:28 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -17,6 +17,7 @@ extern bool op_in_opclass(Oid opno, Oid opclass); extern bool op_requires_recheck(Oid opno, Oid opclass); +extern Oid get_opclass_member(Oid opclass, int16 strategy); extern char *get_attname(Oid relid, AttrNumber attnum); extern AttrNumber get_attnum(Oid relid, const char *attname); extern Oid get_atttype(Oid relid, AttrNumber attnum); diff --git a/src/test/regress/expected/name.out b/src/test/regress/expected/name.out index 8cc09dae744..f6c3a17fe51 100644 --- a/src/test/regress/expected/name.out +++ b/src/test/regress/expected/name.out @@ -27,7 +27,7 @@ INSERT INTO NAME_TBL(f1) VALUES ('d34aaasdf'); INSERT INTO NAME_TBL(f1) VALUES (''); INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ'); SELECT '' AS seven, NAME_TBL.*; - seven | f1 + seven | f1 -------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq @@ -39,7 +39,7 @@ SELECT '' AS seven, NAME_TBL.*; (7 rows) SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - six | f1 + six | f1 -----+----------------------------------------------------------------- | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq | asdfghjkl; @@ -49,20 +49,20 @@ SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOP (5 rows) SELECT '' AS one, c.f1 FROM NAME_TBL c WHERE c.f1 = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - one | f1 + one | f1 -----+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ (2 rows) SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 < '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - three | f1 + three | f1 -------+---- | (1 row) SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - four | f1 + four | f1 ------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | @@ -70,7 +70,7 @@ SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNO (3 rows) SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - three | f1 + three | f1 -------+----------------------------------------------------------------- | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq | asdfghjkl; @@ -79,7 +79,7 @@ SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNO (4 rows) SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR'; - four | f1 + four | f1 ------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq @@ -90,7 +90,7 @@ SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNO (6 rows) SELECT '' AS seven, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*'; - seven | f1 + seven | f1 -------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq @@ -107,7 +107,7 @@ SELECT '' AS zero, c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*'; (0 rows) SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]'; - three | f1 + three | f1 -------+----------------------------------------------------------------- | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 2573d2011c3..e0504706f3c 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -14,6 +14,23 @@ -- -- NB: run this test earlier than the create_operator test, because -- that test creates some bogus operators... +-- Helper functions to deal with cases where binary-coercible matches are +-- allowed. +-- This should match IsBinaryCoercible() in parse_coerce.c. +create function binary_coercible(oid, oid) returns bool as +'SELECT ($1 = $2) OR + EXISTS(select 1 from pg_cast where + castsource = $1 and casttarget = $2 and + castfunc = 0 and castcontext = ''i'')' +language sql; +-- This one ignores castcontext, so it considers only physical equivalence +-- and not whether the coercion can be invoked implicitly. +create function physically_coercible(oid, oid) returns bool as +'SELECT ($1 = $2) OR + EXISTS(select 1 from pg_cast where + castsource = $1 and casttarget = $2 and + castfunc = 0)' +language sql; -- **************** pg_proc **************** -- Look for illegal values in pg_proc fields. -- NOTE: in reality pronargs could be more than 10, but I'm too lazy to put @@ -105,11 +122,10 @@ WHERE p1.oid != p2.oid AND -------------+------------- 25 | 1042 25 | 1043 - 1042 | 1043 1114 | 1184 1560 | 1562 2277 | 2283 -(6 rows) +(5 rows) SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1] FROM pg_proc AS p1, pg_proc AS p2 @@ -120,13 +136,12 @@ WHERE p1.oid != p2.oid AND (p1.proargtypes[1] < p2.proargtypes[1]); proargtypes | proargtypes -------------+------------- + 23 | 28 25 | 1042 - 25 | 1043 - 1042 | 1043 1114 | 1184 1560 | 1562 2277 | 2283 -(6 rows) +(5 rows) SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2] FROM pg_proc AS p1, pg_proc AS p2 @@ -228,23 +243,17 @@ SELECT c.* FROM pg_cast c, pg_proc p WHERE c.castfunc = p.oid AND (p.pronargs <> 1 - OR NOT (c.castsource = p.proargtypes[0] OR - EXISTS (SELECT 1 FROM pg_cast k - WHERE k.castfunc = 0 AND - k.castsource = c.castsource AND - k.casttarget = p.proargtypes[0])) - OR NOT (p.prorettype = c.casttarget OR - EXISTS (SELECT 1 FROM pg_cast k - WHERE k.castfunc = 0 AND - k.castsource = p.prorettype AND - k.casttarget = c.casttarget))); + OR NOT binary_coercible(c.castsource, p.proargtypes[0]) + OR NOT binary_coercible(p.prorettype, c.casttarget)); castsource | casttarget | castfunc | castcontext ------------+------------+----------+------------- (0 rows) -- Look for binary compatible casts that do not have the reverse -- direction registered as well, or where the reverse direction is not --- also binary compatible. This is legal, but probably not intended. +-- also binary compatible. This is legal, but usually not intended. +-- As of 7.4, this finds the casts from text and varchar to bpchar, because +-- those are binary-compatible while the reverse way goes through rtrim(). SELECT * FROM pg_cast c WHERE c.castfunc = 0 AND @@ -254,7 +263,9 @@ WHERE c.castfunc = 0 AND k.casttarget = c.castsource); castsource | casttarget | castfunc | castcontext ------------+------------+----------+------------- -(0 rows) + 25 | 1042 | 0 | i + 1043 | 1042 | 0 | i +(2 rows) -- **************** pg_operator **************** -- Look for illegal values in pg_operator fields. @@ -425,14 +436,15 @@ WHERE p1.oprlsortop != p1.oprrsortop AND -- Hashing only works on simple equality operators "type = sametype", -- since the hash itself depends on the bitwise representation of the type. -- Check that allegedly hashable operators look like they might be "=". --- NOTE: in 7.3, this search finds xideqint4. --- Until we have some cleaner way of dealing with binary-equivalent types, --- just leave that tuple in the expected output. +-- NOTE: as of 7.3, this search finds xideqint4. Since we do not mark +-- xid and int4 as binary-equivalent in pg_cast, there's no easy way to +-- recognize that case as OK; just leave that tuple in the expected output. SELECT p1.oid, p1.oprname FROM pg_operator AS p1 WHERE p1.oprcanhash AND NOT (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND - p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND p1.oprcom = p1.oid); + p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND + p1.oprcom = p1.oid); oid | oprname -----+--------- 353 | = @@ -464,33 +476,26 @@ WHERE p1.oprcanhash AND p1.oprcode = p2.oid AND p2.proname = 'array_eq'; -- Check that each operator defined in pg_operator matches its oprcode entry -- in pg_proc. Easiest to do this separately for each oprkind. --- FIXME: want to check that argument/result types match, but how to do that --- in the face of binary-compatible types? SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'b' AND (p2.pronargs != 2 --- diked out until we find a way of marking binary-compatible types --- OR --- p1.oprresult != p2.prorettype OR --- (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR --- (p1.oprright != p2.proargtypes[1] AND p2.proargtypes[1] != 0) -); + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) + OR NOT binary_coercible(p1.oprright, p2.proargtypes[1])); oid | oprname | oid | proname -----+---------+-----+--------- (0 rows) --- These two selects can be left as-is because there are no binary-compatible --- cases that they trip over, at least in 6.5: SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'l' AND - (p2.pronargs != 1 OR - p1.oprresult != p2.prorettype OR - (p1.oprright != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR - p1.oprleft != 0); + (p2.pronargs != 1 + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprright, p2.proargtypes[0]) + OR p1.oprleft != 0); oid | oprname | oid | proname -----+---------+-----+--------- (0 rows) @@ -499,10 +504,10 @@ SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'r' AND - (p2.pronargs != 1 OR - p1.oprresult != p2.prorettype OR - (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR - p1.oprright != 0); + (p2.pronargs != 1 + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) + OR p1.oprright != 0); oid | oprname | oid | proname -----+---------+-----+--------- (0 rows) @@ -591,48 +596,46 @@ WHERE a.aggfnoid = p.oid AND (0 rows) -- Cross-check transfn against its entry in pg_proc. --- FIXME: what about binary-compatible types? --- NOTE: in 7.1, this search finds max and min on abstime, which are --- implemented using int4larger/int4smaller. Until we have --- some cleaner way of dealing with binary-equivalent types, just leave --- those two tuples in the expected output. -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +-- NOTE: use physically_coercible here, not binary_coercible, because +-- max and min on abstime are implemented using int4larger/int4smaller. +SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND - a.aggtransfn = p2.oid AND - (p2.proretset OR - a.aggtranstype != p2.prorettype OR - a.aggtranstype != p2.proargtypes[0] OR - NOT ((p2.pronargs = 2 AND p.proargtypes[0] = p2.proargtypes[1]) OR - (p2.pronargs = 1 AND p.proargtypes[0] = '"any"'::regtype))) -ORDER BY 1; - aggfnoid | proname | oid | proname -----------+---------+-----+------------- - 2121 | max | 768 | int4larger - 2137 | min | 769 | int4smaller -(2 rows) + a.aggtransfn = ptr.oid AND + (ptr.proretset + OR NOT physically_coercible(ptr.prorettype, a.aggtranstype) + OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0]) + OR NOT ((ptr.pronargs = 2 AND + physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) + OR + (ptr.pronargs = 1 AND + p.proargtypes[0] = '"any"'::regtype))); + aggfnoid | proname | oid | proname +----------+---------+-----+--------- +(0 rows) -- Cross-check finalfn (if present) against its entry in pg_proc. --- FIXME: what about binary-compatible types? -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn WHERE a.aggfnoid = p.oid AND - a.aggfinalfn = p2.oid AND - (p2.proretset OR p.prorettype != p2.prorettype OR - p2.pronargs != 1 OR - a.aggtranstype != p2.proargtypes[0]); + a.aggfinalfn = pfn.oid AND + (pfn.proretset + OR NOT binary_coercible(pfn.prorettype, p.prorettype) + OR pfn.pronargs != 1 + OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0])); aggfnoid | proname | oid | proname ----------+---------+-----+--------- (0 rows) -- If transfn is strict then either initval should be non-NULL, or --- input type should equal transtype so that the first non-null input +-- input type should match transtype so that the first non-null input -- can be assigned as the state value. -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND - a.aggtransfn = p2.oid AND p2.proisstrict AND - a.agginitval IS NULL AND p.proargtypes[0] != a.aggtranstype; + a.aggtransfn = ptr.oid AND ptr.proisstrict AND + a.agginitval IS NULL AND + NOT binary_coercible(p.proargtypes[0], a.aggtranstype); aggfnoid | proname | oid | proname ----------+---------+-----+--------- (0 rows) @@ -714,7 +717,8 @@ WHERE p1.amopopr = p2.oid AND SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND - (p3.opcintype != p2.oprleft OR p3.opcintype != p2.oprright); + (NOT binary_coercible(p3.opcintype, p2.oprleft) OR + p2.oprleft != p2.oprright); amopclaid | amopopr | oid | oprname | opcname -----------+---------+-----+---------+--------- (0 rows) @@ -752,7 +756,8 @@ WHERE p2.opcamid = p1.oid AND -- signature of the function may be different for different support routines -- or different base data types. -- We can check that all the referenced instances of the same support --- routine number take the same number of parameters, but that's about it... +-- routine number take the same number of parameters, but that's about it +-- for a general check... SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.proname, p3.opcname, @@ -769,3 +774,22 @@ WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND -----------+-----------+-----+---------+---------+-----------+-----------+-----+---------+--------- (0 rows) +-- For btree, though, we can do better since we know the support routines +-- must be of the form cmp(input, input) returns int4. +SELECT p1.amopclaid, p1.amprocnum, + p2.oid, p2.proname, + p3.opcname +FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3 +WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND + (opckeytype != 0 + OR amprocnum != 1 + OR proretset + OR prorettype != 23 + OR pronargs != 2 + OR NOT binary_coercible(opcintype, proargtypes[0]) + OR proargtypes[0] != proargtypes[1]); + amopclaid | amprocnum | oid | proname | opcname +-----------+-----------+-----+---------+--------- +(0 rows) + diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f173b6cf544..322b84ff100 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1317,9 +1317,9 @@ SELECT tablename, rulename, definition FROM pg_rules ---------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; - rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired '::bpchar, '$0.00'::money, old.salary); - rtest_emp | rtest_emp_ins | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired '::bpchar, new.salary, '$0.00'::money); - rtest_emp | rtest_emp_upd | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored '::bpchar, new.salary, old.salary); + rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired'::bpchar, '$0.00'::money, old.salary); + rtest_emp | rtest_emp_ins | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired'::bpchar, new.salary, '$0.00'::money); + rtest_emp | rtest_emp_upd | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored'::bpchar, new.salary, old.salary); rtest_nothn1 | rtest_nothn_r1 | CREATE RULE rtest_nothn_r1 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD NOTHING; rtest_nothn1 | rtest_nothn_r2 | CREATE RULE rtest_nothn_r2 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING; rtest_nothn2 | rtest_nothn_r3 | CREATE RULE rtest_nothn_r3 AS ON INSERT TO rtest_nothn2 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b) VALUES (new.a, new.b); diff --git a/src/test/regress/expected/select_having.out b/src/test/regress/expected/select_having.out index 4447a9df4d6..37793d49b57 100644 --- a/src/test/regress/expected/select_having.out +++ b/src/test/regress/expected/select_having.out @@ -33,11 +33,11 @@ SELECT b, c FROM test_having SELECT lower(c), count(c) FROM test_having GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a) ORDER BY lower(c); - lower | count -----------+------- - bbbb | 3 - cccc | 4 - xxxx | 1 + lower | count +-------+------- + bbbb | 3 + cccc | 4 + xxxx | 1 (3 rows) SELECT c, max(a) FROM test_having diff --git a/src/test/regress/expected/select_having_1.out b/src/test/regress/expected/select_having_1.out index ffbb591b125..6154bcbfe86 100644 --- a/src/test/regress/expected/select_having_1.out +++ b/src/test/regress/expected/select_having_1.out @@ -33,11 +33,11 @@ SELECT b, c FROM test_having SELECT lower(c), count(c) FROM test_having GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a) ORDER BY lower(c); - lower | count -----------+------- - bbbb | 3 - cccc | 4 - xxxx | 1 + lower | count +-------+------- + bbbb | 3 + cccc | 4 + xxxx | 1 (3 rows) SELECT c, max(a) FROM test_having diff --git a/src/test/regress/expected/select_implicit.out b/src/test/regress/expected/select_implicit.out index 47d2e80289e..d8a9edbd781 100644 --- a/src/test/regress/expected/select_implicit.out +++ b/src/test/regress/expected/select_implicit.out @@ -248,12 +248,12 @@ SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2; -- w/ existing GROUP BY target using a relation name in target SELECT lower(test_missing_target.c), count(c) FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c); - lower | count -----------+------- - aaaa | 2 - bbbb | 3 - cccc | 4 - xxxx | 1 + lower | count +-------+------- + aaaa | 2 + bbbb | 3 + cccc | 4 + xxxx | 1 (4 rows) -- w/o existing GROUP BY target diff --git a/src/test/regress/expected/select_implicit_1.out b/src/test/regress/expected/select_implicit_1.out index 80abfd8f9a2..fa67bf3a5d1 100644 --- a/src/test/regress/expected/select_implicit_1.out +++ b/src/test/regress/expected/select_implicit_1.out @@ -248,12 +248,12 @@ SELECT count(b) FROM test_missing_target GROUP BY b/2 ORDER BY b/2; -- w/ existing GROUP BY target using a relation name in target SELECT lower(test_missing_target.c), count(c) FROM test_missing_target GROUP BY lower(c) ORDER BY lower(c); - lower | count -----------+------- - aaaa | 2 - bbbb | 3 - cccc | 4 - xxxx | 1 + lower | count +-------+------- + aaaa | 2 + bbbb | 3 + cccc | 4 + xxxx | 1 (4 rows) -- w/o existing GROUP BY target diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out index bb999785eb1..26c18d1690b 100644 --- a/src/test/regress/expected/strings.out +++ b/src/test/regress/expected/strings.out @@ -26,8 +26,8 @@ ERROR: parser: parse error at or near "' - third line'" at character 75 SELECT CAST(f1 AS text) AS "text(char)" FROM CHAR_TBL; text(char) ------------ - a - ab + a + ab abcd abcd (4 rows) @@ -88,8 +88,8 @@ SELECT CAST(f1 AS varchar) AS "varchar(text)" FROM TEXT_TBL; SELECT CAST(f1 AS varchar) AS "varchar(char)" FROM CHAR_TBL; varchar(char) --------------- - a - ab + a + ab abcd abcd (4 rows) @@ -570,16 +570,16 @@ SELECT text 'text' || ' and unknown' AS "Concat text to unknown type"; text and unknown (1 row) -SELECT char(20) 'characters' || 'and text' AS "Concat char to unknown type"; - Concat char to unknown type ------------------------------- - characters and text +SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; + Concat char to unknown type +----------------------------- + characters and text (1 row) SELECT text 'text' || char(20) ' and characters' AS "Concat text to char"; - Concat text to char --------------------------- - text and characters + Concat text to char +--------------------- + text and characters (1 row) SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar"; diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index a46a44becab..43d32298a58 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -203,21 +203,19 @@ SELECT f1 FROM INT4_TBL 123456 (5 rows) -SELECT f1 AS five FROM VARCHAR_TBL +SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL UNION SELECT f1 FROM CHAR_TBL; - five ------- - a + three +------- a - ab ab abcd -(5 rows) +(3 rows) SELECT f1 AS three FROM VARCHAR_TBL UNION -SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL; +SELECT CAST(f1 AS varchar) FROM CHAR_TBL; three ------- a @@ -234,8 +232,8 @@ SELECT f1 FROM CHAR_TBL; ab abcd abcd - a - ab + a + ab abcd abcd (8 rows) diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 5be76aa3b41..4b7bd7b4dd5 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -15,6 +15,27 @@ -- NB: run this test earlier than the create_operator test, because -- that test creates some bogus operators... + +-- Helper functions to deal with cases where binary-coercible matches are +-- allowed. + +-- This should match IsBinaryCoercible() in parse_coerce.c. +create function binary_coercible(oid, oid) returns bool as +'SELECT ($1 = $2) OR + EXISTS(select 1 from pg_cast where + castsource = $1 and casttarget = $2 and + castfunc = 0 and castcontext = ''i'')' +language sql; + +-- This one ignores castcontext, so it considers only physical equivalence +-- and not whether the coercion can be invoked implicitly. +create function physically_coercible(oid, oid) returns bool as +'SELECT ($1 = $2) OR + EXISTS(select 1 from pg_cast where + castsource = $1 and casttarget = $2 and + castfunc = 0)' +language sql; + -- **************** pg_proc **************** -- Look for illegal values in pg_proc fields. @@ -180,20 +201,15 @@ SELECT c.* FROM pg_cast c, pg_proc p WHERE c.castfunc = p.oid AND (p.pronargs <> 1 - OR NOT (c.castsource = p.proargtypes[0] OR - EXISTS (SELECT 1 FROM pg_cast k - WHERE k.castfunc = 0 AND - k.castsource = c.castsource AND - k.casttarget = p.proargtypes[0])) - OR NOT (p.prorettype = c.casttarget OR - EXISTS (SELECT 1 FROM pg_cast k - WHERE k.castfunc = 0 AND - k.castsource = p.prorettype AND - k.casttarget = c.casttarget))); + OR NOT binary_coercible(c.castsource, p.proargtypes[0]) + OR NOT binary_coercible(p.prorettype, c.casttarget)); -- Look for binary compatible casts that do not have the reverse -- direction registered as well, or where the reverse direction is not --- also binary compatible. This is legal, but probably not intended. +-- also binary compatible. This is legal, but usually not intended. + +-- As of 7.4, this finds the casts from text and varchar to bpchar, because +-- those are binary-compatible while the reverse way goes through rtrim(). SELECT * FROM pg_cast c @@ -347,15 +363,17 @@ WHERE p1.oprlsortop != p1.oprrsortop AND -- Hashing only works on simple equality operators "type = sametype", -- since the hash itself depends on the bitwise representation of the type. -- Check that allegedly hashable operators look like they might be "=". --- NOTE: in 7.3, this search finds xideqint4. --- Until we have some cleaner way of dealing with binary-equivalent types, --- just leave that tuple in the expected output. + +-- NOTE: as of 7.3, this search finds xideqint4. Since we do not mark +-- xid and int4 as binary-equivalent in pg_cast, there's no easy way to +-- recognize that case as OK; just leave that tuple in the expected output. SELECT p1.oid, p1.oprname FROM pg_operator AS p1 WHERE p1.oprcanhash AND NOT (p1.oprkind = 'b' AND p1.oprresult = 'bool'::regtype AND - p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND p1.oprcom = p1.oid); + p1.oprleft = p1.oprright AND p1.oprname IN ('=', '~=~') AND + p1.oprcom = p1.oid); -- In 6.5 we accepted hashable array equality operators when the array element -- type is hashable. However, what we actually need to make hashjoin work on @@ -382,41 +400,33 @@ WHERE p1.oprcanhash AND p1.oprcode = p2.oid AND p2.proname = 'array_eq'; -- Check that each operator defined in pg_operator matches its oprcode entry -- in pg_proc. Easiest to do this separately for each oprkind. --- FIXME: want to check that argument/result types match, but how to do that --- in the face of binary-compatible types? SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'b' AND (p2.pronargs != 2 --- diked out until we find a way of marking binary-compatible types --- OR --- p1.oprresult != p2.prorettype OR --- (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR --- (p1.oprright != p2.proargtypes[1] AND p2.proargtypes[1] != 0) -); - --- These two selects can be left as-is because there are no binary-compatible --- cases that they trip over, at least in 6.5: + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) + OR NOT binary_coercible(p1.oprright, p2.proargtypes[1])); SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'l' AND - (p2.pronargs != 1 OR - p1.oprresult != p2.prorettype OR - (p1.oprright != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR - p1.oprleft != 0); + (p2.pronargs != 1 + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprright, p2.proargtypes[0]) + OR p1.oprleft != 0); SELECT p1.oid, p1.oprname, p2.oid, p2.proname FROM pg_operator AS p1, pg_proc AS p2 WHERE p1.oprcode = p2.oid AND p1.oprkind = 'r' AND - (p2.pronargs != 1 OR - p1.oprresult != p2.prorettype OR - (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR - p1.oprright != 0); + (p2.pronargs != 1 + OR NOT binary_coercible(p2.prorettype, p1.oprresult) + OR NOT binary_coercible(p1.oprleft, p2.proargtypes[0]) + OR p1.oprright != 0); -- If the operator is mergejoinable or hashjoinable, its underlying function -- should not be volatile. @@ -489,42 +499,42 @@ WHERE a.aggfnoid = p.oid AND a.aggfinalfn = 0 AND p.prorettype != a.aggtranstype; -- Cross-check transfn against its entry in pg_proc. --- FIXME: what about binary-compatible types? --- NOTE: in 7.1, this search finds max and min on abstime, which are --- implemented using int4larger/int4smaller. Until we have --- some cleaner way of dealing with binary-equivalent types, just leave --- those two tuples in the expected output. -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +-- NOTE: use physically_coercible here, not binary_coercible, because +-- max and min on abstime are implemented using int4larger/int4smaller. +SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND - a.aggtransfn = p2.oid AND - (p2.proretset OR - a.aggtranstype != p2.prorettype OR - a.aggtranstype != p2.proargtypes[0] OR - NOT ((p2.pronargs = 2 AND p.proargtypes[0] = p2.proargtypes[1]) OR - (p2.pronargs = 1 AND p.proargtypes[0] = '"any"'::regtype))) -ORDER BY 1; + a.aggtransfn = ptr.oid AND + (ptr.proretset + OR NOT physically_coercible(ptr.prorettype, a.aggtranstype) + OR NOT physically_coercible(a.aggtranstype, ptr.proargtypes[0]) + OR NOT ((ptr.pronargs = 2 AND + physically_coercible(p.proargtypes[0], ptr.proargtypes[1])) + OR + (ptr.pronargs = 1 AND + p.proargtypes[0] = '"any"'::regtype))); -- Cross-check finalfn (if present) against its entry in pg_proc. --- FIXME: what about binary-compatible types? -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +SELECT a.aggfnoid::oid, p.proname, pfn.oid, pfn.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS pfn WHERE a.aggfnoid = p.oid AND - a.aggfinalfn = p2.oid AND - (p2.proretset OR p.prorettype != p2.prorettype OR - p2.pronargs != 1 OR - a.aggtranstype != p2.proargtypes[0]); + a.aggfinalfn = pfn.oid AND + (pfn.proretset + OR NOT binary_coercible(pfn.prorettype, p.prorettype) + OR pfn.pronargs != 1 + OR NOT binary_coercible(a.aggtranstype, pfn.proargtypes[0])); -- If transfn is strict then either initval should be non-NULL, or --- input type should equal transtype so that the first non-null input +-- input type should match transtype so that the first non-null input -- can be assigned as the state value. -SELECT a.aggfnoid::oid, p.proname, p2.oid, p2.proname -FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS p2 +SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname +FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr WHERE a.aggfnoid = p.oid AND - a.aggtransfn = p2.oid AND p2.proisstrict AND - a.agginitval IS NULL AND p.proargtypes[0] != a.aggtranstype; + a.aggtransfn = ptr.oid AND ptr.proisstrict AND + a.agginitval IS NULL AND + NOT binary_coercible(p.proargtypes[0], a.aggtranstype); -- **************** pg_opclass **************** @@ -592,7 +602,8 @@ WHERE p1.amopopr = p2.oid AND SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND - (p3.opcintype != p2.oprleft OR p3.opcintype != p2.oprright); + (NOT binary_coercible(p3.opcintype, p2.oprleft) OR + p2.oprleft != p2.oprright); -- **************** pg_amproc **************** @@ -622,7 +633,8 @@ WHERE p2.opcamid = p1.oid AND -- signature of the function may be different for different support routines -- or different base data types. -- We can check that all the referenced instances of the same support --- routine number take the same number of parameters, but that's about it... +-- routine number take the same number of parameters, but that's about it +-- for a general check... SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.proname, @@ -636,3 +648,20 @@ WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND p3.opcamid = p6.opcamid AND p1.amprocnum = p4.amprocnum AND p1.amproc = p2.oid AND p4.amproc = p5.oid AND (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs); + +-- For btree, though, we can do better since we know the support routines +-- must be of the form cmp(input, input) returns int4. + +SELECT p1.amopclaid, p1.amprocnum, + p2.oid, p2.proname, + p3.opcname +FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3 +WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + AND p1.amopclaid = p3.oid AND p1.amproc = p2.oid AND + (opckeytype != 0 + OR amprocnum != 1 + OR proretset + OR prorettype != 23 + OR pronargs != 2 + OR NOT binary_coercible(opcintype, proargtypes[0]) + OR proargtypes[0] != proargtypes[1]); diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql index b84d0cb8adb..d2368d6ba53 100644 --- a/src/test/regress/sql/strings.sql +++ b/src/test/regress/sql/strings.sql @@ -199,7 +199,7 @@ SELECT 'unknown' || ' and unknown' AS "Concat unknown types"; SELECT text 'text' || ' and unknown' AS "Concat text to unknown type"; -SELECT char(20) 'characters' || 'and text' AS "Concat char to unknown type"; +SELECT char(20) 'characters' || ' and text' AS "Concat char to unknown type"; SELECT text 'text' || char(20) ' and characters' AS "Concat text to char"; diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql index 747d281948f..c69e4c3f152 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -66,13 +66,13 @@ UNION SELECT f1 FROM INT4_TBL WHERE f1 BETWEEN 0 AND 1000000; -SELECT f1 AS five FROM VARCHAR_TBL +SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL UNION SELECT f1 FROM CHAR_TBL; SELECT f1 AS three FROM VARCHAR_TBL UNION -SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL; +SELECT CAST(f1 AS varchar) FROM CHAR_TBL; SELECT f1 AS eight FROM VARCHAR_TBL UNION ALL |