public inbox for [email protected]
help / color / mirror / Atom feedFrom: Henson Choi <[email protected]>
To: pgsql-hackers <[email protected]>
To: Ashutosh Bapat <[email protected]>
To: Junwang Zhao <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Ajay Pal <[email protected]>
Cc: Imran Zaheer <[email protected]>
Subject: SQL/PGQ: Support multi-pattern path matching in GRAPH_TABLE
Date: Fri, 24 Apr 2026 13:44:26 +0900
Message-ID: <CAAAe_zAxTXev97Zhc=Z+LH6ZWQ8UbHQ5LAo_iVkqHm=49SZS+g@mail.gmail.com> (raw)
Hi hackers,
Now that the SQL/PGQ core has been committed, I'd like to propose
extending GRAPH_TABLE to accept multiple path patterns in the MATCH
clause, i.e. the comma-separated form:
SELECT ... FROM GRAPH_TABLE (g
MATCH <path_pattern_1>, <path_pattern_2>, ...
COLUMNS (...)
);
This shape is not supported today — the parser rejects it with
multiple path patterns in one GRAPH_TABLE clause not supported
and the rewriter asserts that the path_pattern_list has exactly one
entry. Among the features that are not yet covered, I think this one
has the highest practical need: many realistic graph queries express
joins and star-shaped traversals most naturally as multiple
comma-separated patterns, and without it those queries have to be
rewritten into more awkward forms. The attached patch lifts the
restriction and wires the existing path-rewriting pipeline through
the list of path patterns.
What the patch does
-------------------
Parser side: the error that rejected multi-pattern MATCH is
removed, so such queries now reach the rewriter.
Rewriter side: each path pattern is processed as its own chain,
so adjacency linking never crosses a path boundary. Element
variables that share a name across paths are still merged into
the same element — shared variables produce joins, disconnected
paths produce cross products.
An earlier version flattened all element patterns into a single
list, but that treated elements from adjacent paths as adjacent
within one path and broke on vertex-vertex boundaries. The
per-path approach is the minimal fix; a more principled cross-path
join construction is left for this thread to settle.
Examples
--------
Shared variable (join):
MATCH (a IS vl1)-[e1 IS el1]->(b IS vl2),
(b)-[e2 IS el2]->(c IS vl3)
-- b is shared -> the two patterns are joined on b.
Star/hub:
MATCH (a IS vl1)-[]->(b IS vl2),
(a)-[]->(c IS vl3),
(d IS vl2)-[]->(a)
-- three patterns meeting at a.
Disconnected patterns (cross product):
MATCH (a IS vl1), (b IS vl3)
Partial connection mixed with a disconnected piece:
MATCH (a)-[]->(b), (b)-[]->(c), (d IS vl1)
Status
------
I would appreciate feedback along these axes:
* standard conformance — whether the shape and the handling of
multi-pattern MATCH are aligned with SQL/PGQ (ISO/IEC 9075-16);
* semantics — whether the behavior on shared variables,
disconnected patterns, and their combinations is the right one;
* functionality — coverage gaps, cases the patch does not yet
handle, or constructs that should be rejected but currently are
not (and vice versa);
* robustness — correctness under edge cases, error handling, and
anything that could destabilize the existing GRAPH_TABLE path;
* code shape — whether generate_queries_for_path_pattern() has
grown large enough that the per-path body should be factored
out into a helper. I kept the function intact in this round,
but would gladly split it if reviewers prefer that.
Review comments, objections, and alternative approaches are all
welcome — please don't hesitate to push back on anything that looks
off.
Thanks,
Henson
Reference to the SQL/PGQ main thread:
https://postgr.es/m/CAAAe_zAEEAb=piH4n-mZUhqcL=oKbDv4v-_7C_7KyXroem=HUg@mail.gmail.com
Attachments:
[application/octet-stream] v1-0001-Multi-pattern-path-matching.patch (34.5K, 3-v1-0001-Multi-pattern-path-matching.patch)
download | inline diff:
From 0000000000000000000000000000000000000000 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 22 Mar 2026 22:24:00 +0900
Subject: [PATCH v1] SQL/PGQ: Support multi-pattern path matching in
GRAPH_TABLE
Extend GRAPH_TABLE to accept multiple comma-separated path patterns in
the MATCH clause. Element patterns are processed path-by-path for
adjacency linking; across paths, element variables that share a name
are merged into the same path_factor, so shared variables form join
points between paths and unrelated paths produce a cross product. The
parser-side restriction in transformPathPatternList() is lifted.
---
src/backend/parser/parse_graphtable.c | 9 -
src/backend/rewrite/rewriteGraphTable.c | 295 ++++++++++++++-------------
src/test/regress/expected/graph_table.out | 328 +++++++++++++++++++++++++++++-
src/test/regress/sql/graph_table.sql | 114 ++++++++++-
4 files changed, 592 insertions(+), 154 deletions(-)
diff --git a/src/backend/parser/parse_graphtable.c b/src/backend/parser/parse_graphtable.c
index 30ddce5aa9f..84c13ee8ec7 100644
--- a/src/backend/parser/parse_graphtable.c
+++ b/src/backend/parser/parse_graphtable.c
@@ -331,15 +331,6 @@ transformPathPatternList(ParseState *pstate, List *path_pattern)
/* Grammar doesn't allow empty path pattern list */
Assert(list_length(path_pattern) > 0);
- /*
- * We do not support multiple path patterns in one GRAPH_TABLE clause
- * right now. But we may do so in future.
- */
- if (list_length(path_pattern) != 1)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("multiple path patterns in one GRAPH_TABLE clause not supported")));
-
/*
* Collect all the variables in the path pattern into the
* GraphTableParseState so that we can detect any non-local element
diff --git a/src/backend/rewrite/rewriteGraphTable.c b/src/backend/rewrite/rewriteGraphTable.c
index 2c3199d3230..09959f1dd95 100644
--- a/src/backend/rewrite/rewriteGraphTable.c
+++ b/src/backend/rewrite/rewriteGraphTable.c
@@ -91,7 +91,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, Oid refid, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum, AttrNumber catalog_eqop_attnum);
-static List *generate_queries_for_path_pattern(RangeTblEntry *rte, List *element_patterns);
+static List *generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern_list);
static Query *generate_query_for_graph_path(RangeTblEntry *rte, List *path);
static Node *generate_setop_from_pathqueries(List *pathqueries, List **rtable, List **targetlist);
static List *generate_queries_for_path_pattern_recurse(RangeTblEntry *rte, List *pathqueries, List *cur_path, List *path_pattern_lists, int elempos);
@@ -110,15 +110,12 @@ rewriteGraphTable(Query *parsetree, int rt_index)
{
RangeTblEntry *rte;
Query *graph_table_query;
- List *path_pattern;
List *pathqueries = NIL;
rte = rt_fetch(rt_index, parsetree->rtable);
- Assert(list_length(rte->graph_pattern->path_pattern_list) == 1);
-
- path_pattern = linitial(rte->graph_pattern->path_pattern_list);
- pathqueries = generate_queries_for_path_pattern(rte, path_pattern);
+ pathqueries = generate_queries_for_path_pattern(rte,
+ rte->graph_pattern->path_pattern_list);
graph_table_query = generate_union_from_pathqueries(&pathqueries);
AcquireRewriteLocks(graph_table_query, true, false);
@@ -171,167 +168,181 @@ rewriteGraphTable(Query *parsetree, int rt_index)
* the GRAPH_TABLE clause represented by given 'rte'.
*/
static List *
-generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern)
+generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern_list)
{
List *pathqueries = NIL;
List *path_elem_lists = NIL;
int factorpos = 0;
List *path_factors = NIL;
- struct path_factor *prev_pf = NULL;
- Assert(list_length(path_pattern) > 0);
+ Assert(list_length(path_pattern_list) > 0);
/*
- * Create a list of path factors representing the given path pattern
+ * Create a list of path factors representing the given path patterns
* linking edge path factors to their adjacent vertex path factors.
*
- * While doing that merge element patterns with the same variable name
- * into a single path_factor.
+ * Each path pattern (path term) is processed independently for adjacency
+ * linking; prev_pf is reset at the start of every path so that the last
+ * element of one path is not treated as adjacent to the first element of
+ * the next one. Across paths, element patterns with the same variable
+ * name are still merged into a single path_factor, so shared variables
+ * act as join points between paths and unrelated paths produce a cross
+ * product.
*/
- foreach_node(GraphElementPattern, gep, path_pattern)
+ foreach_node(List, path_pattern, path_pattern_list)
{
- struct path_factor *pf = NULL;
-
- /*
- * Unsupported conditions should have been caught by the parser
- * itself. We have corresponding Asserts here to document the
- * assumptions in this code.
- */
- Assert(gep->kind == VERTEX_PATTERN || IS_EDGE_PATTERN(gep->kind));
- Assert(!gep->quantifier);
+ struct path_factor *prev_pf = NULL;
- foreach_ptr(struct path_factor, other, path_factors)
+ foreach_node(GraphElementPattern, gep, path_pattern)
{
- if (gep->variable && other->variable &&
- strcmp(gep->variable, other->variable) == 0)
- {
- if (other->kind != gep->kind)
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("element patterns with same variable name \"%s\" but different element pattern types",
- gep->variable)));
+ struct path_factor *pf = NULL;
- /*
- * If both the element patterns have label expressions, they
- * need to be conjuncted, which is not supported right now.
- *
- * However, an empty label expression means all labels.
- * Conjunction of any label expression with all labels is the
- * expression itself. Hence if only one of the two element
- * patterns has a label expression use that expression.
- */
- if (!other->labelexpr)
- other->labelexpr = gep->labelexpr;
- else if (gep->labelexpr && !equal(other->labelexpr, gep->labelexpr))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("element patterns with same variable name \"%s\" but different label expressions are not supported",
- gep->variable)));
+ /*
+ * Unsupported conditions should have been caught by the parser
+ * itself. We have corresponding Asserts here to document the
+ * assumptions in this code.
+ */
+ Assert(gep->kind == VERTEX_PATTERN || IS_EDGE_PATTERN(gep->kind));
+ Assert(!gep->quantifier);
- /*
- * If two element patterns have the same variable name, they
- * represent the same set of graph elements and hence are
- * constrained by conditions from both the element patterns.
- */
- if (!other->whereClause)
- other->whereClause = gep->whereClause;
- else if (gep->whereClause)
- other->whereClause = (Node *) makeBoolExpr(AND_EXPR,
- list_make2(other->whereClause, gep->whereClause),
- -1);
- pf = other;
- break;
- }
- }
+ foreach_ptr(struct path_factor, other, path_factors)
+ {
+ if (gep->variable && other->variable &&
+ strcmp(gep->variable, other->variable) == 0)
+ {
+ if (other->kind != gep->kind)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("element patterns with same variable name \"%s\" but different element pattern types",
+ gep->variable)));
- if (!pf)
- {
- pf = palloc0_object(struct path_factor);
- pf->factorpos = factorpos++;
- pf->kind = gep->kind;
- pf->labelexpr = gep->labelexpr;
- pf->variable = gep->variable;
- pf->whereClause = gep->whereClause;
-
- path_factors = lappend(path_factors, pf);
- }
+ /*
+ * If both the element patterns have label expressions,
+ * they need to be conjuncted, which is not supported
+ * right now.
+ *
+ * However, an empty label expression means all labels.
+ * Conjunction of any label expression with all labels is
+ * the expression itself. Hence if only one of the two
+ * element patterns has a label expression use that
+ * expression.
+ */
+ if (!other->labelexpr)
+ other->labelexpr = gep->labelexpr;
+ else if (gep->labelexpr && !equal(other->labelexpr, gep->labelexpr))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("element patterns with same variable name \"%s\" but different label expressions are not supported",
+ gep->variable)));
- /*
- * Setup links to the previous path factor in the path.
- *
- * If the previous path factor represents an edge, this path factor
- * represents an adjacent vertex; the source vertex for an edge
- * pointing left or the destination vertex for an edge pointing right.
- * If this path factor represents an edge, the previous path factor
- * represents an adjacent vertex; source vertex for an edge pointing
- * right or the destination vertex for an edge pointing left.
- *
- * Edge pointing in any direction is treated similar to that pointing
- * in right direction here. When constructing a query in
- * generate_query_for_graph_path(), we will try links in both the
- * directions.
- *
- * If multiple edge patterns share the same variable name, they
- * constrain the adjacent vertex patterns since an edge can connect
- * only one pair of vertexes. These adjacent vertex patterns need to
- * be merged even though they have different variables. Such element
- * patterns form a walk of graph where vertex and edges are repeated.
- * For example, in (a)-[b]->(c)<-[b]-(d), (a) and (d) represent the
- * same vertex element. This is slightly harder to implement and
- * probably less useful. Hence not supported for now.
- */
- if (prev_pf)
- {
- if (prev_pf->kind == EDGE_PATTERN_RIGHT || prev_pf->kind == EDGE_PATTERN_ANY)
- {
- Assert(!IS_EDGE_PATTERN(pf->kind));
- if (prev_pf->dest_pf && prev_pf->dest_pf != pf)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
- prev_pf->dest_pf = pf;
- }
- else if (prev_pf->kind == EDGE_PATTERN_LEFT)
- {
- Assert(!IS_EDGE_PATTERN(pf->kind));
- if (prev_pf->src_pf && prev_pf->src_pf != pf)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
- prev_pf->src_pf = pf;
- }
- else
- {
- Assert(prev_pf->kind == VERTEX_PATTERN);
- Assert(IS_EDGE_PATTERN(pf->kind));
+ /*
+ * If two element patterns have the same variable name,
+ * they represent the same set of graph elements and hence
+ * are constrained by conditions from both the element
+ * patterns.
+ */
+ if (!other->whereClause)
+ other->whereClause = gep->whereClause;
+ else if (gep->whereClause)
+ other->whereClause = (Node *) makeBoolExpr(AND_EXPR,
+ list_make2(other->whereClause, gep->whereClause),
+ -1);
+ pf = other;
+ break;
+ }
}
- if (pf->kind == EDGE_PATTERN_RIGHT || pf->kind == EDGE_PATTERN_ANY)
- {
- Assert(!IS_EDGE_PATTERN(prev_pf->kind));
- if (pf->src_pf && pf->src_pf != prev_pf)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
- pf->src_pf = prev_pf;
- }
- else if (pf->kind == EDGE_PATTERN_LEFT)
+ if (!pf)
{
- Assert(!IS_EDGE_PATTERN(prev_pf->kind));
- if (pf->dest_pf && pf->dest_pf != prev_pf)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
- pf->dest_pf = prev_pf;
+ pf = palloc0_object(struct path_factor);
+ pf->factorpos = factorpos++;
+ pf->kind = gep->kind;
+ pf->labelexpr = gep->labelexpr;
+ pf->variable = gep->variable;
+ pf->whereClause = gep->whereClause;
+
+ path_factors = lappend(path_factors, pf);
}
- else
+
+ /*
+ * Setup links to the previous path factor in the path.
+ *
+ * If the previous path factor represents an edge, this path
+ * factor represents an adjacent vertex; the source vertex for an
+ * edge pointing left or the destination vertex for an edge
+ * pointing right. If this path factor represents an edge, the
+ * previous path factor represents an adjacent vertex; source
+ * vertex for an edge pointing right or the destination vertex for
+ * an edge pointing left.
+ *
+ * Edge pointing in any direction is treated similar to that
+ * pointing in right direction here. When constructing a query in
+ * generate_query_for_graph_path(), we will try links in both the
+ * directions.
+ *
+ * If multiple edge patterns share the same variable name, they
+ * constrain the adjacent vertex patterns since an edge can
+ * connect only one pair of vertexes. These adjacent vertex
+ * patterns need to be merged even though they have different
+ * variables. Such element patterns form a walk of graph where
+ * vertex and edges are repeated. For example, in
+ * (a)-[b]->(c)<-[b]-(d), (a) and (d) represent the same vertex
+ * element. This is slightly harder to implement and probably less
+ * useful. Hence not supported for now.
+ */
+ if (prev_pf)
{
- Assert(pf->kind == VERTEX_PATTERN);
- Assert(IS_EDGE_PATTERN(prev_pf->kind));
+ if (prev_pf->kind == EDGE_PATTERN_RIGHT || prev_pf->kind == EDGE_PATTERN_ANY)
+ {
+ Assert(!IS_EDGE_PATTERN(pf->kind));
+ if (prev_pf->dest_pf && prev_pf->dest_pf != pf)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
+ prev_pf->dest_pf = pf;
+ }
+ else if (prev_pf->kind == EDGE_PATTERN_LEFT)
+ {
+ Assert(!IS_EDGE_PATTERN(pf->kind));
+ if (prev_pf->src_pf && prev_pf->src_pf != pf)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
+ prev_pf->src_pf = pf;
+ }
+ else
+ {
+ Assert(prev_pf->kind == VERTEX_PATTERN);
+ Assert(IS_EDGE_PATTERN(pf->kind));
+ }
+
+ if (pf->kind == EDGE_PATTERN_RIGHT || pf->kind == EDGE_PATTERN_ANY)
+ {
+ Assert(!IS_EDGE_PATTERN(prev_pf->kind));
+ if (pf->src_pf && pf->src_pf != prev_pf)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
+ pf->src_pf = prev_pf;
+ }
+ else if (pf->kind == EDGE_PATTERN_LEFT)
+ {
+ Assert(!IS_EDGE_PATTERN(prev_pf->kind));
+ if (pf->dest_pf && pf->dest_pf != prev_pf)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
+ pf->dest_pf = prev_pf;
+ }
+ else
+ {
+ Assert(pf->kind == VERTEX_PATTERN);
+ Assert(IS_EDGE_PATTERN(prev_pf->kind));
+ }
}
- }
- prev_pf = pf;
+ prev_pf = pf;
+ }
}
/*
diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out
index b579e3df635..43bf3a5b13b 100644
--- a/src/test/regress/expected/graph_table.out
+++ b/src/test/regress/expected/graph_table.out
@@ -93,8 +93,6 @@ SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.addr
ERROR: syntax error at or near "COLUMNS"
LINE 1: ...mers WHERE c.address = 'US')-[IS customer_orders] COLUMNS (c...
^
-SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name)); -- error
-ERROR: multiple path patterns in one GRAPH_TABLE clause not supported
SELECT * FROM GRAPH_TABLE (myshop MATCH COLUMNS (1 AS col)); -- error, empty match clause
ERROR: syntax error at or near "COLUMNS"
LINE 1: SELECT * FROM GRAPH_TABLE (myshop MATCH COLUMNS (1 AS col));
@@ -1022,4 +1020,330 @@ SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE src.vprop1 >
ERROR: subqueries within GRAPH_TABLE reference are not supported
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE out_degree(src.vname) > (SELECT max(out_degree(nname)) FROM GRAPH_TABLE (g1 MATCH (node) COLUMNS (node.vname AS nname))) COLUMNS(src.vname AS sname, dest.vname AS dname));
ERROR: subqueries within GRAPH_TABLE reference are not supported
+-- Multi-pattern path tests (comma-separated path patterns in MATCH)
+-- disconnected patterns: cross product of customers and orders
+-- (3 customers x 3 orders = 9 rows, projecting c.name only)
+SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name));
+ customer_name
+---------------
+ customer1
+ customer2
+ customer3
+ customer1
+ customer2
+ customer3
+ customer1
+ customer2
+ customer3
+(9 rows)
+
+-- disconnected patterns: one side yields no match -> empty result
+SELECT * FROM GRAPH_TABLE (myshop
+ MATCH (c IS customers WHERE c.address = 'NONEXISTENT'), (o IS orders)
+ COLUMNS (c.name AS customer_name));
+ customer_name
+---------------
+(0 rows)
+
+-- Basic multi-pattern: (a)->(b), (b)->(c) where b is shared
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[e1 IS el1]->(b IS vl2),
+ (b)-[e2 IS el2]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY a_name, b_name, c_name;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v22 | v32
+(1 row)
+
+-- Multi-pattern with same start vertex reaching different destinations
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (a)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY a_name, b_name, c_name;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v22 | v22
+ v11 | v22 | v31
+ v11 | v22 | v33
+ v12 | v21 | v21
+ v13 | v23 | v23
+(5 rows)
+
+-- Multi-pattern with three patterns sharing one variable
+SELECT a_name, b2_name, b3_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b2 IS vl2),
+ (a)-[]->(b3 IS vl3)
+ COLUMNS (a.vname AS a_name, b2.vname AS b2_name, b3.vname AS b3_name)
+) ORDER BY a_name, b2_name, b3_name;
+ a_name | b2_name | b3_name
+--------+---------+---------
+ v11 | v22 | v22
+ v11 | v22 | v31
+ v11 | v22 | v33
+ v12 | v21 | v21
+ v13 | v23 | v23
+(5 rows)
+
+-- Same variable with same label (should work)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b IS vl2)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v22 | v32
+(1 row)
+
+-- Same variable with different labels (conflict - what happens?)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b IS vl3)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+ERROR: element patterns with same variable name "b" but different label expressions are not supported
+-- Same variable with label OR expression (b IS vl2|vl3)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2|vl3),
+ (b)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v22 | v32
+ v11 | v33 | v33
+ v11 | v33 | v33
+(3 rows)
+
+-- Disconnected patterns (no shared variables) - should produce cross product
+-- (a)->(b) and (c)->(d) are independent, result is Cartesian product
+-- vl1->vl2 has 3 paths, vl1->vl3 has 3 paths, so cross product = 3 x 3 = 9 rows
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (c IS vl1)-[]->(d IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+ a_name | b_name | c_name | d_name
+--------+--------+--------+--------
+ v11 | v22 | v11 | v22
+ v11 | v22 | v11 | v31
+ v11 | v22 | v11 | v33
+ v11 | v22 | v12 | v21
+ v11 | v22 | v13 | v23
+ v12 | v21 | v11 | v22
+ v12 | v21 | v11 | v31
+ v12 | v21 | v11 | v33
+ v12 | v21 | v12 | v21
+ v12 | v21 | v13 | v23
+ v13 | v23 | v11 | v22
+ v13 | v23 | v11 | v31
+ v13 | v23 | v11 | v33
+ v13 | v23 | v12 | v21
+ v13 | v23 | v13 | v23
+(15 rows)
+
+-- Disconnected patterns: EXPLAIN should show cross join (Nested Loop without join condition)
+EXPLAIN (COSTS OFF) SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (c IS vl1)-[]->(d IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
+ Append
+ -> Merge Join
+ Merge Cond: (v1.id = e1_2.id_1)
+ -> Nested Loop
+ -> Index Scan using v1_pkey on v1
+ -> Materialize
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: ((e1_2_1.id_2_1 = v2_1.id1) AND (e1_2_1.id_2_2 = v2_1.id2))
+ -> Sort
+ Sort Key: e1_2_1.id_2_1, e1_2_1.id_2_2
+ -> Seq Scan on e1_2 e1_2_1
+ -> Sort
+ Sort Key: v2_1.id1, v2_1.id2
+ -> Seq Scan on v2 v2_1
+ -> Index Scan using v1_pkey on v1 v1_1
+ Index Cond: (id = e1_2_1.id_1)
+ -> Sort
+ Sort Key: e1_2.id_1
+ -> Merge Join
+ Merge Cond: ((e1_2.id_2_1 = v2.id1) AND (e1_2.id_2_2 = v2.id2))
+ -> Sort
+ Sort Key: e1_2.id_2_1, e1_2.id_2_2
+ -> Seq Scan on e1_2
+ -> Sort
+ Sort Key: v2.id1, v2.id2
+ -> Seq Scan on v2
+ -> Hash Join
+ Hash Cond: (e1_3.id_3 = v3.id)
+ -> Hash Join
+ Hash Cond: (e1_3.id_1 = v1_3.id)
+ -> Nested Loop
+ -> Seq Scan on e1_3
+ -> Materialize
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: ((e1_2_2.id_2_1 = v2_2.id1) AND (e1_2_2.id_2_2 = v2_2.id2))
+ -> Sort
+ Sort Key: e1_2_2.id_2_1, e1_2_2.id_2_2
+ -> Seq Scan on e1_2 e1_2_2
+ -> Sort
+ Sort Key: v2_2.id1, v2_2.id2
+ -> Seq Scan on v2 v2_2
+ -> Index Scan using v1_pkey on v1 v1_2
+ Index Cond: (id = e1_2_2.id_1)
+ -> Hash
+ -> Seq Scan on v1 v1_3
+ -> Hash
+ -> Seq Scan on v3
+(49 rows)
+
+-- Disconnected patterns: single vertex patterns (no edges)
+-- vl1 has 3 rows (v1), vl3 has 6 rows (v2+v3), cross product = 18 rows
+SELECT a_name, b_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1), (b IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name)
+) ORDER BY 1, 2;
+ a_name | b_name
+--------+--------
+ v11 | v21
+ v11 | v22
+ v11 | v23
+ v11 | v31
+ v11 | v32
+ v11 | v33
+ v12 | v21
+ v12 | v22
+ v12 | v23
+ v12 | v31
+ v12 | v32
+ v12 | v33
+ v13 | v21
+ v13 | v22
+ v13 | v23
+ v13 | v31
+ v13 | v32
+ v13 | v33
+(18 rows)
+
+-- Disconnected patterns: three independent patterns (3-way cross product)
+-- vl1 has 3 rows, vl2 has 3 rows, vl3 has 6 rows = 54 rows
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1), (b IS vl2), (c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v21 | v21
+ v11 | v21 | v22
+ v11 | v21 | v23
+ v11 | v21 | v31
+ v11 | v21 | v32
+ v11 | v21 | v33
+ v11 | v22 | v21
+ v11 | v22 | v22
+ v11 | v22 | v23
+ v11 | v22 | v31
+ v11 | v22 | v32
+ v11 | v22 | v33
+ v11 | v23 | v21
+ v11 | v23 | v22
+ v11 | v23 | v23
+ v11 | v23 | v31
+ v11 | v23 | v32
+ v11 | v23 | v33
+ v12 | v21 | v21
+ v12 | v21 | v22
+ v12 | v21 | v23
+ v12 | v21 | v31
+ v12 | v21 | v32
+ v12 | v21 | v33
+ v12 | v22 | v21
+ v12 | v22 | v22
+ v12 | v22 | v23
+ v12 | v22 | v31
+ v12 | v22 | v32
+ v12 | v22 | v33
+ v12 | v23 | v21
+ v12 | v23 | v22
+ v12 | v23 | v23
+ v12 | v23 | v31
+ v12 | v23 | v32
+ v12 | v23 | v33
+ v13 | v21 | v21
+ v13 | v21 | v22
+ v13 | v21 | v23
+ v13 | v21 | v31
+ v13 | v21 | v32
+ v13 | v21 | v33
+ v13 | v22 | v21
+ v13 | v22 | v22
+ v13 | v22 | v23
+ v13 | v22 | v31
+ v13 | v22 | v32
+ v13 | v22 | v33
+ v13 | v23 | v21
+ v13 | v23 | v22
+ v13 | v23 | v23
+ v13 | v23 | v31
+ v13 | v23 | v32
+ v13 | v23 | v33
+(54 rows)
+
+-- Mixed: partially connected + disconnected patterns
+-- (a)->(b), (b)->(c) are connected via 'b', but (d) is disconnected
+-- vl1->vl2->vl3 chain + independent vl1 vertex
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b)-[]->(c IS vl3),
+ (d IS vl1)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+ a_name | b_name | c_name | d_name
+--------+--------+--------+--------
+ v11 | v22 | v32 | v11
+ v11 | v22 | v32 | v12
+ v11 | v22 | v32 | v13
+(3 rows)
+
+-- Disconnected patterns: verify row count with different sizes
+-- Using WHERE to filter one side: (a IS vl1 WHERE vprop1 = 10) has 1 row
+-- Cross with (b IS vl3) which has 6 rows = 6 rows
+SELECT a_name, b_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1 WHERE a.vprop1 = 10), (b IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name)
+) ORDER BY 1, 2;
+ a_name | b_name
+--------+--------
+ v11 | v21
+ v11 | v22
+ v11 | v23
+ v11 | v31
+ v11 | v32
+ v11 | v33
+(6 rows)
+
+-- Star pattern: three patterns with shared hub 'a' (A->B, A->C, D->A)
+-- All joined via 'a', NOT a cross product
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (a)-[]->(c IS vl3),
+ (d IS vl2)-[]->(a)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+ a_name | b_name | c_name | d_name
+--------+--------+--------+--------
+ v12 | v21 | v21 | v21
+ v13 | v23 | v23 | v23
+(2 rows)
+
-- leave the objects behind for pg_upgrade/pg_dump tests
diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql
index 4ff98817420..008a18b219b 100644
--- a/src/test/regress/sql/graph_table.sql
+++ b/src/test/regress/sql/graph_table.sql
@@ -89,7 +89,6 @@ SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.addr
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.namex AS customer_name)); -- error
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers|employees WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name)); -- error
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders] COLUMNS (c.name AS customer_name)); -- error
-SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name)); -- error
SELECT * FROM GRAPH_TABLE (myshop MATCH COLUMNS (1 AS col)); -- error, empty match clause
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)->{1,2}(o IS orders) COLUMNS (c.name AS customer_name)); -- error
SELECT * FROM GRAPH_TABLE (myshop MATCH ((c IS customers)->(o IS orders)) COLUMNS (c.name));
@@ -582,4 +581,117 @@ SELECT * FROM customers co WHERE co.customer_id = (SELECT customer_id FROM GRAPH
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE src.vprop1 > (SELECT max(v1.vprop1) FROM v1) COLUMNS(src.vname AS sname, dest.vname AS dname));
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE out_degree(src.vname) > (SELECT max(out_degree(nname)) FROM GRAPH_TABLE (g1 MATCH (node) COLUMNS (node.vname AS nname))) COLUMNS(src.vname AS sname, dest.vname AS dname));
+-- Multi-pattern path tests (comma-separated path patterns in MATCH)
+-- disconnected patterns: cross product of customers and orders
+-- (3 customers x 3 orders = 9 rows, projecting c.name only)
+SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name));
+
+-- disconnected patterns: one side yields no match -> empty result
+SELECT * FROM GRAPH_TABLE (myshop
+ MATCH (c IS customers WHERE c.address = 'NONEXISTENT'), (o IS orders)
+ COLUMNS (c.name AS customer_name));
+
+-- Basic multi-pattern: (a)->(b), (b)->(c) where b is shared
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[e1 IS el1]->(b IS vl2),
+ (b)-[e2 IS el2]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY a_name, b_name, c_name;
+
+-- Multi-pattern with same start vertex reaching different destinations
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (a)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY a_name, b_name, c_name;
+
+-- Multi-pattern with three patterns sharing one variable
+SELECT a_name, b2_name, b3_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b2 IS vl2),
+ (a)-[]->(b3 IS vl3)
+ COLUMNS (a.vname AS a_name, b2.vname AS b2_name, b3.vname AS b3_name)
+) ORDER BY a_name, b2_name, b3_name;
+
+-- Same variable with same label (should work)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b IS vl2)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+
+-- Same variable with different labels (conflict - what happens?)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b IS vl3)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+
+-- Same variable with label OR expression (b IS vl2|vl3)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2|vl3),
+ (b)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+
+-- Disconnected patterns (no shared variables) - should produce cross product
+-- (a)->(b) and (c)->(d) are independent, result is Cartesian product
+-- vl1->vl2 has 3 paths, vl1->vl3 has 3 paths, so cross product = 3 x 3 = 9 rows
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (c IS vl1)-[]->(d IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+
+-- Disconnected patterns: EXPLAIN should show cross join (Nested Loop without join condition)
+EXPLAIN (COSTS OFF) SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (c IS vl1)-[]->(d IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+);
+
+-- Disconnected patterns: single vertex patterns (no edges)
+-- vl1 has 3 rows (v1), vl3 has 6 rows (v2+v3), cross product = 18 rows
+SELECT a_name, b_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1), (b IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name)
+) ORDER BY 1, 2;
+
+-- Disconnected patterns: three independent patterns (3-way cross product)
+-- vl1 has 3 rows, vl2 has 3 rows, vl3 has 6 rows = 54 rows
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1), (b IS vl2), (c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+
+-- Mixed: partially connected + disconnected patterns
+-- (a)->(b), (b)->(c) are connected via 'b', but (d) is disconnected
+-- vl1->vl2->vl3 chain + independent vl1 vertex
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b)-[]->(c IS vl3),
+ (d IS vl1)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+
+-- Disconnected patterns: verify row count with different sizes
+-- Using WHERE to filter one side: (a IS vl1 WHERE vprop1 = 10) has 1 row
+-- Cross with (b IS vl3) which has 6 rows = 6 rows
+SELECT a_name, b_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1 WHERE a.vprop1 = 10), (b IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name)
+) ORDER BY 1, 2;
+
+-- Star pattern: three patterns with shared hub 'a' (A->B, A->C, D->A)
+-- All joined via 'a', NOT a cross product
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (a)-[]->(c IS vl3),
+ (d IS vl2)-[]->(a)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+
-- leave the objects behind for pg_upgrade/pg_dump tests
[application/octet-stream] v1-0001-Multi-pattern-path-matching.patch (34.5K, 4-v1-0001-Multi-pattern-path-matching.patch)
download | inline diff:
From 0000000000000000000000000000000000000000 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sun, 22 Mar 2026 22:24:00 +0900
Subject: [PATCH v1] SQL/PGQ: Support multi-pattern path matching in
GRAPH_TABLE
Extend GRAPH_TABLE to accept multiple comma-separated path patterns in
the MATCH clause. Element patterns are processed path-by-path for
adjacency linking; across paths, element variables that share a name
are merged into the same path_factor, so shared variables form join
points between paths and unrelated paths produce a cross product. The
parser-side restriction in transformPathPatternList() is lifted.
---
src/backend/parser/parse_graphtable.c | 9 -
src/backend/rewrite/rewriteGraphTable.c | 295 ++++++++++++++-------------
src/test/regress/expected/graph_table.out | 328 +++++++++++++++++++++++++++++-
src/test/regress/sql/graph_table.sql | 114 ++++++++++-
4 files changed, 592 insertions(+), 154 deletions(-)
diff --git a/src/backend/parser/parse_graphtable.c b/src/backend/parser/parse_graphtable.c
index 30ddce5aa9f..84c13ee8ec7 100644
--- a/src/backend/parser/parse_graphtable.c
+++ b/src/backend/parser/parse_graphtable.c
@@ -331,15 +331,6 @@ transformPathPatternList(ParseState *pstate, List *path_pattern)
/* Grammar doesn't allow empty path pattern list */
Assert(list_length(path_pattern) > 0);
- /*
- * We do not support multiple path patterns in one GRAPH_TABLE clause
- * right now. But we may do so in future.
- */
- if (list_length(path_pattern) != 1)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("multiple path patterns in one GRAPH_TABLE clause not supported")));
-
/*
* Collect all the variables in the path pattern into the
* GraphTableParseState so that we can detect any non-local element
diff --git a/src/backend/rewrite/rewriteGraphTable.c b/src/backend/rewrite/rewriteGraphTable.c
index 2c3199d3230..09959f1dd95 100644
--- a/src/backend/rewrite/rewriteGraphTable.c
+++ b/src/backend/rewrite/rewriteGraphTable.c
@@ -91,7 +91,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, Oid refid, AttrNumber catalog_key_attnum, AttrNumber catalog_ref_attnum, AttrNumber catalog_eqop_attnum);
-static List *generate_queries_for_path_pattern(RangeTblEntry *rte, List *element_patterns);
+static List *generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern_list);
static Query *generate_query_for_graph_path(RangeTblEntry *rte, List *path);
static Node *generate_setop_from_pathqueries(List *pathqueries, List **rtable, List **targetlist);
static List *generate_queries_for_path_pattern_recurse(RangeTblEntry *rte, List *pathqueries, List *cur_path, List *path_pattern_lists, int elempos);
@@ -110,15 +110,12 @@ rewriteGraphTable(Query *parsetree, int rt_index)
{
RangeTblEntry *rte;
Query *graph_table_query;
- List *path_pattern;
List *pathqueries = NIL;
rte = rt_fetch(rt_index, parsetree->rtable);
- Assert(list_length(rte->graph_pattern->path_pattern_list) == 1);
-
- path_pattern = linitial(rte->graph_pattern->path_pattern_list);
- pathqueries = generate_queries_for_path_pattern(rte, path_pattern);
+ pathqueries = generate_queries_for_path_pattern(rte,
+ rte->graph_pattern->path_pattern_list);
graph_table_query = generate_union_from_pathqueries(&pathqueries);
AcquireRewriteLocks(graph_table_query, true, false);
@@ -171,167 +168,181 @@ rewriteGraphTable(Query *parsetree, int rt_index)
* the GRAPH_TABLE clause represented by given 'rte'.
*/
static List *
-generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern)
+generate_queries_for_path_pattern(RangeTblEntry *rte, List *path_pattern_list)
{
List *pathqueries = NIL;
List *path_elem_lists = NIL;
int factorpos = 0;
List *path_factors = NIL;
- struct path_factor *prev_pf = NULL;
- Assert(list_length(path_pattern) > 0);
+ Assert(list_length(path_pattern_list) > 0);
/*
- * Create a list of path factors representing the given path pattern
+ * Create a list of path factors representing the given path patterns
* linking edge path factors to their adjacent vertex path factors.
*
- * While doing that merge element patterns with the same variable name
- * into a single path_factor.
+ * Each path pattern (path term) is processed independently for adjacency
+ * linking; prev_pf is reset at the start of every path so that the last
+ * element of one path is not treated as adjacent to the first element of
+ * the next one. Across paths, element patterns with the same variable
+ * name are still merged into a single path_factor, so shared variables
+ * act as join points between paths and unrelated paths produce a cross
+ * product.
*/
- foreach_node(GraphElementPattern, gep, path_pattern)
+ foreach_node(List, path_pattern, path_pattern_list)
{
- struct path_factor *pf = NULL;
-
- /*
- * Unsupported conditions should have been caught by the parser
- * itself. We have corresponding Asserts here to document the
- * assumptions in this code.
- */
- Assert(gep->kind == VERTEX_PATTERN || IS_EDGE_PATTERN(gep->kind));
- Assert(!gep->quantifier);
+ struct path_factor *prev_pf = NULL;
- foreach_ptr(struct path_factor, other, path_factors)
+ foreach_node(GraphElementPattern, gep, path_pattern)
{
- if (gep->variable && other->variable &&
- strcmp(gep->variable, other->variable) == 0)
- {
- if (other->kind != gep->kind)
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("element patterns with same variable name \"%s\" but different element pattern types",
- gep->variable)));
+ struct path_factor *pf = NULL;
- /*
- * If both the element patterns have label expressions, they
- * need to be conjuncted, which is not supported right now.
- *
- * However, an empty label expression means all labels.
- * Conjunction of any label expression with all labels is the
- * expression itself. Hence if only one of the two element
- * patterns has a label expression use that expression.
- */
- if (!other->labelexpr)
- other->labelexpr = gep->labelexpr;
- else if (gep->labelexpr && !equal(other->labelexpr, gep->labelexpr))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("element patterns with same variable name \"%s\" but different label expressions are not supported",
- gep->variable)));
+ /*
+ * Unsupported conditions should have been caught by the parser
+ * itself. We have corresponding Asserts here to document the
+ * assumptions in this code.
+ */
+ Assert(gep->kind == VERTEX_PATTERN || IS_EDGE_PATTERN(gep->kind));
+ Assert(!gep->quantifier);
- /*
- * If two element patterns have the same variable name, they
- * represent the same set of graph elements and hence are
- * constrained by conditions from both the element patterns.
- */
- if (!other->whereClause)
- other->whereClause = gep->whereClause;
- else if (gep->whereClause)
- other->whereClause = (Node *) makeBoolExpr(AND_EXPR,
- list_make2(other->whereClause, gep->whereClause),
- -1);
- pf = other;
- break;
- }
- }
+ foreach_ptr(struct path_factor, other, path_factors)
+ {
+ if (gep->variable && other->variable &&
+ strcmp(gep->variable, other->variable) == 0)
+ {
+ if (other->kind != gep->kind)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("element patterns with same variable name \"%s\" but different element pattern types",
+ gep->variable)));
- if (!pf)
- {
- pf = palloc0_object(struct path_factor);
- pf->factorpos = factorpos++;
- pf->kind = gep->kind;
- pf->labelexpr = gep->labelexpr;
- pf->variable = gep->variable;
- pf->whereClause = gep->whereClause;
-
- path_factors = lappend(path_factors, pf);
- }
+ /*
+ * If both the element patterns have label expressions,
+ * they need to be conjuncted, which is not supported
+ * right now.
+ *
+ * However, an empty label expression means all labels.
+ * Conjunction of any label expression with all labels is
+ * the expression itself. Hence if only one of the two
+ * element patterns has a label expression use that
+ * expression.
+ */
+ if (!other->labelexpr)
+ other->labelexpr = gep->labelexpr;
+ else if (gep->labelexpr && !equal(other->labelexpr, gep->labelexpr))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("element patterns with same variable name \"%s\" but different label expressions are not supported",
+ gep->variable)));
- /*
- * Setup links to the previous path factor in the path.
- *
- * If the previous path factor represents an edge, this path factor
- * represents an adjacent vertex; the source vertex for an edge
- * pointing left or the destination vertex for an edge pointing right.
- * If this path factor represents an edge, the previous path factor
- * represents an adjacent vertex; source vertex for an edge pointing
- * right or the destination vertex for an edge pointing left.
- *
- * Edge pointing in any direction is treated similar to that pointing
- * in right direction here. When constructing a query in
- * generate_query_for_graph_path(), we will try links in both the
- * directions.
- *
- * If multiple edge patterns share the same variable name, they
- * constrain the adjacent vertex patterns since an edge can connect
- * only one pair of vertexes. These adjacent vertex patterns need to
- * be merged even though they have different variables. Such element
- * patterns form a walk of graph where vertex and edges are repeated.
- * For example, in (a)-[b]->(c)<-[b]-(d), (a) and (d) represent the
- * same vertex element. This is slightly harder to implement and
- * probably less useful. Hence not supported for now.
- */
- if (prev_pf)
- {
- if (prev_pf->kind == EDGE_PATTERN_RIGHT || prev_pf->kind == EDGE_PATTERN_ANY)
- {
- Assert(!IS_EDGE_PATTERN(pf->kind));
- if (prev_pf->dest_pf && prev_pf->dest_pf != pf)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
- prev_pf->dest_pf = pf;
- }
- else if (prev_pf->kind == EDGE_PATTERN_LEFT)
- {
- Assert(!IS_EDGE_PATTERN(pf->kind));
- if (prev_pf->src_pf && prev_pf->src_pf != pf)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
- prev_pf->src_pf = pf;
- }
- else
- {
- Assert(prev_pf->kind == VERTEX_PATTERN);
- Assert(IS_EDGE_PATTERN(pf->kind));
+ /*
+ * If two element patterns have the same variable name,
+ * they represent the same set of graph elements and hence
+ * are constrained by conditions from both the element
+ * patterns.
+ */
+ if (!other->whereClause)
+ other->whereClause = gep->whereClause;
+ else if (gep->whereClause)
+ other->whereClause = (Node *) makeBoolExpr(AND_EXPR,
+ list_make2(other->whereClause, gep->whereClause),
+ -1);
+ pf = other;
+ break;
+ }
}
- if (pf->kind == EDGE_PATTERN_RIGHT || pf->kind == EDGE_PATTERN_ANY)
- {
- Assert(!IS_EDGE_PATTERN(prev_pf->kind));
- if (pf->src_pf && pf->src_pf != prev_pf)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
- pf->src_pf = prev_pf;
- }
- else if (pf->kind == EDGE_PATTERN_LEFT)
+ if (!pf)
{
- Assert(!IS_EDGE_PATTERN(prev_pf->kind));
- if (pf->dest_pf && pf->dest_pf != prev_pf)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
- pf->dest_pf = prev_pf;
+ pf = palloc0_object(struct path_factor);
+ pf->factorpos = factorpos++;
+ pf->kind = gep->kind;
+ pf->labelexpr = gep->labelexpr;
+ pf->variable = gep->variable;
+ pf->whereClause = gep->whereClause;
+
+ path_factors = lappend(path_factors, pf);
}
- else
+
+ /*
+ * Setup links to the previous path factor in the path.
+ *
+ * If the previous path factor represents an edge, this path
+ * factor represents an adjacent vertex; the source vertex for an
+ * edge pointing left or the destination vertex for an edge
+ * pointing right. If this path factor represents an edge, the
+ * previous path factor represents an adjacent vertex; source
+ * vertex for an edge pointing right or the destination vertex for
+ * an edge pointing left.
+ *
+ * Edge pointing in any direction is treated similar to that
+ * pointing in right direction here. When constructing a query in
+ * generate_query_for_graph_path(), we will try links in both the
+ * directions.
+ *
+ * If multiple edge patterns share the same variable name, they
+ * constrain the adjacent vertex patterns since an edge can
+ * connect only one pair of vertexes. These adjacent vertex
+ * patterns need to be merged even though they have different
+ * variables. Such element patterns form a walk of graph where
+ * vertex and edges are repeated. For example, in
+ * (a)-[b]->(c)<-[b]-(d), (a) and (d) represent the same vertex
+ * element. This is slightly harder to implement and probably less
+ * useful. Hence not supported for now.
+ */
+ if (prev_pf)
{
- Assert(pf->kind == VERTEX_PATTERN);
- Assert(IS_EDGE_PATTERN(prev_pf->kind));
+ if (prev_pf->kind == EDGE_PATTERN_RIGHT || prev_pf->kind == EDGE_PATTERN_ANY)
+ {
+ Assert(!IS_EDGE_PATTERN(pf->kind));
+ if (prev_pf->dest_pf && prev_pf->dest_pf != pf)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
+ prev_pf->dest_pf = pf;
+ }
+ else if (prev_pf->kind == EDGE_PATTERN_LEFT)
+ {
+ Assert(!IS_EDGE_PATTERN(pf->kind));
+ if (prev_pf->src_pf && prev_pf->src_pf != pf)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
+ prev_pf->src_pf = pf;
+ }
+ else
+ {
+ Assert(prev_pf->kind == VERTEX_PATTERN);
+ Assert(IS_EDGE_PATTERN(pf->kind));
+ }
+
+ if (pf->kind == EDGE_PATTERN_RIGHT || pf->kind == EDGE_PATTERN_ANY)
+ {
+ Assert(!IS_EDGE_PATTERN(prev_pf->kind));
+ if (pf->src_pf && pf->src_pf != prev_pf)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
+ pf->src_pf = prev_pf;
+ }
+ else if (pf->kind == EDGE_PATTERN_LEFT)
+ {
+ Assert(!IS_EDGE_PATTERN(prev_pf->kind));
+ if (pf->dest_pf && pf->dest_pf != prev_pf)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("an edge cannot connect more than two vertexes even in a cyclic pattern"));
+ pf->dest_pf = prev_pf;
+ }
+ else
+ {
+ Assert(pf->kind == VERTEX_PATTERN);
+ Assert(IS_EDGE_PATTERN(prev_pf->kind));
+ }
}
- }
- prev_pf = pf;
+ prev_pf = pf;
+ }
}
/*
diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out
index b579e3df635..43bf3a5b13b 100644
--- a/src/test/regress/expected/graph_table.out
+++ b/src/test/regress/expected/graph_table.out
@@ -93,8 +93,6 @@ SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.addr
ERROR: syntax error at or near "COLUMNS"
LINE 1: ...mers WHERE c.address = 'US')-[IS customer_orders] COLUMNS (c...
^
-SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name)); -- error
-ERROR: multiple path patterns in one GRAPH_TABLE clause not supported
SELECT * FROM GRAPH_TABLE (myshop MATCH COLUMNS (1 AS col)); -- error, empty match clause
ERROR: syntax error at or near "COLUMNS"
LINE 1: SELECT * FROM GRAPH_TABLE (myshop MATCH COLUMNS (1 AS col));
@@ -1022,4 +1020,330 @@ SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE src.vprop1 >
ERROR: subqueries within GRAPH_TABLE reference are not supported
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE out_degree(src.vname) > (SELECT max(out_degree(nname)) FROM GRAPH_TABLE (g1 MATCH (node) COLUMNS (node.vname AS nname))) COLUMNS(src.vname AS sname, dest.vname AS dname));
ERROR: subqueries within GRAPH_TABLE reference are not supported
+-- Multi-pattern path tests (comma-separated path patterns in MATCH)
+-- disconnected patterns: cross product of customers and orders
+-- (3 customers x 3 orders = 9 rows, projecting c.name only)
+SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name));
+ customer_name
+---------------
+ customer1
+ customer2
+ customer3
+ customer1
+ customer2
+ customer3
+ customer1
+ customer2
+ customer3
+(9 rows)
+
+-- disconnected patterns: one side yields no match -> empty result
+SELECT * FROM GRAPH_TABLE (myshop
+ MATCH (c IS customers WHERE c.address = 'NONEXISTENT'), (o IS orders)
+ COLUMNS (c.name AS customer_name));
+ customer_name
+---------------
+(0 rows)
+
+-- Basic multi-pattern: (a)->(b), (b)->(c) where b is shared
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[e1 IS el1]->(b IS vl2),
+ (b)-[e2 IS el2]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY a_name, b_name, c_name;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v22 | v32
+(1 row)
+
+-- Multi-pattern with same start vertex reaching different destinations
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (a)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY a_name, b_name, c_name;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v22 | v22
+ v11 | v22 | v31
+ v11 | v22 | v33
+ v12 | v21 | v21
+ v13 | v23 | v23
+(5 rows)
+
+-- Multi-pattern with three patterns sharing one variable
+SELECT a_name, b2_name, b3_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b2 IS vl2),
+ (a)-[]->(b3 IS vl3)
+ COLUMNS (a.vname AS a_name, b2.vname AS b2_name, b3.vname AS b3_name)
+) ORDER BY a_name, b2_name, b3_name;
+ a_name | b2_name | b3_name
+--------+---------+---------
+ v11 | v22 | v22
+ v11 | v22 | v31
+ v11 | v22 | v33
+ v12 | v21 | v21
+ v13 | v23 | v23
+(5 rows)
+
+-- Same variable with same label (should work)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b IS vl2)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v22 | v32
+(1 row)
+
+-- Same variable with different labels (conflict - what happens?)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b IS vl3)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+ERROR: element patterns with same variable name "b" but different label expressions are not supported
+-- Same variable with label OR expression (b IS vl2|vl3)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2|vl3),
+ (b)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v22 | v32
+ v11 | v33 | v33
+ v11 | v33 | v33
+(3 rows)
+
+-- Disconnected patterns (no shared variables) - should produce cross product
+-- (a)->(b) and (c)->(d) are independent, result is Cartesian product
+-- vl1->vl2 has 3 paths, vl1->vl3 has 3 paths, so cross product = 3 x 3 = 9 rows
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (c IS vl1)-[]->(d IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+ a_name | b_name | c_name | d_name
+--------+--------+--------+--------
+ v11 | v22 | v11 | v22
+ v11 | v22 | v11 | v31
+ v11 | v22 | v11 | v33
+ v11 | v22 | v12 | v21
+ v11 | v22 | v13 | v23
+ v12 | v21 | v11 | v22
+ v12 | v21 | v11 | v31
+ v12 | v21 | v11 | v33
+ v12 | v21 | v12 | v21
+ v12 | v21 | v13 | v23
+ v13 | v23 | v11 | v22
+ v13 | v23 | v11 | v31
+ v13 | v23 | v11 | v33
+ v13 | v23 | v12 | v21
+ v13 | v23 | v13 | v23
+(15 rows)
+
+-- Disconnected patterns: EXPLAIN should show cross join (Nested Loop without join condition)
+EXPLAIN (COSTS OFF) SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (c IS vl1)-[]->(d IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
+ Append
+ -> Merge Join
+ Merge Cond: (v1.id = e1_2.id_1)
+ -> Nested Loop
+ -> Index Scan using v1_pkey on v1
+ -> Materialize
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: ((e1_2_1.id_2_1 = v2_1.id1) AND (e1_2_1.id_2_2 = v2_1.id2))
+ -> Sort
+ Sort Key: e1_2_1.id_2_1, e1_2_1.id_2_2
+ -> Seq Scan on e1_2 e1_2_1
+ -> Sort
+ Sort Key: v2_1.id1, v2_1.id2
+ -> Seq Scan on v2 v2_1
+ -> Index Scan using v1_pkey on v1 v1_1
+ Index Cond: (id = e1_2_1.id_1)
+ -> Sort
+ Sort Key: e1_2.id_1
+ -> Merge Join
+ Merge Cond: ((e1_2.id_2_1 = v2.id1) AND (e1_2.id_2_2 = v2.id2))
+ -> Sort
+ Sort Key: e1_2.id_2_1, e1_2.id_2_2
+ -> Seq Scan on e1_2
+ -> Sort
+ Sort Key: v2.id1, v2.id2
+ -> Seq Scan on v2
+ -> Hash Join
+ Hash Cond: (e1_3.id_3 = v3.id)
+ -> Hash Join
+ Hash Cond: (e1_3.id_1 = v1_3.id)
+ -> Nested Loop
+ -> Seq Scan on e1_3
+ -> Materialize
+ -> Nested Loop
+ -> Merge Join
+ Merge Cond: ((e1_2_2.id_2_1 = v2_2.id1) AND (e1_2_2.id_2_2 = v2_2.id2))
+ -> Sort
+ Sort Key: e1_2_2.id_2_1, e1_2_2.id_2_2
+ -> Seq Scan on e1_2 e1_2_2
+ -> Sort
+ Sort Key: v2_2.id1, v2_2.id2
+ -> Seq Scan on v2 v2_2
+ -> Index Scan using v1_pkey on v1 v1_2
+ Index Cond: (id = e1_2_2.id_1)
+ -> Hash
+ -> Seq Scan on v1 v1_3
+ -> Hash
+ -> Seq Scan on v3
+(49 rows)
+
+-- Disconnected patterns: single vertex patterns (no edges)
+-- vl1 has 3 rows (v1), vl3 has 6 rows (v2+v3), cross product = 18 rows
+SELECT a_name, b_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1), (b IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name)
+) ORDER BY 1, 2;
+ a_name | b_name
+--------+--------
+ v11 | v21
+ v11 | v22
+ v11 | v23
+ v11 | v31
+ v11 | v32
+ v11 | v33
+ v12 | v21
+ v12 | v22
+ v12 | v23
+ v12 | v31
+ v12 | v32
+ v12 | v33
+ v13 | v21
+ v13 | v22
+ v13 | v23
+ v13 | v31
+ v13 | v32
+ v13 | v33
+(18 rows)
+
+-- Disconnected patterns: three independent patterns (3-way cross product)
+-- vl1 has 3 rows, vl2 has 3 rows, vl3 has 6 rows = 54 rows
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1), (b IS vl2), (c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+ a_name | b_name | c_name
+--------+--------+--------
+ v11 | v21 | v21
+ v11 | v21 | v22
+ v11 | v21 | v23
+ v11 | v21 | v31
+ v11 | v21 | v32
+ v11 | v21 | v33
+ v11 | v22 | v21
+ v11 | v22 | v22
+ v11 | v22 | v23
+ v11 | v22 | v31
+ v11 | v22 | v32
+ v11 | v22 | v33
+ v11 | v23 | v21
+ v11 | v23 | v22
+ v11 | v23 | v23
+ v11 | v23 | v31
+ v11 | v23 | v32
+ v11 | v23 | v33
+ v12 | v21 | v21
+ v12 | v21 | v22
+ v12 | v21 | v23
+ v12 | v21 | v31
+ v12 | v21 | v32
+ v12 | v21 | v33
+ v12 | v22 | v21
+ v12 | v22 | v22
+ v12 | v22 | v23
+ v12 | v22 | v31
+ v12 | v22 | v32
+ v12 | v22 | v33
+ v12 | v23 | v21
+ v12 | v23 | v22
+ v12 | v23 | v23
+ v12 | v23 | v31
+ v12 | v23 | v32
+ v12 | v23 | v33
+ v13 | v21 | v21
+ v13 | v21 | v22
+ v13 | v21 | v23
+ v13 | v21 | v31
+ v13 | v21 | v32
+ v13 | v21 | v33
+ v13 | v22 | v21
+ v13 | v22 | v22
+ v13 | v22 | v23
+ v13 | v22 | v31
+ v13 | v22 | v32
+ v13 | v22 | v33
+ v13 | v23 | v21
+ v13 | v23 | v22
+ v13 | v23 | v23
+ v13 | v23 | v31
+ v13 | v23 | v32
+ v13 | v23 | v33
+(54 rows)
+
+-- Mixed: partially connected + disconnected patterns
+-- (a)->(b), (b)->(c) are connected via 'b', but (d) is disconnected
+-- vl1->vl2->vl3 chain + independent vl1 vertex
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b)-[]->(c IS vl3),
+ (d IS vl1)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+ a_name | b_name | c_name | d_name
+--------+--------+--------+--------
+ v11 | v22 | v32 | v11
+ v11 | v22 | v32 | v12
+ v11 | v22 | v32 | v13
+(3 rows)
+
+-- Disconnected patterns: verify row count with different sizes
+-- Using WHERE to filter one side: (a IS vl1 WHERE vprop1 = 10) has 1 row
+-- Cross with (b IS vl3) which has 6 rows = 6 rows
+SELECT a_name, b_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1 WHERE a.vprop1 = 10), (b IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name)
+) ORDER BY 1, 2;
+ a_name | b_name
+--------+--------
+ v11 | v21
+ v11 | v22
+ v11 | v23
+ v11 | v31
+ v11 | v32
+ v11 | v33
+(6 rows)
+
+-- Star pattern: three patterns with shared hub 'a' (A->B, A->C, D->A)
+-- All joined via 'a', NOT a cross product
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (a)-[]->(c IS vl3),
+ (d IS vl2)-[]->(a)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+ a_name | b_name | c_name | d_name
+--------+--------+--------+--------
+ v12 | v21 | v21 | v21
+ v13 | v23 | v23 | v23
+(2 rows)
+
-- leave the objects behind for pg_upgrade/pg_dump tests
diff --git a/src/test/regress/sql/graph_table.sql b/src/test/regress/sql/graph_table.sql
index 4ff98817420..008a18b219b 100644
--- a/src/test/regress/sql/graph_table.sql
+++ b/src/test/regress/sql/graph_table.sql
@@ -89,7 +89,6 @@ SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.addr
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.namex AS customer_name)); -- error
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers|employees WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name)); -- error
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders] COLUMNS (c.name AS customer_name)); -- error
-SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name)); -- error
SELECT * FROM GRAPH_TABLE (myshop MATCH COLUMNS (1 AS col)); -- error, empty match clause
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)->{1,2}(o IS orders) COLUMNS (c.name AS customer_name)); -- error
SELECT * FROM GRAPH_TABLE (myshop MATCH ((c IS customers)->(o IS orders)) COLUMNS (c.name));
@@ -582,4 +581,117 @@ SELECT * FROM customers co WHERE co.customer_id = (SELECT customer_id FROM GRAPH
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE src.vprop1 > (SELECT max(v1.vprop1) FROM v1) COLUMNS(src.vname AS sname, dest.vname AS dname));
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE out_degree(src.vname) > (SELECT max(out_degree(nname)) FROM GRAPH_TABLE (g1 MATCH (node) COLUMNS (node.vname AS nname))) COLUMNS(src.vname AS sname, dest.vname AS dname));
+-- Multi-pattern path tests (comma-separated path patterns in MATCH)
+-- disconnected patterns: cross product of customers and orders
+-- (3 customers x 3 orders = 9 rows, projecting c.name only)
+SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name));
+
+-- disconnected patterns: one side yields no match -> empty result
+SELECT * FROM GRAPH_TABLE (myshop
+ MATCH (c IS customers WHERE c.address = 'NONEXISTENT'), (o IS orders)
+ COLUMNS (c.name AS customer_name));
+
+-- Basic multi-pattern: (a)->(b), (b)->(c) where b is shared
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[e1 IS el1]->(b IS vl2),
+ (b)-[e2 IS el2]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY a_name, b_name, c_name;
+
+-- Multi-pattern with same start vertex reaching different destinations
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (a)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY a_name, b_name, c_name;
+
+-- Multi-pattern with three patterns sharing one variable
+SELECT a_name, b2_name, b3_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b2 IS vl2),
+ (a)-[]->(b3 IS vl3)
+ COLUMNS (a.vname AS a_name, b2.vname AS b2_name, b3.vname AS b3_name)
+) ORDER BY a_name, b2_name, b3_name;
+
+-- Same variable with same label (should work)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b IS vl2)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+
+-- Same variable with different labels (conflict - what happens?)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b IS vl3)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+
+-- Same variable with label OR expression (b IS vl2|vl3)
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2|vl3),
+ (b)-[]->(c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+
+-- Disconnected patterns (no shared variables) - should produce cross product
+-- (a)->(b) and (c)->(d) are independent, result is Cartesian product
+-- vl1->vl2 has 3 paths, vl1->vl3 has 3 paths, so cross product = 3 x 3 = 9 rows
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (c IS vl1)-[]->(d IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+
+-- Disconnected patterns: EXPLAIN should show cross join (Nested Loop without join condition)
+EXPLAIN (COSTS OFF) SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (c IS vl1)-[]->(d IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+);
+
+-- Disconnected patterns: single vertex patterns (no edges)
+-- vl1 has 3 rows (v1), vl3 has 6 rows (v2+v3), cross product = 18 rows
+SELECT a_name, b_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1), (b IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name)
+) ORDER BY 1, 2;
+
+-- Disconnected patterns: three independent patterns (3-way cross product)
+-- vl1 has 3 rows, vl2 has 3 rows, vl3 has 6 rows = 54 rows
+SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1), (b IS vl2), (c IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name)
+) ORDER BY 1, 2, 3;
+
+-- Mixed: partially connected + disconnected patterns
+-- (a)->(b), (b)->(c) are connected via 'b', but (d) is disconnected
+-- vl1->vl2->vl3 chain + independent vl1 vertex
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (b)-[]->(c IS vl3),
+ (d IS vl1)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+
+-- Disconnected patterns: verify row count with different sizes
+-- Using WHERE to filter one side: (a IS vl1 WHERE vprop1 = 10) has 1 row
+-- Cross with (b IS vl3) which has 6 rows = 6 rows
+SELECT a_name, b_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1 WHERE a.vprop1 = 10), (b IS vl3)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name)
+) ORDER BY 1, 2;
+
+-- Star pattern: three patterns with shared hub 'a' (A->B, A->C, D->A)
+-- All joined via 'a', NOT a cross product
+SELECT a_name, b_name, c_name, d_name FROM GRAPH_TABLE (g1
+ MATCH (a IS vl1)-[]->(b IS vl2),
+ (a)-[]->(c IS vl3),
+ (d IS vl2)-[]->(a)
+ COLUMNS (a.vname AS a_name, b.vname AS b_name,
+ c.vname AS c_name, d.vname AS d_name)
+) ORDER BY 1, 2, 3, 4;
+
-- leave the objects behind for pg_upgrade/pg_dump tests
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL/PGQ: Support multi-pattern path matching in GRAPH_TABLE
In-Reply-To: <CAAAe_zAxTXev97Zhc=Z+LH6ZWQ8UbHQ5LAo_iVkqHm=49SZS+g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox