Error could not determine which collation to use for string hashing Reported During Service Execution

Symptom

Error could not determine which collation to use for string hashing is reported during SELECT query.

1
2
3
4
5
6

CREATE TABLE t(a text collate "C", b text collate case_insensitive);
INSERT INTO t VALUES('Hello','world');
- - Calculate the hash value of 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.

The hashtext function is used to obtain the hash value. This section is only an example to describe how to resolve a collation conflict.

Possible Causes

Table t contains two columns whose collation rules are different. The sorting rule of column a is C (default sorting rule during database installation), and the sorting rule of column b is case_insensitive. In the SELECT statement, the expression hashtext(ifnull(a,b)) has multiple collations, causing a conflict. As a result, an error is reported.

Handling Procedure

If there are multiple collations in a string expression, you can manually specify COLLATE collation_name.

When executing SELECT, set the collation rule of the expression ifnull(a,b) to C or 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)

More

In the following two scenarios, multiple collations may also occur. The error messages are different, but the solutions are the same.

• Scenario 1

  In the SELECT statement, the comparison expression a=b has multiple collations, causing a conflict. As a result, the error could not determine which collation to use for string comparison is reported.

  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.

  When executing SELECT, set the collation of the expression a=b to case_insensitive.

  1 2 3 4 5

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

• Scenario 2

  In the SELECT statement, the expression instr(a,b) has multiple collations, causing a conflict. As a result, the error could not determine which collation to use for string searching is reported.

  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.

  When executing SELECT, set the collation rule of column a to case_insensitive or set the collation rule of column b to C to ensure unified collation rules.

  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)