This is an automated email from the ASF dual-hosted git repository.
jgemignani pushed a commit to branch PG14
in repository https://gitbox.apache.org/repos/asf/age.git
The following commit(s) were added to refs/heads/PG14 by this push:
new 521838b6 Implement Constraints on Subqueries (#1791)
521838b6 is described below
commit 521838b6061615fa24642eafa3f995805404a5e9
Author: Dehowe Feng <[email protected]>
AuthorDate: Fri Apr 26 08:50:57 2024 +0800
Implement Constraints on Subqueries (#1791)
* Implement COUNT Subquery with PG specifications
Implements a version of COUNT subquery that evaluates the
underlying subquery according to Postgres subquery specifications.
Modifies the grammar to accept all valid input for both COUNT
and EXISTS subqueries.
Add regression tests related to COUNT
Modify the subquery regression tests to accomodate changes
introduced in this update.
* Implement Constraints on Subqueries
Added logic to support constraints on subqueries.
Added an expression tree walker to traverse expr trees. This logic
can be expanded to search expr trees for other exprs outside of
subqueries as well.
Changed OPTIONAL MATCH logic to accomodate for subqueries.
Changed the grammar for COUNT () functions to use a keyword.
This avoids grammatical errors with other components.
Add regression tests for subqueries as well as some for the expr
tree walker. Reformatted previous regression tests.
---
regress/expected/cypher_subquery.out | 944 +++++++++++++++++++++++++----------
regress/sql/cypher_subquery.sql | 503 +++++++++++++------
src/backend/nodes/cypher_outfuncs.c | 1 +
src/backend/parser/cypher_analyze.c | 402 +++++++++++++++
src/backend/parser/cypher_clause.c | 56 ++-
src/backend/parser/cypher_gram.y | 114 ++++-
src/include/nodes/cypher_nodes.h | 1 +
src/include/parser/cypher_analyze.h | 10 +
src/include/parser/cypher_kwlist.h | 1 +
9 files changed, 1564 insertions(+), 468 deletions(-)
diff --git a/regress/expected/cypher_subquery.out
b/regress/expected/cypher_subquery.out
index 1b21319b..559f0c67 100644
--- a/regress/expected/cypher_subquery.out
+++ b/regress/expected/cypher_subquery.out
@@ -1,355 +1,759 @@
LOAD 'age';
SET search_path TO ag_catalog;
-SELECT * FROM create_graph('exists_subquery');
-NOTICE: graph "exists_subquery" has been created
+SELECT * FROM create_graph('subquery');
+NOTICE: graph "subquery" has been created
create_graph
--------------
(1 row)
-SELECT * FROM cypher('exists_subquery', $$
-
CREATE (:person {name: "Briggite", age: 32})-[:knows]->(:person {name:
"Takeshi", age: 28}),
-
(:person {name: "Faye", age: 25})-[:knows]->(:person {name: "Tony",
age: 34})-[:loved]->(:person {name : "Valerie", age: 33}),
-
(:person {name: "Calvin", age: 6})-[:knows]->(:pet {name: "Hobbes"}),
-
(:person {name: "Charlie", age: 8})-[:knows]->(:pet {name :
"Snoopy"})
-
$$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$
+ CREATE
(:person {name: "Briggite", age: 32})-[:knows]->(:person {name: "Takeshi", age:
28}),
+
(:person {name: "Chan", age: 45})<-[:knows]-(:person {name: "Faye", age:
25})-[:knows]->
+
(:person {name: "Tony", age: 34})-[:loved]->(:person {name : "Valerie", age:
33}),
+
(:person {name: "Calvin", age: 6})-[:knows]->(:pet {name: "Hobbes"}),
+
(:person {name: "Charlie", age: 8})-[:knows]->(:pet {name : "Snoopy"}),
+
(:pet {name: "Odie"})<-[:knows]-(:person {name: "Jon", age:
29})-[:knows]->(:pet {name: "Garfield"})
+ $$) AS (result
agtype);
result
--------
(0 rows)
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a) RETURN (a) $$) AS (result
agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a) RETURN (a) $$) AS (result
agtype);
result
---------------------------------------------------------------------------------------------------
{"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
{"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131971, "label": "person", "properties": {"age": 45, "name":
"Chan"}}::vertex
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
+ {"id": 844424930131974, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
{"id": 1688849860263937, "label": "pet", "properties": {"name":
"Hobbes"}}::vertex
{"id": 1688849860263938, "label": "pet", "properties": {"name":
"Snoopy"}}::vertex
-(9 rows)
+ {"id": 1688849860263939, "label": "pet", "properties": {"name":
"Odie"}}::vertex
+ {"id": 1688849860263940, "label": "pet", "properties": {"name":
"Garfield"}}::vertex
+(13 rows)
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {(a:person)-[]->(:pet)}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {(a:person)-[]->(:pet)}
+ RETURN
(a) $$) AS (result agtype);
result
-------------------------------------------------------------------------------------------------
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(2 rows)
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(3 rows)
---trying to use b when not defined, should fail
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
+--trying to use b when not defined, should create pattern
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
WHERE EXISTS {(a:person)-[]->(b:pet)}
RETURN (a) $$) AS (result agtype);
-ERROR: variable `b` does not exist
-LINE 2: WHERE EXISTS {(a:person)-[]->(b:pet)}
- ^
+ result
+-------------------------------------------------------------------------------------------------
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(3 rows)
+
--query inside
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {MATCH (a:person)-[]->(b:pet) RETURN b}
-
RETURN (a) $$) AS (result agtype);
- result
----------------------------------------------------------------------------------------------------
- {"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
- {"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {MATCH (a:person)-[]->(b:pet) RETURN b}
+ RETURN
(a) $$) AS (result agtype);
+ result
+-------------------------------------------------------------------------------------------------
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(3 rows)
--repeat variable in match
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)
-
WHERE a.name = 'Takeshi'
-
RETURN a
-
}
-
RETURN (a) $$) AS (result agtype);
- result
----------------------------------------------------------------------------------------------------
- {"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)
+
WHERE a.name = 'Takeshi'
+
RETURN a
+
}
+ RETURN
(a) $$) AS (result agtype);
+ result
+--------------------------------------------------------------------------------------------------
{"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
+(1 row)
--query inside, with WHERE
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {MATCH (a:person)-[]->(b:pet)
-
WHERE b.name = 'Briggite'
-
RETURN b}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {MATCH (a:person)-[]->(b:pet)
+
WHERE b.name = 'Briggite'
+
RETURN b}
+ RETURN
(a) $$) AS (result agtype);
result
--------
(0 rows)
--no return
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {MATCH (a:person)-[]->(b:pet)
-
WHERE a.name = 'Calvin'}
-
RETURN (a) $$) AS (result agtype);
- result
----------------------------------------------------------------------------------------------------
- {"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
- {"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {MATCH (a:person)-[]->(b:pet)
+
WHERE a.name = 'Calvin'}
+ RETURN
(a) $$) AS (result agtype);
+ result
+------------------------------------------------------------------------------------------------
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+(1 row)
--union
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)-[]->(b:pet)
-
WHERE b.name = 'Hobbes'
-
RETURN b
-
UNION
-
MATCH (c:person)-[]->(d:person)
-
RETURN c
-
}
-
RETURN (a) $$) AS (result agtype);
- result
----------------------------------------------------------------------------------------------------
- {"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
- {"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)-[]->(b:pet)
+
WHERE b.name = 'Hobbes'
+
RETURN b
+
UNION
+
MATCH (a:person)-[]->(c:person)
+
WHERE a.name = 'Faye'
+
RETURN c
+
}
+ RETURN
(a) $$) AS (result agtype);
+ result
+------------------------------------------------------------------------------------------------
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+(2 rows)
-- union, mismatched var, should fail
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)-[]->(b:pet)
-
WHERE b.name = 'Snoopy'
-
RETURN c
-
UNION
-
MATCH (c:person)-[]->(d:person)
-
RETURN c
-
}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)-[]->(b:pet)
+
WHERE b.name = 'Snoopy'
+
RETURN c
+
UNION
+
MATCH (c:person)-[]->(d:person)
+
RETURN c
+
}
+ RETURN
(a) $$) AS (result agtype);
ERROR: could not find rte for c
-LINE 5: RETURN c
- ^
---union, no returns
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)-[]->(b:pet)
-
WHERE a.name = 'Charlie'
-
UNION
-
MATCH (c:person)-[]->(d:person)
-
}
-
RETURN (a) $$) AS (result agtype);
- result
----------------------------------------------------------------------------------------------------
- {"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
- {"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
-
---union, mismatched returns, should fail
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)-[]->(b:pet)
-
WHERE a.name = 'Faye'
-
RETURN a
-
UNION
-
MATCH (c:person)-[]->(d:person)
-
}
-
RETURN (a) $$) AS (result agtype);
+LINE 5: RETURN c
+ ^
+--union, no returns, not yet implemented, should error out
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)-[]->(b:pet)
+
WHERE a.name = 'Charlie'
+
UNION
+
MATCH (c:person)-[]->(d:person)
+
}
+ RETURN
(a) $$) AS (result agtype);
+ERROR: Subquery UNION without returns not yet implemented
+LINE 5: UNION
+ ^
+--union, only one has return, should fail
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)-[]->(b:pet)
+
WHERE a.name = 'Faye'
+
RETURN a
+
UNION
+
MATCH (c:person)-[]->(d:person)
+
}
+ RETURN
(a) $$) AS (result agtype);
ERROR: syntax error at or near "}"
-LINE 8: }
- ^
---nesting
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE c.name =
'Takeshi'
-
RETURN c
-
}
-
}
-
RETURN (a) $$) AS (result agtype);
+LINE 8: }
+ ^
+--nesting (should return everything since a isn't sent all the way down)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (b:person)
+
WHERE EXISTS {
+
MATCH (c:person)
+
WHERE c.name = 'Takeshi'
+
RETURN c
+
}
+
RETURN b
+
}
+ RETURN
(a) $$) AS (result agtype);
result
---------------------------------------------------------------------------------------------------
{"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
{"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
+ {"id": 844424930131971, "label": "person", "properties": {"age": 45, "name":
"Chan"}}::vertex
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
+ {"id": 844424930131974, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(9 rows)
+
+--nesting same var multiple layers
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)
+
WHERE EXISTS {
+
MATCH (a:person)
+
WHERE a.name = 'Takeshi'
+
}
+
}
+ RETURN
(a) $$) AS (result agtype);
+ result
+--------------------------------------------------------------------------------------------------
+ {"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
+(1 row)
--nesting, accessing var in outer scope
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE b = c
-
RETURN c
-
}
-
}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a)
+ WHERE
EXISTS {
+
MATCH (b)
+
WHERE EXISTS {
+
MATCH (c:person)
+
WHERE b = c
+
}
+
}
+ RETURN
(a) $$) AS (result agtype);
result
---------------------------------------------------------------------------------------------------
{"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
{"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
+ {"id": 844424930131971, "label": "person", "properties": {"age": 45, "name":
"Chan"}}::vertex
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
+ {"id": 844424930131974, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+ {"id": 1688849860263937, "label": "pet", "properties": {"name":
"Hobbes"}}::vertex
+ {"id": 1688849860263938, "label": "pet", "properties": {"name":
"Snoopy"}}::vertex
+ {"id": 1688849860263939, "label": "pet", "properties": {"name":
"Odie"}}::vertex
+ {"id": 1688849860263940, "label": "pet", "properties": {"name":
"Garfield"}}::vertex
+(13 rows)
--nesting, accessing indirection in outer scope
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE b.name =
'Takeshi'
-
RETURN c
-
}
-
}
+
MATCH (b:person)
+
WHERE EXISTS {
+
MATCH (c:person)
+
WHERE b.name =
'Takeshi'
+
RETURN c
+
}
+
}
RETURN (a) $$) AS (result agtype);
result
---------------------------------------------------------------------------------------------------
{"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
{"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
+ {"id": 844424930131971, "label": "person", "properties": {"age": 45, "name":
"Chan"}}::vertex
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
+ {"id": 844424930131974, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(9 rows)
--nesting, accessing var 2+ levels up
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE a.name =
'Takeshi'
-
RETURN c
-
}
-
}
+
MATCH (b:person)
+
WHERE EXISTS {
+
MATCH (a:person)
+
WHERE a.name =
'Takeshi'
+
RETURN a
+
}
+
}
RETURN (a) $$) AS (result agtype);
- result
----------------------------------------------------------------------------------------------------
- {"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
+ result
+--------------------------------------------------------------------------------------------------
{"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
+(1 row)
--nesting, accessing indirection 2+ levels up
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE a = b
-
RETURN c
-
}
-
}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (b:person)
+
WHERE EXISTS {
+
MATCH (a:person)
+
WHERE a = b
+
RETURN a
+
}
+
}
+ RETURN
(a) $$) AS (result agtype);
result
---------------------------------------------------------------------------------------------------
{"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
{"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
-(7 rows)
+ {"id": 844424930131971, "label": "person", "properties": {"age": 45, "name":
"Chan"}}::vertex
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
+ {"id": 844424930131974, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(9 rows)
--EXISTS outside of WHERE
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
RETURN a, EXISTS {(a:person)-[]->(:pet)}
-
$$) AS (a agtype, exists agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a, EXISTS {(a:person)-[]->(:pet)}
+ $$) AS
(a agtype, exists agtype);
a
| exists
---------------------------------------------------------------------------------------------------+--------
{"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex | false
{"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex | false
- {"id": 844424930131971, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex | false
- {"id": 844424930131972, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex | false
- {"id": 844424930131973, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex | false
- {"id": 844424930131974, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex | true
- {"id": 844424930131975, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex | true
-(7 rows)
+ {"id": 844424930131971, "label": "person", "properties": {"age": 45, "name":
"Chan"}}::vertex | false
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex | false
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex | false
+ {"id": 844424930131974, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex | false
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex | true
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex | true
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex | true
+(9 rows)
--Var doesnt exist in outside scope, should fail
-SELECT * FROM cypher('exists_subquery', $$ RETURN 1,
-
EXISTS {
-
MATCH (b:person)-[]->(:pet)
-
RETURN a
-
}
-
$$) AS (a agtype, exists agtype);
+SELECT * FROM cypher('subquery', $$ RETURN 1,
+
EXISTS {
+
MATCH (b:person)-[]->(:pet)
+
RETURN a
+
}
+ $$) AS
(a agtype, exists agtype);
ERROR: could not find rte for a
-LINE 4: RETURN a
- ^
+LINE 4: RETURN a
+ ^
+--- COUNT
+--count pattern subquery in where
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT {(a:person)} > 1
+ RETURN
(a) $$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+--count pattern in return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
COUNT{(a)-[]->(:pet)} $$) AS (result agtype);
+ result
+--------
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 1
+ 1
+ 2
+(9 rows)
+
+--count pattern with WHERE
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
COUNT{(a)-[]->(b:pet)
+
WHERE b.name = 'Hobbes'}
+ $$) AS
(result agtype);
+ result
+--------
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 1
+ 0
+ 0
+(9 rows)
+
+--solo match in where
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[]-()} > 1
+ RETURN
a $$) AS (result agtype);
+ result
+-----------------------------------------------------------------------------------------------
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(3 rows)
+
+--match where person has more than one pet
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[]-(:pet)} > 1
+ RETURN
a $$) AS (result agtype);
+ result
+----------------------------------------------------------------------------------------------
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(1 row)
+
+--match on labels
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[:knows]-()} > 1
+ RETURN
a $$) AS (result agtype);
+ result
+-----------------------------------------------------------------------------------------------
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(2 rows)
+
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[:knows]-(:pet)} > 1
+ RETURN
a $$) AS (result agtype);
+ result
+----------------------------------------------------------------------------------------------
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(1 row)
+
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[:knows]-(:person)} > 1
+ RETURN
a $$) AS (result agtype);
+ result
+-----------------------------------------------------------------------------------------------
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+(1 row)
+
+--solo match in return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
COUNT{MATCH (a)} $$) AS (result agtype);
+ result
+--------
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+(9 rows)
+
+--match return in where
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a) RETURN a} > 1 RETURN a $$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+--match return in return with return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
COUNT{MATCH (a) RETURN a} $$) AS (result agtype);
+ result
+--------
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+(9 rows)
+
+--match where return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, a.age, COUNT{MATCH (a)
+
WHERE a.age > 25
+
RETURN a.name} $$)
+ AS
(person agtype, age agtype, count agtype);
+ person | age | count
+------------+-----+-------
+ "Briggite" | 32 | 1
+ "Takeshi" | 28 | 1
+ "Chan" | 45 | 1
+ "Faye" | 25 | 0
+ "Tony" | 34 | 1
+ "Valerie" | 33 | 1
+ "Calvin" | 6 | 0
+ "Charlie" | 8 | 0
+ "Jon" | 29 | 1
+(9 rows)
+
+--counting number of relationships per node
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, COUNT{(a)-[]-()} $$) AS (name agtype, count agtype);
+ name | count
+------------+-------
+ "Briggite" | 1
+ "Takeshi" | 1
+ "Chan" | 1
+ "Faye" | 2
+ "Tony" | 2
+ "Valerie" | 1
+ "Calvin" | 1
+ "Charlie" | 1
+ "Jon" | 2
+(9 rows)
+
+--nested counts
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, COUNT{MATCH (a)
+
WHERE COUNT {MATCH (a)
+
WHERE a.age < 23
+
RETURN a } > 0 } $$)
+ AS
(name agtype, count agtype);
+ name | count
+------------+-------
+ "Briggite" | 0
+ "Takeshi" | 0
+ "Chan" | 0
+ "Faye" | 0
+ "Tony" | 0
+ "Valerie" | 0
+ "Calvin" | 1
+ "Charlie" | 1
+ "Jon" | 0
+(9 rows)
+
+--incorrect variable reference
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, COUNT{MATCH (a) RETURN b} $$)
+ AS
(name agtype, count agtype);
+ERROR: could not find rte for b
+LINE 2: RETURN a.name, COUNT{MATCH (a) RETURN b} $$)
+ ^
+--incorrect nested variable reference
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, COUNT{MATCH (a)
+
WHERE COUNT {MATCH (b) RETURN b } > 1
+
RETURN b} $$)
+ AS
(name agtype, count agtype);
+ERROR: could not find rte for b
+LINE 4: RETURN b} $$)
+ ^
+--count nested with exists
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name,
+
COUNT{MATCH (a) WHERE EXISTS {MATCH (a)-[]-(:pet)
+
RETURN a }} $$)
+ AS
(name agtype, count agtype);
+ name | count
+------------+-------
+ "Briggite" | 0
+ "Takeshi" | 0
+ "Chan" | 0
+ "Faye" | 0
+ "Tony" | 0
+ "Valerie" | 0
+ "Calvin" | 1
+ "Charlie" | 1
+ "Jon" | 1
+(9 rows)
+
+--
+-- expression tree walker additional tests. want to test the nesting
capabilties of the expr tree walker
+--
+--BoolExpr
+-- with comparison
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {(a:person)-[]->(:pet)}
+ AND
a.name = 'Odie'
+ RETURN
(a) $$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+-- BoolExpr two subqueries
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {(a:person)-[]->(:pet)}
+ OR
EXISTS {(a:person)-[]->(:person)}
+ RETURN
(a) $$) AS (result agtype);
+ result
+---------------------------------------------------------------------------------------------------
+ {"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(6 rows)
+
+-- Nested BoolExpr
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {(a:person)-[]->(:pet)
+ WHERE
a.name = 'Charlie'}
+ AND
EXISTS {(a:person)-[]->(:person)}
+ RETURN
(a) $$) AS (result agtype);
+ result
+--------
+(0 rows)
+
+-- CaseExpr
+-- subqueries in WHEN statement in RETURN
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ RETURN
+
a.name,
+
b.name,
+ CASE
+
WHEN EXISTS { MATCH (a)-[:loved]->(b) } THEN "There is LOVE!!!!!!"
+
WHEN EXISTS { MATCH (a)-[:knows]->(b) } THEN "There is a relationship"
+
ELSE "No relation"
+ END
$$) AS (a agtype, b agtype, c agtype);
+ a | b | c
+------------+------------+---------------------------
+ "Briggite" | "Takeshi" | "There is a relationship"
+ "Faye" | "Chan" | "There is a relationship"
+ "Faye" | "Tony" | "There is a relationship"
+ "Tony" | "Valerie" | "There is LOVE!!!!!!"
+ "Calvin" | "Hobbes" | "There is a relationship"
+ "Charlie" | "Snoopy" | "There is a relationship"
+ "Jon" | "Odie" | "There is a relationship"
+ "Jon" | "Garfield" | "There is a relationship"
+(8 rows)
+
+-- subqueries in THEN, WHERE
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ WHERE
+ CASE
a.name
+
WHEN "Jon" THEN EXISTS { MATCH (a)-[:knows]->(b) }
+
WHEN "Tony" THEN EXISTS { MATCH (a)-[:loved]->(b) }
+
ELSE False
+ END =
true RETURN a $$) AS (result agtype);
+ result
+-----------------------------------------------------------------------------------------------
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex
+(3 rows)
+
+-- nested in another exists
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ WHERE
+
EXISTS { MATCH (a) WHERE CASE a.name
+
WHEN "Jon" THEN EXISTS { MATCH (a)-[:knows]->(b) }
+
WHEN "Tony" THEN EXISTS { MATCH (a)-[:loved]->(b) }
+
ELSE False}
+ END =
true RETURN a $$) AS (result agtype);
+ERROR: syntax error at or near "}"
+LINE 6: ELSE False}
+ ^
+-- CoalesceExpr
+--coalesce, nested in where
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ WHERE
COALESCE( EXISTS {MATCH (a)-[:knows]->(b)
+
WHERE COALESCE( EXISTS {MATCH (a)
+
WHERE a.name = "Calvin"
+
OR a.name = "Charlie"})})
+ RETURN
a $$) AS (result agtype);
+ result
+-------------------------------------------------------------------------------------------------
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex
+(2 rows)
+
+--coalesce, nested in return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ RETURN
COALESCE( EXISTS {MATCH (a)-[:knows]->(b)
+
WHERE COALESCE( EXISTS {MATCH (a)
+
WHERE a.name = "Calvin"
+
OR a.name = "Charlie"})}) AS coalescefunc
+ $$) AS
(name agtype, result agtype);
+ERROR: return row and column definition list do not match
+LINE 1: SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ ^
+-- maps
+--nested exists maps
+SELECT * FROM cypher('subquery', $$MATCH (a)
+ WHERE {cond
: true} = { cond : EXISTS {(a)
+
WHERE {age: a.age } = {age:
a.age > 22}}}
+ RETURN a $$
) AS (a agtype);
+ a
+--------------------------------------------------------------------------------------
+ {"id": 1688849860263937, "label": "pet", "properties": {"name":
"Hobbes"}}::vertex
+ {"id": 1688849860263938, "label": "pet", "properties": {"name":
"Snoopy"}}::vertex
+ {"id": 1688849860263939, "label": "pet", "properties": {"name":
"Odie"}}::vertex
+ {"id": 1688849860263940, "label": "pet", "properties": {"name":
"Garfield"}}::vertex
+(4 rows)
+
+--nested exists in return
+SELECT * FROM cypher('subquery', $$MATCH (a)
+ RETURN a,
{cond : true} = { cond : EXISTS {(a)
+
WHERE {age: a.age } =
{age: a.age > 22}}}
+ $$ ) AS (a
agtype, cond agtype);
+ a
| cond
+---------------------------------------------------------------------------------------------------+-------
+ {"id": 844424930131969, "label": "person", "properties": {"age": 32, "name":
"Briggite"}}::vertex | false
+ {"id": 844424930131970, "label": "person", "properties": {"age": 28, "name":
"Takeshi"}}::vertex | false
+ {"id": 844424930131971, "label": "person", "properties": {"age": 45, "name":
"Chan"}}::vertex | false
+ {"id": 844424930131972, "label": "person", "properties": {"age": 25, "name":
"Faye"}}::vertex | false
+ {"id": 844424930131973, "label": "person", "properties": {"age": 34, "name":
"Tony"}}::vertex | false
+ {"id": 844424930131974, "label": "person", "properties": {"age": 33, "name":
"Valerie"}}::vertex | false
+ {"id": 844424930131975, "label": "person", "properties": {"age": 6, "name":
"Calvin"}}::vertex | false
+ {"id": 844424930131976, "label": "person", "properties": {"age": 8, "name":
"Charlie"}}::vertex | false
+ {"id": 844424930131977, "label": "person", "properties": {"age": 29, "name":
"Jon"}}::vertex | false
+ {"id": 1688849860263937, "label": "pet", "properties": {"name":
"Hobbes"}}::vertex | true
+ {"id": 1688849860263938, "label": "pet", "properties": {"name":
"Snoopy"}}::vertex | true
+ {"id": 1688849860263939, "label": "pet", "properties": {"name":
"Odie"}}::vertex | true
+ {"id": 1688849860263940, "label": "pet", "properties": {"name":
"Garfield"}}::vertex | true
+(13 rows)
+
+-- map projection
+--where
+SELECT * FROM cypher('subquery', $$MATCH (a)
+ WITH { a :
EXISTS {(a) WHERE a.name = 'Chan'}} as matchmap, a
+ WHERE true =
matchmap.a
+ RETURN a $$
) AS (a agtype);
+ a
+-----------------------------------------------------------------------------------------------
+ {"id": 844424930131971, "label": "person", "properties": {"age": 45, "name":
"Chan"}}::vertex
+(1 row)
+
+--return
+SELECT * FROM cypher('subquery', $$MATCH (a)
+ RETURN
a.name,
+ { a : EXISTS
{(a) WHERE a.name = 'Chan'}} $$ ) AS (a agtype, cond agtype);
+ a | cond
+------------+--------------
+ "Briggite" | {"a": false}
+ "Takeshi" | {"a": false}
+ "Chan" | {"a": true}
+ "Faye" | {"a": false}
+ "Tony" | {"a": false}
+ "Valerie" | {"a": false}
+ "Calvin" | {"a": false}
+ "Charlie" | {"a": false}
+ "Jon" | {"a": false}
+ "Hobbes" | {"a": false}
+ "Snoopy" | {"a": false}
+ "Odie" | {"a": false}
+ "Garfield" | {"a": false}
+(13 rows)
+
+--lists
+--list
+SELECT * FROM cypher('subquery', $$ MATCH (a:pet) WHERE true IN [EXISTS {
+MATCH (a) WHERE a.name = 'Hobbes' RETURN a}] RETURN a $$) AS (result agtype);
+ result
+------------------------------------------------------------------------------------
+ {"id": 1688849860263937, "label": "pet", "properties": {"name":
"Hobbes"}}::vertex
+(1 row)
+
+--nested in list
+SELECT * FROM cypher('subquery', $$ MATCH (a:pet) WHERE [true] IN [[EXISTS {
+MATCH (a) WHERE a.name = 'Hobbes' RETURN a}]] RETURN a $$) AS (result agtype);
+ result
+------------------------------------------------------------------------------------
+ {"id": 1688849860263937, "label": "pet", "properties": {"name":
"Hobbes"}}::vertex
+(1 row)
+
+--exist nested in list nested in list
+SELECT * FROM cypher('subquery', $$ MATCH (a:pet) WHERE [true] IN [[EXISTS {
+MATCH (a) WHERE EXISTS {MATCH (a)-[]-()} RETURN a}]] RETURN a $$) AS (result
agtype);
+ result
+--------------------------------------------------------------------------------------
+ {"id": 1688849860263937, "label": "pet", "properties": {"name":
"Hobbes"}}::vertex
+ {"id": 1688849860263938, "label": "pet", "properties": {"name":
"Snoopy"}}::vertex
+ {"id": 1688849860263939, "label": "pet", "properties": {"name":
"Odie"}}::vertex
+ {"id": 1688849860263940, "label": "pet", "properties": {"name":
"Garfield"}}::vertex
+(4 rows)
+
--
-- Cleanup
--
-SELECT * FROM drop_graph('exists_subquery', true);
+SELECT * FROM drop_graph('subquery', true);
NOTICE: drop cascades to 6 other objects
-DETAIL: drop cascades to table exists_subquery._ag_label_vertex
-drop cascades to table exists_subquery._ag_label_edge
-drop cascades to table exists_subquery.person
-drop cascades to table exists_subquery.knows
-drop cascades to table exists_subquery.loved
-drop cascades to table exists_subquery.pet
-NOTICE: graph "exists_subquery" has been dropped
+DETAIL: drop cascades to table subquery._ag_label_vertex
+drop cascades to table subquery._ag_label_edge
+drop cascades to table subquery.person
+drop cascades to table subquery.knows
+drop cascades to table subquery.loved
+drop cascades to table subquery.pet
+NOTICE: graph "subquery" has been dropped
drop_graph
------------
diff --git a/regress/sql/cypher_subquery.sql b/regress/sql/cypher_subquery.sql
index efc90295..1f10435a 100644
--- a/regress/sql/cypher_subquery.sql
+++ b/regress/sql/cypher_subquery.sql
@@ -1,200 +1,393 @@
LOAD 'age';
SET search_path TO ag_catalog;
-SELECT * FROM create_graph('exists_subquery');
+SELECT * FROM create_graph('subquery');
-SELECT * FROM cypher('exists_subquery', $$
-
CREATE (:person {name: "Briggite", age: 32})-[:knows]->(:person {name:
"Takeshi", age: 28}),
-
(:person {name: "Faye", age: 25})-[:knows]->(:person {name: "Tony",
age: 34})-[:loved]->(:person {name : "Valerie", age: 33}),
-
(:person {name: "Calvin", age: 6})-[:knows]->(:pet {name: "Hobbes"}),
-
(:person {name: "Charlie", age: 8})-[:knows]->(:pet {name :
"Snoopy"})
-
$$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$
+ CREATE
(:person {name: "Briggite", age: 32})-[:knows]->(:person {name: "Takeshi", age:
28}),
+
(:person {name: "Chan", age: 45})<-[:knows]-(:person {name: "Faye", age:
25})-[:knows]->
+
(:person {name: "Tony", age: 34})-[:loved]->(:person {name : "Valerie", age:
33}),
+
(:person {name: "Calvin", age: 6})-[:knows]->(:pet {name: "Hobbes"}),
+
(:person {name: "Charlie", age: 8})-[:knows]->(:pet {name : "Snoopy"}),
+
(:pet {name: "Odie"})<-[:knows]-(:person {name: "Jon", age:
29})-[:knows]->(:pet {name: "Garfield"})
+ $$) AS (result
agtype);
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a) RETURN (a) $$) AS (result
agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a) RETURN (a) $$) AS (result
agtype);
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {(a:person)-[]->(:pet)}
-
RETURN (a) $$) AS (result agtype);
---trying to use b when not defined, should fail
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {(a:person)-[]->(:pet)}
+ RETURN
(a) $$) AS (result agtype);
+--trying to use b when not defined, should create pattern
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
WHERE EXISTS {(a:person)-[]->(b:pet)}
RETURN (a) $$) AS (result agtype);
--query inside
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {MATCH (a:person)-[]->(b:pet) RETURN b}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {MATCH (a:person)-[]->(b:pet) RETURN b}
+ RETURN
(a) $$) AS (result agtype);
--repeat variable in match
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)
-
WHERE a.name = 'Takeshi'
-
RETURN a
-
}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)
+
WHERE a.name = 'Takeshi'
+
RETURN a
+
}
+ RETURN
(a) $$) AS (result agtype);
--query inside, with WHERE
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {MATCH (a:person)-[]->(b:pet)
-
WHERE b.name = 'Briggite'
-
RETURN b}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {MATCH (a:person)-[]->(b:pet)
+
WHERE b.name = 'Briggite'
+
RETURN b}
+ RETURN
(a) $$) AS (result agtype);
--no return
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {MATCH (a:person)-[]->(b:pet)
-
WHERE a.name = 'Calvin'}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {MATCH (a:person)-[]->(b:pet)
+
WHERE a.name = 'Calvin'}
+ RETURN
(a) $$) AS (result agtype);
--union
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)-[]->(b:pet)
-
WHERE b.name = 'Hobbes'
-
RETURN b
-
UNION
-
MATCH (c:person)-[]->(d:person)
-
RETURN c
-
}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)-[]->(b:pet)
+
WHERE b.name = 'Hobbes'
+
RETURN b
+
UNION
+
MATCH (a:person)-[]->(c:person)
+
WHERE a.name = 'Faye'
+
RETURN c
+
}
+ RETURN
(a) $$) AS (result agtype);
-- union, mismatched var, should fail
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)-[]->(b:pet)
-
WHERE b.name = 'Snoopy'
-
RETURN c
-
UNION
-
MATCH (c:person)-[]->(d:person)
-
RETURN c
-
}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)-[]->(b:pet)
+
WHERE b.name = 'Snoopy'
+
RETURN c
+
UNION
+
MATCH (c:person)-[]->(d:person)
+
RETURN c
+
}
+ RETURN
(a) $$) AS (result agtype);
---union, no returns
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)-[]->(b:pet)
-
WHERE a.name = 'Charlie'
-
UNION
-
MATCH (c:person)-[]->(d:person)
-
}
-
RETURN (a) $$) AS (result agtype);
+--union, no returns, not yet implemented, should error out
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)-[]->(b:pet)
+
WHERE a.name = 'Charlie'
+
UNION
+
MATCH (c:person)-[]->(d:person)
+
}
+ RETURN
(a) $$) AS (result agtype);
---union, mismatched returns, should fail
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (a:person)-[]->(b:pet)
-
WHERE a.name = 'Faye'
-
RETURN a
-
UNION
-
MATCH (c:person)-[]->(d:person)
-
}
-
RETURN (a) $$) AS (result agtype);
+--union, only one has return, should fail
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)-[]->(b:pet)
+
WHERE a.name = 'Faye'
+
RETURN a
+
UNION
+
MATCH (c:person)-[]->(d:person)
+
}
+ RETURN
(a) $$) AS (result agtype);
---nesting
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE c.name =
'Takeshi'
-
RETURN c
-
}
-
}
-
RETURN (a) $$) AS (result agtype);
+--nesting (should return everything since a isn't sent all the way down)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (b:person)
+
WHERE EXISTS {
+
MATCH (c:person)
+
WHERE c.name = 'Takeshi'
+
RETURN c
+
}
+
RETURN b
+
}
+ RETURN
(a) $$) AS (result agtype);
+
+--nesting same var multiple layers
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (a:person)
+
WHERE EXISTS {
+
MATCH (a:person)
+
WHERE a.name = 'Takeshi'
+
}
+
}
+ RETURN
(a) $$) AS (result agtype);
--nesting, accessing var in outer scope
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE b = c
-
RETURN c
-
}
-
}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a)
+ WHERE
EXISTS {
+
MATCH (b)
+
WHERE EXISTS {
+
MATCH (c:person)
+
WHERE b = c
+
}
+
}
+ RETURN
(a) $$) AS (result agtype);
--nesting, accessing indirection in outer scope
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE b.name =
'Takeshi'
-
RETURN c
-
}
-
}
+
MATCH (b:person)
+
WHERE EXISTS {
+
MATCH (c:person)
+
WHERE b.name =
'Takeshi'
+
RETURN c
+
}
+
}
RETURN (a) $$) AS (result agtype);
--nesting, accessing var 2+ levels up
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE a.name =
'Takeshi'
-
RETURN c
-
}
-
}
+
MATCH (b:person)
+
WHERE EXISTS {
+
MATCH (a:person)
+
WHERE a.name =
'Takeshi'
+
RETURN a
+
}
+
}
RETURN (a) $$) AS (result agtype);
--nesting, accessing indirection 2+ levels up
---EXISTS subquery is currently implemented naively, without constraints in the
---subquery. the results of this regression test may change upon implementation
---TODO: implement inner subquery constraints
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
WHERE EXISTS {
-
MATCH (b:person)
-
WHERE EXISTS {
-
MATCH (c:person)
-
WHERE a = b
-
RETURN c
-
}
-
}
-
RETURN (a) $$) AS (result agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {
+
MATCH (b:person)
+
WHERE EXISTS {
+
MATCH (a:person)
+
WHERE a = b
+
RETURN a
+
}
+
}
+ RETURN
(a) $$) AS (result agtype);
--EXISTS outside of WHERE
-SELECT * FROM cypher('exists_subquery', $$ MATCH (a:person)
-
RETURN a, EXISTS {(a:person)-[]->(:pet)}
-
$$) AS (a agtype, exists agtype);
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a, EXISTS {(a:person)-[]->(:pet)}
+ $$) AS
(a agtype, exists agtype);
--Var doesnt exist in outside scope, should fail
-SELECT * FROM cypher('exists_subquery', $$ RETURN 1,
-
EXISTS {
-
MATCH (b:person)-[]->(:pet)
-
RETURN a
-
}
-
$$) AS (a agtype, exists agtype);
+SELECT * FROM cypher('subquery', $$ RETURN 1,
+
EXISTS {
+
MATCH (b:person)-[]->(:pet)
+
RETURN a
+
}
+ $$) AS
(a agtype, exists agtype);
+
+--- COUNT
+
+--count pattern subquery in where
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT {(a:person)} > 1
+ RETURN
(a) $$) AS (result agtype);
+
+--count pattern in return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
COUNT{(a)-[]->(:pet)} $$) AS (result agtype);
+
+--count pattern with WHERE
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
COUNT{(a)-[]->(b:pet)
+
WHERE b.name = 'Hobbes'}
+ $$) AS
(result agtype);
+
+--solo match in where
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[]-()} > 1
+ RETURN
a $$) AS (result agtype);
+--match where person has more than one pet
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[]-(:pet)} > 1
+ RETURN
a $$) AS (result agtype);
+
+--match on labels
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[:knows]-()} > 1
+ RETURN
a $$) AS (result agtype);
+
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[:knows]-(:pet)} > 1
+ RETURN
a $$) AS (result agtype);
+
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a:person)-[:knows]-(:person)} > 1
+ RETURN
a $$) AS (result agtype);
+
+--solo match in return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
COUNT{MATCH (a)} $$) AS (result agtype);
+
+--match return in where
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
COUNT{MATCH (a) RETURN a} > 1 RETURN a $$) AS (result agtype);
+
+--match return in return with return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
COUNT{MATCH (a) RETURN a} $$) AS (result agtype);
+
+--match where return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, a.age, COUNT{MATCH (a)
+
WHERE a.age > 25
+
RETURN a.name} $$)
+ AS
(person agtype, age agtype, count agtype);
+
+--counting number of relationships per node
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, COUNT{(a)-[]-()} $$) AS (name agtype, count agtype);
+
+--nested counts
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, COUNT{MATCH (a)
+
WHERE COUNT {MATCH (a)
+
WHERE a.age < 23
+
RETURN a } > 0 } $$)
+ AS
(name agtype, count agtype);
+
+--incorrect variable reference
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, COUNT{MATCH (a) RETURN b} $$)
+ AS
(name agtype, count agtype);
+
+--incorrect nested variable reference
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name, COUNT{MATCH (a)
+
WHERE COUNT {MATCH (b) RETURN b } > 1
+
RETURN b} $$)
+ AS
(name agtype, count agtype);
+
+
+--count nested with exists
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ RETURN
a.name,
+
COUNT{MATCH (a) WHERE EXISTS {MATCH (a)-[]-(:pet)
+
RETURN a }} $$)
+ AS
(name agtype, count agtype);
+
+--
+-- expression tree walker additional tests. want to test the nesting
capabilties of the expr tree walker
+--
+
+--BoolExpr
+
+-- with comparison
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {(a:person)-[]->(:pet)}
+ AND
a.name = 'Odie'
+ RETURN
(a) $$) AS (result agtype);
+
+-- BoolExpr two subqueries
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {(a:person)-[]->(:pet)}
+ OR
EXISTS {(a:person)-[]->(:person)}
+ RETURN
(a) $$) AS (result agtype);
+
+-- Nested BoolExpr
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)
+ WHERE
EXISTS {(a:person)-[]->(:pet)
+ WHERE
a.name = 'Charlie'}
+ AND
EXISTS {(a:person)-[]->(:person)}
+ RETURN
(a) $$) AS (result agtype);
+
+-- CaseExpr
+
+-- subqueries in WHEN statement in RETURN
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ RETURN
+
a.name,
+
b.name,
+ CASE
+
WHEN EXISTS { MATCH (a)-[:loved]->(b) } THEN "There is LOVE!!!!!!"
+
WHEN EXISTS { MATCH (a)-[:knows]->(b) } THEN "There is a relationship"
+
ELSE "No relation"
+ END
$$) AS (a agtype, b agtype, c agtype);
+
+-- subqueries in THEN, WHERE
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ WHERE
+ CASE
a.name
+
WHEN "Jon" THEN EXISTS { MATCH (a)-[:knows]->(b) }
+
WHEN "Tony" THEN EXISTS { MATCH (a)-[:loved]->(b) }
+
ELSE False
+ END =
true RETURN a $$) AS (result agtype);
+
+-- nested in another exists
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ WHERE
+
EXISTS { MATCH (a) WHERE CASE a.name
+
WHEN "Jon" THEN EXISTS { MATCH (a)-[:knows]->(b) }
+
WHEN "Tony" THEN EXISTS { MATCH (a)-[:loved]->(b) }
+
ELSE False}
+ END =
true RETURN a $$) AS (result agtype);
+
+-- CoalesceExpr
+
+--coalesce, nested in where
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ WHERE
COALESCE( EXISTS {MATCH (a)-[:knows]->(b)
+
WHERE COALESCE( EXISTS {MATCH (a)
+
WHERE a.name = "Calvin"
+
OR a.name = "Charlie"})})
+ RETURN
a $$) AS (result agtype);
+
+--coalesce, nested in return
+SELECT * FROM cypher('subquery', $$ MATCH (a:person)-[]->(b)
+ RETURN
COALESCE( EXISTS {MATCH (a)-[:knows]->(b)
+
WHERE COALESCE( EXISTS {MATCH (a)
+
WHERE a.name = "Calvin"
+
OR a.name = "Charlie"})}) AS coalescefunc
+ $$) AS
(name agtype, result agtype);
+
+-- maps
+
+--nested exists maps
+SELECT * FROM cypher('subquery', $$MATCH (a)
+ WHERE {cond
: true} = { cond : EXISTS {(a)
+
WHERE {age: a.age } = {age:
a.age > 22}}}
+ RETURN a $$
) AS (a agtype);
+
+--nested exists in return
+SELECT * FROM cypher('subquery', $$MATCH (a)
+ RETURN a,
{cond : true} = { cond : EXISTS {(a)
+
WHERE {age: a.age } =
{age: a.age > 22}}}
+ $$ ) AS (a
agtype, cond agtype);
+
+
+-- map projection
+
+--where
+SELECT * FROM cypher('subquery', $$MATCH (a)
+ WITH { a :
EXISTS {(a) WHERE a.name = 'Chan'}} as matchmap, a
+ WHERE true =
matchmap.a
+ RETURN a $$
) AS (a agtype);
+
+--return
+SELECT * FROM cypher('subquery', $$MATCH (a)
+ RETURN
a.name,
+ { a : EXISTS
{(a) WHERE a.name = 'Chan'}} $$ ) AS (a agtype, cond agtype);
+
+--lists
+
+--list
+SELECT * FROM cypher('subquery', $$ MATCH (a:pet) WHERE true IN [EXISTS {
+MATCH (a) WHERE a.name = 'Hobbes' RETURN a}] RETURN a $$) AS (result agtype);
+
+--nested in list
+SELECT * FROM cypher('subquery', $$ MATCH (a:pet) WHERE [true] IN [[EXISTS {
+MATCH (a) WHERE a.name = 'Hobbes' RETURN a}]] RETURN a $$) AS (result agtype);
+
+--exist nested in list nested in list
+SELECT * FROM cypher('subquery', $$ MATCH (a:pet) WHERE [true] IN [[EXISTS {
+MATCH (a) WHERE EXISTS {MATCH (a)-[]-()} RETURN a}]] RETURN a $$) AS (result
agtype);
--
-- Cleanup
--
-SELECT * FROM drop_graph('exists_subquery', true);
+SELECT * FROM drop_graph('subquery', true);
--
-- End of tests
diff --git a/src/backend/nodes/cypher_outfuncs.c
b/src/backend/nodes/cypher_outfuncs.c
index 5c175a30..662d7847 100644
--- a/src/backend/nodes/cypher_outfuncs.c
+++ b/src/backend/nodes/cypher_outfuncs.c
@@ -116,6 +116,7 @@ void out_cypher_with(StringInfo str, const ExtensibleNode
*node)
DEFINE_AG_NODE(cypher_with);
WRITE_BOOL_FIELD(distinct);
+ WRITE_BOOL_FIELD(subquery_intermediate);
WRITE_NODE_FIELD(items);
WRITE_NODE_FIELD(order_by);
WRITE_NODE_FIELD(skip);
diff --git a/src/backend/parser/cypher_analyze.c
b/src/backend/parser/cypher_analyze.c
index 48d0200e..b95a0712 100644
--- a/src/backend/parser/cypher_analyze.c
+++ b/src/backend/parser/cypher_analyze.c
@@ -35,6 +35,8 @@
#include "utils/ag_func.h"
#include "utils/age_session_info.h"
+typedef bool (*cypher_expression_condition)(Node *expr);
+
/*
* extra_node is a global variable to this source to store, at the moment, the
* explain stmt node passed up by the parser. The return value from the parser
@@ -65,6 +67,11 @@ static Query *analyze_cypher_and_coerce(List *stmt,
RangeTblFunction *rtfunc,
const char *query_str, int query_loc,
char *graph_name, uint32 graph_oid,
Param *params);
+cypher_clause *build_subquery_node(cypher_clause *next);
+
+/* expr tree walker */
+bool expr_contains_node(cypher_expression_condition is_expr, Node *expr);
+bool expr_has_subquery(Node * expr);
void post_parse_analyze_init(void)
@@ -653,6 +660,382 @@ static int get_query_location(const int location, const
char *source_str)
return strchr(p + 1, '$') - source_str + 1;
}
+/*
+ * This is a specialized expression tree walker for finding exprs of a
specified
+ * type. Create a function that checks for the type you want, and this function
+ * will iterate through the tree.
+ */
+
+bool expr_contains_node(cypher_expression_condition is_expr, Node *expr)
+{
+ if (!expr)
+ {
+ return false;
+ }
+
+ switch (nodeTag(expr))
+ {
+ case T_A_Const:
+ case T_ColumnRef:
+ case T_A_Indirection:
+ {
+ break;
+ }
+ case T_A_Expr:
+ {
+ A_Expr *a = (A_Expr *)expr;
+
+ switch (a->kind)
+ {
+ case AEXPR_OP:
+ case AEXPR_IN:
+ {
+ if (expr_contains_node(is_expr, a->lexpr) ||
+ expr_contains_node(is_expr, a->rexpr))
+ {
+ return true;
+ }
+ break;
+ }
+ default:
+ ereport(ERROR, (errmsg_internal("unrecognized A_Expr kind: %d",
+ a->kind)));
+ }
+ break;
+ }
+ case T_BoolExpr:
+ {
+ BoolExpr *b = (BoolExpr *)expr;
+ ListCell *la;
+
+ foreach(la, b->args)
+ {
+ Node *arg = lfirst(la);
+
+ if (expr_contains_node(is_expr, arg))
+ {
+ return true;
+ }
+ }
+ break;
+ }
+ case T_NullTest:
+ {
+ NullTest *n = (NullTest *)expr;
+
+ if (expr_contains_node(is_expr, (Node *)n->arg))
+ {
+ return true;
+ }
+ break;
+ }
+ case T_CaseExpr:
+ {
+ CaseExpr *cexpr = (CaseExpr *)expr;
+ ListCell *l;
+
+ if (cexpr->arg && expr_contains_node(is_expr, (Node *) cexpr->arg))
+ {
+ return true;
+ }
+
+ foreach(l, cexpr->args)
+ {
+ CaseWhen *w = lfirst_node(CaseWhen, l);
+ Node *warg;
+
+ warg = (Node *) w->expr;
+
+ if (expr_contains_node(is_expr, warg))
+ {
+ return true;
+ }
+ warg = (Node *)w->result;
+
+ if (expr_contains_node(is_expr, warg))
+ {
+ return true;
+ }
+ }
+
+ if (expr_contains_node(is_expr , (Node *)cexpr->defresult))
+ {
+ return true;
+ }
+
+ break;
+ }
+ case T_CaseTestExpr:
+ {
+ break;
+ }
+ case T_CoalesceExpr:
+ {
+ CoalesceExpr *cexpr = (CoalesceExpr *) expr;
+ ListCell *args;
+
+ foreach(args, cexpr->args)
+ {
+ Node *e = (Node *)lfirst(args);
+
+ if (expr_contains_node(is_expr, e))
+ {
+ return true;
+ }
+ }
+ break;
+ }
+ case T_ExtensibleNode:
+ {
+ if (is_ag_node(expr, cypher_bool_const))
+ {
+ return is_expr(expr);
+ }
+ if (is_ag_node(expr, cypher_integer_const))
+ {
+ return is_expr(expr);
+ }
+ if (is_ag_node(expr, cypher_param))
+ {
+ return is_expr(expr);
+ }
+ if (is_ag_node(expr, cypher_map))
+ {
+ cypher_map *cm = (cypher_map *)expr;
+ ListCell *le;
+
+ Assert(list_length(cm->keyvals) % 2 == 0);
+
+ le = list_head(cm->keyvals);
+
+ while(le != NULL)
+ {
+ Node *val;
+
+ le = lnext(cm->keyvals, le);
+
+ val = lfirst(le);
+
+ if (expr_contains_node(is_expr, val))
+ {
+ return true;
+ }
+
+ le = lnext(cm->keyvals, le);
+
+ }
+ break;
+ }
+ if (is_ag_node(expr, cypher_map_projection))
+ {
+ cypher_map_projection *cmp = (cypher_map_projection *)expr;
+ ListCell *lc;
+
+ foreach(lc, cmp->map_elements)
+ {
+ cypher_map_projection_element *elem;
+
+ elem = lfirst(lc);
+
+ if (expr_contains_node(is_expr, elem->value))
+ {
+ return true;
+ }
+ }
+
+ break;
+ }
+ if (is_ag_node(expr, cypher_list))
+ {
+ cypher_list *cl = (cypher_list *)expr;
+ ListCell *le = NULL;
+
+ foreach(le, cl->elems)
+ {
+ Node *texpr = lfirst(le);
+
+ if (expr_contains_node(is_expr, texpr))
+ {
+ return true;
+ }
+ }
+ break;
+ }
+ if (is_ag_node(expr, cypher_string_match))
+ {
+ cypher_string_match *csm = (cypher_string_match *)expr;
+
+ if (expr_contains_node(is_expr, csm->lhs) ||
+ expr_contains_node(is_expr, csm->rhs))
+ {
+ return true;
+ }
+ break;
+ }
+ if (is_ag_node(expr, cypher_typecast))
+ {
+ cypher_typecast *t = (cypher_typecast *) expr;
+
+ if (expr_contains_node(is_expr, t->expr))
+ {
+ return true;
+ }
+ break;
+ }
+ if (is_ag_node(expr, cypher_comparison_aexpr))
+ {
+ cypher_comparison_aexpr *a = (cypher_comparison_aexpr *) expr;
+
+ if (expr_contains_node(is_expr, a->lexpr) ||
+ expr_contains_node(is_expr, a->rexpr))
+ {
+ return true;
+ }
+ break;
+ }
+ if (is_ag_node(expr, cypher_comparison_boolexpr))
+ {
+ cypher_comparison_boolexpr *b = (cypher_comparison_boolexpr *)
expr;
+ ListCell *la;
+
+ foreach(la, b->args)
+ {
+ Node *arg = lfirst(la);
+
+ if (expr_contains_node(is_expr, arg))
+ {
+ return true;
+ }
+ }
+ break;
+ }
+ if (is_ag_node(expr, cypher_unwind))
+ {
+ cypher_unwind* lc = (cypher_unwind *)expr;
+
+ if (expr_contains_node(is_expr, lc->where) ||
+ expr_contains_node(is_expr, lc->collect))
+ {
+ return true;
+ }
+ break;
+ }
+
+ if (is_ag_node(expr, cypher_sub_pattern))
+ {
+ break;
+ }
+
+ if (is_ag_node(expr, cypher_sub_query))
+ {
+ break;
+ }
+
+ ereport(ERROR,
+ (errmsg_internal("unrecognized ExtensibleNode: %s",
+ ((ExtensibleNode *)expr)->extnodename)));
+
+ break;
+ }
+ case T_FuncCall:
+ {
+ FuncCall *fn = (FuncCall *)expr;
+ ListCell *arg;
+
+ foreach(arg, fn->args)
+ {
+ Node *farg = NULL;
+
+ farg = (Node *)lfirst(arg);
+
+ if (expr_contains_node(is_expr, farg))
+ {
+ return true;
+ }
+ }
+ break;
+ }
+ case T_SubLink:
+ {
+ SubLink *s = (SubLink *)expr;
+
+ if (expr_contains_node(is_expr, s->subselect))
+ {
+ return true;
+ }
+ break;
+ }
+ default:
+ ereport(ERROR, (errmsg_internal("unrecognized node type: %d",
+ nodeTag(expr))));
+ }
+
+ return (is_expr(expr));
+}
+
+/*
+ * Function that checks if an expr is a cypher_sub_query. Used in tandem with
+ * expr_contains_node. Can write more similar to this to find similar nodes.
+ */
+
+bool expr_has_subquery(Node * expr)
+{
+ if (expr == NULL)
+ {
+ return false;
+ }
+
+ if (IsA(expr, ExtensibleNode))
+ {
+ if (is_ag_node(expr, cypher_sub_query))
+ {
+ return true;
+ }
+ }
+ return false;
+}
+
+/*
+ * This function constructs an intermediate WITH node for processing subqueries
+ */
+cypher_clause *build_subquery_node(cypher_clause *next)
+{
+ cypher_match *match = (cypher_match *)next->self;
+ cypher_clause *where_container_clause;
+ cypher_with *with_clause = make_ag_node(cypher_with);
+ ColumnRef *cr;
+ ResTarget *rt;
+
+ /* construct the column ref star */
+ cr = makeNode(ColumnRef);
+ cr->fields = list_make1(makeNode(A_Star));
+ cr->location = exprLocation((Node *)next);
+
+ /*construct the restarget */
+ rt = makeNode(ResTarget);
+ rt->name = NULL;
+ rt->indirection = NIL;
+ rt->val = (Node *)cr;
+ rt->location = exprLocation((Node *)next);
+
+
+ /* construct the with_clause */
+ with_clause->items = list_make1(rt);
+ with_clause->where = match->where;
+ with_clause->subquery_intermediate = true;
+
+ /*
+ * create the where container, and set the match (next) as the
+ * prev of the where container
+ */
+ where_container_clause = palloc(sizeof(*where_container_clause));
+ where_container_clause->self = (Node *)with_clause;
+ where_container_clause->next = NULL;
+ where_container_clause->prev = next;
+
+ return where_container_clause;
+}
+
static Query *analyze_cypher(List *stmt, ParseState *parent_pstate,
const char *query_str, int query_loc,
char *graph_name, uint32 graph_oid, Param *params)
@@ -679,8 +1062,27 @@ static Query *analyze_cypher(List *stmt, ParseState
*parent_pstate,
next->self = lfirst(lc);
next->prev = clause;
+ /* check for subqueries in match */
+ if (is_ag_node(next->self, cypher_match))
+ {
+ cypher_match *match = (cypher_match *)next->self;
+
+ if (match->where != NULL && expr_contains_node(expr_has_subquery,
match->where))
+ {
+ /* advance the clause iterator to the intermediate clause
position */
+ clause = build_subquery_node(next);
+
+ /* set the next of the match to the where_container_clause */
+ match->where = NULL;
+ next->next = clause;
+ continue;
+ }
+ }
+
if (clause != NULL)
+ {
clause->next = next;
+ }
clause = next;
}
diff --git a/src/backend/parser/cypher_clause.c
b/src/backend/parser/cypher_clause.c
index 026c8035..3e4c52c0 100644
--- a/src/backend/parser/cypher_clause.c
+++ b/src/backend/parser/cypher_clause.c
@@ -42,6 +42,7 @@
#include "catalog/ag_graph.h"
#include "catalog/ag_label.h"
#include "commands/label_commands.h"
+#include "parser/cypher_analyze.h"
#include "parser/cypher_clause.h"
#include "parser/cypher_expr.h"
#include "parser/cypher_item.h"
@@ -422,9 +423,8 @@ Query *transform_cypher_clause(cypher_parsestate *cpstate,
}
/*
- * Transform the UNION operator/clause. Creates a cypher_union
- * node and the necessary information needed in the execution
- * phase
+ * Makes a cypher_clause from a list of nodes. Used by union
+ * and subquery procedures to generate a subquery to transform.
*/
static cypher_clause *make_cypher_clause(List *stmt)
@@ -446,6 +446,23 @@ static cypher_clause *make_cypher_clause(List *stmt)
next->self = lfirst(lc);
next->prev = clause;
+ /* check for subqueries in match */
+ if (is_ag_node(next->self, cypher_match))
+ {
+ cypher_match *match = (cypher_match *)next->self;
+
+ if (match->where != NULL && expr_contains_node(expr_has_subquery,
match->where))
+ {
+ /* advance the clause iterator to the intermediate clause
position */
+ clause = build_subquery_node(next);
+
+ /* set the next of the match to the where_container_clause */
+ match->where = NULL;
+ next->next = clause;
+ continue;
+ }
+ }
+
if (clause != NULL)
{
clause->next = next;
@@ -2622,6 +2639,19 @@ static Query
*transform_cypher_match_pattern(cypher_parsestate *cpstate,
if(self->optional == true && clause->next)
{
cypher_clause *next = clause->next;
+
+ /*
+ * check if optional match has a subquery node-- it could still
+ * be following a match
+ */
+ if(is_ag_node(next->self, cypher_with))
+ {
+ cypher_with *next_with = (cypher_with *)next->self;
+ if (next_with->subquery_intermediate == true)
+ {
+ next = next->next;
+ }
+ }
if (is_ag_node(next->self, cypher_match))
{
cypher_match *next_self = (cypher_match *)next->self;
@@ -5014,15 +5044,7 @@ static Expr *transform_cypher_edge(cypher_parsestate
*cpstate,
* variables, we want to use the existing ones. So, error if otherwise.
* If we are in a subquery transform, we are allowed to create new
variables
* in the match, and all variables outside are visible to
- * the subquery. Since there is no existing SQL logic that allows
- * subqueries to alter variables of outer queries, we bypass this
- * logic we would normally use to process WHERE clauses.
- *
- * Currently, the EXISTS subquery logic is naive. It returns a boolean
- * result on the outer queries, but does not restrict the results set.
- *
- * TODO: Implement logic to alter outer scope results.
- *
+ * the subquery.
*/
if (pstate->p_expr_kind == EXPR_KIND_WHERE &&
cpstate->subquery_where_flag == false)
@@ -5286,15 +5308,7 @@ static Expr *transform_cypher_node(cypher_parsestate
*cpstate,
* variables, we want to use the existing ones. So, error if otherwise.
* If we are in a subquery transform, we are allowed to create new
variables
* in the match, and all variables outside are visible to
- * the subquery. Since there is no existing SQL logic that allows
- * subqueries to alter variables of outer queries, we bypass this
- * logic we would normally use to process WHERE clauses.
- *
- * Currently, the EXISTS subquery logic is naive. It returns a boolean
- * result on the outer queries, but does not restrict the results set.
- *
- * TODO: Implement logic to alter outer scope results.
- *
+ * the subquery.
*/
if (pstate->p_expr_kind == EXPR_KIND_WHERE &&
cpstate->subquery_where_flag == false)
diff --git a/src/backend/parser/cypher_gram.y b/src/backend/parser/cypher_gram.y
index 7c2c31f7..b987519d 100644
--- a/src/backend/parser/cypher_gram.y
+++ b/src/backend/parser/cypher_gram.y
@@ -74,7 +74,7 @@
/* keywords in alphabetical order */
%token <keyword> ALL ANALYZE AND AS ASC ASCENDING
BY
- CALL CASE COALESCE CONTAINS CREATE
+ CALL CASE COALESCE CONTAINS COUNT CREATE
DELETE DESC DESCENDING DETACH DISTINCT
ELSE END_P ENDS EXISTS EXPLAIN
FALSE_P
@@ -100,6 +100,7 @@
%type <list> subquery_stmt subquery_stmt_with_return subquery_stmt_no_return
single_subquery single_subquery_no_return subquery_part_init
+%type <node> subquery_pattern
/* RETURN and WITH clause */
%type <node> return return_item sort_item skip_opt limit_opt with
@@ -646,7 +647,9 @@ subquery_stmt_no_return:
}
| subquery_stmt_no_return UNION all_or_distinct subquery_stmt_no_return
{
- $$ = list_make1(make_set_op(SETOP_UNION, $3, $1, $4));
+ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("Subquery UNION without returns not yet implemented"),
+ ag_scanner_errposition(@2, scanner)));
}
;
@@ -688,6 +691,31 @@ single_subquery_no_return:
$$ = list_concat($1, lappend($2, n));
}
+ | subquery_pattern
+ {
+ ColumnRef *cr;
+ ResTarget *rt;
+ cypher_return *n;
+
+ cr = makeNode(ColumnRef);
+ cr->fields = list_make1(makeNode(A_Star));
+ cr->location = @1;
+
+ rt = makeNode(ResTarget);
+ rt->name = NULL;
+ rt->indirection = NIL;
+ rt->val = (Node *)cr;
+ rt->location = @1;
+
+ n = make_ag_node(cypher_return);
+ n->distinct = false;
+ n->items = list_make1((Node *)rt);
+ n->order_by = NULL;
+ n->skip = NULL;
+ n->limit = NULL;
+
+ $$ = lappend(list_make1($1), n);
+ }
;
subquery_part_init:
@@ -945,7 +973,7 @@ with:
cypher_with *n;
// check expressions are aliased
- foreach (li, $3)
+ foreach(li, $3)
{
ResTarget *item = lfirst(li);
@@ -1896,26 +1924,40 @@ expr_func_subexpr:
$$ = (Node *)node_to_agtype((Node *)n, "boolean", @2);
}
+ | COUNT '(' ')'
+ {
+ $$ = make_function_expr(list_make1(makeString("count")), NIL, @1);
+ }
+ | COUNT '(' expr_list ')'
+ {
+ $$ = make_function_expr(list_make1(makeString("count")), $3, @2);
+ }
+ | COUNT '(' DISTINCT expr_list ')'
+ {
+ FuncCall *n = (FuncCall *)make_distinct_function_expr(
+
list_make1(makeString("count")), $4, @1);
+ $$ = (Node *)n;
+ }
+ | COUNT '(' '*' ')'
+ {
+ FuncCall *n = (FuncCall *)make_star_function_expr(
+
list_make1(makeString("count")), NIL, @1);
+ $$ = (Node *)n;
+ }
;
expr_subquery:
- EXISTS '{' anonymous_path '}'
+ EXISTS '{' subquery_stmt '}'
{
- /*
- * EXISTS subquery with an anonymous path is almost
- * the same as a EXISTS sub pattern, so we reuse that
- * logic here to simplify more complex subquery transformations.
- * TODO: Add WHERE clause support for anonymous paths in functions.
- */
-
- cypher_sub_pattern *sub;
+ cypher_sub_query *sub;
SubLink *n;
- sub = make_ag_node(cypher_sub_pattern);
+ sub = make_ag_node(cypher_sub_query);
sub->kind = CSP_EXISTS;
- sub->pattern = list_make1($3);
+ sub->query = $3;
n = makeNode(SubLink);
+
n->subLinkType = EXISTS_SUBLINK;
n->subLinkId = 0;
n->testexpr = NULL;
@@ -1924,24 +1966,51 @@ expr_subquery:
n->location = @1;
$$ = (Node *)node_to_agtype((Node *)n, "boolean", @1);
}
- | EXISTS '{' subquery_stmt '}'
+ | COUNT '{' subquery_stmt '}'
{
- cypher_sub_query *sub;
SubLink *n;
+ cypher_sub_query *sub;
+ cypher_return *r;
+ ResTarget *rt;
+ FuncCall *func;
+
+ func = (FuncCall *)make_star_function_expr(
+
list_make1(makeString("count")), NIL, @1);
+
+ rt = makeNode(ResTarget);
+ rt->name = NULL;
+ rt->indirection = NIL;
+ rt->val = (Node *)func;
+ rt->location = @1;
+
+ r = make_ag_node(cypher_return);
+ r->items = list_make1((Node *)rt);
sub = make_ag_node(cypher_sub_query);
- sub->kind = CSP_EXISTS;
- sub->query = $3;
+ sub->query = lappend($3, r);
n = makeNode(SubLink);
-
- n->subLinkType = EXISTS_SUBLINK;
+ n->subLinkType = EXPR_SUBLINK;
n->subLinkId = 0;
n->testexpr = NULL;
n->operName = NIL;
- n->subselect = (Node *) sub;
+ n->subselect = (Node *)sub;
n->location = @1;
- $$ = (Node *)node_to_agtype((Node *)n, "boolean", @1);
+
+ $$ = (Node *) n;
+ }
+ ;
+
+subquery_pattern:
+ anonymous_path where_opt
+ {
+ cypher_match *n;
+
+ n = make_ag_node(cypher_match);
+ n->pattern = list_make1($1);
+ n->where = $2;
+
+ $$ = (Node *)n;
}
;
@@ -2334,6 +2403,7 @@ safe_keywords:
| CASE { $$ = pnstrdup($1, 4); }
| COALESCE { $$ = pnstrdup($1, 8); }
| CONTAINS { $$ = pnstrdup($1, 8); }
+ | COUNT { $$ = pnstrdup($1 ,5); }
| CREATE { $$ = pnstrdup($1, 6); }
| DELETE { $$ = pnstrdup($1, 6); }
| DESC { $$ = pnstrdup($1, 4); }
diff --git a/src/include/nodes/cypher_nodes.h b/src/include/nodes/cypher_nodes.h
index 7af97931..ec2b8a59 100644
--- a/src/include/nodes/cypher_nodes.h
+++ b/src/include/nodes/cypher_nodes.h
@@ -67,6 +67,7 @@ typedef struct cypher_with
{
ExtensibleNode extensible;
bool distinct;
+ bool subquery_intermediate; //flag that denotes a subquery node
List *items; // a list of ResTarget's
List *order_by;
Node *skip;
diff --git a/src/include/parser/cypher_analyze.h
b/src/include/parser/cypher_analyze.h
index 32f3a47f..97616e54 100644
--- a/src/include/parser/cypher_analyze.h
+++ b/src/include/parser/cypher_analyze.h
@@ -20,7 +20,17 @@
#ifndef AG_CYPHER_ANALYZE_H
#define AG_CYPHER_ANALYZE_H
+#include "parser/cypher_clause.h"
+
+typedef bool (*cypher_expression_condition)( Node *expr);
+
void post_parse_analyze_init(void);
void post_parse_analyze_fini(void);
+cypher_clause *build_subquery_node(cypher_clause *next);
+
+/*expr tree walker */
+bool expr_contains_node(cypher_expression_condition is_expr, Node *expr);
+bool expr_has_subquery(Node * expr);
+
#endif
diff --git a/src/include/parser/cypher_kwlist.h
b/src/include/parser/cypher_kwlist.h
index d15a0e34..ce48f288 100644
--- a/src/include/parser/cypher_kwlist.h
+++ b/src/include/parser/cypher_kwlist.h
@@ -9,6 +9,7 @@ PG_KEYWORD("call", CALL, RESERVED_KEYWORD)
PG_KEYWORD("case", CASE, RESERVED_KEYWORD)
PG_KEYWORD("coalesce", COALESCE, RESERVED_KEYWORD)
PG_KEYWORD("contains", CONTAINS, RESERVED_KEYWORD)
+PG_KEYWORD("count", COUNT, RESERVED_KEYWORD)
PG_KEYWORD("create", CREATE, RESERVED_KEYWORD)
PG_KEYWORD("delete", DELETE, RESERVED_KEYWORD)
PG_KEYWORD("desc", DESC, RESERVED_KEYWORD)
