You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@age.apache.org by jg...@apache.org on 2023/03/20 20:38:59 UTC
[age] 15/19: Update regression tests for cypher_with (#725)
This is an automated email from the ASF dual-hosted git repository.
jgemignani pushed a commit to branch PG11
in repository https://gitbox.apache.org/repos/asf/age.git
commit 9dbdde99ba326a9b469a606cb773e0b766330df4
Author: Muhammad Taha Naveed <mn...@seecs.edu.pk>
AuthorDate: Fri Mar 3 23:41:55 2023 +0500
Update regression tests for cypher_with (#725)
- Used a small dataset for regression tests.
---
regress/expected/cypher_with.out | 408 ++++++++++++++++++---------------------
regress/sql/cypher_with.sql | 207 ++++++++++----------
2 files changed, 286 insertions(+), 329 deletions(-)
diff --git a/regress/expected/cypher_with.out b/regress/expected/cypher_with.out
index 1166fff3..e5f82aa2 100644
--- a/regress/expected/cypher_with.out
+++ b/regress/expected/cypher_with.out
@@ -16,7 +16,6 @@
* specific language governing permissions and limitations
* under the License.
*/
- \! cp -r regress/age_load/data regress/instance/data/age_load
LOAD 'age';
SET search_path TO ag_catalog;
--
@@ -29,82 +28,71 @@ NOTICE: graph "cypher_with" has been created
(1 row)
-SELECT create_vlabel('cypher_with','Country');
-NOTICE: VLabel "Country" has been created
- create_vlabel
----------------
-
-(1 row)
-
-SELECT load_labels_from_file('cypher_with', 'Country',
- 'age_load/countries.csv');
- load_labels_from_file
------------------------
-
-(1 row)
-
-SELECT create_vlabel('cypher_with','City');
-NOTICE: VLabel "City" has been created
- create_vlabel
----------------
-
-(1 row)
-
-SELECT load_labels_from_file('cypher_with', 'City',
- 'age_load/cities.csv');
- load_labels_from_file
------------------------
-
-(1 row)
-
-SELECT create_elabel('cypher_with','has_city');
-NOTICE: ELabel "has_city" has been created
- create_elabel
----------------
-
-(1 row)
-
-SELECT load_edges_from_file('cypher_with', 'has_city',
- 'age_load/edges.csv');
- load_edges_from_file
-----------------------
-
-(1 row)
+SELECT * FROM cypher('cypher_with', $$
+ CREATE (andres {name : 'Andres', age : 36}),
+ (caesar {name : 'Caesar', age : 25}),
+ (bossman {name : 'Bossman', age : 55}),
+ (david {name : 'David', age : 35}),
+ (george {name : 'George', age : 37}),
+ (andres)-[:BLOCKS]->(caesar),
+ (andres)-[:KNOWS]->(bossman),
+ (caesar)-[:KNOWS]->(george),
+ (bossman)-[:BLOCKS]->(david),
+ (bossman)-[:KNOWS]->(george),
+ (david)-[:KNOWS]->(andres)
+$$) as (a agtype);
+ a
+---
+(0 rows)
--
-- Test WITH clause
--
-SELECT count(*) FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m,b
- RETURN m,b
-$$) AS (City agtype, Country agtype);
- count
--------
- 2361
-(1 row)
+SELECT * FROM cypher('cypher_with', $$
+ MATCH (n)-[e]->(m)
+ WITH n,e,m
+ RETURN n,e,m
+$$) AS (N1 agtype, edge agtype, N2 agtype);
+ n1 | edge | n2
+--------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex | {"id": 844424930131969, "label": "BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge | {"id": 281474976710658, "label": "", "properties": {"age": 25, "name": "Caesar"}}::vertex
+ {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex | {"id": 844424930131970, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge | {"id": 281474976710660, "label": "", "properties": {"age": 35, "name": "David"}}::vertex
+ {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex | {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge | {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex
+ {"id": 281474976710658, "label": "", "properties": {"age": 25, "name": "Caesar"}}::vertex | {"id": 1125899906842626, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710658, "properties": {}}::edge | {"id": 281474976710661, "label": "", "properties": {"age": 37, "name": "George"}}::vertex
+ {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex | {"id": 1125899906842627, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710659, "properties": {}}::edge | {"id": 281474976710661, "label": "", "properties": {"age": 37, "name": "George"}}::vertex
+ {"id": 281474976710660, "label": "", "properties": {"age": 35, "name": "David"}}::vertex | {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge | {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex
+(6 rows)
-- WITH/AS
SELECT * FROM cypher('cypher_with', $$
- MATCH (b:Country {iso2 : 'AT'})
- WITH b.name AS name, id(b) AS id
- RETURN name,id
-$$) AS (Country agtype, Country_id agtype);
- country | country_id
------------+-----------------
- "Austria" | 844424930131983
-(1 row)
+ MATCH (n)-[e]->(m)
+ WITH n.name AS n1, e as edge, m.name as n2
+ RETURN n1,label(edge),n2
+$$) AS (start_node agtype,edge agtype, end_node agtype);
+ start_node | edge | end_node
+------------+----------+-----------
+ "Andres" | "BLOCKS" | "Caesar"
+ "Bossman" | "BLOCKS" | "David"
+ "Andres" | "KNOWS" | "Bossman"
+ "Caesar" | "KNOWS" | "George"
+ "Bossman" | "KNOWS" | "George"
+ "David" | "KNOWS" | "Andres"
+(6 rows)
-SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City {name: 'Zell'})-[]-(b:Country {iso2 : 'AT'})
- WITH b as country, count(*) AS foaf
- WHERE foaf > 1
- RETURN country.name, foaf
-$$) as (name agtype, foaf agtype);
- name | foaf
------------+------
- "Austria" | 2
-(1 row)
+SELECT * FROM cypher('cypher_with',$$
+ MATCH (person)-[r]->(otherPerson)
+ WITH *, type(r) AS connectionType
+ RETURN person.name, connectionType, otherPerson.name
+$$) AS (start_node agtype, connection agtype, end_node agtype);
+ start_node | connection | end_node
+------------+------------+-----------
+ "Andres" | "BLOCKS" | "Caesar"
+ "Bossman" | "BLOCKS" | "David"
+ "Andres" | "KNOWS" | "Bossman"
+ "Caesar" | "KNOWS" | "George"
+ "Bossman" | "KNOWS" | "George"
+ "David" | "KNOWS" | "Andres"
+(6 rows)
SELECT * FROM cypher('cypher_with', $$
WITH true AS b
@@ -117,162 +105,139 @@ $$) AS (b bool);
-- WITH/WHERE
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country{iso2:'BE'})
- WITH b,m
- WHERE m.name='x'
- RETURN m.name,b.iso2
-$$) AS ( "m.name" agtype, "b" agtype);
- m.name | b
---------+---
-(0 rows)
-
-SELECT * FROM cypher('cypher_with', $$
- MATCH (b:Country {iso2 : 'AT'})
- WITH b.name AS name, id(b) AS id
- WHERE name = 'Austria'
- RETURN name,id
-$$) AS (Country agtype, Country_id agtype);
- country | country_id
------------+-----------------
- "Austria" | 844424930131983
+MATCH (george {name: 'George'})<-[]-(otherPerson)
+ WITH otherPerson, toUpper(otherPerson.name) AS upperCaseName
+ WHERE upperCaseName STARTS WITH 'C'
+ RETURN otherPerson.name
+$$) as (name agtype);
+ name
+----------
+ "Caesar"
(1 row)
SELECT * FROM cypher('cypher_with', $$
- MATCH (b:Country {iso2 : 'AT'})
- WITH b.name AS name, id(b) AS id
- WHERE name = 'Austria' OR name = 'Kosovo'
- RETURN name,id
-$$) AS (Country agtype, Country_id agtype);
- country | country_id
------------+-----------------
- "Austria" | 844424930131983
+ MATCH (david {name: 'David'})-[]-(otherPerson)-[]->()
+ WITH otherPerson, count(*) AS foaf
+ WHERE foaf > 1
+ RETURN otherPerson.name
+$$) as (name agtype);
+ name
+----------
+ "Andres"
(1 row)
SELECT * FROM cypher('cypher_with', $$
- MATCH p = (m:City)-[:has_city*1..2]->(b:Country {iso2 : 'AT'})
+ MATCH p = (m)-[*1..2]->(b)
WITH p, length(p) AS path_length
WHERE path_length > 1
RETURN p
$$) AS (pattern agtype);
- pattern
----------
-(0 rows)
+ pattern [...]
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ [...]
+ [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex, {"id": 1125899906842627, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710661, "la [...]
+ [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex, {"id": 844424930131970, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710660, "la [...]
+ [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 844424930131969, "label": "BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710658, "label": "_ag_label_vertex", "properties": {"age": 25, "name": "Caesar"}}::vertex, {"id": 1125899906842626, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710658, "properties": {}}::edge, {"id": 281474976710661, "lab [...]
+ [{"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex, {"id": 844424930131970, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710657, "lab [...]
+ [{"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710659, "labe [...]
+ [{"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 844424930131969, "label": "BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710658, "labe [...]
+(6 rows)
-- MATCH/WHERE with WITH/WHERE
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WHERE b.name = 'Austria'
- WITH m.name AS city,b.name AS country
- WHERE city = 'Vienna'
- RETURN city,country
-$$) AS (City agtype, Country agtype);
- city | country
-----------+-----------
- "Vienna" | "Austria"
+ MATCH (m)-[e]->(b)
+ WHERE label(e) = 'KNOWS'
+ WITH *
+ WHERE m.name = 'Andres'
+ RETURN m.name,label(e),b.name
+$$) AS (N1 agtype, edge agtype, N2 agtype);
+ n1 | edge | n2
+----------+---------+-----------
+ "Andres" | "KNOWS" | "Bossman"
(1 row)
-- WITH/ORDER BY
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- ORDER BY id(m) DESC LIMIT 10
- RETURN id(city),city
-$$) AS (id agtype, city agtype);
- id | city
-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- 1125899906986495 | {"id": 1125899906986495, "label": "City", "properties": {"id": "143871", "name": "Zell", "__id__": 143871, "latitude": "46.47222222", "state_id": "2057", "longitude": "14.38888889", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
- 1125899906986492 | {"id": 1125899906986492, "label": "City", "properties": {"id": "143868", "name": "Weißenstein", "__id__": 143868, "latitude": "46.68222222", "state_id": "2057", "longitude": "13.72361111", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
- 1125899906986491 | {"id": 1125899906986491, "label": "City", "properties": {"id": "143867", "name": "Weißensee", "__id__": 143867, "latitude": "46.71666667", "state_id": "2057", "longitude": "13.3", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
- 1125899906986490 | {"id": 1125899906986490, "label": "City", "properties": {"id": "143866", "name": "Weitensfeld im Gurktal", "__id__": 143866, "latitude": "46.84861111", "state_id": "2057", "longitude": "14.19166667", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
- 1125899906986489 | {"id": 1125899906986489, "label": "City", "properties": {"id": "143865", "name": "Velden am Wörther See", "__id__": 143865, "latitude": "46.6125", "state_id": "2057", "longitude": "14.04194444", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
- 1125899906986488 | {"id": 1125899906986488, "label": "City", "properties": {"id": "143864", "name": "Umberg", "__id__": 143864, "latitude": "46.64833", "state_id": "2057", "longitude": "13.9575", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
- 1125899906986487 | {"id": 1125899906986487, "label": "City", "properties": {"id": "143863", "name": "Tschachoritsch", "__id__": 143863, "latitude": "46.55352", "state_id": "2057", "longitude": "14.21461", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
- 1125899906986486 | {"id": 1125899906986486, "label": "City", "properties": {"id": "143862", "name": "Treffen", "__id__": 143862, "latitude": "46.66833333", "state_id": "2057", "longitude": "13.85555556", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
- 1125899906986484 | {"id": 1125899906986484, "label": "City", "properties": {"id": "143860", "name": "Timenitz", "__id__": 143860, "latitude": "46.68333", "state_id": "2057", "longitude": "14.41667", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
- 1125899906986483 | {"id": 1125899906986483, "label": "City", "properties": {"id": "143859", "name": "Techelsberg", "__id__": 143859, "latitude": "46.65861111", "state_id": "2057", "longitude": "14.10194444", "country_id": "15", "state_code": "2", "country_code": "AT"}}::vertex
-(10 rows)
+ MATCH (n)
+ WITH n
+ ORDER BY id(n)
+ RETURN n
+$$) as (name agtype);
+ name
+--------------------------------------------------------------------------------------------
+ {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex
+ {"id": 281474976710658, "label": "", "properties": {"age": 25, "name": "Caesar"}}::vertex
+ {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex
+ {"id": 281474976710660, "label": "", "properties": {"age": 35, "name": "David"}}::vertex
+ {"id": 281474976710661, "label": "", "properties": {"age": 37, "name": "George"}}::vertex
+(5 rows)
+-- WITH/ORDER BY/DESC
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)
- WITH m AS city
- ORDER BY id(m) ASC LIMIT 10
- RETURN id(city),city.name
-$$) AS (id agtype, names agtype);
- id | names
-------------------+-----------------------
- 1125899906842625 | "Andorra la Vella"
- 1125899906842626 | "Arinsal"
- 1125899906842627 | "Canillo"
- 1125899906842628 | "El Tarter"
- 1125899906842629 | "Encamp"
- 1125899906842630 | "Ordino"
- 1125899906842631 | "Pas de la Casa"
- 1125899906842632 | "Sant Julià de Lòria"
- 1125899906842633 | "la Massana"
- 1125899906842634 | "les Escaldes"
-(10 rows)
+ MATCH (n)
+ WITH n
+ ORDER BY n.name DESC LIMIT 3
+ RETURN collect(n.name)
+$$) as (names agtype);
+ names
+-------------------------------
+ ["George", "David", "Caesar"]
+(1 row)
--- WITH/ORDER BY/DESC/WHERE
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- ORDER BY id(m) DESC LIMIT 10
- WHERE city.name = 'Zell' OR city.name = 'Umberg'
- RETURN id(city),city.name,country.name
-$$) AS (id agtype, city agtype, country agtype);
- id | city | country
-------------------+----------+-----------
- 1125899906986495 | "Zell" | "Austria"
- 1125899906986488 | "Umberg" | "Austria"
+ MATCH (n {name: 'Andres'})-[]-(m)
+ WITH m
+ ORDER BY m.name DESC LIMIT 1
+ MATCH (m)-[]-(o)
+ RETURN o.name ORDER BY o.name
+$$) as (name agtype);
+ name
+-----------
+ "Andres"
+ "Bossman"
(2 rows)
-- multiple WITH clauses
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- WITH city LIMIT 10
- RETURN city.name
-$$) AS (city agtype);
- city
----------------------
- "Andau"
- "Antau"
- "Apetlon"
- "Bad Sauerbrunn"
- "Bad Tatzmannsdorf"
- "Badersdorf"
- "Bernstein"
- "Bocksdorf"
- "Breitenbrunn"
- "Bruckneudorf"
-(10 rows)
+ MATCH (n)-[e]->(m)
+ WITH n, e, m
+ WHERE label(e) = 'KNOWS'
+ WITH n.name as n1, label(e) as edge, m.name as n2
+ WHERE n1 = 'Andres'
+ RETURN n1,edge,n2
+$$) AS (N1 agtype, edge agtype, N2 agtype);
+ n1 | edge | n2
+----------+---------+-----------
+ "Andres" | "KNOWS" | "Bossman"
+(1 row)
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- ORDER BY id(m) DESC LIMIT 10
- WITH city
- WHERE city.name = 'Zell'
- RETURN id(city),city.name
-$$) AS (id agtype, city agtype);
- id | city
-------------------+--------
- 1125899906986495 | "Zell"
-(1 row)
+ UNWIND [1, 2, 3, 4, 5, 6] AS x
+ WITH x
+ WHERE x > 2
+ WITH x
+ LIMIT 5
+ RETURN x
+$$) as (name agtype);
+ name
+------
+ 3
+ 4
+ 5
+ 6
+(4 rows)
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- WHERE country.name = 'Austria'
- WITH city
- ORDER BY id(city) DESC
- WHERE city.name = 'Zell'
- RETURN id(city),city.name
-$$) AS (id agtype, city agtype);
- id | city
-------------------+--------
- 1125899906986495 | "Zell"
- 1125899906846495 | "Zell"
+ MATCH (m)-[]->(b)
+ WITH m,b
+ ORDER BY id(m) DESC LIMIT 5
+ WITH m as start_node, b as end_node
+ WHERE end_node.name = 'George'
+ RETURN id(start_node),start_node.name,id(end_node),end_node.name
+$$) AS (id1 agtype, name1 agtype, id2 agtype, name2 agtype);
+ id1 | name1 | id2 | name2
+-----------------+-----------+-----------------+----------
+ 281474976710659 | "Bossman" | 281474976710661 | "George"
+ 281474976710658 | "Caesar" | 281474976710661 | "George"
(2 rows)
-- Expression item must be aliased.
@@ -285,42 +250,41 @@ LINE 2: WITH 1 + 1
^
HINT: Items can be aliased by using AS.
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
+ MATCH (m)-[]->(b)
WITH id(m)
RETURN m
-$$) AS (id agtype, city agtype);
+$$) AS (id agtype);
ERROR: expression item must be aliased
LINE 3: WITH id(m)
^
HINT: Items can be aliased by using AS.
-- Reference undefined variable in WITH clause (should error out)
SELECT count(*) FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
+ MATCH (m)-[]->(b)
WITH m
RETURN m,b
-$$) AS (City agtype, Country agtype);
+$$) AS (a agtype, b agtype);
ERROR: could not find rte for b
LINE 4: RETURN m,b
^
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- WHERE country.name = 'Austria'
- WITH city
- WHERE city.name = 'Zell'
- RETURN id(city),country.name
-$$) AS (id agtype, country agtype);
-ERROR: could not find rte for country
-LINE 7: RETURN id(city),country.name
- ^
+ MATCH (m)-[]->(b)
+ WITH m AS start_node,b AS end_node
+ WHERE start_node.name = 'Andres'
+ WITH start_node
+ WHERE start_node.name = 'George'
+ RETURN id(start_node),end_node.name
+$$) AS (id agtype, node agtype);
+ERROR: could not find rte for end_node
+LINE 7: RETURN id(start_node),end_node.name
+ ^
-- Clean up
SELECT drop_graph('cypher_with', true);
-NOTICE: drop cascades to 5 other objects
+NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to table cypher_with._ag_label_vertex
drop cascades to table cypher_with._ag_label_edge
-drop cascades to table cypher_with."Country"
-drop cascades to table cypher_with."City"
-drop cascades to table cypher_with.has_city
+drop cascades to table cypher_with."BLOCKS"
+drop cascades to table cypher_with."KNOWS"
NOTICE: graph "cypher_with" has been dropped
drop_graph
------------
@@ -358,22 +322,22 @@ LINE 8: RETURN c,d
^
-- Issue 396 (should error out)
SELECT * FROM cypher('graph',$$
- CREATE (v),(u),(w),
- (v)-[:hasFriend]->(u),
- (u)-[:hasFriend]->(w)
+ CREATE (v),(u),(w),
+ (v)-[:hasFriend]->(u),
+ (u)-[:hasFriend]->(w)
$$) as (a agtype);
a
---
(0 rows)
SELECT * FROM cypher('graph',$$
- MATCH p=(v)-[*1..2]->(u)
- WITH p,length(p) AS path_length
- RETURN v,path_length
+ MATCH p=(v)-[*1..2]->(u)
+ WITH p,length(p) AS path_length
+ RETURN v,path_length
$$) as (a agtype,b agtype);
ERROR: could not find rte for v
-LINE 4: RETURN v,path_length
- ^
+LINE 4: RETURN v,path_length
+ ^
-- Clean up
SELECT drop_graph('graph', true);
NOTICE: drop cascades to 6 other objects
diff --git a/regress/sql/cypher_with.sql b/regress/sql/cypher_with.sql
index 95b18c50..93413f2c 100644
--- a/regress/sql/cypher_with.sql
+++ b/regress/sql/cypher_with.sql
@@ -17,8 +17,6 @@
* under the License.
*/
- \! cp -r regress/age_load/data regress/instance/data/age_load
-
LOAD 'age';
SET search_path TO ag_catalog;
@@ -27,42 +25,43 @@ SET search_path TO ag_catalog;
--
SELECT create_graph('cypher_with');
-SELECT create_vlabel('cypher_with','Country');
-SELECT load_labels_from_file('cypher_with', 'Country',
- 'age_load/countries.csv');
-
-SELECT create_vlabel('cypher_with','City');
-SELECT load_labels_from_file('cypher_with', 'City',
- 'age_load/cities.csv');
-
-SELECT create_elabel('cypher_with','has_city');
-SELECT load_edges_from_file('cypher_with', 'has_city',
- 'age_load/edges.csv');
+SELECT * FROM cypher('cypher_with', $$
+ CREATE (andres {name : 'Andres', age : 36}),
+ (caesar {name : 'Caesar', age : 25}),
+ (bossman {name : 'Bossman', age : 55}),
+ (david {name : 'David', age : 35}),
+ (george {name : 'George', age : 37}),
+ (andres)-[:BLOCKS]->(caesar),
+ (andres)-[:KNOWS]->(bossman),
+ (caesar)-[:KNOWS]->(george),
+ (bossman)-[:BLOCKS]->(david),
+ (bossman)-[:KNOWS]->(george),
+ (david)-[:KNOWS]->(andres)
+$$) as (a agtype);
--
-- Test WITH clause
--
-SELECT count(*) FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m,b
- RETURN m,b
-$$) AS (City agtype, Country agtype);
+SELECT * FROM cypher('cypher_with', $$
+ MATCH (n)-[e]->(m)
+ WITH n,e,m
+ RETURN n,e,m
+$$) AS (N1 agtype, edge agtype, N2 agtype);
-- WITH/AS
SELECT * FROM cypher('cypher_with', $$
- MATCH (b:Country {iso2 : 'AT'})
- WITH b.name AS name, id(b) AS id
- RETURN name,id
-$$) AS (Country agtype, Country_id agtype);
+ MATCH (n)-[e]->(m)
+ WITH n.name AS n1, e as edge, m.name as n2
+ RETURN n1,label(edge),n2
+$$) AS (start_node agtype,edge agtype, end_node agtype);
-SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City {name: 'Zell'})-[]-(b:Country {iso2 : 'AT'})
- WITH b as country, count(*) AS foaf
- WHERE foaf > 1
- RETURN country.name, foaf
-$$) as (name agtype, foaf agtype);
+SELECT * FROM cypher('cypher_with',$$
+ MATCH (person)-[r]->(otherPerson)
+ WITH *, type(r) AS connectionType
+ RETURN person.name, connectionType, otherPerson.name
+$$) AS (start_node agtype, connection agtype, end_node agtype);
SELECT * FROM cypher('cypher_with', $$
WITH true AS b
@@ -72,28 +71,21 @@ $$) AS (b bool);
-- WITH/WHERE
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country{iso2:'BE'})
- WITH b,m
- WHERE m.name='x'
- RETURN m.name,b.iso2
-$$) AS ( "m.name" agtype, "b" agtype);
-
-SELECT * FROM cypher('cypher_with', $$
- MATCH (b:Country {iso2 : 'AT'})
- WITH b.name AS name, id(b) AS id
- WHERE name = 'Austria'
- RETURN name,id
-$$) AS (Country agtype, Country_id agtype);
+MATCH (george {name: 'George'})<-[]-(otherPerson)
+ WITH otherPerson, toUpper(otherPerson.name) AS upperCaseName
+ WHERE upperCaseName STARTS WITH 'C'
+ RETURN otherPerson.name
+$$) as (name agtype);
SELECT * FROM cypher('cypher_with', $$
- MATCH (b:Country {iso2 : 'AT'})
- WITH b.name AS name, id(b) AS id
- WHERE name = 'Austria' OR name = 'Kosovo'
- RETURN name,id
-$$) AS (Country agtype, Country_id agtype);
+ MATCH (david {name: 'David'})-[]-(otherPerson)-[]->()
+ WITH otherPerson, count(*) AS foaf
+ WHERE foaf > 1
+ RETURN otherPerson.name
+$$) as (name agtype);
SELECT * FROM cypher('cypher_with', $$
- MATCH p = (m:City)-[:has_city*1..2]->(b:Country {iso2 : 'AT'})
+ MATCH p = (m)-[*1..2]->(b)
WITH p, length(p) AS path_length
WHERE path_length > 1
RETURN p
@@ -102,66 +94,67 @@ $$) AS (pattern agtype);
-- MATCH/WHERE with WITH/WHERE
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WHERE b.name = 'Austria'
- WITH m.name AS city,b.name AS country
- WHERE city = 'Vienna'
- RETURN city,country
-$$) AS (City agtype, Country agtype);
+ MATCH (m)-[e]->(b)
+ WHERE label(e) = 'KNOWS'
+ WITH *
+ WHERE m.name = 'Andres'
+ RETURN m.name,label(e),b.name
+$$) AS (N1 agtype, edge agtype, N2 agtype);
-- WITH/ORDER BY
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- ORDER BY id(m) DESC LIMIT 10
- RETURN id(city),city
-$$) AS (id agtype, city agtype);
+ MATCH (n)
+ WITH n
+ ORDER BY id(n)
+ RETURN n
+$$) as (name agtype);
-SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)
- WITH m AS city
- ORDER BY id(m) ASC LIMIT 10
- RETURN id(city),city.name
-$$) AS (id agtype, names agtype);
+-- WITH/ORDER BY/DESC
--- WITH/ORDER BY/DESC/WHERE
+SELECT * FROM cypher('cypher_with', $$
+ MATCH (n)
+ WITH n
+ ORDER BY n.name DESC LIMIT 3
+ RETURN collect(n.name)
+$$) as (names agtype);
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- ORDER BY id(m) DESC LIMIT 10
- WHERE city.name = 'Zell' OR city.name = 'Umberg'
- RETURN id(city),city.name,country.name
-$$) AS (id agtype, city agtype, country agtype);
+ MATCH (n {name: 'Andres'})-[]-(m)
+ WITH m
+ ORDER BY m.name DESC LIMIT 1
+ MATCH (m)-[]-(o)
+ RETURN o.name ORDER BY o.name
+$$) as (name agtype);
-- multiple WITH clauses
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- WITH city LIMIT 10
- RETURN city.name
-$$) AS (city agtype);
+ MATCH (n)-[e]->(m)
+ WITH n, e, m
+ WHERE label(e) = 'KNOWS'
+ WITH n.name as n1, label(e) as edge, m.name as n2
+ WHERE n1 = 'Andres'
+ RETURN n1,edge,n2
+$$) AS (N1 agtype, edge agtype, N2 agtype);
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- ORDER BY id(m) DESC LIMIT 10
- WITH city
- WHERE city.name = 'Zell'
- RETURN id(city),city.name
-$$) AS (id agtype, city agtype);
+ UNWIND [1, 2, 3, 4, 5, 6] AS x
+ WITH x
+ WHERE x > 2
+ WITH x
+ LIMIT 5
+ RETURN x
+$$) as (name agtype);
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- WHERE country.name = 'Austria'
- WITH city
- ORDER BY id(city) DESC
- WHERE city.name = 'Zell'
- RETURN id(city),city.name
-$$) AS (id agtype, city agtype);
+ MATCH (m)-[]->(b)
+ WITH m,b
+ ORDER BY id(m) DESC LIMIT 5
+ WITH m as start_node, b as end_node
+ WHERE end_node.name = 'George'
+ RETURN id(start_node),start_node.name,id(end_node),end_node.name
+$$) AS (id1 agtype, name1 agtype, id2 agtype, name2 agtype);
-- Expression item must be aliased.
@@ -171,27 +164,27 @@ SELECT * FROM cypher('cypher_with', $$
$$) AS (i int);
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
+ MATCH (m)-[]->(b)
WITH id(m)
RETURN m
-$$) AS (id agtype, city agtype);
+$$) AS (id agtype);
-- Reference undefined variable in WITH clause (should error out)
SELECT count(*) FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
+ MATCH (m)-[]->(b)
WITH m
RETURN m,b
-$$) AS (City agtype, Country agtype);
+$$) AS (a agtype, b agtype);
SELECT * FROM cypher('cypher_with', $$
- MATCH (m:City)-[:has_city]->(b:Country {iso2 : 'AT'})
- WITH m AS city,b AS country
- WHERE country.name = 'Austria'
- WITH city
- WHERE city.name = 'Zell'
- RETURN id(city),country.name
-$$) AS (id agtype, country agtype);
+ MATCH (m)-[]->(b)
+ WITH m AS start_node,b AS end_node
+ WHERE start_node.name = 'Andres'
+ WITH start_node
+ WHERE start_node.name = 'George'
+ RETURN id(start_node),end_node.name
+$$) AS (id agtype, node agtype);
-- Clean up
@@ -219,15 +212,15 @@ $$) AS (n agtype, d agtype);
-- Issue 396 (should error out)
SELECT * FROM cypher('graph',$$
- CREATE (v),(u),(w),
- (v)-[:hasFriend]->(u),
- (u)-[:hasFriend]->(w)
+ CREATE (v),(u),(w),
+ (v)-[:hasFriend]->(u),
+ (u)-[:hasFriend]->(w)
$$) as (a agtype);
SELECT * FROM cypher('graph',$$
- MATCH p=(v)-[*1..2]->(u)
- WITH p,length(p) AS path_length
- RETURN v,path_length
+ MATCH p=(v)-[*1..2]->(u)
+ WITH p,length(p) AS path_length
+ RETURN v,path_length
$$) as (a agtype,b agtype);
-- Clean up