System Function Compatibility Overview

GaussDB is compatible with most MySQL system functions, but there are some differences. You are advised to use system functions supported in M-compatible mode. Do not use the original GaussDB system functions.

Currently, there are system functions in GaussDB with the same names as MySQL system functions, but these functions are not yet supported in M-compatible mode; functions listed in Table 1 will prompt users that they are not supported in M-compatible mode, while functions listed in Table 2 retain the behavior of the original GaussDB system functions. The behavior of functions with the same name is greatly different from that of MySQL. Therefore, you are advised to avoid using them but use only system functions in M-compatible mode.

**Table 1** Same-name functions for which a message indicating that they are not supported in M-compatible mode is displayed
isEmpty	variance	overlaps	point	stddev_pop
stddev_samp	var_pop	var_samp	-	-

**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_number	regexp_instr
regexp_like	regexp_replace	regexp_substr	-	-

When the value of the parameter m_format_dev_version is s2 or later and the value of m_format_behavior_compat_options includes enable_conflict_funcs, the behavior of the functions with the same name in the following table will be modified to that in M-compatible mode.

**Table 3** Same-name functions controlled by GUC parameters in M-compatible mode
ceil	format	instr	position	row_number

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.
In M-compatible mode, system functions have the following 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 pg_catalog.substr('demo' FROM 1 FOR 2)), an error may occur when the function is called.