Skip to content

Recursion

Recursive predicates compute transitive closures, relationships that span an arbitrary number of hops. This is the kind of query that is impossible to write correctly in plain SQL without engine-specific recursive CTEs.

Typical uses: org charts, referral chains, product taxonomies, bill of materials, dependency graphs.

Transitive closure

Define a base case and a recursive case, and put the @Recursive directive before the rules with an iteration limit:

@Recursive(AllManagers, 20);

# Base case: direct manager
AllManagers(employee_id:, manager_id:) :- Employees(employee_id:, manager_id:);

# Recursive case: manager's managers
AllManagers(employee_id:, manager_id:) :-
  AllManagers(employee_id:, manager_id: intermediate),
  Employees(employee_id: intermediate, manager_id:);

Shortest paths

Find shortest paths in weighted graphs by enumerating route costs recursively, then keeping the minimum per destination with a Min= aggregation:

# Enumerate route costs from the origin, hop by hop.
@Recursive(RouteCost, 10);
RouteCost(destination:, cost:) :-
  ShippingRoutes(origin: "warehouse_main", destination:, cost:);
RouteCost(destination:, cost: total) :-
  RouteCost(destination: hub, cost: hub_cost),
  ShippingRoutes(origin: hub, destination:, cost:),
  total == hub_cost + cost;

# Keep the cheapest cost per destination.
@OrderBy(ShippingCost, "destination");
ShippingCost(destination:, total? Min= cost) distinct :- RouteCost(destination:, cost:);

The @Recursive iteration limit bounds the path length, so cyclic route graphs terminate; the final aggregation keeps only the cheapest route per destination.

Cycle detection

The recursive closure of a parent/child edge detects cycles in a hierarchy: a node that is its own ancestor:

@Recursive(AncestorOf, 100);
AncestorOf(ancestor_id:, descendant_id:) :- ParentOf(parent_id: ancestor_id, child_id: descendant_id);
AncestorOf(ancestor_id:, descendant_id:) :-
  AncestorOf(ancestor_id:, descendant_id: intermediate),
  ParentOf(parent_id: intermediate, child_id: descendant_id);

HierarchyCycle(node_id:) :- AncestorOf(ancestor_id: node_id, descendant_id: node_id);

How recursion runs

A step applies every rule of the recursion once, also when predicates recurse through each other: @Recursive(Even, 5) over Even and Odd gives what five rounds of their rules give, whatever the engine and the depth.

Up to 20 steps, a recursion is unrolled into a single query. Past 20 steps, or with @Recursive(P, -1), it is computed into tables, step by step. On Presto, Databricks and Trino every recursion is computed into tables: the planners of Presto and Spark take time exponential in the number of unrolled steps (20 steps took minutes to plan on Presto, 12 on Spark), and Trino copies an unrolled recursion into every query that reads it, past its limit of stages when a query reads it several times. A table per step costs one short query each.

  • Until it converges. When Synalog runs a predicate (synalog ... run, synalog.execute), it stops a recursion as soon as a step changes nothing. A recursion costs the steps its data needs: @Recursive(AncestorOf, 100) over a hierarchy 6 levels deep takes 6 steps, and @Recursive(P, -1) recurses until nothing changes, on every engine.
  • One step, the new rows. A recursion of one distinct predicate, without aggregation, whose rules reference it at most once (the transitive closures above) is evaluated semi-naively: each step derives rows only from the rows the previous step added, so a step costs what changed, not the whole relation. Other recursions (an aggregate such as Min= in the recursion, mutual recursion, a rule joining the recursion with itself) recompute the relation at every step.
  • No tables left behind. The tables a run computes in Synalog's own schemas (logica_home, logica_test) are dropped once its rows are read.
  • A script. synalog.compile returns one SQL script, which cannot stop by itself: it writes every step out, up to the declared depth, and refuses a recursion too deep to write out. Run deep recursions with Synalog.

Safety

The verifier checks recursive programs at compile time: missing base cases, trivial loops, and unbounded recursion without @Recursive are all reported as errors before any SQL is generated.

Complete example

A management chain (transitive closure) and a shortest-path computation. The shortest path is written as a recursive RouteCost enumeration followed by a Min= aggregation per destination:

# run: AllManagers, ShippingCost
@Engine("duckdb");

Employees(employee_id: 1, manager_id: null);
Employees(employee_id: 2, manager_id: 1);
Employees(employee_id: 3, manager_id: 2);
Employees(employee_id: 4, manager_id: 2);

ShippingRoutes(origin: "warehouse_main", destination: "rotterdam", cost: 4);
ShippingRoutes(origin: "rotterdam", destination: "oslo", cost: 3);
ShippingRoutes(origin: "warehouse_main", destination: "oslo", cost: 9);
ShippingRoutes(origin: "oslo", destination: "helsinki", cost: 2);

# Transitive closure: every manager above an employee, at any depth.
@Recursive(AllManagers, 20);
@OrderBy(AllManagers, "employee_id", "manager_id");
AllManagers(employee_id:, manager_id:) :-
  Employees(employee_id:, manager_id:), manager_id is not null;
AllManagers(employee_id:, manager_id:) :-
  AllManagers(employee_id:, manager_id: intermediate),
  Employees(employee_id: intermediate, manager_id:), manager_id is not null;

# Shortest paths: enumerate route costs recursively...
@Recursive(RouteCost, 10);
RouteCost(destination:, cost:) :-
  ShippingRoutes(origin: "warehouse_main", destination:, cost:);
RouteCost(destination:, cost: total) :-
  RouteCost(destination: hub, cost: hub_cost),
  ShippingRoutes(origin: hub, destination:, cost:),
  total == hub_cost + cost;

# ...then keep the cheapest per destination.
@OrderBy(ShippingCost, "destination");
ShippingCost(destination:, total? Min= cost) distinct :- RouteCost(destination:, cost:);
Generated SQL and execution results 
$ synalog.check('recursion.l')
No errors found.

$ synalog.compile('recursion.l', 'AllManagers')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_41_Employees AS (SELECT * FROM (

    SELECT
      1 AS employee_id,
      null AS manager_id
   UNION ALL

    SELECT
      2 AS employee_id,
      1 AS manager_id
   UNION ALL

    SELECT
      3 AS employee_id,
      2 AS manager_id
   UNION ALL

    SELECT
      4 AS employee_id,
      2 AS manager_id

) AS UNUSED_TABLE_NAME  ),
t_38_AllManagers_r0 AS (SELECT * FROM (

    SELECT
      t_40_Employees.employee_id AS employee_id,
      t_40_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_40_Employees
    WHERE
      (t_40_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_36_AllManagers_r1 AS (SELECT * FROM (

    SELECT
      AllManagers_r0.employee_id AS employee_id,
      t_37_Employees.manager_id AS manager_id
    FROM
      t_38_AllManagers_r0 AS AllManagers_r0, t_41_Employees AS t_37_Employees
    WHERE
      (t_37_Employees.manager_id IS NOT null) AND
      (t_37_Employees.employee_id = AllManagers_r0.manager_id)
   UNION ALL

    SELECT
      t_42_Employees.employee_id AS employee_id,
      t_42_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_42_Employees
    WHERE
      (t_42_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_34_AllManagers_r2 AS (SELECT * FROM (

    SELECT
      AllManagers_r1.employee_id AS employee_id,
      t_35_Employees.manager_id AS manager_id
    FROM
      t_36_AllManagers_r1 AS AllManagers_r1, t_41_Employees AS t_35_Employees
    WHERE
      (t_35_Employees.manager_id IS NOT null) AND
      (t_35_Employees.employee_id = AllManagers_r1.manager_id)
   UNION ALL

    SELECT
      t_43_Employees.employee_id AS employee_id,
      t_43_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_43_Employees
    WHERE
      (t_43_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_32_AllManagers_r3 AS (SELECT * FROM (

    SELECT
      AllManagers_r2.employee_id AS employee_id,
      t_33_Employees.manager_id AS manager_id
    FROM
      t_34_AllManagers_r2 AS AllManagers_r2, t_41_Employees AS t_33_Employees
    WHERE
      (t_33_Employees.manager_id IS NOT null) AND
      (t_33_Employees.employee_id = AllManagers_r2.manager_id)
   UNION ALL

    SELECT
      t_44_Employees.employee_id AS employee_id,
      t_44_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_44_Employees
    WHERE
      (t_44_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_30_AllManagers_r4 AS (SELECT * FROM (

    SELECT
      AllManagers_r3.employee_id AS employee_id,
      t_31_Employees.manager_id AS manager_id
    FROM
      t_32_AllManagers_r3 AS AllManagers_r3, t_41_Employees AS t_31_Employees
    WHERE
      (t_31_Employees.manager_id IS NOT null) AND
      (t_31_Employees.employee_id = AllManagers_r3.manager_id)
   UNION ALL

    SELECT
      t_45_Employees.employee_id AS employee_id,
      t_45_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_45_Employees
    WHERE
      (t_45_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_28_AllManagers_r5 AS (SELECT * FROM (

    SELECT
      AllManagers_r4.employee_id AS employee_id,
      t_29_Employees.manager_id AS manager_id
    FROM
      t_30_AllManagers_r4 AS AllManagers_r4, t_41_Employees AS t_29_Employees
    WHERE
      (t_29_Employees.manager_id IS NOT null) AND
      (t_29_Employees.employee_id = AllManagers_r4.manager_id)
   UNION ALL

    SELECT
      t_46_Employees.employee_id AS employee_id,
      t_46_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_46_Employees
    WHERE
      (t_46_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_26_AllManagers_r6 AS (SELECT * FROM (

    SELECT
      AllManagers_r5.employee_id AS employee_id,
      t_27_Employees.manager_id AS manager_id
    FROM
      t_28_AllManagers_r5 AS AllManagers_r5, t_41_Employees AS t_27_Employees
    WHERE
      (t_27_Employees.manager_id IS NOT null) AND
      (t_27_Employees.employee_id = AllManagers_r5.manager_id)
   UNION ALL

    SELECT
      t_47_Employees.employee_id AS employee_id,
      t_47_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_47_Employees
    WHERE
      (t_47_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_24_AllManagers_r7 AS (SELECT * FROM (

    SELECT
      AllManagers_r6.employee_id AS employee_id,
      t_25_Employees.manager_id AS manager_id
    FROM
      t_26_AllManagers_r6 AS AllManagers_r6, t_41_Employees AS t_25_Employees
    WHERE
      (t_25_Employees.manager_id IS NOT null) AND
      (t_25_Employees.employee_id = AllManagers_r6.manager_id)
   UNION ALL

    SELECT
      t_48_Employees.employee_id AS employee_id,
      t_48_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_48_Employees
    WHERE
      (t_48_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_22_AllManagers_r8 AS (SELECT * FROM (

    SELECT
      AllManagers_r7.employee_id AS employee_id,
      t_23_Employees.manager_id AS manager_id
    FROM
      t_24_AllManagers_r7 AS AllManagers_r7, t_41_Employees AS t_23_Employees
    WHERE
      (t_23_Employees.manager_id IS NOT null) AND
      (t_23_Employees.employee_id = AllManagers_r7.manager_id)
   UNION ALL

    SELECT
      t_49_Employees.employee_id AS employee_id,
      t_49_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_49_Employees
    WHERE
      (t_49_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_20_AllManagers_r9 AS (SELECT * FROM (

    SELECT
      AllManagers_r8.employee_id AS employee_id,
      t_21_Employees.manager_id AS manager_id
    FROM
      t_22_AllManagers_r8 AS AllManagers_r8, t_41_Employees AS t_21_Employees
    WHERE
      (t_21_Employees.manager_id IS NOT null) AND
      (t_21_Employees.employee_id = AllManagers_r8.manager_id)
   UNION ALL

    SELECT
      t_50_Employees.employee_id AS employee_id,
      t_50_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_50_Employees
    WHERE
      (t_50_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_18_AllManagers_r10 AS (SELECT * FROM (

    SELECT
      AllManagers_r9.employee_id AS employee_id,
      t_19_Employees.manager_id AS manager_id
    FROM
      t_20_AllManagers_r9 AS AllManagers_r9, t_41_Employees AS t_19_Employees
    WHERE
      (t_19_Employees.manager_id IS NOT null) AND
      (t_19_Employees.employee_id = AllManagers_r9.manager_id)
   UNION ALL

    SELECT
      t_51_Employees.employee_id AS employee_id,
      t_51_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_51_Employees
    WHERE
      (t_51_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_16_AllManagers_r11 AS (SELECT * FROM (

    SELECT
      AllManagers_r10.employee_id AS employee_id,
      t_17_Employees.manager_id AS manager_id
    FROM
      t_18_AllManagers_r10 AS AllManagers_r10, t_41_Employees AS t_17_Employees
    WHERE
      (t_17_Employees.manager_id IS NOT null) AND
      (t_17_Employees.employee_id = AllManagers_r10.manager_id)
   UNION ALL

    SELECT
      t_52_Employees.employee_id AS employee_id,
      t_52_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_52_Employees
    WHERE
      (t_52_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_14_AllManagers_r12 AS (SELECT * FROM (

    SELECT
      AllManagers_r11.employee_id AS employee_id,
      t_15_Employees.manager_id AS manager_id
    FROM
      t_16_AllManagers_r11 AS AllManagers_r11, t_41_Employees AS t_15_Employees
    WHERE
      (t_15_Employees.manager_id IS NOT null) AND
      (t_15_Employees.employee_id = AllManagers_r11.manager_id)
   UNION ALL

    SELECT
      t_53_Employees.employee_id AS employee_id,
      t_53_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_53_Employees
    WHERE
      (t_53_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_12_AllManagers_r13 AS (SELECT * FROM (

    SELECT
      AllManagers_r12.employee_id AS employee_id,
      t_13_Employees.manager_id AS manager_id
    FROM
      t_14_AllManagers_r12 AS AllManagers_r12, t_41_Employees AS t_13_Employees
    WHERE
      (t_13_Employees.manager_id IS NOT null) AND
      (t_13_Employees.employee_id = AllManagers_r12.manager_id)
   UNION ALL

    SELECT
      t_54_Employees.employee_id AS employee_id,
      t_54_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_54_Employees
    WHERE
      (t_54_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_10_AllManagers_r14 AS (SELECT * FROM (

    SELECT
      AllManagers_r13.employee_id AS employee_id,
      t_11_Employees.manager_id AS manager_id
    FROM
      t_12_AllManagers_r13 AS AllManagers_r13, t_41_Employees AS t_11_Employees
    WHERE
      (t_11_Employees.manager_id IS NOT null) AND
      (t_11_Employees.employee_id = AllManagers_r13.manager_id)
   UNION ALL

    SELECT
      t_55_Employees.employee_id AS employee_id,
      t_55_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_55_Employees
    WHERE
      (t_55_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_8_AllManagers_r15 AS (SELECT * FROM (

    SELECT
      AllManagers_r14.employee_id AS employee_id,
      t_9_Employees.manager_id AS manager_id
    FROM
      t_10_AllManagers_r14 AS AllManagers_r14, t_41_Employees AS t_9_Employees
    WHERE
      (t_9_Employees.manager_id IS NOT null) AND
      (t_9_Employees.employee_id = AllManagers_r14.manager_id)
   UNION ALL

    SELECT
      t_56_Employees.employee_id AS employee_id,
      t_56_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_56_Employees
    WHERE
      (t_56_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_6_AllManagers_r16 AS (SELECT * FROM (

    SELECT
      AllManagers_r15.employee_id AS employee_id,
      t_7_Employees.manager_id AS manager_id
    FROM
      t_8_AllManagers_r15 AS AllManagers_r15, t_41_Employees AS t_7_Employees
    WHERE
      (t_7_Employees.manager_id IS NOT null) AND
      (t_7_Employees.employee_id = AllManagers_r15.manager_id)
   UNION ALL

    SELECT
      t_57_Employees.employee_id AS employee_id,
      t_57_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_57_Employees
    WHERE
      (t_57_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_4_AllManagers_r17 AS (SELECT * FROM (

    SELECT
      AllManagers_r16.employee_id AS employee_id,
      t_5_Employees.manager_id AS manager_id
    FROM
      t_6_AllManagers_r16 AS AllManagers_r16, t_41_Employees AS t_5_Employees
    WHERE
      (t_5_Employees.manager_id IS NOT null) AND
      (t_5_Employees.employee_id = AllManagers_r16.manager_id)
   UNION ALL

    SELECT
      t_58_Employees.employee_id AS employee_id,
      t_58_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_58_Employees
    WHERE
      (t_58_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_2_AllManagers_r18 AS (SELECT * FROM (

    SELECT
      AllManagers_r17.employee_id AS employee_id,
      t_3_Employees.manager_id AS manager_id
    FROM
      t_4_AllManagers_r17 AS AllManagers_r17, t_41_Employees AS t_3_Employees
    WHERE
      (t_3_Employees.manager_id IS NOT null) AND
      (t_3_Employees.employee_id = AllManagers_r17.manager_id)
   UNION ALL

    SELECT
      t_59_Employees.employee_id AS employee_id,
      t_59_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_59_Employees
    WHERE
      (t_59_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ),
t_0_AllManagers_r19 AS (SELECT * FROM (

    SELECT
      AllManagers_r18.employee_id AS employee_id,
      t_1_Employees.manager_id AS manager_id
    FROM
      t_2_AllManagers_r18 AS AllManagers_r18, t_41_Employees AS t_1_Employees
    WHERE
      (t_1_Employees.manager_id IS NOT null) AND
      (t_1_Employees.employee_id = AllManagers_r18.manager_id)
   UNION ALL

    SELECT
      t_60_Employees.employee_id AS employee_id,
      t_60_Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS t_60_Employees
    WHERE
      (t_60_Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  )
SELECT * FROM (

    SELECT
      AllManagers_r19.employee_id AS employee_id,
      Employees.manager_id AS manager_id
    FROM
      t_0_AllManagers_r19 AS AllManagers_r19, t_41_Employees AS Employees
    WHERE
      (Employees.manager_id IS NOT null) AND
      (Employees.employee_id = AllManagers_r19.manager_id)
   UNION ALL

    SELECT
      Employees.employee_id AS employee_id,
      Employees.manager_id AS manager_id
    FROM
      t_41_Employees AS Employees
    WHERE
      (Employees.manager_id IS NOT null)

) AS UNUSED_TABLE_NAME  ORDER BY employee_id, manager_id ;

-- Executed on DuckDB:
| employee_id | manager_id |
|-------------|------------|
| 2           | 1          |
| 3           | 1          |
| 3           | 2          |
| 4           | 1          |
| 4           | 2          |
(5 rows)

$ synalog.compile('recursion.l', 'ShippingCost')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_1_ShippingRoutes AS (SELECT * FROM (

    SELECT
      'warehouse_main' AS origin,
      'rotterdam' AS destination,
      4 AS cost
   UNION ALL

    SELECT
      'rotterdam' AS origin,
      'oslo' AS destination,
      3 AS cost
   UNION ALL

    SELECT
      'warehouse_main' AS origin,
      'oslo' AS destination,
      9 AS cost
   UNION ALL

    SELECT
      'oslo' AS origin,
      'helsinki' AS destination,
      2 AS cost

) AS UNUSED_TABLE_NAME  ),
t_30_RouteCost_r0 AS (SELECT * FROM (

    SELECT
      t_31_ShippingRoutes.destination AS destination,
      t_31_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_31_ShippingRoutes
    WHERE
      (t_31_ShippingRoutes.origin = 'warehouse_main')

) AS UNUSED_TABLE_NAME  ),
t_27_RouteCost_r1 AS (SELECT * FROM (

    SELECT
      t_28_ShippingRoutes.destination AS destination,
      t_28_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_28_ShippingRoutes
    WHERE
      (t_28_ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_29_ShippingRoutes.destination AS destination,
      ((RouteCost_r0.cost) + (t_29_ShippingRoutes.cost)) AS cost
    FROM
      t_30_RouteCost_r0 AS RouteCost_r0, t_1_ShippingRoutes AS t_29_ShippingRoutes
    WHERE
      (t_29_ShippingRoutes.origin = RouteCost_r0.destination)

) AS UNUSED_TABLE_NAME  ),
t_24_RouteCost_r2 AS (SELECT * FROM (

    SELECT
      t_25_ShippingRoutes.destination AS destination,
      t_25_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_25_ShippingRoutes
    WHERE
      (t_25_ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_26_ShippingRoutes.destination AS destination,
      ((RouteCost_r1.cost) + (t_26_ShippingRoutes.cost)) AS cost
    FROM
      t_27_RouteCost_r1 AS RouteCost_r1, t_1_ShippingRoutes AS t_26_ShippingRoutes
    WHERE
      (t_26_ShippingRoutes.origin = RouteCost_r1.destination)

) AS UNUSED_TABLE_NAME  ),
t_21_RouteCost_r3 AS (SELECT * FROM (

    SELECT
      t_22_ShippingRoutes.destination AS destination,
      t_22_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_22_ShippingRoutes
    WHERE
      (t_22_ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_23_ShippingRoutes.destination AS destination,
      ((RouteCost_r2.cost) + (t_23_ShippingRoutes.cost)) AS cost
    FROM
      t_24_RouteCost_r2 AS RouteCost_r2, t_1_ShippingRoutes AS t_23_ShippingRoutes
    WHERE
      (t_23_ShippingRoutes.origin = RouteCost_r2.destination)

) AS UNUSED_TABLE_NAME  ),
t_18_RouteCost_r4 AS (SELECT * FROM (

    SELECT
      t_19_ShippingRoutes.destination AS destination,
      t_19_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_19_ShippingRoutes
    WHERE
      (t_19_ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_20_ShippingRoutes.destination AS destination,
      ((RouteCost_r3.cost) + (t_20_ShippingRoutes.cost)) AS cost
    FROM
      t_21_RouteCost_r3 AS RouteCost_r3, t_1_ShippingRoutes AS t_20_ShippingRoutes
    WHERE
      (t_20_ShippingRoutes.origin = RouteCost_r3.destination)

) AS UNUSED_TABLE_NAME  ),
t_15_RouteCost_r5 AS (SELECT * FROM (

    SELECT
      t_16_ShippingRoutes.destination AS destination,
      t_16_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_16_ShippingRoutes
    WHERE
      (t_16_ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_17_ShippingRoutes.destination AS destination,
      ((RouteCost_r4.cost) + (t_17_ShippingRoutes.cost)) AS cost
    FROM
      t_18_RouteCost_r4 AS RouteCost_r4, t_1_ShippingRoutes AS t_17_ShippingRoutes
    WHERE
      (t_17_ShippingRoutes.origin = RouteCost_r4.destination)

) AS UNUSED_TABLE_NAME  ),
t_12_RouteCost_r6 AS (SELECT * FROM (

    SELECT
      t_13_ShippingRoutes.destination AS destination,
      t_13_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_13_ShippingRoutes
    WHERE
      (t_13_ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_14_ShippingRoutes.destination AS destination,
      ((RouteCost_r5.cost) + (t_14_ShippingRoutes.cost)) AS cost
    FROM
      t_15_RouteCost_r5 AS RouteCost_r5, t_1_ShippingRoutes AS t_14_ShippingRoutes
    WHERE
      (t_14_ShippingRoutes.origin = RouteCost_r5.destination)

) AS UNUSED_TABLE_NAME  ),
t_9_RouteCost_r7 AS (SELECT * FROM (

    SELECT
      t_10_ShippingRoutes.destination AS destination,
      t_10_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_10_ShippingRoutes
    WHERE
      (t_10_ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_11_ShippingRoutes.destination AS destination,
      ((RouteCost_r6.cost) + (t_11_ShippingRoutes.cost)) AS cost
    FROM
      t_12_RouteCost_r6 AS RouteCost_r6, t_1_ShippingRoutes AS t_11_ShippingRoutes
    WHERE
      (t_11_ShippingRoutes.origin = RouteCost_r6.destination)

) AS UNUSED_TABLE_NAME  ),
t_6_RouteCost_r8 AS (SELECT * FROM (

    SELECT
      t_7_ShippingRoutes.destination AS destination,
      t_7_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_7_ShippingRoutes
    WHERE
      (t_7_ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_8_ShippingRoutes.destination AS destination,
      ((RouteCost_r7.cost) + (t_8_ShippingRoutes.cost)) AS cost
    FROM
      t_9_RouteCost_r7 AS RouteCost_r7, t_1_ShippingRoutes AS t_8_ShippingRoutes
    WHERE
      (t_8_ShippingRoutes.origin = RouteCost_r7.destination)

) AS UNUSED_TABLE_NAME  ),
t_3_RouteCost_r9 AS (SELECT * FROM (

    SELECT
      t_4_ShippingRoutes.destination AS destination,
      t_4_ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS t_4_ShippingRoutes
    WHERE
      (t_4_ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_5_ShippingRoutes.destination AS destination,
      ((RouteCost_r8.cost) + (t_5_ShippingRoutes.cost)) AS cost
    FROM
      t_6_RouteCost_r8 AS RouteCost_r8, t_1_ShippingRoutes AS t_5_ShippingRoutes
    WHERE
      (t_5_ShippingRoutes.origin = RouteCost_r8.destination)

) AS UNUSED_TABLE_NAME  ),
t_0_RouteCost AS (SELECT * FROM (

    SELECT
      ShippingRoutes.destination AS destination,
      ShippingRoutes.cost AS cost
    FROM
      t_1_ShippingRoutes AS ShippingRoutes
    WHERE
      (ShippingRoutes.origin = 'warehouse_main')
   UNION ALL

    SELECT
      t_2_ShippingRoutes.destination AS destination,
      ((RouteCost_r9.cost) + (t_2_ShippingRoutes.cost)) AS cost
    FROM
      t_3_RouteCost_r9 AS RouteCost_r9, t_1_ShippingRoutes AS t_2_ShippingRoutes
    WHERE
      (t_2_ShippingRoutes.origin = RouteCost_r9.destination)

) AS UNUSED_TABLE_NAME  )
SELECT
  RouteCost.destination AS destination,
  MIN(RouteCost.cost) AS total
FROM
  t_0_RouteCost AS RouteCost
GROUP BY RouteCost.destination ORDER BY destination;

-- Executed on DuckDB:
| destination | total |
|-------------|-------|
| helsinki    | 9     |
| oslo        | 7     |
| rotterdam   | 4     |
(3 rows)