Help Center/ GaussDB/ MySQL Compatibility(Centralized)/ MySQL Compatibility in M-Compatible Mode/ System Functions/ Aggregate Functions
Updated on 2024-12-06 GMT+08:00
View PDF
Share

Aggregate Functions

Table 1 Aggregate functions

No.

MySQL

GaussDB

Difference

1

AVG()

Supported, with differences.
  • In GaussDB, if DISTINCT is specified and the SQL statement contains a GROUP BY clause, the result sequence is not guaranteed.
  • In GaussDB, if the columns in expr are of the BIT, BOOL, or integer type and the sum of all rows exceeds the range of BIGINT, overflow occurs, reversing integers.
  • In GaussDB, the behavior is different when the input parameter of the AVG function is of the TEXT or BLOB type.
    • In MySQL 5.7, the return value type of AVG(TEXT/BLOB) is MEDIUMTEXT. In MySQL 8.0, the return value type of AVG(TEXT/BLOB) is DOUBLE.
    • In GaussDB, the return value type of AVG(TEXT/BLOB) is the same as that in MySQL 8.0.

2

BIT_AND()

Supported.
When the input parameter of the BIT_AND function is NULL and the BIT_AND function is nested by other functions, the result is –1 in MySQL 5.7 and NULL in MySQL 8.0. In GaussDB, the function nesting is the same as that in MySQL 8.0. 
-- GaussDB:
m_db=# SELECT acos(bit_and(null));
 acos 
------

(1 row)
-- MySQL 5.7:
mysql> SELECT acos(bit_and(null));
+---------------------+
| acos(bit_and(null)) |
+---------------------+
|   3.141592653589793 |
+---------------------+
1 row in set (0.03 sec)

-- MySQL8.0
mysql> SELECT acos(bit_and(null));
+---------------------+
| acos(bit_and(null)) |
+---------------------+
|                NULL |
+---------------------+
1 row in set (0.01 sec)

3

BIT_OR()

Supported.

-

4

BIT_XOR()

Supported.

-

5

COUNT()

Supported, with differences.

In GaussDB, if DISTINCT is specified and the SQL statement contains a GROUP BY clause, the result sequence is not guaranteed.

GaussDB supports the count(tablename.*) syntax, but MySQL does not.

6

GROUP_CONCAT()

Supported, with differences.
  • In GaussDB, if DISTINCT is specified and the SQL statement contains a GROUP BY clause, the result sequence is not guaranteed.
  • In GaussDB, if the parameters in GROUP_CONCAT contain both the DISTINCT and ORDER BY syntaxes, all expressions following ORDER BY must be in the DISTINCT expression.
  • In GaussDB, 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.
  • In GaussDB, the group_concat_max_len parameter is used to limit the maximum return length of GROUP_CONCAT. If the return length exceeds the maximum, the length is truncated. Currently, the maximum length that can be returned is 1073741823, which is smaller than that in MySQL.
  • When the default UTF-8 character set is used, the maximum number of bytes of UTF-8 character set in GaussDB is different from that in MySQL. As a result, the table structure in GaussDB is different from that in MySQL. 
    -- GaussDB:
m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
SET
m_db=# CREATE TABLE t1 AS SELECT * FROM (SELECT case WHEN 1 < 2 THEN group_concat(1.23, 3.24) ELSE 12.34 END v1) c1;
INSERT 0 1
m_db=# DESC t1;
 Field |     Type     | Null | Key | Default | Extra 
-------+--------------+------+-----+---------+-------
 v1    | varchar(256) | YES  |     |         | 
(1 row)
-- MySQL 5.7:
mysql> CREATE TABLE t1 AS SELECT * FROM (SELECT case WHEN 1 < 2 THEN group_concat(1.23, 3.24) ELSE 12.34 END v1) c1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| v1    | varchar(341) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • When the GROUP_CONCAT function is used as the input parameter of the NULLIF function, the behavior in nested scenarios is different. In MySQL 5.7, no matter whether the GROUP_CONCAT function is nested in the input parameters of NULLIF, the values of GROUP_CONCAT in both occasions are regarded as the same and NULL is returned. In MySQL 8.0, the values are regarded as unequal due to precision differences. In GaussDB, this function is nested in the same way as that in MySQL 8.0. 
    -- GaussDB:
m_db=# SELECT nullif(group_concat(1/7), 1/7);
 nullif
--------
 0.1429
(1 row)
-- MySQL 5.7:
mysql> SELECT nullif(group_concat(1/7), 1/7);
+--------------------------------+
| nullif(group_concat(1/7), 1/7) |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set (0.00 sec)
-- MySQL 8.0:
mysql> SELECT nullif(group_concat(1/7), 1/7);
+--------------------------------+
| nullif(group_concat(1/7), 1/7) |
+--------------------------------+
| 0.1429                         |
+--------------------------------+
1 row in set (0.00 sec)

7

MAX()

Supported, with differences.

In GaussDB, if DISTINCT is specified and the SQL statement contains a GROUP BY clause, the result sequence is not guaranteed. When the parameter is not a table field, the return value type of the MAX function is different from that of MySQL 5.7.

When precision transfer is enabled, the MAX function is nested with the time interval calculation of the time, date, datetime, or timestamp type. The return value and return type are the same as those in MySQL 8.0.

When precision transfer is enabled, the return value and return type of the MAX and INTERVAL functions are the same as those of MySQL 8.0.

8

MIN()

Supported, with differences.

In GaussDB, if DISTINCT is specified and the SQL statement contains a GROUP BY clause, the result sequence is not guaranteed. When the parameter is not a table field, the return value type of the MIN function is different from that of MySQL 5.7.

When precision transfer is enabled, the MIN function is nested with the time interval calculation of the time, date, datetime, and timestamp types. The return value and return type are the same as those in MySQL 8.0.

When precision transfer is enabled, the return value and return type of the MIN and INTERVAL functions are the same as those in MySQL 8.0.

9

SUM()

Supported, with differences.
  • In GaussDB, if DISTINCT is specified and the SQL statement contains a GROUP BY clause, the result sequence is not guaranteed.
  • In GaussDB, if the columns in expr are of the BIT, BOOL, or integer type and the sum of all rows exceeds the range of BIGINT, overflow occurs, reversing integers.

10

STD()

Supported.

-

11

Aggregate Functions

Supported, with differences.
  • If the ORDER BY statement contains an aggregate function, no error is reported in GaussDB, but an error is reported in MySQL.
  • If precision transfer is disabled (m_format_behavior_compat_options is not set to 'enable_precision_decimal'), when an aggregate function uses other functions, operators, or expressions such as SELECT clauses as input parameters, for example, SELECT sum(abs(n)) FROM t, but cannot obtain the precision information transferred by the input parameter expression, the result precision of the function is different from that of MySQL.
  • The result of the aggregate function varies depending on the data input sequence.
    • For example, if ORDER BY is used together with the aggregate function, the execution sequence of the function is changed. As a result, the result is inconsistent with that in MySQL. 
      -- Prepare a base table.
CREATE TABLE test_n(col_unumeric1  decimal(4,3) unsigned, col_znumeric2 decimal(3,2) unsigned zerofill, col_znumeric3 decimal(5,3) unsigned zerofill);
Query OK, 0 rows affected (0.01 sec)

INSERT INTO test_n VALUES(1.010, 2.02, 3.303),(1.190, 2.29, 3.339),(1.180, 2.28, 3.338);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

CREATE TABLE test_n_2(col_unumeric1  decimal(4,3) unsigned, col_znumeric2 decimal(3,2) unsigned zerofill, col_znumeric3 decimal(5,3) unsigned zerofill);
Query OK, 0 rows affected (0.02 sec)

INSERT INTO test_n_2 VALUES(1.180, 2.28, 3.338),(1.190, 2.29, 3.339),(1.010, 2.02, 3.303);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

CREATE TABLE IF NOT EXISTS fun_op_case_tb_1 (id int, name varchar(20), col_unumeric1 NUMERIC(4,3) unsigned, col_znumeric2 DECIMAL(3,2) zerofill,col_znumeric3 DEC(5,3) zerofill);
CREATE TABLE

INSERT INTO fun_op_case_tb_1 (id, name, col_unumeric1, col_znumeric2, col_znumeric3) VALUES
(1, 'Computer', 1.11, 2.12, 3.133),
(2, 'Computer', 2.11, 2.22, 3.233),
(3, 'Computer', 3.11, 2.32, 3.333),
(4, 'Computer', 1.41, 2.42, 3.343),
(5, 'Computer', 1.51, 2.52, 3.353),
(6, 'Computer', 1.61, 2.26, 3.363),
(7, 'Computer', 1.17, 2.27, 3.337),
(8, 'Computer', 1.18, 2.28, 3.338),
(9, 'Computer', 1.19, 2.29, 3.339),
(10, 'Computer', 1.01, 2.02, 3.303),
(1, 'Software', 1.11, 2.12, 3.133),
(2, 'Software', 2.11, 2.22, 3.233),
(3, 'Software', 3.11, 2.32, 3.333),
(4, 'Software', 1.41, 2.42, 3.343),
(5, 'Software', 1.51, 2.52, 3.353),
(6, 'Software', 1.61, 2.26, 3.363),
(7, 'Software', 1.17, 2.27, 3.337),
(8, 'Software', 1.18, 2.28, 3.338),
(9, 'Software', 1.19, 2.29, 3.339),
(10, 'Software', 1.01, 2.02, 3.303),
(1, 'Database', 1.11, 2.12, 3.133),
(2, 'Database', 2.11, 2.22, 3.233),
(3, 'Database', 3.11, 2.32, 3.333),
(4, 'Database', 1.41, 2.42, 3.343),
(5, 'Database', 1.51, 2.52, 3.353),
(6, 'Database', 1.61, 2.26, 3.363),
(7, 'Database', 1.17, 2.27, 3.337),
(8, 'Database', 1.18, 2.28, 3.338),
(9, 'Database', 1.19, 2.29, 3.339),
(10, 'Database', 1.01, 2.02, 3.303);
INSERT 0 30
-- GaussDB:
m_db=# SELECT * FROM test_n;
 col_unumeric1 | col_znumeric2 | col_znumeric3 
---------------+---------------+---------------
         1.010 |          2.02 |        03.303
         1.190 |          2.29 |        03.339
         1.180 |          2.28 |        03.338
m_db=# SELECT * FROM test_n_2;
 col_unumeric1 | col_znumeric2 | col_znumeric3 
---------------+---------------+---------------
         1.180 |          2.28 |        03.338
         1.190 |          2.29 |        03.339
         1.010 |          2.02 |        03.303
m_db=# SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM test_n_2 ;
         std         
---------------------
 0.24779023386727736
(1 row)
m_db=# SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM test_n ;
         std         
---------------------
 0.24779023386727742
(1 row)

m_db=# SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM fun_op_case_tb_1 GROUP BY name ORDER BY name;
        std         
--------------------
 1.8167446160646796
 1.8167446160646794
 1.8167446160646796
(3 rows)

--MySQL:
mysql> SELECT * FROM test_n;
+---------------+---------------+---------------+
| col_unumeric1 | col_znumeric2 | col_znumeric3 |
+---------------+---------------+---------------+
|         1.010 |          2.02 |        03.303 |
|         1.190 |          2.29 |        03.339 |
|         1.180 |          2.28 |        03.338 |
+---------------+---------------+---------------+
3 rows in set (0.00 sec)
mysql> SELECT *FROM test_n_2;
+---------------+---------------+---------------+
| col_unumeric1 | col_znumeric2 | col_znumeric3 |
+---------------+---------------+---------------+
|         1.180 |          2.28 |        03.338 |
|         1.190 |          2.29 |        03.339 |
|         1.010 |          2.02 |        03.303 |
+---------------+---------------+---------------+
3 rows in set (0.00 sec)
mysql>  SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM test_n_2 ;
+----------------------------------------------------+
| std(col_unumeric1*(col_znumeric2 | col_znumeric3)) |
+----------------------------------------------------+
|                                0.24779023386727736 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql>  SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM test_n;
+----------------------------------------------------+
| std(col_unumeric1*(col_znumeric2 | col_znumeric3)) |
+----------------------------------------------------+
|                                0.24779023386727742 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT std(col_unumeric1*(col_znumeric2 | col_znumeric3)) FROM fun_op_case_tb_1 GROUP BY name ORDER BY name;
+----------------------------------------------------+
| std(col_unumeric1*(col_znumeric2 | col_znumeric3)) |
+----------------------------------------------------+
|                                 1.8167446160646794 |
|                                 1.8167446160646794 |
|                                 1.8167446160646794 |
+----------------------------------------------------+
3 rows in set (0.00 sec)

-- Delete the base table.
DROP TABLE test_n;
DROP TABLE
DROP TABLE test_n_2;
DROP TABLE
DROP TABLE fun_op_case_tb_1;
DROP TABLE
    • For example, if WITH ROLLUP is used together with the aggregate function, the execution sequence of the function is changed. As a result, the result is inconsistent with that in MySQL. 
      -- Prepare a base table.
CREATE TABLE IF NOT EXISTS t1 (name VARCHAR(20), c1 INT(100), c2 FLOAT(7,5));
INSERT INTO t1 VALUES
('Computer', 666,-55.155),
('Computer', 789, -15.593),
('Computer', 928,-53.963),
('Computer', 666, -54.555),
('Computer', 666,-55.555),
('Database', 666,-55.155),
('Database', 789, -15.593),
('Database', 928,-53.963),
('Database', 666, -54.555),
('Database', 666,-55.555);

-- GaussDB:
m_db=# select name, std(c1/c2) c5 from t1  group by name with rollup;
  name  |         c5         
--------+--------------------
 Database | 15.02396266299967
 Computer | 15.023962662999669
        |  15.02396266299967
(3 rows)

--MySQL
mysql> select name, std(c1/c2) c5 from t1  group by name with rollup;
+-----------+--------------------+
| name      | c5                 |
+-----------+--------------------+
| Database | 15.023962662999669 |
| Computer | 15.023962662999669 |
| NULL      |  15.02396266299967 |
+-----------+--------------------+
3 rows in set (0.00 sec)

-- Delete the base table.
DROP TABLE t1;
DROP TABLE
  • If GROUP BY is used together with the aggregate function and the intermediate result of the DECIMAL data type is involved in calculation, data distortion occurs in MySQL, and GaussDB retains data in full precision. 
    -- Prepare a base table.
CREATE TABLE IF NOT EXISTS fun_op_case_tb_1 (id int,name varchar(20),col_znumeric2 DECIMAL(3,2) zerofill,col_znumeric3 DEC(5,3) zerofill, col_bit1 BIT(3), col_time2 time);

INSERT INTO fun_op_case_tb_1 VALUES
(1, 'Computer', 0.01, 3.130, b'101', '08:30:23.01'),
(2, 'Computer', 1.20, 30.990, b'101', '08:30:23.01'),
(3, 'Computer', 1.33, 43.500, b'101', '08:30:23.01'),
(4, 'Computer', 2.24, 30.990, b'101', '08:30:23.01'),
(5, 'Computer', 1.25, 43.600, b'101', '08:30:23.01'),
(6, 'Computer', 2.20, '20.900', b'101', '08:30:23.01'),
(7, 'Computer', 2.20, '20.900', b'101', '08:30:23.01'),
(8, 'Computer', 2.20, '20.900', b'101', '08:30:23.01'),
(9, 'Computer', 2.29, '22.780', b'101', '08:30:23.01'),
(10, 'Computer', 2.02, '20.900', b'101', '08:30:23.01')

-- GaussDB:
m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal';
m_db=# SELECT avg(col_znumeric3/col_znumeric2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name;
      avg       
----------------
 46.90407212526
(1 row)
m_db=# SELECT sum(col_bit1/col_time2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name;
  sum   
--------
 0.0006
(1 rows)

--MySQL:
mysql>  SELECT avg(col_znumeric3/col_znumeric2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name;
+----------------------------------+
| avg(col_znumeric3/col_znumeric2) |
+----------------------------------+
|                   46.90407213000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT sum(col_bit1/col_time2) FROM fun_op_case_tb_1 WHERE id<=10 GROUP BY name;
+-------------------------+
| sum(col_bit1/col_time2) |
+-------------------------+
|                  0.0010 |
+-------------------------+
1 row in set (0.00 sec)
-- Delete a base table.
DROP TABLE fun_op_case_tb_1;
DROP TABLE
Parent topic: System Functions