0006-Property-collation-and-edge-vertex-link-sup-20241122.patch
text/x-patch
Filename: 0006-Property-collation-and-edge-vertex-link-sup-20241122.patch
Type: text/x-patch
Part: 5
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch 0006
Subject: Property collation and edge-vertex link support
| File | + | − |
|---|---|---|
| src/backend/catalog/information_schema.sql | 5 | 3 |
| src/backend/commands/propgraphcmds.c | 123 | 13 |
| src/backend/nodes/nodeFuncs.c | 2 | 3 |
| src/backend/parser/parse_collate.c | 1 | 7 |
| src/backend/parser/parse_graphtable.c | 11 | 5 |
| src/backend/rewrite/rewriteGraphTable.c | 55 | 16 |
| src/include/catalog/pg_propgraph_property.h | 6 | 0 |
| src/include/nodes/primnodes.h | 2 | 0 |
| src/test/regress/expected/create_property_graph.out | 221 | 40 |
| src/test/regress/expected/graph_table.out | 98 | 13 |
| src/test/regress/expected/oidjoins.out | 1 | 0 |
| src/test/regress/sql/create_property_graph.sql | 77 | 0 |
| src/test/regress/sql/graph_table.sql | 31 | 10 |
From dd3cf8d4ad1ca3058684c957e348eb4134860a54 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Mon, 28 Oct 2024 16:36:27 +0530
Subject: [PATCH 7/8] Property collation and edge-vertex link support
The commit has following changes.
1. collation of a property
--------------------------
The values with differing collations can not be collated. This means
that we will not able to compare, sort or order values of properties
with the same name, if they have different collations in different
elements or labels. This restricts that property's usage in graph table
query. Hence the collation of all the properties with the same name
needs to be the same. Enforce this.
Often the collation of a property reference is required before
graph_table is rewritten. For example, assign_query_collation() is
called during transformSelectStmt() which happens before calling
rewriteGraphTable(). Hence the collation of a property needs to be
accessible before any property reference is accessed. Hence the
collation of a property is stored in pg_propgraph_property.
Note: This is not explicitly specified in Section 9.15, "Consistency check of a
tabular property graph descriptor", syntax rule 4.c.iii.2 of SQL/PGQ standard.
2. collation of source and destination keys of an edge
------------------------------------------------------
If collations of edge key and the corresponding referenced key differ, an edge
may end up being adjacent to undesirable vertex. Prohibit such a case.
This is different from how foreign keys, in which case the collation of
referenced key is used and that of referencing key is ignored.
Note: This is not specified in Section 9.14, "Creation of an edge table
descriptor".
3. WIP creation of edge-vertex link quals
-----------------------------------------
When creating an edge element make sure that there exists an equality
operator that can be used to match vertex and edge keys. Fail the DDL if
such an operator does not exist.
In build_edge_vertex_link_quals(), the types of keys of referenced
elements need to use the OID of referenced element's relation OID. Also
use make_op() instead of hand-crafting OpExpr.
TODO:
Similar to primary keys, we need to save the operator used for equating
keys in the catalog, use it when crafting the edge-vertex qual. To avoid
it being dropped we need to add a dependency?
4. Tests
--------
Adds tests for above items.
The collation tests, test that
a. the collation is correctly set for the columns projected by
GRAPH_TABLE clause.
b. quals corresponding to the various WHERE clause in GRAPH_TABLE have
correct collation set
c. quals corresponding to the edge-vertex links have correct collation
set.
Note: These tests combined with tests in collate.sql and other collation
specific tests indicate that we have covered all scenarios testing
collations in the context of GRAPH_TABLE. More collation specific tests
may be added as required.
Ashutosh Bapat
---
src/backend/catalog/information_schema.sql | 8 +-
src/backend/commands/propgraphcmds.c | 136 ++++++++-
src/backend/nodes/nodeFuncs.c | 5 +-
src/backend/parser/parse_collate.c | 8 +-
src/backend/parser/parse_graphtable.c | 16 +-
src/backend/rewrite/rewriteGraphTable.c | 71 +++--
src/include/catalog/pg_propgraph_property.h | 6 +
src/include/nodes/primnodes.h | 2 +
.../expected/create_property_graph.out | 261 +++++++++++++++---
src/test/regress/expected/graph_table.out | 111 +++++++-
src/test/regress/expected/oidjoins.out | 1 +
.../regress/sql/create_property_graph.sql | 77 ++++++
src/test/regress/sql/graph_table.sql | 41 ++-
13 files changed, 633 insertions(+), 110 deletions(-)
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index d6b08f99137..cc94339aa26 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -3271,9 +3271,9 @@ CREATE VIEW pg_property_data_types AS
CAST(null AS sql_identifier) AS character_set_catalog,
CAST(null AS sql_identifier) AS character_set_schema,
CAST(null AS sql_identifier) AS character_set_name,
- CAST(null AS sql_identifier) AS collation_catalog, -- FIXME
- CAST(null AS sql_identifier) AS collation_schema, -- FIXME
- CAST(null AS sql_identifier) AS collation_name, -- FIXME
+ CAST(current_database() AS sql_identifier) AS collation_catalog,
+ CAST(nc.nspname AS sql_identifier) AS collation_schema,
+ CAST(c.collname AS sql_identifier) AS collation_name,
CAST(null AS cardinal_number) AS numeric_precision,
CAST(null AS cardinal_number) AS numeric_precision_radix,
CAST(null AS cardinal_number) AS numeric_scale,
@@ -3297,6 +3297,8 @@ CREATE VIEW pg_property_data_types AS
JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON pgp.pgptypid = t.oid
LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
+ LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid))
+ ON pgp.pgpcollation = c.oid AND (nc.nspname, c.collname) <> ('pg_catalog', 'default')
WHERE pg.relkind = 'g'
AND (NOT pg_is_other_temp_schema(npg.oid))
diff --git a/src/backend/commands/propgraphcmds.c b/src/backend/commands/propgraphcmds.c
index 8430bfda7b7..090c4209dc2 100644
--- a/src/backend/commands/propgraphcmds.c
+++ b/src/backend/commands/propgraphcmds.c
@@ -20,6 +20,7 @@
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_class.h"
+#include "catalog/pg_collation_d.h"
#include "catalog/pg_propgraph_element.h"
#include "catalog/pg_propgraph_element_label.h"
#include "catalog/pg_propgraph_label.h"
@@ -28,6 +29,8 @@
#include "commands/propgraphcmds.h"
#include "commands/tablecmds.h"
#include "nodes/nodeFuncs.h"
+#include "parser/parse_collate.h"
+#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
#include "parser/parse_target.h"
#include "utils/array.h"
@@ -69,7 +72,7 @@ static void propgraph_edge_get_ref_keys(ParseState *pstate, const List *keycols,
Relation edge_rel, Relation ref_rel,
const char *aliasname, int location, const char *type,
ArrayType **outkey, ArrayType **outref);
-static ArrayType *array_from_column_list(ParseState *pstate, const List *colnames, int location, Relation element_rel);
+static AttrNumber *array_from_column_list(ParseState *pstate, const List *colnames, int location, Relation element_rel);
static ArrayType *array_from_attnums(int numattrs, const AttrNumber *attnums);
static Oid insert_element_record(ObjectAddress pgaddress, struct element_info *einfo);
static Oid insert_label_record(Oid graphid, Oid peoid, const char *label);
@@ -336,7 +339,8 @@ propgraph_element_get_key(ParseState *pstate, const List *key_clause, Relation e
}
else
{
- a = array_from_column_list(pstate, key_clause, location, element_rel);
+ a = array_from_attnums(list_length(key_clause),
+ array_from_column_list(pstate, key_clause, location, element_rel));
}
return a;
@@ -362,6 +366,11 @@ propgraph_edge_get_ref_keys(ParseState *pstate, const List *keycols, const List
const char *aliasname, int location, const char *type,
ArrayType **outkey, ArrayType **outref)
{
+ int nkeys;
+ AttrNumber *keyattnums;
+ AttrNumber *refattnums;
+ int i;
+
Assert((keycols && refcols) || (!keycols && !refcols));
if (keycols)
@@ -372,8 +381,9 @@ propgraph_edge_get_ref_keys(ParseState *pstate, const List *keycols, const List
errmsg("mismatching number of columns in %s vertex definition of edge \"%s\"", type, aliasname),
parser_errposition(pstate, location));
- *outkey = array_from_column_list(pstate, keycols, location, edge_rel);
- *outref = array_from_column_list(pstate, refcols, location, ref_rel);
+ nkeys = list_length(keycols);
+ keyattnums = array_from_column_list(pstate, keycols, location, edge_rel);
+ refattnums = array_from_column_list(pstate, refcols, location, ref_rel);
}
else
{
@@ -404,25 +414,82 @@ propgraph_edge_get_ref_keys(ParseState *pstate, const List *keycols, const List
Assert(fk);
- *outkey = array_from_attnums(fk->nkeys, fk->conkey);
- *outref = array_from_attnums(fk->nkeys, fk->confkey);
+ nkeys = fk->nkeys;
+ keyattnums = fk->conkey;
+ refattnums = fk->confkey;
+ }
+
+ for (i = 0; i < nkeys; i++)
+ {
+ Oid keytype;
+ int32 keytypmod;
+ Oid keycoll;
+ Oid reftype;
+ int32 reftypmod;
+ Oid refcoll;
+ Operator qualop;
+ List *op = list_make2(makeString("pg_catalog"), makeString("="));
+
+ get_atttypetypmodcoll(RelationGetRelid(edge_rel), keyattnums[i], &keytype, &keytypmod, &keycoll);
+ get_atttypetypmodcoll(RelationGetRelid(ref_rel), refattnums[i], &reftype, &reftypmod, &refcoll);
+
+ /*
+ * If an equality operator does not exist for data types of edge and
+ * vertex keys, we can not construct a qual to link edge to its
+ * adjancent vertexes. Prohibit such case.
+ *
+ * XXX: this won't prohibit cases where all values of one type are not
+ * coercible to the other type. To fix that case, we will need type
+ * compatibility check similar to foreign key constraints.
+ *
+ * XXX: Also, even if this check passes, the required operator may
+ * still be dropped later. How to avoid that case?
+ */
+ qualop = oper(NULL, op, keytype, reftype, true, -1);
+ if (!HeapTupleIsValid(qualop))
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("no operator exists for %s key comparison of edge \"%s\": %s",
+ type, aliasname,
+ op_signature_string(op, keytype, reftype)),
+ parser_errposition(pstate, location));
+ else
+ ReleaseSysCache(qualop);
+
+ /*
+ * If collations of key attribute and referenced attribute are
+ * different, an edge may end up being adjacent to undesired vertexes.
+ * Prohibit such a case.
+ */
+ if (keycoll != refcoll &&
+ keycoll != DEFAULT_COLLATION_OID && refcoll != DEFAULT_COLLATION_OID &&
+ OidIsValid(keycoll) && OidIsValid(refcoll))
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("collation mismatch in %s key of edge \"%s\": %s vs. %s",
+ type, aliasname,
+ get_collation_name(keycoll), get_collation_name(refcoll)),
+ parser_errposition(pstate, location));
}
+
+ *outkey = array_from_attnums(nkeys, keyattnums);
+ *outref = array_from_attnums(nkeys, refattnums);
}
/*
* Convert list of column names in the specified relation into an array of
* column numbers.
*/
-static ArrayType *
+static AttrNumber *
array_from_column_list(ParseState *pstate, const List *colnames, int location, Relation element_rel)
{
int numattrs;
- Datum *attnumsd;
+ AttrNumber *attnums;
int i;
ListCell *lc;
numattrs = list_length(colnames);
- attnumsd = palloc_array(Datum, numattrs);
+ attnums = palloc_array(AttrNumber, numattrs);
i = 0;
foreach(lc, colnames)
@@ -438,14 +505,14 @@ array_from_column_list(ParseState *pstate, const List *colnames, int location, R
errmsg("column \"%s\" of relation \"%s\" does not exist",
colname, get_rel_name(relid)),
parser_errposition(pstate, location)));
- attnumsd[i++] = Int16GetDatum(attnum);
+ attnums[i++] = attnum;
}
for (int j = 0; j < numattrs; j++)
{
for (int k = j + 1; k < numattrs; k++)
{
- if (DatumGetInt16(attnumsd[j]) == DatumGetInt16(attnumsd[k]))
+ if (attnums[j] == attnums[k])
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("graph key columns list must not contain duplicates"),
@@ -453,7 +520,7 @@ array_from_column_list(ParseState *pstate, const List *colnames, int location, R
}
}
- return construct_array_builtin(attnumsd, numattrs, INT2OID);
+ return attnums;
}
static ArrayType *
@@ -768,6 +835,7 @@ insert_property_records(Oid graphid, Oid ellabeloid, Oid pgerelid, const PropGra
table_close(rel, NoLock);
tp = transformTargetList(pstate, proplist, EXPR_KIND_PROPGRAPH_PROPERTY);
+ assign_expr_collations(pstate, (Node *) tp);
foreach(lc, tp)
{
@@ -786,9 +854,15 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
{
Oid propoid;
Oid exprtypid;
+ int32 exprtypmod;
+ Oid exprcollation;
Oid proptypid;
+ int32 proptypmod;
+ Oid propcollation;
exprtypid = exprType((const Node *) expr);
+ exprcollation = exprCollation((const Node *) expr);
+ exprtypmod = exprTypmod((const Node *) expr);
/*
* Insert into pg_propgraph_property if not already existing.
@@ -805,6 +879,8 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
ObjectAddress referenced;
proptypid = exprtypid;
+ proptypmod = exprtypmod;
+ propcollation = exprcollation;
rel = table_open(PropgraphPropertyRelationId, RowExclusiveLock);
@@ -814,6 +890,8 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
namestrcpy(&propnamedata, propname);
values[Anum_pg_propgraph_property_pgpname - 1] = NameGetDatum(&propnamedata);
values[Anum_pg_propgraph_property_pgptypid - 1] = ObjectIdGetDatum(proptypid);
+ values[Anum_pg_propgraph_property_pgptypmod - 1] = Int32GetDatum(proptypmod);
+ values[Anum_pg_propgraph_property_pgpcollation - 1] = ObjectIdGetDatum(propcollation);
tup = heap_form_tuple(RelationGetDescr(rel), values, nulls);
CatalogTupleInsert(rel, tup);
@@ -830,7 +908,13 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
}
else
{
- proptypid = GetSysCacheOid1(PROPGRAPHPROPOID, Anum_pg_propgraph_property_pgptypid, ObjectIdGetDatum(propoid));
+ HeapTuple pgptup = SearchSysCache1(PROPGRAPHPROPOID, ObjectIdGetDatum(propoid));
+ Form_pg_propgraph_property pgpform = (Form_pg_propgraph_property) GETSTRUCT(pgptup);
+
+ proptypid = pgpform->pgptypid;
+ proptypmod = pgpform->pgptypmod;
+ propcollation = pgpform->pgpcollation;
+ ReleaseSysCache(pgptup);
}
/*
@@ -847,6 +931,32 @@ insert_property_record(Oid graphid, Oid ellabeloid, Oid pgerelid, const char *pr
errdetail("In a property graph, a property of the same name has to have the same data type in each label."));
}
+ /* Similarly for collation */
+ if (propcollation != exprcollation)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("property \"%s\" collation mismatch: %s vs. %s",
+ propname, get_collation_name(propcollation), get_collation_name(exprcollation)),
+ errdetail("In a property graph, a property of the same name has to have the same collation in each label."));
+ }
+
+ /*
+ * And typmod. It does not seem to be necessary to enforce typmod
+ * consistency across properties with the same name. But when properties
+ * with the same name have different typmods, it is not clear which one
+ * should be used as the typmod of the graph property when typmod of a
+ * property is requested before fetching any of the property expressions.
+ */
+ if (proptypmod != exprtypmod)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("property \"%s\" data type modifier mismatch: %d vs. %d",
+ propname, proptypmod, exprtypmod),
+ errdetail("In a property graph, a property of the same name has to have the same type modifier in each label."));
+ }
+
/*
* Insert into pg_propgraph_label_property
*/
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index a26cfe09f8c..b38c8921603 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -535,8 +535,7 @@ exprTypmod(const Node *expr)
case T_PlaceHolderVar:
return exprTypmod((Node *) ((const PlaceHolderVar *) expr)->phexpr);
case T_GraphPropertyRef:
- /* TODO */
- return -1;
+ return ((const GraphPropertyRef *) expr)->typmod;
default:
break;
}
@@ -1057,7 +1056,7 @@ exprCollation(const Node *expr)
coll = exprCollation((Node *) ((const PlaceHolderVar *) expr)->phexpr);
break;
case T_GraphPropertyRef:
- coll = DEFAULT_COLLATION_OID; /* FIXME */
+ coll = ((const GraphPropertyRef *) expr)->collation;
break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
diff --git a/src/backend/parser/parse_collate.c b/src/backend/parser/parse_collate.c
index 33a1f3f2413..f91cece808f 100644
--- a/src/backend/parser/parse_collate.c
+++ b/src/backend/parser/parse_collate.c
@@ -546,6 +546,7 @@ assign_collations_walker(Node *node, assign_collations_context *context)
case T_CaseTestExpr:
case T_SetToDefault:
case T_CurrentOfExpr:
+ case T_GraphPropertyRef:
/*
* General case for childless expression nodes. These should
@@ -571,13 +572,6 @@ assign_collations_walker(Node *node, assign_collations_context *context)
location = exprLocation(node);
break;
- case T_GraphPropertyRef:
- /* FIXME */
- collation = DEFAULT_COLLATION_OID;
- strength = COLLATE_IMPLICIT;
- location = -1;
- break;
-
default:
{
/*
diff --git a/src/backend/parser/parse_graphtable.c b/src/backend/parser/parse_graphtable.c
index b088306b5b3..a8769a67b6a 100644
--- a/src/backend/parser/parse_graphtable.c
+++ b/src/backend/parser/parse_graphtable.c
@@ -56,18 +56,24 @@ transformGraphTablePropertyRef(ParseState *pstate, ColumnRef *cref)
if (list_member(gpstate->variables, field1))
{
GraphPropertyRef *gpr = makeNode(GraphPropertyRef);
- Oid propid;
+ HeapTuple pgptup;
+ Form_pg_propgraph_property pgpform;
- propid = GetSysCacheOid2(PROPGRAPHPROPNAME, Anum_pg_propgraph_property_oid, ObjectIdGetDatum(gpstate->graphid), CStringGetDatum(propname));
- if (!propid)
+ pgptup = SearchSysCache2(PROPGRAPHPROPNAME, ObjectIdGetDatum(gpstate->graphid), CStringGetDatum(propname));
+ if (!HeapTupleIsValid(pgptup))
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
errmsg("property \"%s\" does not exist", propname));
+ pgpform = (Form_pg_propgraph_property) GETSTRUCT(pgptup);
gpr->location = cref->location;
gpr->elvarname = elvarname;
- gpr->propid = propid;
- gpr->typeId = GetSysCacheOid1(PROPGRAPHPROPOID, Anum_pg_propgraph_property_pgptypid, ObjectIdGetDatum(propid));
+ gpr->propid = pgpform->oid;
+ gpr->typeId = pgpform->pgptypid;
+ gpr->typmod = pgpform->pgptypmod;
+ gpr->collation = pgpform->pgpcollation;
+
+ ReleaseSysCache(pgptup);
return (Node *) gpr;
}
diff --git a/src/backend/rewrite/rewriteGraphTable.c b/src/backend/rewrite/rewriteGraphTable.c
index 9c14fa3b1b7..914b829ae47 100644
--- a/src/backend/rewrite/rewriteGraphTable.c
+++ b/src/backend/rewrite/rewriteGraphTable.c
@@ -23,7 +23,9 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
#include "parser/analyze.h"
+#include "parser/parse_collate.h"
#include "parser/parse_node.h"
+#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "parser/parse_relation.h"
@@ -83,7 +85,7 @@ struct path_element
};
static Node *replace_property_refs(Oid propgraphid, Node *node, const List *mappings);
-static List *build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum);
+static List *build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, Oid refid, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum);
static List *generate_queries_for_path_pattern(RangeTblEntry *rte, List *element_patterns);
static Query *generate_query_for_graph_path(RangeTblEntry *rte, List *path);
static Node *generate_setop_from_pathqueries(List *pathqueries, List **rtable, List **targetlist);
@@ -373,6 +375,23 @@ generate_queries_for_path_pattern_recurse(RangeTblEntry *rte, List *pathqueries,
/*
* Construct a query representing given graph path.
*
+ * The query contains:
+ *
+ * 1. targetlist corresponding the COLUMNS clause of GRAPH_TABLE clause
+ *
+ * 2. quals corresponding to the WHERE clause of individual elements, WHERE
+ * clause in GRAPH_TABLE clause and quals representing edge-vertex links.
+ *
+ * 3. fromlist containing all elements in the path
+ *
+ * The collations of property expressions are obtained from the catalog and
+ * substituted in place of a property reference. The collations of expressions
+ * in COLUMNS and WHERE clauses are assigned before rewriting the graph table.
+ * The collations of the edge-vertex link quals are assigned when crafting those
+ * quals. Thus everything in the query that requires collation assignment has
+ * been taken care of already. So no collation assignment is required in this
+ * function.
+ *
* More details in the prologue of generate_queries_for_path_pattern().
*/
static Query *
@@ -552,6 +571,12 @@ generate_query_for_empty_path_pattern(RangeTblEntry *rte)
/*
* Construct a query which is UNION of given path queries.
*
+ * The UNION query derives collations of its targetlist entries from the
+ * corresponding targetlist entries of the path queries and projects it. The
+ * targetlists of path queries being UNION'ed already have collations assigned.
+ * The same collations are used for targetlist of UNION query. Thus there is no
+ * separate collation assignment required in this function.
+ *
* The function destroys given pathqueries list while constructing
* SetOperationStmt recrursively. Hence the function always returns with
* `pathqueries` set to NIL.
@@ -747,9 +772,11 @@ create_gpe_for_element(struct path_factor *pf, Oid elemoid)
* each time.
*/
pe->src_quals = build_edge_vertex_link_quals(eletup, pf->factorpos + 1, pf->src_pf->factorpos + 1,
+ pe->srcvertexid,
Anum_pg_propgraph_element_pgesrckey,
Anum_pg_propgraph_element_pgesrcref);
pe->dest_quals = build_edge_vertex_link_quals(eletup, pf->factorpos + 1, pf->dest_pf->factorpos + 1,
+ pe->destvertexid,
Anum_pg_propgraph_element_pgedestkey,
Anum_pg_propgraph_element_pgedestref);
}
@@ -1125,7 +1152,7 @@ replace_property_refs(Oid propgraphid, Node *node, const List *mappings)
* Build join qualification expressions between edge and vertex tables.
*/
static List *
-build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum)
+build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, Oid refid, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum)
{
List *quals = NIL;
Form_pg_propgraph_element pgeform;
@@ -1134,6 +1161,11 @@ build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, AttrNum
*d2;
int n1,
n2;
+ ParseState *pstate = make_parsestate(NULL);
+ HeapTuple reftup = SearchSysCache1(PROPGRAPHELOID, ObjectIdGetDatum(refid));
+ Oid refrelid = ((Form_pg_propgraph_element) GETSTRUCT(reftup))->pgerelid;
+
+ ReleaseSysCache(reftup);
pgeform = (Form_pg_propgraph_element) GETSTRUCT(edgetup);
@@ -1150,26 +1182,33 @@ build_edge_vertex_link_quals(HeapTuple edgetup, int edgerti, int refrti, AttrNum
{
AttrNumber keyattn = DatumGetInt16(d1[i]);
AttrNumber refattn = DatumGetInt16(d2[i]);
+ Var *keyvar;
+ Var *refvar;
Oid atttypid;
- TypeCacheEntry *typentry;
- OpExpr *op;
+ int32 atttypmod;
+ Oid attcoll;
+ Expr *linkqual;
+
+ get_atttypetypmodcoll(pgeform->pgerelid, keyattn, &atttypid, &atttypmod, &attcoll);
+ keyvar = makeVar(edgerti, keyattn, atttypid, atttypmod, attcoll, 0);
+ get_atttypetypmodcoll(refrelid, refattn, &atttypid, &atttypmod, &attcoll);
+ refvar = makeVar(refrti, refattn, atttypid, atttypmod, attcoll, 0);
/*
- * TODO: Assumes types the same on both sides; no collations yet. Some
- * of this could probably be shared with foreign key triggers.
+ * We do not allow keys with conflicting collations (see
+ * propgraph_edge_get_ref_keys()). Hence the qual constructed here
+ * should not cause assign_expr_collations() to throw an error. But in
+ * case we change that in future we should coerce edge key collation
+ * to that of vertex key collation similar to how foreign key
+ * constraints are crafted.
*/
- atttypid = get_atttype(pgeform->pgerelid, keyattn);
- typentry = lookup_type_cache(atttypid, TYPECACHE_EQ_OPR);
-
- op = makeNode(OpExpr);
- op->location = -1;
- op->opno = typentry->eq_opr;
- op->opresulttype = BOOLOID;
- op->args = list_make2(makeVar(edgerti, keyattn, atttypid, -1, 0, 0),
- makeVar(refrti, refattn, atttypid, -1, 0, 0));
- quals = lappend(quals, op);
+ linkqual = make_op(pstate, list_make2(makeString("pg_catalog"), makeString("=")),
+ (Node *) keyvar, (Node *) refvar, NULL, -1);
+ quals = lappend(quals, linkqual);
}
+ assign_expr_collations(pstate, (Node *) quals);
+
return quals;
}
diff --git a/src/include/catalog/pg_propgraph_property.h b/src/include/catalog/pg_propgraph_property.h
index b8921ace30c..240b34f0390 100644
--- a/src/include/catalog/pg_propgraph_property.h
+++ b/src/include/catalog/pg_propgraph_property.h
@@ -36,6 +36,12 @@ CATALOG(pg_propgraph_property,8306,PropgraphPropertyRelationId)
/* data type of the property */
Oid pgptypid BKI_LOOKUP_OPT(pg_type);
+
+ /* typemod of the property */
+ int32 pgptypmod;
+
+ /* collation of the property */
+ Oid pgpcollation BKI_LOOKUP_OPT(pg_collation);
} FormData_pg_propgraph_property;
/* ----------------
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index a246bd9dfd6..5a485ee471f 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -2147,6 +2147,8 @@ typedef struct GraphPropertyRef
const char *elvarname;
Oid propid;
Oid typeId;
+ int32 typmod;
+ Oid collation;
ParseLoc location;
} GraphPropertyRef;
diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out
index 43316fbc029..d9aa2e79cc7 100644
--- a/src/test/regress/expected/create_property_graph.out
+++ b/src/test/regress/expected/create_property_graph.out
@@ -187,6 +187,94 @@ GRANT SELECT ON PROPERTY GRAPH g1 TO regress_graph_user2;
GRANT UPDATE ON PROPERTY GRAPH g1 TO regress_graph_user2; -- fail
ERROR: invalid privilege type UPDATE for property graph
RESET ROLE;
+-- collation
+CREATE TABLE tc1 (a int, b text);
+CREATE TABLE tc2 (a int, b text);
+CREATE TABLE tc3 (a int, b text COLLATE "C");
+CREATE TABLE ec1 (ek1 int, ek2 int, eb text);
+CREATE TABLE ec2 (ek1 int, ek2 int, eb text COLLATE "POSIX");
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a), tc3 KEY (a)); -- fail
+ERROR: property "b" collation mismatch: default vs. C
+DETAIL: In a property graph, a property of the same name has to have the same collation in each label.
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a),
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)); -- fail
+ERROR: property "eb" collation mismatch: default vs. POSIX
+DETAIL: In a property graph, a property of the same name has to have the same collation in each label.
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a) DEFAULT LABEL PROPERTIES (a), tc3 KEY (b))
+ EDGE TABLES (
+ ec2 KEY (ek1, eb)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (eb) REFERENCES tc3 (b)); -- fail
+ERROR: collation mismatch in DESTINATION key of edge "ec2": POSIX vs. C
+LINE 4: ec2 KEY (ek1, eb)
+ ^
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a));
+ALTER PROPERTY GRAPH gc1 ADD VERTEX TABLES (tc3 KEY (a)); -- fail
+ERROR: property "b" collation mismatch: default vs. C
+DETAIL: In a property graph, a property of the same name has to have the same collation in each label.
+ALTER PROPERTY GRAPH gc1 ADD EDGE TABLES (
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)); -- fail
+ERROR: property "eb" collation mismatch: default vs. POSIX
+DETAIL: In a property graph, a property of the same name has to have the same collation in each label.
+ALTER PROPERTY GRAPH gc1
+ ADD VERTEX TABLES (
+ tc3 KEY (a) DEFAULT LABEL PROPERTIES (a, b COLLATE pg_catalog.DEFAULT AS b));
+ALTER PROPERTY GRAPH gc1 ADD EDGE TABLES (
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb COLLATE pg_catalog.DEFAULT AS eb));
+DROP PROPERTY GRAPH gc1;
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (
+ tc1 KEY (a) DEFAULT LABEL PROPERTIES (a, b::varchar COLLATE "C" AS b),
+ tc2 KEY (a) DEFAULT LABEL PROPERTIES (a, (b COLLATE "C")::varchar AS b),
+ tc3 KEY (a) DEFAULT LABEL PROPERTIES (a, b::varchar AS b))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb),
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb COLLATE pg_catalog.DEFAULT AS eb));
+-- type incosistency check
+CREATE TABLE v1 (a int primary key, b text);
+CREATE TABLE e(k1 text, k2 text, c text);
+CREATE TABLE v2 (m text, n text);
+CREATE PROPERTY GRAPH gt
+ VERTEX TABLES (v1 KEY (a), v2 KEY (m))
+ EDGE TABLES (
+ e KEY (k1, k2)
+ SOURCE KEY (k1) REFERENCES v1(a)
+ DESTINATION KEY (k2) REFERENCES v2(m)); -- fail
+ERROR: no operator exists for SOURCE key comparison of edge "e": text pg_catalog.= integer
+LINE 4: e KEY (k1, k2)
+ ^
+ALTER TABLE e DROP COLUMN k1, ADD COLUMN k1 bigint primary key;
+CREATE PROPERTY GRAPH gt
+ VERTEX TABLES (v1 KEY (a), v2 KEY (m))
+ EDGE TABLES (
+ e KEY (k1, k2)
+ SOURCE KEY (k1) REFERENCES v1(a)
+ DESTINATION KEY (k2) REFERENCES v2(m));
-- information schema
SELECT * FROM information_schema.property_graphs ORDER BY property_graph_name;
property_graph_catalog | property_graph_schema | property_graph_name
@@ -196,7 +284,9 @@ SELECT * FROM information_schema.property_graphs ORDER BY property_graph_name;
regression | create_property_graph_tests | g3
regression | create_property_graph_tests | g4
regression | create_property_graph_tests | g5
-(5 rows)
+ regression | create_property_graph_tests | gc1
+ regression | create_property_graph_tests | gt
+(7 rows)
SELECT * FROM information_schema.pg_element_tables ORDER BY property_graph_name, element_table_alias;
property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | element_table_kind | table_catalog | table_schema | table_name | element_table_definition
@@ -216,7 +306,15 @@ SELECT * FROM information_schema.pg_element_tables ORDER BY property_graph_name,
regression | create_property_graph_tests | g5 | t11 | VERTEX | regression | create_property_graph_tests | t11 |
regression | create_property_graph_tests | g5 | t12 | VERTEX | regression | create_property_graph_tests | t12 |
regression | create_property_graph_tests | g5 | t13 | EDGE | regression | create_property_graph_tests | t13 |
-(15 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | EDGE | regression | create_property_graph_tests | ec1 |
+ regression | create_property_graph_tests | gc1 | ec2 | EDGE | regression | create_property_graph_tests | ec2 |
+ regression | create_property_graph_tests | gc1 | tc1 | VERTEX | regression | create_property_graph_tests | tc1 |
+ regression | create_property_graph_tests | gc1 | tc2 | VERTEX | regression | create_property_graph_tests | tc2 |
+ regression | create_property_graph_tests | gc1 | tc3 | VERTEX | regression | create_property_graph_tests | tc3 |
+ regression | create_property_graph_tests | gt | e | EDGE | regression | create_property_graph_tests | e |
+ regression | create_property_graph_tests | gt | v1 | VERTEX | regression | create_property_graph_tests | v1 |
+ regression | create_property_graph_tests | gt | v2 | VERTEX | regression | create_property_graph_tests | v2 |
+(23 rows)
SELECT * FROM information_schema.pg_element_table_key_columns ORDER BY property_graph_name, element_table_alias, ordinal_position;
property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | column_name | ordinal_position
@@ -240,7 +338,18 @@ SELECT * FROM information_schema.pg_element_table_key_columns ORDER BY property_
regression | create_property_graph_tests | g5 | t11 | a | 1
regression | create_property_graph_tests | g5 | t12 | b | 1
regression | create_property_graph_tests | g5 | t13 | c | 1
-(19 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | ek1 | 1
+ regression | create_property_graph_tests | gc1 | ec1 | ek2 | 2
+ regression | create_property_graph_tests | gc1 | ec2 | ek1 | 1
+ regression | create_property_graph_tests | gc1 | ec2 | ek2 | 2
+ regression | create_property_graph_tests | gc1 | tc1 | a | 1
+ regression | create_property_graph_tests | gc1 | tc2 | a | 1
+ regression | create_property_graph_tests | gc1 | tc3 | a | 1
+ regression | create_property_graph_tests | gt | e | k1 | 1
+ regression | create_property_graph_tests | gt | e | k2 | 2
+ regression | create_property_graph_tests | gt | v1 | a | 1
+ regression | create_property_graph_tests | gt | v2 | m | 1
+(30 rows)
SELECT * FROM information_schema.pg_edge_table_components ORDER BY property_graph_name, edge_table_alias, edge_end DESC, ordinal_position;
property_graph_catalog | property_graph_schema | property_graph_name | edge_table_alias | vertex_table_alias | edge_end | edge_table_column_name | vertex_table_column_name | ordinal_position
@@ -257,7 +366,13 @@ SELECT * FROM information_schema.pg_edge_table_components ORDER BY property_grap
regression | create_property_graph_tests | g4 | e2 | t3 | DESTINATION | t | y | 2
regression | create_property_graph_tests | g5 | t13 | t11 | SOURCE | e | a | 1
regression | create_property_graph_tests | g5 | t13 | t12 | DESTINATION | e | b | 1
-(12 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | tc1 | SOURCE | ek1 | a | 1
+ regression | create_property_graph_tests | gc1 | ec1 | tc2 | DESTINATION | ek2 | a | 1
+ regression | create_property_graph_tests | gc1 | ec2 | tc1 | SOURCE | ek1 | a | 1
+ regression | create_property_graph_tests | gc1 | ec2 | tc2 | DESTINATION | ek2 | a | 1
+ regression | create_property_graph_tests | gt | e | v1 | SOURCE | k1 | a | 1
+ regression | create_property_graph_tests | gt | e | v2 | DESTINATION | k2 | m | 1
+(18 rows)
SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph_name, element_table_alias, label_name;
property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | label_name
@@ -280,11 +395,19 @@ SELECT * FROM information_schema.pg_element_table_labels ORDER BY property_graph
regression | create_property_graph_tests | g5 | t11 | t11
regression | create_property_graph_tests | g5 | t12 | t12
regression | create_property_graph_tests | g5 | t13 | t13
-(18 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | ec1
+ regression | create_property_graph_tests | gc1 | ec2 | ec2
+ regression | create_property_graph_tests | gc1 | tc1 | tc1
+ regression | create_property_graph_tests | gc1 | tc2 | tc2
+ regression | create_property_graph_tests | gc1 | tc3 | tc3
+ regression | create_property_graph_tests | gt | e | e
+ regression | create_property_graph_tests | gt | v1 | v1
+ regression | create_property_graph_tests | gt | v2 | v2
+(26 rows)
SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_graph_name, element_table_alias, property_name;
- property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | property_name | property_expression
-------------------------+-----------------------------+---------------------+---------------------+---------------+---------------------
+ property_graph_catalog | property_graph_schema | property_graph_name | element_table_alias | property_name | property_expression
+------------------------+-----------------------------+---------------------+---------------------+---------------+--------------------------------------
regression | create_property_graph_tests | g2 | e1 | a | a
regression | create_property_graph_tests | g2 | e1 | i | i
regression | create_property_graph_tests | g2 | e1 | t | t
@@ -320,7 +443,26 @@ SELECT * FROM information_schema.pg_element_table_properties ORDER BY property_g
regression | create_property_graph_tests | g5 | t13 | c | c
regression | create_property_graph_tests | g5 | t13 | d | d
regression | create_property_graph_tests | g5 | t13 | e | e
-(35 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | eb | eb
+ regression | create_property_graph_tests | gc1 | ec1 | ek1 | ek1
+ regression | create_property_graph_tests | gc1 | ec1 | ek2 | ek2
+ regression | create_property_graph_tests | gc1 | ec2 | eb | (eb COLLATE "default")
+ regression | create_property_graph_tests | gc1 | ec2 | ek1 | ek1
+ regression | create_property_graph_tests | gc1 | ec2 | ek2 | ek2
+ regression | create_property_graph_tests | gc1 | tc1 | a | a
+ regression | create_property_graph_tests | gc1 | tc1 | b | ((b)::character varying COLLATE "C")
+ regression | create_property_graph_tests | gc1 | tc2 | a | a
+ regression | create_property_graph_tests | gc1 | tc2 | b | ((b)::character varying COLLATE "C")
+ regression | create_property_graph_tests | gc1 | tc3 | a | a
+ regression | create_property_graph_tests | gc1 | tc3 | b | (b)::character varying
+ regression | create_property_graph_tests | gt | e | c | c
+ regression | create_property_graph_tests | gt | e | k1 | k1
+ regression | create_property_graph_tests | gt | e | k2 | k2
+ regression | create_property_graph_tests | gt | v1 | a | a
+ regression | create_property_graph_tests | gt | v1 | b | b
+ regression | create_property_graph_tests | gt | v2 | m | m
+ regression | create_property_graph_tests | gt | v2 | n | n
+(54 rows)
SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_name, label_name, property_name;
property_graph_catalog | property_graph_schema | property_graph_name | label_name | property_name
@@ -367,7 +509,26 @@ SELECT * FROM information_schema.pg_label_properties ORDER BY property_graph_nam
regression | create_property_graph_tests | g5 | t13 | c
regression | create_property_graph_tests | g5 | t13 | d
regression | create_property_graph_tests | g5 | t13 | e
-(42 rows)
+ regression | create_property_graph_tests | gc1 | ec1 | eb
+ regression | create_property_graph_tests | gc1 | ec1 | ek1
+ regression | create_property_graph_tests | gc1 | ec1 | ek2
+ regression | create_property_graph_tests | gc1 | ec2 | eb
+ regression | create_property_graph_tests | gc1 | ec2 | ek1
+ regression | create_property_graph_tests | gc1 | ec2 | ek2
+ regression | create_property_graph_tests | gc1 | tc1 | a
+ regression | create_property_graph_tests | gc1 | tc1 | b
+ regression | create_property_graph_tests | gc1 | tc2 | a
+ regression | create_property_graph_tests | gc1 | tc2 | b
+ regression | create_property_graph_tests | gc1 | tc3 | a
+ regression | create_property_graph_tests | gc1 | tc3 | b
+ regression | create_property_graph_tests | gt | e | c
+ regression | create_property_graph_tests | gt | e | k1
+ regression | create_property_graph_tests | gt | e | k2
+ regression | create_property_graph_tests | gt | v1 | a
+ regression | create_property_graph_tests | gt | v1 | b
+ regression | create_property_graph_tests | gt | v2 | m
+ regression | create_property_graph_tests | gt | v2 | n
+(61 rows)
SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_name;
property_graph_catalog | property_graph_schema | property_graph_name | label_name
@@ -390,39 +551,59 @@ SELECT * FROM information_schema.pg_labels ORDER BY property_graph_name, label_n
regression | create_property_graph_tests | g5 | t11
regression | create_property_graph_tests | g5 | t12
regression | create_property_graph_tests | g5 | t13
-(18 rows)
+ regression | create_property_graph_tests | gc1 | ec1
+ regression | create_property_graph_tests | gc1 | ec2
+ regression | create_property_graph_tests | gc1 | tc1
+ regression | create_property_graph_tests | gc1 | tc2
+ regression | create_property_graph_tests | gc1 | tc3
+ regression | create_property_graph_tests | gt | e
+ regression | create_property_graph_tests | gt | v1
+ regression | create_property_graph_tests | gt | v2
+(26 rows)
SELECT * FROM information_schema.pg_property_data_types ORDER BY property_graph_name, property_name;
- property_graph_catalog | property_graph_schema | property_graph_name | property_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier
-------------------------+-----------------------------+---------------------+---------------+-----------+--------------------------+------------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+---------------------------+--------------------------+------------------------+---------------+--------------+------------+---------------------+----------------
- regression | create_property_graph_tests | g2 | a | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | a
- regression | create_property_graph_tests | g2 | b | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | b
- regression | create_property_graph_tests | g2 | i | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | i
- regression | create_property_graph_tests | g2 | j | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | j
- regression | create_property_graph_tests | g2 | k | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | k
- regression | create_property_graph_tests | g2 | t | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | t
- regression | create_property_graph_tests | g2 | x | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | x
- regression | create_property_graph_tests | g2 | y | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | y
- regression | create_property_graph_tests | g2 | z | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | z
- regression | create_property_graph_tests | g3 | a | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | a
- regression | create_property_graph_tests | g3 | b | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | b
- regression | create_property_graph_tests | g3 | x | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | x
- regression | create_property_graph_tests | g3 | y | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | y
- regression | create_property_graph_tests | g3 | z | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | z
- regression | create_property_graph_tests | g4 | a | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | a
- regression | create_property_graph_tests | g4 | i | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | i
- regression | create_property_graph_tests | g4 | i_j | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | i_j
- regression | create_property_graph_tests | g4 | kk | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | kk
- regression | create_property_graph_tests | g4 | t | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | t
- regression | create_property_graph_tests | g4 | x | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | x
- regression | create_property_graph_tests | g4 | yy | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | yy
- regression | create_property_graph_tests | g4 | zz | text | | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | zz
- regression | create_property_graph_tests | g5 | a | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | a
- regression | create_property_graph_tests | g5 | b | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | b
- regression | create_property_graph_tests | g5 | c | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | c
- regression | create_property_graph_tests | g5 | d | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | d
- regression | create_property_graph_tests | g5 | e | integer | | | | | | | | | | | | | | | regression | pg_catalog | int4 | | | | | e
-(27 rows)
+ property_graph_catalog | property_graph_schema | property_graph_name | property_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier
+------------------------+-----------------------------+---------------------+---------------+-------------------+--------------------------+------------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+---------------------------+--------------------------+------------------------+---------------+--------------+------------+---------------------+----------------
+ regression | create_property_graph_tests | g2 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | g2 | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b
+ regression | create_property_graph_tests | g2 | i | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i
+ regression | create_property_graph_tests | g2 | j | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | j
+ regression | create_property_graph_tests | g2 | k | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | k
+ regression | create_property_graph_tests | g2 | t | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | t
+ regression | create_property_graph_tests | g2 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x
+ regression | create_property_graph_tests | g2 | y | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | y
+ regression | create_property_graph_tests | g2 | z | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | z
+ regression | create_property_graph_tests | g3 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | g3 | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b
+ regression | create_property_graph_tests | g3 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x
+ regression | create_property_graph_tests | g3 | y | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | y
+ regression | create_property_graph_tests | g3 | z | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | z
+ regression | create_property_graph_tests | g4 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | g4 | i | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i
+ regression | create_property_graph_tests | g4 | i_j | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | i_j
+ regression | create_property_graph_tests | g4 | kk | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | kk
+ regression | create_property_graph_tests | g4 | t | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | t
+ regression | create_property_graph_tests | g4 | x | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | x
+ regression | create_property_graph_tests | g4 | yy | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | yy
+ regression | create_property_graph_tests | g4 | zz | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | zz
+ regression | create_property_graph_tests | g5 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | g5 | b | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | b
+ regression | create_property_graph_tests | g5 | c | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | c
+ regression | create_property_graph_tests | g5 | d | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | d
+ regression | create_property_graph_tests | g5 | e | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | e
+ regression | create_property_graph_tests | gc1 | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | gc1 | b | character varying | | | | | | regression | pg_catalog | C | | | | | | | regression | pg_catalog | varchar | | | | | b
+ regression | create_property_graph_tests | gc1 | eb | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | eb
+ regression | create_property_graph_tests | gc1 | ek1 | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | ek1
+ regression | create_property_graph_tests | gc1 | ek2 | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | ek2
+ regression | create_property_graph_tests | gt | a | integer | | | | | | regression | | | | | | | | | regression | pg_catalog | int4 | | | | | a
+ regression | create_property_graph_tests | gt | b | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | b
+ regression | create_property_graph_tests | gt | c | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | c
+ regression | create_property_graph_tests | gt | k1 | bigint | | | | | | regression | | | | | | | | | regression | pg_catalog | int8 | | | | | k1
+ regression | create_property_graph_tests | gt | k2 | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | k2
+ regression | create_property_graph_tests | gt | m | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | m
+ regression | create_property_graph_tests | gt | n | text | | | | | | regression | | | | | | | | | regression | pg_catalog | text | | | | | n
+(39 rows)
SELECT * FROM information_schema.pg_property_graph_privileges WHERE grantee LIKE 'regress%' ORDER BY property_graph_name;
grantor | grantee | property_graph_catalog | property_graph_schema | property_graph_name | privilege_type | is_grantable
diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out
index 3796297cb07..0a574b10f4b 100644
--- a/src/test/regress/expected/graph_table.out
+++ b/src/test/regress/expected/graph_table.out
@@ -517,7 +517,8 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a)->(b)->(a is vl1) COLUMNS (a.vname AS sel
v13 | v23 | 30 | 1030
(2 rows)
--- add an edge with same vertex as source and destination to test loops
+-- add an edge with same vertex as source and destination to test loops. Also
+-- use this for collation tests
CREATE TABLE e3_3 (src_id int,
dest_id int,
ename varchar(10),
@@ -530,8 +531,35 @@ ALTER PROPERTY GRAPH g1 ADD EDGE TABLES (
LABEL l1 PROPERTIES (ename AS elname)
);
INSERT INTO e3_3 VALUES (2003, 2003, 'e331', 10010);
+INSERT INTO e3_3 VALUES (2003, 2003, 'E331', 10010);
-- cyclic pattern with edge patterns with same variable name
-SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name ASC;
+ self | loop_name
+------+-----------
+ v33 | e331
+ v33 | E331
+(2 rows)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name COLLATE "C" ASC;
+ self | loop_name
+------+-----------
+ v33 | E331
+ v33 | e331
+(2 rows)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b IS el2 WHERE b.ename > 'E331' COLLATE "C"]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
+ self | loop_name
+------+-----------
+ v33 | e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.ename > 'E331' COLLATE "C" COLUMNS (a.vname AS self, b.ename AS loop_name));
+ self | loop_name
+------+-----------
+ v33 | e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) WHERE loop_name > 'E331' COLLATE "C";
self | loop_name
------+-----------
v33 | e331
@@ -541,40 +569,89 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(c)-[b]->(d) COLUMNS (a.vname AS an
ERROR: An edge can not connect more than two vertexes even in a cyclic pattern.
-- property graph with some of the elements, labels and properties same as the
-- previous one. Test whether components from the specified property graph are
--- used.
+-- used. Also use this for collation tests
create property graph g2
vertex tables (
v1
- label l1 properties ('g2.' || vname as elname),
+ label l1 properties ('g2.' || vname COLLATE "C" as elname),
v2 key (id1, id2)
- label l1 properties ('g2.' || vname as elname),
+ label l1 properties ('g2.' || vname COLLATE "C" as elname),
v3
- label l1 properties ('g2.' || vname as elname)
+ label l1 properties ('g2.' || vname COLLATE "C" as elname)
)
edge tables (
e1_2 key (id_1, id_2_1, id_2_2)
source key (id_1) references v1 (id)
destination key (id_2_1, id_2_2) references v2 (id1, id2)
- label l1 properties ('g2.' || ename as elname),
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
e1_3
source key (id_1) references v1 (id)
destination key (id_3) references v3 (id)
- label l1 properties ('g2.' || ename as elname),
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
e2_3 key (id_2_1, id_2_2, id_3)
source key (id_2_1, id_2_2) references v2 (id1, id2)
destination key (id_3) references v3 (id)
- label l1 properties ('g2.' || ename as elname)
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
+ e3_3 KEY (src_id, dest_id)
+ SOURCE KEY (src_id) REFERENCES v3 (id)
+ DESTINATION KEY (src_id) REFERENCES v3 (id)
+ LABEL l1 PROPERTIES ('g2.' || ename COLLATE "C" as elname)
);
-select sn, cn, dn from graph_table (g2 match (src : l1)-[conn : l1]->(dest : l1) columns (src.elname as sn, conn.elname as cn, dest.elname as dn));
+select sn, cn, dn from graph_table (g2 match (src : l1)-[conn : l1]->(dest : l1) columns (src.elname as sn, conn.elname as cn, dest.elname as dn)) ORDER BY 1, 2, 3;
sn | cn | dn
--------+---------+--------
- g2.v12 | g2.e122 | g2.v21
g2.v11 | g2.e121 | g2.v22
- g2.v13 | g2.e123 | g2.v23
g2.v11 | g2.e131 | g2.v33
g2.v11 | g2.e132 | g2.v31
+ g2.v12 | g2.e122 | g2.v21
+ g2.v13 | g2.e123 | g2.v23
g2.v22 | g2.e231 | g2.v32
-(6 rows)
+ g2.v33 | g2.E331 | g2.v33
+ g2.v33 | g2.e331 | g2.v33
+(8 rows)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname COLLATE pg_catalog."default" AS loop_name)) ORDER BY loop_name ASC;
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.e331
+ g2.v33 | g2.E331
+(2 rows)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b WHERE b.elname > 'g2.E331']->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name));
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.elname > 'g2.E331' COLUMNS (a.elname AS self, b.elname AS loop_name));
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name)) WHERE loop_name > 'g2.E331';
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.e331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b WHERE b.elname > 'g2.e331' COLLATE pg_catalog."default"]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name));
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.E331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.elname > 'g2.e331' COLLATE pg_catalog."default" COLUMNS (a.elname AS self, b.elname AS loop_name));
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.E331
+(1 row)
+
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name)) WHERE loop_name > 'g2.e331' COLLATE pg_catalog."default";
+ self | loop_name
+--------+-----------
+ g2.v33 | g2.E331
+(1 row)
CREATE VIEW customers_us AS SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name));
SELECT pg_get_viewdef('customers_us'::regclass);
@@ -671,6 +748,14 @@ SELECT *
1 | customer1 | US | redacted1
(1 row)
+-- graph table in a subquery
+SELECT * FROM customers co WHERE co.customer_id =
+ (SELECT customer_id FROM GRAPH_TABLE (myshop2 MATCH (cg IS customers WHERE cg.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (cg.customer_id)));
+ customer_id | name | address
+-------------+-----------+---------
+ 1 | customer1 | US
+(1 row)
+
-- query within graph table
SELECT sname, dname
FROM GRAPH_TABLE (g1 MATCH (src)->(dest)
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index 3b41fc7ba04..9ad17275f24 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -277,3 +277,4 @@ NOTICE: checking pg_propgraph_label_property {plppropid} => pg_propgraph_proper
NOTICE: checking pg_propgraph_label_property {plpellabelid} => pg_propgraph_element_label {oid}
NOTICE: checking pg_propgraph_property {pgppgid} => pg_class {oid}
NOTICE: checking pg_propgraph_property {pgptypid} => pg_type {oid}
+NOTICE: checking pg_propgraph_property {pgpcollation} => pg_collation {oid}
diff --git a/src/test/regress/sql/create_property_graph.sql b/src/test/regress/sql/create_property_graph.sql
index 4f9b5c0349b..9a1fe4fc6de 100644
--- a/src/test/regress/sql/create_property_graph.sql
+++ b/src/test/regress/sql/create_property_graph.sql
@@ -147,6 +147,83 @@ GRANT SELECT ON PROPERTY GRAPH g1 TO regress_graph_user2;
GRANT UPDATE ON PROPERTY GRAPH g1 TO regress_graph_user2; -- fail
RESET ROLE;
+-- collation
+CREATE TABLE tc1 (a int, b text);
+CREATE TABLE tc2 (a int, b text);
+CREATE TABLE tc3 (a int, b text COLLATE "C");
+
+CREATE TABLE ec1 (ek1 int, ek2 int, eb text);
+CREATE TABLE ec2 (ek1 int, ek2 int, eb text COLLATE "POSIX");
+
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a), tc3 KEY (a)); -- fail
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a),
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)); -- fail
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a) DEFAULT LABEL PROPERTIES (a), tc3 KEY (b))
+ EDGE TABLES (
+ ec2 KEY (ek1, eb)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (eb) REFERENCES tc3 (b)); -- fail
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (tc1 KEY (a), tc2 KEY (a))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a));
+ALTER PROPERTY GRAPH gc1 ADD VERTEX TABLES (tc3 KEY (a)); -- fail
+ALTER PROPERTY GRAPH gc1 ADD EDGE TABLES (
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)); -- fail
+ALTER PROPERTY GRAPH gc1
+ ADD VERTEX TABLES (
+ tc3 KEY (a) DEFAULT LABEL PROPERTIES (a, b COLLATE pg_catalog.DEFAULT AS b));
+ALTER PROPERTY GRAPH gc1 ADD EDGE TABLES (
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb COLLATE pg_catalog.DEFAULT AS eb));
+DROP PROPERTY GRAPH gc1;
+CREATE PROPERTY GRAPH gc1
+ VERTEX TABLES (
+ tc1 KEY (a) DEFAULT LABEL PROPERTIES (a, b::varchar COLLATE "C" AS b),
+ tc2 KEY (a) DEFAULT LABEL PROPERTIES (a, (b COLLATE "C")::varchar AS b),
+ tc3 KEY (a) DEFAULT LABEL PROPERTIES (a, b::varchar AS b))
+ EDGE TABLES (
+ ec1 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb),
+ ec2 KEY (ek1, ek2)
+ SOURCE KEY (ek1) REFERENCES tc1 (a)
+ DESTINATION KEY (ek2) REFERENCES tc2 (a)
+ DEFAULT LABEL PROPERTIES (ek1, ek2, eb COLLATE pg_catalog.DEFAULT AS eb));
+
+-- type incosistency check
+CREATE TABLE v1 (a int primary key, b text);
+CREATE TABLE e(k1 text, k2 text, c text);
+CREATE TABLE v2 (m text, n text);
+CREATE PROPERTY GRAPH gt
+ VERTEX TABLES (v1 KEY (a), v2 KEY (m))
+ EDGE TABLES (
+ e KEY (k1, k2)
+ SOURCE KEY (k1) REFERENCES v1(a)
+ DESTINATION KEY (k2) REFERENCES v2(m)); -- fail
+ALTER TABLE e DROP COLUMN k1, ADD COLUMN k1 bigint primary key;
+CREATE PROPERTY GRAPH gt
+ VERTEX TABLES (v1 KEY (a), v2 KEY (m))
+ EDGE TABLES (
+ e KEY (k1, k2)
+ SOURCE KEY (k1) REFERENCES v1(a)
+ DESTINATION KEY (k2) REFERENCES v2(m));
-- information schema
diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql
index 905be9df01b..977bf2774e5 100644
--- a/src/test/regress/sql/graph_table.sql
+++ b/src/test/regress/sql/graph_table.sql
@@ -322,7 +322,8 @@ SELECT * FROM GRAPH_TABLE (g1 MATCH (a is vl1)->(b)->(a is vl2) WHERE a.vname <>
SELECT * FROM GRAPH_TABLE (g1 MATCH (a is vl1)->(b)->(a) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)->(b)->(a is vl1) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
--- add an edge with same vertex as source and destination to test loops
+-- add an edge with same vertex as source and destination to test loops. Also
+-- use this for collation tests
CREATE TABLE e3_3 (src_id int,
dest_id int,
ename varchar(10),
@@ -336,37 +337,53 @@ ALTER PROPERTY GRAPH g1 ADD EDGE TABLES (
);
INSERT INTO e3_3 VALUES (2003, 2003, 'e331', 10010);
+INSERT INTO e3_3 VALUES (2003, 2003, 'E331', 10010);
-- cyclic pattern with edge patterns with same variable name
-SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name ASC;
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) ORDER BY loop_name COLLATE "C" ASC;
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b IS el2 WHERE b.ename > 'E331' COLLATE "C"]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.ename > 'E331' COLLATE "C" COLUMNS (a.vname AS self, b.ename AS loop_name));
+SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) WHERE loop_name > 'E331' COLLATE "C";
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(c)-[b]->(d) COLUMNS (a.vname AS aname, b.ename AS bname, c.vname AS cname, d.vname AS dname)); --error
-- property graph with some of the elements, labels and properties same as the
-- previous one. Test whether components from the specified property graph are
--- used.
+-- used. Also use this for collation tests
create property graph g2
vertex tables (
v1
- label l1 properties ('g2.' || vname as elname),
+ label l1 properties ('g2.' || vname COLLATE "C" as elname),
v2 key (id1, id2)
- label l1 properties ('g2.' || vname as elname),
+ label l1 properties ('g2.' || vname COLLATE "C" as elname),
v3
- label l1 properties ('g2.' || vname as elname)
+ label l1 properties ('g2.' || vname COLLATE "C" as elname)
)
edge tables (
e1_2 key (id_1, id_2_1, id_2_2)
source key (id_1) references v1 (id)
destination key (id_2_1, id_2_2) references v2 (id1, id2)
- label l1 properties ('g2.' || ename as elname),
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
e1_3
source key (id_1) references v1 (id)
destination key (id_3) references v3 (id)
- label l1 properties ('g2.' || ename as elname),
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
e2_3 key (id_2_1, id_2_2, id_3)
source key (id_2_1, id_2_2) references v2 (id1, id2)
destination key (id_3) references v3 (id)
- label l1 properties ('g2.' || ename as elname)
+ label l1 properties ('g2.' || ename COLLATE "C" as elname),
+ e3_3 KEY (src_id, dest_id)
+ SOURCE KEY (src_id) REFERENCES v3 (id)
+ DESTINATION KEY (src_id) REFERENCES v3 (id)
+ LABEL l1 PROPERTIES ('g2.' || ename COLLATE "C" as elname)
);
-select sn, cn, dn from graph_table (g2 match (src : l1)-[conn : l1]->(dest : l1) columns (src.elname as sn, conn.elname as cn, dest.elname as dn));
+select sn, cn, dn from graph_table (g2 match (src : l1)-[conn : l1]->(dest : l1) columns (src.elname as sn, conn.elname as cn, dest.elname as dn)) ORDER BY 1, 2, 3;
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname COLLATE pg_catalog."default" AS loop_name)) ORDER BY loop_name ASC;
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b WHERE b.elname > 'g2.E331']->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name));
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.elname > 'g2.E331' COLUMNS (a.elname AS self, b.elname AS loop_name));
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name)) WHERE loop_name > 'g2.E331';
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b WHERE b.elname > 'g2.e331' COLLATE pg_catalog."default"]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name));
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.elname > 'g2.e331' COLLATE pg_catalog."default" COLUMNS (a.elname AS self, b.elname AS loop_name));
+SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name)) WHERE loop_name > 'g2.e331' COLLATE pg_catalog."default";
CREATE VIEW customers_us AS SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name));
@@ -428,6 +445,10 @@ SELECT *
COLUMNS (cg.name_redacted AS customer_name_redacted))
WHERE co.customer_id = 1;
+-- graph table in a subquery
+SELECT * FROM customers co WHERE co.customer_id =
+ (SELECT customer_id FROM GRAPH_TABLE (myshop2 MATCH (cg IS customers WHERE cg.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (cg.customer_id)));
+
-- query within graph table
SELECT sname, dname
FROM GRAPH_TABLE (g1 MATCH (src)->(dest)
--
2.34.1