System Functions

GaussDB is compatible with most MySQL system functions, but there are some differences. Only system functions in M-compatible mode can be used. System functions of the original GaussDB cannot be used in case of unexpected results. Currently, some system functions in GaussDB with the same names as those in MySQL are not supported in M-compatible mode. For some of them, the message indicating that they are not supported in M-compatible mode is displayed. Other functions still retain the behaviors of the original GaussDB system functions. Do not use these functions in case of unexpected results. The following table lists the functions with the same name.

**Table 1** Same-name functions for which a message indicating that they are not supported in M-compatible mode is displayed
cot	isEmpty	last_insert_id	mod	octet_length
overlaps	point	radians	regexp_instr	regexp_like
regexp_replace	regexp_substr	stddev_pop	stddev_samp	var_pop
var_samp	variance	-	-	-

**Table 2** Same-name functions that retain the behaviors of the original GaussDB system functions in M-compatible mode
ceil	decode	encode	format	instr
position	round	stddev	row_num	-

When the function regexp_instr, regexp_like, regexp_replace, or regexp_substr is used, if the value of the m_format_dev_version parameter is 's2' or a value indicating a later version and the value of the m_format_behavior_compat_options parameter contains 'enable_conflict_funcs', an error is reported, indicating that the behavior is not supported in M-compatible mode. Other behaviors of these functions are the same as those of functions with the same name in "SQL Reference > Functions and Operators > Character Processing Functions and Operators" in Developer Guide.
MySQL allows you to add user-defined functions to the database through the loadable functions. When such functions are called, aliases can be specified in the input parameters of the functions. GaussDB does not support loadable functions. When a function is called, aliases cannot be specified for input parameters of the function.
In M-compatible mode, system functions have the following common differences:
- The return value type of a system function is the same as that of MySQL only when the node type of the input parameter is Var (table data) or Const (constant input). In other cases (for example, the input parameter is a calculation expression or function expression), the return value type may be different from that of MySQL.
- In the table query scenario where LIMIT and OFFSET are used at the same time, execution layer mechanisms of GaussDB and MySQL are different. GaussDB calls functions line by line. Therefore, if an error occurs, it is reported and the execution is interrupted. However, MySQL does not execute functions line by line. Therefore, errors are not reported line by line and the execution is not interrupted, which may lead to inconsistent returned results.
- Calling system functions by pg_catalog.func_name() is not recommended. If the called function has input parameters in the format of syntax (such as SELECT substr('demo' from 1 for 2)), an error may occur when the function is called.