默认为null的数据库字段导致SQL排序失效--以pg数据库为例

默认为空的数据库字段导致SQL排序失效？

废话不多说，上货。

追根溯源

生命不息，学习不止，总能发现新玩意。我们都知道当我们创建对象时，会自动在内存中分配一块区域用来存放对象实例。
今天写bug的时候看到了一个有趣的现象，当我数据库设定一个字段default为0时，order by该字段排序时居然会导致排序失效；
为什么会失效，有什么影响，如何避免？
一起来看看吧。

情景重现

1.我们先创建一个没用的表

CREATE TABLE "public"."test" (
  "id" int4 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "number" int4 DEFAULT null
);

2. 然后我们插入几条没用的数据

INSERT INTO "public"."test" ("id", "name") VALUES (1, 'a');
INSERT INTO "public"."test" ("id", "name", "number") VALUES (2, 'b', 50);
INSERT INTO "public"."test" ("id", "name", "number") VALUES (3, 'c', 2);
INSERT INTO "public"."test" ("id", "name", "number") VALUES (4, 'd', 9);
INSERT INTO "public"."test" ("id", "name", "number") VALUES (5, 'd', 9);
INSERT INTO "public"."test" ("id", "name", "number") VALUES (6, 'b', 50);
INSERT INTO "public"."test" ("id", "name", "number") VALUES (7, 'c', 2);
INSERT INTO "public"."test" ("id", "name", "number") VALUES (8, 'd', 9);

3.我们的需求是根据表中number总数，拿到排名，展示name，执行查询

SELECT name ,sum(number) from test GROUP BY name ORDER BY sum(number) DESC

为了方便结果查看，未隐藏number总数

此时产生了错误排序，number总数为0的a排到了第一位

4.修正方式，使用 nulls last 设定空字段排序规则

SELECT name ,sum(number) from test GROUP BY name ORDER BY sum(number) DESC NULLs LAST

nulls first 和 nulls last

nulls first 排序指定让null 放在最前
nulls last 排序指定null放在最后
不添加时默认使用nulls last

nulls first 和 nulls last 的影响

nulls first 和 nulls last并不是随便使用，使用时要保证与其索引创建的顺序需要保持一致，否者会让索引失效。

因为我们默认创建的btree索引，其叶子结点的数据是有序排列的，一个从大到小的有序链表，
当创建索引不指定nulls first时，pgl默认把null值放在叶子节点的最后
如果排序时只是order by number，未指定nulls first，pg只需要根据索引顺序的返回需要的数据则
否则，如果order by numbernulls last,pg如果使用索引，
其可能先在叶子未尾节点返回null值的数据，再在叶子起始节点开始返回数据
（假设需要返回10条数据，null值为5条，非null值有5条），其明显此时使用索引不是高效的

最后给你们推荐一本书吧

如果有错误或者需要补充请写在下面，跟我打一架！

古德拜~