Extension Functions and Operators
pkg_bpchar_opc()
Description: Serves as an extension API to add the comparison operator between bpchar and text or between text and bpchar policies, so as to solve the problem that indexes cannot be matched when data of the bpchar and text types is compared. Only a system administrator can install extensions.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
/* Create a table and initialize data. */ 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; /* The purpose is to obtain the detailed execution plan of all nodes. */ gaussdb=# SET max_datanode_for_plan = 1; SET /* If no extension is installed, when nchar and text are compared, nchar is implicitly converted to text because there is no bpchar or text index operator. That is, the fixed-length character type is converted to the variable-length character type. As a result, the execution plan changes and the index cannot be matched. */ 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) /* The log_id column in the logs_nchar table is of nchar(16) type. The inserted data is 'FE306991300002 ', which is implicitly converted to text. During comparison, spaces are deleted, that is, 'FE306991300002'='FE306991300002 '. Therefore, the data is not matched. */ gaussdb=# SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' '); log_id | log_message --------+------------- (0 rows) /* Delete the table. */ gaussdb=# DROP TABLE logs_nchar; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
/* Create a table and initialize data. */ 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; /* A system administrator installs the pkg_bpchar_opc extension. The comparison operators of the bpchar and text types and index-related content are added to the database. */ 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) /* In this case, when log_id is implicitly converted to the bpchar type and compared with the text type, the comparison operator and index information can be found, and the index can be matched. */ gaussdb=# SELECT * FROM logs_nchar WHERE log_id = RPAD(TRIM('FE306991300002 '),16,' '); log_id | log_message ------------------+------------- FE306991300002 | 002 (1 row) /* Delete the table and extension. */ gaussdb=# DROP TABLE logs_nchar; gaussdb=# DROP EXTENSION pkg_bpchar_opc; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
/* Create a table and initialize data. */ 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) /* Delete the table. */ gaussdb=# DROP TABLE logs_text; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
/* Create a table and initialize data. */ 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) /* Delete the table and extension. */ gaussdb=# DROP TABLE logs_text; gaussdb=# DROP EXTENSION pkg_bpchar_opc; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
/* Create a table and initialize data. */ 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) /* Delete the table. */ gaussdb=# DROP TABLE logs_varchar2; gaussdb=# DROP TABLE logs_char; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
/* Create a table and initialize data. */ 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 /* This format is not recommended. After the extension is installed, the varchar2 type of log_id in the t1 table is implicitly converted to the text type. When it is compared with the log_id in the t2 table, the char type of log_id in the t2 table is implicitly converted to the bpchar type. In this case, spaces after log_id is removed by the database, that is, 'FE306991300002'='FE306991300002 '. Therefore, no data is matched. */ /* Incorrect example: */ 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) /* This format is recommended to avoid the following problems: the data type of log_id in the t1 table is converted to the text type, spaces are reserved during comparison, and data cannot be matched when the data type of log_id in the t2 table is compared with the data type of log_id in the t1 table. The type of t1 table is forcibly converted to the bpchar type before the extension is installed, that is, 'FE306991300002' = 'FE306991300002'. Therefore, data is matched. */ /* Correct example: */ 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) /* The execution plan is the same as that before the extension is installed. */ 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) /* Implicit conversion between bpchar and text of indexes. */ 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) /* Delete the table and extension. */ 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; |

- This solves the problem that data and indexes cannot be properly matched when equality matching is performed between the bpchar type (containing multiple spaces) and the text type.
- The UB-tree and B-tree are involved. The comparison symbols include >, >=, <, <=, and <>.
- The impact scope involves implicit conversion between character types. For example, when a variable-length data type is compared with a fixed-length data type, the variable-length data type is preferentially converted to the text type instead of the original bpchar type.
- The pkg_bpchar_opc extension is disabled by default. You can check whether the extension is enabled in the pg_extension system catalog. If the extension data exists, the extension is enabled. If the extension data does not exist, the extension is disabled. When extension is disabled, forward compatibility is maintained. When extension is enabled, compatibility with database ORA is maintained. After the pkg_bpchar_opc extension is enabled, you need to set the max_datanode_for_plan parameter as required. This parameter specifies the number of execution plans displayed on DNs when the FQS plan is generated. For example, to set max_datanode_for_plan to 64, run the following command:
set max_datanode_for_plan = 64
The extended function is for internal use only. You are advised not to use it.
- In the example, the table structure uses log_id as the index and has two columns: log_id and log_message. The table name is followed by the log_id column type. For example, if the table name is logs_text, the log_id column type is text.
API |
Description |
---|---|
pg_catalog.bpchar_text_lt |
Compares the bpchar type with the text type to check whether the value on the left is less than the value on the right. |
pg_catalog.bpchar_text_le |
Compares the bpchar type with the text type to check whether the value on the left is less than or equal to the value on the right. |
pg_catalog.bpchar_text_eq |
Compares the bpchar type with the text type to check whether the value on the left is equal to the value on the right. |
pg_catalog.bpchar_text_ge |
Compares the bpchar type with the text type to check whether the value on the left is greater than or equal to the value on the right. |
pg_catalog.bpchar_text_gt |
Compares the bpchar type with the text type to check whether the value on the left is greater than the value on the right. |
pg_catalog.bpchar_text_ne |
Compares the bpchar type with the text type to check whether the value on the left is different from the value on the right. |
pg_catalog.bpchar_text_cmp |
Specifies that the index of the bpchar and text types supports comparison functions. |
pg_catalog.text_bpchar_lt |
Compares the text type with the bpchar type to check whether the value on the left is less than the value on the right. |
pg_catalog.text_bpchar_le |
Compares the text type with the bpchar type to check whether the value on the left is less than or equal to the value on the right. |
pg_catalog.text_bpchar_eq |
Compares the text type with the bpchar type to check whether the value on the left is equal to the value on the right. |
pg_catalog.text_bpchar_ge |
Compares the text type with the bpchar type to check whether the value on the left is greater than or equal to the value on the right. |
pg_catalog.text_bpchar_gt |
Compares the text type with the bpchar type to check whether the value on the left is greater than the value on the right. |
pg_catalog.text_bpchar_ne |
Compares the text type with the bpchar type to check whether the value on the left is different from the value on the right. |
pg_catalog.text_bpchar_cmp |
Specifies that the index of the text and bpchar types supports comparison functions. |
pg_catalog.hashbpchartext |
Specifies that the hash of the bpchar and text types supports comparison functions. |
pg_catalog.hashtextbpchar |
Specifies that the hash of the text and bpchar types supports comparison functions. |
bpcharlikebpchar(BPCHAR, BPCHAR)
Description: Determines whether the BPCHAR character string of the first input parameter is LIKE and whether the BPCHAR character string of the second input parameter is LIKE. It is used to add the BPCHAR type and the LIKE operator of the BPCHAR type. To enable the ~~ operator, ensure that the value of the GUC parameter behavior_compat_options contains the enable_bpcharlikebpchar_compare configuration item.
Return type: Boolean.
Value range:
- t: Two parameters of the BPCHAR type are matched.
- f: Two parameters of the BPCHAR type are not matched.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
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) -- If behavior_compat_options does not contain enable_bpcharlikebpchar_compare, the latest bpcharlikebpchar operator is not enabled and the result set returned by the matching between bpchars is not the same as expected (all data should be returned in normal cases). 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) -- After this parameter is enabled, the latest bpcharlikebpchar operator is enabled, and the returned behavior meets the expected behavior during matching. 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 |

- bpcharlikebpchar can be used only when the database compatibility parameter SQL_COMPATIBILITY is set to ORA and the GUC parameter behavior_compat_options contains the enable_bpcharlikebpchar_compare column.
- If this feature is enabled, the result set and execution plan for the LIKE pattern matching of the BPCHAR types are affected.
- SET behavior_compat_options=''; indicates that this feature is disabled, and SET behavior_compat_options='enable_bpcharlikebpchar_compare' indicates that this feature is enabled.
- After the new feature is enabled, fixed-length matching is used (bpchar matches bpchar). The parameter lengths on the left and right sides must be the same. During pattern matching, ensure that the length of the pattern column is the same as the forcible conversion length to avoid the difference between the result and the expected result caused by filling spaces after the length is too long.
bpcharnlikebpchar(BPCHAR, BPCHAR)
Description: Determines whether the BPCHAR character string of the first input parameter is NOT LIKE and whether the BPCHAR character string of the second input parameter is NOT LIKE. It is used to add the BPCHAR type and the NOT LIKE operator of the BPCHAR type. To enable the !~~ operator, ensure that the value of behavior_compat_options contains the enable_bpcharlikebpchar_compare configuration item.
Return type: Boolean.
Value range:
- t: Two parameters of the BPCHAR type are matched.
- f: Two parameters of the BPCHAR type are not matched.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
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) -- If behavior_compat_options does not contain enable_bpcharlikebpchar_compare, the latest bpcharnlikebpchar operator is not enabled and the result set returned by the matching between bpchars is not the same as expected (no data record is returned in normal cases). 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) -- After this parameter is enabled, the latest bpcharnlikebpchar operator is enabled, and the returned behavior meets the expected behavior during matching. 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 |

- bpcharlikebpchar can be used only when the database compatibility parameter SQL_COMPATIBILITY is set to ORA and the GUC parameter behavior_compat_options contains the enable_bpcharlikebpchar_compare column.
- If this feature is enabled, the result set and execution plan for the NOT LIKE pattern matching of the BPCHAR types are affected.
- SET behavior_compat_options=''; indicates that this feature is disabled, and SET behavior_compat_options='enable_bpcharlikebpchar_compare' indicates that this feature is enabled.
- After the new feature is enabled, fixed-length matching is used (bpchar matches bpchar). The parameter lengths on the left and right sides must be the same. During pattern matching, ensure that the length of the pattern column is the same as the forcible conversion length to avoid the difference between the result and the expected result caused by filling spaces after the length is too long.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot