rpr_nav_nesting_test.txt

-- ============================================================
-- RPR Navigation Nesting Tests
-- Tests for prohibited nesting of PREV/NEXT (SQL standard 5.6)
-- ============================================================
--
-- The SQL standard prohibits:
--   - PREV/NEXT nested within PREV/NEXT
--   - PREV/NEXT nested within other functions inside PREV/NEXT
--
-- These should all produce syntax errors, not server crashes.
-- ============================================================

CREATE TEMP TABLE nav_test (id INT, price INT);
INSERT INTO nav_test VALUES (1, 10), (2, 20), (3, 15), (4, 25), (5, 30);

-- PREV(PREV(price)) - direct nesting
SELECT * FROM nav_test WINDOW w AS (
    ORDER BY id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    PATTERN (A B+)
    DEFINE B AS prev(prev(b.price)) > 0
);

-- NEXT(NEXT(price)) - direct nesting
SELECT * FROM nav_test WINDOW w AS (
    ORDER BY id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    PATTERN (A B+)
    DEFINE B AS next(next(b.price)) > 0
);

-- PREV(NEXT(price)) - cross nesting
SELECT * FROM nav_test WINDOW w AS (
    ORDER BY id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    PATTERN (A B+)
    DEFINE B AS prev(next(b.price)) > 0
);

-- NEXT(PREV(price)) - cross nesting
SELECT * FROM nav_test WINDOW w AS (
    ORDER BY id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    PATTERN (A B+)
    DEFINE B AS next(prev(b.price)) > 0
);

-- PREV inside expression inside PREV
SELECT * FROM nav_test WINDOW w AS (
    ORDER BY id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    PATTERN (A B+)
    DEFINE B AS prev(abs(prev(b.price))) > 0
);

-- NEXT inside expression inside NEXT
SELECT * FROM nav_test WINDOW w AS (
    ORDER BY id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    PATTERN (A B+)
    DEFINE B AS next(abs(next(b.price))) > 0
);

DROP TABLE nav_test;