| -- Unit-Testing checklist |
| -- 1. Make sure the function does the right thing on normal input(s). |
| -- 2. Make sure the function is non-destructive to the input(s). |
| -- 3. Make sure the function handles null values in svecs correctly. |
| -- 4. Make sure the function handles length-1 svecs correctly. |
| -- 5. Make sure the function handles null svecs properly. |
| -- 6. For functions that handle float8[] input(s), make sure the two |
| -- representations of null values are handled properly. |
| -- \timing |
| -- \i gp_svec.sql_in |
| set search_path to "$user",madlib,public; |
| select madlib.dmin(1000,1000.1); |
| dmin |
| ------ |
| 1000 |
| (1 row) |
| |
| select madlib.dmin(1000,NULL); |
| dmin |
| ------ |
| 1000 |
| (1 row) |
| |
| select madlib.dmin(NULL,1000); |
| dmin |
| ------ |
| 1000 |
| (1 row) |
| |
| select madlib.dmin(NULL,NULL); |
| dmin |
| ------ |
| |
| (1 row) |
| |
| select madlib.dmax(1000,1000.1); |
| dmax |
| -------- |
| 1000.1 |
| (1 row) |
| |
| select madlib.dmax(1000,NULL); |
| dmax |
| ------ |
| 1000 |
| (1 row) |
| |
| select madlib.dmax(NULL,1000); |
| dmax |
| ------ |
| 1000 |
| (1 row) |
| |
| select madlib.dmax(NULL,NULL); |
| dmax |
| ------ |
| |
| (1 row) |
| |
| drop table if exists madlib.test_pairs; |
| create table madlib.test_pairs( id int, a madlib.svec, b madlib.svec ) distributed by (id); |
| insert into madlib.test_pairs values |
| (0, '{1,100,1}:{5,0,5}', '{50,50,2}:{1,2,10}'), |
| (1, '{1,100,1}:{-5,0,-5}', '{50,50,2}:{-1,-2,-10}'); |
| insert into madlib.test_pairs values |
| -- (10, null, null), |
| (11, '{1}:{0}', '{1}:{1}'), |
| (12, '{1}:{5}', '{3}:{-8}'), |
| (13, '{1}:{0}', '{1}:{NULL}'), |
| (14, '{1,2,1}:{2,4,2}', '{2,1,1}:{0,3,5}'), |
| (15, '{1,2,1}:{2,4,2}', '{2,1,1}:{NULL,3,5}'); |
| select id, madlib.svec_count(a,b) from madlib.test_pairs where madlib.dimension(a) = madlib.dimension(b) order by id; |
| id | svec_count |
| ----+--------------------- |
| 0 | {1,100,1}:{6,1,6} |
| 1 | {1,100,1}:{-4,1,-4} |
| 11 | {1}:{1} |
| 13 | {1}:{0} |
| 14 | {1,1,1,1}:{2,4,5,3} |
| 15 | {1,1,1,1}:{2,4,5,3} |
| (6 rows) |
| |
| select id, madlib.svec_plus(a,b) from madlib.test_pairs order by id; |
| id | svec_plus |
| ----+---------------------------------- |
| 0 | {1,49,50,1,1}:{6,1,2,10,15} |
| 1 | {1,49,50,1,1}:{-6,-1,-2,-10,-15} |
| 11 | {1}:{1} |
| 12 | {3}:{-3} |
| 13 | {1}:{NVP} |
| 14 | {1,1,2}:{2,4,7} |
| 15 | {2,2}:{NVP,7} |
| (7 rows) |
| |
| select id, madlib.svec_plus(a,b) = madlib.svec_plus(b,a) from madlib.test_pairs order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 12 | t |
| 13 | t |
| 14 | t |
| 15 | t |
| (7 rows) |
| |
| select id, madlib.svec_mult(a,b) from madlib.test_pairs order by id; |
| id | svec_mult |
| ----+--------------------- |
| 0 | {1,100,1}:{5,0,50} |
| 1 | {1,100,1}:{5,-0,50} |
| 11 | {1}:{0} |
| 12 | {3}:{-40} |
| 13 | {1}:{NVP} |
| 14 | {2,1,1}:{0,12,10} |
| 15 | {2,1,1}:{NVP,12,10} |
| (7 rows) |
| |
| select id, madlib.svec_mult(a,b) = madlib.svec_mult(b,a) from madlib.test_pairs order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 12 | t |
| 13 | t |
| 14 | t |
| 15 | t |
| (7 rows) |
| |
| select id, madlib.svec_div(a,b) = madlib.svec_mult(a, madlib.svec_pow(b,(-1)::svec)) from madlib.test_pairs order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 12 | t |
| 13 | t |
| 14 | t |
| 15 | t |
| (7 rows) |
| |
| select id, madlib.svec_minus(a, b) = madlib.svec_plus(madlib.svec_mult((-1)::svec,b), a) from madlib.test_pairs order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 12 | t |
| 13 | t |
| 14 | t |
| 15 | t |
| (7 rows) |
| |
| select id, madlib.svec_pow(a,2::svec) = madlib.svec_mult(a,a) from madlib.test_pairs order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 12 | t |
| 13 | t |
| 14 | t |
| 15 | t |
| (7 rows) |
| |
| select id, madlib.dot(a,b) from madlib.test_pairs where madlib.dimension(a) = madlib.dimension(b) order by id; |
| id | dot |
| ----+----- |
| 0 | 55 |
| 1 | 55 |
| 11 | 0 |
| 13 | |
| 14 | 22 |
| 15 | |
| (6 rows) |
| |
| select id, madlib.dot(a,b) = madlib.dot(b,a) from madlib.test_pairs where madlib.dimension(a) = madlib.dimension(b) order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 13 | |
| 14 | t |
| 15 | |
| (6 rows) |
| |
| select id, madlib.dot(a,b::float8[]) = madlib.dot(b,a::float8[]) from madlib.test_pairs where madlib.dimension(a) = madlib.dimension(b) order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 13 | |
| 14 | t |
| 15 | |
| (6 rows) |
| |
| select id, madlib.dot(a::float8[],b) = madlib.dot(b::float8[],a) from madlib.test_pairs where madlib.dimension(a) = madlib.dimension(b) order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 13 | |
| 14 | t |
| 15 | |
| (6 rows) |
| |
| select id, madlib.dot(a::float8[],b::float8[]) = madlib.dot(b::float8[],a::float8[]) from madlib.test_pairs where madlib.dimension(a) = madlib.dimension(b) order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 13 | |
| 14 | t |
| 15 | |
| (6 rows) |
| |
| select id, madlib.l2norm(a), madlib.l2norm(a::float[]), madlib.l2norm(b), madlib.l2norm(b::float8[]) from madlib.test_pairs order by id; |
| id | l2norm | l2norm | l2norm | l2norm |
| ----+------------------+------------------+------------------+------------------ |
| 0 | 7.07106781186548 | 7.07106781186548 | 21.2132034355964 | 21.2132034355964 |
| 1 | 7.07106781186548 | 7.07106781186548 | 21.2132034355964 | 21.2132034355964 |
| 11 | 0 | 0 | 1 | 1 |
| 12 | 5 | 5 | 13.856406460551 | 13.856406460551 |
| 13 | 0 | 0 | | |
| 14 | 6.32455532033676 | 6.32455532033676 | 5.8309518948453 | 5.8309518948453 |
| 15 | 6.32455532033676 | 6.32455532033676 | | |
| (7 rows) |
| |
| select id, madlib.l1norm(a), madlib.l1norm(a::float[]), madlib.l1norm(b), madlib.l1norm(b::float8[]) from madlib.test_pairs order by id; |
| id | l1norm | l1norm | l1norm | l1norm |
| ----+--------+--------+--------+-------- |
| 0 | 10 | 10 | 170 | 170 |
| 1 | 10 | 10 | 170 | 170 |
| 11 | 0 | 0 | 1 | 1 |
| 12 | 5 | 5 | 24 | 24 |
| 13 | 0 | 0 | | |
| 14 | 12 | 12 | 8 | 8 |
| 15 | 12 | 12 | | |
| (7 rows) |
| |
| select madlib.svec_plus('{1,2,3}:{4,5,6}', 5::madlib.svec); |
| svec_plus |
| ------------------- |
| {1,2,3}:{9,10,11} |
| (1 row) |
| |
| select madlib.svec_plus(5::madlib.svec, '{1,2,3}:{4,5,6}'); |
| svec_plus |
| ------------------- |
| {1,2,3}:{9,10,11} |
| (1 row) |
| |
| select madlib.svec_plus(500::madlib.svec, '{1,2,3}:{4,null,6}'); |
| svec_plus |
| ----------------------- |
| {1,2,3}:{504,NVP,506} |
| (1 row) |
| |
| select madlib.svec_div(500::madlib.svec, '{1,2,3}:{4,null,6}'); |
| svec_div |
| ------------------------------------ |
| {1,2,3}:{125,NVP,83.3333333333333} |
| (1 row) |
| |
| select madlib.svec_div('{1,2,3}:{4,null,6}', 500::madlib.svec); |
| svec_div |
| --------------------------- |
| {1,2,3}:{0.008,NVP,0.012} |
| (1 row) |
| |
| -- Test operators between svec and float8[] |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec) %*% ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[]; |
| ?column? |
| ---------- |
| 260 |
| (1 row) |
| |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[] %*% ('{1,2,3,4}:{3,4,5,6}'::madlib.svec); |
| ?column? |
| ---------- |
| 260 |
| (1 row) |
| |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec) / ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[]; |
| ?column? |
| ---------- |
| {10}:{1} |
| (1 row) |
| |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[] / ('{1,2,3,4}:{3,4,5,6}'::madlib.svec); |
| ?column? |
| ---------- |
| {10}:{1} |
| (1 row) |
| |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec) * ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[]; |
| ?column? |
| ------------------------ |
| {1,2,3,4}:{9,16,25,36} |
| (1 row) |
| |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[] * ('{1,2,3,4}:{3,4,5,6}'::madlib.svec); |
| ?column? |
| ------------------------ |
| {1,2,3,4}:{9,16,25,36} |
| (1 row) |
| |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec) + ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[]; |
| ?column? |
| ----------------------- |
| {1,2,3,4}:{6,8,10,12} |
| (1 row) |
| |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[] + ('{1,2,3,4}:{3,4,5,6}'::madlib.svec); |
| ?column? |
| ----------------------- |
| {1,2,3,4}:{6,8,10,12} |
| (1 row) |
| |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec) - ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[]; |
| ?column? |
| ---------- |
| {10}:{0} |
| (1 row) |
| |
| select ('{1,2,3,4}:{3,4,5,6}'::madlib.svec)::float8[] - ('{1,2,3,4}:{3,4,5,6}'::madlib.svec); |
| ?column? |
| ---------- |
| {10}:{0} |
| (1 row) |
| |
| -- these should produce error messages |
| /* |
| select '{10000000000000000000}:{1}'::madlib.svec ; |
| select '{1,null,2}:{2,3,4}'::madlib.svec; |
| select madlib.svec_count('{1,1,1}:{3,4,5}', '{2,2}:{1,3}'); |
| select madlib.svec_plus('{1,1,1}:{3,4,5}', '{2,2}:{1,3}'); |
| select madlib.svec_minus('{1,1,1}:{3,4,5}', '{2,2}:{1,3}'); |
| select madlib.svec_mult('{1,1,1}:{3,4,5}', '{2,2}:{1,3}'); |
| select madlib.svec_div('{1,1,1}:{3,4,5}', '{2,2}:{1,3}'); |
| */ |
| select madlib.unnest('{1}:{5}'::madlib.svec); |
| unnest |
| -------- |
| 5 |
| (1 row) |
| |
| select madlib.unnest('{1,2,3,4}:{5,6,7,8}'::madlib.svec); |
| unnest |
| -------- |
| 5 |
| 6 |
| 6 |
| 7 |
| 7 |
| 7 |
| 8 |
| 8 |
| 8 |
| 8 |
| (10 rows) |
| |
| select madlib.unnest('{1,2,3,4}:{5,6,null,8}'::madlib.svec); |
| unnest |
| -------- |
| 5 |
| 6 |
| 6 |
| |
| |
| |
| 8 |
| 8 |
| 8 |
| 8 |
| (10 rows) |
| |
| select id, madlib.unnest(a),a from madlib.test_pairs where id >= 10 order by id; |
| id | unnest | a |
| ----+--------+----------------- |
| 11 | 0 | {1}:{0} |
| 12 | 5 | {1}:{5} |
| 13 | 0 | {1}:{0} |
| 14 | 4 | {1,2,1}:{2,4,2} |
| 14 | 2 | {1,2,1}:{2,4,2} |
| 14 | 2 | {1,2,1}:{2,4,2} |
| 14 | 4 | {1,2,1}:{2,4,2} |
| 15 | 2 | {1,2,1}:{2,4,2} |
| 15 | 4 | {1,2,1}:{2,4,2} |
| 15 | 2 | {1,2,1}:{2,4,2} |
| 15 | 4 | {1,2,1}:{2,4,2} |
| (11 rows) |
| |
| select madlib.vec_pivot('{1}:{5}', 2); |
| vec_pivot |
| ------------- |
| {1,1}:{5,2} |
| (1 row) |
| |
| select madlib.vec_pivot('{1}:{5}', 5); |
| vec_pivot |
| ----------- |
| {2}:{5} |
| (1 row) |
| |
| select madlib.vec_pivot('{1}:{5}', null); |
| vec_pivot |
| --------------- |
| {1,1}:{5,NVP} |
| (1 row) |
| |
| select madlib.vec_pivot('{1}:{null}', 5); |
| vec_pivot |
| --------------- |
| {1,1}:{NVP,5} |
| (1 row) |
| |
| select madlib.vec_pivot('{1}:{null}', null); |
| vec_pivot |
| ----------- |
| {2}:{NVP} |
| (1 row) |
| |
| select madlib.vec_pivot('{1,2,3,4}:{5,6,7,8}'::madlib.svec, 2); |
| vec_pivot |
| ------------------------- |
| {1,2,3,4,1}:{5,6,7,8,2} |
| (1 row) |
| |
| select id, madlib.vec_pivot(a, 5), a, madlib.vec_pivot(a,6), a, madlib.vec_pivot(a,2) from madlib.test_pairs order by id; |
| id | vec_pivot | a | vec_pivot | a | vec_pivot |
| ----+-------------------------+---------------------+-------------------------+---------------------+------------------------- |
| 0 | {1,100,2}:{5,0,5} | {1,100,1}:{5,0,5} | {1,100,1,1}:{5,0,5,6} | {1,100,1}:{5,0,5} | {1,100,1,1}:{5,0,5,2} |
| 1 | {1,100,1,1}:{-5,0,-5,5} | {1,100,1}:{-5,0,-5} | {1,100,1,1}:{-5,0,-5,6} | {1,100,1}:{-5,0,-5} | {1,100,1,1}:{-5,0,-5,2} |
| 11 | {1,1}:{0,5} | {1}:{0} | {1,1}:{0,6} | {1}:{0} | {1,1}:{0,2} |
| 12 | {2}:{5} | {1}:{5} | {1,1}:{5,6} | {1}:{5} | {1,1}:{5,2} |
| 13 | {1,1}:{0,5} | {1}:{0} | {1,1}:{0,6} | {1}:{0} | {1,1}:{0,2} |
| 14 | {1,2,1,1}:{2,4,2,5} | {1,2,1}:{2,4,2} | {1,2,1,1}:{2,4,2,6} | {1,2,1}:{2,4,2} | {1,2,2}:{2,4,2} |
| 15 | {1,2,1,1}:{2,4,2,5} | {1,2,1}:{2,4,2} | {1,2,1,1}:{2,4,2,6} | {1,2,1}:{2,4,2} | {1,2,2}:{2,4,2} |
| (7 rows) |
| |
| select id, madlib.vec_pivot(b, 5), b, madlib.vec_pivot(b,6), b, madlib.vec_pivot(b,null) from madlib.test_pairs order by id; |
| id | vec_pivot | b | vec_pivot | b | vec_pivot |
| ----+---------------------------+-----------------------+---------------------------+-----------------------+----------------------------- |
| 0 | {50,50,2,1}:{1,2,10,5} | {50,50,2}:{1,2,10} | {50,50,2,1}:{1,2,10,6} | {50,50,2}:{1,2,10} | {50,50,2,1}:{1,2,10,NVP} |
| 1 | {50,50,2,1}:{-1,-2,-10,5} | {50,50,2}:{-1,-2,-10} | {50,50,2,1}:{-1,-2,-10,6} | {50,50,2}:{-1,-2,-10} | {50,50,2,1}:{-1,-2,-10,NVP} |
| 11 | {1,1}:{1,5} | {1}:{1} | {1,1}:{1,6} | {1}:{1} | {1,1}:{1,NVP} |
| 12 | {3,1}:{-8,5} | {3}:{-8} | {3,1}:{-8,6} | {3}:{-8} | {3,1}:{-8,NVP} |
| 13 | {1,1}:{NVP,5} | {1}:{NVP} | {1,1}:{NVP,6} | {1}:{NVP} | {2}:{NVP} |
| 14 | {2,1,2}:{0,3,5} | {2,1,1}:{0,3,5} | {2,1,1,1}:{0,3,5,6} | {2,1,1}:{0,3,5} | {2,1,1,1}:{0,3,5,NVP} |
| 15 | {2,1,2}:{NVP,3,5} | {2,1,1}:{NVP,3,5} | {2,1,1,1}:{NVP,3,5,6} | {2,1,1}:{NVP,3,5} | {2,1,1,1}:{NVP,3,5,NVP} |
| (7 rows) |
| |
| select madlib.vec_sum('{1}:{-5}'::madlib.svec); |
| vec_sum |
| --------- |
| -5 |
| (1 row) |
| |
| select id, a, madlib.vec_sum(a), a, b, madlib.vec_sum(b), b from madlib.test_pairs order by id; |
| id | a | vec_sum | a | b | vec_sum | b |
| ----+---------------------+---------+---------------------+-----------------------+---------+----------------------- |
| 0 | {1,100,1}:{5,0,5} | 10 | {1,100,1}:{5,0,5} | {50,50,2}:{1,2,10} | 170 | {50,50,2}:{1,2,10} |
| 1 | {1,100,1}:{-5,0,-5} | -10 | {1,100,1}:{-5,0,-5} | {50,50,2}:{-1,-2,-10} | -170 | {50,50,2}:{-1,-2,-10} |
| 11 | {1}:{0} | 0 | {1}:{0} | {1}:{1} | 1 | {1}:{1} |
| 12 | {1}:{5} | 5 | {1}:{5} | {3}:{-8} | -24 | {3}:{-8} |
| 13 | {1}:{0} | 0 | {1}:{0} | {1}:{NVP} | | {1}:{NVP} |
| 14 | {1,2,1}:{2,4,2} | 12 | {1,2,1}:{2,4,2} | {2,1,1}:{0,3,5} | 8 | {2,1,1}:{0,3,5} |
| 15 | {1,2,1}:{2,4,2} | 12 | {1,2,1}:{2,4,2} | {2,1,1}:{NVP,3,5} | | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| select id, madlib.vec_sum(a) = madlib.vec_sum(a::float8[]) from madlib.test_pairs order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 12 | t |
| 13 | t |
| 14 | t |
| 15 | t |
| (7 rows) |
| |
| select id, madlib.vec_sum(b) = madlib.vec_sum(b::float8[]) from madlib.test_pairs order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 12 | t |
| 13 | |
| 14 | t |
| 15 | |
| (7 rows) |
| |
| select id, a, madlib.vec_median(a), a from madlib.test_pairs order by id; |
| id | a | vec_median | a |
| ----+---------------------+------------+--------------------- |
| 0 | {1,100,1}:{5,0,5} | 0 | {1,100,1}:{5,0,5} |
| 1 | {1,100,1}:{-5,0,-5} | 0 | {1,100,1}:{-5,0,-5} |
| 11 | {1}:{0} | 0 | {1}:{0} |
| 12 | {1}:{5} | 5 | {1}:{5} |
| 13 | {1}:{0} | 0 | {1}:{0} |
| 14 | {1,2,1}:{2,4,2} | 2 | {1,2,1}:{2,4,2} |
| 15 | {1,2,1}:{2,4,2} | 2 | {1,2,1}:{2,4,2} |
| (7 rows) |
| |
| select id, b, madlib.vec_median(b), b from madlib.test_pairs order by id; |
| id | b | vec_median | b |
| ----+-----------------------+------------+----------------------- |
| 0 | {50,50,2}:{1,2,10} | 2 | {50,50,2}:{1,2,10} |
| 1 | {50,50,2}:{-1,-2,-10} | -2 | {50,50,2}:{-1,-2,-10} |
| 11 | {1}:{1} | 1 | {1}:{1} |
| 12 | {3}:{-8} | -8 | {3}:{-8} |
| 13 | {1}:{NVP} | | {1}:{NVP} |
| 14 | {2,1,1}:{0,3,5} | 0 | {2,1,1}:{0,3,5} |
| 15 | {2,1,1}:{NVP,3,5} | | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| select id, madlib.vec_median(a) = madlib.vec_median(a::float8[]), |
| madlib.vec_median(b) = madlib.vec_median(b::float8[]) from madlib.test_pairs order by id; |
| id | ?column? | ?column? |
| ----+----------+---------- |
| 0 | t | t |
| 1 | t | t |
| 11 | t | t |
| 12 | t | t |
| 13 | t | |
| 14 | t | t |
| 15 | t | |
| (7 rows) |
| |
| select id, a, b, madlib.svec_concat(a,b), a, b from madlib.test_pairs order by id; |
| id | a | b | svec_concat | a | b |
| ----+---------------------+-----------------------+---------------------------------------+---------------------+----------------------- |
| 0 | {1,100,1}:{5,0,5} | {50,50,2}:{1,2,10} | {1,100,1,50,50,2}:{5,0,5,1,2,10} | {1,100,1}:{5,0,5} | {50,50,2}:{1,2,10} |
| 1 | {1,100,1}:{-5,0,-5} | {50,50,2}:{-1,-2,-10} | {1,100,1,50,50,2}:{-5,0,-5,-1,-2,-10} | {1,100,1}:{-5,0,-5} | {50,50,2}:{-1,-2,-10} |
| 11 | {1}:{0} | {1}:{1} | {1,1}:{0,1} | {1}:{0} | {1}:{1} |
| 12 | {1}:{5} | {3}:{-8} | {1,3}:{5,-8} | {1}:{5} | {3}:{-8} |
| 13 | {1}:{0} | {1}:{NVP} | {1,1}:{0,NVP} | {1}:{0} | {1}:{NVP} |
| 14 | {1,2,1}:{2,4,2} | {2,1,1}:{0,3,5} | {1,2,1,2,1,1}:{2,4,2,0,3,5} | {1,2,1}:{2,4,2} | {2,1,1}:{0,3,5} |
| 15 | {1,2,1}:{2,4,2} | {2,1,1}:{NVP,3,5} | {1,2,1,2,1,1}:{2,4,2,NVP,3,5} | {1,2,1}:{2,4,2} | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| select id, madlib.svec_concat_replicate(0, b), b from madlib.test_pairs order by id; |
| id | svec_concat_replicate | b |
| ----+-----------------------+----------------------- |
| 0 | {}:{} | {50,50,2}:{1,2,10} |
| 1 | {}:{} | {50,50,2}:{-1,-2,-10} |
| 11 | {}:{} | {1}:{1} |
| 12 | {}:{} | {3}:{-8} |
| 13 | {}:{} | {1}:{NVP} |
| 14 | {}:{} | {2,1,1}:{0,3,5} |
| 15 | {}:{} | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| select id, madlib.svec_concat_replicate(1, b) = b from madlib.test_pairs order by id; |
| id | ?column? |
| ----+---------- |
| 0 | t |
| 1 | t |
| 11 | t |
| 12 | t |
| 13 | t |
| 14 | t |
| 15 | t |
| (7 rows) |
| |
| select id, madlib.svec_concat_replicate(3, b), b from madlib.test_pairs order by id; |
| id | svec_concat_replicate | b |
| ----+-----------------------------------------------------------+----------------------- |
| 0 | {50,50,2,50,50,2,50,50,2}:{1,2,10,1,2,10,1,2,10} | {50,50,2}:{1,2,10} |
| 1 | {50,50,2,50,50,2,50,50,2}:{-1,-2,-10,-1,-2,-10,-1,-2,-10} | {50,50,2}:{-1,-2,-10} |
| 11 | {1,1,1}:{1,1,1} | {1}:{1} |
| 12 | {3,3,3}:{-8,-8,-8} | {3}:{-8} |
| 13 | {1,1,1}:{NVP,NVP,NVP} | {1}:{NVP} |
| 14 | {2,1,1,2,1,1,2,1,1}:{0,3,5,0,3,5,0,3,5} | {2,1,1}:{0,3,5} |
| 15 | {2,1,1,2,1,1,2,1,1}:{NVP,3,5,NVP,3,5,NVP,3,5} | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| -- select id, madlib.svec_concat_replicate(-2, b), b from madlib.test_pairs order by id; -- this should produce error message |
| select id, madlib.dimension(a), a, madlib.dimension(b), b from madlib.test_pairs order by id; |
| id | dimension | a | dimension | b |
| ----+-----------+---------------------+-----------+----------------------- |
| 0 | 102 | {1,100,1}:{5,0,5} | 102 | {50,50,2}:{1,2,10} |
| 1 | 102 | {1,100,1}:{-5,0,-5} | 102 | {50,50,2}:{-1,-2,-10} |
| 11 | 1 | {1}:{0} | 1 | {1}:{1} |
| 12 | 1 | {1}:{5} | 3 | {3}:{-8} |
| 13 | 1 | {1}:{0} | 1 | {1}:{NVP} |
| 14 | 4 | {1,2,1}:{2,4,2} | 4 | {2,1,1}:{0,3,5} |
| 15 | 4 | {1,2,1}:{2,4,2} | 4 | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| select madlib.svec_lapply('sqrt', null); |
| svec_lapply |
| ------------- |
| |
| (1 row) |
| |
| select id, madlib.svec_lapply('sqrt', madlib.svec_lapply('abs', a)), a from madlib.test_pairs order by id; |
| id | svec_lapply | a |
| ----+-------------------------------------------------+--------------------- |
| 0 | {1,100,1}:{2.23606797749979,0,2.23606797749979} | {1,100,1}:{5,0,5} |
| 1 | {1,100,1}:{2.23606797749979,0,2.23606797749979} | {1,100,1}:{-5,0,-5} |
| 11 | {1}:{0} | {1}:{0} |
| 12 | {1}:{2.23606797749979} | {1}:{5} |
| 13 | {1}:{0} | {1}:{0} |
| 14 | {1,2,1}:{1.4142135623731,2,1.4142135623731} | {1,2,1}:{2,4,2} |
| 15 | {1,2,1}:{1.4142135623731,2,1.4142135623731} | {1,2,1}:{2,4,2} |
| (7 rows) |
| |
| select id, madlib.svec_lapply('sqrt', madlib.svec_lapply('abs', b)), b from madlib.test_pairs order by id; |
| id | svec_lapply | b |
| ----+-------------------------------------------------+----------------------- |
| 0 | {50,50,2}:{1,1.4142135623731,3.16227766016838} | {50,50,2}:{1,2,10} |
| 1 | {50,50,2}:{1,1.4142135623731,3.16227766016838} | {50,50,2}:{-1,-2,-10} |
| 11 | {1}:{1} | {1}:{1} |
| 12 | {3}:{2.82842712474619} | {3}:{-8} |
| 13 | {1}:{NVP} | {1}:{NVP} |
| 14 | {2,1,1}:{0,1.73205080756888,2.23606797749979} | {2,1,1}:{0,3,5} |
| 15 | {2,1,1}:{NVP,1.73205080756888,2.23606797749979} | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| select madlib.svec_append(null::madlib.svec, 220::float8, 20::int8); |
| svec_append |
| ------------- |
| {20}:{220} |
| (1 row) |
| |
| select id, madlib.svec_append(a, 50, 100), a, madlib.svec_append(b, null, 50), b from madlib.test_pairs order by id; |
| id | svec_append | a | svec_append | b |
| ----+----------------------------+---------------------+------------------------------+----------------------- |
| 0 | {1,100,1,100}:{5,0,5,50} | {1,100,1}:{5,0,5} | {50,50,2,50}:{1,2,10,NVP} | {50,50,2}:{1,2,10} |
| 1 | {1,100,1,100}:{-5,0,-5,50} | {1,100,1}:{-5,0,-5} | {50,50,2,50}:{-1,-2,-10,NVP} | {50,50,2}:{-1,-2,-10} |
| 11 | {1,100}:{0,50} | {1}:{0} | {1,50}:{1,NVP} | {1}:{1} |
| 12 | {1,100}:{5,50} | {1}:{5} | {3,50}:{-8,NVP} | {3}:{-8} |
| 13 | {1,100}:{0,50} | {1}:{0} | {1,50}:{NVP,NVP} | {1}:{NVP} |
| 14 | {1,2,1,100}:{2,4,2,50} | {1,2,1}:{2,4,2} | {2,1,1,50}:{0,3,5,NVP} | {2,1,1}:{0,3,5} |
| 15 | {1,2,1,100}:{2,4,2,50} | {1,2,1}:{2,4,2} | {2,1,1,50}:{NVP,3,5,NVP} | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| select madlib.svec_proj(a,1), a, madlib.svec_proj(b,1), b from madlib.test_pairs order by id; |
| svec_proj | a | svec_proj | b |
| -----------+---------------------+-----------+----------------------- |
| 5 | {1,100,1}:{5,0,5} | 1 | {50,50,2}:{1,2,10} |
| -5 | {1,100,1}:{-5,0,-5} | -1 | {50,50,2}:{-1,-2,-10} |
| 0 | {1}:{0} | 1 | {1}:{1} |
| 5 | {1}:{5} | -8 | {3}:{-8} |
| 0 | {1}:{0} | | {1}:{NVP} |
| 2 | {1,2,1}:{2,4,2} | 0 | {2,1,1}:{0,3,5} |
| 2 | {1,2,1}:{2,4,2} | | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| -- select madlib.svec_proj(a,2), a, madlib.svec_proj(b,2), b from madlib.test_pairs order by id; -- this should result in an appropriate error message |
| select madlib.svec_subvec('{1,20,30,10,600,2}:{1,2,3,4,5,6}', 3,69); |
| svec_subvec |
| ------------------------ |
| {19,30,10,8}:{2,3,4,5} |
| (1 row) |
| |
| select madlib.svec_subvec('{1,20,30,10,600,2}:{1,2,3,4,5,6}', 69,3); |
| svec_subvec |
| ------------------------ |
| {8,10,30,19}:{5,4,3,2} |
| (1 row) |
| |
| select madlib.svec_subvec(a,2,4), a from madlib.test_pairs where madlib.dimension(a) >= 4 order by id; |
| svec_subvec | a |
| -------------+--------------------- |
| {3}:{0} | {1,100,1}:{5,0,5} |
| {3}:{0} | {1,100,1}:{-5,0,-5} |
| {2,1}:{4,2} | {1,2,1}:{2,4,2} |
| {2,1}:{4,2} | {1,2,1}:{2,4,2} |
| (4 rows) |
| |
| select madlib.svec_subvec(a,2,madlib.dimension(a)-1), a from madlib.test_pairs where madlib.dimension(a) >= 2 order by id; |
| svec_subvec | a |
| -------------+--------------------- |
| {100}:{0} | {1,100,1}:{5,0,5} |
| {100}:{0} | {1,100,1}:{-5,0,-5} |
| {2}:{4} | {1,2,1}:{2,4,2} |
| {2}:{4} | {1,2,1}:{2,4,2} |
| (4 rows) |
| |
| -- select madlib.svec_subvec(a,madlib.dimension(a)-1,0), a from madlib.test_pairs where madlib.dimension(a) >= 2 order by id; |
| select madlib.svec_reverse(a), a, madlib.svec_reverse(b), b from madlib.test_pairs order by id; |
| svec_reverse | a | svec_reverse | b |
| ---------------------+---------------------+-----------------------+----------------------- |
| {1,100,1}:{5,0,5} | {1,100,1}:{5,0,5} | {2,50,50}:{10,2,1} | {50,50,2}:{1,2,10} |
| {1,100,1}:{-5,0,-5} | {1,100,1}:{-5,0,-5} | {2,50,50}:{-10,-2,-1} | {50,50,2}:{-1,-2,-10} |
| {1}:{0} | {1}:{0} | {1}:{1} | {1}:{1} |
| {1}:{5} | {1}:{5} | {3}:{-8} | {3}:{-8} |
| {1}:{0} | {1}:{0} | {1}:{NVP} | {1}:{NVP} |
| {1,2,1}:{2,4,2} | {1,2,1}:{2,4,2} | {1,1,2}:{5,3,0} | {2,1,1}:{0,3,5} |
| {1,2,1}:{2,4,2} | {1,2,1}:{2,4,2} | {1,1,2}:{5,3,NVP} | {2,1,1}:{NVP,3,5} |
| (7 rows) |
| |
| select madlib.svec_subvec('{1,20,30,10,600,2}:{1,2,3,4,5,6}', 3,69) = |
| madlib.svec_reverse(madlib.svec_subvec('{1,20,30,10,600,2}:{1,2,3,4,5,6}', 69,3)); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select madlib.svec_change('{1,20,30,10,600,2}:{1,2,3,4,5,6}', 3, '{2,3}:{4,null}'); |
| svec_change |
| ------------------------------------------------ |
| {1,1,2,3,14,30,10,600,2}:{1,2,4,NVP,2,3,4,5,6} |
| (1 row) |
| |
| select madlib.svec_change(a,1,'{1}:{-50}'), a from madlib.test_pairs order by id; |
| svec_change | a |
| ----------------------+--------------------- |
| {1,100,1}:{-50,0,5} | {1,100,1}:{5,0,5} |
| {1,100,1}:{-50,0,-5} | {1,100,1}:{-5,0,-5} |
| {1}:{-50} | {1}:{0} |
| {1}:{-50} | {1}:{5} |
| {1}:{-50} | {1}:{0} |
| {1,2,1}:{-50,4,2} | {1,2,1}:{2,4,2} |
| {1,2,1}:{-50,4,2} | {1,2,1}:{2,4,2} |
| (7 rows) |
| |
| -- Test the multi-concatenation and show sizes compared with a normal array |
| drop table if exists corpus_proj; |
| psql:sql/gp_svec_test.sql:142: NOTICE: table "corpus_proj" does not exist, skipping |
| drop table if exists corpus_proj_array; |
| psql:sql/gp_svec_test.sql:143: NOTICE: table "corpus_proj_array" does not exist, skipping |
| create table corpus_proj as (select 10000 *|| ('{45,2,35,4,15,1}:{0,1,0,1,0,2}'::madlib.svec) result ) distributed randomly; |
| create table corpus_proj_array as (select result::float8[] from corpus_proj) distributed randomly; |
| -- Calculate on-disk size of sparse vector |
| select pg_size_pretty(pg_total_relation_size('corpus_proj')); |
| pg_size_pretty |
| ---------------- |
| 192 kB |
| (1 row) |
| |
| -- Calculate on-disk size of normal array |
| select pg_size_pretty(pg_total_relation_size('corpus_proj_array')); |
| pg_size_pretty |
| ---------------- |
| 288 kB |
| (1 row) |
| |
| -- Calculate L1 norm from sparse vector |
| select madlib.l1norm(result) from corpus_proj; |
| l1norm |
| -------- |
| 80000 |
| (1 row) |
| |
| -- Calculate L1 norm from float8[] |
| select madlib.l1norm(result) from corpus_proj_array; |
| l1norm |
| -------- |
| 80000 |
| (1 row) |
| |
| -- Calculate L2 norm from sparse vector |
| select madlib.l2norm(result) from corpus_proj; |
| l2norm |
| ------------------ |
| 316.227766016838 |
| (1 row) |
| |
| -- Calculate L2 norm from float8[] |
| select madlib.l2norm(result) from corpus_proj_array; |
| l2norm |
| ------------------ |
| 316.227766016838 |
| (1 row) |
| |
| drop table corpus_proj; |
| drop table corpus_proj_array; |
| -- -- Test the pivot operator |
| drop table if exists pivot_test; |
| psql:sql/gp_svec_test.sql:165: NOTICE: table "pivot_test" does not exist, skipping |
| create table pivot_test(a float8) distributed randomly; |
| insert into pivot_test values (0),(1),(0),(2),(3); |
| -- select madlib.array_agg(a) from pivot_test; |
| select madlib.l1norm(madlib.array_agg(a)) from pivot_test; |
| l1norm |
| -------- |
| 6 |
| (1 row) |
| |
| drop table if exists pivot_test; |
| -- Answer should be 5 |
| select madlib.vec_median(madlib.array_agg(a)) from (select generate_series(1,9) a) foo; |
| vec_median |
| ------------ |
| 5 |
| (1 row) |
| |
| -- Answer should be a 10-wide vector |
| -- select madlib.array_agg(a) from (select trunc(random()*10) a,generate_series(1,100000) order by a) foo; |
| -- Average is 4.50034, median is 5 |
| select madlib.vec_median('{9960,9926,10053,9993,10080,10050,9938,9941,10030,10029}:{1,9,8,7,6,5,4,3,2,0}'::madlib.svec); |
| vec_median |
| ------------ |
| 5 |
| (1 row) |
| |
| select madlib.vec_median('{9960,9926,10053,9993,10080,10050,9938,9941,10030,10029}:{1,9,8,7,6,5,4,3,2,0}'::madlib.svec::float8[]); |
| vec_median |
| ------------ |
| 5 |
| (1 row) |
| |
