Hi Ashutosh, Thank you for reviewing the path pattern list implementation. I've added comprehensive tests to address your concerns below.
2025년 12월 30일 (화) PM 6:50, Ashutosh Bapat <[email protected]>님이 작성: > Hi Henson, > > > 3. Multiple path patterns > > - Syntax: MATCH (a)->(b), (b)->(c) > > - Implements SQL/PGQ standard feature (was TODO) > > > > This path pattern list feature is discussed in section 10.4 of the > standard. I think supporting this feature would be a good addition to > SQL/PGQ compliance. The current patchset is already large. I (and > AFAIK Peter as well) am focusing on getting that patchset in a > committable shape. Once committed, it will facilitate adding features > like path pattern list, quantified path patterns, label conjunction > etc. as incremental relatively smaller commits. > Agreed. Now is not the time to add this feature - the current patchset should be stabilized first. > Since you could build one of the complex features without changing a > lot of earlier implementation, it validates that the basic > implementation is extensible and yet robust. Thanks for that > validation. > Thank you. That was indeed the intent. > The way you have implemented it, it can lead to a path pattern with > two vertex patterns next to each other without an interleaving edge > pattern. That would throw an error or assertion. This does NOT throw an error. Single vertex patterns produce a Cartesian product correctly (vl1 has 3 rows, vl3 has 6 rows = 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 ... (18 rows) -- 3 x 6 = 18, confirming cross join > If you try path > patterns without a common vertex, you will get that error. Disconnected patterns (no shared variables) work correctly and produce a Cartesian product (vl1->vl2 has 3 paths, vl1->vl3 has 5 paths = 15 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 ... (15 rows) -- 3 x 5 = 15, confirming cross join > That is not > covered by your tests. I have now added comprehensive tests: Disconnected patterns (cross product): 1. Two independent paths - 3 x 5 = 15 rows 2. EXPLAIN verification - confirms Nested Loop (cross join) 3. Single vertex patterns - 3 x 6 = 18 rows 4. Three-way cross product - 3 x 3 x 6 = 54 rows 5. Mixed connected + disconnected - 1 path x 3 vertices = 3 rows 6. Filtered cross product - 1 x 6 = 6 rows Connected patterns (join via shared variable): 7. Star pattern: A->B, A->C, D->A with shared hub 'a' - 2 rows (NOT cross product) MATCH (a IS vl1)-[]->(b IS vl2), (a)-[]->(c IS vl3), (d IS vl2)-[]->(a) a_name | b_name | c_name | d_name --------+--------+--------+-------- v12 | v21 | v21 | v21 v13 | v23 | v23 | v23 (2 rows) -- all joined via 'a', NOT cross product All row counts match expected behavior. > In such cases, the paths should end up being > full-joined rather than concatenated. I don't see it being implemented > that way. The implementation DOES produce cross joins for disconnected patterns. The EXPLAIN output confirms this: EXPLAIN (COSTS OFF) SELECT ... FROM GRAPH_TABLE (g1 MATCH (a IS vl1)-[]->(b IS vl2), (c IS vl1)-[]->(d IS vl3) ... ); QUERY PLAN ----------------------------------------------- Append -> Merge Join ... -> Nested Loop <-- Cross join here -> Index Scan ... -> Materialize -> Nested Loop ... The Nested Loop without join condition IS the cross join (Cartesian product). > I think more work is needed here. > All test cases pass. This work was intended to validate extensibility of the current design, not to push for immediate inclusion at this stage. > [1] > https://www.postgresql.org/message-id/caexhw5sr+djpcfw2oqxufppqpks8qmsivaayhribdfancx8...@mail.gmail.com > > -- > Best Wishes, > Ashutosh Bapat > Best regards, Henson
From 5d74b7157270f056556d88e700396d1cbd82bbb1 Mon Sep 17 00:00:00 2001 From: Henson Choi <[email protected]> Date: Fri, 26 Dec 2025 20:17:29 +0900 Subject: [PATCH] SQL/PGQ: Support multi-pattern path matching in GRAPH_TABLE Add support for comma-separated path patterns in MATCH clause, e.g.: MATCH (a)->(b), (b)->(c) Path patterns with shared variables are merged into a single join. Disconnected patterns (no shared variables) produce a Cartesian product, consistent with SQL/PGQ standard and Neo4j Cypher semantics. The parser already supported this syntax; this commit enables execution by merging multiple path patterns instead of rejecting them. --- src/backend/rewrite/rewriteGraphTable.c | 34 +- src/test/regress/expected/graph_table.out | 375 +++++++++++++++++++++- src/test/regress/sql/graph_table.sql | 138 +++++++- 3 files changed, 539 insertions(+), 8 deletions(-) diff --git a/src/backend/rewrite/rewriteGraphTable.c b/src/backend/rewrite/rewriteGraphTable.c index 44bdc8a1f20..1420786fbb4 100644 --- a/src/backend/rewrite/rewriteGraphTable.c +++ b/src/backend/rewrite/rewriteGraphTable.c @@ -121,16 +121,40 @@ rewriteGraphTable(Query *parsetree, int rt_index) { RangeTblEntry *rte; Query *graph_table_query; - List *path_pattern; List *pathqueries = NIL; + int num_patterns; rte = rt_fetch(rt_index, parsetree->rtable); + num_patterns = list_length(rte->graph_pattern->path_pattern_list); - if (list_length(rte->graph_pattern->path_pattern_list) != 1) - elog(ERROR, "unsupported path pattern list length"); + if (num_patterns == 1) + { + /* Single path pattern - original behavior */ + List *path_pattern = linitial(rte->graph_pattern->path_pattern_list); + + pathqueries = generate_queries_for_path_pattern(rte, path_pattern); + } + else + { + /* + * Multiple path patterns - merge all element patterns into a single + * combined path pattern. Variables with the same name will be merged + * by generate_queries_for_path_pattern(). + */ + List *combined_pattern = NIL; + ListCell *lc; + + foreach(lc, rte->graph_pattern->path_pattern_list) + { + List *path_pattern = (List *) lfirst(lc); + + combined_pattern = list_concat(combined_pattern, + list_copy(path_pattern)); + } + + pathqueries = generate_queries_for_path_pattern(rte, combined_pattern); + } - path_pattern = linitial(rte->graph_pattern->path_pattern_list); - pathqueries = generate_queries_for_path_pattern(rte, path_pattern); graph_table_query = generate_union_from_pathqueries(&pathqueries); AcquireRewriteLocks(graph_table_query, true, false); diff --git a/src/test/regress/expected/graph_table.out b/src/test/regress/expected/graph_table.out index f2bb3512a2d..812c1ed836f 100644 --- a/src/test/regress/expected/graph_table.out +++ b/src/test/regress/expected/graph_table.out @@ -93,8 +93,11 @@ 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: unsupported path pattern list length +SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name)); -- disconnected patterns: cross product with empty orders table + customer_name +--------------- +(0 rows) + SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)->{1,2}(o IS orders) COLUMNS (c.name AS customer_name)); -- error ERROR: element pattern quantifier not supported yet -- a property graph can be referenced only from within GRAPH_TABLE clause. @@ -1407,4 +1410,372 @@ SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers) COLUMNS (PROPERTY_NAMES ERROR: PROPERTY_NAMES() argument must be an element variable LINE 1: ...APH_TABLE (myshop MATCH (c IS customers) COLUMNS (PROPERTY_N... ^ +-- Multi-pattern path tests (comma-separated path patterns with shared variables) +-- 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) + +-- Multi-pattern with LABELS() filtering in WHERE clause +-- Filter shared variable b by label using LABELS() function +EXPLAIN (COSTS OFF) SELECT a_name, b_name, b_labels, c_name FROM GRAPH_TABLE (g1 + MATCH (a IS vl1)-[]->(b IS vl2|vl3), + (b)-[]->(c IS vl3) + WHERE 'vl2' = ANY(LABELS(b)) + COLUMNS (a.vname AS a_name, b.vname AS b_name, LABELS(b) AS b_labels, c.vname AS c_name) +); + QUERY PLAN +------------------------------------------------------------------------------------------- + Nested Loop + -> Nested Loop + -> Hash Join + Hash Cond: ((e2_3.id_2_1 = v2.id1) AND (e2_3.id_2_2 = v2.id2)) + -> Seq Scan on e2_3 + -> Hash + -> 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 + -> Index Scan using v1_pkey on v1 + Index Cond: (id = e1_2.id_1) + -> Index Scan using v3_pkey on v3 + Index Cond: (id = e2_3.id_3) +(18 rows) + +SELECT a_name, b_name, b_labels, c_name FROM GRAPH_TABLE (g1 + MATCH (a IS vl1)-[]->(b IS vl2|vl3), + (b)-[]->(c IS vl3) + WHERE 'vl2' = ANY(LABELS(b)) + COLUMNS (a.vname AS a_name, b.vname AS b_name, LABELS(b) AS b_labels, c.vname AS c_name) +) ORDER BY 1, 2, 3; + a_name | b_name | b_labels | c_name +--------+--------+--------------+-------- + v11 | v22 | {l1,vl2,vl3} | v32 +(1 row) + +-- Multi-pattern with LABELS() filtering - no common labels (should prune all) +-- b IS vl2|vl3 but WHERE filters by 'vl1' which has no overlap +EXPLAIN (COSTS OFF) SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1 + MATCH (a IS vl1)-[]->(b IS vl2|vl3), + (b)-[]->(c IS vl3) + WHERE 'vl1' = ANY(LABELS(b)) + COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name) +); + QUERY PLAN +--------------------------------- + Result + Replaces: Scan on graph_table + One-Time Filter: false +(3 rows) + +SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1 + MATCH (a IS vl1)-[]->(b IS vl2|vl3), + (b)-[]->(c IS vl3) + WHERE 'vl1' = ANY(LABELS(b)) + 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 +--------+--------+-------- +(0 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 e8658348b5d..08c1ff685b9 100644 --- a/src/test/regress/sql/graph_table.sql +++ b/src/test/regress/sql/graph_table.sql @@ -89,7 +89,7 @@ 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 (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name)); -- disconnected patterns: cross product with empty orders table SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)->{1,2}(o IS orders) COLUMNS (c.name AS customer_name)); -- error -- a property graph can be referenced only from within GRAPH_TABLE clause. @@ -780,4 +780,140 @@ SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers) COLUMNS (PROPERTY_NAMES -- PROPERTY_NAMES() error: non-variable argument SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers) COLUMNS (PROPERTY_NAMES(123))); +-- Multi-pattern path tests (comma-separated path patterns with shared variables) +-- 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; + +-- Multi-pattern with LABELS() filtering in WHERE clause +-- Filter shared variable b by label using LABELS() function +EXPLAIN (COSTS OFF) SELECT a_name, b_name, b_labels, c_name FROM GRAPH_TABLE (g1 + MATCH (a IS vl1)-[]->(b IS vl2|vl3), + (b)-[]->(c IS vl3) + WHERE 'vl2' = ANY(LABELS(b)) + COLUMNS (a.vname AS a_name, b.vname AS b_name, LABELS(b) AS b_labels, c.vname AS c_name) +); + +SELECT a_name, b_name, b_labels, c_name FROM GRAPH_TABLE (g1 + MATCH (a IS vl1)-[]->(b IS vl2|vl3), + (b)-[]->(c IS vl3) + WHERE 'vl2' = ANY(LABELS(b)) + COLUMNS (a.vname AS a_name, b.vname AS b_name, LABELS(b) AS b_labels, c.vname AS c_name) +) ORDER BY 1, 2, 3; + +-- Multi-pattern with LABELS() filtering - no common labels (should prune all) +-- b IS vl2|vl3 but WHERE filters by 'vl1' which has no overlap +EXPLAIN (COSTS OFF) SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1 + MATCH (a IS vl1)-[]->(b IS vl2|vl3), + (b)-[]->(c IS vl3) + WHERE 'vl1' = ANY(LABELS(b)) + COLUMNS (a.vname AS a_name, b.vname AS b_name, c.vname AS c_name) +); + +SELECT a_name, b_name, c_name FROM GRAPH_TABLE (g1 + MATCH (a IS vl1)-[]->(b IS vl2|vl3), + (b)-[]->(c IS vl3) + WHERE 'vl1' = ANY(LABELS(b)) + 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 -- 2.50.1 (Apple Git-155)
