-- test/sql/surf_prefix.sql
-- Regression test for the v10 SuRF range-filter prefilter.
--
-- The SuRF indexes an order-preserving trigram key so that an anchored /
-- prefix pattern (^foo, LIKE 'foo%') whose leading trigram key is absent
-- from the whole index can be rejected without scanning the posting tier
-- or the heap.  This test confirms:
--
--   1. A build produces a SuRF (tre_surf_stats reports keys/nodes/pages).
--   2. Anchored queries whose prefix EXISTS return the same rows as a seq
--      scan (SuRF must not drop true matches).
--   3. Anchored queries whose prefix does NOT exist return zero rows
--      (matching the seq-scan ground truth) -- exercising the reject path.
--   4. Non-anchored queries are unaffected (no SuRF range applied).
CREATE EXTENSION IF NOT EXISTS pg_tre;
psql:test/sql/surf_prefix.sql:16: NOTICE:  extension "pg_tre" already exists, skipping
CREATE EXTENSION
DROP TABLE IF EXISTS surf_t CASCADE;
psql:test/sql/surf_prefix.sql:18: NOTICE:  table "surf_t" does not exist, skipping
DROP TABLE
CREATE TABLE surf_t (id serial, body text);
CREATE TABLE
INSERT INTO surf_t (body)
SELECT 'error' || i || ' connection refused ' || md5(i::text)
FROM generate_series(1, 3000) i;
INSERT 0 3000
CREATE INDEX surf_idx ON surf_t USING tre (body);
psql:test/sql/surf_prefix.sql:24: NOTICE:  pg_tre: collected 175568 trigram entries from 3000 heap tuples
psql:test/sql/surf_prefix.sql:24: NOTICE:  pg_tre: built 4607 posting trees
psql:test/sql/surf_prefix.sql:24: NOTICE:  pg_tre: build complete, indexed 3000 heap tuples into 4607 trigrams
CREATE INDEX
-- The index has a SuRF filter with a positive key/node/page count.
SELECT (n_keys > 0) AS has_keys,
       (n_nodes > 0) AS has_nodes,
       (n_pages > 0) AS has_pages
FROM tre_surf_stats('surf_idx');
 has_keys | has_nodes | has_pages 
----------+-----------+-----------
 t        | t         | t
(1 row)

SET enable_seqscan = off;
SET
-- (2) anchored prefix that EXISTS: index result == seq-scan ground truth.
SELECT count(*) AS idx_exist FROM surf_t WHERE body %~~ tre_pattern('^error', 0);
 idx_exist 
-----------
      3000
(1 row)

-- (3) anchored prefix that does NOT exist: SuRF rejects; must be 0 and
--     match the seq-scan ground truth.
SELECT count(*) AS idx_absent FROM surf_t WHERE body %~~ tre_pattern('^zzqqxj', 0);
 idx_absent 
------------
          0
(1 row)

-- (4) a non-anchored pattern still works (no SuRF range applied).
SELECT count(*) AS idx_unanchored FROM surf_t WHERE body %~~ tre_pattern('refused', 0);
 idx_unanchored 
----------------
           3000
(1 row)

-- (5) REGRESSION: case-insensitive anchored patterns must NOT be rejected by
--     the SuRF prefilter.  The prefix key is derived from the pattern's
--     literal codepoints with NO case folding, while the index stores
--     trigrams case-sensitively, so '^ERROR' carries a key the index does not
--     contain even though ~* genuinely matches every row.  Extraction marks
--     these always_true (the recheck decides); the prefilter ignored that and
--     returned ZERO rows, silently, with no error.  Reported against 3.2.2;
--     the defect dates to the v10 SuRF tier in 3.2.0.  Each must equal its
--     seq-scan ground truth below.
--
--     Parameterised over pattern CASING, per the 3.2.4 field report: an
--     all-lowercase pattern exercises a different route than one containing
--     an uppercase byte, so testing only '^error' (or only '^ERROR') leaves
--     half the matrix uncovered.  All four spellings must agree.
SELECT count(*) AS idx_iregex_upper FROM surf_t WHERE body ~* '^ERROR';
 idx_iregex_upper 
------------------
             3000
(1 row)

SELECT count(*) AS idx_iregex_lower FROM surf_t WHERE body ~* '^error';
 idx_iregex_lower 
------------------
             3000
(1 row)

SELECT count(*) AS idx_iregex_title FROM surf_t WHERE body ~* '^Error';
 idx_iregex_title 
------------------
             3000
(1 row)

SELECT count(*) AS idx_iregex_mixed FROM surf_t WHERE body ~* '^eRrOr';
 idx_iregex_mixed 
------------------
             3000
(1 row)

SELECT count(*) AS idx_ilike_upper  FROM surf_t WHERE body ILIKE 'ERROR%';
 idx_ilike_upper 
-----------------
            3000
(1 row)

SELECT count(*) AS idx_ilike_lower  FROM surf_t WHERE body ILIKE 'error%';
 idx_ilike_lower 
-----------------
            3000
(1 row)

SELECT count(*) AS idx_ilike_title  FROM surf_t WHERE body ILIKE 'Error%';
 idx_ilike_title 
-----------------
            3000
(1 row)

-- ...and a case-insensitive prefix that genuinely matches nothing stays 0.
SELECT count(*) AS idx_iregex_absent FROM surf_t WHERE body ~* '^ZZQQXJ';
 idx_iregex_absent 
-------------------
                 0
(1 row)

SELECT count(*) AS idx_iregex_absent_up FROM surf_t WHERE body ~* '^ZzQqXj';
 idx_iregex_absent_up 
----------------------
                    0
(1 row)

-- (6) REGRESSION: a literal '-' first or last in a bracket expression is a
--     POSIX literal, not a range operator.  The tokenizer emitted TOK_DASH
--     unconditionally, so the grammar could not reduce `[-_.]` and the whole
--     pattern was rejected with "invalid regex pattern" -- which took out a
--     caller's word-boundary pattern `(^|[-_.])git([-_.0-9]|$)` entirely.
--     These must return rows, not ERROR, and match the seq-scan truth.
SELECT count(*) AS idx_dash_lead  FROM surf_t WHERE body ~ '[-_.]error';
 idx_dash_lead 
---------------
             0
(1 row)

SELECT count(*) AS idx_dash_trail FROM surf_t WHERE body ~ '[error-]';
 idx_dash_trail 
----------------
           3000
(1 row)

SELECT count(*) AS idx_dash_only  FROM surf_t WHERE body ~ '[-]';
 idx_dash_only 
---------------
             0
(1 row)

SELECT count(*) AS idx_dash_neg   FROM surf_t WHERE body ~ '[^-x]error';
 idx_dash_neg 
--------------
            0
(1 row)

SELECT count(*) AS idx_wordbound  FROM surf_t WHERE body ~* '(^|[-_.])error([-_.0-9]|$)';
 idx_wordbound 
---------------
          3000
(1 row)

-- (7) REGRESSION: scan-path axis.  Everything above runs with only
--     enable_seqscan=off, which lets the planner choose a Bitmap Index Scan.
--     A field report showed a plain Index Scan (amgettuple) disagreeing with
--     the Bitmap path on the same index, so the casing cases could all pass
--     while amgettuple stayed broken.  Force each path explicitly and require
--     both to equal the sequential-scan ground truth below.
SET enable_bitmapscan = off;   -- forces plain Index Scan (amgettuple)
SET
SELECT count(*) AS gettuple_iregex_upper FROM surf_t WHERE body ~* '^ERROR';
 gettuple_iregex_upper 
-----------------------
                  3000
(1 row)

SELECT count(*) AS gettuple_iregex_lower FROM surf_t WHERE body ~* '^error';
 gettuple_iregex_lower 
-----------------------
                  3000
(1 row)

SELECT count(*) AS gettuple_iregex_mixed FROM surf_t WHERE body ~* '^eRrOr';
 gettuple_iregex_mixed 
-----------------------
                  3000
(1 row)

SELECT count(*) AS gettuple_ilike_upper  FROM surf_t WHERE body ILIKE 'ERROR%';
 gettuple_ilike_upper 
----------------------
                 3000
(1 row)

SELECT count(*) AS gettuple_absent       FROM surf_t WHERE body ~* '^ZZQQXJ';
 gettuple_absent 
-----------------
               0
(1 row)

SELECT count(*) AS gettuple_sensitive    FROM surf_t WHERE body %~~ tre_pattern('^error', 0);
 gettuple_sensitive 
--------------------
               3000
(1 row)

RESET enable_bitmapscan;
RESET
SET enable_indexscan = off;    -- forces Bitmap Index Scan (amgetbitmap)
SET
SELECT count(*) AS bitmap_iregex_upper FROM surf_t WHERE body ~* '^ERROR';
 bitmap_iregex_upper 
---------------------
                3000
(1 row)

SELECT count(*) AS bitmap_iregex_lower FROM surf_t WHERE body ~* '^error';
 bitmap_iregex_lower 
---------------------
                3000
(1 row)

SELECT count(*) AS bitmap_iregex_mixed FROM surf_t WHERE body ~* '^eRrOr';
 bitmap_iregex_mixed 
---------------------
                3000
(1 row)

SELECT count(*) AS bitmap_ilike_upper  FROM surf_t WHERE body ILIKE 'ERROR%';
 bitmap_ilike_upper 
--------------------
               3000
(1 row)

SELECT count(*) AS bitmap_absent       FROM surf_t WHERE body ~* '^ZZQQXJ';
 bitmap_absent 
---------------
             0
(1 row)

SELECT count(*) AS bitmap_sensitive    FROM surf_t WHERE body %~~ tre_pattern('^error', 0);
 bitmap_sensitive 
------------------
             3000
(1 row)

RESET enable_indexscan;
RESET
RESET enable_seqscan;
RESET
-- Ground-truth via sequential scan.
SET enable_indexscan = off;
SET
SET enable_bitmapscan = off;
SET
SELECT count(*) AS seq_exist FROM surf_t WHERE body %~~ tre_pattern('^error', 0);
 seq_exist 
-----------
      3000
(1 row)

SELECT count(*) AS seq_absent FROM surf_t WHERE body %~~ tre_pattern('^zzqqxj', 0);
 seq_absent 
------------
          0
(1 row)

SELECT count(*) AS seq_unanchored FROM surf_t WHERE body %~~ tre_pattern('refused', 0);
 seq_unanchored 
----------------
           3000
(1 row)

SELECT count(*) AS seq_iregex_upper FROM surf_t WHERE body ~* '^ERROR';
 seq_iregex_upper 
------------------
             3000
(1 row)

SELECT count(*) AS seq_iregex_lower FROM surf_t WHERE body ~* '^error';
 seq_iregex_lower 
------------------
             3000
(1 row)

SELECT count(*) AS seq_iregex_title FROM surf_t WHERE body ~* '^Error';
 seq_iregex_title 
------------------
             3000
(1 row)

SELECT count(*) AS seq_iregex_mixed FROM surf_t WHERE body ~* '^eRrOr';
 seq_iregex_mixed 
------------------
             3000
(1 row)

SELECT count(*) AS seq_ilike_upper  FROM surf_t WHERE body ILIKE 'ERROR%';
 seq_ilike_upper 
-----------------
            3000
(1 row)

SELECT count(*) AS seq_ilike_lower  FROM surf_t WHERE body ILIKE 'error%';
 seq_ilike_lower 
-----------------
            3000
(1 row)

SELECT count(*) AS seq_ilike_title  FROM surf_t WHERE body ILIKE 'Error%';
 seq_ilike_title 
-----------------
            3000
(1 row)

SELECT count(*) AS seq_iregex_absent FROM surf_t WHERE body ~* '^ZZQQXJ';
 seq_iregex_absent 
-------------------
                 0
(1 row)

SELECT count(*) AS seq_iregex_absent_up FROM surf_t WHERE body ~* '^ZzQqXj';
 seq_iregex_absent_up 
----------------------
                    0
(1 row)

SELECT count(*) AS seq_dash_lead  FROM surf_t WHERE body ~ '[-_.]error';
 seq_dash_lead 
---------------
             0
(1 row)

SELECT count(*) AS seq_dash_trail FROM surf_t WHERE body ~ '[error-]';
 seq_dash_trail 
----------------
           3000
(1 row)

SELECT count(*) AS seq_dash_only  FROM surf_t WHERE body ~ '[-]';
 seq_dash_only 
---------------
             0
(1 row)

SELECT count(*) AS seq_dash_neg   FROM surf_t WHERE body ~ '[^-x]error';
 seq_dash_neg 
--------------
            0
(1 row)

SELECT count(*) AS seq_wordbound  FROM surf_t WHERE body ~* '(^|[-_.])error([-_.0-9]|$)';
 seq_wordbound 
---------------
          3000
(1 row)

RESET enable_indexscan;
RESET
RESET enable_bitmapscan;
RESET
DROP TABLE surf_t CASCADE;
DROP TABLE
-- (8) REGRESSION (4.5.0): a prefix that first appears AFTER CREATE INDEX.
--     The SuRF holds the trigram keys of the build; rows inserted later
--     (pending list, then merged by VACUUM) add trigrams it never saw.
--     Until 4.5.0 the prefilter then rejected `~ '^warn'` outright: the
--     index returned 0 rows for rows a sequential scan found, both before
--     and after VACUUM.  Index and seq scan must agree at every stage.
CREATE TABLE surf_late (id serial, body text);
CREATE TABLE
INSERT INTO surf_late (body) SELECT 'error code ' || g FROM generate_series(1, 300) g;
INSERT 0 300
SET client_min_messages = warning;
SET
CREATE INDEX surf_late_idx ON surf_late USING tre (body);
CREATE INDEX
RESET client_min_messages;
RESET
INSERT INTO surf_late (body) SELECT 'warning code ' || g FROM generate_series(1, 200) g;
INSERT 0 200
CREATE FUNCTION surf_late_cmp(tag text) RETURNS TABLE(stage text, q text, seq bigint, bitmap bigint, indexscan bigint)
LANGUAGE plpgsql AS $f$
DECLARE p text;
BEGIN
  FOREACH p IN ARRAY ARRAY['body ~ ''^warn''', 'body %~~ tre_pattern(''^warning'', 0)',
                           'body ~ ''^error''', 'body ~ ''^zzqq''', 'body LIKE ''warn%''']
  LOOP
    stage := tag; q := p;
    PERFORM set_config('enable_seqscan', 'on', true);
    PERFORM set_config('enable_indexscan', 'off', true);
    PERFORM set_config('enable_bitmapscan', 'off', true);
    EXECUTE 'SELECT count(*) FROM surf_late WHERE ' || p INTO seq;
    PERFORM set_config('enable_seqscan', 'off', true);
    PERFORM set_config('enable_bitmapscan', 'on', true);
    EXECUTE 'SELECT count(*) FROM surf_late WHERE ' || p INTO bitmap;
    PERFORM set_config('enable_bitmapscan', 'off', true);
    PERFORM set_config('enable_indexscan', 'on', true);
    EXECUTE 'SELECT count(*) FROM surf_late WHERE ' || p INTO indexscan;
    RETURN NEXT;
  END LOOP;
END $f$;
CREATE FUNCTION
SELECT * FROM surf_late_cmp('pending');
  stage  |                  q                  | seq | bitmap | indexscan 
---------+-------------------------------------+-----+--------+-----------
 pending | body ~ '^warn'                      | 200 |    200 |       200
 pending | body %~~ tre_pattern('^warning', 0) | 200 |    200 |       200
 pending | body ~ '^error'                     | 300 |    300 |       300
 pending | body ~ '^zzqq'                      |   0 |      0 |         0
 pending | body LIKE 'warn%'                   | 200 |    200 |       200
(5 rows)

VACUUM surf_late;
VACUUM
SELECT * FROM surf_late_cmp('merged');
 stage  |                  q                  | seq | bitmap | indexscan 
--------+-------------------------------------+-----+--------+-----------
 merged | body ~ '^warn'                      | 200 |    200 |       200
 merged | body %~~ tre_pattern('^warning', 0) | 200 |    200 |       200
 merged | body ~ '^error'                     | 300 |    300 |       300
 merged | body ~ '^zzqq'                      |   0 |      0 |         0
 merged | body LIKE 'warn%'                   | 200 |    200 |       200
(5 rows)

-- A rebuilt index's SuRF covers the new rows again (and still rejects an
-- absent prefix: ^zzqq).
SET client_min_messages = warning;
SET
REINDEX INDEX surf_late_idx;
REINDEX
RESET client_min_messages;
RESET
SELECT * FROM surf_late_cmp('reindexed');
   stage   |                  q                  | seq | bitmap | indexscan 
-----------+-------------------------------------+-----+--------+-----------
 reindexed | body ~ '^warn'                      | 200 |    200 |       200
 reindexed | body %~~ tre_pattern('^warning', 0) | 200 |    200 |       200
 reindexed | body ~ '^error'                     | 300 |    300 |       300
 reindexed | body ~ '^zzqq'                      |   0 |      0 |         0
 reindexed | body LIKE 'warn%'                   | 200 |    200 |       200
(5 rows)

DROP FUNCTION surf_late_cmp(text);
DROP FUNCTION
DROP TABLE surf_late;
DROP TABLE