regress/sql/age_reduce.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;

SELECT create_graph('reduce');

--
-- Basic folds
--
-- sum
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x)
$$) AS (result agtype);

-- sum of a longer list
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] | s + x)
$$) AS (result agtype);

-- product (factorial)
SELECT * FROM cypher('reduce', $$
    RETURN reduce(p = 1, x IN [1, 2, 3, 4, 5] | p * x)
$$) AS (result agtype);

-- non-zero initial accumulator
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 100, x IN [1, 2, 3] | s + x)
$$) AS (result agtype);

-- single element
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [42] | s + x)
$$) AS (result agtype);

--
-- List order is significant
--
-- left-associative subtraction: ((((0-1)-2)-3)-4) = -10
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2, 3, 4] | s - x)
$$) AS (result agtype);

-- forward string concatenation
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = '', x IN ['a', 'b', 'c'] | s + x)
$$) AS (result agtype);

-- reverse string concatenation (element before accumulator)
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = '', x IN ['a', 'b', 'c'] | x + s)
$$) AS (result agtype);

--
-- Empty and NULL list semantics
--
-- empty list returns the initial value
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [] | s + x)
$$) AS (result agtype);

-- empty list returns the initial value (non-zero)
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 999, x IN [] | s + x)
$$) AS (result agtype);

-- NULL list returns NULL
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN null | s + x)
$$) AS (result agtype);

-- empty list with a NULL initial value yields NULL: the list is empty (not
-- null) so the fold runs over zero rows, and COALESCE(<no rows>, init) is
-- COALESCE(NULL, NULL) -> NULL. (Distinct from a NULL *list*, which the outer
-- CASE short-circuits to NULL, and from a non-empty list with a NULL init,
-- which seeds the accumulator with agtype 'null'.)
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = null, x IN [] | s + x)
$$) AS (result agtype);

--
-- NULL handling within the fold
--
-- a NULL element propagates through arithmetic to NULL
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, null, 3] | s + x)
$$) AS (result agtype);

-- NULL initial value
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = null, x IN [1, 2, 3] | s)
$$) AS (result agtype);

-- a body that always evaluates to null yields null, NOT the initial value:
-- every step stores agtype 'null' as the running state, so the final state is
-- a real agtype 'null' and the empty-list COALESCE(..., init) guard must not
-- resurrect the initial value here (the load-bearing fold-to-null vs empty-list
-- distinction)
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 7, x IN [1, 2, 3] | null)
$$) AS (result agtype);

-- the accumulator legitimately becomes null mid-fold and the body climbs back
-- out of it: element 2 sets the accumulator to null, element 3 produces a fresh
-- non-null value, and element 4 reads that recovered state (999 + 4), proving a
-- null intermediate state does not poison the rest of the fold
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2, 3, 4] |
                  CASE WHEN x = 2 THEN null
                       WHEN x = 3 THEN 999
                       ELSE s + x END)
$$) AS (result agtype);

--
-- Errors raised from the fold body propagate cleanly
--
-- a type error in the body (agtype number + map) aborts the statement rather
-- than corrupting the running aggregate state or crashing the backend
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2] | s + {a: 1})
$$) AS (result agtype);

-- a runtime arithmetic error in the body (division by zero) likewise aborts
-- the fold; the error surfaces from the standalone per-element evaluator
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 1, x IN [1, 0, 2] | s / x)
$$) AS (result agtype);

--
-- Building a list with the accumulator
--
-- collect squares
SELECT * FROM cypher('reduce', $$
    RETURN reduce(acc = [], x IN [1, 2, 3] | acc + [x * x])
$$) AS (result agtype);

--
-- Value types in the fold
--
-- a float accumulator and float elements
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0.0, x IN [1.5, 2.5, 3.0] | s + x)
$$) AS (result agtype);

-- negative numbers
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [-1, -2, -3] | s + x)
$$) AS (result agtype);

-- a map accumulator passed through unchanged
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = {n: 0}, x IN [1, 2, 3] | s)
$$) AS (result agtype);

-- elements that are themselves lists, indexed in the body
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [[1, 2], [3, 4], [5, 6]] | s + x[0])
$$) AS (result agtype);

--
-- Function calls in the fold body
--
-- a scalar function applied to the element
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN ['a', 'bb', 'ccc'] | s + size(x))
$$) AS (result agtype);

-- the list itself produced by a function
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN range(1, 5) | s + x)
$$) AS (result agtype);

--
-- Composing reduce() with surrounding expressions
--
-- the reduce() result consumed by another function
SELECT * FROM cypher('reduce', $$
    RETURN size(reduce(s = [], x IN [1, 2, 3, 4] | s + [x]))
$$) AS (result agtype);

-- the reduce() result used in a comparison
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x) = 6
$$) AS (result agtype);

--
-- A conditional body (CASE)
--
-- sum of even elements only
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2, 3, 4, 5, 6] | CASE WHEN x % 2 = 0 THEN s + x ELSE s END)
$$) AS (result agtype);

--
-- Boolean and comparison fold bodies
--
-- the body evaluates to a boolean, which is normalized to an agtype boolean
-- (a boolean accumulator is a real Cypher use case for "all"/"any" style folds)
-- logical AND fold: all true?
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = true, x IN [true, true, false] | s AND x)
$$) AS (result agtype);

-- logical OR fold: any true?
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = false, x IN [false, true, false] | s OR x)
$$) AS (result agtype);

-- a comparison body
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = false, x IN [1, 2, 3] | x = 2)
$$) AS (result agtype);

-- "does any element equal 2?" (search fold)
SELECT * FROM cypher('reduce', $$
    RETURN reduce(found = false, x IN [1, 2, 3] | found OR x = 2)
$$) AS (result agtype);

-- "are all elements positive?" (using a comparison inside the fold)
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = true, x IN [1, 2, 3] | s AND x > 0)
$$) AS (result agtype);

SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = true, x IN [1, -2, 3] | s AND x > 0)
$$) AS (result agtype);

--
-- Property access on the element variable
--
-- sum a field across a list of maps
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [{n: 1}, {n: 2}, {n: 3}] | s + x.n)
$$) AS (result agtype);

-- concatenate a string field across a list of maps
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = '', x IN [{w: 'a'}, {w: 'b'}, {w: 'c'}] | s + x.w)
$$) AS (result agtype);

--
-- Multiple reduce() in one expression
--
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x) + reduce(p = 1, y IN [2, 3] | p * y)
$$) AS (result agtype);

--
-- reduce() in a boolean expression
--
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x) > 5
           AND reduce(p = 1, y IN [2, 3] | p * y) < 10
$$) AS (result agtype);

--
-- reduce() nested in the list or initial value of another reduce()
--
-- nesting is allowed in the list and the initial value (both evaluated in the
-- outer context) even though it is rejected inside the fold body.
-- nested reduce() in the list
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [reduce(a = 0, y IN [1, 2, 3] | a + y), 10] | s + x)
$$) AS (result agtype);

-- nested reduce() in the initial value
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = reduce(a = 0, y IN [1, 2, 3] | a + y), x IN [10, 20] | s + x)
$$) AS (result agtype);

--
-- reduce() over a correlated (per-row) list
--
SELECT * FROM cypher('reduce', $$
    UNWIND [[1, 2], [3, 4, 5], []] AS arr
    RETURN reduce(s = 0, x IN arr | s + x) AS total
    ORDER BY total
$$) AS (result agtype);

--
-- reduce() with the list and initial value bound in an outer clause
--
SELECT * FROM cypher('reduce', $$
    WITH [10, 20, 30] AS ns
    RETURN reduce(t = 0, n IN ns | t + n)
$$) AS (result agtype);

-- the initial value may reference an outer variable (correlation is allowed
-- in the init and the list, only not in the body)
SELECT * FROM cypher('reduce', $$
    WITH 5 AS base
    RETURN reduce(s = base, x IN [1, 2, 3] | s + x)
$$) AS (result agtype);

--
-- reduce() nested inside a list comprehension
--
SELECT * FROM cypher('reduce', $$
    RETURN [v IN [1, 2, 3] | reduce(s = 0, x IN [v, v, v] | s + x)]
$$) AS (result agtype);

--
-- reduce() in a WHERE clause
--
SELECT * FROM cypher('reduce', $$
    UNWIND [[1, 2, 3], [1, 1], [10]] AS l
    WITH l WHERE reduce(s = 0, x IN l | s + x) > 3
    RETURN l
    ORDER BY l
$$) AS (result agtype);

--
-- reduce() over graph data (the canonical Cypher example)
--
SELECT * FROM cypher('reduce', $$
    CREATE (:person {name: 'Alice', age: 38}),
           (:person {name: 'Bob', age: 25}),
           (:person {name: 'Daniel', age: 54})
$$) AS (result agtype);

-- sum the ages of all person nodes
SELECT * FROM cypher('reduce', $$
    MATCH (p:person)
    WITH collect(p) AS people
    RETURN reduce(total = 0, n IN people | total + n.age)
$$) AS (result agtype);

--
-- reduce() over a graph node's list property
--
SELECT * FROM cypher('reduce', $$
    CREATE (:bag {name: 'low', vals: [1, 2, 3]}),
           (:bag {name: 'mid', vals: [5, 5, 5]}),
           (:bag {name: 'high', vals: [10, 20, 30]})
$$) AS (result agtype);

-- filter nodes by a reduce() over their list property
SELECT * FROM cypher('reduce', $$
    MATCH (u:bag) WHERE reduce(s = 0, x IN u.vals | s + x) > 10
    RETURN u.name
    ORDER BY u.name
$$) AS (result agtype);

-- compute a reduce() value per node and order by it
SELECT * FROM cypher('reduce', $$
    MATCH (u:bag)
    RETURN u.name AS name, reduce(s = 0, x IN u.vals | s + x) AS total
    ORDER BY total
$$) AS (name agtype, total agtype);

--
-- Outer references in the fold body
--
-- The body may reference loop-invariant values from the enclosing query: an
-- outer variable, a property of an outer variable, or a cypher() parameter.
-- a plain outer variable in the body
SELECT * FROM cypher('reduce', $$
    WITH 5 AS w
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x + w)
$$) AS (result agtype);

-- an outer variable used as a multiplier
SELECT * FROM cypher('reduce', $$
    WITH 3 AS factor
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x * factor)
$$) AS (result agtype);

-- two distinct outer variables in the body
SELECT * FROM cypher('reduce', $$
    WITH 2 AS a, 100 AS b
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x * a + b)
$$) AS (result agtype);

-- a property of an outer (graph) variable in the body
SELECT * FROM cypher('reduce', $$
    MATCH (u:bag) WHERE u.name = 'mid'
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x + u.vals[0])
$$) AS (result agtype);

-- the same outer variable referenced more than once in the body
SELECT * FROM cypher('reduce', $$
    WITH 7 AS k
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + k + k)
$$) AS (result agtype);

-- a property of an outer map referenced in the body
SELECT * FROM cypher('reduce', $$
    WITH {factor: 10} AS m
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x * m.factor)
$$) AS (result agtype);

-- a subexpression that mixes an outer reference with the element: only the
-- loop-invariant part (the outer list) is captured, the element index is not
SELECT * FROM cypher('reduce', $$
    WITH [10, 20, 30] AS lookup
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + lookup[x - 1])
$$) AS (result agtype);

-- an outer reference inside a CASE branch of the body is captured
SELECT * FROM cypher('reduce', $$
    WITH 10 AS w
    RETURN reduce(s = 0, x IN [1, 2, 3] | CASE WHEN x % 2 = 0 THEN s + w ELSE s + x END)
$$) AS (result agtype);

-- a NULL outer value propagates through the fold
SELECT * FROM cypher('reduce', $$
    WITH null AS w
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x + w)
$$) AS (result agtype);

-- multiple outer captures with a mix of NULL and non-NULL: each is bound to its
-- own slot (the non-NULL multiplier is bound and the NULL still propagates)
SELECT * FROM cypher('reduce', $$
    WITH 3 AS a, null AS b
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + x * a + b)
$$) AS (result agtype);

-- an outer variable that changes per row is captured per group
SELECT * FROM cypher('reduce', $$
    UNWIND [1, 2, 3] AS m
    RETURN reduce(s = 0, x IN [1, 2, 3, 4] | s + x * m) AS total
    ORDER BY total
$$) AS (result agtype);

--
-- Short-circuit evaluation is preserved for outer references in the body
--
-- Only the outer leaf is captured; operators and CASE/AND/OR branches stay in
-- the body, so a guarded outer sub-expression is not evaluated on a branch
-- that is not taken. Each case below would divide by zero if the whole "1/w"
-- were hoisted into an eagerly evaluated aggregate argument instead.
-- the THEN branch is never taken, so "1/w" is not evaluated (expect 6)
SELECT * FROM cypher('reduce', $$
    WITH 0 AS w
    RETURN reduce(s = 0, x IN [1, 2, 3] | CASE WHEN false THEN s + 1/w ELSE s + x END)
$$) AS (result agtype);

-- the ELSE branch is never taken, so "1/w" is not evaluated (expect 6)
SELECT * FROM cypher('reduce', $$
    WITH 0 AS w
    RETURN reduce(s = 0, x IN [1, 2, 3] | CASE WHEN true THEN s + x ELSE s + 1/w END)
$$) AS (result agtype);

-- OR short-circuits once "w = 0" is true, so "1/w > 0" is not evaluated
SELECT * FROM cypher('reduce', $$
    WITH 0 AS w
    RETURN reduce(s = true, x IN [1, 2, 3] | s AND (w = 0 OR 1/w > 0))
$$) AS (result agtype);

-- AND short-circuits once "w <> 0" is false, so "1/w > 0" is not evaluated
SELECT * FROM cypher('reduce', $$
    WITH 0 AS w
    RETURN reduce(s = true, x IN [1, 2, 3] | s AND (w <> 0 AND 1/w > 0))
$$) AS (result agtype);

-- coalesce short-circuits: "1/w" is not evaluated when arg 1 is non-null
SELECT * FROM cypher('reduce', $$
    WITH 0 AS w
    RETURN reduce(s = 0, x IN [1, 2, 3] | s + coalesce(w, 1/w))
$$) AS (result agtype);

-- when the guarded branch is taken, the outer sub-expression is evaluated
-- normally (division by a non-zero outer value): x = 2 -> s + 10/2 (expect 9)
SELECT * FROM cypher('reduce', $$
    WITH 2 AS w
    RETURN reduce(s = 0, x IN [1, 2, 3] | CASE WHEN x % 2 = 0 THEN s + 10/w ELSE s + x END)
$$) AS (result agtype);

--
-- Not-yet-supported constructs raise a clean feature error
--
-- a nested reduce() in the body (any subquery in the body is unsupported)
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2] | s + reduce(t = 0, y IN [x] | t + y))
$$) AS (result agtype);

-- an aggregate function in the body
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2] | s + count(x))
$$) AS (result agtype);

--
-- Syntax errors: each required piece of the reduce() form is enforced
--
-- missing "= init"
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s, x IN [1, 2] | s + x)
$$) AS (result agtype);

-- missing ", var IN list"
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0 | s)
$$) AS (result agtype);

-- missing "| body"
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x IN [1, 2])
$$) AS (result agtype);

-- a qualified iterator variable is not allowed
SELECT * FROM cypher('reduce', $$
    RETURN reduce(s = 0, x.y IN [1, 2] | s)
$$) AS (result agtype);

--
-- cypher() parameter referenced in the fold body (via a prepared statement)
--
PREPARE reduce_param(agtype) AS
    SELECT * FROM cypher('reduce', $$
        RETURN reduce(s = 0, x IN [1, 2, 3] | s + x + $p)
    $$, $1) AS (result agtype);

EXECUTE reduce_param('{"p": 10}');

EXECUTE reduce_param('{"p": 100}');

DEALLOCATE reduce_param;

--
-- "reduce" as a property key name (safe_keywords backward compatibility):
-- because reduce() introduced a reserved keyword, confirm the word is still
-- usable as a map key, the same way any/none/single are.
--
SELECT * FROM cypher('reduce', $$
    RETURN {reduce: 1, any: 2, none: 3}
$$) AS (result agtype);

--
-- Cleanup
--
SELECT * FROM drop_graph('reduce', true);