# 扩展函数和操作符 #### pkg_bpchar_opc() 描述：扩展接口，用于新增bpchar和text或者text和bpchar策略比较操作符，解决bpchar类型和text类型数据比较无法命中索引的问题。仅系统管理员可以安装扩展。 示例： bpchar类型和text类型比较时（初始状态，前向兼容）: ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_nchar(log_id nchar(16), log_message text); gaussdb=# INSERT INTO logs_nchar SELECT GENERATE_SERIES(1,100000),MD5(RANDOM()); gaussdb=# INSERT INTO logs_nchar VALUES ('FE306991300002 ','002'); gaussdb=# CREATE INDEX idx_nchar_logid on logs_nchar(log_id); gaussdb=# VACUUM ANALYZE logs_nchar; /* 目的就是获得所有节点的详细执行计划。 */ gaussdb=# SET max_datanode_for_plan = 1; SET /* 在没有安装扩展的情况下，当对nchar和text类型进行比较时，由于没有bpchar和text索引操作符，nchar会隐式转换为text,即定长字符类型转换成变长字符类型，导致执行计划发生变化，从而无法命中索引。 */ gaussdb=# EXPLAIN SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' '); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_nchar WHERE log_id::text = rpad(btrim('FE306991300002 '::text), 16, ' '::text) Datanode Name: datanode1 Seq Scan on logs_nchar (cost=0.00..2539.01 rows=500 width=50) Filter: ((log_id)::text = 'FE306991300002 '::text) (8 rows) /* 表logs_nchar里log_id字段类型是nchar(16)，插入数据为'FE306991300002 '，隐式转换成text类型，进行比较时，会把后面空格去掉进行比较，即'FE306991300002'='FE306991300002 '，所以不命中数据。 */ gaussdb=# SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' '); log_id | log_message --------+------------- (0 rows) /* 删除表。 */ gaussdb=# DROP TABLE logs_nchar; ``` bpchar类型和text类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）: ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_nchar(log_id nchar(16), log_message text); gaussdb=# INSERT INTO logs_nchar SELECT GENERATE_SERIES(1,100000),MD5(RANDOM()); gaussdb=# INSERT INTO logs_nchar VALUES ('FE306991300002 ','002'); gaussdb=# CREATE INDEX idx_nchar_logid on logs_nchar(log_id); gaussdb=# VACUUM ANALYZE logs_nchar; /* 系统管理员安装pkg_bpchar_opc扩展，数据库增加了bpchar和text类型比较操作符，以及索引相关内容。 */ gaussdb=# CREATE EXTENSION pkg_bpchar_opc; CREATE EXTENSION gaussdb=# SET max_datanode_for_plan = 1; SET gaussdb=# EXPLAIN SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' '); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_nchar WHERE log_id = rpad(btrim('FE306991300002 '::text), 16, ' '::text) Datanode Name: datanode1 [Bypass] Index Scan using idx_nchar_logid on logs_nchar (cost=0.00..8.27 rows=1 width=50) Index Cond: (log_id = 'FE306991300002 '::text) (9 rows) /* 此时，log_id隐式转换为bpchar类型时，和text类型比较时，能找到比较操作符以及索引信息，能命中索引。 */ gaussdb=# SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' '); log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) /* 删除表和扩展。 */ gaussdb=# DROP TABLE logs_nchar; gaussdb=# DROP EXTENSION pkg_bpchar_opc; ``` text类型和bpchar类型比较时（初始状态，前向兼容）: ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_text(log_id nchar(16), log_message text); gaussdb=# INSERT INTO logs_text SELECT GENERATE_SERIES(1,100000),MD5(RANDOM()); gaussdb=# INSERT INTO logs_text VALUES ('FE306991300002 ','002'); gaussdb=# CREATE INDEX idx_text_logid on logs_text(log_id); gaussdb=# VACUUM ANALYZE logs_text; gaussdb=# SET max_datanode_for_plan = 1; SET gaussdb=# EXPLAIN SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_text WHERE log_id = 'FE306991300002 '::bpchar::text Datanode Name: datanode1 [Bypass] Index Scan using idx_text_logid on logs_text (cost=0.00..8.27 rows=1 width=38) Index Cond: (log_id = 'FE306991300002'::text) (9 rows) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar; log_id | log_message --------+------------- (0 rows) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::text; log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) /* 删除表。 */ gaussdb=# DROP TABLE logs_text; ``` text类型和bpchar类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）: ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_text(log_id nchar(16), log_message text); gaussdb=# INSERT INTO logs_text SELECT GENERATE_SERIES(1,100000),MD5(RANDOM()); gaussdb=# INSERT INTO logs_text VALUES ('FE306991300002 ','002'); gaussdb=# CREATE INDEX idx_text_logid on logs_text(log_id); gaussdb=# VACUUM ANALYZE logs_text; gaussdb=# CREATE EXTENSION pkg_bpchar_opc; CREATE EXTENSION gaussdb=# SET max_datanode_for_plan = 1; SET gaussdb=# explain select * from logs_text t1 where t1.log_id ='FE306991300002 '::bpchar; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT log_id, log_message FROM public.logs_text t1 WHERE log_id = 'FE306991300002 '::bpchar Datanode Name: datanode1 [Bypass] Index Scan using idx_text_logid on logs_text t1 (cost=0.00..8.27 rows=1 width=38) Index Cond: (log_id = 'FE306991300002 '::bpchar) (9 rows) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::bpchar; log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) gaussdb=# SELECT * FROM logs_text WHERE log_id = 'FE306991300002 '::text; log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) /* 删除表和扩展。 */ gaussdb=# DROP TABLE logs_text; gaussdb=# DROP EXTENSION pkg_bpchar_opc; ``` hash join、text类型和bpchar类型比较时（初始状态，前向兼容）: ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_varchar2(log_id varchar2, log_message text); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300002 ','002'); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300003 ','003'); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300004 ','004'); gaussdb=# VACUUM ANALYZE logs_varchar2; gaussdb=# CREATE TABLE logs_char(log_id char(16), log_message text); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300002 ','002'); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300003 ','003'); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300004 ','004'); gaussdb=# VACUUM ANALYZE logs_char; gaussdb=# SET max_datanode_for_plan = 1; SET gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id; log_id | log_message | log_id | log_message ------------------+-------------+------------------+------------- FE306991300002 | 002 | FE306991300002 | 002 FE306991300003 | 003 | FE306991300003 | 003 FE306991300004 | 004 | FE306991300004 | 004 (3 rows) gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT t1.log_id, t1.log_message, t2.log_id, t2.log_message FROM public.logs_varchar2 t1, public.logs_char t2 WHERE t1.log_id::bpchar = t2.log_id Datanode Name: datanode1 Hash Join (cost=1.07..2.14 rows=3 width=42) Hash Cond: ((t1.log_id)::bpchar = t2.log_id) -> Seq Scan on logs_varchar2 t1 (cost=0.00..1.03 rows=3 width=21) -> Hash (cost=1.03..1.03 rows=3 width=21) -> Seq Scan on logs_char t2 (cost=0.00..1.03 rows=3 width=21) (11 rows) gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002 '; log_id | log_message | log_id | log_message ------------------+-------------+------------------+------------- FE306991300002 | 002 | FE306991300002 | 002 FE306991300002 | 002 | FE306991300003 | 003 FE306991300002 | 002 | FE306991300004 | 004 (3 rows) /* 删除表。 */ gaussdb=# DROP TABLE logs_varchar2; gaussdb=# DROP TABLE logs_char; ``` hash join、text类型和bpchar类型比较时（安装pkg_bpchar_opc扩展，与ORA保持一致）: ``` /* 建表和数据初始化。 */ gaussdb=# CREATE TABLE logs_varchar2(log_id varchar2, log_message text); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300002 ','002'); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300003 ','003'); gaussdb=# INSERT INTO logs_varchar2 VALUES ('FE306991300004 ','004'); gaussdb=# VACUUM ANALYZE logs_varchar2; gaussdb=# CREATE TABLE logs_char(log_id char(16), log_message text); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300002 ','002'); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300003 ','003'); gaussdb=# INSERT INTO logs_char VALUES ('FE306991300004 ','004'); gaussdb=# VACUUM ANALYZE logs_char; gaussdb=# CREATE EXTENSION pkg_bpchar_opc; CREATE EXTENSION gaussdb=# SET max_datanode_for_plan = 1; SET /* 不推荐写法，由于t1表的log_id的varchar2类型，受安装扩展影响，隐式转换为text类型，和t2表的log_id比较，t2表的log_id类型会由char隐式转换为bpchar类型，此时log_id后面的空格会被数据库去掉，即'FE306991300002'='FE306991300002 '，所以不命中数据。 */ /* 错误示例： */ gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id; log_id | log_message | log_id | log_message --------+-------------+--------+------------- (0 rows) gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = t2.log_id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT t1.log_id, t1.log_message, t2.log_id, t2.log_message FROM public.logs_varchar2 t1, public.logs_char t2 WHERE t1.log_id::text = t2.log_id Datanode Name: datanode1 Hash Join (cost=1.07..2.14 rows=3 width=42) Hash Cond: ((t1.log_id)::text = t2.log_id) -> Seq Scan on logs_varchar2 t1 (cost=0.00..1.03 rows=3 width=21) -> Hash (cost=1.03..1.03 rows=3 width=21) -> Seq Scan on logs_char t2 (cost=0.00..1.03 rows=3 width=21) (11 rows) gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002 '; log_id | log_message | log_id | log_message ------------------+-------------+------------------+------------- FE306991300002 | 002 | FE306991300002 | 002 FE306991300002 | 002 | FE306991300003 | 003 FE306991300002 | 002 | FE306991300004 | 004 (3 rows) /* 推荐写法，为了避免t1表的log_id的数据类型转换成text类型时保留空格，导致与t2表的log_id比较无法命中数据，可以将t1表类型强转成没安装扩展前的bpchar类型，即'FE306991300002'='FE306991300002'，以确保命中数据。 */ /* 正确示例： */ gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id::bpchar = t2.log_id; log_id | log_message | log_id | log_message ------------------+-------------+------------------+------------- FE306991300002 | 002 | FE306991300002 | 002 FE306991300003 | 003 | FE306991300003 | 003 FE306991300004 | 004 | FE306991300004 | 004 (3 rows) /* 执行计划和没安装扩展前是一致的。 */ gaussdb=# EXPLAIN SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id::bpchar = t2.log_id; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT t1.log_id, t1.log_message, t2.log_id, t2.log_message FROM public.logs_varchar2 t1, public.logs_char t2 WHERE t1.log_id::bpchar = t2.log_id Datanode Name: datanode1 Hash Join (cost=1.07..2.14 rows=3 width=42) Hash Cond: ((t1.log_id)::bpchar = t2.log_id) -> Seq Scan on logs_varchar2 t1 (cost=0.00..1.03 rows=3 width=21) -> Hash (cost=1.03..1.03 rows=3 width=21) -> Seq Scan on logs_char t2 (cost=0.00..1.03 rows=3 width=21) (11 rows) gaussdb=# SELECT * FROM logs_varchar2 t1, logs_char t2 WHERE t1.log_id = 'FE306991300002 '; log_id | log_message | log_id | log_message ------------------+-------------+------------------+------------- FE306991300002 | 002 | FE306991300002 | 002 FE306991300002 | 002 | FE306991300003 | 003 FE306991300002 | 002 | FE306991300004 | 004 (3 rows) /* 索引的bpchar与text隐式转换。 */ gaussdb=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA'; CREATE DATABASE gaussdb=# \c ora_compatible_db; Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "ora_compatible_db" as user "gaozheng". ora_compatible_db=# CREATE EXTENSION pkg_bpchar_opc; CREATE EXTENSION ora_compatible_db=# CREATE TABLE t1_1(c1 char(5), c2 int); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE ora_compatible_db=# ALTER TABLE t1_1 ADD CONSTRAINT t1_1_pkey PRIMARY KEY (c1) WITH (storage_type=USTORE); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "t1_1_pkey" for table "t1_1" ALTER TABLE ora_compatible_db=# INSERT INTO t1_1 VALUES('abc ', 0); INSERT 0 1 ora_compatible_db=# CREATE TABLE t1_2(c1 varchar(5)); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE ora_compatible_db=# INSERT INTO t1_2 VALUES('abc '); INSERT 0 1 ora_compatible_db=# CREATE /*+INDEXSCAN (t1_1 t1_1_pkey)*/ c1 FROM t1_1 WHERE c2 = 0 AND c1 IN (SELECT c1 FROM t1_2); c1 ------- abc (1 row) ora_compatible_db=# SET max_datanode_for_plan = 64; SET ora_compatible_db=# EXPLAIN CREATE /*+INDEXSCAN (t1_1 t1_1_pkey)*/ c1 FROM t1_1 WHERE c2 = 0 AND c1 IN (SELECT c1 FROM t1_2); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT/*+ IndexScan(t1_1 t1_1_pkey)*/ c1 FROM public.t1_1 WHERE c2 = 0 AND (c1 IN (SELECT t1_2.c1 FROM public.t1_2)) Datanode Name: dist1_datanode1 Nested Loop (cost=28.91..93.76 rows=4 width=24) -> HashAggregate (cost=28.91..30.91 rows=200 width=24) Group By Key: (t1_2.c1)::text -> Seq Scan on t1_2 (cost=0.00..25.13 rows=1513 width=24) -> Index Scan using t1_1_pkey on t1_1 (cost=0.00..0.30 rows=1 width=24) Index Cond: (c1 = (t1_2.c1)::text) Filter: (c2 = 0) (13 rows) /* 删除表和扩展。 */ gaussdb=# DROP TABLE t1_1; gaussdb=# DROP TABLE t1_2; gaussdb=# DROP TABLE logs_varchar2; gaussdb=# DROP TABLE logs_char; gaussdb=# DROP EXTENSION pkg_bpchar_opc; ``` ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 为了解决bpchar类型（包含多个后补空格）与text类型做等值匹配操作时无法正常匹配数据以及索引问题。 - 涉及ubtree, btree，比较符号包含： \>, \>=, \<, \<=, \<\>,。 - 影响面涉及字符类型之间的隐式转换，例如：变长和定长数据类型比较时，变长会优先转换为text类型，而不是最初的bpchar类型。 - 默认不开启pkg_bpchar_opc扩展。检验扩展启用状态可通过查询系统表pg_extension，存在对应记录则表示扩展已启用，没有则表示未开启。关闭扩展时，保持前向兼容；开启扩展时，保持与ORA数据库兼容等。开启pkg_bpchar_opc扩展后，需要设置max_datanode_for_plan参数（该参数可以控制生成FQS计划时展示显示DN上执行计划的个数），根据自身需求去设置。以设置max_datanode_for_plan参数为64为例，命令如下： ``` set max_datanode_for_plan = 64。 ``` 扩展功能为内部使用功能，不建议用户使用。 - 示例中所用的表结构都是以log_id为索引，共有log_id，log_message两个字段，表名下划线后面接的是log_id字段类型（例如：表名为logs_text,则log_id字段类型为text）。 表1pkg_bpchar_opc支持的函数说明 | 接口名称 | 描述 | |:---|:---| | pg_catalog.bpchar_text_lt | bpchar类型和text类型比较，左边数值是否小于右边的数值。 | | pg_catalog.bpchar_text_le | bpchar类型和text类型比较，左边数值是否小于等于右边的数值。 | | pg_catalog.bpchar_text_eq | bpchar类型和text类型比较，左边数值是否等于右边的数值。 | | pg_catalog.bpchar_text_ge | bpchar类型和text类型比较，左边数值是否大于等于右边的数值。 | | pg_catalog.bpchar_text_gt | bpchar类型和text类型比较，左边数值是否大于右边的数值。 | | pg_catalog.bpchar_text_ne | bpchar类型和text类型比较，左边数值是否不等于右边的数值。 | | pg_catalog.bpchar_text_cmp | bpchar类型和text类型的索引支持比较函数。 | | pg_catalog.text_bpchar_lt | text类型和bpchar 类型比较，左边数值是否小于右边的数值。 | | pg_catalog.text_bpchar_le | text类型和bpchar类型比较，左边数值是否小于等于右边的数值。 | | pg_catalog.text_bpchar_eq | text类型和bpchar类型比较，左边数值是否等于右边的数值。 | | pg_catalog.text_bpchar_ge | text类型和bpchar类型比较，左边数值是否大于等于右边的数值。 | | pg_catalog.text_bpchar_gt | text类型和bpchar类型比较，左边数值是否大于右边的数值。 | | pg_catalog.text_bpchar_ne | text类型和bpchar类型比较，左边数值是否不等于右边的数值。 | | pg_catalog.text_bpchar_cmp | text类型和bpchar类型的索引支持比较函数。 | | pg_catalog.hashbpchartext | bpchar类型和text类型的hash支持比较函数。 | | pg_catalog.hashtextbpchar | text类型和bpchar类型的hash支持比较函数。 | #### bpcharlikebpchar(BPCHAR, BPCHAR) 描述：判断第一个入参BPCHAR字符串是否LIKE第二个入参BPCHAR字符串。用于新增BPCHAR类型和BPCHAR类型的LIKE操作符。启用"\~\~"操作符需在GUC参数behavior_compat_options的取值中包含enable_bpcharlikebpchar_compare配置项。 返回值类型：boolean。 取值范围： - t：表示两个BPCHAR类型参数匹配。 - f：表示两个BPCHAR类型参数不匹配。 示例： ``` gaussdb=# SELECT bpcharlikebpchar('455'::BPCHAR(10), '455 '::BPCHAR); bpcharlikebpchar ------------------ f (1 row) gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455 '::BPCHAR(10)); bpcharlikebpchar ------------------ t (1 row) gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(10)); bpcharlikebpchar ------------------ t (1 row) gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(11)); bpcharlikebpchar ------------------ f (1 row) gaussdb=# CREATE TABLE op_test ( col BPCHAR(2) DEFAULT NULL ); CREATE TABLE gaussdb=# CREATE INDEX op_index ON op_test(col); CREATE INDEX gaussdb=# INSERT INTO op_test VALUES ('a'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('1'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('11'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('12'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('sd'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('aa'); INSERT 0 1 gaussdb=# SHOW behavior_compat_options; behavior_compat_options ------------------------- (1 row) --当behavior_compat_options不包含enable_bpcharlikebpchar_compare时，则未启用最新bpcharlikebpchar操作符，其bpchar匹配bpchar返回结果集非预期（正常应返回全部数据）。 gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col; QUERY PLAN --------------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col !~~ (col)::text) (7 rows) gaussdb=# SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col; col ----- 11 12 aa sd (4 rows) gaussdb=# SET behavior_compat_options = 'enable_bpcharlikebpchar_compare'; SET gaussdb=# SHOW behavior_compat_options; behavior_compat_options --------------------------------- enable_bpcharlikebpchar_compare (1 row) --开启参数后，将启用最新bpcharlikebpchar操作符，其匹配时返回行为符合预期。 gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col; QUERY PLAN ------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col ~~ col) (7 rows) gaussdb=# SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col; col ----- 1 11 12 a aa sd (6 rows) gaussdb=# DROP TABLE op_test; DROP TABLE ``` ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 仅在数据库兼容性参数SQL_COMPATIBILITY为ORA时，设置GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段，则可以使用bpcharlikebpchar的匹配。 - 启用该特性，会影响BPCHAR类型进行LIKE模式匹配时的结果集与执行计划。 - GUC参数behavior_compat_options中不含有enable_bpcharlikebpchar_compare字段，表示关闭该特性；GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段，表示开启该特性。 - 开启新特性后，定长匹配定长（bpchar匹配bpchar），需要指定左右两侧参数长度相同；模式匹配时，注意模式列长度与强制转换长度相同，避免过长后填补空格导致结果与预期存在差异。 #### bpcharnlikebpchar(BPCHAR, BPCHAR) 描述：判断第一个入参BPCHAR字符串是否NOT LIKE第二个入参BPCHAR字符串。用于新增BPCHAR类型和BPCHAR类型的NOT LIKE操作符。启用"!\~\~"操作符需在GUC参数behavior_compat_options的取值中包含enable_bpcharlikebpchar_compare配置项。 返回值类型：boolean。 取值范围： - t：表示两个BPCHAR类型参数不匹配。 - f：表示两个BPCHAR类型参数匹配。 示例： ``` gaussdb=# SELECT bpcharnlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(11)); bpcharnlikebpchar ------------------- t (1 row) gaussdb=# SELECT bpcharnlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(10)); bpcharnlikebpchar ------------------- f (1 row) gaussdb=# SELECT bpcharnlikebpchar('455 '::BPCHAR(10), '455 '::BPCHAR); bpcharnlikebpchar ------------------- t (1 row) gaussdb=# CREATE TABLE op_test ( col BPCHAR(2) DEFAULT NULL ); CREATE TABLE gaussdb=# CREATE INDEX op_index ON op_test(col); CREATE INDEX gaussdb=# INSERT INTO op_test VALUES ('a'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('1'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('11'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('12'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('sd'); INSERT 0 1 gaussdb=# INSERT INTO op_test VALUES ('aa'); INSERT 0 1 gaussdb=# SHOW behavior_compat_options; behavior_compat_options ------------------------- (1 row) --当behavior_compat_options不包含enable_bpcharlikebpchar_compare时，则未启用最新bpcharnlikebpchar操作符，其BPCHAR匹配BPCHAR返回结果集非预期（正常应返回0条数据）。 gaussdb=# SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col; col ----- 1 a (2 rows) gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col; QUERY PLAN --------------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col !~~ (col)::text) (7 rows) gaussdb=# SET behavior_compat_options = 'enable_bpcharlikebpchar_compare'; SET gaussdb=# SHOW behavior_compat_options; behavior_compat_options --------------------------------- enable_bpcharlikebpchar_compare (1 row) --开启参数后，将启用最新bpcharnlikebpchar操作符，其匹配时返回行为符合预期。 gaussdb=# SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col; col ----- (0 rows) gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col NOT LIKE col::BPCHAR ORDER BY col; QUERY PLAN ------------------------------------- Streaming (type: GATHER) Merge Sort Key: col Node/s: All datanodes -> Sort Sort Key: col -> Seq Scan on op_test Filter: (col !~~ col) (7 rows) gaussdb=# DROP TABLE op_test; DROP TABLE ``` ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 仅在数据库兼容性参数SQL_COMPATIBILITY为ORA时，设置GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段可以使用bpcharlikebpchar的匹配。 - 启用该特性，会影响BPCHAR类型进行NOT LIKE模式匹配时的结果集与执行计划。 - GUC参数behavior_compat_options中不含有enable_bpcharlikebpchar_compare字段，表示关闭该特性；GUC参数behavior_compat_options中含有enable_bpcharlikebpchar_compare字段，表示开启该特性。 - 开启新特性后，定长匹配定长（bpchar匹配bpchar），需要指定左右两侧参数长度相同；模式匹配时，注意模式列长度与强制转换长度相同，避免过长后填补空格导致结果与预期存在差异。