执行业务报错could not determine which collation to use for string hashing

问题现象

执行SELECT查询时报错could not determine which collation to use for string hashing。

1
2
3
4
5
6

CREATE TABLE t(a text collate "C", b text collate case_insensitive);
INSERT INTO t VALUES('Hello','world');
——计算ifnull(a,b)的值的哈希值
SELECT hashtext(ifnull(a,b)) FROM t;
ERROR:  dn_6005_6006: could not determine which collation to use for string hashing.
HINT:  Use the COLLATE clause to set the collation explicitly.

hashtext函数用于计算适当数据类型的值的哈希值。此处仅用作示例说明出现collate冲突时应该如何解决。

原因分析

表t中有两个字段，且两个字段的排序规则不同，字段a的排序规则为C（安装数据库时的默认排序规则），字段b的排序规则为case_insensitive（不区分大小写），SELECT语句中表达式hashtext(ifnull(a,b))存在多个collation造成冲突，执行报错。

处理方法

当字符串表达式中collation有多个时，可手动指定COLLATE collation_name。

执行SELECT时，指定表达式ifnull(a,b)的排序规则为C或者case_insensitive。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT hashtext(ifnull(a,b) collate "C") FROM t;
 hashtext
-----------
 820977155
(1 row)

SELECT hashtext(ifnull(a,b) collate case_insensitive) FROM t;
 hashtext
-----------
 238052143
(1 row)

扩展

除上述场景会出现存在多个collation造成冲突，以下两种场景也会出现多个collation，报错不同但解决方法相同。

• 场景一

  SELECT语句中比较表达式a=b存在多个collation造成冲突，执行报错could not determine which collation to use for string comparison。

  1 2 3

  SELECT a=b FROM t; ERROR: dn_6001_6002: could not determine which collation to use for string comparison HINT: Use the COLLATE clause to set the collation explicitly.

  执行SELECT时，指定表达式a=b的排序规则为case_insensitive。

  1 2 3 4 5

  SELECT a=b COLLATE case_insensitive FROM t; ?column? ---------- f (1 row)

• 场景二

  SELECT语句中表达式instr(a,b)存在多个collation造成冲突，执行报错could not determine which collation to use for string searching。

  1 2 3

  SELECT instr(a,b) FROM t; ERROR: dn_6005_6006: could not determine which collation to use for string searching HINT: Use the COLLATE clause to set the collation explicitly.

  执行SELECT时，指定字段a的排序规则为case_insensitive或者指定字段b的排序规则为C来保证字段排序规则的统一。

  1 2 3 4 5 6 7 8 9 10 11

  SELECT instr(a collate case_insensitive,b) FROM t; instr ------- 0 (1 row) SELECT instr(a,b collate "C") FROM t; instr ------- 0 (1 row)