This is an automated email from the ASF dual-hosted git repository. jgemignani pushed a commit to branch PG12 in repository https://gitbox.apache.org/repos/asf/age.git
commit 1123878a0c28bebe40455556c72ffbad1ca6f405 Author: Muhammad Taha Naveed <[email protected]> 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
