-- start_matchsubs
-- m/^LOG.*PartitionSelector/
-- s/^LOG.*PartitionSelector/PartitionSelector/
-- m/^LOG.*Feature not supported/
-- s/^LOG.*Feature not supported/Feature not supported/
-- end_matchsubs
-- start_matchignore
-- m/^LOG:  Missing statistics for column: .*/
-- end_matchignore
-- start_ignore
DROP DATABASE IF EXISTS incrementalanalyze;
CREATE DATABASE incrementalanalyze;
ALTER DATABASE incrementalanalyze SET lc_monetary TO 'C';
\c incrementalanalyze
DROP SCHEMA IF EXISTS incremental_analyze;
CREATE SCHEMA incremental_analyze;
-- end_ignore
-- Test ANALYZE for different data types
-- Case 1: Partitions have MCVs but after merge, none of the partition MCVs 
-- qualify as a global MCV for the root and they are used to create the
-- root histogram
DROP TABLE IF EXISTS foo;
SET default_statistics_target = 4;
CREATE TYPE inc_analyze_composite AS
(
    a numeric,
    b numeric
);
CREATE TABLE foo (
    a int,
    b int, 
    c03_bigint bigint, 
    c04_bigserial bigserial, 
    c05_bit bit, 
    c06_bit_varying_10 bit varying(10), 
    c07_bool bool, 
    c08_char char, 
    c09_varchar_10 varchar(10), 
    c10_cidr cidr, 
    c11_date date, 
    c12_numeric_8_2 numeric(8,2), 
    c13_float8 float8, 
    c14_inet inet, 
    c15_int4 int4, 
    c16_interval interval, 
    c17_macaddr macaddr, 
    c18_money money, 
    c19_float4 float4, 
    c20_serial4 serial4, 
    c21_smallint smallint, 
    c22_text text, 
    c23_time time, 
    c24_timetz timetz, 
    c25_timestamp timestamp, 
    c26_timestamptz timestamptz,
    c27_uuid uuid,
    c28_tsquery tsquery,
    c29_varchararray character varying(2)[],
    c30_intarray int[],
    c31_bigintarray bigint[],
    c33_bitarray bit(1)[],
    c34_bitvaryingarray bit varying(10)[],
    c35_boolarray boolean[],
    c36_chararray character(1)[],
    c37_cidrarray cidr[],
    c38_datearray date[],
    c39_numericarray numeric[],
    c40_float8array double precision[],
    c41_inetarray inet[],
    c42_int4array integer[],
    c43_intervalarray interval[],
    c44_macaddrarray macaddr[],
    c45_moneyarray money[],
    c46_float4array real[],
    c47_smallintarrayn smallint[],
    c48_textarray text[],
    c49_timearray time without time zone[],
    c50_timetzarray time with time zone[],
    c51_timestamparray timestamp without time zone[],
    c52_timestamptzarray timestamp with time zone[],
    c53_uuidzarray uuid[],
    c54_tsqueryarray tsquery[],
    c55_jsonarray json[],
    c57_pointarray point[],
    c58_linesegarray lseg[],
    c59_patharray path[],
    c60_boxarray box[],
    c61_polygonarray polygon[],
    c62_circlearray circle[],
    c63_inc_analyze_composite_array inc_analyze_composite[]
) PARTITION BY RANGE (b) (START (0) END(6) EVERY(3));
CREATE TYPE input_fields AS
(
    varchararray character varying(2)[],
    intarray int[],
    _bigintarray bigint[],
    bitarray bit(1)[],
    bitvaryingarray bit varying(10)[],
    boolarray boolean[],
    chararray character(1)[],
    cidrarray cidr[],
    datearray date[],
    numericarray numeric[],
    float8array double precision[],
    inetarray inet[],
    int4array integer[],
    intervalarray interval[],
    macaddrarray macaddr[],
    moneyarray money[],
    float4array real[],
    smallintarrayn smallint[],
    textarray text[],
    timearray time without time zone[],
    timetzarray time with time zone[],
    timestamparray timestamp without time zone[],
    timestamptzarray timestamp with time zone[],
    uuidzarray uuid[],
    tsqueryarray tsquery[],
    jsonarray json[],
    pointarray point[],
    linesegarray lseg[],
    patharray path[],
    boxarray box[],
    polygonarray polygon[],
    circlearray circle[],
    inc_analyze_composite_array inc_analyze_composite[]
);

CREATE OR REPLACE FUNCTION get_input_fields(i INT) RETURNS input_fields AS $$
DECLARE
    fields input_fields;
BEGIN
    SELECT
        ARRAY['a' || (i % 6), 'b' || (i % 6)]::character varying(2)[],
        ARRAY[i, i % 6]::int[],
        ARRAY[(i % 6), ((i + 1) % 6)]::bigint[],
        ARRAY[i, (i + 1)]::bit[],
        ARRAY[(i % 6)::bit(10), ((i + 1) % 6)::bit(10)]::bit varying(10)[],
        ARRAY[(i % 2), ((i + 1) % 2)]::bool[],
        ARRAY['a' || i, 'b' || i]::char[],
        ARRAY['192.168.100.' || (i % 6), '192.168.101.' || (i % 6)]::cidr[],
        ARRAY['2018-01-' || ((i % 6) + 1), '2018-01-' || ((i % 6) + 1)]::date[],
        ARRAY[(i % 6), ((i + 1) % 6)]::numeric[],
        ARRAY[(i % 6), ((i + 1) % 6)]::float8[],
        ARRAY['192.168.100.' || (i % 6), '192.168.100.' || ((i + 1) % 6)]::inet[],
        ARRAY[(i % 6), ((i + 1) % 6)]::int4[],
        '{"1 hour", "1 hour"}'::interval[],
        ARRAY['08:00:2b:01:02:' || (i % 6), '08:00:2b:01:02:' || (i % 6)]::macaddr[],
        ARRAY['123.4', '234.5']::money[],
        ARRAY[(i % 6), ((i + 1) % 6)]::float4[],
        ARRAY[(i % 6), ((i + 1) % 6)]::smallint[],
        ARRAY['abcd' || (i % 6), 'def' || ((i + 1) % 6)]::text[],
        ARRAY[(i % 6) || ':00:00', ((i + 1) % 6) || ':00:00']::time[],
        ARRAY[(i % 6) || ':00:59 PST', ((i + 1) % 6) || ':00:59 EST']::timetz[],
        ARRAY['2018-01-01 ' || (i % 6) || ':59:00', '2018-01-01 ' || ((i + 1) % 6) || ':59:00']::timestamp[],
        ARRAY['2018-01-01 ' || (i % 6) || ':59:00 PST', '2018-01-01 ' || ((i + 1) % 6) || ':59:00 EST']::timestamptz[],
        ARRAY['11111111-1111-1111-1111-1111111111' || (i % 6) || (i % 6), '11111111-1111-1111-1111-1111111111' || ((i + 1) % 6) || ((i + 1) % 6)]::uuid[],
        ARRAY['foo' || (i % 6) || ' & rat', 'rat' || (i % 6) || ' & foo']::tsquery[],
        ARRAY['{"a": "b"}', '{"c": "d"}']::json[],
        ARRAY[point(i, i + 1), point(i + 2, i + 3)],
        ARRAY[lseg(point(i, i + 1), point(i + 2, i + 3)), lseg(point(i + 4, i + 5), point(i + 6, i + 7))],
        ARRAY[path(polygon(box(point(i + 1, i + 2), point(i + 3, i + 4)))), path(polygon(box(point(i + 1, i + 2), point(i + 3, i + 4))))],
        ARRAY[box(point(i + 1, i + 2), point(i + 3, i + 4)), box(point(i + 1, i + 2), point(i + 3, i + 4))],
        ARRAY[polygon(box(point(i + 1, i + 2), point(i + 3, i + 4))), polygon(box(point(i + 1, i + 2), point(i + 3, i + 4)))], 
        ARRAY[circle(point(i + 1, i + 2), i + 3), circle(point(i + 4, i + 5), i + 6)],
        ARRAY[ROW(i % 6, (i + 1) % 6), ROW(i % 6, (i + 1) % 6)]::inc_analyze_composite[]
    INTO fields;

    RETURN fields;
END;
$$ LANGUAGE PLPGSQL;
INSERT INTO foo
SELECT
i,
i%6,
i%6::bigint,
i%6,
i::bit,
(i%6)::bit(10)::bit varying(10),
(i%2)::bool,
(i%6)::char,
('a'||(i%6))::varchar(10),
('192.168.100.'||(i%6))::cidr, 
('2018-4-'||(i%6)+1)::date, 
(i%6)::numeric(8,2), 
(i%6)::float8,
inet ('192.168.100.'||(i%6)),
i%6::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%6)::text)::text::macaddr,
(i%6)::text::money,
(i%6)::float4, 
i%6,
i%6,
'aa'||(i%6)::text,
((i%6)::text||':59:59')::time,
((i%6)::text||':59:00 EST')::timetz,
('2018-01-01 '||(i%6)::text||':59:00')::timestamp,
('2018-01-01 '||(i%6)::text||':59:00 EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%6)::text||(i%6)::text)::uuid,
('foo'||(i%6)::text||' & rat'||(i%6)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,100) i;
ANALYZE foo;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' and attname != 'a' ORDER BY attname,tablename;
-- Case 2: Partitions have no MCVs, only histograms, after merge root has to
-- have approximately merged histograms.
TRUNCATE foo;
INSERT INTO foo 
SELECT
i,
i%3,
i::bigint,
i,
i::bit,
(i)::bit(10)::bit varying(10),
(i)::bool,
(i)::char,
('a'||(i))::varchar(10),
('192.168.100.'||(i%128))::cidr, 
('2018-4-'||(i%15)+1)::date, 
(i)::numeric(8,2), 
(i)::float8,
inet ('192.168.100.'||(i%128)),
i::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%50)::text)::text::macaddr,
(i)::text::money,
(i)::float4, 
i,
i,
'aa'||(i)::text,
((i%12)::text||':59:59')::time,
((i%12)::text||':59:00 EST')::timetz,
('2018-01-01 '||(i%12)::text||':59:00')::timestamp,
('2018-01-01 '||(i%12)::text||':59:00 EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%5)::text||(i%5)::text)::uuid,
('foo'||(i)::text||' & rat'||(i)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,100) i;
INSERT INTO foo 
SELECT
i,
i%3,
i+200::bigint,
i+200,
i::bit,
(i+200)::bit(10)::bit varying(10),
(i+200)::bool,
(i+200)::char,
('a'||(i+200))::varchar(10),
('192.168.100.'||(i%128+128))::cidr, 
('2018-4-'||(i%15+16))::date, 
(i)::numeric(8,2),
(i)::float8,
inet ('192.168.100.'||(i%128+128)),
i::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%50+50)::text)::text::macaddr,
(i)::text::money,
(i)::float4,
i,
i,
'aa'||(i)::text,
((i%12+12)::text||':59:59')::time,
((i%12+12)::text||':59:00 EST')::timetz,
('2018-01-01 '||(i%12+12)::text||':59:00')::timestamp,
('2018-01-01 '||(i%12+12)::text||':59:00 EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%5+5)::text||(i%5+5)::text)::uuid,
('foo'||(i+200)::text||' & rat'||(i+200)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,100) i;
INSERT INTO foo 
SELECT
i,
i%3+3,
i::bigint,
i,
i::bit,
(i)::bit(10)::bit varying(10),
(i)::bool,
(i)::char,
('a'||(i))::varchar(10),
('192.168.100.'||(i%128))::cidr, 
('2018-4-'||(i%15)+1)::date, 
(i)::numeric(8,2), 
(i)::float8,
inet ('192.168.100.'||(i%128)),
i::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%50)::text)::text::macaddr,
(i)::text::money,
(i)::float4, 
i,
i,
'aa'||(i)::text,
((i%12)::text||':59:59')::time,
((i%12)::text||':59:00 EST')::timetz,
('2018-01-01 '||(i%12)::text||':59:00')::timestamp,
('2018-01-01 '||(i%12)::text||':59:00 EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%5)::text||(i%5)::text)::uuid,
('foo'||(i)::text||' & rat'||(i)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,200) i;
ANALYZE foo;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
-- Case 3: Partitions have MCVs but after merge, MCVs that do not qualify as 
-- global MCV for the root will be used to create root histograms
TRUNCATE foo;
INSERT INTO foo 
SELECT
i,
i%3,
i%4::bigint,
i%4,
i::bit,
(i%4)::bit(10)::bit varying(10),
(i)::bool,
(i%4)::char,
('a'||(i%4))::varchar(10),
('192.168.100.'||(i%4))::cidr, 
('2018-4-'||(i%4)+1)::date, 
(i%4)::numeric(8,2), 
(i%4)::float8,
inet ('192.168.100.'||(i%4)),
i%4::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%4)::text)::text::macaddr,
(i%4)::text::money,
(i%4)::float4, 
i%4,
i%4,
'aa'||(i%4)::text,
('12:'||(i%4)::text||':00')::time,
('12:'||(i%4)::text||':00 EST')::timetz,
('2018-01-01 '||'12:'||(i%4)::text||':00')::timestamp,
('2018-01-01 '||'12:'||(i%4)::text||':00 EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%4)::text||(i%4)::text)::uuid,
('foo'||(i%4)::text||' & rat'||(i%4)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,100) i;
INSERT INTO foo 
SELECT
i,
i%3+3,
i::bigint,
i,
i::bit,
(i)::bit(10)::bit varying(10),
(i)::bool,
(i)::char,
('a'||(i))::varchar(10),
('192.168.100.'||(i))::cidr, 
('2018-4-'||(i%26+1))::date, 
(i)::numeric(8,2),
(i)::float8,
inet ('192.168.100.'||(i)),
i::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i)::text)::text::macaddr,
(i)::text::money,
(i)::float4,
i,
i,
'aa'||(i)::text,
('12:'||(i%60)::text||':00')::time,
('12:'||(i%60)::text||':00 EST')::timetz,
('2018-01-01 '||'12:'||(i%60)::text||':00')::timestamp,
('2018-01-01 '||'12:'||(i%60)::text||':00 EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%6)::text||(i%6)::text)::uuid,
('foo'||(i)::text||' & rat'||(i)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,60) i;
INSERT INTO foo 
SELECT
i,
i%3+3,
i%4+61::bigint,
i%4+61,
(i%4+61)::bit,
(i%4+61)::bit(10)::bit varying(10),
(i%4+61)::bool,
(i%4+61)::char,
('a'||(i%4+61))::varchar(10),
('192.168.100.'||(i%4+61))::cidr, 
('2018-4-'||(i%4)+27)::date, 
(i%4+61)::numeric(8,2), 
(i%4+61)::float8,
inet ('192.168.100.'||(i%4+61)),
i%4+61::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%4+61)::text)::text::macaddr,
(i%4+61)::text::money,
(i%4+61)::float4, 
i%4+61,
i%4+61,
'aa'||(i%4+61)::text,
('12:59:'||(i%4+10)::text)::time,
('12:59:'||(i%4+10)::text||' EST')::timetz,
('2018-01-01 '||'12:59:'||(i%4+10)::text)::timestamp,
('2018-01-01 '||'12:59:'||(i%4+10)::text||' EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%4+6)::text||(i%4+6)::text)::uuid,
('foo'||(i%4)::text||' & rat'||(i%4)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,40) i;
ANALYZE foo;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
-- Case 4: A partition has MCVs but after merge, those MCVs do not qualify as 
-- global MCV for the root will be used to create root histograms
TRUNCATE foo;
INSERT INTO foo 
SELECT
i,
i%3,
i::bigint,
i,
i::bit,
(i)::bit(10)::bit varying(10),
(i)::bool,
(i)::char,
('a'||(i))::varchar(10),
('192.168.100.'||(i%128))::cidr, 
('2018-4-'||(i%15)+1)::date, 
(i)::numeric(8,2), 
(i)::float8,
inet ('192.168.100.'||(i%128)),
i::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%50)::text)::text::macaddr,
(i)::text::money,
(i)::float4, 
i,
i,
'aa'||(i)::text,
((i%12)::text||':59:59')::time,
((i%12)::text||':59:00 EST')::timetz,
('2018-01-01 '||(i%12)::text||':59:00')::timestamp,
('2018-01-01 '||(i%12)::text||':59:00 EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%5)::text||(i%5)::text)::uuid,
('foo'||(i)::text||' & rat'||(i)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,100) i;
INSERT INTO foo 
SELECT
i,
i%3+3,
i%4+61::bigint,
i%4+61,
(i%4+61)::bit,
(i%4+61)::bit(10)::bit varying(10),
(i%4+61)::bool,
(i%4+61)::char,
('a'||(i%4+61))::varchar(10),
('192.168.100.'||(i%4+61))::cidr, 
('2018-4-'||(i%4+26))::date, 
(i%4+61)::numeric(8,2),
(i%4+61)::float8,
inet ('192.168.100.'||(i%4+61)),
i%4+61::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%4+61)::text)::text::macaddr,
(i%4+61)::text::money,
(i%4+61)::float4,
i%4+61,
i%4+61,
'aa'||(i%4+61)::text,
('12:'||(i%4+56)::text||':00')::time,
('12:'||(i%4+56)::text||':00 EST')::timetz,
('2018-01-01 '||'12:'||(i%4+56)::text||':00')::timestamp,
('2018-01-01 '||'12:'||(i%4+56)::text||':00 EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%4+6)::text||(i%4+6)::text)::uuid,
('foo'||(i%4+61)::text||' & rat'||(i%4+61)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,8) i;
INSERT INTO foo 
SELECT
i,
i%3+3,
i::bigint,
i,
i::bit,
(i)::bit(10)::bit varying(10),
(i)::bool,
(i)::char,
('a'||(i))::varchar(10),
('192.168.100.'||(i))::cidr, 
('2018-4-'||(i%25+1))::date, 
(i)::numeric(8,2),
(i)::float8,
inet ('192.168.100.'||(i)),
i::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i)::text)::text::macaddr,
(i)::text::money,
(i)::float4,
i,
i,
'aa'||(i)::text,
('12:00:'||(i%60)::text)::time,
('12:00:'||(i%60)::text||' EST')::timetz,
('2018-01-01 '||'12:00'||(i%60)::text)::timestamp,
('2018-01-01 '||'12:00'||(i%60)::text||' EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%10)::text||(i%10)::text)::uuid,
('foo'||(i)::text||' & rat'||(i)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,60) i;
ANALYZE foo;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' and attname != 'a' ORDER BY attname,tablename;
-- Case 5: A partition has MCVs but after merge, those MCVs qualify as global
-- MCVs for the root
TRUNCATE foo;
INSERT INTO foo 
SELECT
i,
i%3,
i::bigint,
i,
i::bit,
(i)::bit(10)::bit varying(10),
(i)::bool,
(i)::char,
('a'||(i))::varchar(10),
('192.168.100.'||(i%128))::cidr, 
('2018-'||(i%12)+1||'-'||(i%15)+1)::date,
(i)::numeric(8,2), 
(i)::float8,
inet ('192.168.100.'||(i%128)),
i::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%50)::text)::text::macaddr,
(i)::text::money,
(i)::float4, 
i,
i,
'aa'||(i)::text,
('12:'||(i%60)::text||':'||(i%30)::text)::time,
('12:'||(i%60)::text||':'||(i%30)::text||' EST')::timetz,
('2018-01-01 '||'12:'||(i%60)::text||':'||(i%30)::text)::timestamp,
('2018-01-01 '||'12:'||(i%60)::text||':'||(i%30)::text||' EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%5)::text||(i%5)::text)::uuid,
('foo'||(i)::text||' & rat'||(i)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,60) i;
INSERT INTO foo 
SELECT
i,
i%3+3,
i%4+61::bigint,
i%4+61,
(i%4+61)::bit,
(i%4+61)::bit(10)::bit varying(10),
(i%4+61)::bool,
(i%4+61)::char,
('a'||(i%4+61))::varchar(10),
('192.168.100.'||(i%4+61))::cidr, 
('2018-5-'||(i%4)+16)::date,
(i%4+61)::numeric(8,2),
(i%4+61)::float8,
inet ('192.168.100.'||(i%4+61)),
i%4+61::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i%4+61)::text)::text::macaddr,
(i%4+61)::text::money,
(i%4+61)::float4,
i%4+61,
i%4+61,
'aa'||(i%4+61)::text,
('01:00:'||(i%4)::text)::time,
('01:00:'||(i%4)::text||' EST')::timetz,
('2018-01-01 '||'01:00:'||(i%4)::text)::timestamp,
('2018-01-01 '||'01:00:'||(i%4)::text||' EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%4+6)::text||(i%4+6)::text)::uuid,
('foo'||(i%4+61)::text||' & rat'||(i%4+61)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,8) i;
INSERT INTO foo 
SELECT
i,
i%3+3,
i::bigint,
i,
i::bit,
(i)::bit(10)::bit varying(10),
(i)::bool,
(i)::char,
('a'||(i))::varchar(10),
('192.168.100.'||(i))::cidr, 
('2018-'||(i%12)+1||'-'||(i%15)+1)::date,
(i)::numeric(8,2),
(i)::float8,
inet ('192.168.100.'||(i)),
i::int4,
interval '24 hours',
macaddr ('12:34:56:78:90:'||(i)::text)::text::macaddr,
(i)::text::money,
(i)::float4,
i,
i,
'aa'||(i)::text,
('12:'||(i%60)::text||':'||(i%30)::text)::time,
('12:'||(i%60)::text||':'||(i%30)::text||' EST')::timetz,
('2018-01-01 '||'12:'||(i%60)::text||':'||(i%30)::text)::timestamp,
('2018-01-01 '||'12:'||(i%60)::text||':'||(i%30)::text||' EST')::timestamptz,
('11111111-1111-1111-1111-1111111111'||(i%10)::text||(i%10)::text)::uuid,
('foo'||(i)::text||' & rat'||(i)::text)::tsquery,
(get_input_fields(i)).*
FROM generate_series(1,10) i;
ANALYZE foo;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' and attname != 'a' ORDER BY attname,tablename;
-- Test merging leaf stats where HLL is empty
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c text) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
INSERT INTO foo select i, i%6, repeat('aaaa', 100000) FROM generate_series(1, 100)i;
ANALYZE foo;
-- In single node, we don't 'toolarge' bitmap created in acquire_sample_rows_dispatcher. The result is more similar to postgres.
SELECT * FROM pg_stats WHERE tablename like 'foo%' and attname = 'c' ORDER BY attname,tablename;
-- Test ANALYZE full scan HLL
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c text) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
INSERT INTO foo SELECT i, i%3, 'text_'||i%100 FROM generate_series(1,1000)i; 
INSERT INTO foo SELECT i, i%3+3, 'text_'||i%200 FROM generate_series(1,1000)i; 
SET default_statistics_target to 3;
ANALYZE FULLSCAN foo;
SELECT tablename, n_distinct FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
-- Test ANALYZE auto merge behavior
-- Do not merge stats from only one partition while other partitions have not been analyzed yet
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c int) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
INSERT INTO foo SELECT i, i%6, i%6 FROM generate_series(1,100)i; 
ANALYZE foo_1_prt_1;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;

-- Merge stats from both partitions
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c int) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
INSERT INTO foo SELECT i, i%6, i%6 FROM generate_series(1,100)i; 
ANALYZE foo_1_prt_1;
ANALYZE foo_1_prt_2;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;

-- No stats after merging 
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c int) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
ANALYZE foo_1_prt_1;
ANALYZE foo_1_prt_2;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;

-- Merge stats from only one partition
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c int) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
INSERT INTO foo SELECT i, i%6, i%6 FROM generate_series(1,100)i; 
SET allow_system_table_mods=true;
UPDATE pg_attribute SET attstattarget=0 WHERE attrelid = 'foo_1_prt_1'::regclass and ATTNAME in ('a','b','c');
ANALYZE foo_1_prt_1;
ANALYZE foo_1_prt_2;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;

-- Merge stats from only one partition one column
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c int) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
INSERT INTO foo SELECT i, i%6, i%6 FROM generate_series(1,100)i; 
ANALYZE foo_1_prt_1(c);
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;

-- Test merging of leaf stats when one partition has
-- FULL SCAN HLL and the other has HLL from sample
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
INSERT INTO FOO SELECT i,i%6 FROM generate_series(1,1000)i;
ANALYZE foo_1_prt_1;
ANALYZE FULLSCAN foo_1_prt_2;
-- Test merging of stats for a newly added partition
-- Do not collect samples while merging stats
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
INSERT INTO FOO SELECT i,i%6 FROM generate_series(1,1000)i;
SET default_statistics_target = 4;
ANALYZE foo;
ALTER TABLE foo ADD partition new_part START (6) INCLUSIVE END (9) EXCLUSIVE;
INSERT INTO foo SELECT i, i%3+6 FROM generate_series(1,500)i;
ANALYZE foo_1_prt_new_part;
SET log_statement='none';
SET client_min_messages = 'log';
-- Insert a new column that is not analyzed in the leaf partitions.
-- Analyzing root partition will use merging statistics for the first 2 columns, 
-- will create a sample for the root to analyze the newly added columns since 
-- the leaf partitions does not have any stats for it, yet
ALTER TABLE foo ADD COLUMN c int;
INSERT INTO foo SELECT i, i%9, i%100 FROM generate_series(1,500)i;
-- start_matchsubs
-- m/gp_acquire_sample_rows([^,]+, [^,]+, .+)/
-- s/gp_acquire_sample_rows([^,]+, [^,]+, .+)/gp_acquire_sample_rows()/
-- end_matchsubs
ANALYZE VERBOSE rootpartition foo;
-- Testing auto merging root statistics for all columns
-- where column attnums are differents due to dropped columns
-- and split partitions.
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c text, d int)
	PARTITION BY RANGE (a) 
		(START (0) END (8) EVERY (4), 
		DEFAULT PARTITION def_part);
INSERT INTO foo SELECT i%13, i, 'something'||i::text, i%121 FROM generate_series(1,1000)i;
ALTER TABLE foo DROP COLUMN b;
ALTER TABLE foo SPLIT DEFAULT PARTITION START (8) END (12) INTO (PARTITION new_part, default PARTITION);
set client_min_messages to 'log';
ANALYZE foo_1_prt_2;
ANALYZE foo_1_prt_3;
ANALYZE foo_1_prt_new_part;
ANALYZE foo_1_prt_def_part;
reset client_min_messages;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
-- Testing auto merging root statistics for a column whose attnum
-- is aligned and the same in every partition due to dropped columns
-- and split partitions.
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c text, d int)
	PARTITION BY RANGE (a) 
		(START (0) END (8) EVERY (4), 
		DEFAULT PARTITION def_part);
INSERT INTO foo SELECT i%13, i, 'something'||i::text, i%121 FROM generate_series(1,1000)i;
ALTER TABLE foo DROP COLUMN b;
ALTER TABLE foo SPLIT DEFAULT PARTITION START (8) END (12) INTO (PARTITION new_part, default PARTITION);
set client_min_messages to 'log';
ANALYZE foo_1_prt_2(a);
ANALYZE foo_1_prt_3(a);
ANALYZE foo_1_prt_new_part(a);
ANALYZE foo_1_prt_def_part(a);
reset client_min_messages;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
-- Testing auto merging root statistics for a column whose attnum
-- is not aligned and different in partitions due to dropped columns
-- and split partitions.
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c text, d int)
	PARTITION BY RANGE (a) 
		(START (0) END (8) EVERY (4), 
		DEFAULT PARTITION def_part);
INSERT INTO foo SELECT i%13, i, 'something'||i::text, i%121 FROM generate_series(1,1000)i;
ALTER TABLE foo DROP COLUMN b;
ALTER TABLE foo SPLIT DEFAULT PARTITION START (8) END (12) INTO (PARTITION new_part, default PARTITION);
set client_min_messages to 'log';
ANALYZE foo_1_prt_2(d);
ANALYZE foo_1_prt_3(d);
ANALYZE foo_1_prt_new_part(d);
ANALYZE foo_1_prt_def_part(d);
reset client_min_messages;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
-- Testing ANALYZE ROOTPARTITION and when optimizer_analyze_root_partition is off
-- for incremental analyze.
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c text)
	PARTITION BY RANGE (b) 
		(START (0) END (8) EVERY (4));
INSERT INTO foo SELECT i%130, i%8, 'something'||i::text FROM generate_series(1,1000)i;
set optimizer_analyze_root_partition=off;
set client_min_messages to 'log';
-- ANALYZE ROOTPARTITION will sample the table and compute statistics since there
-- is not stats to be merged in the leaf partitions
ANALYZE ROOTPARTITION foo;
reset client_min_messages;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
ANALYZE foo_1_prt_1;
ANALYZE foo_1_prt_2;
set client_min_messages to 'log';
-- ANALYZE ROOT PARTITION will piggyback on the stats collected from the leaf and merge them
ANALYZE ROOTPARTITION foo;
reset client_min_messages;
reset optimizer_analyze_root_partition;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
-- Testing that auto merge will be disabled when optimizer_analyze_root_partition
-- is off for incremental analyze. 
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c text)
	PARTITION BY RANGE (b) 
		(START (0) END (8) EVERY (4));
INSERT INTO foo SELECT i%130, i%8, 'something'||i::text FROM generate_series(1,1000)i;
set optimizer_analyze_root_partition=off;
ANALYZE foo_1_prt_1;
ANALYZE foo_1_prt_2;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
reset optimizer_analyze_root_partition;
-- Test incremental analyze on a partitioned table with different storage type and compression algorithm
DROP TABLE IF EXISTS incr_analyze_test;
CREATE TABLE incr_analyze_test (
    a integer,
    b character varying,
    c date
)
WITH (appendonly=true, orientation=row) PARTITION BY RANGE(c)
          (
          START ('2018-01-01'::date) END ('2018-01-02'::date) EVERY ('1 day'::interval) WITH (tablename='incr_analyze_test_1_prt_1', appendonly=true, compresslevel=3, orientation=column, compresstype=ZLIB ),
          START ('2018-01-02'::date) END ('2018-01-03'::date) EVERY ('1 day'::interval) WITH (tablename='incr_analyze_test_1_prt_2', appendonly=true, compresslevel=1, orientation=column, compresstype=RLE_TYPE ),
          START ('2018-01-03'::date) END ('2018-01-04'::date) EVERY ('1 day'::interval) WITH (tablename='incr_analyze_test_1_prt_3', appendonly=true, compresslevel=1, orientation=column, compresstype=ZLIB ),
          START ('2018-01-04'::date) END ('2018-01-05'::date) EVERY ('1 day'::interval) WITH (tablename='incr_analyze_test_1_prt_4', appendonly=true, compresslevel=1, orientation=row, compresstype=ZLIB ),
          START ('2018-01-05'::date) END ('2018-01-06'::date) EVERY ('1 day'::interval) WITH (tablename='incr_analyze_test_1_prt_5', appendonly=true, compresslevel=1, orientation=row, compresstype=ZLIB ),
          START ('2018-01-06'::date) END ('2018-01-07'::date) EVERY ('1 day'::interval) WITH (tablename='incr_analyze_test_1_prt_6', appendonly=false)
          );

INSERT INTO incr_analyze_test VALUES (1, 'a', '2018-01-01');
ANALYZE incr_analyze_test;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'incr_analyze_test%' ORDER BY attname,tablename;
INSERT INTO incr_analyze_test SELECT s, md5(s::varchar), '2018-01-02' FROM generate_series(1, 1000) AS s;
ANALYZE incr_analyze_test_1_prt_2;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'incr_analyze_test%' ORDER BY attname,tablename;
SELECT relname, relpages, reltuples FROM pg_class WHERE relname LIKE 'incr_analyze_test%' ORDER BY relname;
-- Test merging of stats if an empty partition contains relpages > 0
-- Do not collect samples while merging stats
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int) PARTITION BY RANGE (b) (START (0) END (6) EVERY (3));
INSERT INTO foo SELECT i,i%3 FROM generate_series(1,10)i;
ANALYZE foo_1_prt_1;
ANALYZE foo_1_prt_2;
SET allow_system_table_mods = on;
UPDATE pg_class set relpages=3 WHERE relname='foo_1_prt_2';
RESET allow_system_table_mods;
analyze verbose rootpartition foo;
-- ensure relpages is correctly set after analyzing
analyze foo_1_prt_2;
select reltuples, relpages from pg_class where relname ='foo_1_prt_2';
-- Test application of column-wise statistics setting to the number of MCVs and histogram bounds on partitioned table
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int) PARTITION BY RANGE (a) (START (0) END (10) EVERY (5));
-- fill foo with even numbers twice as large than odd ones to avoid fully even distribution of 'a' attribute and hence empty MCV/MCF
INSERT INTO foo SELECT i%10 FROM generate_series(0, 100) i;
INSERT INTO foo SELECT i%10 FROM generate_series(0, 100) i WHERE i%2 = 0;
-- default_statistics_target is 4
ALTER TABLE foo ALTER COLUMN a SET STATISTICS 5;
ANALYZE foo;
SELECT array_length(most_common_vals, 1), array_length(most_common_freqs, 1), array_length(histogram_bounds, 1) FROM pg_stats WHERE tablename = 'foo' AND attname = 'a';

-- Make sure a simple heap table does not store HLL values
CREATE TABLE simple_table_no_hll (a int);
INSERT INTO simple_table_no_hll SELECT generate_series(1,10);
ANALYZE simple_table_no_hll;
SELECT staattnum, stakind1, stakind2, stakind3, stakind4, stakind5,
       stavalues1, stavalues2, stavalues3, stavalues4, stavalues5
FROM pg_statistic WHERE starelid = 'simple_table_no_hll'::regclass;

-- Make sure analyze rootpartition option works in an option list
set optimizer_analyze_root_partition to off;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo(a int) PARTITION BY RANGE(a) (start (0) INCLUSIVE END (20) EVERY (10));
INSERT INTO foo values (5),(15);
ANALYZE (verbose, rootpartition off) foo;

-- root should not have stats
SELECT count(*) from pg_statistic where starelid='foo'::regclass;

-- root should have stats
ANALYZE (verbose, rootpartition on) foo;
SELECT count(*) from pg_statistic where starelid='foo'::regclass;

-- Make sure analyze hll fullscan option works in an option list
ANALYZE (verbose, fullscan on) foo;
ANALYZE (verbose, fullscan off) foo;
reset optimizer_analyze_root_partition;

-- Test merging of stats after the last partition is analyzed. Merging should
-- be done for root without taking a sample from root if one of the column
-- statistics collection is turned off
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int, b int, c gp_hyperloglog_estimator) PARTITION BY RANGE (b) (START (1) END (3) EVERY (1));
SET gp_autostats_mode=none;
ALTER TABLE foo ALTER COLUMN c SET STATISTICS 0;
INSERT INTO foo SELECT i,i%2+1, NULL FROM generate_series(1,100)i;
ANALYZE VERBOSE foo_1_prt_1;
ANALYZE VERBOSE foo_1_prt_2;
SELECT tablename, attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds FROM pg_stats WHERE tablename like 'foo%' ORDER BY attname,tablename;
RESET gp_autostats_mode;

-- analyze in transaction should merge leaves instead of resampling
drop table if exists foo;
create table foo (a int, b date) partition by range(b) (partition "20210101" start ('20210101'::date) end ('20210201'::date), partition "20210201" start ('20210201'::date) end ('20210301'::date), partition "20210301" start ('20210301'::date) end ('20210401'::date));
insert into foo select a, '20210101'::date+a from (select generate_series(1,80) a) t1;
analyze verbose foo;

-- we should see "analyzing "public.foo" inheritance tree" in the output below
begin;
truncate foo_1_prt_20210201;
insert into foo select a, '20210101'::date+a from (select generate_series(31,40) a) t1;
analyze verbose foo_1_prt_20210201;
rollback;