DO $$
DECLARE
    v_version numeric;
BEGIN

    SELECT substring(setting from '\d+')::numeric INTO v_version FROM pg_settings WHERE name = 'server_version';

    -- GUCs common for all versions
    PERFORM set_config('extra_float_digits', '0', false);
    PERFORM set_config('parallel_setup_cost', '0', false);
    PERFORM set_config('parallel_tuple_cost', '0', false);
    PERFORM set_config('max_parallel_workers_per_gather', '2', false);

    -- 9.6 used somewhat different GUC name for relation size
    IF v_version < 10 THEN
        PERFORM set_config('min_parallel_relation_size', '1kB', false);
    ELSE
        PERFORM set_config('min_parallel_table_scan_size', '1kB', false);
    END IF;

    -- in 14 disable Memoize nodes, to make explain more consistent
    IF v_version >= 14 THEN
        PERFORM set_config('enable_memoize', 'off', false);
    END IF;

END;
$$ LANGUAGE plpgsql;

-- check trimmed mean (from raw data)
-- we compare the result to a range, to deal with the randomness
WITH data AS (SELECT random() AS r FROM generate_series(1,10000) AS x)
SELECT
    tdigest_avg(data.r, 50, 0.1, 0.9) between 0.45 and 0.55 AS mean_10_90,
    tdigest_avg(data.r, 50, 0.25, 0.75) between 0.45 and 0.55 AS mean_25_75,
    tdigest_avg(data.r, 50, 0.0, 0.5) between 0.2 and 0.3 AS mean_0_50,
    tdigest_avg(data.r, 50, 0.5, 1.0) between 0.7 and 0.8 AS mean_50_100
FROM data;

WITH data AS (SELECT random() AS r, (1 + (3 * random())::int) AS c FROM generate_series(1,10000) AS x)
SELECT
    tdigest_avg(data.r, data.c, 100, 0.1, 0.9) between 0.45 and 0.55 AS mean_10_90,
    tdigest_avg(data.r, data.c, 100, 0.25, 0.75) between 0.45 and 0.55 AS mean_25_75,
    tdigest_avg(data.r, data.c, 100, 0.0, 0.5) between 0.2 and 0.3 AS mean_0_50,
    tdigest_avg(data.r, data.c, 100, 0.5, 1.0) between 0.7 and 0.8 AS mean_50_100
FROM data;

-- check trimmed mean (from pracalculated tdigest)
-- we compare the result to a range, to deal with the randomness
WITH data AS (SELECT tdigest(random(), 50) AS d FROM generate_series(1,10000) AS x)
SELECT
    tdigest_avg(data.d, 0.1, 0.9) between 0.45 and 0.55 AS mean_10_90,
    tdigest_avg(data.d, 0.25, 0.75) between 0.45 and 0.55 AS mean_25_75,
    tdigest_avg(data.d, 0.0, 0.5) between 0.2 and 0.3 AS mean_0_50,
    tdigest_avg(data.d, 0.5, 1.0) between 0.7 and 0.8 AS mean_50_100
FROM data;

-- check trimmed sum (from raw data)
-- we compare the result to a range, to deal with the randomness
WITH data AS (SELECT random() AS r FROM generate_series(1,10000) AS x)
SELECT
    tdigest_sum(data.r, 50, 0.1, 0.9) between 8000 * 0.45 and 8000 * 0.55 AS sum_10_90,
    tdigest_sum(data.r, 50, 0.25, 0.75) between 5000 * 0.45 and 5000 * 0.55 AS sum_25_75,
    tdigest_sum(data.r, 50, 0.0, 0.5) between 5000 * 0.2 and 5000 * 0.3 AS sum_0_50,
    tdigest_sum(data.r, 50, 0.5, 1.0) between 5000 * 0.7 and 5000 * 0.8 AS sum_50_100
FROM data;

WITH data AS (SELECT random() AS r, (1 + (3 * random())::int) AS c FROM generate_series(1,10000) AS x)
SELECT
    tdigest_sum(data.r, data.c, 100, 0.1, 0.9) between 20000 * 0.45 and 20000 * 0.55 AS sum_10_90,
    tdigest_sum(data.r, data.c, 100, 0.25, 0.75) between 12500 * 0.45 and 12500 * 0.55 AS sum_25_75,
    tdigest_sum(data.r, data.c, 100, 0.0, 0.5) between 12500 * 0.2 and 12500 * 0.3 AS sum_0_50,
    tdigest_sum(data.r, data.c, 100, 0.5, 1.0) between 12500 * 0.7 and 12500 * 0.8 AS sum_50_100
FROM data;

-- check trimmed sum (from pracalculated tdigest)
-- we compare the result to a range, to deal with the randomness
WITH data AS (SELECT tdigest(random(), 50) AS d FROM generate_series(1,10000) AS x)
SELECT
    tdigest_sum(data.d, 0.1, 0.9) between 8000 * 0.45 and 8000 * 0.55 AS sum_10_90,
    tdigest_sum(data.d, 0.25, 0.75) between 5000 * 0.45 and 5000 * 0.55 AS sum_25_75,
    tdigest_sum(data.d, 0.0, 0.5) between 5000 * 0.2 and 5000 * 0.3 AS sum_0_50,
    tdigest_sum(data.d, 0.5, 1.0) between 5000 * 0.7 and 5000 * 0.8 AS sum_50_100
FROM data;

WITH data AS (SELECT tdigest(random(), 50) AS d FROM generate_series(1,10000) AS x)
SELECT
    tdigest_digest_sum(data.d, 0.05, 0.95) between 9000 * 0.45 and 9000 * 0.55 AS sum_05_95,
    tdigest_digest_avg(data.d, 0.05, 0.95) between 0.45 and 0.55 AS mean_05_95
FROM data;

-- results should not depend on ordering of input data (with compression
-- large enough to not compact anything)

SELECT tdigest_sum(i, 10000, 0.0, 0.5) FROM generate_series(1, 10000) s(i);
SELECT tdigest_sum(i, 10000, 0.0, 0.5) from generate_series(10000, 1, -1) s(i);

SELECT tdigest_sum(i, 10000, 0.0, 0.25) FROM generate_series(1, 10000) s(i);
SELECT tdigest_sum(i, 10000, 0.0, 0.25) from generate_series(10000, 1, -1) s(i);

SELECT tdigest_sum(i, 10000, 0.1, 0.2) FROM generate_series(1, 10000) s(i);
SELECT tdigest_sum(i, 10000, 0.1, 0.2) from generate_series(10000, 1, -1) s(i);

SELECT tdigest_sum(i, 10000, 0.5, 1.0) FROM generate_series(1, 10000) s(i);
SELECT tdigest_sum(i, 10000, 0.5, 1.0) from generate_series(10000, 1, -1) s(i);

SELECT tdigest_sum(i, 10000, 0.75, 1.0) FROM generate_series(1, 10000) s(i);
SELECT tdigest_sum(i, 10000, 0.75, 1.0) from generate_series(10000, 1, -1) s(i);

SELECT tdigest_sum(i, 10000, 0.75, 0.9) FROM generate_series(1, 10000) s(i);
SELECT tdigest_sum(i, 10000, 0.75, 0.9) from generate_series(10000, 1, -1) s(i);

-- the same thing with calculating a t-digest first (but make sure all
-- centroids have count 1, to make it exactly the same)
WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1, 1500) s(i))
SELECT tdigest_sum(d, 0.0, 0.5) from tmp;
WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1500, 1, -1) s(i))
SELECT tdigest_sum(d, 0.0, 0.5) from tmp;

WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1, 1500) s(i))
SELECT tdigest_sum(d, 0.0, 0.25) from tmp;
WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1500, 1, -1) s(i))
SELECT tdigest_sum(d, 0.0, 0.25) from tmp;

WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1, 1500) s(i))
SELECT tdigest_sum(d, 0.1, 0.2) from tmp;
WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1500, 1, -1) s(i))
SELECT tdigest_sum(d, 0.1, 0.2) from tmp;

WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1, 1500) s(i))
SELECT tdigest_sum(d, 0.5, 1.0) from tmp;
WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1500, 1, -1) s(i))
SELECT tdigest_sum(d, 0.5, 1.0) from tmp;

WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1, 1500) s(i))
SELECT tdigest_sum(d, 0.75, 1.0) from tmp;
WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1500, 1, -1) s(i))
SELECT tdigest_sum(d, 0.75, 1.0) from tmp;

WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1, 1500) s(i))
SELECT tdigest_sum(d, 0.75, 0.9) from tmp;
WITH tmp AS (SELECT tdigest(i, 10000) AS d FROM generate_series(1500, 1, -1) s(i))
SELECT tdigest_sum(d, 0.75, 0.9) from tmp;

-- simple sanity checks of high/low thresholds

-- correct low/high ranges
SELECT tdigest_sum(1.0, 100, 0.0, 1.0);
SELECT tdigest_avg(1.0, 100, 0.0, 1.0);

SELECT tdigest_sum(1.0, 100, 0.25, 0.75);
SELECT tdigest_avg(1.0, 100, 0.25, 0.75);

SELECT tdigest_sum(1.0, 100, 0.5, 0.5);
SELECT tdigest_avg(1.0, 100, 0.5, 0.5);

-- inverted range
SELECT tdigest_sum(1.0, 100, 0.75, 0.25);
SELECT tdigest_avg(1.0, 100, 0.75, 0.25);

-- negative low threshold
SELECT tdigest_sum(1.0, 100, -1.0, 1.0);
SELECT tdigest_avg(1.0, 100, -1.0, 1.0);

-- bogus high threshold
SELECT tdigest_sum(1.0, 100, -1.0, 1.0);
SELECT tdigest_avg(1.0, 100, -1.0, 1.0);

-- infinity in low threshold
SELECT tdigest_sum(1.0, 100, '-infinity'::double precision, 1.0);
SELECT tdigest_avg(1.0, 100, '-infinity'::double precision, 1.0);

SELECT tdigest_sum(1.0, 100, 'infinity'::double precision, 1.0);
SELECT tdigest_avg(1.0, 100, 'infinity'::double precision, 1.0);

-- infinity in high threshold
SELECT tdigest_sum(1.0, 100, 0.0, '-infinity'::double precision);
SELECT tdigest_avg(1.0, 100, 0.0, '-infinity'::double precision);

SELECT tdigest_sum(1.0, 100, 0.0, 'infinity'::double precision);
SELECT tdigest_avg(1.0, 100, 0.0, 'infinity'::double precision);

-- infinity in both thresholds
SELECT tdigest_sum(1.0, 100, '-infinity'::double precision, '-infinity'::double precision);
SELECT tdigest_avg(1.0, 100, '-infinity'::double precision, '-infinity'::double precision);

SELECT tdigest_sum(1.0, 100, '-infinity'::double precision, 'infinity'::double precision);
SELECT tdigest_avg(1.0, 100, '-infinity'::double precision, 'infinity'::double precision);

SELECT tdigest_sum(1.0, 100, 'infinity'::double precision, '-infinity'::double precision);
SELECT tdigest_avg(1.0, 100, 'infinity'::double precision, '-infinity'::double precision);

SELECT tdigest_sum(1.0, 100, 'infinity'::double precision, 'infinity'::double precision);
SELECT tdigest_avg(1.0, 100, 'infinity'::double precision, 'infinity'::double precision);

-- NaN in thresholds
SELECT tdigest_sum(1.0, 100, 'NaN'::double precision, 1.0);
SELECT tdigest_avg(1.0, 100, 'NaN'::double precision, 1.0);

SELECT tdigest_sum(1.0, 100, 0.0, 'NaN'::double precision);
SELECT tdigest_avg(1.0, 100, 0.0, 'NaN'::double precision);

SELECT tdigest_sum(1.0, 100, 'NaN'::double precision, 'NaN'::double precision);
SELECT tdigest_avg(1.0, 100, 'NaN'::double precision, 'NaN'::double precision);

-- check tdigest_trimmed_agg() calculates and aggregates the right range
-- of centroids to process, especially when [count_low, count_high) falls
-- into a single centroid
--
-- A digest holding a single centroid, i.e. 100 items that all have the value
-- 10. The trimmed sum then has to be exactly (10 * number of items in the
-- range) for every range, which makes the expected values easy to derive
--
-- count_low = floor(100 * lo) and count_high = ceil(100 * hi).
SELECT lo, hi,
       tdigest_digest_sum(d, lo, hi) AS trimmed_sum,
       10 * (ceil(100::double precision * hi) - floor(100::double precision * lo)) AS expected_sum,
       tdigest_digest_avg(d, lo, hi) AS trimmed_avg
  FROM (SELECT 'flags 1 count 100 compression 10 centroids 1 (10, 100)'::tdigest) t(d),
       (VALUES (0.0::double precision,  1.0::double precision),
               (0.0::double precision,  0.5::double precision),
               (0.5::double precision,  1.0::double precision),
               (0.4::double precision,  0.5::double precision),
               (0.1::double precision,  0.2::double precision),
               (0.25::double precision, 0.75::double precision)) v(lo, hi)
 ORDER BY lo, hi;

-- The same through the aggregates, to cover tdigest_trimmed_sum() and
-- tdigest_trimmed_avg() too. All the input values are the same, so the digest
-- is a single centroid no matter how the compaction merges the centroids.
SELECT tdigest(10.0::double precision, 10) AS digest
  FROM generate_series(1, 100);

SELECT tdigest_sum(10.0::double precision, 10, 0.4, 0.5) AS agg_sum,
       tdigest_avg(10.0::double precision, 10, 0.4, 0.5) AS agg_avg
  FROM generate_series(1, 100);

-- Now the same thing, but with more than one centroid, covering a range
-- inside the middle centroid, a range straddling a centroid boundary, a
-- range inside the last centroid, and the untrimmed case.
SELECT lo, hi,
       tdigest_digest_sum(d, lo, hi) AS trimmed_sum,
       tdigest_digest_avg(d, lo, hi) AS trimmed_avg
  FROM (SELECT 'flags 1 count 30 compression 10 centroids 3 (10, 10) (20, 5) (30, 15)'::tdigest) t(d),
       (VALUES (0.4::float8,  0.5::float8),
               (0.35::float8, 0.45::float8),
               (0.45::float8, 0.55::float8),
               (0.3::float8,  0.6::float8),
               (0.6::float8,  0.9::float8),
               (0.0::float8,  1.0::float8)) v(lo, hi)
 ORDER BY lo, hi;

-- Exhaustive cross-check of every trimmed range against a brute-force
-- expansion of the same digest into individual items. Expected to return no
-- rows; any row is a range where the trimmed sum disagrees with the sum of
-- the items the range is supposed to cover.
WITH digest(d) AS (
  SELECT 'flags 1 count 30 compression 10 centroids 3 (10, 10) (20, 5) (30, 15)'::tdigest
),
-- the individual items of the digest, with their 0-based index
items(idx, val) AS (
  SELECT c.first_idx + g - 1, c.mean
    FROM (VALUES (0, 10.0::float8, 10),
                 (10, 20.0::float8, 5),
                 (15, 30.0::float8, 15)) c(first_idx, mean, cnt),
         generate_series(1, c.cnt) g
),
ranges(lo, hi) AS (
  SELECT (a / 20.0)::float8, (b / 20.0)::float8
    FROM generate_series(0, 20) a, generate_series(0, 20) b
   WHERE a < b
)
SELECT r.lo, r.hi,
       tdigest_digest_sum(digest.d, r.lo, r.hi) AS trimmed_sum,
       (SELECT sum(i.val) FROM items i
         WHERE i.idx >= floor(30::float8 * r.lo)
           AND i.idx <  ceil(30::float8 * r.hi)) AS expected_sum
  FROM ranges r, digest
 WHERE tdigest_digest_sum(digest.d, r.lo, r.hi)
       IS DISTINCT FROM
       (SELECT sum(i.val) FROM items i
         WHERE i.idx >= floor(30::float8 * r.lo)
           AND i.idx <  ceil(30::float8 * r.hi))
 ORDER BY r.lo, r.hi;