lateral.sql

-- 0) (Optional) Create a test database for isolation
CREATE DATABASE IF NOT EXISTS test;
USE test;

-- 1) Drop existing tables
DROP TABLE IF EXISTS edges;
DROP TABLE IF EXISTS nodes;

-- 2) Create nodes table
CREATE TABLE nodes (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 3) Create edges table
CREATE TABLE edges (
    from_id INT,
    to_id INT,
    weight INT,
    PRIMARY KEY (from_id, to_id)
);

-- 4) Insert nodes
INSERT INTO nodes (id, name) VALUES
(1, 'Node_1'),
(10, 'Node_2'),
(11, 'Node_3'),
(100, 'Node_4');

-- 5) Insert edges
INSERT INTO edges (from_id, to_id, weight) VALUES
-- Layer 1: from node 1
(1, 10, 100),
(1, 11, 95),
(1, 12, 90),
(1, 13, 85),
-- Layer 2
(10, 100, 50),
(10, 101, 40),
(10, 102, 30),
(11, 110, 55),
(11, 111, 45),
(11, 112, 35),
-- Layer 3
(100, 1000, 10),
(100, 1001, 5);

---------------------------------------------------------------------
-- Query without LATERAL JOIN support
-- This approach joins all outgoing edges at each step.
-- It is NOT possible to apply LIMIT per node in recursive expansion.
---------------------------------------------------------------------
WITH RECURSIVE walk AS (
    SELECT
        from_id,
        to_id,
        weight,
        1 AS depth
    FROM edges
    WHERE from_id = 1

    UNION ALL

    SELECT
        e.from_id,
        e.to_id,
        e.weight,
        w.depth + 1
    FROM walk w
    JOIN edges e
      ON e.from_id = w.to_id
    WHERE w.depth < 3
)
SELECT * FROM walk;

---------------------------------------------------------------------
-- Query using LATERAL JOIN
-- This allows applying ORDER BY + LIMIT per node
-- during recursive traversal.
---------------------------------------------------------------------
WITH RECURSIVE walk AS (
    -- Anchor: start from root node 1
    SELECT
        e.from_id,
        e.to_id,
        e.weight,
        1 AS depth
    FROM edges e
    WHERE e.from_id = 1

    UNION ALL

    -- Recursive step with per-node LIMIT using LATERAL
    SELECT
        t.from_id,
        t.to_id,
        t.weight,
        w.depth + 1
    FROM walk w,
    LATERAL (
        SELECT
            from_id,
            to_id,
            weight
        FROM edges
        WHERE from_id = w.to_id
        ORDER BY weight DESC
        LIMIT 2
    ) AS t
    WHERE w.depth < 3
)
SELECT * FROM walk;