Merge pull request #11788 from ydb-platform/mergelibs-241120-1113

Library import 241120-1113

1 files changed, 311 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/cases/text.out b/yql/essentials/tests/postgresql/cases/text.out
new file mode 100644
index 0000000000..86196b7f59
--- /dev/null
+++ b/yql/essentials/tests/postgresql/cases/text.out
@@ -0,0 +1,311 @@
+--
+-- 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)
+
+/*
+ * various string functions
+ */
+select concat('one');
+ concat 
+--------
+ one
+(1 row)
+
+select concat_ws('#','one');
+ concat_ws 
+-----------
+ one
+(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)
+
+/*
+ * 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 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)
+