【我和openGauss的故事】原生openGauss与Oracle数据库函数兼容性对比验证测试

尚雷 openGauss 2023-08-08 18:00 发表于四川

收录于合集#第六届openGauss技术文章征集初审合格文章62个

一、测试目的

近期研究了原生openGauss和Oracle数据库兼容性，特整理测试了Oracle常用函数和openGauss数据库的对比测试。

二、创建DUAL虚拟表

openGauss 数据库不提供DUAL虚拟表，需要通过如下方式创建视图模拟dual虚拟表：

create or replace view dual as select NULL::"unknown" where 1=1;

alter table dual owner to 连接用户名; grant all on table dual to 连接用户名; grant select on table dual to public;

三、测试结果

测试语句及结果如下：

序号	函数名称	查询语句	openGauss查询结果	Oracle查询结果	是否兼容
1	ASCII	SELECT ASCII('A') FROM DUAL;	openGauss=# SELECT ASCII('A') FROM DUAL; ascii ------- 65 (1 row)	SQL> SELECT ASCII('A') FROM DUAL; ASCII('A') ---------- 65	支持
2	**CHR**	SELECT CHR(65) FROM DUAL;	openGauss=# SELECT CHR(65) FROM DUAL; chr ----- A (1 row)	SQL> SELECT CHR(65) FROM DUAL; C - A	支持
3	**CONCAT**		openGauss=# SELECT CONCAT(CONCAT('Hello', ' '), 'World') FROM DUAL; concat ------------- Hello World (1 row)	SQL> SELECT CONCAT(CONCAT('Hello', ' '), 'World') FROM DUAL; CONCAT(CONC ----------- Hello World	支持
4	**INITCAP**	SELECT INITCAP('hello world') FROM DUAL;	openGauss=# SELECT INITCAP('hello world') FROM DUAL; initcap ------------- Hello World (1 row)	SQL> SELECT INITCAP('hello world') FROM DUAL; INITCAP('HE ----------- Hello World	支持
5	**INSTR**	SELECT INSTR('Hello World', 'o') FROM DUAL;	openGauss=# SELECT INSTR('Hello World', 'o') FROM DUAL; instr ------- 5 (1 row	SQL> SELECT INSTR('Hello World', 'o') FROM DUAL; INSTR('HELLOWORLD','O') ----------------------- 5	支持
6	**LENGTH**	SELECT LENGTH('Hello') FROM DUAL;	openGauss=# SELECT LENGTH('Hello') FROM DUAL; length -------- 5 (1 row)	SQL> SELECT LENGTH('Hello') FROM DUAL; LENGTH('HELLO') --------------- 5	支持
7	**LOWER**	SELECT LOWER('Hello') FROM DUAL;	openGauss=# SELECT LOWER('Hello') FROM DUAL; lower ------- hello (1 row)	SQL> SELECT LOWER('Hello') FROM DUAL; LOWER ----- hello	支持
8	**UPPER**	SELECT UPPER('Hello') FROM DUAL;	openGauss=# SELECT UPPER('Hello') FROM DUAL; upper ------- HELLO (1 row)	SQL> SELECT UPPER('Hello') FROM DUAL; UPPER ----- HELLO	支持
9	**RPAD and LPAD**	SELECT RPAD('Hello', 10, ' ') FROM DUAL;SELECT LPAD('Hello', 10, ' ') FROM DUAL;	openGauss=# SELECT RPAD('Hello', 10, ' ') FROM DUAL; rpad ------------ Hello (1 row) openGauss=# SELECT LPAD('Hello', 10, ' ') FROM DUAL; lpad ------------ Hello (1 row)	SQL> SELECT RPAD('Hello', 10, ' ') FROM DUAL; RPAD('HELL ---------- Hello SQL> SELECT LPAD('Hello', 10, ' ') FROM DUAL; LPAD('HELL ---------- Hello	支持
10	**LTRIM and RTRIM**	SELECT LTRIM(' Hello ') FROM DUAL;SELECT RTRIM(' Hello ') FROM DUAL;	openGauss=# SELECT LTRIM(' Hello ') FROM DUAL; ltrim ---------- Hello (1 row) openGauss=# SELECT RTRIM(' Hello ') FROM DUAL; rtrim ---------- Hello (1 row)	SQL> SELECT LTRIM(' Hello ') FROM DUAL; LTRIM('H -------- Hello SQL> SELECT RTRIM(' Hello ') FROM DUAL; RTRIM('H -------- Hello	支持
11	**SUBSTR**	SELECT SUBSTR('Hello World', 7, 5) FROM DUAL;	openGauss=# SELECT SUBSTR('Hello World', 7, 5) FROM DUAL; substr -------- World (1 row)	SQL> SELECT SUBSTR('Hello World', 7, 5) FROM DUAL; SUBST ----- World	支持
12	**REPLACE**		openGauss=# SELECT REPLACE('Hello World', 'o', 'a') FROM DUAL; replace ------------- Hella Warld (1 row)	SQL> SELECT REPLACE('Hello World', 'o', 'a') FROM DUAL; REPLACE('HE ----------- Hella Warld	支持
13	**SOUNDEX**	SELECT SOUNDEX('Hello') FROM DUAL;	openGauss=# SELECT SOUNDEX('Hello') FROM DUAL; ERROR: function soundex(unknown) does not exist LINE 1: SELECT SOUNDEX('Hello') FROM DUAL; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. CONTEXT: referenced column: soundex	SQL> SELECT SOUNDEX('Hello') FROM DUAL; SOUN ---- H400	不支持
14	**TRIM**	SELECT TRIM(' Hello ') FROM DUAL;	openGauss=# SELECT TRIM(' Hello ') FROM DUAL; btrim ------- Hello (1 row)	SQL> SELECT TRIM(' Hello ') FROM DUAL; TRIM( ----- Hello	支持
15	**ACOS**	SELECT ACOS(0) FROM DUAL;	openGauss=# SELECT ACOS(0) FROM DUAL; acos ----------------- 1.5707963267949 (1 row)	SQL> SELECT ACOS(0) FROM DUAL; ACOS(0) ---------- 1.57079633	支持
16	**ASIN**	SELECT ASIN(0) FROM DUAL;	openGauss=# SELECT ASIN(0) FROM DUAL; asin ------ 0 (1 row)	SQL> SELECT ASIN(0) FROM DUAL; ASIN(0) ---------- 0	支持
17	**ATAN**	SELECT ATAN(1) FROM DUAL;	openGauss=# SELECT ATAN(1) FROM DUAL; atan ------------------ .785398163397448 (1 row)	SQL> SELECT ATAN(1) FROM DUAL; ATAN(1) ---------- .785398163	支持
18	**CEIL**	SELECT CEIL(3.14) FROM DUAL;	openGauss=# SELECT CEIL(3.14) FROM DUAL; ceil ------ 4 (1 row)	SQL> SELECT CEIL(3.14) FROM DUAL; CEIL(3.14) ---------- 4	支持
19	**COS**	SELECT COS(0) FROM DUAL;	openGauss=# SELECT COS(0) FROM DUAL; cos ----- 1 (1 row)	SQL> SELECT COS(0) FROM DUAL; COS(0) ---------- 1	支持
20	**COSH**	SELECT COSH(0) FROM DUAL;	openGauss=# SELECT COSH(0) FROM