GaussDB(DWS)排序规则相关错误信息整理

排序规则collation相关报错信息整理

使用场景报错

1，
GAUSS-00058: “collations are not supported by type %s”
错误原因：collation与类型不匹配，类型问题。
解决办法：用户检查语句中的类型，collate仅支持字符相关类型。
示例：

postgres=# create table t1(a int collate "C");
ERROR:  collations are not supported by type integer
LINE 1: create table t1(a int collate "C");
                              ^

2，
GAUSS-00092: “collation mismatch between implicit collations ‘%s’ and ‘%s’”
GAUSS-00093: “collation mismatch between explicit collations ‘%s’ and ‘%s’”
错误原因：两个字符集不能进行隐式/显式转换。
解决办法：字符集转换时要保证两者之间是兼容的。
示例：

postgres=# create table t1(a text collate "C");
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
postgres=# create table t2(a text collate "POSIX");
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
postgres=# select * from t1 union select * from t2;
ERROR:  collation mismatch between implicit collations "C" and "POSIX"
LINE 1: select * from t1 union select * from t2;
                                      ^
HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
postgres=# select * from t1 where a collate "C" > 'abc' collate "POSIX";
ERROR:  collation mismatch between explicit collations "C" and "POSIX"
LINE 1: select * from t1 where a collate "C" > 'abc' collate "POSIX"...
                                                     ^

3，
GAUSS-00191: “recursive query ‘%s’ column %d has collation ‘%s’ in non-recursive term but collation ‘%s’ overall”
错误原因：递归查询中的列的字符集和非递归查询的列的字符集不一致。
解决办法：将非递归查询的输出字符集转换成递归查询的输出字符集。
示例：

postgres=# WITH RECURSIVE foo(x) AS
postgres-#    (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x)
postgres(#    UNION ALL
postgres(#    SELECT (x || 'c') COLLATE "POSIX" FROM foo WHERE length(x) < 10)
postgres-# SELECT * FROM foo;
ERROR:  recursive query "foo" column 1 has collation "C" in non-recursive term but collation "POSIX" overall
LINE 2:    (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x)
                   ^
HINT:  Use the COLLATE clause to set the collation of the non-recursive term.

4，
GAUSS-00506: “new collation (%s) is incompatible with the collation of the template database (%s)”
错误原因：在创建数据库语法中，指定新数据库使用的字符集与template0模板数据库的字符集不匹配。
解决办法：请使用命令"show lc_collate;"检查template0模板数据库的字符集，并修改当前创建数据库指定的字符集。
示例：

postgres=# \l
                                   List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 postgres  | jack  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | jack  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/jack          +
           |       |          |             |             | jack=CTc/jack
 template1 | jack  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/jack          +
           |       |          |             |             | jack=CTc/jack
(3 rows)

postgres=# create database testdb LC_COLLATE='zh_CN.utf8';
ERROR:  new collation (zh_CN.utf8) is incompatible with the collation of the template database (en_US.UTF-8)
HINT:  Use the same collation as in the template database, or use template0 as template.

5，
GAUSS-00551: “could not determine which collation to use for view column ‘%s’”
错误原因：对于视图的列无法决策使用哪种排序规则。
解决办法：使用COLLATE子句指定排序规则。
示例：

postgres=# create view v1 as select t1.a || t2.a from t1,t2;
ERROR:  could not determine which collation to use for view column "?column?"
HINT:  Use the COLLATE clause to set the collation explicitly.

6，
GAUSS-00908: “column collation mismatch in ALTER TABLE EXCHANGE PARTITION”
错误原因：ALTER TABLE EXCHANGE PARTITION语句执行表的列排序不匹配。
解决办法：建议检查ALTER TABLE EXCHANGE PARTITION语句执行的表是否有相同的列排序，否则不能执行。
示例：

postgres=# create table t3 (a int,b text collate "C")
postgres-# with (orientation = column,compression=middle)
postgres-# distribute by hash (a);
CREATE TABLE
postgres=# create table t4 (a int,b text)
postgres-# with (orientation = column,compression=middle)
postgres-# distribute by hash (a)
postgres-# partition by range(b)(
postgres(# partition p1 values less than('a'),
postgres(# partition p2 values less than('h'),
postgres(# partition p3 values less than(maxvalue));
CREATE TABLE
postgres=# alter table t4 exchange partition (p3) with table t3;
ERROR:  column collation mismatch in ALTER TABLE EXCHANGE PARTITION

7，
GAUSS-01058: “no collation was derived for column ‘%s’ with collatable type %s”
错误原因：属性列支持attcollation但是对应的attcollation无效，导致无法正常创建heap表格。
解决办法：检查创建表格时属性列关于collation定义的有效性。
示例：

postgres=# create table t5 as select a from t1 union all select a from t2;
ERROR:  no collation was derived for column "a" with collatable type text
HINT:  Use the COLLATE clause to set the collation explicitly.

8，
GAUSS-01280: “unique index columns must contain the partition key and collation must be default collation”
错误原因：唯一索引列须包含分区键，排序方式须为默认方式。
解决办法：检查唯一索引列，须包含分区键，排序方式须为默认方式。
示例：

postgres=# create table t6 (a int,b text collate "C")
postgres-# distribute by hash (a)
postgres-# partition by range(b)(
postgres(# partition p1 values less than('a'),
postgres(# partition p2 values less than('h'),
postgres(# partition p3 values less than(maxvalue));
CREATE TABLE
postgres=# alter table t6 add constraint t6_unique_key primary key (a);
ERROR:  unique index columns must contain the partition key and collation must be default collation
postgres=# alter table t6 add constraint t6_unique_key unique (a,b);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "t6_unique_key" for table "t6"
ALTER TABLE

9，
GAUSS-01289: “collation ‘%s’ does not exist”
错误原因：索引的排序模式不存在。
解决办法：检查索引的排序模式是否正确。
示例：

postgres=# create table t7(c1 int,c2 text)
postgres-# partition by range (c2)(
postgres(# partition p0 values less than ('a'),
postgres(# partition p1 values less than ('b'),
postgres(# partition p2 values less than (maxvalue));
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
postgres=# create unique index t7_idx on t7 (c2 COLLATE "xxxx") local(
postgres(# partition p0,partition p1,partition p2);
ERROR:  collation "xxxx" does not exist

10，
GAUSS-01296: “could not determine which collation to use for index expression”
错误原因：无法决定在表达式索引中使用哪种排序方式。
解决办法：需要在创建表达式索引时指定排序方式。
示例：

postgres=# create table t8 (a text collate "C", b 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
postgres=# select collation for(ifnull(a,b)) from t8;
 pg_collation_for
------------------

(1 row)
postgres=# create index t8_idx on t8(ifnull(a,b));
ERROR:  could not determine which collation to use for index expression
HINT:  Use the COLLATE clause to set the collation explicitly.

11，
GAUSS-01785: “collation ‘%s’ for encoding ‘%s’ does not exist”
错误原因：对应于GetDatabaseEncodingName()所得到的编码方法并不支持NameListToString(name)所对应的字符集。
解决办法：查询手册确认当前系统所支持的字符集以及相应的编码方式。
示例：

postgres=# select 'a' collate "dummy";
ERROR:  collation "dummy" for encoding "UTF8" does not exist
LINE 1: select 'a' collate "dummy";
                   ^

12，
GAUSS-02102: “could not determine which collation to use for regular expression”
错误原因：对于正则表达式，无法确定合适的排序规则。
解决办法：请检查正则表达式的调用。
示例：

postgres=# insert into t8 values('a','b');
INSERT 0 1
postgres=# select regexp_matches(ifnull(a,b),'[Ab]') from t8;
ERROR:  could not determine which collation to use for regular expression
HINT:  Use the COLLATE clause to set the collation explicitly.
CONTEXT:  referenced column: regexp_matches

13，
GAUSS-02103: “nondeterministic collations are not supported for regular expressions”
错误原因：对于正则表达式，不支持不确定行为的排序规则。
解决办法：手动指定为确定行为的排序规则。
示例：

postgres=# select regexp_like('abcd' collate case_insensitive,'[A-D]') from t8;
ERROR:  nondeterministic collations are not supported for regular expressions
CONTEXT:  SQL function "regexp_like" statement 1
referenced column: regexp_like

14,
GAUSS-02952: “could not determine which collation to use for string %s”
%s可以是comparison，hashing，searching
错误原因：存在多个collation造成冲突，解析器无法决定使用哪个。
解决办法：手动指定COLLATE collation_name。
示例：

postgres=# select a=b from t8;
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.
postgres=# select hashtext(ifnull(a,b)) from t8;
ERROR:  could not determine which collation to use for string hashing
HINT:  Use the COLLATE clause to set the collation explicitly.
CONTEXT:  referenced column: hashtext
postgres=# select instr(a,b) from t8;
ERROR:  could not determine which collation to use for string searching
HINT:  Use the COLLATE clause to set the collation explicitly.
CONTEXT:  referenced column: instr

15,
GAUSS-03164: “could not determine which collation to use for ILIKE”
错误原因：ilike语句中，存在多个collation造成冲突，解析器无法决定使用哪个。
解决办法：手动指定COLLATE collation_name。
示例：

postgres=# select ifnull(a,b) ilike 'A%' from t8;
ERROR:  could not determine which collation to use for ILIKE
HINT:  Use the COLLATE clause to set the collation explicitly.

16，
GAUSS-03318: “nondefault collations are not supported on this platform”
错误原因：非缺省的collation不支持，仅出现在不支持locale_t的平台。
解决办法：使用平台缺省的collation。

17，
GAUSS-05180: “encoding ‘%s’ not supported by collation ‘%s’”
错误原因：排序规则不支持当前数据库的字符编码。
解决办法：查阅产品文档，选择支持当前字符编码的排序规则。
示例：

postgres=# CREATE DATABASE test_db ENCODING 'eucjis2004' LC_CTYPE='C' LC_COLLATE='C' template = template0;
CREATE DATABASE
postgres=# \c test_db
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "test_db" as user "jack".
test_db=# select 'a' collate case_insensitive;
ERROR:  encoding "EUC_JIS_2004" not supported by collation "case_insensitive"
LINE 1: select 'a' collate case_insensitive;
                   ^

18，
GAUSS-05246: “Collation ‘case_insensitive’ for DDL statements cannot be used in upgrade mode.”
错误原因：处于升级模式无法使用的排序规则。
解决办法：升级模式结束后使用。

19，
GAUSS-06506: “could not determine which collation to use for bound expression of %s”
错误原因：分区边界表达式排序规则无效或与分区键排序规则不一致。
解决办法：指定排序规则与分区键一致。
示例：

postgres=# create table t9(c1 int,c2 text collate "C")
postgres-# partition by range (c2)(
postgres(# partition p0 values less than ('a' collate "POSIX"),
postgres(# partition p1 values less than ('b'),
postgres(# partition p2 values less than (maxvalue));
ERROR:  could not determine which collation to use for bound expression of partitioning column "c2" in partition "p0"
HINT:  the collation of bound expression must be euqal to the collation of partitioning column

20，
GAUSS-07314: “could not determine which collation to use for %s function”
%s可以是lower()，upper()，initcap()
错误原因：函数调用时，存在多个collation造成冲突，解析器无法决定使用哪个。
解决办法：应该使用COLLATE子句显式设定collation。

postgres=# select upper(ifnull(a,b)) from t8;
ERROR:  could not determine which collation to use for upper() function
HINT:  Use the COLLATE clause to set the collation explicitly.
CONTEXT:  referenced column: upper
postgres=# select lower(ifnull(a,b)) from t8;
ERROR:  could not determine which collation to use for lower() function
HINT:  Use the COLLATE clause to set the collation explicitly.
CONTEXT:  referenced column: lower
postgres=# select initcap(ifnull(a,b)) from t8;
ERROR:  could not determine which collation to use for initcap() function
HINT:  Use the COLLATE clause to set the collation explicitly.
CONTEXT:  referenced column: initcap

特性相关

特性相关报错，受制于特性约束，用户不可见。

1,
GAUSS-00688: “inherited column ‘%s’ has a collation conflict”
GAUSS-00691: “column ‘%s’ has a collation conflict”
GAUSS-00732: “child table ‘%s’ has different collation for column ‘%s’”
GAUSS-01538: “attribute ‘%s’ of relation ‘%s’ does not match parent’s collation”
场景说明：inherits场景不支持。

2，
GAUSS-01311: “collation attribute ‘%s’ not recognized”
GAUSS-01314: “collation ‘%s’ for encoding ‘%s’ already exists in schema ‘%s’”
GAUSS-01315: “collation ‘%s’ already exists in schema ‘%s’”
GAUSS-01791: “collation ‘%s’ for encoding ‘%s’ already exists”
GAUSS-01792: “collation ‘%s’ already exists”
场景说明：create collation语法不支持。

内部错误

非预期错误，请联系工程师分析解决。

1，GAUSS-00340: “cache lookup failed for collation %u”
2，GAUSS-01706: “no collation was derived for column ‘%s’ with collatable type %s”
3，GAUSS-01793: “could not find tuple for collation %u”
4，GAUSS-01906: “collation with OID %u does not exist”
5，GAUSS-02099: “cannot get collation for untransformed sublink”
6，GAUSS-02100: “cannot set collation for untransformed sublink”
7，GAUSS-05659: “could not open collator for locale ‘%s’: %s”
8，GAUSS-07313: “case conversion failed: %s”
9，GAUSS-07514: “could not open ICU converter for encoding ‘%s’: %s”
10，GAUSS-07515: “ICUToUChar failed: %s”
11，GAUSS-07516: “ICUFromUChar failed: %s”
12，GAUSS-07461: “collation failed: %s”
13，GAUSS-07462: “sort key generation failed: %s”
14，GAUSS-07510: “Collations are not supported by type %s.”
15，GAUSS-07513: “collations with different collate and ctype values are not supported by ICU”
16，GAUSS-07640: “encoding ‘%s’ not supported by ICU”

想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技，后台还可获取众多学习资料哦~