src/ports/postgres/modules/linalg/test/matrix_ops.sql_in

m4_include(`SQLCommon.m4')

CREATE TABLE s(
    row_id INT8,
    col_id INT8,
    val INT4
)
m4_ifdef(`__GREENPLUM__',
`WITH (APPENDONLY=TRUE) DISTRIBUTED BY (row_id)');

INSERT INTO s VALUES(1, 1, 1) , (1, 2, 3) , (2, 1, 4) , (2, 2, 5) , (3, 1, 7) ,
                    (3, 2, 8), (3, 3, 0);
SELECT * FROM s order by row_id;

DROP TABLE IF EXISTS s_t;
SELECT matrix_trans('s', 'row=row_id, col=col_id, val=val', 's_t', 'row=row_num, col=col_num, val=val');
SELECT * from s_t order by row_num;

DROP TABLE IF EXISTS d;
SELECT matrix_densify('s', 'row=row_id, col=col_id, val=val', 'd');
SELECT * FROM d order by row_id;

DROP TABLE IF EXISTS d_t;
SELECT matrix_trans('d', 'row=row_id', 'd_t');
SELECT * FROM d_t order by row_id;

DROP TABLE IF EXISTS s;
SELECT matrix_sparsify('d', 'row=row_id', 's', 'row=row_id, col=col_id, val=val');
SELECT * from s order by row_id;

DROP TABLE IF EXISTS s2;
SELECT matrix_mult('s', 'row=row_id, col=col_id, val=val, trans=false',
                   's_t', 'row=row_num, col=col_num, val=val, trans=false', 's2');
SELECT * FROM s2;

DROP TABLE IF EXISTS d2;
SELECT matrix_mult('d', 'row=row_id', 'd_t', 'row=row_id', 'd2');
SELECT * FROM d2;

DROP TABLE IF EXISTS sd;
SELECT matrix_mult('s', 'row=row_id, col=col_id, val=val, trans=false',
                   'd', 'row=row_id, trans=true', 'sd');
SELECT * FROM sd;

DROP TABLE IF EXISTS ds;
SELECT matrix_mult('d', 'row=row_id', 's', 'row=row_id, col=col_id, val=val', 'ds');
SELECT * FROM ds;

DROP TABLE IF EXISTS dd;
SELECT matrix_max('d', 'row=row_id', 1, 'dd', true);
SELECT * from dd;

DROP TABLE IF EXISTS ss;
SELECT matrix_max('s', 'row=row_id, col=col_id, val=val', 1, 'ss', true);
SELECT * from ss;

DROP TABLE IF EXISTS dd;
SELECT matrix_max('d', 'row=row_id', 2, 'dd', true);
SELECT * from dd;

DROP TABLE IF EXISTS ss;
SELECT matrix_max('s', 'row=row_id, col=col_id, val=val', 2, 'ss', true);
SELECT * from ss;

DROP TABLE IF EXISTS dd;
SELECT matrix_min('d', 'row=row_id', 2, 'dd', true);
SELECT * from dd;

DROP TABLE IF EXISTS ss;
SELECT matrix_min('s', 'row=row_id, col=col_id, val=val', 2, 'ss', true);
SELECT * from ss;

DROP TABLE IF EXISTS dd;
SELECT matrix_min('d', 'row=row_id', 1, 'dd', true);
SELECT * from dd;

DROP TABLE IF EXISTS ss;
SELECT matrix_min('s', 'row=row_id, col=col_id, val=val', 1, 'ss', true);
SELECT * from ss;

SELECT matrix_extract_row('d', 'row=row_id', 3);
SELECT matrix_extract_col('d', 'row=row_id', 3);
SELECT matrix_extract_row('s', 'row=row_id, col=col_id, val=val', 1);
SELECT matrix_extract_col('s', 'row=row_id, col=col_id, val=val', 1);

DROP TABLE IF EXISTS d_d;
SELECT matrix_square('d', 'row=row_id', 'd_d');
SELECT * FROM d_d;

DROP TABLE IF EXISTS s3;
SELECT matrix_add('s', 'row=row_id, col=col_id, val=val',
                  's', 'row=row_id, col=col_id, val=val',
                  's3');
SELECT * FROM s3;


DROP TABLE IF EXISTS d3;
SELECT matrix_add('d', 'row=row_id', 'd', 'row=row_id', 'd3');
SELECT * FROM d3;

DROP TABLE IF EXISTS sd;
SELECT matrix_add('s', 'row=row_id, col=col_id, val=val', 'd', 'row=row_id', 'sd1');
SELECT * FROM sd1;

DROP TABLE IF EXISTS s3;
SELECT matrix_sub('s', 'row=row_id, col=col_id, val=val',
                  's', 'row=row_id, col=col_id, val=val',
                  's3');
SELECT * FROM s3;


DROP TABLE IF EXISTS d3;
SELECT matrix_sub('d', 'row=row_id', 'd', 'row=row_id', 'd3');
SELECT * FROM d3;

DROP TABLE IF EXISTS sd1;
SELECT matrix_sub('s', 'row=row_id, col=col_id, val=val', 'd', 'row=row_id', 'sd1');
SELECT * FROM sd1;

DROP TABLE IF EXISTS s3;
SELECT matrix_elem_mult('s', 'row=row_id, col=col_id, val=val',
                        's', 'row=row_id, col=col_id, val=val',
                        's3');
SELECT * FROM s3;


DROP TABLE IF EXISTS d3;
SELECT matrix_elem_mult('d', 'row=row_id', 'd', 'row=row_id', 'd3');
SELECT * FROM d3;

DROP TABLE IF EXISTS sd1;
SELECT matrix_elem_mult('s', 'row=row_id, col=col_id, val=val', 'd', 'row=row_id', 'sd1');
SELECT * FROM sd1;

-- sd1 should contain same number of rows as s
SELECT assert(n_rows = 7, 'bad matrix element-wise mult dense')
from (select count(*) as n_rows from sd1) t;

SELECT assert(n_rows = 3, 'bad matrix element-wise add dense')
from (select count(*) as n_rows from d3) t;

CREATE TABLE b(
    row_id INT4,
    col_id INT4,
    block INT4[]
)
m4_ifdef(`__GREENPLUM__',
`WITH (APPENDONLY=TRUE) DISTRIBUTED BY (row_id)');

INSERT INTO b VALUES(1, 1, array[[1,2], [3,4]]);
INSERT INTO b VALUES(2, 1, array[[5,6], [7,8]]);
INSERT INTO b VALUES(3, 1, array[[9,10]]);


SELECT matrix_mem_trans(array[[1,2,3], [4,5,6]]);
SELECT matrix_mem_mult(array[[1,2,3], [4,5,6]], array[[1,4],[2,5],[3,6]]);
SELECT matrix_mem_mult(array[[1,2,3], [4,5,6]], array[[1,2,3], [4,5,6]], true);

DROP TABLE if exists b_t;
SELECT matrix_block_trans('b', 'row=row_id, col=col_id, val=block', 'b_t', NULL);
SELECT * FROM b_t;

DROP TABLE if exists b2;
SELECT matrix_block_square('b', 'row=row_id, col=col_id, val=block', 'b2', NULL);
SELECT * FROM b2;

DROP TABLE if exists b2;
SELECT matrix_block_mult('b', 'row=row_id, col=col_id, val=block',
                         'b_t', 'row=row_id, col=col_id, val=block',
                         'b2', NULL);
SELECT * FROM b2;

DROP TABLE if exists d_b;
SELECT matrix_blockize('d', 'row=row_id', 2, 2, 'd_b');
SELECT * FROM d order by row_id;
SELECT * FROM d_b;

DROP TABLE if exists d_r;
SELECT matrix_unblockize('d_b', 'row=row_id', 'd_r', NULL);
SELECT * FROM d_r;

DROP TABLE IF EXISTS identity;
CREATE TABLE identity(row_id INTEGER, row_vec DOUBLE PRECISION[]);
INSERT INTO identity VALUES
  (1, array[1, 0, 0]), (2, array[0, 1, 0]), (3, array[0, 0, 1]);

SELECT assert(relative_error(result,
                             ARRAY[3, 3, 1,0,0, 0,1,0, 0,0,1]) < 1e-6,
              'incorrect result for inverse')
FROM (
        SELECT array_to_1d(__matrix_dense_inverse(3, row_id - 1, row_vec)) as result
        FROM identity
    ) q;


CREATE TABLE random_matrix(row_id INTEGER, row_vec DOUBLE PRECISION[]);
INSERT INTO random_matrix VALUES
  (1, array[2, 2, 0]), (2, array[15, 2, 0]), (3, array[0, 5, 2]);
SELECT assert(relative_error(result,
                             ARRAY[3, 3, -0.07692308,0.07692308,0,
                                    0.57692308, -0.07692308,0,
                                    -1.44230769,0.19230769,0.5]) < 1e-6,
              'incorrect result for inverse')
FROM (
        SELECT array_to_1d(__matrix_dense_inverse(3, row_id - 1, row_vec)) as result
        FROM random_matrix
    ) q;

CREATE TABLE random_sparse_matrix(row_id INTEGER, col_id INTEGER, val DOUBLE PRECISION);
INSERT INTO random_sparse_matrix VALUES
  (1, 1, 2), (1, 2, 2), (2, 1, 15), (2, 2, 2), (3, 2, 5), (3, 3, 2);
SELECT assert(relative_error(result,
                             ARRAY[3, 3, -0.07692308,0.07692308,0, 0.57692308,-0.07692308,0, -1.44230769,0.19230769,0.5]) < 1e-6,
              'incorrect result for inverse')
FROM (
        SELECT array_to_1d(__matrix_sparse_inverse(3, 3, row_id - 1, col_id - 1, val)) as result
        FROM random_sparse_matrix
    ) q;