数据仓库服务 GAUSSDB(DWS)-执行业务报错could not determine which collation to use for string hashing:扩展

时间：2024-05-07 20:29:47

扩展

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

场景一

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

    
         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。

    
          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。

    
         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来保证字段排序规则的统一。

    
         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)