Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Statement/Type-Related Parameters

The following are parameters related to SQL statements and data types supported by CUBRID. The type and value range for each parameter are as follows:

Parameter Name

Type

Default Value

Min. Value

Max. Value

add_column_update_hard_default

bool

no

 

 

alter_table_change_type_strict

bool

no

 

 

ansi_quotes

bool

yes

 

 

block_ddl_statement

bool

no

 

 

block_nowhere_statement

bool

no

 

 

compat_numeric_division_scale

bool

no

 

 

default_week_format

int

0

 

 

group_concat_max_len

int

1024

4

33554432

intl_check_input_string

bool

no

intl_date_lang

string

intl_number_lang

string

no_backslash_escapes

bool

yes

 

 

only_full_group_by

bool

no

 

 

oracle_style_empty_string

bool

no

 

 

pipes_as_concat

bool

yes

 

 

plus_as_concat

bool

yes

 

 

require_like_escape_character

bool

no

 

 

return_null_on_function_errors

bool

no

 

 

string_max_size_bytes

int

1048576

64

33554432

unicode_input_normalization

bool

no

 

 

unicode_output_normalization

bool

no

 

 

add_column_update_hard_default

add_column_update_hard_default is a parameter used to configure whether or not to provide the hard_default value as the input value for a column when you add a new column to the ALTER TABLE … ADD COLUMN clause.

If a value for this parameter is set to yes, enter a new input value of a column as a hard default value when you have NOT NULL constraints but no DEFAULT constraints. If the parameter value is set to no, enter NULL, even if NOT NULL constraints exist. If a value for this parameter is set to yes and there is no hard default value for the column type to add, an error message will be displayed and a roll-back occurs. For the hard default for each type, see the CHANGE Clause of the ALTER TABLE statement.

-- add_column_update_hard_default=no

 

CREATE TABLE tbl (i INT);

INSERT INTO tbl VALUES (1),(2);

ALTER TABLE tbl ADD COLUMN j INT NOT NULL;

 

SELECT * FROM TBL;

 

            i          j

========================

            2       NULL

            1       NULL

 

-- add_column_update_hard_default=yes

 

CREATE TABLE tbl (i int);

INSERT INTO tbl VALUES (1),(2);

ALTER TABLE tbl ADD COLUMN j INT NOT NULL;

 

SELECT * FROM tbl;

 

            i          j

=========================

            2          0

            1          0

alter_table_change_type_strict

alter_table_change_type_strict is a parameter used to configure whether or not to allow the conversion of column values according to the type change, and the default value is no. If a value for this parameter is set to no, the value may be changed when you change the column types or when you add NOT NULL constraints; if it is set to yes, the value is not changed. For details, see CHANGE Clause in the CHANGE/MODIFY Clause.

ansi_quotes

ansi_quotes is a parameter used to enclose symbols and character string to handle identifiers. The default value is yes. If this parameter value is set to yes, double quotations are handled as identifier symbols and single quotations are handled as character string symbols. If it is set to no, both double and single quotations are handled as character string symbols.

block_ddl_statement

block_ddl_statement is a parameter used to limit the execution of DDL (Data Definition Language) statements by the client. If the parameter is set to no, the given client is allowed to execute DDL statements. If it is set to yes, the client is not permitted to execute DDL statements. The default value is no.

block_nowhere_statement

block_nowhere_statement is a parameter used to limit the execution of UPDATE/DELETE statements without a condition clause (WHERE) by the client. If the parameter is set to no, the given client is allowed to execute UPDATE/DELETE statements without a condition clause. If it is set to yes, the client is not permitted to execute UPDATE/DELETE statements without a condition clause. The default value is no.

compat_numeric_division_scale

compat_numeric_division_scale is a parameter used to configure the scale to be displayed in the result (quotient) of a division operation. If the parameter is set to no, the scale of the quotient is 9 if it is set to yes, the scale is determined by that of the operand. The default value is no.

default_week_format

default_week_format is a parameter used to configure default value for the mode attribute of the WEEK function. The default value is 0. For details, see WEEK Function.

intl_check_input_string

intl_check_input_string is a parameter used to whether to check that string entered is correctly corresponded to character set used. The default value is no. If this value is no and character set is UTF-8 and incorrect data is enter which violate UTF-8 byte sequence, it can show abnormal behavior or database server and applications can be termminated abnormally. However, if it is guaranteed this problem does not happen, it has advantage in performance not to do it.

UTF-8 and EUC-KR can be checked; ISO-8859-1 is one-byte encoding so it does not have to be checked because every byte is valid.

group_concat_max_len

group_concat_max_len is a parameter used to limit the return value size of the GROUP_CONCAT function. The default value is 1024 bytes, the minimum value is 4 bytes, and the maximum value is 33,554,432 bytes. If the return value of the GROUP_CONCAT function exceeds the limitation, NULL will be returned.

intl_check_input_string

intl_check_input_string is a parameter used to whether to check that string entered is correctly corresponded to character set used. The default value is no. If this value is no and character set is UTF-8 and incorrect data is enter which violate UTF-8 byte sequence, it can show abnormal behavior or database server and applications can be termminated abnormally. However, if it is guaranteed this problem does not happen, it has advantage in performance not to do it.

UTF-8 and EUC-KR can be checked; ISO-8859-1 is one-byte encoding so it does not have to be checked because every byte is valid.

intl_date_lang

intl_date_lang is a parameter used to input/output the values of TIME, DATE, DATETIME, and TIMESTAMP. If language name is omitted, it specifies a locale format of string of localized calendar (month, weekday, and AM/PM).

The values allowed are as follows: Note that to use all values, locale library should be configured except built-in locale. For configuring locale, see Administrator Guide > Locale Setting.

Language

Locale Name of Language

English

en_US

German

de_DE

Spanish

es_ES

French

fr_FR

Italian

it_IT

Japanese

ja_JP

Cambodian

km_KH

Korean

ko_KR

Turkish

tr_TR

Vietnamese

vi_VN

Chinese

zh_CN

The function recognizing input string based on calendar format of specified language is as follows:

  • TO_DATE
  • TO_TIME
  • TO_DATETIME
  • TO_TIMESTAMP
  • STR_TO_DATE

The function outputting string based on calendar format of specified language is as follows:

  • TO_CHAR(date)
  • DATE_FORMAT
  • TIME_FORMAT
intl_number_lang

intl_number_lane  is a parameter used to specify locale applied when numeric format is assiged to input/output string in the function where a string is converted to number or number is converted to string. A delimiter and decimal symbol are used for numeric localization. In general, a comma and period are used; however, it can be changeable based on locale. For example, while number 1000.12 is used as 1,000.12 in most locale, it used as 1.000,12 in , tr_TR locale.

The function recognizing input string based on calendar format of specified language is as follows:

  • TO_NUMBER

The function outputting string based on calendar format of specified language is as follows:

  • FORMAT
  • TO_CHAR(number)
no_backslash_escapes

no_backslash_escapes is a parameter used to configure whether or not to use backslash () as an escape character, and the default value is yes. If a value for this parameter is set to no, backslash () will be used as an escape character; if it is set to yes, backslash () will be used as a normal character. For details, see Escape Special Characters.

only_full_group_by

only_full_group_by is a parameter used to configure whether to use extended syntax about using GROUP BY statement.

If this parameter value is set to no, an extended syntax is applied thus, a column that is not specified in the GROUP BY statement can be specified in the SELECT column list. If it is set to yes, a column that is only specified in the GROUP BY statement can be the SELECT column list.

The default value is no. Therefore, specify the only_full_group_by parameter value to yes to execute queries by SQL standards. Because the extended syntax is not applied in this case, an error below is displayed.

ERROR: Attributes exposed in aggregate queries must also appear in the group by clause.

oracle_style_empty_string

oracle_style_empty_string is a parameter used to improve compatibility with other DBMS (Database Management Systems) and specifies whether or not to process empty strings as NULL as in Oracle DBMS. If the oracle_style_empty_string parameter is set to no, the character string is processed as a valid string if it is set to yes, the empty string is processed as NULL.

pipes_as_concat

pipes_as_concat is a parameter used to configure how to handle a double pipe symbol. The default value is yes. If this parameter value is set to yes, a double pipe symbol is handled as a concatenation operator if no, it is handled as the OR operator.

plus_as_concat

plus_as_concat is a parameter used to configure the plus (+) operator, and the default value is yes. If a value for this parameter is set to yes, the plus (+) operator will be interpreted as a concatenation operator; if it is set to no, the operator will be interpreted as a numeric operator.

-- plus_as_concat = yes

SELECT '1'+'1';

         '1'+'1'

======================

         '11'  SELECT '1'+'a';

 

         '1'+'a'

======================

         '1a'

 

-- plus_as_concat = no

SELECT '1'+'1';

                '1'+'1'

==========================

 2.000000000000000e+000

 

SELECT '1'+'a';

 

ERROR: Cannot coerce 'a' to type double.

require_like_escape_character

require_like_escape_character is parameter used to configure whether or not to use an ESCAPE character in the LIKE clause, and the default value is no. If a value for this parameter is set to yes and a value for no_backslash_escapes is set to no, backslash () will be used as an ESCAPE character in the strings of the LIKE clause, otherwise you should specify an ESCAPE character by using the LIKE… ESCAPE clause. For details, see LIKE Predicate.

return_null_on_function_errors

return_null_on_function_errors is a parameter used to define actions when errors occur in some SQL functions, and the default value is no. If a value for this parameter is set to yes, NULL is returned; if it is set to no, an error is returned when the error occurs in functions, and the related message is displayed.

The following SQL functions are affected by this system parameter.

  • ADDDATE
  • ADDTIME
  • DATEDIFF
  • DAY
  • DAYOFMONTH
  • DAYOFWEEK
  • DAYOFYEAR
  • FROM_DAYS
  • FROM_UNIXTIME
  • HOUR
  • LAST_DAY
  • MAKEDATE
  • MAKETIME
  • MINUTE
  • MONTH
  • QUARTER
  • SEC_TO_TIME
  • SECOND
  • TIME
  • TIME_TO_SEC
  • TIMEDIFF
  • TO_DAYS
  • WEEK
  • WEEKDAY
  • YEAR

-- return_null_on_function_errors=no

 

SELECT YEAR('12:34:56)'

 

ERROR: Conversion error in time format.

 

-- return_null_on_function_errors=yes

 

SELECT YEAR('12:34:56);

 

     year('12:34:56')

=====================

   NULL

string max_size_bytes

string_max_size_bytes is a parameter used to define the maximum byte allowable in string functions or operators. The default value is 1048576 (1 MB). The minimum value is 64 bytes and the maximum value is 33,554,432 bytes (32 MB).

The functions and operators affected by this parameter are as follows:

  • SPACE
  • CONCAT
  • CONCAT_WS
  • '+': Operand of string
  • REPEAT
  • GROUP_CONCAT: This function is affected not only by string_max_size_bytes parameter but also by group_concat_max_len.
  • INSERT function
unicode_input_normalization

unicode_input_normalization is a parameter used to whether to input unicode stored in system level. The default value is no

In gernal, unicode text can be stored in "fully composed" or "fully decomposed". When character 'Ä' has 00C4 (if it is encoded in UTF-8, it becomes 2 bytes of C3 84) which is only one code point. In "fully decomposed" mode, it has tow code points/characters. It is 0041 (character "A" and 0308(COMBINING DIAERESIS). In case of UTF-8 encoding, it becomes 3 bytes of 41 CC 88.

CUBRID can work with fully composed unicode. For clients which have fully decomposed texts, configure the value of unicode_input_normalization to yes so that it can be converted to fully composed mode; and then it can be reverted to fully decomposed mode. For normalization of unicode encapsulation of CUBRID, compatibility equivalence is not applied. In general, normalization of unicode is not possible to revert after composition, CUBRID supports revert for characters an many as possible, it applies normalization of unicode encapsulation. The characteristics of CUBRID normalization are as follows:

  • In case of language specific, normalization does not depend on locale. If one or more locale cana be used, this means every CAS/CSQL process, not CUBRID server. The unicode_input_normalization system parameter determines whether composition of input codes by normalization in system level. The unicode_output_normalization system parameter determines whether composition of output codes by normalization in system level.
  • Collation and normalization does not have direct relationship. Even though the value of unicode_input_normalization is no, the string of extensible collation (utf8_de_exp, utf8_jap_exp, utf8_km_exp) is properly sorted fully decomposed mode, it is not intended; it is side-effect of UCA(Unicode Collation Algorithm). The extensible collation is implemented only with fully composed texts.
  • In CUBRID, composition and decomposition for normalization does not work separately. It is generally used when unicode_input_normalization and  unicode_output_normalization are yes. In this case, codes entered from clients are stored in composed mode and and output in decomposed mode.

For details, see Administrator Guide > Globalization > Overview.

unicode_output_normalization

unicode_output_normalization is a parameter used to whether to output unicode stored in system level. The default value is no. For details, see the unicode_input_normalization description above.