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

commit_hash:46fdf59714b20cf2b61233a06e58365227d3c8b2

1 files changed, 440 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/original/cases/text.out b/yql/essentials/tests/postgresql/original/cases/text.out
new file mode 100644
index 0000000000..b625b09f32
--- /dev/null
+++ b/yql/essentials/tests/postgresql/original/cases/text.out
@@ -0,0 +1,440 @@
+--
+-- TEXT
+--
+SELECT text 'this is a text string' = text 'this is a text string' AS true;
+ true 
+------
+ t
+(1 row)
+
+SELECT text 'this is a text string' = text 'this is a text strin' AS false;
+ false 
+-------
+ f
+(1 row)
+
+CREATE TABLE TEXT_TBL (f1 text);
+INSERT INTO TEXT_TBL VALUES ('doh!');
+INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor');
+SELECT * FROM TEXT_TBL;
+        f1         
+-------------------
+ doh!
+ hi de ho neighbor
+(2 rows)
+
+-- As of 8.3 we have removed most implicit casts to text, so that for example
+-- this no longer works:
+select length(42);
+ERROR:  function length(integer) does not exist
+LINE 1: select length(42);
+               ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+-- But as a special exception for usability's sake, we still allow implicit
+-- casting to text in concatenations, so long as the other input is text or
+-- an unknown literal.  So these work:
+select 'four: '::text || 2+2;
+ ?column? 
+----------
+ four: 4
+(1 row)
+
+select 'four: ' || 2+2;
+ ?column? 
+----------
+ four: 4
+(1 row)
+
+-- but not this:
+select 3 || 4.0;
+ERROR:  operator does not exist: integer || numeric
+LINE 1: select 3 || 4.0;
+                 ^
+HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
+/*
+ * various string functions
+ */
+select concat('one');
+ concat 
+--------
+ one
+(1 row)
+
+select concat(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
+        concat        
+----------------------
+ 123hellotf03-09-2010
+(1 row)
+
+select concat_ws('#','one');
+ concat_ws 
+-----------
+ one
+(1 row)
+
+select concat_ws('#',1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
+         concat_ws          
+----------------------------
+ 1#2#3#hello#t#f#03-09-2010
+(1 row)
+
+select concat_ws(',',10,20,null,30);
+ concat_ws 
+-----------
+ 10,20,30
+(1 row)
+
+select concat_ws('',10,20,null,30);
+ concat_ws 
+-----------
+ 102030
+(1 row)
+
+select concat_ws(NULL,10,20,null,30) is null;
+ ?column? 
+----------
+ t
+(1 row)
+
+select reverse('abcde');
+ reverse 
+---------
+ edcba
+(1 row)
+
+select i, left('ahoj', i), right('ahoj', i) from generate_series(-5, 5) t(i) order by i;
+ i  | left | right 
+----+------+-------
+ -5 |      | 
+ -4 |      | 
+ -3 | a    | j
+ -2 | ah   | oj
+ -1 | aho  | hoj
+  0 |      | 
+  1 | a    | j
+  2 | ah   | oj
+  3 | aho  | hoj
+  4 | ahoj | ahoj
+  5 | ahoj | ahoj
+(11 rows)
+
+select quote_literal('');
+ quote_literal 
+---------------
+ ''
+(1 row)
+
+select quote_literal('abc''');
+ quote_literal 
+---------------
+ 'abc'''
+(1 row)
+
+select quote_literal(e'\\');
+ quote_literal 
+---------------
+ E'\\'
+(1 row)
+
+-- check variadic labeled argument
+select concat(variadic array[1,2,3]);
+ concat 
+--------
+ 123
+(1 row)
+
+select concat_ws(',', variadic array[1,2,3]);
+ concat_ws 
+-----------
+ 1,2,3
+(1 row)
+
+select concat_ws(',', variadic NULL::int[]);
+ concat_ws 
+-----------
+ 
+(1 row)
+
+select concat(variadic NULL::int[]) is NULL;
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat(variadic '{}'::int[]) = '';
+ ?column? 
+----------
+ t
+(1 row)
+
+--should fail
+select concat_ws(',', variadic 10);
+ERROR:  VARIADIC argument must be an array
+LINE 1: select concat_ws(',', variadic 10);
+                                       ^
+/*
+ * format
+ */
+select format(NULL);
+ format 
+--------
+ 
+(1 row)
+
+select format('Hello');
+ format 
+--------
+ Hello
+(1 row)
+
+select format('Hello %s', 'World');
+   format    
+-------------
+ Hello World
+(1 row)
+
+select format('Hello %%');
+ format  
+---------
+ Hello %
+(1 row)
+
+select format('Hello %%%%');
+  format  
+----------
+ Hello %%
+(1 row)
+
+-- should fail
+select format('Hello %s %s', 'World');
+ERROR:  too few arguments for format()
+select format('Hello %s');
+ERROR:  too few arguments for format()
+select format('Hello %x', 20);
+ERROR:  unrecognized format() type specifier "x"
+HINT:  For a single "%" use "%%".
+-- check literal and sql identifiers
+select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', 10, 'Hello');
+                 format                 
+----------------------------------------
+ INSERT INTO mytab VALUES('10','Hello')
+(1 row)
+
+select format('%s%s%s','Hello', NULL,'World');
+   format   
+------------
+ HelloWorld
+(1 row)
+
+select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', 10, NULL);
+               format                
+-------------------------------------
+ INSERT INTO mytab VALUES('10',NULL)
+(1 row)
+
+select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', NULL, 'Hello');
+                 format                 
+----------------------------------------
+ INSERT INTO mytab VALUES(NULL,'Hello')
+(1 row)
+
+-- should fail, sql identifier cannot be NULL
+select format('INSERT INTO %I VALUES(%L,%L)', NULL, 10, 'Hello');
+ERROR:  null values cannot be formatted as an SQL identifier
+-- check positional placeholders
+select format('%1$s %3$s', 1, 2, 3);
+ format 
+--------
+ 1 3
+(1 row)
+
+select format('%1$s %12$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+ format 
+--------
+ 1 12
+(1 row)
+
+-- should fail
+select format('%1$s %4$s', 1, 2, 3);
+ERROR:  too few arguments for format()
+select format('%1$s %13$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+ERROR:  too few arguments for format()
+select format('%0$s', 'Hello');
+ERROR:  format specifies argument 0, but arguments are numbered from 1
+select format('%*0$s', 'Hello');
+ERROR:  format specifies argument 0, but arguments are numbered from 1
+select format('%1$', 1);
+ERROR:  unterminated format() type specifier
+HINT:  For a single "%" use "%%".
+select format('%1$1', 1);
+ERROR:  unterminated format() type specifier
+HINT:  For a single "%" use "%%".
+-- check mix of positional and ordered placeholders
+select format('Hello %s %1$s %s', 'World', 'Hello again');
+            format             
+-------------------------------
+ Hello World World Hello again
+(1 row)
+
+select format('Hello %s %s, %2$s %2$s', 'World', 'Hello again');
+                      format                      
+--------------------------------------------------
+ Hello World Hello again, Hello again Hello again
+(1 row)
+
+-- check variadic labeled arguments
+select format('%s, %s', variadic array['Hello','World']);
+    format    
+--------------
+ Hello, World
+(1 row)
+
+select format('%s, %s', variadic array[1, 2]);
+ format 
+--------
+ 1, 2
+(1 row)
+
+select format('%s, %s', variadic array[true, false]);
+ format 
+--------
+ t, f
+(1 row)
+
+select format('%s, %s', variadic array[true, false]::text[]);
+   format    
+-------------
+ true, false
+(1 row)
+
+-- check variadic with positional placeholders
+select format('%2$s, %1$s', variadic array['first', 'second']);
+    format     
+---------------
+ second, first
+(1 row)
+
+select format('%2$s, %1$s', variadic array[1, 2]);
+ format 
+--------
+ 2, 1
+(1 row)
+
+-- variadic argument can be array type NULL, but should not be referenced
+select format('Hello', variadic NULL::int[]);
+ format 
+--------
+ Hello
+(1 row)
+
+-- variadic argument allows simulating more than FUNC_MAX_ARGS parameters
+select format(string_agg('%s',','), variadic array_agg(i))
+from generate_series(1,200) g(i);
+                                                                                                                                                                                                                                                                                                                                                       format                                                                                                                                                                                                                                                                                                                                                        
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200
+(1 row)
+
+-- check field widths and left, right alignment
+select format('>>%10s<<', 'Hello');
+     format     
+----------------
+ >>     Hello<<
+(1 row)
+
+select format('>>%10s<<', NULL);
+     format     
+----------------
+ >>          <<
+(1 row)
+
+select format('>>%10s<<', '');
+     format     
+----------------
+ >>          <<
+(1 row)
+
+select format('>>%-10s<<', '');
+     format     
+----------------
+ >>          <<
+(1 row)
+
+select format('>>%-10s<<', 'Hello');
+     format     
+----------------
+ >>Hello     <<
+(1 row)
+
+select format('>>%-10s<<', NULL);
+     format     
+----------------
+ >>          <<
+(1 row)
+
+select format('>>%1$10s<<', 'Hello');
+     format     
+----------------
+ >>     Hello<<
+(1 row)
+
+select format('>>%1$-10I<<', 'Hello');
+     format     
+----------------
+ >>"Hello"   <<
+(1 row)
+
+select format('>>%2$*1$L<<', 10, 'Hello');
+     format     
+----------------
+ >>   'Hello'<<
+(1 row)
+
+select format('>>%2$*1$L<<', 10, NULL);
+     format     
+----------------
+ >>      NULL<<
+(1 row)
+
+select format('>>%2$*1$L<<', -10, NULL);
+     format     
+----------------
+ >>NULL      <<
+(1 row)
+
+select format('>>%*s<<', 10, 'Hello');
+     format     
+----------------
+ >>     Hello<<
+(1 row)
+
+select format('>>%*1$s<<', 10, 'Hello');
+     format     
+----------------
+ >>     Hello<<
+(1 row)
+
+select format('>>%-s<<', 'Hello');
+  format   
+-----------
+ >>Hello<<
+(1 row)
+
+select format('>>%10L<<', NULL);
+     format     
+----------------
+ >>      NULL<<
+(1 row)
+
+select format('>>%2$*1$L<<', NULL, 'Hello');
+   format    
+-------------
+ >>'Hello'<<
+(1 row)
+
+select format('>>%2$*1$L<<', 0, 'Hello');
+   format    
+-------------
+ >>'Hello'<<
+(1 row)
+