Platform and Client Compatibility

display_leading_zero

Specifies how floating point numbers are displayed.

• If this item is not specified, decimal numbers between –1 and 0, and between 0 and 1, do not display the leading zero before the decimal point. For example, 0.25 is displayed as .25.
• If this item is specified, decimal numbers between –1 and 0, and between 0 and 1, display the leading zero before the decimal point. For example, 0.25 is displayed as 0.25.

ORA

TD

end_month_calculate

Specifies the calculation logic of the add_months function.

Assuming that the two parameters of the add_months function are param1 and param2, and the sum of the months of param1 and param2 is result:

• If this item is not specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in param1. For example:

1
2
3
4
5

select add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-28 00:00:00
(1 row)

• If this item is specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in result. For example:

1
2
3
4
5

select add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-31 00:00:00
(1 row)

ORA

TD

compat_analyze_sample

Specifies the sampling behavior of the ANALYZE operation.

If this item is specified, the sample collected by the ANALYZE operation will be limited to around 30,000 records, controlling CN memory consumption and maintaining the stability of ANALYZE.

ORA

TD

MySQL

bind_schema_tablespace

Binds a schema with the tablespace with the same name.

If a tablespace name is the same as sche_name, default_tablespace will also be set to sche_name if search_path is set to sche_name.

ORA

TD

MySQL

bind_procedure_searchpath

Specifies the search path of the database object for which no schema name is specified.

If no schema name is specified for a stored procedure, the search is performed in the schema the stored procedure belongs to.

If the stored procedure is not found, the following operations are performed:

• If this item is not specified, the system reports an error and exits.
• If this item is specified, the search continues based on the settings of search_path. If the issue persists, the system reports an error and exits.

ORA

TD

MySQL

correct_to_number

Controls the compatibility of the to_number() result.

If this item is specified, the result of the to_number() function is the same as that of PG11. Otherwise, the result is the same as that of Oracle.

ORA

unbind_divide_bound

Controls the range check on the result of integer division.

• If this item is not specified, the division result is checked. If the result is out of the range, an error is reported. In the following example, an out-of-range error is reported because the value of INT_MIN/(–1) is greater than the value of INT_MAX.

SELECT (-2147483648)::int / (-1)::int;
ERROR:  integer out of range

• If this item is specified, the range of the division result does not need to be checked. In the following example, INT_MIN/(–1) can be used to obtain the output result INT_MAX+1.

SELECT (-2147483648)::int / (-1)::int;
  ?column?
------------
 2147483648
(1 row)

ORA

TD

merge_update_multi

Specifies whether to perform an update when MERGE INTO is executed to match multiple rows.

If this item is specified, no error is reported when multiple rows are matched. Otherwise, an error is reported (same as Oracle).

ORA

TD

disable_row_update_multi

Specifies whether to perform an update when multiple rows of a row-store table are matched.

If this item is specified, an error is reported when multiple rows are matched. Otherwise, multiple rows can be matched and updated by default.

ORA

TD

return_null_string

Specifies how to display the empty result (empty string '') of the lpad(), rpad(), repeat(), regexp_split_to_table(), and split_part() functions.

• If this item is not specified, the empty string is displayed as NULL.

1
2
3
4
5

select length(lpad('123',0,'*')) from dual;
length
--------

(1 row)

• If this item is specified, the empty string is displayed as single quotation marks ('').

1
2
3
4
5

select length(lpad('123',0,'*')) from dual;
length
--------
0
(1 row)

ORA

compat_concat_variadic

Specifies the compatibility of variadic results of the concat() and concat_ws() functions.

If this item is specified and a concat function has a parameter of the variadic type, different result formats in Oracle and Teradata are retained. If this item is not specified and a concat function has a parameter of the variadic type, the result format of Oracle is retained for both Oracle and Teradata.

ORA

TD

convert_string_digit_to_numeric

Specifies the type casting priority for binary BOOL operations on the CHAR type and INT type.

• If this item is not specified, the type casting priority is the same as that of PG9.6.
• After this item is configured, all binary BOOL operations of the CHAR type and INT type are forcibly converted to the NUMERIC type for computation.

  After this configuration item is set, the CHAR types that are affected include BPCHAR, VARCHAR, NVARCHAR2, and TEXT, and the INT types that are affected include INT1, INT2, INT4, and INT8.

ORA

TD

MySQL

check_function_conflicts

Controls the check of the custom plpgsql/SQL function attributes.

• If this parameter is not specified, the IMMUTABLE/STABLE/VOLATILE attributes of a custom function are not checked.
• If this parameter is specified, the IMMUTABLE attribute of a custom function is checked. If the function contains a table or the STABLE/VOLATILE function, an error is reported during the function execution. In a custom function, a table or the STABLE/VOLATILE function conflicts with the IMMUTABLE attribute, thus function behaviors are not IMMUTABLE in this case.

For example, when this parameter is specified, an error is reported in the following scenarios:

CREATE OR replace FUNCTION sql_immutable (INTEGER)
RETURNS INTEGER AS 'SELECT a+$1 from shipping_schema.t4 where a=1;'
LANGUAGE SQL IMMUTABLE
RETURNS NULL
ON NULL INPUT;
select sql_immutable(1);
ERROR:  IMMUTABLE function cannot contain SQL statements with relation or Non-IMMUTABLE function.
CONTEXT:  SQL function "sql_immutable" during startup
referenced column: sql_immutable

ORA

TD

MySQL

varray_verification

Indicates whether to verify the array length and array type length. This parameter is compatible with GaussDB(DWS) of versions earlier than 8.1.0.

If this parameter is specified, the array length and array type length are not verified.

Scenario 1
CREATE OR REPLACE PROCEDURE varray_verification 
AS 
    TYPE org_varray_type IS varray(5) OF VARCHAR2(2); 
    v_org_varray org_varray_type; 
BEGIN 
    v_org_varray(1) := '111'; --If the value exceeds the limit of VARCHAR2(2), the setting will be consistent with that in the historical version and no verification is performed after configuring this option.
END; 
/ 
Scenario 2
CREATE OR REPLACE PROCEDURE varray_verification_i3_1 
AS 
    TYPE org_varray_type IS varray(2) OF NUMBER(2); 
    v_org_varray org_varray_type; 
BEGIN 
    v_org_varray(3) := 1; --If the value exceeds the limit of varray(2) specified for array length, the setting will be consistent with that in the historical version and no verification is performed after configuring this option.
END; 
/ 

ORA

TD

strict_concat_functions

Indicates whether the textanycat() and anytextcat() functions are compatible with the return value if there are null parameters. This parameter and strict_text_concat_td are mutually exclusive.

In MySQL-compatible mode, this parameter has no impact.

• If this configuration item is not specified, the returned values of the textanycat() and anytextcat() functions are the same as those in the Oracle database.
• When this configuration item is specified, if there are null parameters in the textanycat() and anytextcat() functions, the returned value is also null. Different result formats in Oracle and Teradata are retained.

If this configuration item is not specified, the returned values of the textanycat() and anytextcat() functions are the same as those in the Oracle database.

SELECT textanycat('gauss', cast(NULL as BOOLEAN));
 textanycat
------------
 gauss
(1 row)

SELECT 'gauss' || cast(NULL as BOOLEAN); -- In this case, the || operator is converted to the textanycat function.
 ?column?
----------
 gauss
(1 row)

When setting this configuration item, retain the results that are different from those in Oracle and Teradata:

SELECT textanycat('gauss', cast(NULL as BOOLEAN));
 textanycat
------------

(1 row)

SELECT 'gauss' || cast(NULL as BOOLEAN); -- In this case, the || operator is converted to the textanycat function.
 ?column?
----------

(1 row)

ORA

TD

strict_text_concat_td

In Teradata compatible mode, whether the textcat(), textanycat() and anytextcat() functions are compatible with the return value if there are null parameters. This parameter and strict_concat_functions are mutually exclusive.

• If this parameter is not specified, the return values of the textcat(), textanycat(), and anytextcat() functions in Teradata-compatible mode are the same as those in GaussDB(DWS).
• When this parameter is specified, if the textcat(), textanycat(), and anytextcat() functions contain any null parameter values, the return value is null in Teradata-compatible mode.

If this parameter is not specified, the return values of the textcat(), textanycat(), and anytextcat() functions are the same as those in GaussDB(DWS).

td_compatibility_db=# SELECT textcat('abc', NULL);
textcat
---------
abc
(1 row)

td_compatibility_db=# SELECT 'abc' || NULL; -- In this case, the operator || is converted to the textcat() function.
?column?
----------
abc
(1 row)

When this parameter is specified, NULL is returned if any of the textcat(), textanycat(), and anytextcat() functions returns a null value.

td_compatibility_db=# SELECT textcat('abc', NULL);
textcat
---------

(1 row)

td_compatibility_db=# SELECT 'abc' || NULL;
?column?
----------

(1 row)

TD

compat_display_ref_table

Sets the column display format in the view.

• If this parameter is not specified, the prefix is used by default, in the tab.col format.
• Specify this parameter to the same original definition. It is displayed only when the original definition contains a prefix.

SET behavior_compat_options='compat_display_ref_table';
CREATE OR REPLACE VIEW viewtest2 AS SELECT a.c1, c2, a.c3, 0 AS c4 FROM viewtest_tbl a;
SELECT pg_get_viewdef('viewtest2');
pg_get_viewdef
-----------------------------------------------------
SELECT a.c1, c2, a.c3, 0 AS c4 FROM viewtest_tbl a;
(1 row)

ORA

TD

para_support_set_func

Whether the input parameters of the COALESCE(), NVL(), GREATEST(), and LEAST() functions in a column-store table support multiple result set expressions.

• If this item is not specified and the input parameter contains multiple result set expressions, an error is reported, indicating that the function is not supported.

1
2

SELECT COALESCE(regexp_split_to_table(c3,'#'), regexp_split_to_table(c3,'#')) FROM regexp_ext2_tb1 ORDER BY 1 LIMIT 5;
ERROR:  set-valued function called in context that cannot accept a set

• When this configuration item is specified, the function input parameter can contain multiple result set expressions.

1
2
3
4
5
6
7
8
9

SELECT COALESCE(regexp_split_to_table(c3,'#'), regexp_split_to_table(c3,'#')) FROM regexp_ext2_tb1 ORDER BY 1 LIMIT 5;
 coalesce
----------
 a
 a
 a
 a
 a
(5 rows)

ORA

TD

disable_select_truncate_parallel

Controls the DDL lock level such as TRUNCATE in a partitioned table.

• If this item is specified, the concurrent execution of TRUNCATE and DML operations (such as SELECT) on different partitions is forbidden, and the fast query shipping (FQS) of the SELECT operation on the partitioned table is allowed. You can set this parameter in the OLTP database, where there are many simple queries on partitioned tables, and there is no requirement for concurrent TRUNCATE and DML operations on different partitions.
• If this item is not specified, SELECT and TRUNCATE operations can be concurrently performed on different partitions in a partitioned table, and the FQS of the partitioned table is disabled to avoid possible inconsistency.

ORA

TD

MySQL

bpchar_text_without_rtrim

In Teradata-compatible mode, controls the space to be retained on the right during the character conversion from bpchar to text. If the actual length is less than the length specified by bpchar, spaces are added to the value to be compatible with the Teradata style of the bpchar string.

Currently, ignoring spaces at the end of a string for comparison is not supported. If the concatenated string contains spaces at the end, the comparison is space-sensitive.

The following is an example:

td_compatibility_db=# select length('a'::char(10)::text);
length
--------
10
(1 row)

td_compatibility_db=# select length('a'||'a'::char(10));
length
--------
11
(1 row)

TD

convert_empty_str_to_null_td

In Teradata-compatible mode, controls the to_date, to_timestamp, and to_number type conversion functions to return null when they encounter empty strings, and controls the format of the return value when the to_char function encounters an input parameter of the date type.

Example:

If this parameter is not specified:

td_compatibility_db=# select to_number('');
 to_number 
-----------
         0
(1 row)

td_compatibility_db=# select to_date('');
ERROR:  the format is not correct
DETAIL:  invalid date length "0", must between 8 and 10.
CONTEXT:  referenced column: to_date

td_compatibility_db=# select to_timestamp('');
      to_timestamp
------------------------
 0001-01-01 00:00:00 BC
(1 row)

td_compatibility_db=# select to_char(date '2020-11-16');
        to_char         
------------------------
 2020-11-16 00:00:00+08
(1 row)

If this parameter is specified, and parameters of to_number, to_date, and to_timestamp functions contain empty strings:

td_compatibility_db=# select to_number('');
 to_number 
-----------

(1 row)

td_compatibility_db=# select to_date('');
 to_date 
---------

(1 row)

td_compatibility_db=# select to_timestamp('');
 to_timestamp
--------------

(1 row)

td_compatibility_db=# select to_char(date '2020-11-16');
  to_char   
------------
 2020/11/16
(1 row)

TD

disable_case_specific

Determines whether to ignore case sensitivity during character type match. This parameter is valid only in Teradata-compatible mode.

• If this item is not specified, characters are case-sensitive during character type match.
• If this item is specified, characters are case-insensitive during character type match.

After being specified, this item will affect five character types (CHAR, TEXT, BPCHAR, VARCHAR, and NVARCHAR), 12 operators (<, >, =, >=, <=, !=, <>, !=, like, not like, in, and not in), and expressions case when and decode.

TD

enable_interval_to_text

Controls the implicit conversion from the interval type to the text type.

• When this option is enabled, the implicit conversion from the interval type to the text type is supported.

  SELECT TO_DATE('20200923', 'yyyymmdd') - TO_DATE('20200920', 'yyyymmdd') = '3'::text;
  ?column?
  ----------
  f
  (1 row)

• When this option is disabled, the implicit conversion from the interval type to the text type is not supported.

  SELECT TO_DATE('20200923', 'yyyymmdd') - TO_DATE('20200920', 'yyyymmdd') = '3'::text;
  ?column?
  ----------
  t
  (1 row)

ORA

TD

MySQL

case_insensitive

In MySQL-compatible mode, configure this parameter to specify the case-insensitive input parameters of the locate, strpos, and instr string functions.

Currently, this parameter is not configured by default. That is, the input parameter is case-sensitive.

Example:

• If this parameter is not configured, the input parameter is case-sensitive.

  mysql_compatibility_db=# SELECT LOCATE('sub', 'Substr');
   locate
  --------
        0
  (1 row)

• If this parameter is configured, the input parameter is case-insensitive.

  mysql_compatibility_db=# SELECT LOCATE('sub', 'Substr');
   locate
  --------
        1
  (1 row)

MySQL

inherit_not_null_strict_func

Controls the original strict attribute of a function. A function with one parameter can transfer the NOT NULL attribute. func(x) is used an example. If func() is the strict attribute and x contains the NOT NULL constraint, func(x) also contains the NOT NULL constraint.

The compatible configuration item is effective in some optimization scenarios, for example, NOT IN and COUNT(DISTINCT) optimization. However, the optimization results may be incorrect in specific scenarios.

Currently, this parameter is not configured by default to ensure that the result is correct. However, the performance may be rolled back. If an error occurs, you can set this parameter to roll back to the historical version.

ORA

TD

MySQL

disable_compat_minmax_expr_mysql

Specifies the method for processing the input parameter null in the greatest/least expression in MySQL-compatible mode.

You can configure this parameter to roll back to a historical version.

• If this parameter is not configured and the input parameter is null, null is returned.

  mysql_compatibility_db=# SELECT greatest(1, 2, null), least(1, 2, null);
   greatest | least
  ----------+-------
            |
  (1 row)

• If this parameter is configured, the maximum or minimum value of non-null parameters is returned.

  mysql_compatibility_db=# SELECT greatest(1, 2, null), least(1, 2, null);
   greatest | least
  ----------+-------
          2 |     1
  (1 row)

MySQL

disable_compat_substr_mysql

Specifies the behavior of the substr/substring function when the start position pos is ≤ 0 in MySQL-compatible mode.

You can configure this parameter to roll back to a historical version.

• If this parameter is not configured, that is, an empty string is returned when pos = 0. When pos < 0, TRUNCATE starts from the last |pos| character on.

  mysql_compatibility_db=# SELECT substr('helloworld',0);
   substr
  --------
  
  (1 row)
  mysql_compatibility_db=# SELECT substring('helloworld',0),substring('helloworld',-2,4);
   substring | substring
  -----------+-----------
             | ld
  (1 row)

• If this parameter is configured and pos is ≤ 0, characters are truncated from the left.

  mysql_compatibility_db=# SELECT substr('helloworld',0);
     substr
  ------------
   helloworld
  (1 row)
  mysql_compatibility_db=# SELECT substring('helloworld',0),substring('helloworld',-2,4);
   substring  | substring
  ------------+-----------
   helloworld | h
  (1 row)

MySQL

disable_compat_trim_mysql

Specifies the method for processing the input parameter in the trim/ltrim/rtrim function in MySQL-compatible mode.

You can configure this parameter to roll back to a historical version.

• If this parameter is not configured, the entire substring is matched.

  mysql_compatibility_db=# SELECT trim('{}{name}{}','{}'),trim('xyznamezyx','xyz');
   btrim  |  btrim
  --------+---------
   {name} | namezyx
  (1 row)

• If this parameter is configured, a single character in the character set is matched.

  mysql_compatibility_db=# SELECT trim('{}{name}{}','{}'),trim('xyznamezyx','xyz');
   btrim | btrim
  -------+-------
   name  | name
  (1 row)

MySQL

light_object_mtime

Specifies whether the mtime column in the pg_object system catalog records object operations.

• If this parameter is configured, the GRANT, REVOKE, and TRUNCATE operations are not recorded by mtime, that is, the mtime column is not updated.
• If this parameter is not configured (by default), the ALTER, COMMENT, GRANT, REVOKE, and TRUNCATE operations are recorded by mtime, that is, the mtime column is updated.

ORA

TD

MySQL

disable_including_all_mysql

In MySQL-compatible mode, this parameter controls whether the CREATE TABLE...LIKE syntax is INCLUDING_ALL.

By default, this parameter is not set. That is, in MySQL compatibility mode, CREATE TABLE... LIKE syntax is INCLUDING_ALL.

You can configure this parameter to roll back to a historical version.

• If this parameter is not set, in MySQL-compatible mode, the CREATE TABLE... LIKE syntax is in INCLUDING_ALL.

  mysql_compatibility_db=# CREATE TABLE mysql_like(id int, name varchar(10), score int) DISTRIBUTE BY hash(id) COMMENT 'mysql_like';
  CREATE TABLE
  mysql_compatibility_db=# CREATE INDEX index_like ON mysql_like(name);
  CREATE INDEX
  mysql_compatibility_db=# \d+ mysql_like;
                               Table "public.mysql_like"
   Column |         Type          | Modifiers | Storage  | Stats target | Description
  --------+-----------------------+-----------+----------+--------------+-------------
   id     | integer               |           | plain    |              |
   name   | character varying(10) |           | extended |              |
   score  | integer               |           | plain    |              |
  Indexes:
      "index_like" btree (name) TABLESPACE pg_default
  Has OIDs: no
  Distribute By: HASH(id)
  Location Nodes: ALL DATANODES
  Options: orientation=row, compression=no
  
  mysql_compatibility_db=# CREATE TABLE copy_like like mysql_like;
  CREATE TABLE
  mysql_compatibility_db=# \d+ copy_like;
                                Table "public.copy_like"
   Column |         Type          | Modifiers | Storage  | Stats target | Description
  --------+-----------------------+-----------+----------+--------------+-------------
   id     | integer               |           | plain    |              |
   name   | character varying(10) |           | extended |              |
   score  | integer               |           | plain    |              |
  Indexes:
      "copy_like_name_idx" btree (name) TABLESPACE pg_default
  Has OIDs: no
  Distribute By: HASH(id)
  Location Nodes: ALL DATANODES
  Options: orientation=row, compression=no

• If this parameter is set, in MySQL-compatible mode, the CREATE TABLE... LIKE syntax is empty.

  mysql_compatibility_db=# SET behavior_compat_options = 'disable_including_all_mysql';
  SET
  mysql_compatibility_db=# CREATE TABLE mysql_copy LIKE mysql_like;
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  mysql_db=# \d+ mysql_copy;
                               Table "public.mysql_copy"
   Column |         Type          | Modifiers | Storage  | Stats target | Description
  --------+-----------------------+-----------+----------+--------------+-------------
   id     | integer               |           | plain    |              |
   name   | character varying(10) |           | extended |              |
   score  | integer               |           | plain    |              |
  Has OIDs: no
  Distribute By: ROUND ROBIN
  Location Nodes: ALL DATANODES
  Options: orientation=row, compression=no

MySQL

cte_onetime_inline

Indicates whether to execute inline for non-stream plans.

• When this parameter is set, the CTE that is not in a stream plan and is referenced only once executes inline.
• If this parameter is not set, the CTE that is not in a stream plan and is referenced only once does not execute inline.

ORA

TD

MySQL

skip_first_after_mysql

Controls whether to ignore the FIRST/AFTER colname syntax in ALTER TABLE ADD/MODIFY/CHANGE COLUMN in MySQL-compatible mode.

• If this parameter is set, the FIRST/AFTER colname syntax is ignored, and executing this syntax will not result in any errors.

  mysql_compatibility_db=# SET behavior_compat_options = 'skip_first_after_mysql';
  mysql_compatibility_db=# ALTER TABLE t1 ADD COLUMN b text after a;
  ALTER TABLE

• If this parameter is not set, the FIRST/AFTER colname syntax is not supported, and executing this syntax causes errors.

  mysql_compatibility_db=# SET behavior_compat_options = '';
  mysql_compatibility_db=# ALTER TABLE t1 ADD COLUMN b text after a;
  ERROR:  FIRST/AFTER is not yet supported.

MySQL

enable_division_by_zero_mysql

Specifies whether division or modulo operations will result in an error when the divisor is 0 in MySQL-compatible mode. (This configuration item is supported only by clusters of version 8.1.3.110 or later.)

• If this parameter is set, NULL is returned if the divisor is 0 in a division or modulo operation.

  compatible_mysql_db=# SET behavior_compat_options = 'enable_division_by_zero_mysql';
  SET
  compatible_mysql_db=# SELECT 1/0 AS test;
   test 
  ----------
           
  (1 row)

• If this parameter is not set, an error is returned if the divisor is 0 in a division or modulo operation.

  compatible_mysql_db=# SELECT 1/0;
  ERROR:  division by zero

MySQL

normal_session_id

Indicates whether to generate a session ID in normal format.

• If this option is set, a session ID in normal format will be generated, which is compatible with session IDs in clusters of version 8.1.3 or earlier.

  1 2 3 4 5 6

  SET behavior_compat_options='normal_session_id'; SELECT pg_current_sessionid(); pg_current_sessionid ---------------------------- 1660268184.140594655524608 (1 row)

• If this parameter is not set, a session ID in pretty format will be generated.

  1 2 3 4 5 6

  SET behavior_compat_options=''; SELECT pg_current_sessionid(); pg_current_sessionid ----------------------------------------- 1660268184.140594655524608.coordinator1 (1 row)

ORA

TD

MySQL

disable_jsonb_exact_match

Specifies whether to check the jsonb type during fuzzy match for binary operators. (This parameter is supported by clusters of version 8.2.0 or later.)

• If this parameter is specified, operators search for matched items within the entire search scope (including the jsonb type) during fuzzy match. This setting is compatible with the match rules of clusters of version 8.1.1 to 8.1.3.

  1 2 3

  SET behavior_compat_options='disable_jsonb_exact_match'; select '2022' - '2'::text; ERROR: cannot delete from scalar

• If this parameter is not specified, fuzzy match is performed within the search scope, except for the jsonb type. This setting is compatible with the match rules of clusters of version earlier than 8.1.1.

  1 2 3 4 5 6

  SET behavior_compat_options=''; select '2022' - '2'::text; ?column? ---------- 2020 (1 row)

ORA

TD

MySQL

merge_into_with_trigger

Controls whether the MERGE INTO operation can be performed on tables with triggers. (This parameter is supported only by clusters of version 8.1.3.200 or later.)

• When this option is set, the MERGE INTO operation can be performed on tables with triggers. When the MERGE INTO operation is performed, the trigger on the table is not activated.
• If this option is not set, an error is reported when the MERGE INTO operation is performed on a table with triggers.

ORA

TD

MySQL

add_column_default_v_func

Controls whether expression in alter table add column default expression supports volatile functions. (This parameter is supported only by clusters of version 8.1.3.200 or later.)

• If this option is selected, expression in alter table add column default expression supports volatile functions.
• If this option is not selected, expression in alter table add column default expression does not support volatile functions. If expression contains volatile functions, an error will be reported during statement execution.

ORA

TD

MySQL

disable_full_group_by_mysql

Specifies whether to display non-aggregated function query columns after GROUP BY in a query. (This parameter is supported by clusters of version 8.2.0.101 or later.)

• If this option is specified, the query does not display any non-aggregated function query columns after GROUP BY.

  1 2 3 4 5 6 7

  SET behavior_compat_options='disable_full_group_by_mysql'; SELECT a,b FROM t1 GROUP BY a; a | b ---+--- 1 | 1 2 | 2 (2 rows)

• If this option is not specified, the query must display all non-aggregated function query columns after GROUP BY, or an error will be reported.

  1 2 3 4

  SET behavior_compat_options=''; SELECT a,b FROM t1 GROUP BY a; ERROR: column "t1.b" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a,b FROM t1 GROUP BY a;

MySQL

disable_gc_fdw_filter_partial_pushdown

Controls whether filter criteria are pushed down when querying data from a foreign table (of type gc_fdw) in a collaborative analysis scenario. (This parameter is supported only by clusters of version 8.2.1 or later.)

• When this option is specified, if there are factors in the filter criteria that do not meet the pushdown conditions (such as non-immutable functions), all filter criteria will not be pushed down to ensure the consistency of the result set. This behavior is compatible with clusters of version earlier than 8.2.1.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

  -- Create a table in the source cluster. CREATE TABLE t1(c1 INT, c2 INT, c3 INT) DISTRIBUTE BY HASH(c1); -- Create a foreign table with the same structure in the local cluster. CREATE SERVER server_remote FOREIGN DATA WRAPPER gc_fdw options(ADDRESS 'address', DBNAME 'dbname', USERNAME 'username', PASSWORD 'password'); CREATE FOREIGN TABLE t1(c1 INT, c2 INT, c3 INT) SERVER server_remote; -- Enable the parameter and see the pushdown behavior. SET behavior_compat_options = 'disable_gc_fdw_filter_partial_pushdown'; EXPLAIN (verbose on,costs off) SELECt * FROM t1 WHERE c1>3 AND c2 <100 AND now() - '20230101' < c3; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Streaming (type: GATHER) Output: c1, c2, c3 Node/s: All datanodes -> Foreign Scan on ca_schema.t1 Output: c1, c2, c3 Filter: ((t1.c1 > 3) AND (t1.c2 < 100) AND ((now() - '2023-01-01 00:00:00-08'::timestamp with time zone) < (t1.c3)::interval)) Remote SQL: SELECT c1, c2, c3 FROM ca_schema.t1 (7 rows)

• If this parameter is not set, the pushable part of the filter criteria will be pushed down to the source cluster for execution, while the non-pushable part will be executed in the local cluster, which can improve the efficiency of foreign table queries.

  1 2 3 4 5 6 7 8 9 10 11 12 13

  -- Disable this parameter and see the pushdown behavior. SET behavior_compat_options = ''; EXPLAIN (verbose on,costs off) SELECT * FROM t1 WHERE c1>3 AND c2 <100 AND now() - '20230101' < c3; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Streaming (type: GATHER) Output: c1, c2, c3 Node/s: All datanodes -> Foreign Scan on ca_schema.t1 Output: c1, c2, c3 Filter: ((now() - '2023-01-01 00:00:00-08'::timestamp with time zone) < (t1.c3)::interval) Remote SQL: SELECT c1, c2, c3 FROM ca_schema.t1 WHERE ((c1 > 3)) AND ((c2 < 100)) (7 rows)

ORA

TD

MySQL

ignore_unshipped_concurrent_update

Determines whether to ignore new tuples when the UPDATE or DELETE statement is executed in the current session if the statement is not pushed down and the tuples are updated by other sessions. By default, new tuples are not processed. (This parameter is supported only by clusters of version 8.2.1 or later.)

• If this parameter is specified, new tuples are ignored when the UPDATE or DELETE statement is executed in the current session. If the UPDATE or DELETE statement is successfully executed, data inconsistency occurs in concurrent update scenarios. This behavior is compatible with the behavior in versions earlier than 8.2.1.
• If this parameter is not set and the UPDATE or DELETE statement executed in the current session detects that tuples have been updated, the UPDATE or DELETE statement of the current session will be re-executed to ensure data consistency. The number of statement execution retries is controlled by the max_query_retry_times parameter.

ORA

TD

MySQL

disable_set_global_var_on_datanode

Controls whether the set_config function can be used to set global variables on DNs. (This parameter is supported only by clusters of version 8.2.1 or later.)

• When this parameter is set, the set_config function cannot be used to set global variables on DNs. By default, this behavior is compatible with the behavior in versions earlier than 8.2.1.
• If this parameter is not set, the set_config function can set global variables on DNs. As a result, the global variable values on CNs and DNs are inconsistent, and errors may occur when the read_global_var function is pushed down.

ORA

TD

MySQL

deparse_view_with_partition

Controls whether the view definition contains the partition clause when the table corresponding to the view is a partitioned table and the DML operation is performed on the view. (This parameter is supported only by clusters of version 8.2.1 or later.)

• When this parameter is set, the delivered DML statement contains the partition clause.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

  CREATE TABLE test_range_row(a int, d int, constraint con1 primary key(a, d)) WITH(orientation=row) DISTRIBUTE BY hash(a) PARTITION BY RANGE(d) ( PARTITION p1 values LESS THAN (60), PARTITION p2 values LESS THAN (75), PARTITION p3 values LESS THAN (90), PARTITION p4 VALUES LESS THAN (maxvalue) ); CREATE VIEW view_p1 AS SELECT * FROM test_range_row PARTITION(p1); SET behavior_compat_options = 'deparse_view_with_partition'; EXPLAIN (COSTS OFF, VERBOSE) INSERT INTO view_p1(a, d) SELECT 1,2; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Insert on updatable_view_basic_schema.test_range_row Node/s: All datanodes Node expr: test_range_row.a Remote query: INSERT INTO updatable_view_basic_schema.test_range_row PARTITION(p1) (a, d) VALUES ($1, $2) -> Result Output: 1, 2 (6 rows)

• If this parameter is not set, the delivered DML statement does not contain the partition clause.

  1 2 3 4 5 6 7 8 9 10

  SET behavior_compat_options = ''; EXPLAIN (COSTS OFF, VERBOSE) INSERT INTO view_p1(a, d) SELECT 1,2; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Insert on updatable_view_basic_schema.test_range_row Node/s: All datanodes Node expr: test_range_row.a Remote query: INSERT INTO updatable_view_basic_schema.test_range_row (a, d) VALUES ($1, $2) -> Result Output: 1, 2 (6 rows)

ORA

TD

MySQL

variadic_null_check

Whether variadic can transfer the NULL parameter. This function is disabled by default. (This parameter is supported only by clusters of version 8.2.1.300 or later.)

• When this parameter is set, passing NULL parameters to variadic is not allowed and will result in an error.

  1 2 3 4

  SET behavior_compat_options = 'variadic_null_check'; SELECT format ( 'array', VARIADIC NULL); ERROR: VARIADIC parameter must be an array

  NOTE:

  To be compatible with MySQL, enabling compat_concat_variadic does not take effect for the concat and concat_ws functions, and the NULL parameter can still be passed in.

• If this parameter is not set, NULL parameters can be passed to variadic.

  1 2 3 4 5 6

  SET behavior_compat_options = ''; SELECT format ( 'array', VARIADIC NULL); format -------- array (1 row)

ORA

TD

MySQL

enable_use_syscol_in_replicate_table

Specifies whether oid, ctid, tableoid, or xc_node_id can be used as filter, join, and having conditions during INSERT, UPDATE, MERGE INTO, and DELETE statements are executed on replication tables. This parameter is not set by default. This parameter is supported only by clusters of version 8.2.1.200 or later.

• If this parameter is not set and oid, ctid, tableoid, or xc_node_id is used as filter, join, or having conditions when the INSERT, UPDATE, MERGE INTO, or DELETE statements are executed on replication tables, the following error is reported:

  ERROR:  Can not use system column oid/ctid/tableoid/xc_node_id in Replication Table.

• When this parameter is set, the INSERT, UPDATE, MERGE INTO, and DELETE statements can be executed on replication tables using the system column id, ctid, tableoid, or xc_node_id.
  CAUTION:

  If oid, ctid, tableoid, or xc_node_id is used as filter, join, and having conditions when the INSERT, UPDATE, MERGE INTO, or DELETE statements are executed on partition tables, the statement may result in cluster core dumps. In this case, exercise caution when setting this parameter.

ORA

TD

MYSQL

enable_force_add_batch

Determines whether GaussDB(DWS) receives U packets in addbatch mode when support_batch_bind is set to on and enable_fast_query_shipping and enable_light_proxy are both set to off. This parameter is not set by default. This parameter is supported only by clusters of version 8.2.1.200 or later.

• If this parameter is not set, support_batch_bind is set to on, and enable_fast_query_shipping and enable_light_proxy are both set to off, GaussDB(DWS) does not receive U packets in addbatch mode.
• If this parameter is set, support_batch_bind is set to on, and enable_fast_query_shipping and enable_light_proxy are both set to off, GaussDB(DWS) receives U packets in addbatch mode. However, packets are imported to the database slowly, which may cause insufficient memory. So, exercise caution when setting this parameter.

ORA

TD

MYSQL

disable_mergesort_without_material

Controls whether the current stream segment contains materialized operators. If it is, merge sort is used. This parameter is supported only by clusters of version 8.2.1.100 or later.

• If this parameter is set and the current stream segment contains materialized operators (material, sort, agg, and CteScan), merge sort can be used. Otherwise, merge sort cannot be used.
• If this parameter is unset, there is no need to verify whether the current stream segment contains materialized operators to determine whether to use merge sort.

ORA

TD

MYSQL

enable_pushdown_groupingset_subquery

Specifies whether conditions from the outer query that are only related to a subquery can be pushed down to the subquery when the subquery contains a grouping set. This parameter is supported only by clusters of version 8.2.1.100 or later.

• If the subquery contains grouping sets and this parameter is set, the conditions in the outer query cannot be pushed down to the subquery.
• If the subquery contains grouping sets and this parameter is not set, the conditions in the outer query can be pushed down to the subquery.

ORA

TD

MYSQL

enable_whole_row_var

This parameter mainly involves two scenarios: 1. controlling whether tables or views are allowed to appear in SQL expressions, including but not limited to the target list of queries, GROUP BY lists, etc.; 2. controlling whether non-table records are allowed to appear in SQL expressions. This parameter is supported only by clusters of version 8.3.0 or later.

• When this parameter is set, tables or views are allowed to appear in SQL expressions.

  1 2 3 4 5 6 7 8 9 10

  SET behavior_compat_options = 'enable_whole_row_var'; SELECT a1 FROM t a1; a1 ---- (0 rows) SELECT t FROM (SELECT 1) as t; t ----- (1) (1 rows)

• If this parameter is unset, tables or views are not allowed to appear in SQL expressions.

  1 2 3 4 5

  SET behavior_compat_options = ''; SELECT a1 FROM t a1; ERROR: Table or view cannot appear in expression. Table/view name: t, alias: a1. Please check targetList, groupClause etc. SELECT t FROM (SELECT 1) as t; ERROR: Non-table records cannot appear in expression. alias: t. Please check targetList, groupClause etc.

ORA

TD

MYSQL

enable_unknown_datatype

Specifies whether tables containing unknown columns can be created. This parameter is supported only by clusters of version 8.3.0 or later.

• When this parameter is set, tables containing unknown columns can be created.

  1 2 3 4 5

  SET behavior_compat_options = 'enable_unknown_datatype'; CREATE TABLE t(a unknown); WARNING: column "a" has type "unknown" DETAIL: Proceeding with relation creation anyway. CREATE TABLE

• If this parameter is unset, tables containing unknown columns cannot be created. If the table creation SQL contains an unknown column, an error will be reported.

  1 2 3

  SET behavior_compat_options = ''; create table t(a unknown); ERROR: column "a" has type "unknown"

ORA

TD

MYSQL

alter_distribute_key_by_partition

Specifies whether INSERT INTO is executed by partition when ALTER TABLE is used to modify the distribution column of a partitioned table. This parameter is supported only by clusters of version 8.2.1.210 or later.

• If this parameter is set, INSERT INTO is executed by partition. The memory usage decreases but the performance deteriorates.

• If this parameter is unset, INSERT INTO is performed on the entire partitioned table. The performance is good but the memory usage is high.

ORA

TD

MYSQL

disable_update_returning_check

Specifies whether to prevent multiple joins when a replication table is updated with the returning statement. This parameter is supported only by clusters of version 8.3.0 or later.

• If the parameter is not set, the following error is reported when updating a replication table with a returning statement and involving multiple joins:

  ERROR: Unsupported FOR UPDATE replicated table joined with other table.

• Setting this parameter ensures backward compatibility with earlier versions. However, when updating a replication table with a returning statement and involving multiple joins, there may be inconsistencies in the result set.

ORA

TD

MYSQL

check_function_shippable

Controls the check of the custom plpgsql/SQL function attributes. This parameter is supported only by clusters of version 8.3.0 or later.

• If this parameter is not specified, the IMMUTABLE/STABLE/VOLATILE attributes of a user-defined function are not checked.
• If this parameter is specified, the IMMUTABLE/STABLE/VOLATILE attributes of user-defined functions are checked based on the following principles:
  • Whitelist: For the three functions in DBMS_OUTPUT, skip the check_function_shippable check.
  • If a user-defined function contains DML statements and the outer layer is IMMUTABLE or SHIPPABLE, the function is pushed down. As a result, an error is reported.
  • If the outer layer of a user-defined function is SHIPPABLE and the inner layer is IMMUTABLE, the function passes the check.
  • If the outer layer of a user-defined function is SHIPPABLE, the inner layer is SHIPPABLE and not IMMUTABLE, the function passes the check.
  • If the outer layer of a user-defined function is SHIPPABLE but the inner layer is none of the above, an error is reported.

For example, when this parameter is specified, an error is reported in the following scenarios:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE OR replace function func_ship(a int)
returns int
LANGUAGE plpgsql
NOT FENCED  SHIPPABLE
AS $function$
begin
perform test_ship();
return a;
EXCEPTION WHEN OTHERS THEN
return a;
end $function$;
select func_ship(a) from tt3;
ERROR:  parent function is shippable but child is not immutable or shippable.

ORA

TD

MYSQL

enable_full_string_agg

Specifies how string_agg(a, delimeter) over (partition by b order by c) behaves in different situations, such as using full or incremental aggregation in the window. This parameter is supported only by clusters of version 8.3.0 or later.

If this parameter is not set, incremental aggregation is used. If this parameter is set, full aggregation is used. By default, this parameter is not set.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE string_agg_dn_col(c1 int, c2 text) WITH(orientation = column) distribute by hash(c1); 
INSERT INTO string_agg_dn_col values(1, 'test'); 
INSERT INTO string_agg_dn_col values(1, 'haidian');
INSERT INTO string_agg_dn_col values(1, 'nanjing');
SELECT t.c1 AS c1, string_agg(t.c2, ',') OVER(PARTITION BY t.c1 ORDER BY t.c2) AS c2 FROM string_agg_dn_col t ORDER BY c2;
 c1 |          c2          
----+----------------------
  1 | haidian
  1 | haidian,nanjing
  1 | haidian,nanjing,test
(3 rows)

SET behavior_compat_options='enable_full_string_agg';
SELECT t.c1 AS c1, string_agg(t.c2, ',') OVER(PARTITION BY t.c1 ORDER BY t.c2) AS c2 FROM string_agg_dn_col t ORDER BY c2;
 c1 |          c2          
----+----------------------
  1 | haidian,nanjing,test
  1 | haidian,nanjing,test
  1 | haidian,nanjing,test
(3 rows)

ORA

TD

MYSQL

enable_banker_round

Specifies how numeric types round their values, using the rounding or banker method. This parameter is supported only by clusters of version 8.3.0 or later.

Behaviors controlled by parameters include:

• Type conversion working when INSERT INTO and ::xxx specify a type, such as integer types (int1, int2, int4, int8), any precision types (decimal, numeric, number), or money types.
• Rounding and conversion functions for the numeric type: round(xxx.xx,s), cast('xxx.xx',numeric), or to_char(xxx.xx,'xxx').
• Mathematical calculation of the numeric type.

• If this parameter is set, rounding uses the banker method.

SET behavior_compat_options = enable_banker_round;
SELECT 1.5::int1,1.5::int2,1.5::int4,1.5::int8,1.5::numeric(10,0),1.115::money;
 int1 | int2 | int4 | int8 | numeric | money 
------+------+------+------+---------+-------
 2    |    2 |    2 |    2 |       2 | $1.12
SELECT 0.5::int1,0.5::int2,0.5::int4,0.5::int8,0.5::numeric(10,0),1.105::money;
 int1 | int2 | int4 | int8 | numeric | money 
------+------+------+------+---------+-------
 0    |    0 |    0 |    0 |       0 | $1.10
SELECT round(1.05,1),round(1.15,1),cast('1.05',numeric(10,1)),cast('1.15',numeric(10,1)),to_char(1.05,'9D9'),to_char(1.15,'9D9');
 round | round | numeric | numeric | to_char | to_char 
-------+-------+---------+---------+---------+---------
   1.0 |   1.2 |     1.0 |     1.2 |  1.0    |  1.2

• If this parameter is set, rounding uses round-off method.

SET behavior_compat_options = '';
SELECT 1.5::int1,1.5::int2,1.5::int4,1.5::int8,1.5::numeric(10,0),1.115::money;
 int1 | int2 | int4 | int8 | numeric | money 
------+------+------+------+---------+-------
 2    |    2 |    2 |    2 |       2 | $1.12
SELECT 0.5::int1,0.5::int2,0.5::int4,0.5::int8,0.5::numeric(10,0),1.105::money;
 int1 | int2 | int4 | int8 | numeric | money 
------+------+------+------+---------+-------
 1    |    1 |    1 |    1 |       1 | $1.11
SELECT round(1.05,1),round(1.15,1),cast('1.05',numeric(10,1)),cast('1.15',numeric(10,1)),to_char(1.05,'9D9'),to_char(1.15,'9D9');
 round | round | numeric | numeric | to_char | to_char 
-------+-------+---------+---------+---------+---------
   1.1 |   1.2 |     1.1 |     1.2 |  1.1    |  1.2

ORA

TD

MYSQL

enable_int_division_by_truncate

Controls whether the integer division behavior result set returns integers or floating point numbers and the option is compatible with PG or ORA behaviors.

• If this parameter is set, the integer division result is an integer, the decimal places are truncated, and this parameter is compatible with PG behaviors.

  1 2 3 4 5 6

  SET behavior_compat_options = 'enable_int_division_by_truncate'; SELECT 8::int8 / 3::int8, 8::int4 / 3::int4, 8::int2 / 3::int2, 8::int1 / 3::int1; ?column? | ?column? | ?column? | ?column? ----------+----------+----------+---------- 2 | 2 | 2 | 2 (1 row)

• If this parameter is unset, the integer division result returns a floating point number, including decimal places, and this parameter is compatible with ORA behaviors.

  1 2 3 4 5 6

  SET behavior_compat_options = ''; SELECT 8::int8 / 3::int8, 8::int4 / 3::int4, 8::int2 / 3::int2, 8::int1 / 3::int1; ?column? | ?column? | ?column? | ?column? ------------------+------------------+------------------+------------------ 2.66666666666667 | 2.66666666666667 | 2.66666666666667 | 2.66666666666667 (1 row)

ORA

TD

MYSQL

unsupported_set_function_case

Specifies whether multiple result set functions can be returned in a CASE WHEN condition. This parameter is supported only by clusters of version 8.3.0.100 or later. This parameter is enabled by default for newly installed clusters of version 9.1.0 or later.

• If this parameter is set, column storage does not support multiple result set functions in a CASE WHEN condition.

  CREATE TABLE t1(id int, c1 text) with(orientation=column);
  INSERT INTO t1 values(1, 'a#1');
  SET behavior_compat_options = 'unsupported_set_function_case';
  SELECT CASE split_part(regexp_split_to_table(c1, E'\,'),'#',1) when 'a' then c1 else null end from t1;
  ERROR:  set-valued function called in context that cannot accept a set

• If this parameter is not set, column storage supports multiple result set functions in a CASE WHEN condition.

  SET behavior_compat_options = '';
  SELECT CASE split_part(regexp_split_to_table(c1, E'\,'),'#',1) when 'a' then c1 else null end from t1;
   case
  ------
   a#1
  (1 row)

ORA

TD

MYSQL

enable_change_search_path

Specifies whether the search path can be modified after forming a general plan generic_plan. This is supported only by clusters of version 9.1.0 or later.

• When this parameter is not set, if a new search path is set and an EXECUTE statement is executed, the database will still search for the corresponding table under the original schema of the table.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

  CREATE SCHEMA s1 CREATE TABLE abc(f1 INT); CREATE SCHEMA s2 CREATE TABLE abc(f1 INT); SET search_path = s1; INSERT INTO s1.abc VALUES(123);INSERT INTO s2.abc VALUES(456); SET search_path = s1; PREPARE p1 AS SELECT f1 FROM abc; EXECUTE p1; f1 ----- 123 (1 row) SET search_path = s2; SELECT f1 FROM abc; f1 ----- 456 (1 row) EXECUTE p1; f1 ----- 123 (1 row)

• When this parameter is set, if a new search path is set and an EXECUTE statement is executed, the database will search for the corresponding table in the newly set search path.

  1 2 3 4 5 6 7 8 9 10 11 12 13

  SET behavior_compat_options = 'enable_change_search_path'; EXECUTE p1; f1 ----- 456 (1 row) SET search_path = s1; EXECUTE p1; f1 ----- 123 (1 row)

TD

enable_varchar_to_nvarchar2

Specifies whether varchar fields created or updated through DDL statements are automatically switched to nvarchar2 fields. This is supported only by clusters of version 9.1.0 or later.

• If this parameter is set, varchar fields created or updated through DDL statements are automatically switched to nvarchar2 fields.
• If this parameter is unset, varchar fields created or updated through DDL statements are not automatically switched to nvarchar2 fields.

ORA

TD

MYSQL

normalize_negative_zero

Specifies whether the ceil() and round() functions return –0 when processing specific values of the float type. This parameter is supported only by clusters of version 8.1.3.333 or later.

• When this parameter is set, the ceil() function returns 0 when processing (–1,0), and the round() function returns 0 when processing [–0.5, 0).

  1 2 3 4 5 6 7 8 9 10 11

  SET behavior_compat_options='normalize_negative_zero'; SELECT ceil(cast(-0.1 as float)); ceil ------ 0 (1 row) SELECT round(cast(-0.1 as FLOAT)); round ------- 0 (1 row)

• When this parameter is not set, the ceil() function returns –0 when processing (–1,0), and the round() function returns –0 when processing [–0.5, 0).

  1 2 3 4 5 6 7 8 9 10 11

  SET behavior_compat_options = ''; SELECT ceil(cast(-0.1 as FLOAT)); ceil ------ -0 (1 row) SELECT round(cast(-0.1 as FLOAT)); round ------- -0 (1 row)

ORA

TD

MySQL

disable_client_detection_commit

Specifies whether to check there is a connection with the client before each transaction is committed. If the connection does not exist, an error is reported, the transaction is rolled back, and data duplication caused by repeated issuance due to disconnection is prevented. This parameter is supported only by clusters of version 8.1.3.333 or later.

• If this parameter is not set, the system checks the existence of the client connection before each transaction is committed.
• If this parameter is set, the system does not check the existence of the client connection before each transaction is committed.

ORA

TD

MySQL

change_illegal_char

Specifies the display of illegal UTF8 characters when reading with GDS. This parameter is supported only by clusters of version 8.3.0.100 or later.

When this parameter is enabled, illegal UTF8 characters that are incompatible with GDS are displayed as "�" instead of "?".

MySQL

row_use_pseudo_name

Specifies whether row-related expressions generate pseudo column names for anonymous columns. This parameter is supported only by clusters of version 9.1.0.100 or later.

• When this parameter is not set, if there is a corresponding real column name in the row expression, the real column name is used. If it is an anonymous column, pseudo column names f1, f2...fn are generated.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT row_to_json(row(1,'foo'));
     row_to_json
---------------------
 {"f1":1,"f2":"foo"}
(1 row)

CREATE TABLE json_tbl(id INT, x INT, y text) WITH (ORIENTATION = COLUMN);
INSERT INTO json_tbl VALUES (1, 1, 'txt1'), (2, 2, 'txt2'), (3, 3, 'txt3');
SELECT to_json(t.*) FROM json_tbl t;
          to_json
---------------------------
 {"id":3,"x":3,"y":"txt3"}
 {"id":1,"x":1,"y":"txt1"}
 {"id":2,"x":2,"y":"txt2"}
(3 rows)

• When this parameter is set, pseudo column names f1, f2...fn are generated for anonymous columns under the column storage condition in the row expression.

1
2
3
4
5
6
7
8

SET behavior_compat_options='ROW_USE_PSEUDO_NAME';
SELECT to_json(t.*) FROM json_tbl t;
           to_json
-----------------------------
 {"f1":3,"f2":3,"f3":"txt3"}
 {"f1":1,"f2":1,"f3":"txt1"}
 {"f1":2,"f2":2,"f3":"txt2"}
(3 rows)

ORA

TD

MySQL