| 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; |