reproducer.sql

application/sql

Download

-- Cleanup preexisting tables
DROP TABLE IF EXISTS rc4;
DROP TABLE IF EXISTS rc3;
DROP TABLE IF EXISTS rc2;
DROP TABLE IF EXISTS rc1;
DROP FUNCTION IF EXISTS rc_select;

-- Generate table 1 + varchar index. Seed with dummy data.
CREATE TABLE rc1(
    id SERIAL PRIMARY KEY,
    description VARCHAR NOT NULL
);

INSERT INTO rc1(description)
SELECT left(md5(random()::text), 10) FROM generate_series(1, 1000) s(i);

CREATE UNIQUE INDEX rc1_desc_uniq ON rc1(description);

-- Generate table 2 references table 1 via varchar index. Seed with dummy data for subset of rows
CREATE TABLE rc2(
    id SERIAL PRIMARY KEY,
    rc1_reference VARCHAR REFERENCES rc1(description) NOT NULL,
    rc2_value VARCHAR NOT NULL
);

WITH ids AS (
    SELECT ((random() * 250)::int % 250) + 1 as id
             FROM generate_series(1, 250) s(i)
)
INSERT INTO rc2(rc1_reference, rc2_value)
SELECT rc1.description,left(md5(random()::text), 10) FROM ids JOIN rc1 ON ids.id = rc1.id;

-- Generate table 3 references table 2, seed with dummy data for subset of rows
CREATE TABLE rc3(
                    id SERIAL PRIMARY KEY,
                    rc2_reference INT REFERENCES rc2(id) NOT NULL,
                    rc3_value VARCHAR
);

WITH ids AS (
    SELECT ((random() * 250)::int % 250) + 1 as id
    FROM generate_series(1, 250) s(i)
)
INSERT INTO rc3(rc2_reference, rc3_value)
SELECT rc2.id,left(md5(random()::text), 10) FROM ids JOIN rc2 ON ids.id = rc2.id;

-- Generate table 4 references table 3, seed with dummy data for subset of rows
CREATE TABLE rc4(
                    id SERIAL PRIMARY KEY,
                    rc3_reference INT REFERENCES rc3(id) NOT NULL,
                    rc4_value VARCHAR
);

INSERT INTO rc4(rc3_reference, rc4_value)
SELECT (i % 250) + 1, left(md5(random()::text), 10) FROM generate_series(1, 5000) s(i);

-- Construct a function for lateral joins between table 3 and table 4, highlight scenarios where we hit the bug
CREATE OR REPLACE FUNCTION rc_select(in_id integer DEFAULT NULL::integer)
    RETURNS TABLE(input_id varchar, id integer, value_desc character varying)
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN QUERY
        SELECT COALESCE(in_id::varchar, 'BUG HIT'), rc4.id, rc4.rc4_value
        FROM rc4
        WHERE (in_id IS NULL OR in_id = rc4.rc3_reference);
END;
$BODY$;

-- Sample query to hit bug. Dump to CSV/file and query for rows with "BUG HIT".
--
-- SELECT * FROM rc1
--     LEFT JOIN rc2 ON rc2.rc1_reference = rc1.description
--     LEFT JOIN rc3 ON rc2.id = rc3.rc2_reference
--     LEFT JOIN LATERAL rc_select(rc3.id) ON rc3.id IS NOT NULL;