-- ============================================================
-- 1.2 -> 1.3 upgrade path.
-- ============================================================
-- It must run last (see Makefile), because it drops and reinstalls the extension.
--
-- The assertions come in four parts: roaringbitmap (32-bit) before the upgrade,
-- roaringbitmap64 (64-bit) before the upgrade, and the same two after it.
--
SET client_min_messages = warning;
DROP EXTENSION IF EXISTS roaringbitmap CASCADE;
CREATE EXTENSION roaringbitmap VERSION '1.2';
-- ============================================================================
-- Part 1 of 4: roaringbitmap (32-bit) -- before the upgrade
-- ============================================================================
-- Before the upgrade there is only the = operator, with neither flag set, and no
-- operator class for the type.
SELECT oprname, oprleft::regtype, oprright::regtype, oprcanhash, oprcanmerge, oprrest::regproc, oprjoin::regproc
  FROM pg_operator
 WHERE oprleft = 'roaringbitmap'::regtype
   OR oprright = 'roaringbitmap'::regtype
 ORDER BY oprname,oprleft,oprright;
 oprname |    oprleft    |   oprright    | oprcanhash | oprcanmerge | oprrest |   oprjoin   
---------+---------------+---------------+------------+-------------+---------+-------------
 #       | roaringbitmap | roaringbitmap | f          | f           | -       | -
 &       | roaringbitmap | roaringbitmap | f          | f           | -       | -
 &&      | roaringbitmap | roaringbitmap | f          | f           | contsel | contjoinsel
 -       | roaringbitmap | integer       | f          | f           | -       | -
 -       | roaringbitmap | roaringbitmap | f          | f           | -       | -
 <<      | roaringbitmap | bigint        | f          | f           | -       | -
 <>      | roaringbitmap | roaringbitmap | f          | f           | neqsel  | neqjoinsel
 <@      | integer       | roaringbitmap | f          | f           | contsel | contjoinsel
 <@      | roaringbitmap | roaringbitmap | f          | f           | contsel | contjoinsel
 =       | roaringbitmap | roaringbitmap | f          | f           | eqsel   | eqjoinsel
 >>      | roaringbitmap | bigint        | f          | f           | -       | -
 @>      | roaringbitmap | integer       | f          | f           | contsel | contjoinsel
 @>      | roaringbitmap | roaringbitmap | f          | f           | contsel | contjoinsel
 |       | integer       | roaringbitmap | f          | f           | -       | -
 |       | roaringbitmap | integer       | f          | f           | -       | -
 |       | roaringbitmap | roaringbitmap | f          | f           | -       | -
(16 rows)

SELECT count(*) AS opclasses_before
  FROM pg_opclass
 WHERE opcintype = 'roaringbitmap'::regtype;
 opclasses_before 
------------------
                0
(1 row)

-- 1.2 collected statistics through the backend's compute_trivial_stats(); 1.3
-- replaces that with rb_typanalyze.
SELECT typname, typanalyze::regproc
  FROM pg_type
 WHERE typname = 'roaringbitmap';
    typname    | typanalyze 
---------------+------------
 roaringbitmap | -
(1 row)

SELECT prosrc AS containedby_before
  FROM pg_proc
 WHERE oid = 'rb_containedby(integer, roaringbitmap)'::regprocedure;
     containedby_before      
-----------------------------
 SELECT rb_contains($2, $1);
(1 row)

-- create test table before upgrade
CREATE TABLE rb_upgrade_t (rb roaringbitmap);
INSERT INTO rb_upgrade_t VALUES (rb_build(ARRAY[1, 2, 3, 4, 5]));
INSERT INTO rb_upgrade_t 
  SELECT rb_build_agg(a*111+b*3+7) bitmap
    FROM generate_series(1,100)a,generate_series(1,1000)b;
-- ============================================================================
-- Part 2 of 4: roaringbitmap64 (64-bit) -- before the upgrade
-- ============================================================================
-- Same as part 1 for roaringbitmap64: only the = operator, with neither flag
-- set, no operator class, and no typanalyze function.
SELECT oprname, oprleft::regtype, oprright::regtype, oprcanhash, oprcanmerge, oprrest::regproc, oprjoin::regproc
  FROM pg_operator
 WHERE oprleft = 'roaringbitmap64'::regtype
   OR oprright = 'roaringbitmap64'::regtype
 ORDER BY oprname,oprleft,oprright;
 oprname |     oprleft     |    oprright     | oprcanhash | oprcanmerge | oprrest |   oprjoin   
---------+-----------------+-----------------+------------+-------------+---------+-------------
 #       | roaringbitmap64 | roaringbitmap64 | f          | f           | -       | -
 &       | roaringbitmap64 | roaringbitmap64 | f          | f           | -       | -
 &&      | roaringbitmap64 | roaringbitmap64 | f          | f           | contsel | contjoinsel
 -       | roaringbitmap64 | bigint          | f          | f           | -       | -
 -       | roaringbitmap64 | roaringbitmap64 | f          | f           | -       | -
 <<      | roaringbitmap64 | bigint          | f          | f           | -       | -
 <>      | roaringbitmap64 | roaringbitmap64 | f          | f           | neqsel  | neqjoinsel
 <@      | bigint          | roaringbitmap64 | f          | f           | contsel | contjoinsel
 <@      | roaringbitmap64 | roaringbitmap64 | f          | f           | contsel | contjoinsel
 =       | roaringbitmap64 | roaringbitmap64 | f          | f           | eqsel   | eqjoinsel
 >>      | roaringbitmap64 | bigint          | f          | f           | -       | -
 @>      | roaringbitmap64 | bigint          | f          | f           | contsel | contjoinsel
 @>      | roaringbitmap64 | roaringbitmap64 | f          | f           | contsel | contjoinsel
 |       | bigint          | roaringbitmap64 | f          | f           | -       | -
 |       | roaringbitmap64 | bigint          | f          | f           | -       | -
 |       | roaringbitmap64 | roaringbitmap64 | f          | f           | -       | -
(16 rows)

SELECT count(*) AS opclasses64_before
  FROM pg_opclass
 WHERE opcintype = 'roaringbitmap64'::regtype;
 opclasses64_before 
--------------------
                  0
(1 row)

SELECT typname, typanalyze::regproc
  FROM pg_type
 WHERE typname = 'roaringbitmap64';
     typname     | typanalyze 
-----------------+------------
 roaringbitmap64 | -
(1 row)

SELECT prosrc AS containedby64_before
  FROM pg_proc
 WHERE oid = 'rb64_containedby(bigint, roaringbitmap64)'::regprocedure;
     containedby64_before      
-------------------------------
 SELECT rb64_contains($2, $1);
(1 row)

CREATE TABLE rb64_upgrade_t (rb roaringbitmap64);
INSERT INTO rb64_upgrade_t VALUES (rb64_build(ARRAY[1, 2, 3, 4, 5]));
INSERT INTO rb64_upgrade_t 
  SELECT rb64_build_agg(a*111+b*3+7) bitmap
    FROM generate_series(1,100)a,generate_series(1,1000)b;
ALTER EXTENSION roaringbitmap UPDATE TO '1.3';
-- ============================================================================
-- Part 3 of 4: roaringbitmap (32-bit) -- after the upgrade
-- ============================================================================
-- After the upgrade: = must be hashable and mergejoinable, < <= >= > must NOT be
-- mergejoinable.  ALTER OPERATOR can only turn HASHES/MERGES on from PostgreSQL
-- 17 on, before that the upgraded = keeps its 1.2 flags, so accept either value
-- there and keep a single expected file for PostgreSQL 13 - 19.
SELECT oprname, oprleft::regtype, oprright::regtype,
       CASE WHEN oprname = '='
                 AND current_setting('server_version_num')::int < 170000
            THEN true ELSE oprcanhash END AS oprcanhash,
       CASE WHEN oprname = '='
                 AND current_setting('server_version_num')::int < 170000
            THEN true ELSE oprcanmerge END AS oprcanmerge,
       oprrest::regproc, oprjoin::regproc
  FROM pg_operator
 WHERE oprleft = 'roaringbitmap'::regtype
   OR oprright = 'roaringbitmap'::regtype
 ORDER BY oprname,oprleft,oprright;
 oprname |    oprleft    |   oprright    | oprcanhash | oprcanmerge |     oprrest      |     oprjoin     
---------+---------------+---------------+------------+-------------+------------------+-----------------
 #       | roaringbitmap | roaringbitmap | f          | f           | -                | -
 &       | roaringbitmap | roaringbitmap | f          | f           | -                | -
 &&      | roaringbitmap | roaringbitmap | f          | f           | rb_overlap_sel   | contjoinsel
 -       | roaringbitmap | integer       | f          | f           | -                | -
 -       | roaringbitmap | roaringbitmap | f          | f           | -                | -
 <       | roaringbitmap | roaringbitmap | f          | f           | scalarltsel      | scalarltjoinsel
 <<      | roaringbitmap | bigint        | f          | f           | -                | -
 <=      | roaringbitmap | roaringbitmap | f          | f           | scalarlesel      | scalarlejoinsel
 <>      | roaringbitmap | roaringbitmap | f          | f           | neqsel           | neqjoinsel
 <@      | integer       | roaringbitmap | f          | f           | rb_contained_sel | contjoinsel
 <@      | roaringbitmap | roaringbitmap | f          | f           | rb_contained_sel | contjoinsel
 =       | roaringbitmap | roaringbitmap | t          | t           | eqsel            | eqjoinsel
 >       | roaringbitmap | roaringbitmap | f          | f           | scalargtsel      | scalargtjoinsel
 >=      | roaringbitmap | roaringbitmap | f          | f           | scalargesel      | scalargejoinsel
 >>      | roaringbitmap | bigint        | f          | f           | -                | -
 @>      | roaringbitmap | integer       | f          | f           | rb_contain_sel   | contjoinsel
 @>      | roaringbitmap | roaringbitmap | f          | f           | rb_contain_sel   | contjoinsel
 |       | integer       | roaringbitmap | f          | f           | -                | -
 |       | roaringbitmap | integer       | f          | f           | -                | -
 |       | roaringbitmap | roaringbitmap | f          | f           | -                | -
(20 rows)

-- The rb_containedby wrapper must use the @> form, otherwise "42 <@ rb" is
-- inlined into a FUNCEXPR and cannot use the GIN index.
SELECT prosrc AS containedby_after
  FROM pg_proc
 WHERE oid = 'rb_containedby(integer, roaringbitmap)'::regprocedure;
 containedby_after 
-------------------
 SELECT $2 @> $1;
(1 row)

-- The operator functions behind &&, @>, <@ and = 
SELECT p.proname, oidvectortypes(p.proargtypes) AS args, p.procost
  FROM pg_proc p
 WHERE p.oid IN ('rb_contains(roaringbitmap, roaringbitmap)'::regprocedure,
                 'rb_contains(roaringbitmap, integer)'::regprocedure,
                 'rb_containedby(roaringbitmap, roaringbitmap)'::regprocedure,
                 'rb_containedby(integer, roaringbitmap)'::regprocedure,
                 'rb_intersect(roaringbitmap, roaringbitmap)'::regprocedure,
                 'rb_equals(roaringbitmap, roaringbitmap)'::regprocedure)
 ORDER BY p.proname, args;
    proname     |             args             | procost 
----------------+------------------------------+---------
 rb_containedby | integer, roaringbitmap       |     100
 rb_containedby | roaringbitmap, roaringbitmap |     100
 rb_contains    | roaringbitmap, integer       |     100
 rb_contains    | roaringbitmap, roaringbitmap |     100
 rb_equals      | roaringbitmap, roaringbitmap |     100
 rb_intersect   | roaringbitmap, roaringbitmap |     100
(6 rows)

-- All three operator classes, and their members, must have been created.
SELECT opcname, am.amname, opcdefault
  FROM pg_opclass c
  JOIN pg_am am ON am.oid = c.opcmethod
 WHERE c.opcintype = 'roaringbitmap'::regtype
 ORDER BY am.amname;
      opcname      | amname | opcdefault 
-------------------+--------+------------
 roaringbitmap_ops | btree  | t
 roaringbitmap_ops | gin    | t
 roaringbitmap_ops | hash   | t
(3 rows)

SELECT am.amname, amopstrategy, amopopr::regoperator
  FROM pg_amop ao
  JOIN pg_am am ON am.oid = ao.amopmethod
 WHERE ao.amoplefttype = 'roaringbitmap'::regtype
 ORDER BY am.amname, amopstrategy;
 amname | amopstrategy |             amopopr             
--------+--------------+---------------------------------
 btree  |            1 | <(roaringbitmap,roaringbitmap)
 btree  |            2 | <=(roaringbitmap,roaringbitmap)
 btree  |            3 | =(roaringbitmap,roaringbitmap)
 btree  |            4 | >=(roaringbitmap,roaringbitmap)
 btree  |            5 | >(roaringbitmap,roaringbitmap)
 gin    |            1 | &&(roaringbitmap,roaringbitmap)
 gin    |            2 | @>(roaringbitmap,roaringbitmap)
 gin    |            3 | <@(roaringbitmap,roaringbitmap)
 gin    |            4 | =(roaringbitmap,roaringbitmap)
 gin    |            5 | @>(roaringbitmap,integer)
 hash   |            1 | =(roaringbitmap,roaringbitmap)
(11 rows)

-- Functional sanity check on the upgraded objects.
SELECT count(*) AS lt_matches FROM rb_upgrade_t WHERE rb < rb_build(ARRAY[9]);
 lt_matches 
------------
          1
(1 row)

SELECT count(*) AS eq_matches FROM rb_upgrade_t WHERE rb = rb_build(ARRAY[1, 2, 3, 4, 5]);
 eq_matches 
------------
          1
(1 row)

SELECT count(*) AS containedby_int
  FROM rb_upgrade_t WHERE 3 <@ rb;
 containedby_int 
-----------------
               1
(1 row)

SELECT count(DISTINCT rb) AS distinct_rb FROM rb_upgrade_t;
 distinct_rb 
-------------
           2
(1 row)

-- The upgraded type must have picked up rb_typanalyze, so analysing a
-- roaringbitmap column writes a statistics row with no MCV and no histogram.
SELECT typname, typanalyze::regproc
  FROM pg_type
 WHERE typname = 'roaringbitmap';
    typname    |  typanalyze   
---------------+---------------
 roaringbitmap | rb_typanalyze
(1 row)

INSERT INTO rb_upgrade_t
  SELECT rb_build(ARRAY[1, 2, 3, 4, 5]) FROM generate_series(1, 30);
ANALYZE rb_upgrade_t;
select attname, null_frac, n_distinct, avg_width,
       most_common_vals is null as no_mcv,
       histogram_bounds is null as no_histogram
  from pg_stats
 where tablename = 'rb_upgrade_t'
 order by attname;
 attname | null_frac | n_distinct | avg_width | no_mcv | no_histogram 
---------+-----------+------------+-----------+--------+--------------
 rb      |         0 |          0 |        26 | t      | t
(1 row)

-- ============================================================================
-- Part 4 of 4: roaringbitmap64 (64-bit) -- after the upgrade
-- ============================================================================
-- Same as part 3 for roaringbitmap64: = is hashable and mergejoinable, < <= >= >
-- are not mergejoinable, and the three operator classes exist with the same
-- members as a fresh 1.3 install.
SELECT oprname, oprleft::regtype, oprright::regtype,
       CASE WHEN oprname = '='
                 AND current_setting('server_version_num')::int < 170000
            THEN true ELSE oprcanhash END AS oprcanhash,
       CASE WHEN oprname = '='
                 AND current_setting('server_version_num')::int < 170000
            THEN true ELSE oprcanmerge END AS oprcanmerge,
       oprrest::regproc, oprjoin::regproc
  FROM pg_operator
 WHERE oprleft = 'roaringbitmap64'::regtype
   OR oprright = 'roaringbitmap64'::regtype
 ORDER BY oprname,oprleft,oprright;
 oprname |     oprleft     |    oprright     | oprcanhash | oprcanmerge |      oprrest       |     oprjoin     
---------+-----------------+-----------------+------------+-------------+--------------------+-----------------
 #       | roaringbitmap64 | roaringbitmap64 | f          | f           | -                  | -
 &       | roaringbitmap64 | roaringbitmap64 | f          | f           | -                  | -
 &&      | roaringbitmap64 | roaringbitmap64 | f          | f           | rb64_overlap_sel   | contjoinsel
 -       | roaringbitmap64 | bigint          | f          | f           | -                  | -
 -       | roaringbitmap64 | roaringbitmap64 | f          | f           | -                  | -
 <       | roaringbitmap64 | roaringbitmap64 | f          | f           | scalarltsel        | scalarltjoinsel
 <<      | roaringbitmap64 | bigint          | f          | f           | -                  | -
 <=      | roaringbitmap64 | roaringbitmap64 | f          | f           | scalarlesel        | scalarlejoinsel
 <>      | roaringbitmap64 | roaringbitmap64 | f          | f           | neqsel             | neqjoinsel
 <@      | bigint          | roaringbitmap64 | f          | f           | rb64_contained_sel | contjoinsel
 <@      | roaringbitmap64 | roaringbitmap64 | f          | f           | rb64_contained_sel | contjoinsel
 =       | roaringbitmap64 | roaringbitmap64 | t          | t           | eqsel              | eqjoinsel
 >       | roaringbitmap64 | roaringbitmap64 | f          | f           | scalargtsel        | scalargtjoinsel
 >=      | roaringbitmap64 | roaringbitmap64 | f          | f           | scalargesel        | scalargejoinsel
 >>      | roaringbitmap64 | bigint          | f          | f           | -                  | -
 @>      | roaringbitmap64 | bigint          | f          | f           | rb64_contain_sel   | contjoinsel
 @>      | roaringbitmap64 | roaringbitmap64 | f          | f           | rb64_contain_sel   | contjoinsel
 |       | bigint          | roaringbitmap64 | f          | f           | -                  | -
 |       | roaringbitmap64 | bigint          | f          | f           | -                  | -
 |       | roaringbitmap64 | roaringbitmap64 | f          | f           | -                  | -
(20 rows)

-- The rb64_containedby wrapper must use the @> form, otherwise "42 <@ rb64" is
-- inlined into a FUNCEXPR and cannot use the GIN index.
SELECT prosrc AS containedby64_after
  FROM pg_proc
 WHERE oid = 'rb64_containedby(bigint, roaringbitmap64)'::regprocedure;
 containedby64_after 
---------------------
 SELECT $2 @> $1;
(1 row)

-- The operator functions behind &&, @>, <@ and =
SELECT p.proname, oidvectortypes(p.proargtypes) AS args, p.procost
  FROM pg_proc p
 WHERE p.oid IN ('rb64_contains(roaringbitmap64, roaringbitmap64)'::regprocedure,
                 'rb64_contains(roaringbitmap64, bigint)'::regprocedure,
                 'rb64_containedby(roaringbitmap64, roaringbitmap64)'::regprocedure,
                 'rb64_containedby(bigint, roaringbitmap64)'::regprocedure,
                 'rb64_intersect(roaringbitmap64, roaringbitmap64)'::regprocedure,
                 'rb64_equals(roaringbitmap64, roaringbitmap64)'::regprocedure)
 ORDER BY p.proname, args;
     proname      |               args               | procost 
------------------+----------------------------------+---------
 rb64_containedby | bigint, roaringbitmap64          |     100
 rb64_containedby | roaringbitmap64, roaringbitmap64 |     100
 rb64_contains    | roaringbitmap64, bigint          |     100
 rb64_contains    | roaringbitmap64, roaringbitmap64 |     100
 rb64_equals      | roaringbitmap64, roaringbitmap64 |     100
 rb64_intersect   | roaringbitmap64, roaringbitmap64 |     100
(6 rows)

-- All three operator classes, and their members, must have been created.
SELECT opcname, am.amname, opcdefault
  FROM pg_opclass c
  JOIN pg_am am ON am.oid = c.opcmethod
 WHERE c.opcintype = 'roaringbitmap64'::regtype
 ORDER BY am.amname;
       opcname       | amname | opcdefault 
---------------------+--------+------------
 roaringbitmap64_ops | btree  | t
 roaringbitmap64_ops | gin    | t
 roaringbitmap64_ops | hash   | t
(3 rows)

SELECT am.amname, amopstrategy, amopopr::regoperator
  FROM pg_amop ao
  JOIN pg_am am ON am.oid = ao.amopmethod
 WHERE ao.amoplefttype = 'roaringbitmap64'::regtype
 ORDER BY am.amname, amopstrategy;
 amname | amopstrategy |               amopopr               
--------+--------------+-------------------------------------
 btree  |            1 | <(roaringbitmap64,roaringbitmap64)
 btree  |            2 | <=(roaringbitmap64,roaringbitmap64)
 btree  |            3 | =(roaringbitmap64,roaringbitmap64)
 btree  |            4 | >=(roaringbitmap64,roaringbitmap64)
 btree  |            5 | >(roaringbitmap64,roaringbitmap64)
 gin    |            1 | &&(roaringbitmap64,roaringbitmap64)
 gin    |            2 | @>(roaringbitmap64,roaringbitmap64)
 gin    |            3 | <@(roaringbitmap64,roaringbitmap64)
 gin    |            4 | =(roaringbitmap64,roaringbitmap64)
 gin    |            5 | @>(roaringbitmap64,bigint)
 hash   |            1 | =(roaringbitmap64,roaringbitmap64)
(11 rows)

-- Functional sanity check on the upgraded objects.
SELECT count(*) AS lt_matches FROM rb64_upgrade_t WHERE rb < rb64_build(ARRAY[9]);
 lt_matches 
------------
          1
(1 row)

SELECT count(*) AS eq_matches FROM rb64_upgrade_t WHERE rb = rb64_build(ARRAY[1, 2, 3, 4, 5]);
 eq_matches 
------------
          1
(1 row)

SELECT count(*) AS containedby_int
  FROM rb64_upgrade_t WHERE 3 <@ rb;
 containedby_int 
-----------------
               1
(1 row)

SELECT count(DISTINCT rb) AS distinct_rb FROM rb64_upgrade_t;
 distinct_rb 
-------------
           2
(1 row)

-- The upgraded type must have picked up rb64_typanalyze, so analysing a
-- roaringbitmap64 column writes a statistics row with no MCV and no histogram.
SELECT typname, typanalyze::regproc
  FROM pg_type
 WHERE typname = 'roaringbitmap64';
     typname     |   typanalyze    
-----------------+-----------------
 roaringbitmap64 | rb64_typanalyze
(1 row)

INSERT INTO rb64_upgrade_t
  SELECT rb64_build(ARRAY[1, 2, 3, 4, 5]) FROM generate_series(1, 30);
ANALYZE rb64_upgrade_t;
select attname, null_frac, n_distinct, avg_width,
       most_common_vals is null as no_mcv,
       histogram_bounds is null as no_histogram
  from pg_stats
 where tablename = 'rb64_upgrade_t'
 order by attname;
 attname | null_frac | n_distinct | avg_width | no_mcv | no_histogram 
---------+-----------+------------+-----------+--------+--------------
 rb      |         0 |          0 |        38 | t      | t
(1 row)