methods/array_ops/src/pg_gp/test/array_ops.sql_in

---------------------------------------------------------------------------
-- Rules:
-- ------
-- 1) Any DB objects should be created w/o schema prefix,
--    since this file is executed in a separate schema context.
-- 2) There should be no DROP statements in this script, since
--    all objects created in the default schema will be cleaned-up outside.
---------------------------------------------------------------------------

m4_include(`SQLCommon.m4')
m4_changequote(`<!', `!>')

---------------------------------------------------------------------------
-- Setup:
---------------------------------------------------------------------------
CREATE TABLE data AS
SELECT
    '{1,2,3}'::float8[] AS an,
    '{4,5,7}'::float8[] AS b;

SELECT array_dot(
    array_mult(
        array_add(an,b),
        array_sub(an,b)),
    array_mult(
        array_div(an,b),
        normalize(an))) AS result1
FROM data;

SELECT array_max(b) FROM data;
SELECT array_min(b) FROM data;
SELECT array_sum(b) FROM data;
SELECT array_sum_big(b) FROM data;
SELECT array_mean(b) FROM data;
SELECT array_stddev(b) FROM data;

SELECT array_max(b)
    + array_min(b)
    + array_sum(b)
    + array_sum_big(b)
    + array_mean(b)
    + array_stddev(b) AS result2
FROM data;

SELECT array_sum(
    array_scalar_mult(
        array_fill(
            array_of_float(20),
            234.343::FLOAT8),
        3.7::FLOAT8))
FROM data;

-- array_agg
CREATE TABLE test AS SELECT generate_series(1,100) x;
SELECT array_agg(x) FROM test;

-- array_scalar_add
SELECT assert(
    relative_error(
        array_scalar_add(ARRAY[0.8, 9.5, 2.6]::float8[], 2.2::float8),
        ARRAY[3.0, 11.7, 4.8]::float8[]) < 1e-6,
    'array_scalar_add: Wrong results');

-- array_square
SELECT assert(
    relative_error(
        array_square(ARRAY[3, 4, 5]::float8[]),
        ARRAY[9.0, 16.0, 25.0]::float8[]) < 1e-6,
    'array_square: Wrong results');

SELECT array_filter(ARRAY[0.8, 9.5, 0, 2.6]::float8[]);
SELECT array_filter(ARRAY[0.8, 9.5, 0, 2.6]::float8[], 2.5::float8, '<');

------------------------------------------------------------
-- numeric[]
------------------------------------------------------------
-- (array) -> scalar
SELECT array_sum_big(ARRAY[-1, 1.3, 2.9]);
SELECT array_sum(ARRAY[-1, 1.3, 2.9]);
SELECT array_max(ARRAY[-1, 1.3, 2.9]);
SELECT array_min(ARRAY[-1, 1.3, 2.9]);
SELECT array_stddev(ARRAY[-1, 1.3, 2.9]);
SELECT array_mean(ARRAY[-1, 1.3, 2.9]);

-- (array, array) -> scalar
SELECT array_dot(ARRAY[-1, 1.3, 2.9], ARRAY[1,2,3]::numeric[]);
SELECT array_contains(ARRAY[1., 2.], ARRAY[1., 0]);

-- (array,) -> array
SELECT array_scalar_mult(
    ARRAY[1,2,3,4]::numeric[],
    (1.0/MADLIB_SCHEMA.array_sum(ARRAY[1.,2,3,4]))
);

--------------------------------------------------------------
-- TESTING array_unnest_2d_to_1d FUNCTION
--------------------------------------------------------------
-- 2-element float8 arrays
DROP TABLE IF EXISTS unnest_2d_tbl01;
CREATE TABLE unnest_2d_tbl01 (id INT, val DOUBLE PRECISION[][]);
INSERT INTO unnest_2d_tbl01 VALUES
  (1, ARRAY[[1::float8,2],[3::float8,4],[5::float8,6]]),
  (2, ARRAY[[101::float8,202],[303::float8,404],[505::float8,606]])
;

DROP TABLE IF EXISTS unnest_2d_tbl01_groundtruth;
CREATE TABLE unnest_2d_tbl01_groundtruth (
  id INT,
  unnest_row_id INT,
  val DOUBLE PRECISION[]
);
INSERT INTO unnest_2d_tbl01_groundtruth VALUES
  (1, 1, ARRAY[1::float8,2]),
  (1, 2, ARRAY[3::float8,4]),
  (1, 3, ARRAY[5::float8,6]),
  (2, 1, ARRAY[101::float8,202]),
  (2, 2, ARRAY[303::float8,404]),
  (2, 3, ARRAY[505::float8,606])
;

DROP TABLE IF EXISTS unnest_2d_tbl01_out;
CREATE TABLE unnest_2d_tbl01_out AS
  SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl01;

SELECT assert(
  unnest_result = val,
  'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl01"'
)
FROM (
  SELECT * FROM
    unnest_2d_tbl01_out t1
    JOIN
    unnest_2d_tbl01_groundtruth t2
    USING (id,unnest_row_id)
) t3;

-- 3-element float8 arrays
DROP TABLE IF EXISTS unnest_2d_tbl02;
CREATE TABLE unnest_2d_tbl02 (id INT, val DOUBLE PRECISION[][]);
INSERT INTO unnest_2d_tbl02 VALUES
  (1, ARRAY[[1.57::float8,2,3],[4::float8,5,6]]),
  (2, ARRAY[[101::float8,202,303],[PI(),505,606]]),
  (3, ARRAY[[1011::float8,2022,3033],[4044,5055,60.66]])
;

DROP TABLE IF EXISTS unnest_2d_tbl02_groundtruth;
CREATE TABLE unnest_2d_tbl02_groundtruth (
  id INT,
  unnest_row_id INT,
  val DOUBLE PRECISION[]
);
INSERT INTO unnest_2d_tbl02_groundtruth VALUES
  (1, 1, array[1.57::float8,2,3]),
  (1, 2, array[4::float8,5,6]),
  (2, 1, array[101::float8,202,303]),
  (2, 2, array[pi(),505,606]),
  (3, 1, array[1011::float8,2022,3033]),
  (3, 2, array[4044,5055,60.66])
;

DROP TABLE IF EXISTS unnest_2d_tbl02_out;
CREATE TABLE unnest_2d_tbl02_out AS
  SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl02;

SELECT assert(
  unnest_result = val,
  'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl02"'
)
FROM (
  SELECT * FROM
    unnest_2d_tbl02_out t1
    JOIN
    unnest_2d_tbl02_groundtruth t2
    USING (id,unnest_row_id)
) t3;

-- 2-element text arrays
DROP TABLE IF EXISTS unnest_2d_tbl03;
CREATE TABLE unnest_2d_tbl03 (id INT, val TEXT[][]);
INSERT INTO unnest_2d_tbl03 VALUES
  (1, ARRAY[['a','b'],['c','d'],['e','f']]),
  (2, ARRAY[['apple','banana'],['cherries','kiwi'],['lemon','mango']])
;

DROP TABLE IF EXISTS unnest_2d_tbl03_groundtruth;
CREATE TABLE unnest_2d_tbl03_groundtruth (
  id INT,
  unnest_row_id INT,
  val TEXT[]
);
INSERT INTO unnest_2d_tbl03_groundtruth VALUES
  (1, 1, ARRAY['a','b']),
  (1, 2, ARRAY['c','d']),
  (1, 3, ARRAY['e','f']),
  (2, 1, ARRAY['apple','banana']),
  (2, 2, ARRAY['cherries','kiwi']),
  (2, 3, ARRAY['lemon','mango'])
;

DROP TABLE IF EXISTS unnest_2d_tbl03_out;
CREATE TABLE unnest_2d_tbl03_out AS
  SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl03;

SELECT assert(
  unnest_result = val,
  'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl03"'
)
FROM (
  SELECT * FROM
    unnest_2d_tbl03_out t1
    JOIN
    unnest_2d_tbl03_groundtruth t2
    USING (id,unnest_row_id)
) t3;

-- 3-element float8 arrays with some NULLs
DROP TABLE IF EXISTS unnest_2d_tbl04;
CREATE TABLE unnest_2d_tbl04 (id INT, val DOUBLE PRECISION[][]);
INSERT INTO unnest_2d_tbl04 VALUES
  (1, ARRAY[[1::float8,NULL,3],[4.0,5,NULL]]),
  (2, ARRAY[[101::float8,NULL,303],
            [NULL::float8,NULL,NULL]]::double precision[][]),
  (3, ARRAY[[NULL,2022::float8],[4044::float8,NULL]])
;

DROP TABLE IF EXISTS unnest_2d_tbl04_groundtruth;
CREATE TABLE unnest_2d_tbl04_groundtruth (
  id INT,
  unnest_row_id INT,
  val DOUBLE PRECISION[]
);
INSERT INTO unnest_2d_tbl04_groundtruth VALUES
  (1, 1, ARRAY[1::float8,NULL,3]),
  (1, 2, ARRAY[4.0::float8,5,NULL]),
  (2, 1, ARRAY[101::float8,NULL,303]),
  (2, 2, ARRAY[NULL::float8,NULL,NULL]),
  (3, 1, ARRAY[NULL,2022::float8]),
  (3, 2, ARRAY[4044::float8,NULL])
;

DROP TABLE IF EXISTS unnest_2d_tbl04_out;
CREATE TABLE unnest_2d_tbl04_out AS
  SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl04;

SELECT assert(
  unnest_result = val,
  'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl04"'
)
FROM (
  SELECT * FROM
    unnest_2d_tbl04_out t1
    JOIN
    unnest_2d_tbl04_groundtruth t2
    USING (id,unnest_row_id)
) t3;

-- 3-element timestamp arrays with NULLs
DROP TABLE IF EXISTS unnest_2d_tbl05;
CREATE TABLE unnest_2d_tbl05 (id INT, val TIMESTAMP WITHOUT TIME ZONE[][]);
INSERT INTO unnest_2d_tbl05 VALUES
  (1, array[['2017-01-01 11:00:02'::TIMESTAMP WITHOUT TIME ZONE,
             '2017-01-01 13:00:05',
             '2017-01-02 11:55:00'],
            ['2016-10-12 12:00:02'::TIMESTAMP WITHOUT TIME ZONE,
             '2016-10-12 13:15:22',
             NULL]]),
  (2, NULL),
  (3, array[['2014-02-01 11:00:02'::TIMESTAMP WITHOUT TIME ZONE,
             '2014-02-01 13:00:05',
             '2014-02-02 11:55:00'],
            ['2013-07-12 12:00:02'::TIMESTAMP WITHOUT TIME ZONE,
             NULL,
             '2013-07-12 13:15:22']])
;

DROP TABLE IF EXISTS unnest_2d_tbl05_groundtruth;
CREATE TABLE unnest_2d_tbl05_groundtruth (
  id INT,
  unnest_row_id INT,
  val TIMESTAMP WITHOUT TIME ZONE[]
);
INSERT INTO unnest_2d_tbl05_groundtruth VALUES
  (1, 1, ARRAY['2017-01-01 11:00:02'::TIMESTAMP WITHOUT TIME ZONE,
               '2017-01-01 13:00:05',
               '2017-01-02 11:55:00']),
  (1, 2, ARRAY['2016-10-12 12:00:02'::TIMESTAMP WITHOUT TIME ZONE,
               '2016-10-12 13:15:22',
               NULL]),
  (2, NULL, NULL),
  (3, 1, ARRAY['2014-02-01 11:00:02'::TIMESTAMP WITHOUT TIME ZONE,
               '2014-02-01 13:00:05',
               '2014-02-02 11:55:00']),
  (3, 2, ARRAY['2013-07-12 12:00:02'::TIMESTAMP WITHOUT TIME ZONE,
               NULL,
               '2013-07-12 13:15:22'])
;

DROP TABLE IF EXISTS unnest_2d_tbl05_out;
CREATE TABLE unnest_2d_tbl05_out AS
  SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl05;

SELECT assert(
  unnest_result = val,
  'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl05"'
)
FROM (
  SELECT * FROM
    unnest_2d_tbl05_out t1
    JOIN
    unnest_2d_tbl05_groundtruth t2
    USING (id,unnest_row_id)
) t3;

-- TESTING array_to_bytea() function - skip for gpdb 4.3
m4_ifelse(__PORT__ __DBMS_VERSION_MAJOR__, <!GREENPLUM 4!>,,
<!

-- create input table ( n = 3 x 5 x 7 dim SMALLINT[], r =  2 x 3 x 5 dim REAL[] )
DROP TABLE IF EXISTS array_input_tbl;
CREATE TABLE array_input_tbl (id SMALLINT, n SMALLINT[], r REAL[]);
INSERT INTO array_input_tbl SELECT generate_series(1, 10);
SELECT id, count(*), array_agg(n) from (select id, unnest(n) as n from array_input_tbl) u group by id order by id;
UPDATE array_input_tbl SET
    n=array_fill(2*id, ARRAY[3, 5, 7]),
    r=array_fill(id + 0.4, array[2, 3, 5]);

-- create flattened input table
DROP TABLE IF EXISTS flat_array_input_tbl;
CREATE TABLE flat_array_input_tbl (id SMALLINT, n SMALLINT[], n_length SMALLINT, r REAL[], r_length SMALLINT);
INSERT INTO flat_array_input_tbl
    SELECT n.id, n, n_length, r, r_length
    FROM
    (
        SELECT id, array_agg(n) AS n, 2*COUNT(*) AS n_length
        FROM
        (
            SELECT id, unnest(n) AS n FROM array_input_tbl
        ) n_values
        GROUP BY id
    ) n
    JOIN
    (
        SELECT id, array_agg(r) AS r, 4*COUNT(*) AS r_length
        FROM
        (
            SELECT id, unnest(r) AS r FROM array_input_tbl
        ) r_values
        GROUP BY id
    ) r
    USING (id);

CREATE TABLE bytea_tbl AS SELECT id, array_to_bytea(n) AS n, array_to_bytea(r) AS r FROM array_input_tbl;

    -- verify lengths of BYTEA output is correct for SMALLINT & REAL arrays
    SELECT assert(
        length(o.n) = i.n_length AND length(o.r) = i.r_length,
        'array_to_bytea() returned incorrect lengths:\n' ||
        '   Expected length(n) = ' || n_length::TEXT || ', got ' || length(o.n) ||
        '   Expected ' || r_length::TEXT || ', got ' || length(o.r)
    )
    FROM flat_array_input_tbl i JOIN bytea_tbl o USING (id);

    -- convert BYTEA back to flat arrays of SMALLINT's & REAL's

    CREATE TABLE array_output_tbl AS
    SELECT
        id,
        convert_bytea_to_smallint_array(n) AS n,
        convert_bytea_to_real_array(r) AS r
    FROM bytea_tbl;

    -- verify that data in above table matches flattened input table exactly
    SELECT assert(
        i.n = o.n AND i.r = o.r,
        'output of array_to_bytea() does not convert back to flattened input'
    )
    FROM flat_array_input_tbl i JOIN array_output_tbl o USING (id);
!>)

m4_changequote(,)