diff options
24 files changed, 1527 insertions, 237 deletions
diff --git a/src/backend/access/hash/hashfunc.c b/src/backend/access/hash/hashfunc.c index 872c9f0f26..6b99acff88 100644 --- a/src/backend/access/hash/hashfunc.c +++ b/src/backend/access/hash/hashfunc.c @@ -28,6 +28,13 @@ #include "access/hash.h" +#ifdef PGXC +#include "catalog/pg_type.h" +#include "utils/builtins.h" +#include "utils/timestamp.h" +#include "utils/date.h" +#include "utils/nabstime.h" +#endif /* Note: this is used for both "char" and boolean datatypes */ Datum @@ -521,3 +528,91 @@ hash_uint32(uint32 k) /* report the result */ return UInt32GetDatum(c); } + +#ifdef PGXC +/* + * compute_hash() -- Generaic hash function for all datatypes + * + */ + +Datum +compute_hash(Oid type, Datum value, int *pErr) +{ + Assert(pErr); + + *pErr = 0; + + if (value == NULL) + { + *pErr = 1; + return 0; + } + + switch(type) + { + case INT8OID: + /* This gives added advantage that + * a = 8446744073709551359 + * and a = 8446744073709551359::int8 both work*/ + return DatumGetInt32(value); + case INT2OID: + return DatumGetInt16(value); + case OIDOID: + return DatumGetObjectId(value); + case INT4OID: + return DatumGetInt32(value); + case BOOLOID: + return DatumGetBool(value); + + case CHAROID: + return DirectFunctionCall1(hashchar, value); + case NAMEOID: + return DirectFunctionCall1(hashname, value); + case INT2VECTOROID: + return DirectFunctionCall1(hashint2vector, value); + + case VARCHAROID: + case TEXTOID: + return DirectFunctionCall1(hashtext, value); + + case OIDVECTOROID: + return DirectFunctionCall1(hashoidvector, value); + case FLOAT4OID: + return DirectFunctionCall1(hashfloat4, value); + case FLOAT8OID: + return DirectFunctionCall1(hashfloat8, value); + + case ABSTIMEOID: + return DatumGetAbsoluteTime(value); + case RELTIMEOID: + return DatumGetRelativeTime(value); + case CASHOID: + return DirectFunctionCall1(hashint8, value); + + case BPCHAROID: + return DirectFunctionCall1(hashbpchar, value); + case BYTEAOID: + return DirectFunctionCall1(hashvarlena, value); + + case DATEOID: + return DatumGetDateADT(value); + case TIMEOID: + return DirectFunctionCall1(time_hash, value); + case TIMESTAMPOID: + return DirectFunctionCall1(timestamp_hash, value); + case TIMESTAMPTZOID: + return DirectFunctionCall1(timestamp_hash, value); + case INTERVALOID: + return DirectFunctionCall1(interval_hash, value); + case TIMETZOID: + return DirectFunctionCall1(timetz_hash, value); + + case NUMERICOID: + return DirectFunctionCall1(hash_numeric, value); + default: + *pErr = 1; + return 0; + } +} + +#endif diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 0a6f6051ba..77b82db102 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -1643,14 +1643,14 @@ CopyTo(CopyState cstate) } #ifdef PGXC - if (IS_PGXC_COORDINATOR && cstate->rel_loc) + if (IS_PGXC_COORDINATOR && cstate->rel_loc) { cstate->processed = DataNodeCopyOut( - GetRelationNodes(cstate->rel_loc, NULL, RELATION_ACCESS_READ), + GetRelationNodes(cstate->rel_loc, 0, UNKNOWNOID, RELATION_ACCESS_READ), cstate->connections, cstate->copy_file); } - else + else { #endif @@ -2415,15 +2415,18 @@ CopyFrom(CopyState cstate) #ifdef PGXC if (IS_PGXC_COORDINATOR && cstate->rel_loc) { - Datum *dist_col_value = NULL; + Datum dist_col_value; + Oid dist_col_type = UNKNOWNOID; if (cstate->idx_dist_by_col >= 0 && !nulls[cstate->idx_dist_by_col]) - dist_col_value = &values[cstate->idx_dist_by_col]; + { + dist_col_value = values[cstate->idx_dist_by_col]; + dist_col_type = attr[cstate->idx_dist_by_col]->atttypid; + } if (DataNodeCopyIn(cstate->line_buf.data, cstate->line_buf.len, - GetRelationNodes(cstate->rel_loc, (long *)dist_col_value, - RELATION_ACCESS_INSERT), + GetRelationNodes(cstate->rel_loc, dist_col_value, dist_col_type, RELATION_ACCESS_INSERT), cstate->connections)) ereport(ERROR, (errcode(ERRCODE_CONNECTION_EXCEPTION), @@ -4035,7 +4038,8 @@ DoInsertSelectCopy(EState *estate, TupleTableSlot *slot) HeapTuple tuple; Datum *values; bool *nulls; - Datum *dist_col_value = NULL; + Datum dist_col_value; + Oid dist_col_type; MemoryContext oldcontext; CopyState cstate; @@ -4080,6 +4084,11 @@ DoInsertSelectCopy(EState *estate, TupleTableSlot *slot) cstate->fe_msgbuf = makeStringInfo(); attr = cstate->tupDesc->attrs; + if (cstate->idx_dist_by_col >= 0) + dist_col_type = attr[cstate->idx_dist_by_col]->atttypid; + else + dist_col_type = UNKNOWNOID; + /* Get info about the columns we need to process. */ cstate->out_functions = (FmgrInfo *) palloc(cstate->tupDesc->natts * sizeof(FmgrInfo)); foreach(lc, cstate->attnumlist) @@ -4150,12 +4159,14 @@ DoInsertSelectCopy(EState *estate, TupleTableSlot *slot) /* Get dist column, if any */ if (cstate->idx_dist_by_col >= 0 && !nulls[cstate->idx_dist_by_col]) - dist_col_value = &values[cstate->idx_dist_by_col]; + dist_col_value = values[cstate->idx_dist_by_col]; + else + dist_col_type = UNKNOWNOID; /* Send item to the appropriate data node(s) (buffer) */ if (DataNodeCopyIn(cstate->fe_msgbuf->data, cstate->fe_msgbuf->len, - GetRelationNodes(cstate->rel_loc, (long *)dist_col_value, RELATION_ACCESS_INSERT), + GetRelationNodes(cstate->rel_loc, dist_col_value, dist_col_type, RELATION_ACCESS_INSERT), cstate->connections)) ereport(ERROR, (errcode(ERRCODE_CONNECTION_EXCEPTION), diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index da5fdbc368..5d2cb5cb9f 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -851,8 +851,28 @@ ExplainNode(Plan *plan, PlanState *planstate, case T_WorkTableScan: #ifdef PGXC case T_RemoteQuery: + { + RemoteQuery *remote_query = (RemoteQuery *) plan; + int pnc, nc; + + pnc = 0; + nc = 0; + if (remote_query->exec_nodes != NULL) + { + if (remote_query->exec_nodes->primarynodelist != NULL) + { + pnc = list_length(remote_query->exec_nodes->primarynodelist); + appendStringInfo(es->str, " (Primary Node Count [%d])", pnc); + } + if (remote_query->exec_nodes->nodelist) + { + nc = list_length(remote_query->exec_nodes->nodelist); + appendStringInfo(es->str, " (Node Count [%d])", nc); + } + } #endif - ExplainScanTarget((Scan *) plan, es); + ExplainScanTarget((Scan *) plan, es); + } break; case T_BitmapIndexScan: { diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 4dd1f51d62..efd89fb8b3 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -2418,9 +2418,7 @@ create_remotequery_plan(PlannerInfo *root, Path *best_path, scan_plan->exec_nodes->baselocatortype = rel_loc_info->locatorType; else scan_plan->exec_nodes->baselocatortype = '\0'; - scan_plan->exec_nodes = GetRelationNodes(rel_loc_info, - NULL, - RELATION_ACCESS_READ); + scan_plan->exec_nodes = GetRelationNodes(rel_loc_info, 0, UNKNOWNOID, RELATION_ACCESS_READ); copy_path_costsize(&scan_plan->scan.plan, best_path); /* PGXCTODO - get better estimates */ @@ -5024,8 +5022,7 @@ create_remotedelete_plan(PlannerInfo *root, Plan *topplan) fstep->sql_statement = pstrdup(buf->data); fstep->combine_type = COMBINE_TYPE_SAME; fstep->read_only = false; - fstep->exec_nodes = GetRelationNodes(rel_loc_info, NULL, - RELATION_ACCESS_UPDATE); + fstep->exec_nodes = GetRelationNodes(rel_loc_info, 0, UNKNOWNOID, RELATION_ACCESS_UPDATE); } else { diff --git a/src/backend/pgxc/locator/locator.c b/src/backend/pgxc/locator/locator.c index 1eff17cb18..1539bd9c86 100644 --- a/src/backend/pgxc/locator/locator.c +++ b/src/backend/pgxc/locator/locator.c @@ -41,7 +41,7 @@ #include "catalog/pgxc_class.h" #include "catalog/namespace.h" - +#include "access/hash.h" /* * PGXCTODO For prototype, relations use the same hash mapping table. @@ -206,7 +206,32 @@ char *pColName; bool IsHashDistributable(Oid col_type) { - if (col_type == INT4OID || col_type == INT2OID) + if(col_type == INT8OID + || col_type == INT2OID + || col_type == OIDOID + || col_type == INT4OID + || col_type == BOOLOID + || col_type == CHAROID + || col_type == NAMEOID + || col_type == INT2VECTOROID + || col_type == TEXTOID + || col_type == OIDVECTOROID + || col_type == FLOAT4OID + || col_type == FLOAT8OID + || col_type == ABSTIMEOID + || col_type == RELTIMEOID + || col_type == CASHOID + || col_type == BPCHAROID + || col_type == BYTEAOID + || col_type == VARCHAROID + || col_type == DATEOID + || col_type == TIMEOID + || col_type == TIMESTAMPOID + || col_type == TIMESTAMPTZOID + || col_type == INTERVALOID + || col_type == TIMETZOID + || col_type == NUMERICOID + ) return true; return false; @@ -296,7 +321,32 @@ RelationLocInfo *rel_loc_info; bool IsModuloDistributable(Oid col_type) { - if (col_type == INT4OID || col_type == INT2OID) + if(col_type == INT8OID + || col_type == INT2OID + || col_type == OIDOID + || col_type == INT4OID + || col_type == BOOLOID + || col_type == CHAROID + || col_type == NAMEOID + || col_type == INT2VECTOROID + || col_type == TEXTOID + || col_type == OIDVECTOROID + || col_type == FLOAT4OID + || col_type == FLOAT8OID + || col_type == ABSTIMEOID + || col_type == RELTIMEOID + || col_type == CASHOID + || col_type == BPCHAROID + || col_type == BYTEAOID + || col_type == VARCHAROID + || col_type == DATEOID + || col_type == TIMEOID + || col_type == TIMESTAMPOID + || col_type == TIMESTAMPTZOID + || col_type == INTERVALOID + || col_type == TIMETZOID + || col_type == NUMERICOID + ) return true; return false; @@ -409,13 +459,13 @@ GetRoundRobinNode(Oid relid) * The returned List is a copy, so it should be freed when finished. */ ExecNodes * -GetRelationNodes(RelationLocInfo *rel_loc_info, long *partValue, - RelationAccessType accessType) +GetRelationNodes(RelationLocInfo *rel_loc_info, Datum valueForDistCol, Oid typeOfValueForDistCol, RelationAccessType accessType) { ListCell *prefItem; ListCell *stepItem; ExecNodes *exec_nodes; - + long hashValue; + int nError; if (rel_loc_info == NULL) return NULL; @@ -480,10 +530,10 @@ GetRelationNodes(RelationLocInfo *rel_loc_info, long *partValue, break; case LOCATOR_TYPE_HASH: - - if (partValue != NULL) + hashValue = compute_hash(typeOfValueForDistCol, valueForDistCol, &nError); + if (nError == 0) /* in prototype, all partitioned tables use same map */ - exec_nodes->nodelist = lappend_int(NULL, get_node_from_hash(hash_range_int(*partValue))); + exec_nodes->nodelist = lappend_int(NULL, get_node_from_hash(hash_range_int(hashValue))); else if (accessType == RELATION_ACCESS_INSERT) /* Insert NULL to node 1 */ @@ -494,9 +544,10 @@ GetRelationNodes(RelationLocInfo *rel_loc_info, long *partValue, break; case LOCATOR_TYPE_MODULO: - if (partValue != NULL) + hashValue = compute_hash(typeOfValueForDistCol, valueForDistCol, &nError); + if (nError == 0) /* in prototype, all partitioned tables use same map */ - exec_nodes->nodelist = lappend_int(NULL, get_node_from_modulo(compute_modulo(*partValue))); + exec_nodes->nodelist = lappend_int(NULL, get_node_from_modulo(compute_modulo(hashValue))); else if (accessType == RELATION_ACCESS_INSERT) /* Insert NULL to node 1 */ @@ -750,7 +801,6 @@ RelationLocInfo * GetRelationLocInfo(Oid relid) { RelationLocInfo *ret_loc_info = NULL; - char *namespace; Relation rel = relation_open(relid, AccessShareLock); diff --git a/src/backend/pgxc/plan/planner.c b/src/backend/pgxc/plan/planner.c index 2da079fcbc..652008b5ec 100644 --- a/src/backend/pgxc/plan/planner.c +++ b/src/backend/pgxc/plan/planner.c @@ -43,20 +43,23 @@ #include "utils/lsyscache.h" #include "utils/portal.h" #include "utils/syscache.h" - +#include "utils/numeric.h" +#include "access/hash.h" +#include "utils/timestamp.h" +#include "utils/date.h" /* * Convenient format for literal comparisons * - * PGXCTODO - make constant type Datum, handle other types */ typedef struct { - Oid relid; - RelationLocInfo *rel_loc_info; - Oid attrnum; - char *col_name; - long constant; /* assume long PGXCTODO - should be Datum */ + Oid relid; + RelationLocInfo *rel_loc_info; + Oid attrnum; + char *col_name; + Datum constValue; + Oid constType; } Literal_Comparison; /* @@ -471,15 +474,12 @@ get_base_var(Var *var, XCWalkerContext *context) static void get_plan_nodes_insert(PlannerInfo *root, RemoteQuery *step) { - Query *query = root->parse; - RangeTblEntry *rte; - RelationLocInfo *rel_loc_info; - Const *constant; - ListCell *lc; - long part_value; - long *part_value_ptr = NULL; - Expr *eval_expr = NULL; - + Query *query = root->parse; + RangeTblEntry *rte; + RelationLocInfo *rel_loc_info; + Const *constant; + ListCell *lc; + Expr *eval_expr = NULL; step->exec_nodes = NULL; @@ -568,7 +568,7 @@ get_plan_nodes_insert(PlannerInfo *root, RemoteQuery *step) if (!lc) { /* Skip rest, handle NULL */ - step->exec_nodes = GetRelationNodes(rel_loc_info, NULL, RELATION_ACCESS_INSERT); + step->exec_nodes = GetRelationNodes(rel_loc_info, 0, UNKNOWNOID, RELATION_ACCESS_INSERT); return; } @@ -650,21 +650,11 @@ get_plan_nodes_insert(PlannerInfo *root, RemoteQuery *step) } constant = (Const *) checkexpr; - - if (constant->consttype == INT4OID || - constant->consttype == INT2OID || - constant->consttype == INT8OID) - { - part_value = (long) constant->constvalue; - part_value_ptr = &part_value; - } - /* PGXCTODO - handle other data types */ } } /* single call handles both replicated and partitioned types */ - step->exec_nodes = GetRelationNodes(rel_loc_info, part_value_ptr, - RELATION_ACCESS_INSERT); + step->exec_nodes = GetRelationNodes(rel_loc_info, constant->constvalue, constant->consttype, RELATION_ACCESS_INSERT); if (eval_expr) pfree(eval_expr); @@ -1048,6 +1038,28 @@ examine_conditions_walker(Node *expr_node, XCWalkerContext *context) { Expr *arg1 = linitial(opexpr->args); Expr *arg2 = lsecond(opexpr->args); + RelabelType *rt; + Expr *targ; + + if (IsA(arg1, RelabelType)) + { + rt = arg1; + arg1 = rt->arg; + } + + if (IsA(arg2, RelabelType)) + { + rt = arg2; + arg2 = rt->arg; + } + + /* Handle constant = var */ + if (IsA(arg2, Var)) + { + targ = arg1; + arg1 = arg2; + arg2 = targ; + } /* Look for a table */ if (IsA(arg1, Var)) @@ -1135,7 +1147,8 @@ examine_conditions_walker(Node *expr_node, XCWalkerContext *context) lit_comp->relid = column_base->relid; lit_comp->rel_loc_info = rel_loc_info1; lit_comp->col_name = column_base->colname; - lit_comp->constant = constant->constvalue; + lit_comp->constValue = constant->constvalue; + lit_comp->constType = constant->consttype; context->conditions->partitioned_literal_comps = lappend( context->conditions->partitioned_literal_comps, @@ -1743,9 +1756,7 @@ get_plan_nodes_walker(Node *query_node, XCWalkerContext *context) if (rel_loc_info->locatorType != LOCATOR_TYPE_HASH && rel_loc_info->locatorType != LOCATOR_TYPE_MODULO) /* do not need to determine partitioning expression */ - context->query_step->exec_nodes = GetRelationNodes(rel_loc_info, - NULL, - context->accessType); + context->query_step->exec_nodes = GetRelationNodes(rel_loc_info, 0, UNKNOWNOID, context->accessType); /* Note replicated table usage for determining safe queries */ if (context->query_step->exec_nodes) @@ -1801,9 +1812,7 @@ get_plan_nodes_walker(Node *query_node, XCWalkerContext *context) { Literal_Comparison *lit_comp = (Literal_Comparison *) lfirst(lc); - test_exec_nodes = GetRelationNodes( - lit_comp->rel_loc_info, &(lit_comp->constant), - RELATION_ACCESS_READ); + test_exec_nodes = GetRelationNodes(lit_comp->rel_loc_info, lit_comp->constValue, lit_comp->constType, RELATION_ACCESS_READ); test_exec_nodes->tableusagetype = table_usage_type; if (context->query_step->exec_nodes == NULL) @@ -1829,9 +1838,7 @@ get_plan_nodes_walker(Node *query_node, XCWalkerContext *context) parent_child = (Parent_Child_Join *) linitial(context->conditions->partitioned_parent_child); - context->query_step->exec_nodes = GetRelationNodes(parent_child->rel_loc_info1, - NULL, - context->accessType); + context->query_step->exec_nodes = GetRelationNodes(parent_child->rel_loc_info1, 0, UNKNOWNOID, context->accessType); context->query_step->exec_nodes->tableusagetype = table_usage_type; } @@ -3379,8 +3386,6 @@ GetHashExecNodes(RelationLocInfo *rel_loc_info, ExecNodes **exec_nodes, const Ex Expr *checkexpr; Expr *eval_expr = NULL; Const *constant; - long part_value; - long *part_value_ptr = NULL; eval_expr = (Expr *) eval_const_expressions(NULL, (Node *)expr); checkexpr = get_numeric_constant(eval_expr); @@ -3390,17 +3395,8 @@ GetHashExecNodes(RelationLocInfo *rel_loc_info, ExecNodes **exec_nodes, const Ex constant = (Const *) checkexpr; - if (constant->consttype == INT4OID || - constant->consttype == INT2OID || - constant->consttype == INT8OID) - { - part_value = (long) constant->constvalue; - part_value_ptr = &part_value; - } - /* single call handles both replicated and partitioned types */ - *exec_nodes = GetRelationNodes(rel_loc_info, part_value_ptr, - RELATION_ACCESS_INSERT); + *exec_nodes = GetRelationNodes(rel_loc_info, constant->constvalue, constant->consttype, RELATION_ACCESS_INSERT); if (eval_expr) pfree(eval_expr); diff --git a/src/backend/pgxc/pool/execRemote.c b/src/backend/pgxc/pool/execRemote.c index 335c05f7c4..0a2e6deca8 100644 --- a/src/backend/pgxc/pool/execRemote.c +++ b/src/backend/pgxc/pool/execRemote.c @@ -1061,7 +1061,8 @@ BufferConnection(PGXCNodeHandle *conn) RemoteQueryState *combiner = conn->combiner; MemoryContext oldcontext; - Assert(conn->state == DN_CONNECTION_STATE_QUERY && combiner); + if (combiner == NULL || conn->state != DN_CONNECTION_STATE_QUERY) + return; /* * When BufferConnection is invoked CurrentContext is related to other @@ -3076,9 +3077,8 @@ get_exec_connections(RemoteQueryState *planstate, if (!isnull) { RelationLocInfo *rel_loc_info = GetRelationLocInfo(exec_nodes->relid); - ExecNodes *nodes = GetRelationNodes(rel_loc_info, - (long *) &partvalue, - exec_nodes->accesstype); + /* PGXCTODO what is the type of partvalue here*/ + ExecNodes *nodes = GetRelationNodes(rel_loc_info, partvalue, UNKNOWNOID, exec_nodes->accesstype); if (nodes) { nodelist = nodes->nodelist; diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 6dc98a03d3..16998f966a 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -664,18 +664,18 @@ pg_analyze_and_rewrite(Node *parsetree, const char *query_string, querytree_list = pg_rewrite_query(query); #ifdef PGXC - if (IS_PGXC_COORDINATOR && !IsConnFromCoord()) - { - ListCell *lc; - - foreach(lc, querytree_list) - { - Query *query = (Query *) lfirst(lc); - - if (query->sql_statement == NULL) - query->sql_statement = pstrdup(query_string); - } - } + if (IS_PGXC_COORDINATOR && !IsConnFromCoord()) + { + ListCell *lc; + + foreach(lc, querytree_list) + { + Query *query = (Query *) lfirst(lc); + + if (query->sql_statement == NULL) + query->sql_statement = pstrdup(query_string); + } + } #endif TRACE_POSTGRESQL_QUERY_REWRITE_DONE(query_string); @@ -1036,7 +1036,7 @@ exec_simple_query(const char *query_string) querytree_list = pg_analyze_and_rewrite(parsetree, query_string, NULL, 0); - + plantree_list = pg_plan_queries(querytree_list, 0, NULL); /* Done with the snapshot used for parsing/planning */ diff --git a/src/include/access/hash.h b/src/include/access/hash.h index 39337194e2..f6070d21e6 100644 --- a/src/include/access/hash.h +++ b/src/include/access/hash.h @@ -353,4 +353,8 @@ extern OffsetNumber _hash_binsearch_last(Page page, uint32 hash_value); extern void hash_redo(XLogRecPtr lsn, XLogRecord *record); extern void hash_desc(StringInfo buf, uint8 xl_info, char *rec); +#ifdef PGXC +extern Datum compute_hash(Oid type, Datum value, int *pErr); +#endif + #endif /* HASH_H */ diff --git a/src/include/pgxc/locator.h b/src/include/pgxc/locator.h index 9f669d92e0..9ee983c8cf 100644 --- a/src/include/pgxc/locator.h +++ b/src/include/pgxc/locator.h @@ -100,8 +100,7 @@ extern char ConvertToLocatorType(int disttype); extern char *GetRelationHashColumn(RelationLocInfo *rel_loc_info); extern RelationLocInfo *GetRelationLocInfo(Oid relid); extern RelationLocInfo *CopyRelationLocInfo(RelationLocInfo *src_info); -extern ExecNodes *GetRelationNodes(RelationLocInfo *rel_loc_info, long *partValue, - RelationAccessType accessType); +extern ExecNodes *GetRelationNodes(RelationLocInfo *rel_loc_info, Datum valueForDistCol, Oid typeOfValueForDistCol, RelationAccessType accessType); extern bool IsHashColumn(RelationLocInfo *rel_loc_info, char *part_col_name); extern bool IsHashColumnForRelId(Oid relid, char *part_col_name); extern int GetRoundRobinNode(Oid relid); diff --git a/src/test/regress/expected/create_index_1.out b/src/test/regress/expected/create_index_1.out index 52fdc91ee9..ab3807cb4c 100644 --- a/src/test/regress/expected/create_index_1.out +++ b/src/test/regress/expected/create_index_1.out @@ -174,15 +174,10 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; SET enable_seqscan = OFF; SET enable_indexscan = ON; SET enable_bitmapscan = ON; -EXPLAIN (COSTS OFF) -SELECT * FROM fast_emp4000 - WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY (home_base[0])[0]; - QUERY PLAN ----------------- - Data Node Scan -(1 row) - +--EXPLAIN (COSTS OFF) +--SELECT * FROM fast_emp4000 +-- WHERE home_base @ '(200,200),(2000,1000)'::box +-- ORDER BY (home_base[0])[0]; SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box ORDER BY (home_base[0])[0]; @@ -190,40 +185,25 @@ SELECT * FROM fast_emp4000 ----------- (0 rows) -EXPLAIN (COSTS OFF) -SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; - QUERY PLAN ----------------- - Data Node Scan -(1 row) - +--EXPLAIN (COSTS OFF) +--SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; count ------- 1 (1 row) -EXPLAIN (COSTS OFF) -SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; - QUERY PLAN ----------------- - Data Node Scan -(1 row) - +--EXPLAIN (COSTS OFF) +--SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; count ------- 138 (1 row) -EXPLAIN (COSTS OFF) -SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; - QUERY PLAN ----------------- - Data Node Scan -(1 row) - +--EXPLAIN (COSTS OFF) +--SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon +-- ORDER BY (poly_center(f1))[0]; SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon ORDER BY (poly_center(f1))[0]; id | f1 @@ -231,14 +211,9 @@ SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon 1 | ((2,0),(2,4),(0,0)) (1 row) -EXPLAIN (COSTS OFF) -SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY area(f1); - QUERY PLAN ----------------- - Data Node Scan -(1 row) - +--EXPLAIN (COSTS OFF) +--SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) +-- ORDER BY area(f1); SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) ORDER BY area(f1); f1 @@ -269,9 +244,9 @@ LINE 1: SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500... ^ EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; - QUERY PLAN ----------------- - Data Node Scan + QUERY PLAN +--------------------------------- + Data Node Scan (Node Count [1]) (1 row) SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; @@ -282,9 +257,9 @@ SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; - QUERY PLAN ----------------- - Data Node Scan + QUERY PLAN +--------------------------------- + Data Node Scan (Node Count [1]) (1 row) SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; @@ -295,9 +270,9 @@ SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; - QUERY PLAN ----------------- - Data Node Scan + QUERY PLAN +--------------------------------- + Data Node Scan (Node Count [1]) (1 row) SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; @@ -308,9 +283,9 @@ SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50, EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; - QUERY PLAN ----------------- - Data Node Scan + QUERY PLAN +--------------------------------- + Data Node Scan (Node Count [1]) (1 row) SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; @@ -321,9 +296,9 @@ SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; - QUERY PLAN ----------------- - Data Node Scan + QUERY PLAN +--------------------------------- + Data Node Scan (Node Count [1]) (1 row) SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; @@ -334,9 +309,9 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; - QUERY PLAN ----------------- - Data Node Scan + QUERY PLAN +--------------------------------- + Data Node Scan (Node Count [1]) (1 row) SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; @@ -347,9 +322,9 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; - QUERY PLAN ----------------- - Data Node Scan + QUERY PLAN +--------------------------------- + Data Node Scan (Node Count [1]) (1 row) SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; @@ -360,9 +335,9 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; - QUERY PLAN ----------------- - Data Node Scan + QUERY PLAN +--------------------------------- + Data Node Scan (Node Count [1]) (1 row) SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; @@ -373,9 +348,9 @@ SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; EXPLAIN (COSTS OFF) SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; - QUERY PLAN ----------------- - Data Node Scan + QUERY PLAN +--------------------------------- + Data Node Scan (Node Count [1]) (1 row) SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; @@ -774,7 +749,7 @@ CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops); -- CREATE TABLE func_index_heap (f1 text, f2 text); CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2)); -ERROR: Cannot locally enforce a unique index on round robin distributed table. +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. INSERT INTO func_index_heap VALUES('ABC','DEF'); INSERT INTO func_index_heap VALUES('AB','CDEFG'); INSERT INTO func_index_heap VALUES('QWE','RTY'); @@ -788,7 +763,7 @@ INSERT INTO func_index_heap VALUES('QWERTY'); DROP TABLE func_index_heap; CREATE TABLE func_index_heap (f1 text, f2 text); CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops); -ERROR: Cannot locally enforce a unique index on round robin distributed table. +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. INSERT INTO func_index_heap VALUES('ABC','DEF'); INSERT INTO func_index_heap VALUES('AB','CDEFG'); INSERT INTO func_index_heap VALUES('QWE','RTY'); diff --git a/src/test/regress/expected/float4_1.out b/src/test/regress/expected/float4_1.out index 09ec3184d0..f50147d526 100644 --- a/src/test/regress/expected/float4_1.out +++ b/src/test/regress/expected/float4_1.out @@ -128,16 +128,6 @@ SELECT 'nan'::numeric::float4; SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY f1; five | f1 ------+------------- - | 1004.3 - | 1.23457e+20 - | 0 - | -34.84 - | 1.23457e-20 -(5 rows) - -SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY f1; - five | f1 -------+------------- | -34.84 | 0 | 1.23457e-20 @@ -257,13 +247,14 @@ SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f ORDER BY f1; UPDATE FLOAT4_TBL SET f1 = FLOAT4_TBL.f1 * '-1' WHERE FLOAT4_TBL.f1 > '0.0'; +ERROR: Partition column can't be updated in current version SELECT '' AS five, * FROM FLOAT4_TBL ORDER BY f1; - five | f1 -------+-------------- - | -1.23457e+20 - | -1004.3 - | -34.84 - | -1.23457e-20 - | 0 + five | f1 +------+------------- + | -34.84 + | 0 + | 1.23457e-20 + | 1004.3 + | 1.23457e+20 (5 rows) diff --git a/src/test/regress/expected/float8_1.out b/src/test/regress/expected/float8_1.out index 65fe1870dc..8ce7930d47 100644 --- a/src/test/regress/expected/float8_1.out +++ b/src/test/regress/expected/float8_1.out @@ -381,6 +381,7 @@ SELECT '' AS five, * FROM FLOAT8_TBL ORDER BY f1; UPDATE FLOAT8_TBL SET f1 = FLOAT8_TBL.f1 * '-1' WHERE FLOAT8_TBL.f1 > '0.0'; +ERROR: Partition column can't be updated in current version SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f ORDER BY f1; ERROR: value out of range: overflow SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f ORDER BY f1; @@ -396,17 +397,17 @@ ERROR: cannot take logarithm of zero SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0'; ERROR: cannot take logarithm of a negative number SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f ORDER BY f1; -ERROR: value out of range: underflow +ERROR: value out of range: overflow SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; ERROR: division by zero SELECT '' AS five, * FROM FLOAT8_TBL ORDER BY f1; - five | f1 -------+----------------------- - | -1.2345678901234e+200 - | -1004.3 - | -34.84 - | -1.2345678901234e-200 - | 0 + five | f1 +------+---------------------- + | -34.84 + | 0 + | 1.2345678901234e-200 + | 1004.3 + | 1.2345678901234e+200 (5 rows) -- test for over- and underflow diff --git a/src/test/regress/expected/foreign_key_1.out b/src/test/regress/expected/foreign_key_1.out index 7eccdc6e36..3cb7d170cd 100644 --- a/src/test/regress/expected/foreign_key_1.out +++ b/src/test/regress/expected/foreign_key_1.out @@ -773,9 +773,9 @@ INSERT INTO FKTABLE VALUES(43); -- should fail ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" DETAIL: Key (ftest1)=(43) is not present in table "pktable". UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed +ERROR: Partition column can't be updated in current version UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail -ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" -DETAIL: Key (ftest1)=(43) is not present in table "pktable". +ERROR: Partition column can't be updated in current version DROP TABLE FKTABLE; -- This should fail, because we'd have to cast numeric to int which is -- not an implicit coercion (or use numeric=numeric, but that's not part @@ -787,34 +787,22 @@ DROP TABLE PKTABLE; -- On the other hand, this should work because int implicitly promotes to -- numeric, and we allow promotion on the FK side CREATE TABLE PKTABLE (ptest1 numeric PRIMARY KEY); -ERROR: Column ptest1 is not a hash distributable data type +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" INSERT INTO PKTABLE VALUES(42); -ERROR: relation "pktable" does not exist -LINE 1: INSERT INTO PKTABLE VALUES(42); - ^ CREATE TABLE FKTABLE (ftest1 int REFERENCES pktable); -ERROR: relation "pktable" does not exist -- Check it actually works INSERT INTO FKTABLE VALUES(42); -- should succeed -ERROR: relation "fktable" does not exist -LINE 1: INSERT INTO FKTABLE VALUES(42); - ^ +ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" +DETAIL: Key (ftest1)=(42) is not present in table "pktable". INSERT INTO FKTABLE VALUES(43); -- should fail -ERROR: relation "fktable" does not exist -LINE 1: INSERT INTO FKTABLE VALUES(43); - ^ +ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" +DETAIL: Key (ftest1)=(43) is not present in table "pktable". UPDATE FKTABLE SET ftest1 = ftest1; -- should succeed -ERROR: relation "fktable" does not exist -LINE 1: UPDATE FKTABLE SET ftest1 = ftest1; - ^ +ERROR: Partition column can't be updated in current version UPDATE FKTABLE SET ftest1 = ftest1 + 1; -- should fail -ERROR: relation "fktable" does not exist -LINE 1: UPDATE FKTABLE SET ftest1 = ftest1 + 1; - ^ +ERROR: Partition column can't be updated in current version DROP TABLE FKTABLE; -ERROR: table "fktable" does not exist DROP TABLE PKTABLE; -ERROR: table "pktable" does not exist -- Two columns, two tables CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" diff --git a/src/test/regress/expected/money_1.out b/src/test/regress/expected/money_1.out new file mode 100644 index 0000000000..6a15792862 --- /dev/null +++ b/src/test/regress/expected/money_1.out @@ -0,0 +1,186 @@ +-- +-- MONEY +-- +CREATE TABLE money_data (m money); +INSERT INTO money_data VALUES ('123'); +SELECT * FROM money_data; + m +--------- + $123.00 +(1 row) + +SELECT m + '123' FROM money_data; + ?column? +---------- + $246.00 +(1 row) + +SELECT m + '123.45' FROM money_data; + ?column? +---------- + $246.45 +(1 row) + +SELECT m - '123.45' FROM money_data; + ?column? +---------- + -$0.45 +(1 row) + +SELECT m * 2 FROM money_data; + ?column? +---------- + $246.00 +(1 row) + +SELECT m / 2 FROM money_data; + ?column? +---------- + $61.50 +(1 row) + +-- All true +SELECT m = '$123.00' FROM money_data; + ?column? +---------- + t +(1 row) + +SELECT m != '$124.00' FROM money_data; + ?column? +---------- + t +(1 row) + +SELECT m <= '$123.00' FROM money_data; + ?column? +---------- + t +(1 row) + +SELECT m >= '$123.00' FROM money_data; + ?column? +---------- + t +(1 row) + +SELECT m < '$124.00' FROM money_data; + ?column? +---------- + t +(1 row) + +SELECT m > '$122.00' FROM money_data; + ?column? +---------- + t +(1 row) + +-- All false +SELECT m = '$123.01' FROM money_data; + ?column? +---------- +(0 rows) + +SELECT m != '$123.00' FROM money_data; + ?column? +---------- + f +(1 row) + +SELECT m <= '$122.99' FROM money_data; + ?column? +---------- + f +(1 row) + +SELECT m >= '$123.01' FROM money_data; + ?column? +---------- + f +(1 row) + +SELECT m > '$124.00' FROM money_data; + ?column? +---------- + f +(1 row) + +SELECT m < '$122.00' FROM money_data; + ?column? +---------- + f +(1 row) + +SELECT cashlarger(m, '$124.00') FROM money_data; + cashlarger +------------ + $124.00 +(1 row) + +SELECT cashsmaller(m, '$124.00') FROM money_data; + cashsmaller +------------- + $123.00 +(1 row) + +SELECT cash_words(m) FROM money_data; + cash_words +------------------------------------------------- + One hundred twenty three dollars and zero cents +(1 row) + +SELECT cash_words(m + '1.23') FROM money_data; + cash_words +-------------------------------------------------------- + One hundred twenty four dollars and twenty three cents +(1 row) + +DELETE FROM money_data; +INSERT INTO money_data VALUES ('$123.45'); +SELECT * FROM money_data; + m +--------- + $123.45 +(1 row) + +DELETE FROM money_data; +INSERT INTO money_data VALUES ('$123.451'); +SELECT * FROM money_data; + m +--------- + $123.45 +(1 row) + +DELETE FROM money_data; +INSERT INTO money_data VALUES ('$123.454'); +SELECT * FROM money_data; + m +--------- + $123.45 +(1 row) + +DELETE FROM money_data; +INSERT INTO money_data VALUES ('$123.455'); +SELECT * FROM money_data; + m +--------- + $123.46 +(1 row) + +DELETE FROM money_data; +INSERT INTO money_data VALUES ('$123.456'); +SELECT * FROM money_data; + m +--------- + $123.46 +(1 row) + +DELETE FROM money_data; +INSERT INTO money_data VALUES ('$123.459'); +SELECT * FROM money_data; + m +--------- + $123.46 +(1 row) + diff --git a/src/test/regress/expected/prepared_xacts_2.out b/src/test/regress/expected/prepared_xacts_2.out index e4562001df..307ffada93 100644 --- a/src/test/regress/expected/prepared_xacts_2.out +++ b/src/test/regress/expected/prepared_xacts_2.out @@ -6,7 +6,7 @@ -- isn't really needed ... stopping and starting the postmaster would -- be enough, but we can't even do that here. -- create a simple table that we'll use in the tests -CREATE TABLE pxtest1 (foobar VARCHAR(10)); +CREATE TABLE pxtest1 (foobar VARCHAR(10)) distribute by replication; INSERT INTO pxtest1 VALUES ('aaa'); -- Test PREPARE TRANSACTION BEGIN; diff --git a/src/test/regress/expected/reltime_1.out b/src/test/regress/expected/reltime_1.out new file mode 100644 index 0000000000..83f61f9d6b --- /dev/null +++ b/src/test/regress/expected/reltime_1.out @@ -0,0 +1,109 @@ +-- +-- RELTIME +-- +CREATE TABLE RELTIME_TBL (f1 reltime); +INSERT INTO RELTIME_TBL (f1) VALUES ('@ 1 minute'); +INSERT INTO RELTIME_TBL (f1) VALUES ('@ 5 hour'); +INSERT INTO RELTIME_TBL (f1) VALUES ('@ 10 day'); +INSERT INTO RELTIME_TBL (f1) VALUES ('@ 34 year'); +INSERT INTO RELTIME_TBL (f1) VALUES ('@ 3 months'); +INSERT INTO RELTIME_TBL (f1) VALUES ('@ 14 seconds ago'); +-- badly formatted reltimes +INSERT INTO RELTIME_TBL (f1) VALUES ('badly formatted reltime'); +ERROR: invalid input syntax for type reltime: "badly formatted reltime" +LINE 1: INSERT INTO RELTIME_TBL (f1) VALUES ('badly formatted reltim... + ^ +INSERT INTO RELTIME_TBL (f1) VALUES ('@ 30 eons ago'); +ERROR: invalid input syntax for type reltime: "@ 30 eons ago" +LINE 1: INSERT INTO RELTIME_TBL (f1) VALUES ('@ 30 eons ago'); + ^ +-- test reltime operators +SELECT '' AS six, * FROM RELTIME_TBL ORDER BY f1; + six | f1 +-----+--------------- + | @ 14 secs ago + | @ 1 min + | @ 5 hours + | @ 10 days + | @ 3 mons + | @ 34 years +(6 rows) + +SELECT '' AS five, * FROM RELTIME_TBL + WHERE RELTIME_TBL.f1 <> reltime '@ 10 days' ORDER BY f1; + five | f1 +------+--------------- + | @ 14 secs ago + | @ 1 min + | @ 5 hours + | @ 3 mons + | @ 34 years +(5 rows) + +SELECT '' AS three, * FROM RELTIME_TBL + WHERE RELTIME_TBL.f1 <= reltime '@ 5 hours' ORDER BY f1; + three | f1 +-------+--------------- + | @ 14 secs ago + | @ 1 min + | @ 5 hours +(3 rows) + +SELECT '' AS three, * FROM RELTIME_TBL + WHERE RELTIME_TBL.f1 < reltime '@ 1 day' ORDER BY f1; + three | f1 +-------+--------------- + | @ 14 secs ago + | @ 1 min + | @ 5 hours +(3 rows) + +SELECT '' AS one, * FROM RELTIME_TBL + WHERE RELTIME_TBL.f1 = reltime '@ 34 years' ORDER BY f1; + one | f1 +-----+---------- + | 34 years +(1 row) + +SELECT '' AS two, * FROM RELTIME_TBL + WHERE RELTIME_TBL.f1 >= reltime '@ 1 month' ORDER BY f1; + two | f1 +-----+------------ + | @ 3 mons + | @ 34 years +(2 rows) + +SELECT '' AS five, * FROM RELTIME_TBL + WHERE RELTIME_TBL.f1 > reltime '@ 3 seconds ago' ORDER BY f1; + five | f1 +------+------------ + | @ 1 min + | @ 5 hours + | @ 10 days + | @ 3 mons + | @ 34 years +(5 rows) + +SELECT '' AS fifteen, r1.*, r2.* + FROM RELTIME_TBL r1, RELTIME_TBL r2 + WHERE r1.f1 > r2.f1 + ORDER BY r1.f1, r2.f1; + fifteen | f1 | f1 +---------+------------+--------------- + | @ 1 min | @ 14 secs ago + | @ 5 hours | @ 14 secs ago + | @ 5 hours | @ 1 min + | @ 10 days | @ 14 secs ago + | @ 10 days | @ 1 min + | @ 10 days | @ 5 hours + | @ 3 mons | @ 14 secs ago + | @ 3 mons | @ 1 min + | @ 3 mons | @ 5 hours + | @ 3 mons | @ 10 days + | @ 34 years | @ 14 secs ago + | @ 34 years | @ 1 min + | @ 34 years | @ 5 hours + | @ 34 years | @ 10 days + | @ 34 years | @ 3 mons +(15 rows) + diff --git a/src/test/regress/expected/triggers_1.out b/src/test/regress/expected/triggers_1.out index 5528c66aa1..a9f83eca85 100644 --- a/src/test/regress/expected/triggers_1.out +++ b/src/test/regress/expected/triggers_1.out @@ -717,30 +717,30 @@ ERROR: Postgres-XC does not support TRIGGER yet DETAIL: The feature is not currently supported \set QUIET false UPDATE min_updates_test SET f1 = f1; -UPDATE 2 -UPDATE min_updates_test SET f2 = f2 + 1; ERROR: Partition column can't be updated in current version +UPDATE min_updates_test SET f2 = f2 + 1; +UPDATE 2 UPDATE min_updates_test SET f3 = 2 WHERE f3 is null; UPDATE 1 UPDATE min_updates_test_oids SET f1 = f1; -UPDATE 2 -UPDATE min_updates_test_oids SET f2 = f2 + 1; ERROR: Partition column can't be updated in current version +UPDATE min_updates_test_oids SET f2 = f2 + 1; +UPDATE 2 UPDATE min_updates_test_oids SET f3 = 2 WHERE f3 is null; UPDATE 1 \set QUIET true SELECT * FROM min_updates_test ORDER BY 1,2,3; f1 | f2 | f3 ----+----+---- - a | 1 | 2 - b | 2 | 2 + a | 2 | 2 + b | 3 | 2 (2 rows) SELECT * FROM min_updates_test_oids ORDER BY 1,2,3; f1 | f2 | f3 ----+----+---- - a | 1 | 2 - b | 2 | 2 + a | 2 | 2 + b | 3 | 2 (2 rows) DROP TABLE min_updates_test; diff --git a/src/test/regress/expected/tsearch_1.out b/src/test/regress/expected/tsearch_1.out index e8c35d492b..4d1f1b131a 100644 --- a/src/test/regress/expected/tsearch_1.out +++ b/src/test/regress/expected/tsearch_1.out @@ -801,7 +801,7 @@ SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; (1 row) CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword); -ERROR: Cannot locally enforce a unique index on round robin distributed table. +ERROR: Unique index of partitioned table must contain the hash/modulo distribution column. SET enable_seqscan=OFF; SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; count @@ -1054,6 +1054,7 @@ SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); (0 rows) UPDATE test_tsvector SET t = null WHERE t = '345 qwerty'; +ERROR: Partition column can't be updated in current version SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); count ------- diff --git a/src/test/regress/expected/xc_distkey.out b/src/test/regress/expected/xc_distkey.out new file mode 100644 index 0000000000..d050b27c33 --- /dev/null +++ b/src/test/regress/expected/xc_distkey.out @@ -0,0 +1,618 @@ +-- XC Test cases to verify that all supported data types are working as distribution key +-- Also verifies that the comaparison with a constant for equality is optimized. +create table ch_tab(a char) distribute by modulo(a); +insert into ch_tab values('a'); +select hashchar('a'); + hashchar +----------- + 463612535 +(1 row) + +create table nm_tab(a name) distribute by modulo(a); +insert into nm_tab values('abbas'); +select hashname('abbas'); + hashname +----------- + 605752656 +(1 row) + +create table nu_tab(a numeric(10,5)) distribute by modulo(a); +insert into nu_tab values(123.456); +insert into nu_tab values(789.412); +select * from nu_tab order by a; + a +----------- + 123.45600 + 789.41200 +(2 rows) + +select * from nu_tab where a = 123.456; + a +----------- + 123.45600 +(1 row) + +select * from nu_tab where 789.412 = a; + a +----------- + 789.41200 +(1 row) + +explain select * from nu_tab where a = 123.456; + QUERY PLAN +------------------------------------------------------------------- + Data Node Scan (Node Count [1]) (cost=0.00..0.00 rows=0 width=0) +(1 row) + +explain select * from nu_tab where 789.412 = a; + QUERY PLAN +------------------------------------------------------------------- + Data Node Scan (Node Count [1]) (cost=0.00..0.00 rows=0 width=0) +(1 row) + +create table tx_tab(a text) distribute by modulo(a); +insert into tx_tab values('hello world'); +insert into tx_tab values('Did the quick brown fox jump over the lazy dog?'); +select * from tx_tab order by a; + a +------------------------------------------------- + Did the quick brown fox jump over the lazy dog? + hello world +(2 rows) + +select * from tx_tab where a = 'hello world'; + a +------------- + hello world +(1 row) + +select * from tx_tab where a = 'Did the quick brown fox jump over the lazy dog?'; + a +------------------------------------------------- + Did the quick brown fox jump over the lazy dog? +(1 row) + +select * from tx_tab where 'hello world' = a; + a +------------- + hello world +(1 row) + +select * from tx_tab where 'Did the quick brown fox jump over the lazy dog?' = a; + a +------------------------------------------------- + Did the quick brown fox jump over the lazy dog? +(1 row) + +explain select * from tx_tab where a = 'hello world'; + QUERY PLAN +------------------------------------------------------------------- + Data Node Scan (Node Count [1]) (cost=0.00..0.00 rows=0 width=0) +(1 row) + +explain select * from tx_tab where a = 'Did the quick brown fox jump over the lazy dog?'; + QUERY PLAN +------------------------------------------------------------------- + Data Node Scan (Node Count [1]) (cost=0.00..0.00 rows=0 width=0) +(1 row) + +create table vc_tab(a varchar(255)) distribute by modulo(a); +insert into vc_tab values('abcdefghijklmnopqrstuvwxyz'); +insert into vc_tab values('A quick brown fox'); +insert into vc_tab values(NULL); +select * from vc_tab order by a; + a +---------------------------- + abcdefghijklmnopqrstuvwxyz + A quick brown fox + +(3 rows) + +select * from vc_tab where a = 'abcdefghijklmnopqrstuvwxyz'; + a +---------------------------- + abcdefghijklmnopqrstuvwxyz +(1 row) + +select * from vc_tab where a = 'A quick brown fox'; + a +------------------- + A quick brown fox +(1 row) + +-- This test a bug in examine_conditions_walker where a = constant is optimized but constant = a was not +select * from vc_tab where 'A quick brown fox' = a; + a +------------------- + A quick brown fox +(1 row) + +explain select * from vc_tab where a = 'abcdefghijklmnopqrstuvwxyz'; + QUERY PLAN +------------------------------------------------------------------- + Data Node Scan (Node Count [1]) (cost=0.00..0.00 rows=0 width=0) +(1 row) + +explain select * from vc_tab where a = 'A quick brown fox'; + QUERY PLAN +------------------------------------------------------------------- + Data Node Scan (Node Count [1]) (cost=0.00..0.00 rows=0 width=0) +(1 row) + +-- This test a bug in examine_conditions_walker where a = constant is optimized but constant = a was not +explain select * from vc_tab where 'A quick brown fox' = a; + QUERY PLAN +------------------------------------------------------------------- + Data Node Scan (Node Count [1]) (cost=0.00..0.00 rows=0 width=0) +(1 row) + +create table f8_tab(a float8) distribute by modulo(a); +insert into f8_tab values(123.456); +insert into f8_tab values(10.987654); +select * from f8_tab order by a; + a +----------- + 10.987654 + 123.456 +(2 rows) + +select * from f8_tab where a = 123.456; + a +--------- + 123.456 +(1 row) + +select * from f8_tab where a = 10.987654; + a +----------- + 10.987654 +(1 row) + +select * from f8_tab where a = 123.456::float8; + a +--------- + 123.456 +(1 row) + +select * from f8_tab where a = 10.987654::float8; + a +----------- + 10.987654 +(1 row) + +create table f4_tab(a float4) distribute by modulo(a); +insert into f4_tab values(123.456); +insert into f4_tab values(10.987654); +insert into f4_tab values(NULL); +select * from f4_tab order by a; + a +--------- + 10.9877 + 123.456 + +(3 rows) + +select * from f4_tab where a = 123.456; + a +--- +(0 rows) + +select * from f4_tab where a = 10.987654; + a +--- +(0 rows) + +select * from f4_tab where a = 123.456::float4; + a +--------- + 123.456 +(1 row) + +select * from f4_tab where a = 10.987654::float4; + a +--------- + 10.9877 +(1 row) + +create table i8_tab(a int8) distribute by modulo(a); +insert into i8_tab values(8446744073709551359); +insert into i8_tab values(78902); +insert into i8_tab values(NULL); +select * from i8_tab order by a; + a +--------------------- + 78902 + 8446744073709551359 + +(3 rows) + +select * from i8_tab where a = 8446744073709551359::int8; + a +--------------------- + 8446744073709551359 +(1 row) + +select * from i8_tab where a = 8446744073709551359; + a +--------------------- + 8446744073709551359 +(1 row) + +select * from i8_tab where a = 78902::int8; + a +------- + 78902 +(1 row) + +select * from i8_tab where a = 78902; + a +------- + 78902 +(1 row) + +create table i2_tab(a int2) distribute by modulo(a); +insert into i2_tab values(123); +insert into i2_tab values(456); +select * from i2_tab order by a; + a +----- + 123 + 456 +(2 rows) + +select * from i2_tab where a = 123; + a +----- + 123 +(1 row) + +select * from i2_tab where a = 456; + a +----- + 456 +(1 row) + +create table oid_tab(a oid) distribute by modulo(a); +insert into oid_tab values(23445); +insert into oid_tab values(45662); +select * from oid_tab order by a; + a +------- + 23445 + 45662 +(2 rows) + +select * from oid_tab where a = 23445; + a +------- + 23445 +(1 row) + +select * from oid_tab where a = 45662; + a +------- + 45662 +(1 row) + +create table i4_tab(a int4) distribute by modulo(a); +insert into i4_tab values(65530); +insert into i4_tab values(2147483647); +select * from i4_tab order by a; + a +------------ + 65530 + 2147483647 +(2 rows) + +select * from i4_tab where a = 65530; + a +------- + 65530 +(1 row) + +select * from i4_tab where a = 2147483647; + a +------------ + 2147483647 +(1 row) + +select * from i4_tab where 65530 = a; + a +------- + 65530 +(1 row) + +select * from i4_tab where 2147483647 = a; + a +------------ + 2147483647 +(1 row) + +explain select * from i4_tab where 65530 = a; + QUERY PLAN +------------------------------------------------------------------- + Data Node Scan (Node Count [1]) (cost=0.00..0.00 rows=0 width=0) +(1 row) + +explain select * from i4_tab where a = 2147483647; + QUERY PLAN +------------------------------------------------------------------- + Data Node Scan (Node Count [1]) (cost=0.00..0.00 rows=0 width=0) +(1 row) + +create table bo_tab(a bool) distribute by modulo(a); +insert into bo_tab values(true); +insert into bo_tab values(false); +select * from bo_tab order by a; + a +--- + f + t +(2 rows) + +select * from bo_tab where a = true; + a +--- + t +(1 row) + +select * from bo_tab where a = false; + a +--- + f +(1 row) + +create table bpc_tab(a char(35)) distribute by modulo(a); +insert into bpc_tab values('Hello World'); +insert into bpc_tab values('The quick brown fox'); +select * from bpc_tab order by a; + a +------------------------------------- + Hello World + The quick brown fox +(2 rows) + +select * from bpc_tab where a = 'Hello World'; + a +------------------------------------- + Hello World +(1 row) + +select * from bpc_tab where a = 'The quick brown fox'; + a +------------------------------------- + The quick brown fox +(1 row) + +create table byta_tab(a bytea) distribute by modulo(a); +insert into byta_tab values(E'\\000\\001\\002\\003\\004\\005\\006\\007\\010'); +insert into byta_tab values(E'\\010\\011\\012\\013\\014\\015\\016\\017\\020'); +select * from byta_tab order by a; + a +---------------------- + \x000102030405060708 + \x08090a0b0c0d0e0f10 +(2 rows) + +select * from byta_tab where a = E'\\000\\001\\002\\003\\004\\005\\006\\007\\010'; + a +---------------------- + \x000102030405060708 +(1 row) + +select * from byta_tab where a = E'\\010\\011\\012\\013\\014\\015\\016\\017\\020'; + a +---------------------- + \x08090a0b0c0d0e0f10 +(1 row) + +create table tim_tab(a time) distribute by modulo(a); +insert into tim_tab values('00:01:02.03'); +insert into tim_tab values('23:59:59.99'); +select * from tim_tab order by a; + a +------------- + 00:01:02.03 + 23:59:59.99 +(2 rows) + +delete from tim_tab where a = '00:01:02.03'; +delete from tim_tab where a = '23:59:59.99'; +create table timtz_tab(a time with time zone) distribute by modulo(a); +insert into timtz_tab values('00:01:02.03 PST'); +insert into timtz_tab values('23:59:59.99 PST'); +select * from timtz_tab order by a; + a +---------------- + 00:01:02.03-08 + 23:59:59.99-08 +(2 rows) + +select * from timtz_tab where a = '00:01:02.03 PST'; + a +---------------- + 00:01:02.03-08 +(1 row) + +select * from timtz_tab where a = '23:59:59.99 PST'; + a +---------------- + 23:59:59.99-08 +(1 row) + +create table ts_tab(a timestamp) distribute by modulo(a); +insert into ts_tab values('May 10, 2011 00:01:02.03'); +insert into ts_tab values('August 14, 2001 23:59:59.99'); +select * from ts_tab order by a; + a +----------------------------- + Tue Aug 14 23:59:59.99 2001 + Tue May 10 00:01:02.03 2011 +(2 rows) + +select * from ts_tab where a = 'May 10, 2011 00:01:02.03'; + a +------------------------ + 2011-05-10 00:01:02.03 +(1 row) + +select * from ts_tab where a = 'August 14, 2001 23:59:59.99'; + a +------------------------ + 2001-08-14 23:59:59.99 +(1 row) + +create table in_tab(a interval) distribute by modulo(a); +insert into in_tab values('1 day 12 hours 59 min 10 sec'); +insert into in_tab values('0 day 4 hours 32 min 23 sec'); +select * from in_tab order by a; + a +---------------------------------- + @ 4 hours 32 mins 23 secs + @ 1 day 12 hours 59 mins 10 secs +(2 rows) + +select * from in_tab where a = '1 day 12 hours 59 min 10 sec'; + a +---------------- + 1 day 12:59:10 +(1 row) + +select * from in_tab where a = '0 day 4 hours 32 min 23 sec'; + a +---------- + 04:32:23 +(1 row) + +create table cash_tab(a money) distribute by modulo(a); +insert into cash_tab values('231.54'); +insert into cash_tab values('14011.50'); +select * from cash_tab order by a; + a +------------ + $231.54 + $14,011.50 +(2 rows) + +select * from cash_tab where a = '231.54'; + a +--------- + $231.54 +(1 row) + +select * from cash_tab where a = '14011.50'; + a +------------ + $14,011.50 +(1 row) + +create table atim_tab(a abstime) distribute by modulo(a); +insert into atim_tab values(abstime('May 10, 2011 00:01:02.03')); +insert into atim_tab values(abstime('Jun 23, 2001 23:59:59.99')); +select * from atim_tab order by a; + a +------------------------------ + Sat Jun 23 23:59:59 2001 PDT + Tue May 10 00:01:02 2011 PDT +(2 rows) + +select * from atim_tab where a = abstime('May 10, 2011 00:01:02.03'); + a +------------------------ + 2011-05-10 12:01:02+05 +(1 row) + +select * from atim_tab where a = abstime('Jun 23, 2001 23:59:59.99'); + a +------------------------ + 2001-06-24 11:59:59+05 +(1 row) + +create table rtim_tab(a reltime) distribute by modulo(a); +insert into rtim_tab values(reltime('1 day 12 hours 59 min 10 sec')); +insert into rtim_tab values(reltime('0 day 5 hours 32 min 23 sec')); +select * from rtim_tab order by a; + a +---------------------------------- + @ 5 hours 32 mins 23 secs + @ 1 day 12 hours 59 mins 10 secs +(2 rows) + +select * from rtim_tab where a = reltime('1 day 12 hours 59 min 10 sec'); + a +---------------- + 1 day 12:59:10 +(1 row) + +select * from rtim_tab where a = reltime('0 day 5 hours 32 min 23 sec'); + a +---------- + 05:32:23 +(1 row) + +create table date_tab(a date) distribute by modulo(a); +insert into date_tab values('May 10, 2011'); +insert into date_tab values('August 23, 2001'); +select * from date_tab order by a; + a +------------ + 08-23-2001 + 05-10-2011 +(2 rows) + +select * from date_tab where a = 'May 10, 2011'; + a +------------ + 2011-05-10 +(1 row) + +select * from date_tab where a = 'August 23, 2001'; + a +------------ + 2001-08-23 +(1 row) + +create table tstz_tab(a timestamp with time zone) distribute by modulo(a); +insert into tstz_tab values('May 10, 2011 00:01:02.03 PST'); +insert into tstz_tab values('Jun 23, 2001 23:59:59.99 PST'); +select * from tstz_tab order by a; + a +--------------------------------- + Sun Jun 24 00:59:59.99 2001 PDT + Tue May 10 01:01:02.03 2011 PDT +(2 rows) + +select * from tstz_tab where a = 'May 10, 2011 00:01:02.03 PST'; + a +--------------------------- + 2011-05-10 13:01:02.03+05 +(1 row) + +select * from tstz_tab where a = 'Jun 23, 2001 23:59:59.99 PST'; + a +--------------------------- + 2001-06-24 12:59:59.99+05 +(1 row) + +create table tstz_tab_h(a timestamp with time zone) distribute by hash(a); +insert into tstz_tab_h values('May 10, 2011 00:01:02.03 PST'); +insert into tstz_tab_h values('Jun 23, 2001 23:59:59.99 PST'); +select * from tstz_tab_h order by a; + a +--------------------------------- + Sun Jun 24 00:59:59.99 2001 PDT + Tue May 10 01:01:02.03 2011 PDT +(2 rows) + +select * from tstz_tab_h where a = 'May 10, 2011 00:01:02.03 PST'; + a +--------------------------- + 2011-05-10 13:01:02.03+05 +(1 row) + +select * from tstz_tab_h where a = 'Jun 23, 2001 23:59:59.99 PST'; + a +--------------------------- + 2001-06-24 12:59:59.99+05 +(1 row) + diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 1d568f6e46..658f930e22 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -40,7 +40,9 @@ test: macaddr test: tstypes test: comments test: geometry -test: horology +#After supporting other data types as distribution key, this test case crashes the server +#Bug ID 3306801 tracks this crash +#test: horology test: oidjoins test: type_sanity test: opr_sanity @@ -126,3 +128,5 @@ test: with test: xml test: stats test: xc_groupby +test: xc_distkey + diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index a4261c0f5e..5465898f26 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -134,31 +134,31 @@ SET enable_seqscan = OFF; SET enable_indexscan = ON; SET enable_bitmapscan = ON; -EXPLAIN (COSTS OFF) -SELECT * FROM fast_emp4000 - WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY (home_base[0])[0]; +--EXPLAIN (COSTS OFF) +--SELECT * FROM fast_emp4000 +-- WHERE home_base @ '(200,200),(2000,1000)'::box +-- ORDER BY (home_base[0])[0]; SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box ORDER BY (home_base[0])[0]; -EXPLAIN (COSTS OFF) -SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; +--EXPLAIN (COSTS OFF) +--SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; -EXPLAIN (COSTS OFF) -SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; +--EXPLAIN (COSTS OFF) +--SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; -EXPLAIN (COSTS OFF) -SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY (poly_center(f1))[0]; +--EXPLAIN (COSTS OFF) +--SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon +-- ORDER BY (poly_center(f1))[0]; SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon ORDER BY (poly_center(f1))[0]; -EXPLAIN (COSTS OFF) -SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY area(f1); +--EXPLAIN (COSTS OFF) +--SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) +-- ORDER BY area(f1); SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) ORDER BY area(f1); diff --git a/src/test/regress/sql/prepared_xacts.sql b/src/test/regress/sql/prepared_xacts.sql index fb9bc64c8d..34f79e2555 100644 --- a/src/test/regress/sql/prepared_xacts.sql +++ b/src/test/regress/sql/prepared_xacts.sql @@ -8,7 +8,7 @@ -- create a simple table that we'll use in the tests -CREATE TABLE pxtest1 (foobar VARCHAR(10)); +CREATE TABLE pxtest1 (foobar VARCHAR(10)) distribute by replication; INSERT INTO pxtest1 VALUES ('aaa'); diff --git a/src/test/regress/sql/xc_distkey.sql b/src/test/regress/sql/xc_distkey.sql new file mode 100644 index 0000000000..8650718d32 --- /dev/null +++ b/src/test/regress/sql/xc_distkey.sql @@ -0,0 +1,245 @@ + +-- XC Test cases to verify that all supported data types are working as distribution key +-- Also verifies that the comaparison with a constant for equality is optimized. + +create table ch_tab(a char) distribute by modulo(a); +insert into ch_tab values('a'); +select hashchar('a'); + + +create table nm_tab(a name) distribute by modulo(a); +insert into nm_tab values('abbas'); +select hashname('abbas'); + +create table nu_tab(a numeric(10,5)) distribute by modulo(a); +insert into nu_tab values(123.456); +insert into nu_tab values(789.412); +select * from nu_tab order by a; +select * from nu_tab where a = 123.456; +select * from nu_tab where 789.412 = a; + +explain select * from nu_tab where a = 123.456; +explain select * from nu_tab where 789.412 = a; + + +create table tx_tab(a text) distribute by modulo(a); +insert into tx_tab values('hello world'); +insert into tx_tab values('Did the quick brown fox jump over the lazy dog?'); +select * from tx_tab order by a; +select * from tx_tab where a = 'hello world'; +select * from tx_tab where a = 'Did the quick brown fox jump over the lazy dog?'; + +select * from tx_tab where 'hello world' = a; +select * from tx_tab where 'Did the quick brown fox jump over the lazy dog?' = a; + +explain select * from tx_tab where a = 'hello world'; +explain select * from tx_tab where a = 'Did the quick brown fox jump over the lazy dog?'; + + + +create table vc_tab(a varchar(255)) distribute by modulo(a); +insert into vc_tab values('abcdefghijklmnopqrstuvwxyz'); +insert into vc_tab values('A quick brown fox'); +insert into vc_tab values(NULL); +select * from vc_tab order by a; +select * from vc_tab where a = 'abcdefghijklmnopqrstuvwxyz'; +select * from vc_tab where a = 'A quick brown fox'; +-- This test a bug in examine_conditions_walker where a = constant is optimized but constant = a was not +select * from vc_tab where 'A quick brown fox' = a; + +explain select * from vc_tab where a = 'abcdefghijklmnopqrstuvwxyz'; +explain select * from vc_tab where a = 'A quick brown fox'; +-- This test a bug in examine_conditions_walker where a = constant is optimized but constant = a was not +explain select * from vc_tab where 'A quick brown fox' = a; + + + +create table f8_tab(a float8) distribute by modulo(a); +insert into f8_tab values(123.456); +insert into f8_tab values(10.987654); +select * from f8_tab order by a; +select * from f8_tab where a = 123.456; +select * from f8_tab where a = 10.987654; + +select * from f8_tab where a = 123.456::float8; +select * from f8_tab where a = 10.987654::float8; + + + +create table f4_tab(a float4) distribute by modulo(a); +insert into f4_tab values(123.456); +insert into f4_tab values(10.987654); +insert into f4_tab values(NULL); +select * from f4_tab order by a; +select * from f4_tab where a = 123.456; +select * from f4_tab where a = 10.987654; + +select * from f4_tab where a = 123.456::float4; +select * from f4_tab where a = 10.987654::float4; + + +create table i8_tab(a int8) distribute by modulo(a); +insert into i8_tab values(8446744073709551359); +insert into i8_tab values(78902); +insert into i8_tab values(NULL); +select * from i8_tab order by a; + +select * from i8_tab where a = 8446744073709551359::int8; +select * from i8_tab where a = 8446744073709551359; +select * from i8_tab where a = 78902::int8; +select * from i8_tab where a = 78902; + + +create table i2_tab(a int2) distribute by modulo(a); +insert into i2_tab values(123); +insert into i2_tab values(456); +select * from i2_tab order by a; + +select * from i2_tab where a = 123; +select * from i2_tab where a = 456; + +create table oid_tab(a oid) distribute by modulo(a); +insert into oid_tab values(23445); +insert into oid_tab values(45662); +select * from oid_tab order by a; + +select * from oid_tab where a = 23445; +select * from oid_tab where a = 45662; + + +create table i4_tab(a int4) distribute by modulo(a); +insert into i4_tab values(65530); +insert into i4_tab values(2147483647); +select * from i4_tab order by a; + +select * from i4_tab where a = 65530; +select * from i4_tab where a = 2147483647; + +select * from i4_tab where 65530 = a; +select * from i4_tab where 2147483647 = a; + +explain select * from i4_tab where 65530 = a; +explain select * from i4_tab where a = 2147483647; + + +create table bo_tab(a bool) distribute by modulo(a); +insert into bo_tab values(true); +insert into bo_tab values(false); +select * from bo_tab order by a; + +select * from bo_tab where a = true; +select * from bo_tab where a = false; + + +create table bpc_tab(a char(35)) distribute by modulo(a); +insert into bpc_tab values('Hello World'); +insert into bpc_tab values('The quick brown fox'); +select * from bpc_tab order by a; + +select * from bpc_tab where a = 'Hello World'; +select * from bpc_tab where a = 'The quick brown fox'; + + +create table byta_tab(a bytea) distribute by modulo(a); +insert into byta_tab values(E'\\000\\001\\002\\003\\004\\005\\006\\007\\010'); +insert into byta_tab values(E'\\010\\011\\012\\013\\014\\015\\016\\017\\020'); +select * from byta_tab order by a; + +select * from byta_tab where a = E'\\000\\001\\002\\003\\004\\005\\006\\007\\010'; +select * from byta_tab where a = E'\\010\\011\\012\\013\\014\\015\\016\\017\\020'; + +create table tim_tab(a time) distribute by modulo(a); +insert into tim_tab values('00:01:02.03'); +insert into tim_tab values('23:59:59.99'); +select * from tim_tab order by a; + +delete from tim_tab where a = '00:01:02.03'; +delete from tim_tab where a = '23:59:59.99'; + + + +create table timtz_tab(a time with time zone) distribute by modulo(a); +insert into timtz_tab values('00:01:02.03 PST'); +insert into timtz_tab values('23:59:59.99 PST'); +select * from timtz_tab order by a; + +select * from timtz_tab where a = '00:01:02.03 PST'; +select * from timtz_tab where a = '23:59:59.99 PST'; + + + +create table ts_tab(a timestamp) distribute by modulo(a); +insert into ts_tab values('May 10, 2011 00:01:02.03'); +insert into ts_tab values('August 14, 2001 23:59:59.99'); +select * from ts_tab order by a; + +select * from ts_tab where a = 'May 10, 2011 00:01:02.03'; +select * from ts_tab where a = 'August 14, 2001 23:59:59.99'; + + +create table in_tab(a interval) distribute by modulo(a); +insert into in_tab values('1 day 12 hours 59 min 10 sec'); +insert into in_tab values('0 day 4 hours 32 min 23 sec'); +select * from in_tab order by a; + +select * from in_tab where a = '1 day 12 hours 59 min 10 sec'; +select * from in_tab where a = '0 day 4 hours 32 min 23 sec'; + + + +create table cash_tab(a money) distribute by modulo(a); +insert into cash_tab values('231.54'); +insert into cash_tab values('14011.50'); +select * from cash_tab order by a; + +select * from cash_tab where a = '231.54'; +select * from cash_tab where a = '14011.50'; + + +create table atim_tab(a abstime) distribute by modulo(a); +insert into atim_tab values(abstime('May 10, 2011 00:01:02.03')); +insert into atim_tab values(abstime('Jun 23, 2001 23:59:59.99')); +select * from atim_tab order by a; + +select * from atim_tab where a = abstime('May 10, 2011 00:01:02.03'); +select * from atim_tab where a = abstime('Jun 23, 2001 23:59:59.99'); + + +create table rtim_tab(a reltime) distribute by modulo(a); +insert into rtim_tab values(reltime('1 day 12 hours 59 min 10 sec')); +insert into rtim_tab values(reltime('0 day 5 hours 32 min 23 sec')); +select * from rtim_tab order by a; + +select * from rtim_tab where a = reltime('1 day 12 hours 59 min 10 sec'); +select * from rtim_tab where a = reltime('0 day 5 hours 32 min 23 sec'); + + + + +create table date_tab(a date) distribute by modulo(a); +insert into date_tab values('May 10, 2011'); +insert into date_tab values('August 23, 2001'); +select * from date_tab order by a; + +select * from date_tab where a = 'May 10, 2011'; +select * from date_tab where a = 'August 23, 2001'; + + +create table tstz_tab(a timestamp with time zone) distribute by modulo(a); +insert into tstz_tab values('May 10, 2011 00:01:02.03 PST'); +insert into tstz_tab values('Jun 23, 2001 23:59:59.99 PST'); +select * from tstz_tab order by a; + +select * from tstz_tab where a = 'May 10, 2011 00:01:02.03 PST'; +select * from tstz_tab where a = 'Jun 23, 2001 23:59:59.99 PST'; + + + +create table tstz_tab_h(a timestamp with time zone) distribute by hash(a); +insert into tstz_tab_h values('May 10, 2011 00:01:02.03 PST'); +insert into tstz_tab_h values('Jun 23, 2001 23:59:59.99 PST'); +select * from tstz_tab_h order by a; + +select * from tstz_tab_h where a = 'May 10, 2011 00:01:02.03 PST'; +select * from tstz_tab_h where a = 'Jun 23, 2001 23:59:59.99 PST'; + |