人大金仓数据库KingbaseES order by null值的排序规则

关键字：

ORDER BY NULL、人大金仓、KingbaseES

语法

ORDER [SIBLINGS] BY , | (expr | position | c_alias) [ASC|DESC] [NULLS FIRST] [NULLS LAST]

参数规则

（1）对于order by中NULL值的排序规则说明：当排序值包含NULL时，根据指定的NULLS FIRST | LAST决定包含空值的行排在最前还是最后，默认情况下为NULLS LAST。

数据准备

（1）创建数据表并插入数据

test=# Create table t1( a int, b varchar(10));

CREATE TABLE

test=# Insert into t1 values(1,'a');

INSERT 0 1

test=# Insert into t1 values(NULL,'b');

INSERT 0 1

test=# Insert into t1 values(2, NULL);

INSERT 0 1

（2）查看数据表

test=# select * from t1;

a | b

---+---

1 | a

| b

2 |

(3 rows)

测试用例

（1）DM8 order By子句支持NULLS FIRST | LAST语法，不指定NULLS FIRST|LAST时order By 默认采用NULLS FIRST。

SQL> select * from t1 order by a;

select * from t1 order by a nulls first;

select * from t1 order by a nulls last;

LINEID A B

---------- ----------- -

1 NULL b

2 1 a

3 2 NULL

used time: 33.636(ms). Execute id is 56304.

SQL>

LINEID A B

---------- ----------- -

1 NULL b

2 1 a

3 2 NULL

used time: 0.492(ms). Execute id is 56305.

SQL>

LINEID A B

---------- ----------- -

1 1 a

2 2 NULL

3 NULL b

used time: 0.416(ms). Execute id is 56306.

（2）Oracle order by 子句支持NULLS FIRST | LAST语法，不指定NULLS FIRST|LAST时order by 默认采用NULLS LAST。

SQL> select * from t1;

A B

---------- ----------

1 a

b

2

select * from t1 order by a;

select * from t1 order by a nulls first;

A B

---------- ----------

1 a

2

b

SQL>

A B

---------- ----------

b

1 a

2

SQL> select * from t1 order by a nulls last;

A B

---------- ----------

1 a

2

b

（3）Mysql8 Order By子句不支持NULLS FIRST | LAST语法， 不指定NULLS FIRST|LAST时Order By 默认采用NULLS FIRST。

mysql> select * from t1 order by a;

+------+------+

| a | b |

+------+------+

| NULL | b |

| 1 | a |

| 2 | NULL |

+------+------+

3 rows in set (0.00 sec)

mysql> select * from t1 order by a nulls first;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nulls first' at line 1

mysql> select * from t1 order by a nulls last;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nulls last' at line 1

（4）KES oracle模式order by 子句支持NULLS FIRST | LAST语法，不指定NULLS FIRST|LAST时order by 默认采用NULLS LAST。

test=# select * from t1 order by a;

a | b

---+---

1 | a

2 |

| b

(3 rows)

test=# select * from t1 order by a nulls first;

a | b

---+---

| b

1 | a

2 |

(3 rows)

test=# select * from t1 order by a nulls last;

a | b

---+---

1 | a

2 |

| b

(3 rows)

（5）KES mysql模式order by 子句支持NULLS FIRST | LAST语法，不指定NULLS FIRST|LAST时order by 默认采用NULLS LAST。

test=# select * from t1 order by a;

a | b

---+---

1 | a

2 |

| b

(3 rows)

test=# select * from t1 order by a nulls first;

a | b

---+---

| b

1 | a

2 |

(3 rows)

test=# select * from t1 order by a nulls last;

a | b

---+---

1 | a

2 |

| b

(3 rows)

小结

根据对竞品的调研分析，以及对KES各模式的测试，可以看到KES当前支持的模式中，只有Oracle模式与Oracle的行为一致，Mysql模式不一致。下图是本次调研的KES与各竞品的异同点：

功能点		KES	Mysql8	Oracle21C	DM8
NULL值排序规则	默认	NULLS LAST	NULLS FIRST	NULLS LAST	NULLS FIRST
	NULLS FIRST | LAST语法	支持	不支持	支持	支持

（1） KES Oracle 模式中Order by 默认采用NULLS LAST，支持NULLS FIRST | LAST语法。与Oracle 一致。

（2） KES Mysql模式中Order by 默认采用NULLS LAST，支持NULLS FIRST | LAST语法。与Mysql 不一致。Mysql Order by 默认采用NULLS FIRST, 且不支持NULLS FIRST | LAST语法。