null_pagination_issue.sql

-- PostgreSQL Row Compare NULL Issue - Focused Demonstration
-- Demonstrates how keyset pagination can "lose" rows when NULLs are present
-- in lower-order row compare members

DROP TABLE IF EXISTS demo_table CASCADE;

CREATE TABLE demo_table (
    a INTEGER NOT NULL,
    b INTEGER,
    c INTEGER,
    payload TEXT
);

-- Insert data ensuring NULLs exist for each value of a
DO $$
BEGIN
    FOR a_val IN 1..10 LOOP
        -- Insert regular rows with non-NULL values
        FOR b_val IN 1..10 LOOP
            FOR c_val IN 1..10 LOOP
                IF random() < 0.3 THEN  -- Insert ~30% of possible combinations
                    INSERT INTO demo_table VALUES
                        (a_val, b_val, c_val, format('row_%s_%s_%s', a_val, b_val, c_val));
                END IF;
            END LOOP;
        END LOOP;

        -- Ensure at least one NULL in b for each a
        INSERT INTO demo_table VALUES
            (a_val, NULL, (a_val * 3) % 10 + 1, format('null_b_a%s', a_val));

        -- Ensure at least one NULL in c for each a
        INSERT INTO demo_table VALUES
            (a_val, (a_val * 7) % 10 + 1, NULL, format('null_c_a%s', a_val));

        -- Add some rows with both b and c as NULL
        IF a_val % 2 = 0 THEN
            INSERT INTO demo_table VALUES
                (a_val, NULL, NULL, format('both_null_a%s', a_val));
        END IF;
    END LOOP;
END $$;

-- Create composite index
CREATE INDEX idx_demo ON demo_table(a, b, c);
ANALYZE demo_table;

-- Show data statistics
SELECT
    COUNT(*) as total_rows,
    SUM(CASE WHEN b IS NULL THEN 1 ELSE 0 END) as rows_with_null_b,
    SUM(CASE WHEN c IS NULL THEN 1 ELSE 0 END) as rows_with_null_c,
    SUM(CASE WHEN b IS NULL AND c IS NULL THEN 1 ELSE 0 END) as rows_with_both_null
FROM demo_table;

\echo ''
\echo '==========================='
\echo 'QUERY 1: Single row compare'
\echo '==========================='

-- Query 1: Get all rows in one go using row compare
WITH all_rows_single_query AS (
    SELECT a, b, c, payload
    FROM demo_table
    WHERE (a, b, c) > (0, 0, 0)
    ORDER BY a, b, c
)
SELECT COUNT(*) as "Rows returned by single query"
FROM all_rows_single_query;

\echo ''
\echo '====================================='
\echo 'QUERY 2: Keyset pagination simulation'
\echo '====================================='

-- Query 2: Simulate keyset pagination breaking results into chunks
WITH keyset_pages AS (
    -- Page 1: Get first chunk
    SELECT a, b, c, payload, 1 as page_num
    FROM demo_table
    WHERE (a, b, c) > (0, 0, 0)
      AND (a, b, c) <= (3, 5, 5)

    UNION ALL

    -- Page 2: Continue from where page 1 ended
    SELECT a, b, c, payload, 2 as page_num
    FROM demo_table
    WHERE (a, b, c) > (3, 5, 5)
      AND (a, b, c) <= (6, 5, 5)

    UNION ALL

    -- Page 3: Continue from where page 2 ended
    SELECT a, b, c, payload, 3 as page_num
    FROM demo_table
    WHERE (a, b, c) > (6, 5, 5)
      AND (a, b, c) <= (9, 5, 5)

    UNION ALL

    -- Page 4: Get remaining rows
    SELECT a, b, c, payload, 4 as page_num
    FROM demo_table
    WHERE (a, b, c) > (9, 5, 5)
)
SELECT COUNT(*) as "Rows returned by keyset pagination"
FROM keyset_pages;