Window Functions

1

LAG()

Supported, with differences.

• The value range of offset N is different.

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

  In GaussDB, N must be an integer in the range [0, 2³¹-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.)
• The sorting of NULL values in the ORDER BY clause 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.

• The sorting of NULL values in the ORDER BY clause is different.

  In MySQL, precision is included.

  In GaussDB, precision is missing.

• The display of binary character strings is different.

  In MySQL, the hexadecimal code value of a binary character string is displayed. For example, '-4' is displayed as 0x2D34.

  In GaussDB, the value of the original character string is displayed. For example, '–4' is displayed as '–4'.

• When DESC is used to view the structure of a table created using the CREATE TABLE AS syntax, 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 function of identifying columns with null and default values in a table structure is not implemented currently.
• 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.

2

LEAD()

Supported, with differences.

The differences are the same as those of the LAG() function.

3

ROW_NUMBER()

Supported, with differences.

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.