-- PostgreSQL 18 SUBSTRING bug reproduction
-- SUBSTRING on TOASTed text with multi-byte UTF-8 characters fails with
-- "invalid byte sequence for encoding UTF8" at specific character positions.
--
-- The same data works fine when stored inline (not TOASTed).
-- LEFT/RIGHT return the correct character at the same position.
--
-- Tested on: PostgreSQL 18.2 on x86_64-pc-linux-gnu
-- Encoding: UTF8, Collation: C.UTF-8

-- Setup: load the test data from the hex file
CREATE TABLE _pg18_repro (id INT PRIMARY KEY DEFAULT 1, hex_content TEXT);
\COPY _pg18_repro (hex_content) FROM '/tmp/pg18_repro_hex.txt'
ALTER TABLE _pg18_repro ADD COLUMN content TEXT;
UPDATE _pg18_repro SET content = convert_from(decode(replace(hex_content, '\x', ''), 'hex'), 'UTF8');
ALTER TABLE _pg18_repro DROP COLUMN hex_content;

-- Verify the data is TOASTed
SELECT length(content) AS char_len,
       octet_length(content) AS byte_len,
       pg_column_size(content) AS stored_size
FROM _pg18_repro WHERE id = 1;
-- Expected: char_len=136485, byte_len=137165, stored_size ~65KB (TOASTed+compressed)

-- Bug: SUBSTRING fails at character position 1212
SELECT SUBSTRING(content FROM 1212 FOR 1) FROM _pg18_repro WHERE id = 1;
-- ERROR: invalid byte sequence for encoding "UTF8": 0xc3

-- But the character at position 1212 is a valid '&' (U+0026):
SELECT LEFT(RIGHT(content, -1211), 1) FROM _pg18_repro WHERE id = 1;
-- Returns: &

-- Adjacent positions work fine with SUBSTRING:
SELECT SUBSTRING(content FROM 1211 FOR 1) FROM _pg18_repro WHERE id = 1;
-- Returns: newline character
SELECT SUBSTRING(content FROM 1213 FOR 1) FROM _pg18_repro WHERE id = 1;
-- Returns: S

-- Workaround: force detoasting with || '' before SUBSTRING
SELECT SUBSTRING((content || '') FROM 1212 FOR 1) FROM _pg18_repro WHERE id = 1;
-- Returns: &

-- Inline (non-TOASTed) copy of the same data works fine:
SELECT SUBSTRING(
    (SELECT SUBSTRING(content FROM 1 FOR 1500) FROM _pg18_repro WHERE id = 1)
    FROM 1212 FOR 1
);
-- Returns: &

-- Cleanup
DROP TABLE _pg18_repro;
