排序规则支持

概述

一种可排序数据类型的每一种表达式都有一个排序规则（系统内部的可排序数据类型可以是text、varchar和char等字符类型。用户定义的基础类型也可以被标记为可排序的，并且在一种可排序数据类型上的域也是可排序的）。如果该表达式是一个列引用，该表达式的排序规则就是列所定义的排序规则。如果该表达式是一个常量，排序规则就是该常量数据类型的默认排序规则。更复杂表达式的排序规则根据其输入的排序规则得来。

排序规则组合原则

• 当表达式的collation未指定时，则认为是默认的排序规则default，它表示数据库的区域设置。表达式的collation也可能是不确定的，此时，排序操作和其他不确定的排序规则的操作就会失败。
• 对于函数或操作符调用，其排序规则将通过检查所有参数的collation来决定。如果该函数或操作符调用的结果是一种可排序的数据类型，若有外层表达式要用到排序规则，那么该外层的表达式将继承对应函数和操作符所调用结果集的排序规则。
• 表达式的排序规则派生可以是显式或隐式。该区别会影响多个不同的排序规则出现在同一个表达式中时如何对collation进行组合。当执行语句使用COLLATE子句时，将发生显式派生，否则为隐式派生。当多个排序规则组合时，规则如下：
  • 如果输入表达式中存在显式COLLATE派生，则在输入表达式之间的所有显式派生的COLLATE必须相同，否则将产生冲突错误。如果存在显式COLLATE，那它就是排序规则组合的结果。
  • 如果不存在显式COLLATE，那所有输入表达式必须具有相同的隐式COLLATE或默认COLLATE。如果存在非默认COLLATE，那它就是排序规则组合的结果。否则，结果是默认COLLATE。
  • 如果在输入表达式之间存在多个冲突的非默认COLLATE，则组合被认为是具有不确定排序规则，这并非一种错误。如果被调用的函数或表达式需要用到排序规则，运行时将产生排序规则未知的错误。
• CASE表达式中，比较行为使用的规则以WHEN子句中的COLLATE设置为准。
• 显示COLLATE的派生仅在当前查询（CTE或SUBQUERY）中生效，查询外则降为隐式派生。

排序规则使用建议

• 同一条查询语句中，避免使用多种排序规则，可能导致非预期的结果集。
• 使用collate子句指定排序规则时，避免连续使用多个collate子句变更排序规则。

大小写不敏感排序规则支持

从集群8.1.3版本开始，GaussDB(DWS)增加内置排序规则case_insensitive，即对字符类型的大小写不敏感行为（如排序、比较、哈希）。

约束条件：

• 支持字符类型：char/character/nchar、varchar/character varying/varchar2/nvarchar2/clob/text。
• 不支持字符类型：“char”和name。
• 不支持的编码：PG_EUC_JIS_2004、PG_MULE_INTERNAL、PG_LATIN10、PG_WIN874。
• 不支持CREATE DATABASE时指定到LC_COLLATE。
• 不支持正则表达式。
• 不支持字符类型的record比较（如record_eq）。
• 不支持时序表。
• 不支持倾斜优化。
• 不支持RoughCheck优化。

示例

语句中显示指定COLLATE子句。

1
2
3
4
5

SELECT 'a' = 'A', 'a' = 'A' COLLATE case_insensitive;
 ?column? | ?column?
----------+----------
 f        | t
(1 row)

建表时指定列属性为case_insensitive。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE t1 (a text collate case_insensitive);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
\d t1
            Table "public.t1"
 Column | Type |        Modifiers
--------+------+--------------------------
 a      | text | collate case_insensitive

INSERT INTO t1 values('a'),('A'),('b'),('B');
INSERT 0 4

建表时指定，查询时无需指定。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT a, a='a' FROM t1;
 a | ?column?
---+----------
 A | t
 B | f
 a | t
 b | f
(4 rows)
SELECT a, count(1) FROM t1 GROUP BY a;
 a | count
---+-------
 a |     2
 B |     2
(2 rows)

CASE表达式，以WHEN子句中的COLLATE设置为准。

1
2
3
4
5
6
7
8

SELECT a,case a when 'a' collate case_insensitive then 'case1' when 'b' collate "C" then 'case2' else 'case3' end FROM t1;
 a | case
---+-------
 A | case1
 B | case3
 a | case1
 b | case2
(4 rows)

跨子查询隐式派生。

1
2
3
4
5
6
7
8
9

SELECT * FROM (SELECT a collate "C" from t1) WHERE a in ('a','b');
 a
---
 a
 b
(2 rows)
SELECT * FROM t1,(SELECT a collate "C" from t1) t2 WHERE t1.a=t2.a;
ERROR:  could not determine which collation to use for string hashing
HINT:  Use the COLLATE clause to set the collation explicitly.

• 由于collate case_insensitive为不敏感排序，结果集不确定，再使用敏感排序筛选，会有结果集不稳定的问题，因此语句中避免出现敏感排序和不敏感排序混用。
• 使用collate case_insensitive指定字符类型为大小写不敏感后，性能较使用前会有所下降，因此性能敏感场景需谨慎评估后使用。