diff options
26 files changed, 1479 insertions, 74 deletions
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index a326960ff4d..2736868fb06 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1145,10 +1145,36 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales <para> In strict SQL, <literal>GROUP BY</literal> can only group by columns of - the source table but <productname>PostgreSQL</productname> extends + the source table, but <productname>PostgreSQL</productname> extends this to also allow <literal>GROUP BY</literal> to group by columns in the select list. Grouping by value expressions instead of simple - column names is also allowed. + column names is also allowed (but <literal>GROUP BY</literal> + expressions cannot contain aggregate functions or window functions). + </para> + + <para> + PostgreSQL also supports the syntax <literal>GROUP BY ALL</literal>, + which is equivalent to explicitly writing all select-list entries that + do not contain either an aggregate function or a window function. + This can greatly simplify ad-hoc exploration of data. + As an example, these queries are equivalent: +<screen> +<prompt>=></prompt> <userinput>SELECT a, b, a + b, sum(c) FROM test1 GROUP BY ALL;</userinput> + a | b | ?column? | sum +---+---+----------+---- + 1 | 4 | 5 | 9 + 2 | 5 | 7 | 12 + 3 | 6 | 9 | 15 +(3 rows) + +<prompt>=></prompt> <userinput>SELECT a, b, a + b, sum(c) FROM test1 GROUP BY a, b, a + b;</userinput> + a | b | ?column? | sum +---+---+----------+---- + 1 | 4 | 5 | 9 + 2 | 5 | 7 | 12 + 3 | 6 | 9 | 15 +(3 rows) +</screen> </para> <indexterm> diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index d7089eac0be..5a3bcff7607 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -37,7 +37,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac [ { * | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ] [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ] [ WHERE <replaceable class="parameter">condition</replaceable> ] - [ GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] ] + [ GROUP BY { ALL | [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] } ] [ HAVING <replaceable class="parameter">condition</replaceable> ] [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ] @@ -796,7 +796,7 @@ WHERE <replaceable class="parameter">condition</replaceable> <para> The optional <literal>GROUP BY</literal> clause has the general form <synopsis> -GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] +GROUP BY { ALL | [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] } </synopsis> </para> @@ -808,21 +808,31 @@ GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</rep <replaceable class="parameter">grouping_element</replaceable> can be an input column name, or the name or ordinal number of an output column (<command>SELECT</command> list item), or an arbitrary - expression formed from input-column values. In case of ambiguity, + expression formed from input-column values; however, it cannot contain + an aggregate function or a window function. In case of ambiguity, a <literal>GROUP BY</literal> name will be interpreted as an input-column name rather than an output column name. </para> <para> + The form <literal>GROUP BY ALL</literal> with no explicit + <replaceable class="parameter">grouping_elements</replaceable> + provided is equivalent to writing <literal>GROUP BY</literal> with the + numbers of all <command>SELECT</command> output columns that do not + contain either an aggregate function or a window function. + </para> + + <para> If any of <literal>GROUPING SETS</literal>, <literal>ROLLUP</literal> or <literal>CUBE</literal> are present as grouping elements, then the <literal>GROUP BY</literal> clause as a whole defines some number of independent <replaceable>grouping sets</replaceable>. The effect of this is equivalent to constructing a <literal>UNION ALL</literal> between - subqueries with the individual grouping sets as their + subqueries having the individual grouping sets as their <literal>GROUP BY</literal> clauses. The optional <literal>DISTINCT</literal> - clause removes duplicate sets before processing; it does <emphasis>not</emphasis> - transform the <literal>UNION ALL</literal> into a <literal>UNION DISTINCT</literal>. + key word removes duplicate grouping sets before processing; it does <emphasis>not</emphasis> + transform the implied <literal>UNION ALL</literal> into + a <literal>UNION DISTINCT</literal>. For further details on the handling of grouping sets see <xref linkend="queries-grouping-sets"/>. </para> diff --git a/doc/src/sgml/ref/select_into.sgml b/doc/src/sgml/ref/select_into.sgml index ae7e6bed24f..233f9bfa284 100644 --- a/doc/src/sgml/ref/select_into.sgml +++ b/doc/src/sgml/ref/select_into.sgml @@ -27,15 +27,15 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] <replaceable class="parameter">new_table</replaceable> [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ] [ WHERE <replaceable class="parameter">condition</replaceable> ] - [ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ] + [ GROUP BY { ALL | [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] } ] [ HAVING <replaceable class="parameter">condition</replaceable> ] [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ] [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ] [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ] - [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ] - [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ] + [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } ] + [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">from_reference</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ] </synopsis> </refsynopsisdiv> diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 109713315c0..eceab341255 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -3134,6 +3134,10 @@ XLogNeedsFlush(XLogRecPtr record) */ if (!XLogInsertAllowed()) { + /* Quick exit if already known to be updated or cannot be updated */ + if (!updateMinRecoveryPoint || record <= LocalMinRecoveryPoint) + return false; + /* * An invalid minRecoveryPoint means that we need to recover all the * WAL, i.e., we're doing crash recovery. We never modify the control @@ -3143,11 +3147,10 @@ XLogNeedsFlush(XLogRecPtr record) * it has not replayed all WAL available when doing crash recovery. */ if (XLogRecPtrIsInvalid(LocalMinRecoveryPoint) && InRecovery) + { updateMinRecoveryPoint = false; - - /* Quick exit if already known to be updated or cannot be updated */ - if (record <= LocalMinRecoveryPoint || !updateMinRecoveryPoint) return false; + } /* * Update local copy of minRecoveryPoint. But if the lock is busy, diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c index ed9aeee24bc..d23474da4fb 100644 --- a/src/backend/catalog/namespace.c +++ b/src/backend/catalog/namespace.c @@ -2753,6 +2753,9 @@ StatisticsObjIsVisibleExt(Oid stxid, bool *is_missing) { Oid namespaceId = lfirst_oid(l); + if (namespaceId == myTempNamespace) + continue; /* do not look in temp namespace */ + if (namespaceId == stxnamespace) { /* Found it first in path */ diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 132e8a35a39..d7ff36d89be 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -2254,10 +2254,9 @@ set_dummy_rel_pathlist(RelOptInfo *rel) * return false. */ static bool -find_window_run_conditions(Query *subquery, RangeTblEntry *rte, - AttrNumber attno, WindowFunc *wfunc, OpExpr *opexpr, - bool wfunc_left, bool *keep_original, - Bitmapset **run_cond_attrs) +find_window_run_conditions(Query *subquery, AttrNumber attno, + WindowFunc *wfunc, OpExpr *opexpr, bool wfunc_left, + bool *keep_original, Bitmapset **run_cond_attrs) { Oid prosupport; Expr *otherexpr; @@ -2445,7 +2444,7 @@ find_window_run_conditions(Query *subquery, RangeTblEntry *rte, * will use the runCondition to stop returning tuples. */ static bool -check_and_push_window_quals(Query *subquery, RangeTblEntry *rte, Node *clause, +check_and_push_window_quals(Query *subquery, Node *clause, Bitmapset **run_cond_attrs) { OpExpr *opexpr = (OpExpr *) clause; @@ -2485,9 +2484,8 @@ check_and_push_window_quals(Query *subquery, RangeTblEntry *rte, Node *clause, TargetEntry *tle = list_nth(subquery->targetList, var1->varattno - 1); WindowFunc *wfunc = (WindowFunc *) tle->expr; - if (find_window_run_conditions(subquery, rte, tle->resno, wfunc, - opexpr, true, &keep_original, - run_cond_attrs)) + if (find_window_run_conditions(subquery, tle->resno, wfunc, opexpr, + true, &keep_original, run_cond_attrs)) return keep_original; } @@ -2498,9 +2496,8 @@ check_and_push_window_quals(Query *subquery, RangeTblEntry *rte, Node *clause, TargetEntry *tle = list_nth(subquery->targetList, var2->varattno - 1); WindowFunc *wfunc = (WindowFunc *) tle->expr; - if (find_window_run_conditions(subquery, rte, tle->resno, wfunc, - opexpr, false, &keep_original, - run_cond_attrs)) + if (find_window_run_conditions(subquery, tle->resno, wfunc, opexpr, + false, &keep_original, run_cond_attrs)) return keep_original; } @@ -2622,7 +2619,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel, * runCondition. */ if (!subquery->hasWindowFuncs || - check_and_push_window_quals(subquery, rte, clause, + check_and_push_window_quals(subquery, clause, &run_cond_attrs)) { /* diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 28a4ae64440..6bd0f4a5dc3 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -23,6 +23,8 @@ */ #include "postgres.h" +#include <math.h> + #include "access/htup_details.h" #include "catalog/pg_type.h" #include "miscadmin.h" @@ -74,6 +76,8 @@ static List *generate_append_tlist(List *colTypes, List *colCollations, List *input_tlists, List *refnames_tlist); static List *generate_setop_grouplist(SetOperationStmt *op, List *targetlist); +static PathTarget *create_setop_pathtarget(PlannerInfo *root, List *tlist, + List *child_pathlist); /* @@ -803,7 +807,8 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root, /* Build result relation. */ result_rel = fetch_upper_rel(root, UPPERREL_SETOP, relids); - result_rel->reltarget = create_pathtarget(root, tlist); + result_rel->reltarget = create_setop_pathtarget(root, tlist, + cheapest_pathlist); result_rel->consider_parallel = consider_parallel; result_rel->consider_startup = (root->tuple_fraction > 0); @@ -892,7 +897,7 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root, path = (Path *) create_agg_path(root, result_rel, apath, - create_pathtarget(root, tlist), + result_rel->reltarget, AGG_HASHED, AGGSPLIT_SIMPLE, groupList, @@ -908,7 +913,7 @@ generate_union_paths(SetOperationStmt *op, PlannerInfo *root, path = (Path *) create_agg_path(root, result_rel, gpath, - create_pathtarget(root, tlist), + result_rel->reltarget, AGG_HASHED, AGGSPLIT_SIMPLE, groupList, @@ -1130,7 +1135,18 @@ generate_nonunion_paths(SetOperationStmt *op, PlannerInfo *root, /* Build result relation. */ result_rel = fetch_upper_rel(root, UPPERREL_SETOP, bms_union(lrel->relids, rrel->relids)); - result_rel->reltarget = create_pathtarget(root, tlist); + + /* + * Create the PathTarget and set the width accordingly. For EXCEPT, since + * the set op result won't contain rows from the rpath, we only account + * for the width of the lpath. For INTERSECT, use both input paths. + */ + if (op->op == SETOP_EXCEPT) + result_rel->reltarget = create_setop_pathtarget(root, tlist, + list_make1(lpath)); + else + result_rel->reltarget = create_setop_pathtarget(root, tlist, + list_make2(lpath, rpath)); /* * Estimate number of distinct groups that we'll need hashtable entries @@ -1619,3 +1635,38 @@ generate_setop_grouplist(SetOperationStmt *op, List *targetlist) Assert(lg == NULL); return grouplist; } + +/* + * create_setop_pathtarget + * Do the normal create_pathtarget() work, plus set the resulting + * PathTarget's width to the average width of the Paths in child_pathlist + * weighted using the estimated row count of each path. + * + * Note: This is required because set op target lists use varno==0, which + * results in a type default width estimate rather than one that's based on + * statistics of the columns from the set op children. + */ +static PathTarget * +create_setop_pathtarget(PlannerInfo *root, List *tlist, List *child_pathlist) +{ + PathTarget *reltarget; + ListCell *lc; + double parent_rows = 0; + double parent_size = 0; + + reltarget = create_pathtarget(root, tlist); + + /* Calculate the total rows and total size. */ + foreach(lc, child_pathlist) + { + Path *path = (Path *) lfirst(lc); + + parent_rows += path->rows; + parent_size += path->parent->reltarget->width * path->rows; + } + + if (parent_rows > 0) + reltarget->width = rint(parent_size / parent_rows); + + return reltarget; +} diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 5aeb54eb5f6..3b392b084ad 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1467,12 +1467,14 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt, qry->groupClause = transformGroupClause(pstate, stmt->groupClause, + stmt->groupByAll, &qry->groupingSets, &qry->targetList, qry->sortClause, EXPR_KIND_GROUP_BY, false /* allow SQL92 rules */ ); qry->groupDistinct = stmt->groupDistinct; + qry->groupByAll = stmt->groupByAll; if (stmt->distinctClause == NIL) { diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 9fd48acb1f8..f1def67ac7c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -120,6 +120,7 @@ typedef struct SelectLimit typedef struct GroupClause { bool distinct; + bool all; List *list; } GroupClause; @@ -12993,6 +12994,7 @@ simple_select: n->whereClause = $6; n->groupClause = ($7)->list; n->groupDistinct = ($7)->distinct; + n->groupByAll = ($7)->all; n->havingClause = $8; n->windowClause = $9; $$ = (Node *) n; @@ -13010,6 +13012,7 @@ simple_select: n->whereClause = $6; n->groupClause = ($7)->list; n->groupDistinct = ($7)->distinct; + n->groupByAll = ($7)->all; n->havingClause = $8; n->windowClause = $9; $$ = (Node *) n; @@ -13507,14 +13510,24 @@ group_clause: GroupClause *n = (GroupClause *) palloc(sizeof(GroupClause)); n->distinct = $3 == SET_QUANTIFIER_DISTINCT; + n->all = false; n->list = $4; $$ = n; } + | GROUP_P BY ALL + { + GroupClause *n = (GroupClause *) palloc(sizeof(GroupClause)); + n->distinct = false; + n->all = true; + n->list = NIL; + $$ = n; + } | /*EMPTY*/ { GroupClause *n = (GroupClause *) palloc(sizeof(GroupClause)); n->distinct = false; + n->all = false; n->list = NIL; $$ = n; } @@ -17618,6 +17631,7 @@ PLpgSQL_Expr: opt_distinct_clause opt_target_list n->whereClause = $4; n->groupClause = ($5)->list; n->groupDistinct = ($5)->distinct; + n->groupByAll = ($5)->all; n->havingClause = $6; n->windowClause = $7; n->sortClause = $8; diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 9f20a70ce13..ca26f6f61f2 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -2598,6 +2598,9 @@ transformGroupingSet(List **flatresult, * GROUP BY items will be added to the targetlist (as resjunk columns) * if not already present, so the targetlist must be passed by reference. * + * If GROUP BY ALL is specified, the groupClause will be inferred to be all + * non-aggregate, non-window expressions in the targetlist. + * * This is also used for window PARTITION BY clauses (which act almost the * same, but are always interpreted per SQL99 rules). * @@ -2622,6 +2625,7 @@ transformGroupingSet(List **flatresult, * * pstate ParseState * grouplist clause to transform + * groupByAll is this a GROUP BY ALL statement? * groupingSets reference to list to contain the grouping set tree * targetlist reference to TargetEntry list * sortClause ORDER BY clause (SortGroupClause nodes) @@ -2629,7 +2633,8 @@ transformGroupingSet(List **flatresult, * useSQL99 SQL99 rather than SQL92 syntax */ List * -transformGroupClause(ParseState *pstate, List *grouplist, List **groupingSets, +transformGroupClause(ParseState *pstate, List *grouplist, bool groupByAll, + List **groupingSets, List **targetlist, List *sortClause, ParseExprKind exprKind, bool useSQL99) { @@ -2640,6 +2645,63 @@ transformGroupClause(ParseState *pstate, List *grouplist, List **groupingSets, bool hasGroupingSets = false; Bitmapset *seen_local = NULL; + /* Handle GROUP BY ALL */ + if (groupByAll) + { + /* There cannot have been any explicit grouplist items */ + Assert(grouplist == NIL); + + /* Iterate over targets, adding acceptable ones to the result list */ + foreach_ptr(TargetEntry, tle, *targetlist) + { + /* Ignore junk TLEs */ + if (tle->resjunk) + continue; + + /* + * TLEs containing aggregates are not okay to add to GROUP BY + * (compare checkTargetlistEntrySQL92). But the SQL standard + * directs us to skip them, so it's fine. + */ + if (pstate->p_hasAggs && + contain_aggs_of_level((Node *) tle->expr, 0)) + continue; + + /* + * Likewise, TLEs containing window functions are not okay to add + * to GROUP BY. At this writing, the SQL standard is silent on + * what to do with them, but by analogy to aggregates we'll just + * skip them. + */ + if (pstate->p_hasWindowFuncs && + contain_windowfuncs((Node *) tle->expr)) + continue; + + /* + * Otherwise, add the TLE to the result using default sort/group + * semantics. We specify the parse location as the TLE's + * location, despite the comment for addTargetToGroupList + * discouraging that. The only other thing we could point to is + * the ALL keyword, which seems unhelpful when there are multiple + * TLEs. + */ + result = addTargetToGroupList(pstate, tle, + result, *targetlist, + exprLocation((Node *) tle->expr)); + } + + /* If we found any acceptable targets, we're done */ + if (result != NIL) + return result; + + /* + * Otherwise, the SQL standard says to treat it like "GROUP BY ()". + * Build a representation of that, and let the rest of this function + * handle it. + */ + grouplist = list_make1(makeGroupingSet(GROUPING_SET_EMPTY, NIL, -1)); + } + /* * Recursively flatten implicit RowExprs. (Technically this is only needed * for GROUP BY, per the syntax rules for grouping sets, but we do it @@ -2818,6 +2880,7 @@ transformWindowDefinitions(ParseState *pstate, true /* force SQL99 rules */ ); partitionClause = transformGroupClause(pstate, windef->partitionClause, + false /* not GROUP BY ALL */ , NULL, targetlist, orderClause, diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index defcdaa8b34..c6d83d67b87 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -6186,7 +6186,9 @@ get_basic_select_query(Query *query, deparse_context *context) save_ingroupby = context->inGroupBy; context->inGroupBy = true; - if (query->groupingSets == NIL) + if (query->groupByAll) + appendStringInfoString(buf, "ALL"); + else if (query->groupingSets == NIL) { sep = ""; foreach(l, query->groupClause) diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 62c21d3670d..4e7d5ceb7b8 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202509191 +#define CATALOG_VERSION_NO 202509291 #endif diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index f1706df58fd..ac0e02a1db7 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -214,7 +214,8 @@ typedef struct Query List *returningList; /* return-values list (of TargetEntry) */ List *groupClause; /* a list of SortGroupClause's */ - bool groupDistinct; /* is the group by clause distinct? */ + bool groupDistinct; /* was GROUP BY DISTINCT used? */ + bool groupByAll; /* was GROUP BY ALL used? */ List *groupingSets; /* a list of GroupingSet's if present */ @@ -2192,6 +2193,7 @@ typedef struct SelectStmt Node *whereClause; /* WHERE qualification */ List *groupClause; /* GROUP BY clauses */ bool groupDistinct; /* Is this GROUP BY DISTINCT? */ + bool groupByAll; /* Is this GROUP BY ALL? */ Node *havingClause; /* HAVING conditional-expression */ List *windowClause; /* WINDOW window_name AS (...), ... */ diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h index 3e9894926de..ede3903d1dd 100644 --- a/src/include/parser/parse_clause.h +++ b/src/include/parser/parse_clause.h @@ -26,6 +26,7 @@ extern Node *transformLimitClause(ParseState *pstate, Node *clause, ParseExprKind exprKind, const char *constructName, LimitOption limitOption); extern List *transformGroupClause(ParseState *pstate, List *grouplist, + bool groupByAll, List **groupingSets, List **targetlist, List *sortClause, ParseExprKind exprKind, bool useSQL99); diff --git a/src/test/modules/injection_points/injection_points--1.0.sql b/src/test/modules/injection_points/injection_points--1.0.sql index 5f5657b2043..a7b61fbdfe6 100644 --- a/src/test/modules/injection_points/injection_points--1.0.sql +++ b/src/test/modules/injection_points/injection_points--1.0.sql @@ -100,6 +100,16 @@ AS 'MODULE_PATHNAME', 'injection_points_stats_numcalls' LANGUAGE C STRICT; -- +-- injection_points_stats_count() +-- +-- Return the number of entries stored in the pgstats hash table. +-- +CREATE FUNCTION injection_points_stats_count() +RETURNS bigint +AS 'MODULE_PATHNAME', 'injection_points_stats_count' +LANGUAGE C STRICT; + +-- -- injection_points_stats_drop() -- -- Drop all statistics of injection points. diff --git a/src/test/modules/injection_points/injection_stats.c b/src/test/modules/injection_points/injection_stats.c index ca8df4ad217..158e1631af9 100644 --- a/src/test/modules/injection_points/injection_stats.c +++ b/src/test/modules/injection_points/injection_stats.c @@ -40,6 +40,7 @@ static const PgStat_KindInfo injection_stats = { .name = "injection_points", .fixed_amount = false, /* Bounded by the number of points */ .write_to_file = true, + .track_entry_count = true, /* Injection points are system-wide */ .accessed_across_databases = true, @@ -196,6 +197,17 @@ injection_points_stats_numcalls(PG_FUNCTION_ARGS) PG_RETURN_INT64(entry->numcalls); } +/* + * SQL function returning the number of entries allocated for injection + * points in the shared hashtable of pgstats. + */ +PG_FUNCTION_INFO_V1(injection_points_stats_count); +Datum +injection_points_stats_count(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(pgstat_get_entry_count(PGSTAT_KIND_INJECTION)); +} + /* Only used by injection_points_stats_drop() */ static bool match_inj_entries(PgStatShared_HashEntry *entry, Datum match_data) diff --git a/src/test/modules/injection_points/injection_stats_fixed.c b/src/test/modules/injection_points/injection_stats_fixed.c index bc54c79d190..74c35fcbfa7 100644 --- a/src/test/modules/injection_points/injection_stats_fixed.c +++ b/src/test/modules/injection_points/injection_stats_fixed.c @@ -152,6 +152,8 @@ pgstat_report_inj_fixed(uint32 numattach, stats_shmem = pgstat_get_custom_shmem_data(PGSTAT_KIND_INJECTION_FIXED); + LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE); + pgstat_begin_changecount_write(&stats_shmem->changecount); stats_shmem->stats.numattach += numattach; stats_shmem->stats.numdetach += numdetach; @@ -159,6 +161,8 @@ pgstat_report_inj_fixed(uint32 numattach, stats_shmem->stats.numcached += numcached; stats_shmem->stats.numloaded += numloaded; pgstat_end_changecount_write(&stats_shmem->changecount); + + LWLockRelease(&stats_shmem->lock); } /* diff --git a/src/test/modules/injection_points/t/001_stats.pl b/src/test/modules/injection_points/t/001_stats.pl index 25de5fc46fe..47ab58d0e9b 100644 --- a/src/test/modules/injection_points/t/001_stats.pl +++ b/src/test/modules/injection_points/t/001_stats.pl @@ -36,6 +36,9 @@ $node->safe_psql('postgres', "SELECT injection_points_run('stats-notice');"); my $numcalls = $node->safe_psql('postgres', "SELECT injection_points_stats_numcalls('stats-notice');"); is($numcalls, '2', 'number of stats calls'); +my $entrycount = + $node->safe_psql('postgres', "SELECT injection_points_stats_count();"); +is($entrycount, '1', 'number of entries'); my $fixedstats = $node->safe_psql('postgres', "SELECT * FROM injection_points_stats_fixed();"); is($fixedstats, '1|0|2|0|0', 'fixed stats after some calls'); @@ -55,6 +58,9 @@ $node->restart; $numcalls = $node->safe_psql('postgres', "SELECT injection_points_stats_numcalls('stats-notice');"); is($numcalls, '3', 'number of stats after clean restart'); +$entrycount = + $node->safe_psql('postgres', "SELECT injection_points_stats_count();"); +is($entrycount, '1', 'number of entries after clean restart'); $fixedstats = $node->safe_psql('postgres', "SELECT * FROM injection_points_stats_fixed();"); is($fixedstats, '1|0|2|1|1', 'fixed stats after clean restart'); @@ -65,6 +71,9 @@ $node->start; $numcalls = $node->safe_psql('postgres', "SELECT injection_points_stats_numcalls('stats-notice');"); is($numcalls, '', 'number of stats after crash'); +$entrycount = + $node->safe_psql('postgres', "SELECT injection_points_stats_count();"); +is($entrycount, '0', 'number of entries after crash'); $fixedstats = $node->safe_psql('postgres', "SELECT * FROM injection_points_stats_fixed();"); is($fixedstats, '0|0|0|0|0', 'fixed stats after crash'); @@ -81,6 +90,9 @@ $node->safe_psql('postgres', "SELECT injection_points_stats_drop();"); $numcalls = $node->safe_psql('postgres', "SELECT injection_points_stats_numcalls('stats-notice');"); is($numcalls, '', 'no stats after drop via SQL function'); +$entrycount = + $node->safe_psql('postgres', "SELECT injection_points_stats_count();"); +is($entrycount, '0', 'number of entries after drop via SQL function'); # Stop the server, disable the module, then restart. The server # should be able to come up. diff --git a/src/test/modules/test_bitmapset/expected/test_bitmapset.out b/src/test/modules/test_bitmapset/expected/test_bitmapset.out index abbfef1f7a6..be7b6399c82 100644 --- a/src/test/modules/test_bitmapset/expected/test_bitmapset.out +++ b/src/test/modules/test_bitmapset/expected/test_bitmapset.out @@ -21,6 +21,13 @@ SELECT test_bms_make_singleton(1000) AS result; (b 1000) (1 row) +-- Test module check +SELECT test_bms_make_singleton(NULL) AS result; + result +-------- + +(1 row) + -- bms_add_member() SELECT test_bms_add_member('(b 1)', -1); -- error ERROR: negative bitmapset member not allowed @@ -52,6 +59,13 @@ SELECT test_bms_add_member('(b 10)', 10) AS result; (b 10) (1 row) +-- Test module check +SELECT test_bms_add_member('(b)', NULL) AS result; + result +-------- + +(1 row) + -- bms_replace_members() SELECT test_bms_replace_members(NULL, '(b 1 2 3)') AS result; result @@ -83,6 +97,38 @@ SELECT test_bms_replace_members('(b 1 2)', '(b 3 5 7)') AS result; (b 3 5 7) (1 row) +-- Force repalloc() with larger set +SELECT test_bms_replace_members('(b 1 2 3 4 5)', '(b 500 600)') AS result; + result +------------- + (b 500 600) +(1 row) + +-- Test module checks +SELECT test_bms_replace_members('(b 1 2 3)', NULL) AS result; + result +-------- + +(1 row) + +SELECT test_bms_replace_members('(b 5)', NULL) AS result; + result +-------- + +(1 row) + +SELECT test_bms_replace_members(NULL, '(b 5)') AS result; + result +-------- + (b 5) +(1 row) + +SELECT test_bms_replace_members(NULL, NULL) AS result; + result +-------- + +(1 row) + -- bms_del_member() SELECT test_bms_del_member('(b)', -20); -- error ERROR: negative bitmapset member not allowed @@ -124,6 +170,101 @@ SELECT test_bms_del_member(test_bms_add_range('(b)', 30, 34), 32) AS result; (b 30 31 33 34) (1 row) +-- Force word count changes +SELECT test_bms_del_member('(b 1 200)', 200) AS result; + result +-------- + (b 1) +(1 row) + +SELECT test_bms_del_member('(b 1 50 100 200)', 200) AS result; + result +-------------- + (b 1 50 100) +(1 row) + +SELECT test_bms_del_member('(b 1 50 100 200)', 100) AS result; + result +-------------- + (b 1 50 200) +(1 row) + +-- Test module checks +SELECT test_bms_del_member('(b 42)', 42) AS result; + result +-------- + +(1 row) + +SELECT test_bms_del_member('(b 5)', NULL) AS result; + result +-------- + +(1 row) + +-- bms_del_members() +SELECT test_bms_del_members('(b)', '(b 10)') AS result; + result +-------- + +(1 row) + +SELECT test_bms_del_members('(b 10)', '(b 10)') AS result; + result +-------- + +(1 row) + +SELECT test_bms_del_members('(b 10)', '(b 5)') AS result; + result +-------- + (b 10) +(1 row) + +SELECT test_bms_del_members('(b 1 2 3)', '(b 2)') AS result; + result +--------- + (b 1 3) +(1 row) + +SELECT test_bms_del_members('(b 5 100)', '(b 100)') AS result; + result +-------- + (b 5) +(1 row) + +SELECT test_bms_del_members('(b 5 100 200)', '(b 200)') AS result; + result +----------- + (b 5 100) +(1 row) + +-- Force word count changes +SELECT test_bms_del_members('(b 1 2 100 200 300)', '(b 1 2)') AS result; + result +----------------- + (b 100 200 300) +(1 row) + +SELECT test_bms_del_members('(b 1 2 100 200 300)', '(b 200 300)') AS result; + result +------------- + (b 1 2 100) +(1 row) + +-- Test module checks +SELECT test_bms_del_members('(b 5)', NULL) AS result; + result +-------- + (b 5) +(1 row) + +SELECT test_bms_del_members(NULL, '(b 5)') AS result; + result +-------- + +(1 row) + -- bms_join() SELECT test_bms_join('(b 1 3 5)', NULL) AS result; result @@ -149,8 +290,40 @@ SELECT test_bms_join('(b 1 3 5)', '(b 1 4 5)') AS result; (b 1 3 4 5) (1 row) +-- Force word count changes +SELECT test_bms_join('(b 5)', '(b 100)') AS result; + result +----------- + (b 5 100) +(1 row) + +SELECT test_bms_join('(b 1 2)', '(b 100 200 300)') AS result; + result +--------------------- + (b 1 2 100 200 300) +(1 row) + +-- Test module checks +SELECT test_bms_join('(b 5)', NULL) AS result; + result +-------- + (b 5) +(1 row) + +SELECT test_bms_join(NULL, '(b 5)') AS result; + result +-------- + (b 5) +(1 row) + +SELECT test_bms_join(NULL, NULL) AS result; + result +-------- + +(1 row) + -- bms_union() --- Overlapping sets. +-- Overlapping sets SELECT test_bms_union('(b 1 3 5)', '(b 3 5 7)') AS result; result ------------- @@ -181,6 +354,38 @@ SELECT test_bms_union( (b 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20) (1 row) +-- Union with varrying word counts +SELECT test_bms_union('(b 1 2)', '(b 100 300)') AS result; + result +----------------- + (b 1 2 100 300) +(1 row) + +SELECT test_bms_union('(b 100 300)', '(b 1 2)') AS result; + result +----------------- + (b 1 2 100 300) +(1 row) + +-- Test module checks +SELECT test_bms_union('(b 5)', NULL) AS result; + result +-------- + (b 5) +(1 row) + +SELECT test_bms_union(NULL, '(b 5)') AS result; + result +-------- + (b 5) +(1 row) + +SELECT test_bms_union(NULL, NULL) AS result; + result +-------- + +(1 row) + -- bms_intersect() -- Overlapping sets SELECT test_bms_intersect('(b 1 3 5)', '(b 3 5 7)') AS result; @@ -196,13 +401,51 @@ SELECT test_bms_intersect('(b 1 3 5)', '(b 2 4 6)') AS result; (1 row) --- Intersect with empty. +-- Intersect with empty SELECT test_bms_intersect('(b 1 3 5)', '(b)') AS result; result -------- (1 row) +-- Intersect with varrying word counts +SELECT test_bms_intersect('(b 1 300)', '(b 1 2 3 4 5)') AS result; + result +-------- + (b 1) +(1 row) + +SELECT test_bms_intersect('(b 1 2 3 4 5)', '(b 1 300)') AS result; + result +-------- + (b 1) +(1 row) + +-- Test module checks +SELECT test_bms_intersect('(b 1)', '(b 2)') AS result; + result +-------- + +(1 row) + +SELECT test_bms_intersect('(b 5)', NULL) AS result; + result +-------- + +(1 row) + +SELECT test_bms_intersect(NULL, '(b 5)') AS result; + result +-------- + +(1 row) + +SELECT test_bms_intersect(NULL, NULL) AS result; + result +-------- + +(1 row) + -- bms_int_members() -- Overlapping sets SELECT test_bms_int_members('(b 1 3 5)', '(b 3 5 7)') AS result; @@ -218,7 +461,7 @@ SELECT test_bms_int_members('(b 1 3 5)', '(b 2 4 6)') AS result; (1 row) --- Intersect with empty. +-- Intersect with empty SELECT test_bms_int_members('(b 1 3 5)', '(b)') AS result; result -------- @@ -232,6 +475,31 @@ SELECT test_bms_int_members('(b 0 31 32 63 64)', '(b 31 32 64 65)') AS result; (b 31 32 64) (1 row) +-- Test module checks +SELECT test_bms_int_members('(b 1)', '(b 2)') AS result; + result +-------- + +(1 row) + +SELECT test_bms_int_members('(b 5)', NULL) AS result; + result +-------- + +(1 row) + +SELECT test_bms_int_members(NULL, '(b 5)') AS result; + result +-------- + +(1 row) + +SELECT test_bms_int_members(NULL, NULL) AS result; + result +-------- + +(1 row) + -- bms_difference() -- Overlapping sets SELECT test_bms_difference('(b 1 3 5)', '(b 3 5 7)') AS result; @@ -271,6 +539,44 @@ SELECT test_bms_difference( (b 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49) (1 row) +-- Difference with different word counts +SELECT test_bms_difference('(b 5 100)', '(b 5)') AS result; + result +--------- + (b 100) +(1 row) + +SELECT test_bms_difference('(b 1 2 100 200)', '(b 1 2)') AS result; + result +------------- + (b 100 200) +(1 row) + +-- Test module checks +SELECT test_bms_difference('(b 5)', '(b 5 10)') AS result; + result +-------- + +(1 row) + +SELECT test_bms_difference('(b 5)', NULL) AS result; + result +-------- + (b 5) +(1 row) + +SELECT test_bms_difference(NULL, '(b 5)') AS result; + result +-------- + +(1 row) + +SELECT test_bms_difference(NULL, NULL) AS result; + result +-------- + +(1 row) + -- bms_is_member() SELECT test_bms_is_member('(b)', -5); -- error ERROR: negative bitmapset member not allowed @@ -298,6 +604,13 @@ SELECT test_bms_is_member('(b)', 1) AS result; f (1 row) +-- Test module check +SELECT test_bms_is_member('(b 5)', NULL) AS result; + result +-------- + f +(1 row) + -- bms_member_index() SELECT test_bms_member_index(NULL, 1) AS result; result @@ -323,6 +636,38 @@ SELECT test_bms_member_index('(b 1 3 5)', 3) AS result; 1 (1 row) +-- Member index with various word positions +SELECT test_bms_member_index('(b 100 200)', 100) AS result; + result +-------- + 0 +(1 row) + +SELECT test_bms_member_index('(b 100 200)', 200) AS result; + result +-------- + 1 +(1 row) + +SELECT test_bms_member_index('(b 1 50 100 200)', 200) AS result; + result +-------- + 3 +(1 row) + +-- Test module checks +SELECT test_bms_member_index('', 5) AS result; + result +-------- + -1 +(1 row) + +SELECT test_bms_member_index(NULL, 5) AS result; + result +-------- + -1 +(1 row) + -- bms_num_members() SELECT test_bms_num_members('(b)') AS result; result @@ -373,6 +718,38 @@ SELECT test_bms_equal('(b 1 3 5)', '(b 2 4 6)') AS result; f (1 row) +-- Equal with different word counts +SELECT test_bms_equal('(b 5)', '(b 100)') AS result; + result +-------- + f +(1 row) + +SELECT test_bms_equal('(b 5 10)', '(b 100 200 300)') AS result; + result +-------- + f +(1 row) + +-- Test module checks +SELECT test_bms_equal('(b 5)', NULL) AS result; + result +-------- + f +(1 row) + +SELECT test_bms_equal(NULL, '(b 5)') AS result; + result +-------- + f +(1 row) + +SELECT test_bms_equal(NULL, NULL) AS result; + result +-------- + t +(1 row) + -- bms_compare() SELECT test_bms_compare('(b)', '(b)') AS result; result @@ -419,6 +796,25 @@ SELECT test_bms_compare( -1 (1 row) +-- Test module checks +SELECT test_bms_compare('(b 5)', NULL) AS result; + result +-------- + 1 +(1 row) + +SELECT test_bms_compare(NULL, '(b 5)') AS result; + result +-------- + -1 +(1 row) + +SELECT test_bms_compare(NULL, NULL) AS result; + result +-------- + 0 +(1 row) + -- bms_add_range() SELECT test_bms_add_range('(b)', -5, 10); -- error ERROR: negative bitmapset member not allowed @@ -474,6 +870,56 @@ SELECT length(test_bms_add_range('(b)', 1000, 1100)) AS result; 508 (1 row) +-- Force word count expansion +SELECT test_bms_add_range('(b 5)', 100, 105) AS result; + result +------------------------------- + (b 5 100 101 102 103 104 105) +(1 row) + +SELECT length(test_bms_add_range('(b 1 2)', 200, 250)) AS result; + result +-------- + 211 +(1 row) + +-- Test module checks +SELECT test_bms_add_range('(b 5)', 5, NULL) AS result; + result +-------- + +(1 row) + +SELECT test_bms_add_range('(b 5)', NULL, 10) AS result; + result +-------- + +(1 row) + +SELECT test_bms_add_range('(b 5)', NULL, NULL) AS result; + result +-------- + +(1 row) + +SELECT test_bms_add_range(NULL, 5, 10) AS result; + result +------------------ + (b 5 6 7 8 9 10) +(1 row) + +SELECT test_bms_add_range(NULL, 10, 5) AS result; + result +-------- + +(1 row) + +SELECT test_bms_add_range(NULL, NULL, NULL) AS result; + result +-------- + +(1 row) + -- bms_membership() SELECT test_bms_membership('(b)') AS result; result @@ -493,6 +939,13 @@ SELECT test_bms_membership('(b 1 2)') AS result; 2 (1 row) +-- Test module check +SELECT test_bms_membership(NULL) AS result; + result +-------- + 0 +(1 row) + -- bms_is_empty() SELECT test_bms_is_empty(NULL) AS result; result @@ -512,7 +965,16 @@ SELECT test_bms_is_empty('(b 1)') AS result; f (1 row) +-- Test module check +SELECT test_bms_is_empty(NULL) AS result; + result +-------- + t +(1 row) + -- bms_singleton_member() +SELECT test_bms_singleton_member('(b)'); -- error +ERROR: bitmapset is empty SELECT test_bms_singleton_member('(b 1 2)'); -- error ERROR: bitmapset has multiple members SELECT test_bms_singleton_member('(b 42)') AS result; @@ -521,7 +983,20 @@ SELECT test_bms_singleton_member('(b 42)') AS result; 42 (1 row) +-- Test module check +SELECT test_bms_singleton_member(NULL) AS result; + result +-------- + +(1 row) + -- bms_get_singleton_member() +SELECT test_bms_get_singleton_member('(b)', 1000); + test_bms_get_singleton_member +------------------------------- + 1000 +(1 row) + -- Not a singleton, returns input default SELECT test_bms_get_singleton_member('(b 3 6)', 1000) AS result; result @@ -536,6 +1011,19 @@ SELECT test_bms_get_singleton_member('(b 400)', 1000) AS result; 400 (1 row) +-- Test module checks +SELECT test_bms_get_singleton_member('', 1000) AS result; + result +-------- + 1000 +(1 row) + +SELECT test_bms_get_singleton_member(NULL, -1) AS result; + result +-------- + -1 +(1 row) + -- bms_next_member() and bms_prev_member() -- First member SELECT test_bms_next_member('(b 5 10 15 20)', -1) AS result; @@ -593,6 +1081,56 @@ SELECT test_bms_prev_member('(b)', 100) AS result; -2 (1 row) +-- Negative prevbit should result in highest possible bit in set +SELECT test_bms_prev_member('(b 0 63 64 127)', -1) AS result; + result +-------- + 127 +(1 row) + +-- Test module checks +SELECT test_bms_next_member('', 5) AS result; + result +-------- + -2 +(1 row) + +SELECT test_bms_next_member('(b 5)', NULL) AS result; + result +-------- + -2 +(1 row) + +SELECT test_bms_next_member(NULL, 5) AS result; + result +-------- + -2 +(1 row) + +SELECT test_bms_next_member(NULL, NULL) AS result; + result +-------- + -2 +(1 row) + +SELECT test_bms_prev_member('', 5) AS result; + result +-------- + -2 +(1 row) + +SELECT test_bms_prev_member('(b 5)', NULL) AS result; + result +-------- + -2 +(1 row) + +SELECT test_bms_prev_member(NULL, 5) AS result; + result +-------- + -2 +(1 row) + -- bms_hash_value() SELECT test_bms_hash_value('(b)') = 0 AS result; result @@ -612,6 +1150,13 @@ SELECT test_bms_hash_value('(b 1 3 5)') != test_bms_hash_value('(b 2 4 6)') AS r t (1 row) +-- Test module check +SELECT test_bms_hash_value(NULL) AS result; + result +-------- + 0 +(1 row) + -- bms_overlap() SELECT test_bms_overlap('(b 1 3 5)', '(b 3 5 7)') AS result; result @@ -631,6 +1176,25 @@ SELECT test_bms_overlap('(b)', '(b 1 3 5)') AS result; f (1 row) +-- Test module checks +SELECT test_bms_overlap('(b 5)', NULL) AS result; + result +-------- + f +(1 row) + +SELECT test_bms_overlap(NULL, '(b 5)') AS result; + result +-------- + f +(1 row) + +SELECT test_bms_overlap(NULL, NULL) AS result; + result +-------- + f +(1 row) + -- bms_is_subset() SELECT test_bms_is_subset('(b)', '(b 1 3 5)') AS result; result @@ -663,6 +1227,38 @@ SELECT test_bms_is_subset(test_bms_add_range(NULL, 0, 31), t (1 row) +-- Is subset with shorter word counts? +SELECT test_bms_is_subset('(b 5 100)', '(b 5)') AS result; + result +-------- + f +(1 row) + +SELECT test_bms_is_subset('(b 1 2 50 100)', '(b 1 2)') AS result; + result +-------- + f +(1 row) + +-- Test module checks +SELECT test_bms_is_subset('(b 5)', NULL) AS result; + result +-------- + f +(1 row) + +SELECT test_bms_is_subset(NULL, '(b 5)') AS result; + result +-------- + t +(1 row) + +SELECT test_bms_is_subset(NULL, NULL) AS result; + result +-------- + t +(1 row) + -- bms_subset_compare() SELECT test_bms_subset_compare(NULL, NULL) AS result; result @@ -670,13 +1266,43 @@ SELECT test_bms_subset_compare(NULL, NULL) AS result; 0 (1 row) +SELECT test_bms_subset_compare(NULL, '(b 1 3)') AS result; + result +-------- + 1 +(1 row) + +SELECT test_bms_subset_compare('(b)', '(b)') AS result; + result +-------- + 0 +(1 row) + +SELECT test_bms_subset_compare('(b)', '(b 1)') AS result; + result +-------- + 1 +(1 row) + +SELECT test_bms_subset_compare('(b 1)', '(b)') AS result; + result +-------- + 2 +(1 row) + SELECT test_bms_subset_compare('(b 1 3)', NULL) AS result; result -------- 2 (1 row) -SELECT test_bms_subset_compare(NULL, '(b 1 3)') AS result; +SELECT test_bms_subset_compare('(b 1 3 5)', '(b 1 3 5)') AS result; + result +-------- + 0 +(1 row) + +SELECT test_bms_subset_compare('(b 1 3)', '(b 1 3 5)') AS result; result -------- 1 @@ -688,24 +1314,109 @@ SELECT test_bms_subset_compare('(b 1 3 5)', '(b 1 3)') AS result; 2 (1 row) -SELECT test_bms_subset_compare('(b 1 3)', '(b 1 3 5)') AS result; +SELECT test_bms_subset_compare('(b 1 2)', '(b 1 3)') AS result; + result +-------- + 3 +(1 row) + +SELECT test_bms_subset_compare('(b 1 2)', '(b 1 4)') AS result; + result +-------- + 3 +(1 row) + +SELECT test_bms_subset_compare('(b 1 3)', '(b 1 3 64)') AS result; result -------- 1 (1 row) -SELECT test_bms_subset_compare('(b 1 3 5)', '(b 1 3 5)') AS result; +SELECT test_bms_subset_compare('(b 1 3 64)', '(b 1 3)') AS result; result -------- - 0 + 2 +(1 row) + +SELECT test_bms_subset_compare('(b 1 3 64)', '(b 1 3 65)') AS result; + result +-------- + 3 +(1 row) + +SELECT test_bms_subset_compare('(b 1 3)', '(b 2 4)') AS result; + result +-------- + 3 +(1 row) + +SELECT test_bms_subset_compare('(b 1)', '(b 64)') AS result; + result +-------- + 3 +(1 row) + +SELECT test_bms_subset_compare('(b 0)', '(b 32)') AS result; + result +-------- + 3 (1 row) -SELECT test_bms_subset_compare('(b 1 3 5)', '(b 2 4 6)') AS result; +SELECT test_bms_subset_compare('(b 0)', '(b 64)') AS result; result -------- 3 (1 row) +SELECT test_bms_subset_compare('(b 64)', '(b 1)') AS result; + result +-------- + 3 +(1 row) + +SELECT test_bms_subset_compare('(b 1 2)', '(b 1 2 64)') AS result; + result +-------- + 1 +(1 row) + +SELECT test_bms_subset_compare('(b 64 200)', '(b 1 201)') AS result; + result +-------- + 3 +(1 row) + +SELECT test_bms_subset_compare('(b 1 64 65)', '(b 1 2 64)') AS result; + result +-------- + 3 +(1 row) + +SELECT test_bms_subset_compare('(b 2 64 128)', '(b 1 65)') AS result; + result +-------- + 3 +(1 row) + +-- Test module checks +SELECT test_bms_subset_compare('(b 5)', NULL) AS result; + result +-------- + 2 +(1 row) + +SELECT test_bms_subset_compare(NULL, '(b 5)') AS result; + result +-------- + 1 +(1 row) + +SELECT test_bms_subset_compare(NULL, NULL) AS result; + result +-------- + 0 +(1 row) + -- bms_copy() SELECT test_bms_copy(NULL) AS result; result @@ -719,13 +1430,14 @@ SELECT test_bms_copy('(b 1 3 5 7)') AS result; (b 1 3 5 7) (1 row) --- bms_add_members() -SELECT test_bms_add_member('(b)', 1000); -- error - test_bms_add_member ---------------------- - (b 1000) +-- Test module check +SELECT test_bms_copy(NULL) AS result; + result +-------- + (1 row) +-- bms_add_members() SELECT test_bms_add_members('(b 1 3)', '(b 5 7)') AS result; result ------------- @@ -769,6 +1481,13 @@ SELECT test_bitmap_hash('(b 1 3 5)') != test_bitmap_hash('(b 2 4 6)') AS result; t (1 row) +-- Test module check +SELECT test_bitmap_hash(NULL) AS result; + result +-------- + 0 +(1 row) + -- bitmap_match() SELECT test_bitmap_match('(b)', '(b)') AS result; result @@ -828,6 +1547,25 @@ SELECT (test_bitmap_match('(b)', '(b)') = 0) = t (1 row) +-- Test module checks +SELECT test_bitmap_match('(b 5)', NULL) AS result; + result +-------- + 1 +(1 row) + +SELECT test_bitmap_match(NULL, '(b 5)') AS result; + result +-------- + 1 +(1 row) + +SELECT test_bitmap_match(NULL, NULL) AS result; + result +-------- + 0 +(1 row) + -- bms_overlap_list() SELECT test_bms_overlap_list('(b 0)', ARRAY[0]) AS result; result @@ -866,6 +1604,30 @@ SELECT test_bms_overlap_list('(b 1)', ARRAY[]::integer[]) AS result; f (1 row) +-- Overlap list with negative numbers +SELECT test_bms_overlap_list('(b 5 10)', ARRAY[-1,5]) AS result; -- error +ERROR: negative bitmapset member not allowed +SELECT test_bms_overlap_list('(b 1 2 3)', ARRAY[-5,-1,0]) AS result; -- error +ERROR: negative bitmapset member not allowed +-- Test module checks +SELECT test_bms_overlap_list('(b 5)', NULL) AS result; + result +-------- + f +(1 row) + +SELECT test_bms_overlap_list(NULL, ARRAY[1,2,3]) AS result; + result +-------- + f +(1 row) + +SELECT test_bms_overlap_list(NULL, NULL) AS result; + result +-------- + f +(1 row) + -- bms_nonempty_difference() SELECT test_bms_nonempty_difference(NULL, '(b 1 3 5)') AS result; result @@ -897,6 +1659,44 @@ SELECT test_bms_nonempty_difference('(b 1 3 5)', '(b 1 3 5)') AS result; f (1 row) +-- Difference with different word counts +SELECT test_bms_nonempty_difference('(b 5)', '(b 100)') AS result; + result +-------- + t +(1 row) + +SELECT test_bms_nonempty_difference('(b 100)', '(b 5)') AS result; + result +-------- + t +(1 row) + +SELECT test_bms_nonempty_difference('(b 1 2)', '(b 50 100)') AS result; + result +-------- + t +(1 row) + +-- Test module checks +SELECT test_bms_nonempty_difference('(b 5)', NULL) AS result; + result +-------- + t +(1 row) + +SELECT test_bms_nonempty_difference(NULL, '(b 5)') AS result; + result +-------- + f +(1 row) + +SELECT test_bms_nonempty_difference(NULL, NULL) AS result; + result +-------- + f +(1 row) + -- random operations SELECT test_random_operations(-1, 10000, 81920, 0) > 0 AS result; result diff --git a/src/test/modules/test_bitmapset/sql/test_bitmapset.sql b/src/test/modules/test_bitmapset/sql/test_bitmapset.sql index 2b2c72c876b..c6e6bce60a3 100644 --- a/src/test/modules/test_bitmapset/sql/test_bitmapset.sql +++ b/src/test/modules/test_bitmapset/sql/test_bitmapset.sql @@ -6,6 +6,8 @@ SELECT test_bms_make_singleton(-1); SELECT test_bms_make_singleton(42) AS result; SELECT test_bms_make_singleton(0) AS result; SELECT test_bms_make_singleton(1000) AS result; +-- Test module check +SELECT test_bms_make_singleton(NULL) AS result; -- bms_add_member() SELECT test_bms_add_member('(b 1)', -1); -- error @@ -16,6 +18,8 @@ SELECT test_bms_add_member('(b 5)', 10) AS result; SELECT test_bms_add_member('(b 10)', 5) AS result; -- idempotent change SELECT test_bms_add_member('(b 10)', 10) AS result; +-- Test module check +SELECT test_bms_add_member('(b)', NULL) AS result; -- bms_replace_members() SELECT test_bms_replace_members(NULL, '(b 1 2 3)') AS result; @@ -23,6 +27,13 @@ SELECT test_bms_replace_members('(b 1 2 3)', NULL) AS result; SELECT test_bms_replace_members('(b 1 2 3)', '(b 3 5 6)') AS result; SELECT test_bms_replace_members('(b 1 2 3)', '(b 3 5)') AS result; SELECT test_bms_replace_members('(b 1 2)', '(b 3 5 7)') AS result; +-- Force repalloc() with larger set +SELECT test_bms_replace_members('(b 1 2 3 4 5)', '(b 500 600)') AS result; +-- Test module checks +SELECT test_bms_replace_members('(b 1 2 3)', NULL) AS result; +SELECT test_bms_replace_members('(b 5)', NULL) AS result; +SELECT test_bms_replace_members(NULL, '(b 5)') AS result; +SELECT test_bms_replace_members(NULL, NULL) AS result; -- bms_del_member() SELECT test_bms_del_member('(b)', -20); -- error @@ -34,15 +45,43 @@ SELECT test_bms_del_member('(b 1 2 3)', 2) AS result; SELECT test_bms_del_member(test_bms_del_member('(b 0 31 32 63 64)', 32), 63) AS result; -- Word boundary SELECT test_bms_del_member(test_bms_add_range('(b)', 30, 34), 32) AS result; +-- Force word count changes +SELECT test_bms_del_member('(b 1 200)', 200) AS result; +SELECT test_bms_del_member('(b 1 50 100 200)', 200) AS result; +SELECT test_bms_del_member('(b 1 50 100 200)', 100) AS result; +-- Test module checks +SELECT test_bms_del_member('(b 42)', 42) AS result; +SELECT test_bms_del_member('(b 5)', NULL) AS result; + +-- bms_del_members() +SELECT test_bms_del_members('(b)', '(b 10)') AS result; +SELECT test_bms_del_members('(b 10)', '(b 10)') AS result; +SELECT test_bms_del_members('(b 10)', '(b 5)') AS result; +SELECT test_bms_del_members('(b 1 2 3)', '(b 2)') AS result; +SELECT test_bms_del_members('(b 5 100)', '(b 100)') AS result; +SELECT test_bms_del_members('(b 5 100 200)', '(b 200)') AS result; +-- Force word count changes +SELECT test_bms_del_members('(b 1 2 100 200 300)', '(b 1 2)') AS result; +SELECT test_bms_del_members('(b 1 2 100 200 300)', '(b 200 300)') AS result; +-- Test module checks +SELECT test_bms_del_members('(b 5)', NULL) AS result; +SELECT test_bms_del_members(NULL, '(b 5)') AS result; -- bms_join() SELECT test_bms_join('(b 1 3 5)', NULL) AS result; SELECT test_bms_join(NULL, '(b 2 4 6)') AS result; SELECT test_bms_join('(b 1 3 5)', '(b 2 4 6)') AS result; SELECT test_bms_join('(b 1 3 5)', '(b 1 4 5)') AS result; +-- Force word count changes +SELECT test_bms_join('(b 5)', '(b 100)') AS result; +SELECT test_bms_join('(b 1 2)', '(b 100 200 300)') AS result; +-- Test module checks +SELECT test_bms_join('(b 5)', NULL) AS result; +SELECT test_bms_join(NULL, '(b 5)') AS result; +SELECT test_bms_join(NULL, NULL) AS result; -- bms_union() --- Overlapping sets. +-- Overlapping sets SELECT test_bms_union('(b 1 3 5)', '(b 3 5 7)') AS result; -- Union with NULL SELECT test_bms_union('(b 1 3 5)', '(b)') AS result; @@ -53,24 +92,44 @@ SELECT test_bms_union( test_bms_add_range('(b)', 0, 15), test_bms_add_range('(b)', 10, 20) ) AS result; +-- Union with varrying word counts +SELECT test_bms_union('(b 1 2)', '(b 100 300)') AS result; +SELECT test_bms_union('(b 100 300)', '(b 1 2)') AS result; +-- Test module checks +SELECT test_bms_union('(b 5)', NULL) AS result; +SELECT test_bms_union(NULL, '(b 5)') AS result; +SELECT test_bms_union(NULL, NULL) AS result; -- bms_intersect() -- Overlapping sets SELECT test_bms_intersect('(b 1 3 5)', '(b 3 5 7)') AS result; -- Disjoint sets SELECT test_bms_intersect('(b 1 3 5)', '(b 2 4 6)') AS result; --- Intersect with empty. +-- Intersect with empty SELECT test_bms_intersect('(b 1 3 5)', '(b)') AS result; +-- Intersect with varrying word counts +SELECT test_bms_intersect('(b 1 300)', '(b 1 2 3 4 5)') AS result; +SELECT test_bms_intersect('(b 1 2 3 4 5)', '(b 1 300)') AS result; +-- Test module checks +SELECT test_bms_intersect('(b 1)', '(b 2)') AS result; +SELECT test_bms_intersect('(b 5)', NULL) AS result; +SELECT test_bms_intersect(NULL, '(b 5)') AS result; +SELECT test_bms_intersect(NULL, NULL) AS result; -- bms_int_members() -- Overlapping sets SELECT test_bms_int_members('(b 1 3 5)', '(b 3 5 7)') AS result; -- Disjoint sets SELECT test_bms_int_members('(b 1 3 5)', '(b 2 4 6)') AS result; --- Intersect with empty. +-- Intersect with empty SELECT test_bms_int_members('(b 1 3 5)', '(b)') AS result; -- Multiple members SELECT test_bms_int_members('(b 0 31 32 63 64)', '(b 31 32 64 65)') AS result; +-- Test module checks +SELECT test_bms_int_members('(b 1)', '(b 2)') AS result; +SELECT test_bms_int_members('(b 5)', NULL) AS result; +SELECT test_bms_int_members(NULL, '(b 5)') AS result; +SELECT test_bms_int_members(NULL, NULL) AS result; -- bms_difference() -- Overlapping sets @@ -86,6 +145,14 @@ SELECT test_bms_difference( test_bms_add_range('(b)', 0, 100), test_bms_add_range('(b)', 50, 150) ) AS result; +-- Difference with different word counts +SELECT test_bms_difference('(b 5 100)', '(b 5)') AS result; +SELECT test_bms_difference('(b 1 2 100 200)', '(b 1 2)') AS result; +-- Test module checks +SELECT test_bms_difference('(b 5)', '(b 5 10)') AS result; +SELECT test_bms_difference('(b 5)', NULL) AS result; +SELECT test_bms_difference(NULL, '(b 5)') AS result; +SELECT test_bms_difference(NULL, NULL) AS result; -- bms_is_member() SELECT test_bms_is_member('(b)', -5); -- error @@ -93,12 +160,21 @@ SELECT test_bms_is_member('(b 1 3 5)', 1) AS result; SELECT test_bms_is_member('(b 1 3 5)', 2) AS result; SELECT test_bms_is_member('(b 1 3 5)', 3) AS result; SELECT test_bms_is_member('(b)', 1) AS result; +-- Test module check +SELECT test_bms_is_member('(b 5)', NULL) AS result; -- bms_member_index() SELECT test_bms_member_index(NULL, 1) AS result; SELECT test_bms_member_index('(b 1 3 5)', 2) AS result; SELECT test_bms_member_index('(b 1 3 5)', 1) AS result; SELECT test_bms_member_index('(b 1 3 5)', 3) AS result; +-- Member index with various word positions +SELECT test_bms_member_index('(b 100 200)', 100) AS result; +SELECT test_bms_member_index('(b 100 200)', 200) AS result; +SELECT test_bms_member_index('(b 1 50 100 200)', 200) AS result; +-- Test module checks +SELECT test_bms_member_index('', 5) AS result; +SELECT test_bms_member_index(NULL, 5) AS result; -- bms_num_members() SELECT test_bms_num_members('(b)') AS result; @@ -111,6 +187,13 @@ SELECT test_bms_equal('(b)', '(b 1 3 5)') AS result; SELECT test_bms_equal('(b 1 3 5)', '(b)') AS result; SELECT test_bms_equal('(b 1 3 5)', '(b 1 3 5)') AS result; SELECT test_bms_equal('(b 1 3 5)', '(b 2 4 6)') AS result; +-- Equal with different word counts +SELECT test_bms_equal('(b 5)', '(b 100)') AS result; +SELECT test_bms_equal('(b 5 10)', '(b 100 200 300)') AS result; +-- Test module checks +SELECT test_bms_equal('(b 5)', NULL) AS result; +SELECT test_bms_equal(NULL, '(b 5)') AS result; +SELECT test_bms_equal(NULL, NULL) AS result; -- bms_compare() SELECT test_bms_compare('(b)', '(b)') AS result; @@ -123,6 +206,10 @@ SELECT test_bms_compare( test_bms_add_range('(b)', 0, 63), test_bms_add_range('(b)', 0, 64) ) AS result; +-- Test module checks +SELECT test_bms_compare('(b 5)', NULL) AS result; +SELECT test_bms_compare(NULL, '(b 5)') AS result; +SELECT test_bms_compare(NULL, NULL) AS result; -- bms_add_range() SELECT test_bms_add_range('(b)', -5, 10); -- error @@ -138,26 +225,47 @@ SELECT length(test_bms_add_range('(b)', 0, 1000)) AS result; -- Force reallocations SELECT length(test_bms_add_range('(b)', 0, 200)) AS result; SELECT length(test_bms_add_range('(b)', 1000, 1100)) AS result; +-- Force word count expansion +SELECT test_bms_add_range('(b 5)', 100, 105) AS result; +SELECT length(test_bms_add_range('(b 1 2)', 200, 250)) AS result; +-- Test module checks +SELECT test_bms_add_range('(b 5)', 5, NULL) AS result; +SELECT test_bms_add_range('(b 5)', NULL, 10) AS result; +SELECT test_bms_add_range('(b 5)', NULL, NULL) AS result; +SELECT test_bms_add_range(NULL, 5, 10) AS result; +SELECT test_bms_add_range(NULL, 10, 5) AS result; +SELECT test_bms_add_range(NULL, NULL, NULL) AS result; -- bms_membership() SELECT test_bms_membership('(b)') AS result; SELECT test_bms_membership('(b 42)') AS result; SELECT test_bms_membership('(b 1 2)') AS result; +-- Test module check +SELECT test_bms_membership(NULL) AS result; -- bms_is_empty() SELECT test_bms_is_empty(NULL) AS result; SELECT test_bms_is_empty('(b)') AS result; SELECT test_bms_is_empty('(b 1)') AS result; +-- Test module check +SELECT test_bms_is_empty(NULL) AS result; -- bms_singleton_member() +SELECT test_bms_singleton_member('(b)'); -- error SELECT test_bms_singleton_member('(b 1 2)'); -- error SELECT test_bms_singleton_member('(b 42)') AS result; +-- Test module check +SELECT test_bms_singleton_member(NULL) AS result; -- bms_get_singleton_member() +SELECT test_bms_get_singleton_member('(b)', 1000); -- Not a singleton, returns input default SELECT test_bms_get_singleton_member('(b 3 6)', 1000) AS result; -- Singletone, returns sole member SELECT test_bms_get_singleton_member('(b 400)', 1000) AS result; +-- Test module checks +SELECT test_bms_get_singleton_member('', 1000) AS result; +SELECT test_bms_get_singleton_member(NULL, -1) AS result; -- bms_next_member() and bms_prev_member() -- First member @@ -176,16 +284,32 @@ SELECT test_bms_prev_member('(b 5 10 15 20)', 20) AS result; SELECT test_bms_prev_member('(b 5 10 15 20)', 5) AS result; -- Empty set SELECT test_bms_prev_member('(b)', 100) AS result; +-- Negative prevbit should result in highest possible bit in set +SELECT test_bms_prev_member('(b 0 63 64 127)', -1) AS result; +-- Test module checks +SELECT test_bms_next_member('', 5) AS result; +SELECT test_bms_next_member('(b 5)', NULL) AS result; +SELECT test_bms_next_member(NULL, 5) AS result; +SELECT test_bms_next_member(NULL, NULL) AS result; +SELECT test_bms_prev_member('', 5) AS result; +SELECT test_bms_prev_member('(b 5)', NULL) AS result; +SELECT test_bms_prev_member(NULL, 5) AS result; -- bms_hash_value() SELECT test_bms_hash_value('(b)') = 0 AS result; SELECT test_bms_hash_value('(b 1 3 5)') = test_bms_hash_value('(b 1 3 5)') AS result; SELECT test_bms_hash_value('(b 1 3 5)') != test_bms_hash_value('(b 2 4 6)') AS result; +-- Test module check +SELECT test_bms_hash_value(NULL) AS result; -- bms_overlap() SELECT test_bms_overlap('(b 1 3 5)', '(b 3 5 7)') AS result; SELECT test_bms_overlap('(b 1 3 5)', '(b 2 4 6)') AS result; SELECT test_bms_overlap('(b)', '(b 1 3 5)') AS result; +-- Test module checks +SELECT test_bms_overlap('(b 5)', NULL) AS result; +SELECT test_bms_overlap(NULL, '(b 5)') AS result; +SELECT test_bms_overlap(NULL, NULL) AS result; -- bms_is_subset() SELECT test_bms_is_subset('(b)', '(b 1 3 5)') AS result; @@ -194,22 +318,50 @@ SELECT test_bms_is_subset('(b 1 3 5)', '(b 1 3)') AS result; SELECT test_bms_is_subset('(b 1 3)', '(b 2 4)') AS result; SELECT test_bms_is_subset(test_bms_add_range(NULL, 0, 31), test_bms_add_range(NULL, 0, 63)) AS result; +-- Is subset with shorter word counts? +SELECT test_bms_is_subset('(b 5 100)', '(b 5)') AS result; +SELECT test_bms_is_subset('(b 1 2 50 100)', '(b 1 2)') AS result; +-- Test module checks +SELECT test_bms_is_subset('(b 5)', NULL) AS result; +SELECT test_bms_is_subset(NULL, '(b 5)') AS result; +SELECT test_bms_is_subset(NULL, NULL) AS result; -- bms_subset_compare() SELECT test_bms_subset_compare(NULL, NULL) AS result; -SELECT test_bms_subset_compare('(b 1 3)', NULL) AS result; SELECT test_bms_subset_compare(NULL, '(b 1 3)') AS result; -SELECT test_bms_subset_compare('(b 1 3 5)', '(b 1 3)') AS result; -SELECT test_bms_subset_compare('(b 1 3)', '(b 1 3 5)') AS result; +SELECT test_bms_subset_compare('(b)', '(b)') AS result; +SELECT test_bms_subset_compare('(b)', '(b 1)') AS result; +SELECT test_bms_subset_compare('(b 1)', '(b)') AS result; +SELECT test_bms_subset_compare('(b 1 3)', NULL) AS result; SELECT test_bms_subset_compare('(b 1 3 5)', '(b 1 3 5)') AS result; -SELECT test_bms_subset_compare('(b 1 3 5)', '(b 2 4 6)') AS result; +SELECT test_bms_subset_compare('(b 1 3)', '(b 1 3 5)') AS result; +SELECT test_bms_subset_compare('(b 1 3 5)', '(b 1 3)') AS result; +SELECT test_bms_subset_compare('(b 1 2)', '(b 1 3)') AS result; +SELECT test_bms_subset_compare('(b 1 2)', '(b 1 4)') AS result; +SELECT test_bms_subset_compare('(b 1 3)', '(b 1 3 64)') AS result; +SELECT test_bms_subset_compare('(b 1 3 64)', '(b 1 3)') AS result; +SELECT test_bms_subset_compare('(b 1 3 64)', '(b 1 3 65)') AS result; +SELECT test_bms_subset_compare('(b 1 3)', '(b 2 4)') AS result; +SELECT test_bms_subset_compare('(b 1)', '(b 64)') AS result; +SELECT test_bms_subset_compare('(b 0)', '(b 32)') AS result; +SELECT test_bms_subset_compare('(b 0)', '(b 64)') AS result; +SELECT test_bms_subset_compare('(b 64)', '(b 1)') AS result; +SELECT test_bms_subset_compare('(b 1 2)', '(b 1 2 64)') AS result; +SELECT test_bms_subset_compare('(b 64 200)', '(b 1 201)') AS result; +SELECT test_bms_subset_compare('(b 1 64 65)', '(b 1 2 64)') AS result; +SELECT test_bms_subset_compare('(b 2 64 128)', '(b 1 65)') AS result; +-- Test module checks +SELECT test_bms_subset_compare('(b 5)', NULL) AS result; +SELECT test_bms_subset_compare(NULL, '(b 5)') AS result; +SELECT test_bms_subset_compare(NULL, NULL) AS result; -- bms_copy() SELECT test_bms_copy(NULL) AS result; SELECT test_bms_copy('(b 1 3 5 7)') AS result; +-- Test module check +SELECT test_bms_copy(NULL) AS result; -- bms_add_members() -SELECT test_bms_add_member('(b)', 1000); -- error SELECT test_bms_add_members('(b 1 3)', '(b 5 7)') AS result; SELECT test_bms_add_members('(b 1 3 5)', '(b 2 5 7)') AS result; SELECT test_bms_add_members('(b 1 3 5)', '(b 100 200 300)') AS result; @@ -219,6 +371,8 @@ SELECT test_bitmap_hash('(b)') = 0 AS result; SELECT test_bitmap_hash('(b 1 3 5)') = test_bitmap_hash('(b 1 3 5)') AS result; SELECT test_bitmap_hash('(b 1 3 5)') = test_bms_hash_value('(b 1 3 5)') AS result; SELECT test_bitmap_hash('(b 1 3 5)') != test_bitmap_hash('(b 2 4 6)') AS result; +-- Test module check +SELECT test_bitmap_hash(NULL) AS result; -- bitmap_match() SELECT test_bitmap_match('(b)', '(b)') AS result; @@ -234,6 +388,10 @@ SELECT (test_bitmap_match('(b 1 3 5)', '(b 2 4 6)') = 0) = test_bms_equal('(b 1 3 5)', '(b 2 4 6)') AS result; SELECT (test_bitmap_match('(b)', '(b)') = 0) = test_bms_equal('(b)', '(b)') AS result; +-- Test module checks +SELECT test_bitmap_match('(b 5)', NULL) AS result; +SELECT test_bitmap_match(NULL, '(b 5)') AS result; +SELECT test_bitmap_match(NULL, NULL) AS result; -- bms_overlap_list() SELECT test_bms_overlap_list('(b 0)', ARRAY[0]) AS result; @@ -243,6 +401,13 @@ SELECT test_bms_overlap_list('(b 7 10)', ARRAY[6,7,8,9]) AS result; SELECT test_bms_overlap_list('(b 1 5)', ARRAY[6,7,8,9]) AS result; -- Empty list SELECT test_bms_overlap_list('(b 1)', ARRAY[]::integer[]) AS result; +-- Overlap list with negative numbers +SELECT test_bms_overlap_list('(b 5 10)', ARRAY[-1,5]) AS result; -- error +SELECT test_bms_overlap_list('(b 1 2 3)', ARRAY[-5,-1,0]) AS result; -- error +-- Test module checks +SELECT test_bms_overlap_list('(b 5)', NULL) AS result; +SELECT test_bms_overlap_list(NULL, ARRAY[1,2,3]) AS result; +SELECT test_bms_overlap_list(NULL, NULL) AS result; -- bms_nonempty_difference() SELECT test_bms_nonempty_difference(NULL, '(b 1 3 5)') AS result; @@ -250,6 +415,14 @@ SELECT test_bms_nonempty_difference('(b 1 3 5)', NULL) AS result; SELECT test_bms_nonempty_difference('(b 1 3 5)', '(b 2 4 6)') AS result; SELECT test_bms_nonempty_difference('(b 1 3 5)', '(b 1 5)') AS result; SELECT test_bms_nonempty_difference('(b 1 3 5)', '(b 1 3 5)') AS result; +-- Difference with different word counts +SELECT test_bms_nonempty_difference('(b 5)', '(b 100)') AS result; +SELECT test_bms_nonempty_difference('(b 100)', '(b 5)') AS result; +SELECT test_bms_nonempty_difference('(b 1 2)', '(b 50 100)') AS result; +-- Test module checks +SELECT test_bms_nonempty_difference('(b 5)', NULL) AS result; +SELECT test_bms_nonempty_difference(NULL, '(b 5)') AS result; +SELECT test_bms_nonempty_difference(NULL, NULL) AS result; -- random operations SELECT test_random_operations(-1, 10000, 81920, 0) > 0 AS result; diff --git a/src/test/modules/test_bitmapset/test_bitmapset--1.0.sql b/src/test/modules/test_bitmapset/test_bitmapset--1.0.sql index 95f5ee02e3f..b95c4d0dda5 100644 --- a/src/test/modules/test_bitmapset/test_bitmapset--1.0.sql +++ b/src/test/modules/test_bitmapset/test_bitmapset--1.0.sql @@ -112,6 +112,10 @@ CREATE FUNCTION test_bms_int_members(text, text) RETURNS text AS 'MODULE_PATHNAME' LANGUAGE C; +CREATE FUNCTION test_bms_del_members(text, text) +RETURNS text +AS 'MODULE_PATHNAME' LANGUAGE C; + CREATE FUNCTION test_bms_replace_members(text, text) RETURNS text AS 'MODULE_PATHNAME' LANGUAGE C; diff --git a/src/test/modules/test_bitmapset/test_bitmapset.c b/src/test/modules/test_bitmapset/test_bitmapset.c index 5bc4daa23f1..2e821320836 100644 --- a/src/test/modules/test_bitmapset/test_bitmapset.c +++ b/src/test/modules/test_bitmapset/test_bitmapset.c @@ -58,6 +58,7 @@ PG_FUNCTION_INFO_V1(test_bms_member_index); PG_FUNCTION_INFO_V1(test_bms_add_range); PG_FUNCTION_INFO_V1(test_bms_add_members); PG_FUNCTION_INFO_V1(test_bms_int_members); +PG_FUNCTION_INFO_V1(test_bms_del_members); PG_FUNCTION_INFO_V1(test_bms_replace_members); PG_FUNCTION_INFO_V1(test_bms_join); PG_FUNCTION_INFO_V1(test_bitmap_hash); @@ -111,9 +112,6 @@ test_bms_add_member(PG_FUNCTION_ARGS) if (bms) bms_free(bms); - if (result == NULL) - PG_RETURN_NULL(); - PG_RETURN_TEXT_P(result); } @@ -136,9 +134,6 @@ test_bms_add_members(PG_FUNCTION_ARGS) if (bms2) bms_free(bms2); - if (bms1 == NULL) - PG_RETURN_NULL(); - result = BITMAPSET_TO_TEXT(bms1); bms_free(bms1); @@ -161,12 +156,8 @@ test_bms_del_member(PG_FUNCTION_ARGS) member = PG_GETARG_INT32(1); bms = bms_del_member(bms, member); - if (bms == NULL || bms_is_empty(bms)) - { - if (bms) - bms_free(bms); + if (bms_is_empty(bms)) PG_RETURN_NULL(); - } result = BITMAPSET_TO_TEXT(bms); bms_free(bms); @@ -515,8 +506,8 @@ test_bms_get_singleton_member(PG_FUNCTION_ARGS) { Bitmapset *bms = NULL; int32 default_member = PG_GETARG_INT32(1); - int member; bool success; + int member = -1; if (PG_ARGISNULL(0)) PG_RETURN_INT32(default_member); @@ -532,8 +523,8 @@ test_bms_get_singleton_member(PG_FUNCTION_ARGS) if (success) PG_RETURN_INT32(member); - else - PG_RETURN_INT32(default_member); + + PG_RETURN_INT32(default_member); } Datum @@ -609,11 +600,6 @@ test_bms_overlap_list(PG_FUNCTION_ARGS) array = PG_GETARG_ARRAYTYPE_P(1); - if (ARR_ELEMTYPE(array) != INT4OID) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("integer array expected"))); - deconstruct_array(array, INT4OID, sizeof(int32), true, 'i', &elem_datums, &elem_nulls, &elem_count); @@ -753,6 +739,37 @@ test_bms_int_members(PG_FUNCTION_ARGS) } Datum +test_bms_del_members(PG_FUNCTION_ARGS) +{ + Bitmapset *bms1 = NULL, + *bms2 = NULL; + Bitmapset *result_bms; + text *result; + + if (!PG_ARGISNULL(0)) + bms1 = TEXT_TO_BITMAPSET(PG_GETARG_TEXT_PP(0)); + + if (!PG_ARGISNULL(1)) + bms2 = TEXT_TO_BITMAPSET(PG_GETARG_TEXT_PP(1)); + + /* IMPORTANT: bms_del_members modifies/frees the first argument */ + result_bms = bms_del_members(bms1, bms2); + + /* bms1 is now invalid, do not free it */ + + if (bms2) + bms_free(bms2); + + if (result_bms == NULL) + PG_RETURN_NULL(); + + result = BITMAPSET_TO_TEXT(result_bms); + bms_free(result_bms); + + PG_RETURN_TEXT_P(result); +} + +Datum test_bms_replace_members(PG_FUNCTION_ARGS) { Bitmapset *bms1 = NULL, diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 1f24f6ffd1f..035f9a78206 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1557,6 +1557,129 @@ drop table t2; drop table t3; drop table p_t1; -- +-- Test GROUP BY ALL +-- +-- We don't care about the data here, just the proper transformation of the +-- GROUP BY clause, so test some queries and verify the EXPLAIN plans. +-- +CREATE TEMP TABLE t1 ( + a int, + b int, + c int +); +-- basic example +EXPLAIN (COSTS OFF) SELECT b, COUNT(*) FROM t1 GROUP BY ALL; + QUERY PLAN +---------------------- + HashAggregate + Group Key: b + -> Seq Scan on t1 +(3 rows) + +-- multiple columns, non-consecutive order +EXPLAIN (COSTS OFF) SELECT a, SUM(b), b FROM t1 GROUP BY ALL; + QUERY PLAN +---------------------- + HashAggregate + Group Key: a, b + -> Seq Scan on t1 +(3 rows) + +-- multi columns, no aggregate +EXPLAIN (COSTS OFF) SELECT a + b FROM t1 GROUP BY ALL; + QUERY PLAN +---------------------- + HashAggregate + Group Key: (a + b) + -> Seq Scan on t1 +(3 rows) + +-- check we detect a non-top-level aggregate +EXPLAIN (COSTS OFF) SELECT a, SUM(b) + 4 FROM t1 GROUP BY ALL; + QUERY PLAN +---------------------- + HashAggregate + Group Key: a + -> Seq Scan on t1 +(3 rows) + +-- including grouped column is okay +EXPLAIN (COSTS OFF) SELECT a, SUM(b) + a FROM t1 GROUP BY ALL; + QUERY PLAN +---------------------- + HashAggregate + Group Key: a + -> Seq Scan on t1 +(3 rows) + +-- including non-grouped column, not so much +EXPLAIN (COSTS OFF) SELECT a, SUM(b) + c FROM t1 GROUP BY ALL; +ERROR: column "t1.c" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: EXPLAIN (COSTS OFF) SELECT a, SUM(b) + c FROM t1 GROUP BY AL... + ^ +-- all aggregates, should reduce to GROUP BY () +EXPLAIN (COSTS OFF) SELECT COUNT(a), SUM(b) FROM t1 GROUP BY ALL; + QUERY PLAN +---------------------- + Aggregate + Group Key: () + -> Seq Scan on t1 +(3 rows) + +-- likewise with empty target list +EXPLAIN (COSTS OFF) SELECT FROM t1 GROUP BY ALL; + QUERY PLAN +----------------------- + Result + Replaces: Aggregate +(2 rows) + +-- window functions are not to be included in GROUP BY, either +EXPLAIN (COSTS OFF) SELECT a, COUNT(a) OVER (PARTITION BY a) FROM t1 GROUP BY ALL; + QUERY PLAN +---------------------------------- + WindowAgg + Window: w1 AS (PARTITION BY a) + -> Sort + Sort Key: a + -> HashAggregate + Group Key: a + -> Seq Scan on t1 +(7 rows) + +-- all cols +EXPLAIN (COSTS OFF) SELECT *, count(*) FROM t1 GROUP BY ALL; + QUERY PLAN +---------------------- + HashAggregate + Group Key: a, b, c + -> Seq Scan on t1 +(3 rows) + +-- group by all with grouping element(s) (equivalent to GROUP BY's +-- default behavior, explicit antithesis to GROUP BY DISTINCT) +EXPLAIN (COSTS OFF) SELECT a, count(*) FROM t1 GROUP BY ALL a; + QUERY PLAN +---------------------- + HashAggregate + Group Key: a + -> Seq Scan on t1 +(3 rows) + +-- verify deparsing of GROUP BY ALL +CREATE TEMP VIEW v1 AS SELECT b, COUNT(*) FROM t1 GROUP BY ALL; +SELECT pg_get_viewdef('v1'::regclass); + pg_get_viewdef +----------------------- + SELECT b, + + count(*) AS count+ + FROM t1 + + GROUP BY ALL; +(1 row) + +DROP VIEW v1; +DROP TABLE t1; +-- -- Test GROUP BY matching of join columns that are type-coerced due to USING -- create temp table t1(f1 int, f2 int); diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 9f378a6abdf..73a7ef97355 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -122,6 +122,20 @@ ALTER STATISTICS ab1_a_b_stats RENAME TO ab1_a_b_stats_new; ERROR: must be owner of statistics object ab1_a_b_stats RESET SESSION AUTHORIZATION; DROP ROLE regress_stats_ext; +CREATE STATISTICS pg_temp.stats_ext_temp ON a, b FROM ab1; +SELECT regexp_replace(pg_describe_object(tableoid, oid, 0), + 'pg_temp_[0-9]*', 'pg_temp_REDACTED') AS descr, + pg_statistics_obj_is_visible(oid) AS visible + FROM pg_statistic_ext + WHERE stxname = 'stats_ext_temp'; + descr | visible +---------------------------------------------------+--------- + statistics object pg_temp_REDACTED.stats_ext_temp | f +(1 row) + +DROP STATISTICS stats_ext_temp; -- shall fail +ERROR: statistics object "stats_ext_temp" does not exist +DROP STATISTICS pg_temp.stats_ext_temp; CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1; NOTICE: statistics object "ab1_a_b_stats" already exists, skipping DROP STATISTICS ab1_a_b_stats; diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 62540b1ffa4..908af50def3 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -550,6 +550,60 @@ drop table t3; drop table p_t1; -- +-- Test GROUP BY ALL +-- +-- We don't care about the data here, just the proper transformation of the +-- GROUP BY clause, so test some queries and verify the EXPLAIN plans. +-- + +CREATE TEMP TABLE t1 ( + a int, + b int, + c int +); + +-- basic example +EXPLAIN (COSTS OFF) SELECT b, COUNT(*) FROM t1 GROUP BY ALL; + +-- multiple columns, non-consecutive order +EXPLAIN (COSTS OFF) SELECT a, SUM(b), b FROM t1 GROUP BY ALL; + +-- multi columns, no aggregate +EXPLAIN (COSTS OFF) SELECT a + b FROM t1 GROUP BY ALL; + +-- check we detect a non-top-level aggregate +EXPLAIN (COSTS OFF) SELECT a, SUM(b) + 4 FROM t1 GROUP BY ALL; + +-- including grouped column is okay +EXPLAIN (COSTS OFF) SELECT a, SUM(b) + a FROM t1 GROUP BY ALL; + +-- including non-grouped column, not so much +EXPLAIN (COSTS OFF) SELECT a, SUM(b) + c FROM t1 GROUP BY ALL; + +-- all aggregates, should reduce to GROUP BY () +EXPLAIN (COSTS OFF) SELECT COUNT(a), SUM(b) FROM t1 GROUP BY ALL; + +-- likewise with empty target list +EXPLAIN (COSTS OFF) SELECT FROM t1 GROUP BY ALL; + +-- window functions are not to be included in GROUP BY, either +EXPLAIN (COSTS OFF) SELECT a, COUNT(a) OVER (PARTITION BY a) FROM t1 GROUP BY ALL; + +-- all cols +EXPLAIN (COSTS OFF) SELECT *, count(*) FROM t1 GROUP BY ALL; + +-- group by all with grouping element(s) (equivalent to GROUP BY's +-- default behavior, explicit antithesis to GROUP BY DISTINCT) +EXPLAIN (COSTS OFF) SELECT a, count(*) FROM t1 GROUP BY ALL a; + +-- verify deparsing of GROUP BY ALL +CREATE TEMP VIEW v1 AS SELECT b, COUNT(*) FROM t1 GROUP BY ALL; +SELECT pg_get_viewdef('v1'::regclass); + +DROP VIEW v1; +DROP TABLE t1; + +-- -- Test GROUP BY matching of join columns that are type-coerced due to USING -- diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index d7e5c0c893a..96771600d57 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -83,6 +83,14 @@ DROP STATISTICS ab1_a_b_stats; ALTER STATISTICS ab1_a_b_stats RENAME TO ab1_a_b_stats_new; RESET SESSION AUTHORIZATION; DROP ROLE regress_stats_ext; +CREATE STATISTICS pg_temp.stats_ext_temp ON a, b FROM ab1; +SELECT regexp_replace(pg_describe_object(tableoid, oid, 0), + 'pg_temp_[0-9]*', 'pg_temp_REDACTED') AS descr, + pg_statistics_obj_is_visible(oid) AS visible + FROM pg_statistic_ext + WHERE stxname = 'stats_ext_temp'; +DROP STATISTICS stats_ext_temp; -- shall fail +DROP STATISTICS pg_temp.stats_ext_temp; CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1; DROP STATISTICS ab1_a_b_stats; |