YQL-19206 Move contrib/ydb/library/yql/tests/postgresql -> yql/essentials/tests/postgresql

commit_hash:46fdf59714b20cf2b61233a06e58365227d3c8b2

1 files changed, 771 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/cases/arrays.out b/yql/essentials/tests/postgresql/cases/arrays.out
new file mode 100644
index 0000000000..76e7824f00
--- /dev/null
+++ b/yql/essentials/tests/postgresql/cases/arrays.out
@@ -0,0 +1,771 @@
+--
+-- ARRAYS
+--
+CREATE TABLE arrtest (
+	a 			int2[],
+	b 			int4[][][],
+	c 			name[],
+	d			text[][],
+	e 			float8[],
+	f			char(5)[],
+	g			varchar(5)[]
+);
+-- test mixed slice/scalar subscripting
+select '{{1,2,3},{4,5,6},{7,8,9}}'::int[];
+           int4            
+---------------------------
+ {{1,2,3},{4,5,6},{7,8,9}}
+(1 row)
+
+select '[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[];
+                 int4                 
+--------------------------------------
+ [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}
+(1 row)
+
+-- test slices with empty lower and/or upper index
+CREATE TEMP TABLE arrtest_s (
+  a       int2[],
+  b       int2[][]
+);
+INSERT INTO arrtest_s VALUES(NULL, NULL);
+--
+-- test array extension
+--
+CREATE TEMP TABLE arrtest1 (i int[], t text[]);
+--
+-- array expressions and operators
+--
+-- table creation and INSERTs
+CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]);
+-- some more test data
+CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8);
+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);
+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);
+SELECT ARRAY[[[[[['hello'],['world']]]]]];
+           array           
+---------------------------
+ {{{{{{hello},{world}}}}}}
+(1 row)
+
+SELECT ARRAY[ARRAY['hello'],ARRAY['world']];
+       array       
+-------------------
+ {{hello},{world}}
+(1 row)
+
+-- with nulls
+SELECT '{1,null,3}'::int[];
+    int4    
+------------
+ {1,NULL,3}
+(1 row)
+
+SELECT ARRAY[1,NULL,3];
+   array    
+------------
+ {1,NULL,3}
+(1 row)
+
+SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
+ FALSE 
+-------
+ f
+(1 row)
+
+-- array casts
+SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}";
+ {1,2,3} 
+---------
+ {1,2,3}
+(1 row)
+
+SELECT pg_typeof(ARRAY[1,2,3]::text[]::int[]::float8[]) AS "double precision[]";
+ double precision[] 
+--------------------
+ double precision[]
+(1 row)
+
+SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}";
+ {{a,bc},{def,hijk}} 
+---------------------
+ {{a,bc},{def,hijk}}
+(1 row)
+
+SELECT pg_typeof(ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[]) AS "character varying[]";
+ character varying[] 
+---------------------
+ character varying[]
+(1 row)
+
+SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}";
+ {{{{{{a,bb,ccc}}}}}} 
+----------------------
+ {{{{{{a,bb,ccc}}}}}}
+(1 row)
+
+SELECT NULL::text[]::int[] AS "NULL";
+ NULL 
+------
+ 
+(1 row)
+
+-- scalar op any/all (array)
+select 33 = any ('{1,2,3}');
+ ?column? 
+----------
+ f
+(1 row)
+
+select 33 = any ('{1,2,33}');
+ ?column? 
+----------
+ t
+(1 row)
+
+select 33 = all ('{1,2,33}');
+ ?column? 
+----------
+ f
+(1 row)
+
+select 33 >= all ('{1,2,33}');
+ ?column? 
+----------
+ t
+(1 row)
+
+-- boundary cases
+select null::int >= all ('{1,2,33}');
+ ?column? 
+----------
+ 
+(1 row)
+
+select null::int >= all ('{}');
+ ?column? 
+----------
+ t
+(1 row)
+
+select null::int >= any ('{}');
+ ?column? 
+----------
+ f
+(1 row)
+
+-- cross-datatype
+select 33.4 = any (array[1,2,3]);
+ ?column? 
+----------
+ f
+(1 row)
+
+select 33.4 > all (array[1,2,3]);
+ ?column? 
+----------
+ t
+(1 row)
+
+-- nulls
+select 33 = any (null::int[]);
+ ?column? 
+----------
+ 
+(1 row)
+
+select null::int = any ('{1,2,3}');
+ ?column? 
+----------
+ 
+(1 row)
+
+select 33 = any ('{1,null,3}');
+ ?column? 
+----------
+ 
+(1 row)
+
+select 33 = any ('{1,null,33}');
+ ?column? 
+----------
+ t
+(1 row)
+
+select 33 = all (null::int[]);
+ ?column? 
+----------
+ 
+(1 row)
+
+select null::int = all ('{1,2,3}');
+ ?column? 
+----------
+ 
+(1 row)
+
+select 33 = all ('{1,null,3}');
+ ?column? 
+----------
+ f
+(1 row)
+
+select 33 = all ('{33,null,33}');
+ ?column? 
+----------
+ 
+(1 row)
+
+-- nulls later in the bitmap
+SELECT -1 != ALL(ARRAY(SELECT NULLIF(g.i, 900) FROM generate_series(1,1000) g(i)));
+ ?column? 
+----------
+ 
+(1 row)
+
+-- test indexes on arrays
+create temp table arr_tbl (f1 int[] unique);
+-- test ON CONFLICT DO UPDATE with arrays
+create temp table arr_pk_tbl (pk int4 primary key, f1 int[]);
+-- test [not] (like|ilike) (any|all) (...)
+select 'foo' like any (array['%a', '%o']); -- t
+ ?column? 
+----------
+ t
+(1 row)
+
+select 'foo' like any (array['%a', '%b']); -- f
+ ?column? 
+----------
+ f
+(1 row)
+
+select 'foo' like all (array['f%', '%o']); -- t
+ ?column? 
+----------
+ t
+(1 row)
+
+select 'foo' like all (array['f%', '%b']); -- f
+ ?column? 
+----------
+ f
+(1 row)
+
+select 'foo' not like any (array['%a', '%b']); -- t
+ ?column? 
+----------
+ t
+(1 row)
+
+select 'foo' not like all (array['%a', '%o']); -- f
+ ?column? 
+----------
+ f
+(1 row)
+
+select 'foo' ilike any (array['%A', '%O']); -- t
+ ?column? 
+----------
+ t
+(1 row)
+
+select 'foo' ilike all (array['F%', '%O']); -- t
+ ?column? 
+----------
+ t
+(1 row)
+
+--
+-- General array parser tests
+--
+-- none of the following should be accepted
+select '{{1,{2}},{2,3}}'::text[];
+ERROR:  malformed array literal: "{{1,{2}},{2,3}}"
+LINE 1: select '{{1,{2}},{2,3}}'::text[];
+               ^
+DETAIL:  Unexpected "{" character.
+select '{{},{}}'::text[];
+ERROR:  malformed array literal: "{{},{}}"
+LINE 1: select '{{},{}}'::text[];
+               ^
+DETAIL:  Unexpected "}" character.
+select E'{{1,2},\\{2,3}}'::text[];
+ERROR:  malformed array literal: "{{1,2},\{2,3}}"
+LINE 1: select E'{{1,2},\\{2,3}}'::text[];
+               ^
+DETAIL:  Unexpected "\" character.
+select '{{"1 2" x},{3}}'::text[];
+ERROR:  malformed array literal: "{{"1 2" x},{3}}"
+LINE 1: select '{{"1 2" x},{3}}'::text[];
+               ^
+DETAIL:  Unexpected array element.
+select '{}}'::text[];
+ERROR:  malformed array literal: "{}}"
+LINE 1: select '{}}'::text[];
+               ^
+DETAIL:  Junk after closing right brace.
+select '{ }}'::text[];
+ERROR:  malformed array literal: "{ }}"
+LINE 1: select '{ }}'::text[];
+               ^
+DETAIL:  Junk after closing right brace.
+-- none of the above should be accepted
+-- all of the following should be accepted
+select '{}'::text[];
+ text 
+------
+ {}
+(1 row)
+
+select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
+                     text                      
+-----------------------------------------------
+ {{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}
+(1 row)
+
+select '{ { "," } , { 3 } }'::text[];
+    text     
+-------------
+ {{","},{3}}
+(1 row)
+
+select '  {   {  "  0 second  "   ,  0 second  }   }'::text[];
+             text              
+-------------------------------
+ {{"  0 second  ","0 second"}}
+(1 row)
+
+select '[0:1]={1.1,2.2}'::float8[];
+     float8      
+-----------------
+ [0:1]={1.1,2.2}
+(1 row)
+
+-- all of the above should be accepted
+-- tests for array aggregates
+CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]);
+create table comptable (c1 comptype, c2 comptype[]);
+drop table comptable;
+select string_to_array('1|2|3', '|');
+ string_to_array 
+-----------------
+ {1,2,3}
+(1 row)
+
+select string_to_array('1|2|3|', '|');
+ string_to_array 
+-----------------
+ {1,2,3,""}
+(1 row)
+
+select string_to_array('1||2|3||', '||');
+ string_to_array 
+-----------------
+ {1,2|3,""}
+(1 row)
+
+select string_to_array('1|2|3', '');
+ string_to_array 
+-----------------
+ {1|2|3}
+(1 row)
+
+select string_to_array('', '|');
+ string_to_array 
+-----------------
+ {}
+(1 row)
+
+select string_to_array('1|2|3', NULL);
+ string_to_array 
+-----------------
+ {1,|,2,|,3}
+(1 row)
+
+select string_to_array(NULL, '|') IS NULL;
+ ?column? 
+----------
+ t
+(1 row)
+
+select string_to_array('abc', '');
+ string_to_array 
+-----------------
+ {abc}
+(1 row)
+
+select string_to_array('abc', '', 'abc');
+ string_to_array 
+-----------------
+ {NULL}
+(1 row)
+
+select string_to_array('abc', ',');
+ string_to_array 
+-----------------
+ {abc}
+(1 row)
+
+select string_to_array('abc', ',', 'abc');
+ string_to_array 
+-----------------
+ {NULL}
+(1 row)
+
+select string_to_array('1,2,3,4,,6', ',');
+ string_to_array 
+-----------------
+ {1,2,3,4,"",6}
+(1 row)
+
+select string_to_array('1,2,3,4,,6', ',', '');
+ string_to_array  
+------------------
+ {1,2,3,4,NULL,6}
+(1 row)
+
+select string_to_array('1,2,3,4,*,6', ',', '*');
+ string_to_array  
+------------------
+ {1,2,3,4,NULL,6}
+(1 row)
+
+select v, v is null as "is null" from string_to_table('1|2|3', '|') g(v);
+ v | is null 
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('1|2|3|', '|') g(v);
+ v | is null 
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+   | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('1||2|3||', '||') g(v);
+  v  | is null 
+-----+---------
+ 1   | f
+ 2|3 | f
+     | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('1|2|3', '') g(v);
+   v   | is null 
+-------+---------
+ 1|2|3 | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('', '|') g(v);
+ v | is null 
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('1|2|3', NULL) g(v);
+ v | is null 
+---+---------
+ 1 | f
+ | | f
+ 2 | f
+ | | f
+ 3 | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '|') g(v);
+ v | is null 
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('abc', '') g(v);
+  v  | is null 
+-----+---------
+ abc | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('abc', '', 'abc') g(v);
+ v | is null 
+---+---------
+   | t
+(1 row)
+
+select v, v is null as "is null" from string_to_table('abc', ',') g(v);
+  v  | is null 
+-----+---------
+ abc | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('abc', ',', 'abc') g(v);
+ v | is null 
+---+---------
+   | t
+(1 row)
+
+select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',') g(v);
+ v | is null 
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+ 4 | f
+   | f
+ 6 | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',', '') g(v);
+ v | is null 
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+ 4 | f
+   | t
+ 6 | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('1,2,3,4,*,6', ',', '*') g(v);
+ v | is null 
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+ 4 | f
+   | t
+ 6 | f
+(6 rows)
+
+select array_to_string(NULL::int4[], ',') IS NULL;
+ ?column? 
+----------
+ t
+(1 row)
+
+select array_to_string('{}'::int4[], ',');
+ array_to_string 
+-----------------
+ 
+(1 row)
+
+select array_to_string(array[1,2,3,4,NULL,6], ',');
+ array_to_string 
+-----------------
+ 1,2,3,4,6
+(1 row)
+
+select array_to_string(array[1,2,3,4,NULL,6], ',', '*');
+ array_to_string 
+-----------------
+ 1,2,3,4,*,6
+(1 row)
+
+select array_to_string(array[1,2,3,4,NULL,6], NULL);
+ array_to_string 
+-----------------
+ 
+(1 row)
+
+select array_to_string(array[1,2,3,4,NULL,6], ',', NULL);
+ array_to_string 
+-----------------
+ 1,2,3,4,6
+(1 row)
+
+select array_to_string(string_to_array('1|2|3', '|'), '|');
+ array_to_string 
+-----------------
+ 1|2|3
+(1 row)
+
+select array_length(array[1,2,3], 1);
+ array_length 
+--------------
+            3
+(1 row)
+
+select array_length(array[[1,2,3], [4,5,6]], 0);
+ array_length 
+--------------
+             
+(1 row)
+
+select array_length(array[[1,2,3], [4,5,6]], 1);
+ array_length 
+--------------
+            2
+(1 row)
+
+select array_length(array[[1,2,3], [4,5,6]], 2);
+ array_length 
+--------------
+            3
+(1 row)
+
+select array_length(array[[1,2,3], [4,5,6]], 3);
+ array_length 
+--------------
+             
+(1 row)
+
+select cardinality(NULL::int[]);
+ cardinality 
+-------------
+            
+(1 row)
+
+select cardinality('{}'::int[]);
+ cardinality 
+-------------
+           0
+(1 row)
+
+select cardinality(array[1,2,3]);
+ cardinality 
+-------------
+           3
+(1 row)
+
+select cardinality('[2:4]={5,6,7}'::int[]);
+ cardinality 
+-------------
+           3
+(1 row)
+
+select cardinality('{{1,2}}'::int[]);
+ cardinality 
+-------------
+           2
+(1 row)
+
+select cardinality('{{1,2},{3,4},{5,6}}'::int[]);
+ cardinality 
+-------------
+           6
+(1 row)
+
+select cardinality('{{{1,9},{5,6}},{{2,3},{3,4}}}'::int[]);
+ cardinality 
+-------------
+           8
+(1 row)
+
+select array_agg(unique1) from tenk1 where unique1 < -15;
+ array_agg 
+-----------
+ 
+(1 row)
+
+-- array_agg(anyarray)
+select array_agg(ar)
+  from (values ('{1,2}'::int[]), ('{3,4}'::int[])) v(ar);
+   array_agg   
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+select array_agg(ar)
+  from (select array_agg(array[i, i+1, i-1])
+        from generate_series(1,2) a(i)) b(ar);
+      array_agg      
+---------------------
+ {{{1,2,0},{2,3,1}}}
+(1 row)
+
+select array_agg(array[i+1.2, i+1.3, i+1.4]) from generate_series(1,3) g(i);
+                  array_agg                  
+---------------------------------------------
+ {{2.2,2.3,2.4},{3.2,3.3,3.4},{4.2,4.3,4.4}}
+(1 row)
+
+select array_agg(array['Hello', i::text]) from generate_series(9,11) g(i);
+             array_agg             
+-----------------------------------
+ {{Hello,9},{Hello,10},{Hello,11}}
+(1 row)
+
+select array_agg(array[i, nullif(i, 3), i+1]) from generate_series(1,4) g(i);
+              array_agg               
+--------------------------------------
+ {{1,1,2},{2,2,3},{3,NULL,4},{4,4,5}}
+(1 row)
+
+-- errors
+select array_agg('{}'::int[]) from generate_series(1,2);
+ERROR:  cannot accumulate empty arrays
+select array_agg(null::int[]) from generate_series(1,2);
+ERROR:  cannot accumulate null arrays
+select array_agg(ar)
+  from (values ('{1,2}'::int[]), ('{3}'::int[])) v(ar);
+ERROR:  cannot accumulate arrays of different dimensionality
+select * from unnest(array[1,2,3]);
+ unnest 
+--------
+      1
+      2
+      3
+(3 rows)
+
+-- array(select array-value ...)
+select array(select array[i,i/2] from generate_series(1,5) i);
+              array              
+---------------------------------
+ {{1,0},{2,1},{3,1},{4,2},{5,2}}
+(1 row)
+
+select array(select array['Hello', i::text] from generate_series(9,11) i);
+               array               
+-----------------------------------
+ {{Hello,9},{Hello,10},{Hello,11}}
+(1 row)
+
+-- Insert/update on a column that is array of composite
+create temp table t1 (f1 int8_tbl[]);
+-- Check that arrays of composites are safely detoasted when needed
+create temp table src (f1 text);
+insert into src
+  select string_agg(random()::text,'') from generate_series(1,10000);
+create temp table dest (f1 textandtext[]);
+drop table src;
+drop table dest;
+-- trim_array
+SELECT arr, trim_array(arr, 2)
+FROM
+(VALUES ('{1,2,3,4,5,6}'::bigint[]),
+        ('{1,2}'),
+        ('[10:16]={1,2,3,4,5,6,7}'),
+        ('[-15:-10]={1,2,3,4,5,6}'),
+        ('{{1,10},{2,20},{3,30},{4,40}}')) v(arr);
+              arr              |   trim_array    
+-------------------------------+-----------------
+ {1,2,3,4,5,6}                 | {1,2,3,4}
+ {1,2}                         | {}
+ [10:16]={1,2,3,4,5,6,7}       | {1,2,3,4,5}
+ [-15:-10]={1,2,3,4,5,6}       | {1,2,3,4}
+ {{1,10},{2,20},{3,30},{4,40}} | {{1,10},{2,20}}
+(5 rows)
+
+SELECT trim_array(ARRAY[1, 2, 3], -1); -- fail
+ERROR:  number of elements to trim must be between 0 and 3
+SELECT trim_array(ARRAY[1, 2, 3], 10); -- fail
+ERROR:  number of elements to trim must be between 0 and 3