regress/sql/cypher_with.sql

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */

LOAD 'age';
SET search_path TO ag_catalog;

--
-- Load data
--
SELECT create_graph('cypher_with');

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 * 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 (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 (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
    RETURN b
$$) AS (b bool);

-- WITH/WHERE

SELECT * FROM cypher('cypher_with', $$
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 (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)-[*1..2]->(b) 
    WITH p, length(p) AS path_length 
    WHERE path_length > 1 
    RETURN p
$$) AS (pattern agtype);

-- MATCH/WHERE with WITH/WHERE

SELECT * FROM cypher('cypher_with', $$
    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 (n)
    WITH n
    ORDER BY id(n)
    RETURN n
$$) as (name agtype);

-- WITH/ORDER BY/DESC

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 (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 (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', $$
    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)-[]->(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.

SELECT * FROM cypher('cypher_with', $$
    WITH 1 + 1
    RETURN i
$$) AS (i int);

SELECT * FROM cypher('cypher_with', $$
    MATCH (m)-[]->(b)
    WITH id(m)
    RETURN m
$$) AS (id agtype);

-- Reference undefined variable in WITH clause (should error out)

SELECT count(*) FROM cypher('cypher_with', $$
    MATCH (m)-[]->(b)
    WITH m  
    RETURN m,b
$$) AS (a agtype, b agtype);

SELECT * FROM cypher('cypher_with', $$
    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

SELECT drop_graph('cypher_with', true);

-- Issue 329 (should error out)

SELECT create_graph('graph');

SELECT * FROM cypher('graph', $$
    CREATE (a:A)-[:incs]->(:C), (a)-[:incs]->(:C)
    RETURN a
$$) AS (n agtype);

SELECT * FROM cypher('graph', $$
    MATCH (a:A) 
    WHERE ID(a)=0 
    WITH a 
    OPTIONAL MATCH (a)-[:incs]->(c)-[d:incs]-() 
    WITH a,c,COUNT(d) AS deps 
    WHERE deps<=1 
    RETURN c,d
$$) AS (n agtype, d agtype);

-- Issue 396 (should error out)

SELECT * FROM cypher('graph',$$
    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
$$) as (a agtype,b agtype);

-- Clean up

SELECT drop_graph('graph', true);

--
-- End of test
--