Updated on 2024-05-14 GMT+08:00

Aggregate Functions

Table 1 Aggregate functions

No.

MySQL

GaussDB

Difference

1

GROUP_CONCAT()

Supported.

  1. If the group_concat parameter contains both the DISTINCT and ORDER BY syntaxes, all expressions following ORDER BY must be in the DISTINCT expression.
  2. group_concat(... order by Number) does not indicate the sequence of the parameter. The number is only a constant expression, which is equivalent to no sorting.
  3. The data type of the return value of group_concat is always text regardless of the data type of the parameter. For MySQL, if group_concat contains binary parameters, the return value is binary. In other cases, the return value is a character string. If the return value length is greater than 512 bytes, the data type is a character large object or binary large object.
  4. The value of group_concat_max_len ranges from 0 to 1073741823. The maximum value is smaller than that of MySQL.

2

DEFAULT()

Supported.

  1. The default value of a column is an array. GaussDB returns an array. MySQL does not support the array type.
  2. GaussDB columns are hidden columns (such as xmin and cmin). The function returns a null value.
  3. GaussDB supports default values of partitioned tables, temporary tables, and multi-table join query.
  4. GaussDB supports the query of nodes whose column names contain character string values (indicating names) and A_Star nodes (indicating that asterisks [*] appear), for example, default(tt.t4.id) and default(tt.t4.*). For invalid query column names and A_Star nodes, the error information reported by GaussDB is different from that reported by MySQL.
  5. When the default value of a column is created in GaussDB, the range of the column type is not verified. As a result, an error may be reported when the default function is used.
  6. If the default value of a column is a function expression, the default function in GaussDB returns the calculated value of the default expression of the column during table creation. The default function in MySQL returns NULL.