Merge branch 'rightlib' into mergelibs-241120-1113

1 files changed, 464 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/cases/text.err b/yql/essentials/tests/postgresql/cases/text.err
new file mode 100644
index 0000000000..d41c3081bb
--- /dev/null
+++ b/yql/essentials/tests/postgresql/cases/text.err
@@ -0,0 +1,464 @@
+<sql-statement>
+--
+-- TEXT
+--
+SELECT text 'this is a text string' = text 'this is a text string' AS true;
+</sql-statement>
+<sql-statement>
+SELECT text 'this is a text string' = text 'this is a text strin' AS false;
+</sql-statement>
+<sql-statement>
+CREATE TABLE TEXT_TBL (f1 text);
+</sql-statement>
+<sql-statement>
+INSERT INTO TEXT_TBL VALUES ('doh!');
+</sql-statement>
+<sql-statement>
+INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor');
+</sql-statement>
+<sql-statement>
+SELECT * FROM TEXT_TBL;
+</sql-statement>
+<sql-statement>
+-- As of 8.3 we have removed most implicit casts to text, so that for example
+-- this no longer works:
+select length(42);
+</sql-statement>
+-stdin-:<main>: Error: Type annotation
+
+    -stdin-:<main>:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
+    	-- As of 8.3 we have removed most implicit casts to text, so that for example
+	^
+        -stdin-:<main>:3:8: Error: At function: PgCall
+        	select length(42);
+	       ^
+            -stdin-:<main>:3:8: Error: Unable to find an overload for proc length with given argument types: (int4)
+            	select length(42);
+	       ^
+<sql-statement>
+-- 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;
+</sql-statement>
+-stdin-:<main>: Error: Type annotation
+
+    -stdin-:<main>:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
+    	-- But as a special exception for usability's sake, we still allow implicit
+	^
+        -stdin-:<main>:4:23: Error: At function: PgOp
+        	select 'four: '::text || 2+2;
+	                      ^
+            -stdin-:<main>:4:23: Error: Unable to find an overload for operator || with given argument type(s): (text,int4)
+            	select 'four: '::text || 2+2;
+	                      ^
+<sql-statement>
+select 'four: ' || 2+2;
+</sql-statement>
+-stdin-:<main>: Error: Type annotation
+
+    -stdin-:<main>:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
+    	select 'four: ' || 2+2;
+	^
+        -stdin-:<main>:1:17: Error: At function: PgOp
+        	select 'four: ' || 2+2;
+	                ^
+            -stdin-:<main>:1:17: Error: Unable to find an overload for operator || with given argument type(s): (unknown,int4)
+            	select 'four: ' || 2+2;
+	                ^
+<sql-statement>
+-- but not this:
+select 3 || 4.0;
+</sql-statement>
+-stdin-:<main>: Error: Type annotation
+
+    -stdin-:<main>:1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
+    	-- but not this:
+	^
+        -stdin-:<main>:2:10: Error: At function: PgOp
+        	select 3 || 4.0;
+	         ^
+            -stdin-:<main>:2:10: Error: Unable to find an overload for operator || with given argument type(s): (int4,numeric)
+            	select 3 || 4.0;
+	         ^
+<sql-statement>
+/*
+ * various string functions
+ */
+select concat('one');
+</sql-statement>
+<sql-statement>
+select concat(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
+</sql-statement>
+<sql-statement>
+select concat_ws('#','one');
+</sql-statement>
+<sql-statement>
+select concat_ws('#',1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
+</sql-statement>
+<sql-statement>
+select concat_ws(',',10,20,null,30);
+</sql-statement>
+<sql-statement>
+select concat_ws('',10,20,null,30);
+</sql-statement>
+<sql-statement>
+select concat_ws(NULL,10,20,null,30) is null;
+</sql-statement>
+<sql-statement>
+select reverse('abcde');
+</sql-statement>
+<sql-statement>
+select i, left('ahoj', i), right('ahoj', i) from generate_series(-5, 5) t(i) order by i;
+</sql-statement>
+<sql-statement>
+select quote_literal('');
+</sql-statement>
+<sql-statement>
+select quote_literal('abc''');
+</sql-statement>
+<sql-statement>
+select quote_literal(e'\\');
+</sql-statement>
+<sql-statement>
+-- check variadic labeled argument
+select concat(variadic array[1,2,3]);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:2:8: Error: FuncCall: unsupported func_variadic
+    	select concat(variadic array[1,2,3]);
+	       ^
+<sql-statement>
+select concat_ws(',', variadic array[1,2,3]);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:1:8: Error: FuncCall: unsupported func_variadic
+    	select concat_ws(',', variadic array[1,2,3]);
+	       ^
+<sql-statement>
+select concat_ws(',', variadic NULL::int[]);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:1:8: Error: FuncCall: unsupported func_variadic
+    	select concat_ws(',', variadic NULL::int[]);
+	       ^
+<sql-statement>
+select concat(variadic NULL::int[]) is NULL;
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:1:8: Error: FuncCall: unsupported func_variadic
+    	select concat(variadic NULL::int[]) is NULL;
+	       ^
+<sql-statement>
+select concat(variadic '{}'::int[]) = '';
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:1:8: Error: FuncCall: unsupported func_variadic
+    	select concat(variadic '{}'::int[]) = '';
+	       ^
+<sql-statement>
+--should fail
+select concat_ws(',', variadic 10);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:2:8: Error: FuncCall: unsupported func_variadic
+    	select concat_ws(',', variadic 10);
+	       ^
+<sql-statement>
+/*
+ * format
+ */
+select format(NULL);
+</sql-statement>
+<sql-statement>
+select format('Hello');
+</sql-statement>
+<sql-statement>
+select format('Hello %s', 'World');
+</sql-statement>
+<sql-statement>
+select format('Hello %%');
+</sql-statement>
+<sql-statement>
+select format('Hello %%%%');
+</sql-statement>
+<sql-statement>
+-- should fail
+select format('Hello %s %s', 'World');
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	-- should fail
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  too few arguments for format()
+
+        	-- should fail
+	^
+<sql-statement>
+select format('Hello %s');
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	select format('Hello %s');
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  too few arguments for format()
+
+        	select format('Hello %s');
+	^
+<sql-statement>
+select format('Hello %x', 20);
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	select format('Hello %x', 20);
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  unrecognized format() type specifier "x"
+HINT:  For a single "%" use "%%".
+
+        	select format('Hello %x', 20);
+	^
+<sql-statement>
+-- check literal and sql identifiers
+select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', 10, 'Hello');
+</sql-statement>
+<sql-statement>
+select format('%s%s%s','Hello', NULL,'World');
+</sql-statement>
+<sql-statement>
+select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', 10, NULL);
+</sql-statement>
+<sql-statement>
+select format('INSERT INTO %I VALUES(%L,%L)', 'mytab', NULL, 'Hello');
+</sql-statement>
+<sql-statement>
+-- should fail, sql identifier cannot be NULL
+select format('INSERT INTO %I VALUES(%L,%L)', NULL, 10, 'Hello');
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	-- should fail, sql identifier cannot be NULL
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  null values cannot be formatted as an SQL identifier
+
+        	-- should fail, sql identifier cannot be NULL
+	^
+<sql-statement>
+-- check positional placeholders
+select format('%1$s %3$s', 1, 2, 3);
+</sql-statement>
+<sql-statement>
+select format('%1$s %12$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+</sql-statement>
+<sql-statement>
+-- should fail
+select format('%1$s %4$s', 1, 2, 3);
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	-- should fail
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  too few arguments for format()
+
+        	-- should fail
+	^
+<sql-statement>
+select format('%1$s %13$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	select format('%1$s %13$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  too few arguments for format()
+
+        	select format('%1$s %13$s', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
+	^
+<sql-statement>
+select format('%0$s', 'Hello');
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	select format('%0$s', 'Hello');
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  format specifies argument 0, but arguments are numbered from 1
+
+        	select format('%0$s', 'Hello');
+	^
+<sql-statement>
+select format('%*0$s', 'Hello');
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	select format('%*0$s', 'Hello');
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  format specifies argument 0, but arguments are numbered from 1
+
+        	select format('%*0$s', 'Hello');
+	^
+<sql-statement>
+select format('%1$', 1);
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	select format('%1$', 1);
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  unterminated format() type specifier
+HINT:  For a single "%" use "%%".
+
+        	select format('%1$', 1);
+	^
+<sql-statement>
+select format('%1$1', 1);
+</sql-statement>
+-stdin-:<main>: Fatal: Execution
+
+    -stdin-:<main>:1:1: Fatal: Execution of node: Result
+    	select format('%1$1', 1);
+	^
+        -stdin-:<main>:1:1: Fatal: ERROR:  unterminated format() type specifier
+HINT:  For a single "%" use "%%".
+
+        	select format('%1$1', 1);
+	^
+<sql-statement>
+-- check mix of positional and ordered placeholders
+select format('Hello %s %1$s %s', 'World', 'Hello again');
+</sql-statement>
+<sql-statement>
+select format('Hello %s %s, %2$s %2$s', 'World', 'Hello again');
+</sql-statement>
+<sql-statement>
+-- check variadic labeled arguments
+select format('%s, %s', variadic array['Hello','World']);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:2:8: Error: FuncCall: unsupported func_variadic
+    	select format('%s, %s', variadic array['Hello','World']);
+	       ^
+<sql-statement>
+select format('%s, %s', variadic array[1, 2]);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:1:8: Error: FuncCall: unsupported func_variadic
+    	select format('%s, %s', variadic array[1, 2]);
+	       ^
+<sql-statement>
+select format('%s, %s', variadic array[true, false]);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:1:8: Error: FuncCall: unsupported func_variadic
+    	select format('%s, %s', variadic array[true, false]);
+	       ^
+<sql-statement>
+select format('%s, %s', variadic array[true, false]::text[]);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:1:8: Error: FuncCall: unsupported func_variadic
+    	select format('%s, %s', variadic array[true, false]::text[]);
+	       ^
+<sql-statement>
+-- check variadic with positional placeholders
+select format('%2$s, %1$s', variadic array['first', 'second']);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:2:8: Error: FuncCall: unsupported func_variadic
+    	select format('%2$s, %1$s', variadic array['first', 'second']);
+	       ^
+<sql-statement>
+select format('%2$s, %1$s', variadic array[1, 2]);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:1:8: Error: FuncCall: unsupported func_variadic
+    	select format('%2$s, %1$s', variadic array[1, 2]);
+	       ^
+<sql-statement>
+-- variadic argument can be array type NULL, but should not be referenced
+select format('Hello', variadic NULL::int[]);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:2:8: Error: FuncCall: unsupported func_variadic
+    	select format('Hello', variadic NULL::int[]);
+	       ^
+<sql-statement>
+-- 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);
+</sql-statement>
+-stdin-:<main>: Error: Parse Sql
+
+    -stdin-:<main>:2:8: Error: FuncCall: unsupported func_variadic
+    	select format(string_agg('%s',','), variadic array_agg(i))
+	       ^
+<sql-statement>
+-- check field widths and left, right alignment
+select format('>>%10s<<', 'Hello');
+</sql-statement>
+<sql-statement>
+select format('>>%10s<<', NULL);
+</sql-statement>
+<sql-statement>
+select format('>>%10s<<', '');
+</sql-statement>
+<sql-statement>
+select format('>>%-10s<<', '');
+</sql-statement>
+<sql-statement>
+select format('>>%-10s<<', 'Hello');
+</sql-statement>
+<sql-statement>
+select format('>>%-10s<<', NULL);
+</sql-statement>
+<sql-statement>
+select format('>>%1$10s<<', 'Hello');
+</sql-statement>
+<sql-statement>
+select format('>>%1$-10I<<', 'Hello');
+</sql-statement>
+<sql-statement>
+select format('>>%2$*1$L<<', 10, 'Hello');
+</sql-statement>
+<sql-statement>
+select format('>>%2$*1$L<<', 10, NULL);
+</sql-statement>
+<sql-statement>
+select format('>>%2$*1$L<<', -10, NULL);
+</sql-statement>
+<sql-statement>
+select format('>>%*s<<', 10, 'Hello');
+</sql-statement>
+<sql-statement>
+select format('>>%*1$s<<', 10, 'Hello');
+</sql-statement>
+<sql-statement>
+select format('>>%-s<<', 'Hello');
+</sql-statement>
+<sql-statement>
+select format('>>%10L<<', NULL);
+</sql-statement>
+<sql-statement>
+select format('>>%2$*1$L<<', NULL, 'Hello');
+</sql-statement>
+<sql-statement>
+select format('>>%2$*1$L<<', 0, 'Hello');
+</sql-statement>