Help Center/ GaussDB/ MySQL Compatibility(Centralized)/ M-compatible Mode/ System Functions/ Window Functions
Updated on 2025-06-30 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.
      • Parameter marker denoted by 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.
      • Parameter markers denoted by a question mark (?) in the PREPARE statement are not supported (current difference in the PREPARE statement).
      • 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()

-

Window Functions
  • When the ORDER BY clause is used for sorting, the sorting of NULL values is different.
    • 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 CREATE TABLE AS syntax is used to create a table and DESC is specified to view the table structure, the differences are as follows:
    • 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