| \c hdfs |
| |
| -- |
| -- ARRAYS |
| -- |
| |
| CREATE TABLE arrtest ( |
| a int2[], |
| b int4[][][], |
| c name[], |
| d text[][], |
| e float8[], |
| f char(5)[], |
| g varchar(5)[] |
| ) DISTRIBUTED RANDOMLY; |
| |
| -- |
| -- only the 'e' array is 0-based, the others are 1-based. |
| -- |
| |
| INSERT INTO arrtest (a[1:5], b[1:1][1:2][1:2], c, d, f, g) |
| VALUES ('{1,2,3,4,5}', '{{{0,0},{1,2}}}', '{}', '{}', '{}', '{}'); |
| |
| INSERT INTO arrtest (f) |
| VALUES ('{"too long"}'); |
| |
| INSERT INTO arrtest (a, b[1:2][1:2], c, d, e, f, g) |
| VALUES ('{11,12,23}', '{{3,4},{4,5}}', '{"foobar"}', |
| '{{"elt1", "elt2"}}', '{"3.4", "6.7"}', |
| '{"abc","abcde"}', '{"abc","abcde"}'); |
| |
| INSERT INTO arrtest (a, b[1:2], c, d[1:2]) |
| VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}'); |
| |
| |
| SELECT * FROM arrtest ORDER BY 1,2,3,4; |
| |
| SELECT arrtest.a[1], |
| arrtest.b[1][1][1], |
| arrtest.c[1], |
| arrtest.d[1][1], |
| arrtest.e[0] |
| FROM arrtest ORDER BY 1,2,3,4; |
| |
| SELECT a[1], b[1][1][1], c[1], d[1][1], e[0] |
| FROM arrtest ORDER BY 1,2,3,4; |
| |
| SELECT a[1:3], |
| b[1:1][1:2][1:2], |
| c[1:2], |
| d[1:1][1:2] |
| FROM arrtest ORDER BY 1,2,3,4; |
| |
| -- array_ndims exists in postgres, but not in gp |
| -- SELECT array_ndims(a) AS a,array_ndims(b) AS b,array_ndims(c) AS c |
| -- FROM arrtest; |
| |
| SELECT array_dims(a) AS a,array_dims(b) AS b,array_dims(c) AS c |
| FROM arrtest ORDER BY 1,2; |
| |
| -- returns nothing |
| SELECT * |
| FROM arrtest |
| WHERE a[1] < 5 and |
| c = '{"foobar"}'::_name ORDER BY 1,2,3,4; |
| |
| SELECT a,b,c FROM arrtest ORDER BY 1,2,3; |
| |
| SELECT a[1:3], |
| b[1:1][1:2][1:2], |
| c[1:2], |
| d[1:1][2:2] |
| FROM arrtest ORDER BY 1,2,3,4; |
| |
| INSERT INTO arrtest(a) VALUES('{1,null,3}'); |
| SELECT a,b,c FROM arrtest ORDER BY 1,2,3; |
| |
| -- |
| -- test array extension |
| -- |
| CREATE TEMP TABLE arrtest1 (i int[], t text[]) DISTRIBUTED RANDOMLY; |
| insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']); |
| select * from arrtest1; |
| |
| -- |
| -- array expressions and operators |
| -- |
| |
| -- table creation and INSERTs |
| CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]) DISTRIBUTED RANDOMLY; |
| INSERT INTO arrtest2 VALUES( |
| ARRAY[[[113,142],[1,147]]], |
| ARRAY[1.1,1.2,1.3]::float8[], |
| ARRAY[1.1,1.2,1.3], |
| ARRAY[[['aaa','aab'],['aba','abb'],['aca','acb']],[['baa','bab'],['bba','bbb'],['bca','bcb']]], |
| ARRAY['19620326','19931223','19970117']::timestamp[] |
| ); |
| |
| -- some more test data |
| CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8) DISTRIBUTED RANDOMLY; |
| insert into arrtest_f values(1,'cat1',1.21); |
| insert into arrtest_f values(2,'cat1',1.24); |
| insert into arrtest_f values(3,'cat1',1.18); |
| insert into arrtest_f values(4,'cat1',1.26); |
| insert into arrtest_f values(5,'cat1',1.15); |
| insert into arrtest_f values(6,'cat2',1.15); |
| insert into arrtest_f values(7,'cat2',1.26); |
| insert into arrtest_f values(8,'cat2',1.32); |
| insert into arrtest_f values(9,'cat2',1.30); |
| |
| CREATE TEMP TABLE arrtest_i (f0 int, f1 text, f2 int) DISTRIBUTED RANDOMLY; |
| insert into arrtest_i values(1,'cat1',21); |
| insert into arrtest_i values(2,'cat1',24); |
| insert into arrtest_i values(3,'cat1',18); |
| insert into arrtest_i values(4,'cat1',26); |
| insert into arrtest_i values(5,'cat1',15); |
| insert into arrtest_i values(6,'cat2',15); |
| insert into arrtest_i values(7,'cat2',26); |
| insert into arrtest_i values(8,'cat2',32); |
| insert into arrtest_i values(9,'cat2',30); |
| |
| -- expressions |
| SELECT t.f[1][3][1] AS "131", t.f[2][2][1] AS "221" FROM ( |
| SELECT ARRAY[[[111,112],[121,122],[131,132]],[[211,212],[221,122],[231,232]]] AS f |
| ) AS t; |
| SELECT ARRAY[[[[[['hello'],['world']]]]]]; |
| SELECT ARRAY[ARRAY['hello'],ARRAY['world']]; |
| SELECT ARRAY(select f2 from arrtest_f order by f2) AS "ARRAY" ORDER BY 1; -- MPP-11853 |
| -- check no merge on Motion |
| EXPLAIN SELECT ARRAY(select f2 from arrtest_f) AS "ARRAY"; |
| |
| -- with nulls |
| SELECT '{1,null,3}'::int[]; |
| SELECT ARRAY[1,NULL,3]; |
| |
| -- functions |
| SELECT array_append(array[42], 6) AS "{42,6}"; |
| SELECT array_prepend(6, array[42]) AS "{6,42}"; |
| SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}"; |
| SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}"; |
| SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}"; |
| |
| -- operators |
| SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]]; |
| SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE"; |
| SELECT ARRAY[1,2] || 3 AS "{1,2,3}"; |
| SELECT 0 || ARRAY[1,2] AS "{0,1,2}"; |
| SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}"; |
| SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY"; |
| SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}"; |
| SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}"; |
| SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}"; |
| |
| SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE i && '{17}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE i @> '{32,17}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE i && '{32,17}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; |
| |
| SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; |
| SELECT * FROM array_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno; |
| |
| -- array casts |
| SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}"; |
| SELECT ARRAY[1,2,3]::text[]::int[]::float8[] is of (float8[]) as "TRUE"; |
| SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}"; |
| SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] is of (varchar[]) as "TRUE"; |
| SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}"; |
| |
| -- scalar op any/all (array) |
| select 33 = any ('{1,2,3}'); |
| select 33 = any ('{1,2,33}'); |
| select 33 = all ('{1,2,33}'); |
| select 33 >= all ('{1,2,33}'); |
| -- boundary cases |
| select null::int >= all ('{1,2,33}'); |
| select null::int >= all ('{}'); |
| select null::int >= any ('{}'); |
| -- cross-datatype |
| select 33.4 = any (array[1,2,3]); |
| select 33.4 > all (array[1,2,3]); |
| -- errors |
| select 33 * any ('{1,2,3}'); |
| select 33 * any (44); |
| -- nulls |
| select 33 = any (null::int[]); |
| select null::int = any ('{1,2,3}'); |
| -- select 33 = any ('{1,null,3}'); -- MPP: 11852 |
| select 33 = any ('{1,null,33}'); |
| select 33 = all (null::int[]); |
| select null::int = all ('{1,2,3}'); |
| select 33 = all ('{1,null,3}'); |
| select 33 = all ('{33,null,33}'); |
| |
| -- test indexes on arrays |
| create temp table arr_tbl (f1 int[]) DISTRIBUTED BY (f1); |
| insert into arr_tbl values ('{1,2,3}'); |
| insert into arr_tbl values ('{1,2}'); |
| insert into arr_tbl values ('{2,3,4}'); |
| insert into arr_tbl values ('{1,5,3}'); |
| insert into arr_tbl values ('{1,2,10}'); |
| |
| set enable_seqscan to off; |
| set enable_bitmapscan to off; |
| select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}' ORDER BY 1; |
| select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}' ORDER BY 1; |
| -- note: if above selects don't produce the expected tuple order, |
| -- then you didn't get an indexscan plan, and something is busted. |
| reset enable_seqscan; |
| reset enable_bitmapscan; |
| |
| -- test [not] (like|ilike) (any|all) (...) |
| select 'foo' like any (array['%a', '%o']); -- t |
| select 'foo' like any (array['%a', '%b']); -- f |
| select 'foo' like all (array['f%', '%o']); -- t |
| select 'foo' like all (array['f%', '%b']); -- f |
| select 'foo' not like any (array['%a', '%b']); -- t |
| select 'foo' not like all (array['%a', '%o']); -- f |
| select 'foo' ilike any (array['%A', '%O']); -- t |
| select 'foo' ilike all (array['F%', '%O']); -- t |
| |
| -- |
| -- General array parser tests |
| -- |
| |
| -- none of the following should be accepted |
| select '{{1,{2}},{2,3}}'::text[]; |
| select '{{},{}}'::text[]; |
| select E'{{1,2},\\{2,3}}'::text[]; |
| select '{{"1 2" x},{3}}'::text[]; |
| select '{}}'::text[]; |
| select '{ }}'::text[]; |
| -- select array[]; -- MPP-11851 |
| -- none of the above should be accepted |
| |
| -- all of the following should be accepted |
| select '{}'::text[]; |
| select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[]; |
| select '{0 second ,0 second}'::interval[]; |
| select '{ { "," } , { 3 } }'::text[]; |
| select ' { { " 0 second " , 0 second } }'::text[]; |
| select '{ |
| 0 second, |
| @ 1 hour @ 42 minutes @ 20 seconds |
| }'::interval[]; |
| -- select array[]::text[]; -- MPP-11851 |
| select '[0:1]={1.1,2.2}'::float8[]; |
| -- all of the above should be accepted |
| |
| -- tests for array aggregates |
| CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]) DISTRIBUTED RANDOMLY; |
| |
| INSERT INTO arraggtest (f1, f2, f3) VALUES |
| ('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}'); |
| INSERT INTO arraggtest (f1, f2, f3) VALUES |
| ('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}'); |
| SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; |
| |
| INSERT INTO arraggtest (f1, f2, f3) VALUES |
| ('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}'); |
| SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; |
| |
| INSERT INTO arraggtest (f1, f2, f3) VALUES |
| ('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}'); |
| SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; |
| |
| INSERT INTO arraggtest (f1, f2, f3) VALUES |
| ('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL); |
| SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; |
| |
| INSERT INTO arraggtest (f1, f2, f3) VALUES |
| ('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}'); |
| SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; |