Help Center/ GaussDB/ Compatibility(Centralized)/ MySQL Compatibility Description/ M-compatible Mode/ System Functions/ Window Functions
Updated on 2025-09-09 GMT+08:00
View PDF
Share

Window Functions

Table 1 Window functions

Function

Differences Compared with MySQL

LAG()
  • The value range of offset N is different.

    In MySQL, N must be an integer in the range [0, 263-1].

    In GaussDB, N must be an integer in the range [0, 231-1].

  • The value of offset N varies in terms of the value format.
    • In MySQL, the value format is as follows:
      • Unsigned integer of a constant literal.
      • Marker parameter declared with a question mark (?) in the PREPARE statement.
      • User-defined variable.
      • Local variable in a stored procedure.
    • In GaussDB, the value format is as follows:
      • Unsigned integer of a constant literal.
      • Marker parameters declared with a question mark (?) in the PREPARE statement cannot be used. (There are differences in terms of the PREPARE statement currently.)
      • User-defined variable.
      • Local variables in stored procedures are not supported. (Currently, PL/SQL does not support local variables.)
  • When this function is used as a subquery together with CREATE TABLE AS and no error or alarm is reported when the subquery statement of this function is executed independently, the difference is as follows:
    • If GaussDB is in strict or loose mode, the CREATE TABLE AS statement is successfully executed and a table is successfully created.
    • If MySQL is in strict mode, an error may be reported when the CREATE TABLE AS statement is executed, and table creation fails.

LEAD()

ROW_NUMBER()

-

RANK()

-

DENSE_RANK()

-

FIRST_VALUE()

-

LAST_VALUE()

-

PERCENT_RANK()

-

NTILE()

-

MIN()

In GaussDB, the MIN() function does not support the use of DISTINCT and OVER clauses at the same time. In MySQL, this operation is supported. The following is an example:

DROP TABLE IF EXISTS m_test;
CREATE TABLE m_test(age INT, salary INT);
INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL);

-- Behavior in GaussDB:
m_db=# SELECT MIN(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;
ERROR:
GAUSS-10875: DISTINCT is not implemented for window functions
SQLSTATE: 0A000
LINE 1: SELECT MIN(DISTINCT salary) OVER(PARTITION BY age ORDER by s...
^
CONTEXT:  referenced column: min

-- Behavior in MySQL 8.0:
mysql> SELECT MIN(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;
+------------------------------------------------------------------------------+
| MIN(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) |
+------------------------------------------------------------------------------+
|                                                                         NULL |
|                                                                            1 |
|                                                                            2 |
|                                                                            2 |
|                                                                         NULL |
|                                                                         NULL |
+------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

Whether to use DISTINCT in the MIN() function does not affect the result. You are advised to use DISTINCT directly.

SELECT MIN(salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;

MAX()

In GaussDB, the MAX() function does not support the use of DISTINCT and OVER clauses at the same time. In MySQL, this operation is supported. The following is an example:

DROP TABLE IF EXISTS m_test;
CREATE TABLE m_test(age INT, salary INT);
INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL);

-- Behavior in GaussDB:
m_db=# SELECT MAX(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;
ERROR:
GAUSS-10875: DISTINCT is not implemented for window functions
SQLSTATE: 0A000
LINE 1: SELECT MAX(DISTINCT salary) OVER(PARTITION BY age ORDER by s...
^
CONTEXT:  referenced column: max

-- Behavior in MySQL 8.0:
mysql> SELECT MAX(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;
+------------------------------------------------------------------------------+
| MAX(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) |
+------------------------------------------------------------------------------+
|                                                                         NULL |
|                                                                            1 |
|                                                                            2 |
|                                                                            2 |
|                                                                         NULL |
|                                                                         NULL |
+------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

Whether to use DISTINCT in the MAX() function does not affect the result. You are advised to use DISTINCT directly.

SELECT MAX(salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;

Window functions
  • Window functions in GaussDB are compatible with those in MySQL 8.0. Combining the functions with some MySQL 5.7-compatible features may lead to composite behaviors.
    • For example, when JSON is combined with such functions:
      • Behavior in GaussDB: 
        -- Preset table data.
m_db=# CREATE TABLE t1(name varchar(20), col_json1 json, col_json2 json, col_json3 json);
m_db=# CREATE TABLE t2(name varchar(20), col_json1 json, col_json2 json, col_json3 json);
m_db=# INSERT INTO t1 VALUES('1','false','false','false'),('1','false','false','false');
m_db=# INSERT INTO t1 VALUES('2','true','true','true'),('2','true','true','true');
m_db=# INSERT INTO t2 VALUES('3','true','true','true'),('3','true','true','true');
m_db=# INSERT INTO t2 VALUES('4','false','false','false'),('4','false','false','false');
-- JSON behavior
m_db=# SELECT name,bit_or(col_json1) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
 name | rs 
------+----
 1    | 0
 2    | 0
 3    | 0
 4    | 0
(4 rows)

m_db=# SELECT name,cast(col_json1 AS unsigned) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp ORDER BY name;
WARNING:  The INTEGER value 'true' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'true' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'false' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'false' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'true' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'true' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'false' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'false' is incorrect.
CONTEXT:  referenced column: rs
 name | rs 
------+----
 1    | 0
 1    | 0
 2    | 0
 2    | 0
 3    | 0
 3    | 0
 4    | 0
 4    | 0
(8 rows)

-- Combine window functions and JSON.
m_db=# SELECT name,bit_or(col_json1) OVER(PARTITION BY col_json2 ORDER BY col_json3) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
 name | rs 
------+----
 1    | 0
 2    | 0
 3    | 0
 4    | 0
(4 rows)

m_db=# SELECT name, ROW_NUMBER() OVER(PARTITION BY col_json2 ORDER BY col_json3) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmmp GROUP BY name ORDER BY name;
 name | rs 
------+----
 1    | 1
 2    | 2
 3    | 2
 4    | 1
(4 rows)
      • Behavior in MySQL: 
        -- Preset table data.
mysql> CREATE TABLE t1(name varchar(20), col_json1 json, col_json2 json, col_json3 json);
mysql> CREATE TABLE t2(name varchar(20), col_json1 json, col_json2 json, col_json3 json);
mysql> INSERT INTO t1 VALUES('1','false','false','false'),('1','false','false','false');
mysql> INSERT INTO t1 VALUES('2','true','true','true'),('2','true','true','true');
mysql> INSERT INTO t2 VALUES('3','true','true','true'),('3','true','true','true');
mysql> INSERT INTO t2 VALUES('4','false','false','false'),('4','false','false','false');

-- JSON behavior
-- MySQL 5.7
mysql> SELECT name,bit_or(col_json1) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
+------+----+
| name | rs |
+------+----+
| 1    |  0 |
| 2    |  0 |
| 3    |  0 |
| 4    |  0 |
+------+----+
4 rows in set (0.00 sec)

mysql> SELECT name,cast(col_json1 AS unsigned) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp ORDER BY name;
+------+------+
| name | rs   |
+------+------+
| 1    |    0 |
| 1    |    0 |
| 2    |    0 |
| 2    |    0 |
| 3    |    0 |
| 3    |    0 |
| 4    |    0 |
| 4    |    0 |
+------+------+
8 rows in set, 8 warnings (0.00 sec)

mysql> SHOW warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'true'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'true'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'false' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'false' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'true'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'true'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'false' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'false' |
+---------+------+--------------------------------------------+
8 rows in set (0.00 sec)

-- MySQL 8.0
mysql> SELECT name,bit_or(col_json1) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
+------+----+
| name | rs |
+------+----+
| 1    |  1 |
| 2    |  0 |
| 3    |  1 |
| 4    |  0 |
+------+----+
4 rows in set (0.01 sec)

mysql> SELECT name,cast(col_json1 AS unsigned) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp ORDER BY name;
+------+------+
| name | rs   |
+------+------+
| 1    |    1 |
| 1    |    1 |
| 2    |    0 |
| 2    |    0 |
| 3    |    1 |
| 3    |    1 |
| 4    |    0 |
| 4    |    0 |
+------+------+
8 rows in set (0.00 sec)

-- Only MySQL 8.0 supports the combination of window functions and JSON.
mysql> SELECT name,bit_or(col_json1) OVER(PARTITION BY col_json2 ORDER BY col_json3) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
+------+----+
| name | rs |
+------+----+
| 1    |  1 |
| 2    |  0 |
| 3    |  1 |
| 4    |  0 |
+------+----+
4 rows in set (0.00 sec)

mysql> SELECT name, ROW_NUMBER() OVER(PARTITION BY col_json2 ORDER BY col_json3) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmpmp GROUP BY name ORDER BY name;
+------+----+
| name | rs |
+------+----+
| 1    |  1 |
| 2    |  1 |
| 3    |  2 |
| 4    |  2 |
+------+----+
4 rows in set (0.01 sec)
    • Window functions such as AVG, MAX, MIN, BIT_AND, BIT_OR, and BIT_XOR are used with the CREATE TABLE AS syntax to create a table. The column types of the created table in GaussDB are different from those of MySQL 8.0. 
      -- The window function MIN in GaussDB is only compatible with that in MySQL 8.0, but the result of using the function together with CREATE TABLE AS on INT data is different.
-- GaussDB
m_db=# CREATE TABLE t_int(col_int int(100));
m_db=# CREATE TABLE test AS SELECT min(col_int) OVER(ORDER BY col_int) FROM t_int;
m_db=# DESC test;
 Field |    Type     | Null | Key | Default | Extra 
-------+-------------+------+-----+---------+-------
 min   | bigint(100) | YES  |     |         | 
(1 row)

-- MySQL 8.0
mysql> CREATE TABLE t_int(col_int int(100));
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> CREATE TABLE test AS SELECT min(col_int) OVER(ORDER BY col_int) FROM t_int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC test;
+-------------------------------------+--------+------+-----+---------+-------+
| Field                               | Type   | Null | Key | Default | Extra |
+-------------------------------------+--------+------+-----+---------+-------+
| min(col_int) over(order by col_int) | bigint | YES  |     | NULL    |       |
+-------------------------------------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • When the ORDER BY clause is used for sorting, NULL values are sorted differently.
    • In MySQL, NULL values are placed at the front by default when sorted in ascending order.
    • In GaussDB, NULL values are placed at the end by default when sorted in ascending order.
  • Column aliases are used in the OVER clauses including ORDER BY and PARTITION BY.
    • MySQL does not support column aliases.
    • GaussDB supports column aliases.
  • When the input parameter is an expression (for example, 1 / col1), the precision of the result is different.
    • MySQL first calculates the expression result and rounds it off. As a result, the precision of the final result decreases.
    • GaussDB does not round off the result of the expression.
  • The binary character strings are displayed differently.
    • In MySQL, a binary string is encoded into a hexadecimal value. For example, '-4' is displayed as 0x2D34 after encoding.
    • In GaussDB, the value of the original character string is displayed. For example, '-4' is displayed as '-4'.
  • When the input parameters of the MAX and MIN functions are of the FLOAT type and are used together with CREATE TABLE AS to create a table, the column types and precisions of the created table are different from those in MySQL 8.0. 
    m_db=# CREATE TABLE t_float (id int, col_float1 FLOAT4(6,4), col_float2 FLOAT(7), col_float3 DOUBLE(8,5), col_ufloat1 FLOAT4(6,4) unsigned);

m_db=# CREATE TABLE t_tmp AS SELECT min(col_float1) OVER(ORDER BY id) AS col_float1, min(col_float2) OVER(PARTITION BY id) AS col_float2, min(col_float3) OVER(PARTITION BY id) AS col_float3 , min(col_ufloat1) OVER(PARTITION BY id) AS col_ufloat1 FROM t_float;

-- GaussDB
m_db=# DESC t_tmp;
    Field    |    Type     | Null | Key | Default | Extra 
-------------+-------------+------+-----+---------+-------
 col_float1  | double(21,4) | YES  |     |         | 
 col_float2  | double      | YES  |     |         | 
 col_float3  | double(22,5) | YES  |     |         | 
 col_ufloat1 | double(21,4) | YES  |     |         | 
(4 rows)

--MySQL
mysql> DESC t_tmp;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| col_float1  | float(6,4)  | YES  |     | NULL    |       |
| col_float2  | float       | YES  |     | NULL    |       |
| col_float3  | double(8,5) | YES  |     | NULL    |       |
| col_ufloat1 | float(6,4)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
  • In GaussDB, if UNION is performed on an expression containing the LAG, LEAD, FIRST_VALUE, or LAST_VALUE function and CREATE TABLE AS is used to create a table, the column type and precision of the created table are different from those in MySQL 8.0. 
    -- GaussDB
m_db=# CREATE TABLE t_test(id int, col_int1 json, col_text1 tinyint);
CREATE TABLE
m_db=# CREATE TABLE t_tmp AS SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test;
INSERT 0 0
m_db=# DESC t_tmp;
  Field   | Type | Null | Key | Default | Extra 
----------+------+------+-----+---------+-------
 col_int1 | json | YES  |     |         | 
(1 row)

m_db=# DROP TABLE t_tmp;
DROP TABLE

m_db=# CREATE TABLE t_tmp AS SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test;
INSERT 0 0

m_db=# DESC t_tmp;
   Field   |    Type    | Null | Key | Default | Extra 
-----------+------------+------+-----+---------+-------
 col_text1 | integer(4) | YES  |     |         | 
(1 row)
m_db=# DROP TABLE t_tmp;
DROP TABLE
m_db=# CREATE TABLE t_tmp AS SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test UNION ALL SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test;
INSERT 0 0
m_db=# DESC t_tmp;
  Field   |   Type   | Null | Key | Default | Extra 
----------+----------+------+-----+---------+-------
 col_int1 | longtext | YES  |     |         | 
(1 row)

m_db=# DROP TABLE t_tmp;
DROP TABLE
m_db=# CREATE TABLE t_tmp AS SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test UNION ALL SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test;
INSERT 0 0
m_db=# DESC t_tmp;
   Field   |    Type     | Null | Key | Default | Extra 
-----------+-------------+------+-----+---------+-------
 col_text1 | integer(11) | YES  |     |         | 
(1 row)


--MySQL 8.0
mysql> DESC t_tmp;
+----------+------+------+-----+---------+-------+
| Field    | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| col_int1 | json | YES  |     | NULL    |       |
+----------+------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> DROP TABLE t_tmp;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t_tmp AS SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_tmp;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| col_text1 | int  | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t_test(id int, col_int1 json, col_text1 tinyint);
mysql> CREATE TABLE t_tmp AS SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test UNION ALL SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test;
mysql> DESC t_tmp;
+----------+------+------+-----+---------+-------+
| Field    | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| col_int1 | json | YES  |     | NULL    |       |
+----------+------+------+-----+---------+-------+
1 row in set (0.01 sec)
 
mysql> DROP TABLE t_tmp;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t_tmp AS SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test UNION ALL SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_tmp;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| col_text1 | tinyint | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • When the CREATE TABLE AS syntax is used to create a table and DESC is specified to view the table structure, the differences are as follows:
    • When the AVG, MAX, MIN, BIT_AND, BIT_OR, or BIT_XOR window function is used, the table column of different types and precisions may be returned. The return type in GaussDB is the same as that of a non-window function in MySQL 5.7.
    • The functions MAX, MIN, LAG, LEAD, FIRST_VALUE, and LAST_VALUE return FLOAT values in MySQL 8.0 and return DOUBLE values in GaussDB.
    • In MySQL 8.0:
      • If a column type in a table is BIGINT or INT, the width is not displayed.
      • If the width of a column type (Type) in a table is 0, the width is displayed, for example, binary(0).
    • In GaussDB:
      • If a column type in a table is BIGINT or INTEGER, the width is displayed.
      • If the width of a column type in a table is 0, the width is not displayed. For example, binary(0) is displayed as binary.
  • The execution result of a window function depends on the sequence of table data. In some scenarios (for example, GROUP BY, WHERE, or HAVING), if the table data sequence of GaussDB is different from that of MySQL, the execution result of the window function may be different. For example:
    • Behavior in GaussDB: 
      -- Preset table data.
m_db=# CREATE TABLE t1(id int,name varchar(20),age int);
CREATE TABLE
m_db=# INSERT INTO t1(id, name,age) VALUES (1, 'zwt',90), (2, 'dda',85), (3, 'aab',90), (4, 'aac',78), (5, 'aad',85), (6, 'aae',92), (7, 'aaf',78);
INSERT 0 7
m_db=# INSERT INTO t1(id, name,age) VALUES (1, 'zwt',90), (2, 'dda',85), (3, 'aab',90), (4, 'aac',78), (5, 'aad',85), (6, 'aae',92), (7, 'aaf',78);
INSERT 0 7

-- The sequence of table data is different from that in MySQL. As a result, the value of last_value is different.
m_db=# SELECT age, last_value(age) over() FROM t1 WHERE id > 0 GROUP BY age HAVING age > 10;
 age | last_value 
-----+------------
  78 |         85
  90 |         85
  92 |         85
  85 |         85
(4 rows)

m_db=# DROP TABLE IF EXISTS t1;
DROP TABLE
    • Behavior in MySQL: 
      # Preset table data.
mysql> CREATE TABLE t1(id int,name varchar(20),age int);

Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t1(id, name,age) VALUES (1, 'zwt',90), (2, 'dda',85), (3, 'aab',90), (4, 'aac',78), (5, 'aad',85), (6, 'aae',92), (7, 'aaf',78);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1(id, name,age) VALUES (1, 'zwt',90), (2, 'dda',85), (3, 'aab',90), (4, 'aac',78), (5, 'aad',85), (6, 'aae',92), (7, 'aaf',78);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

# The sequence of table data is different from that in GaussDB. As a result, the value of last_value is different.
mysql> SELECT age, last_value(age) over() FROM t1 WHERE id > 0 GROUP BY age HAVING age > 10;
+------+------------------------+
| age  | last_value(age) over() |
+------+------------------------+
|   90 |                     92 |
|   85 |                     92 |
|   78 |                     92 |
|   92 |                     92 |
+------+------------------------+
4 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.10 sec)
Parent topic: System Functions