src/test/regress/expected/text.out - cloudberry - Git at Google

 --
 -- TEXT
 --
 set optimizer_trace_fallback to on;
 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)

 -- text_tbl was already created and filled in test_setup.sql.
 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.
 --
 -- TEXT CASTING TO/FROM ANY TYPE
 --
 SELECT '1'::bool::text;
  text
 ------
  true
 (1 row)

 SELECT array[1,2]::text;
  array
 -------
  {1,2}
 (1 row)

 SELECT '{1,2}'::text::integer[];
  int4
 -------
  {1,2}
 (1 row)

 CREATE TYPE usr_define_type as (id int, name text);
 SELECT '(1,abc)'::text::usr_define_type;
  usr_define_type
 -----------------
  (1,abc)
 (1 row)

 /*
  * 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)

 set optimizer_trace_fallback to off;
	--
	-- TEXT
	--
	set optimizer_trace_fallback to on;
	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)

	-- text_tbl was already created and filled in test_setup.sql.
	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.
	--
	-- TEXT CASTING TO/FROM ANY TYPE
	--
	SELECT '1'::bool::text;
	text
	------
	true
	(1 row)

	SELECT array[1,2]::text;
	array
	-------
	{1,2}
	(1 row)

	SELECT '{1,2}'::text::integer[];
	int4
	-------
	{1,2}
	(1 row)

	CREATE TYPE usr_define_type as (id int, name text);
	SELECT '(1,abc)'::text::usr_define_type;
	usr_define_type
	-----------------
	(1,abc)
	(1 row)

	/*
	* 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)

	set optimizer_trace_fallback to off;