GaussDB(DWS) TD和Oracle兼容模式的差异

GaussDB(DWS)支持两种兼容模式，即TD(Teradata)兼容模式、ORA(Oracle)兼容模式。可以在CREATE DATABASE时通过指定选项DBCOMPATIBILITY进行选择。语法如下：

--创建兼容TD的数据库
postgres=# CREATE DATABASE td_compatible_db DBCOMPATIBILITY 'TD';
CREATE DATABASE
--创建兼容ORA的数据库
postgres=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'ORA';
CREATE DATABASE
postgres=# SELECT datname,datcompatibility FROM PG_DATABASE WHERE datname LIKE '%compatible_db';
      datname      | datcompatibility 
-------------------+------------------
 td_compatible_db  | TD
 ora_compatible_db | ORA
(2 rows)

两种兼容模式的注意差异对比如下表格所示：

下面对每一个兼容性进行举例说明：

-- 切到TD兼容的库下，创建表并插入数据
postgres=# \c td_compatible_db
td_compatible_db=# CREATE TABLE td_table(a INT,b VARCHAR(5),c date);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
td_compatible_db=# INSERT INTO td_table VALUES(1,null,CURRENT_DATE);   
INSERT 0 1
td_compatible_db=# INSERT INTO td_table VALUES(2,'',CURRENT_DATE);  
INSERT 0 1

-- 区分空串和NULL，date类型只显示年月日

td_compatible_db=# SELECT a, b, b IS NULL AS null, c FROM td_table;
 a | b | null |     c      
---+---+------+------------
 1 |   | t    | 2020-06-19
 2 |   | f    | 2020-06-19
(2 rows)
td_compatible_db=# SELECT CURRENT_DATE;
    date    
------------
 2020-06-19
(1 row)

-- 空串转int，转为0。

TD数据库不同于Oracle，Oracle将空串当做NULL进行处理，TD在将空串转换为数值类型的时候，默认将空串转换为0进行处理，因此查询空串会查询到数值为0的数据。同样地，在TD兼容模式下，字符串转换数值的过程中，也会将空串默认转换为相应数值类型的0值进行处理。除此之外，' - '、' + '、' '这些字符串也都会在TD兼容模式下默认转换为0进行处理，但是小数点字符串' . '会报错

td_compatible_db=# SELECT b::int FROM td_table WHERE b = '';   
 b 
---
 0
(1 row)

--超长字符自动截断。

当连接到TD兼容的数据库时，td_compatible_truncation参数设置为on时，将启用超长字符串自动截断功能，在后续的insert语句中（不包含外表的场景下），对目标表中char和varchar类型的列上插入超长字符串时，系统会自动按照目标表中相应列定义的最大长度对超长字符串进行截断。

td_compatible_db=# SHOW td_compatible_truncation;
 td_compatible_truncation 
--------------------------
 on
(1 row)
td_compatible_db=# INSERT INTO td_table VALUES(3,'12345678',CURRENT_DATE);
INSERT 0 1
td_compatible_db=# SELECT * FROM td_table WHERE a = 3;                    
 a |   b   |     c      
---+-------+------------
 3 | 12345 | 2020-06-19
(1 row)

--varchar   + int运算，转为numeric + numeric计算

td_compatible_db=# EXPLAIN VERBOSE SELECT b + a FROM td_table WHERE a = 3;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
   Output: (((td_table.b)::numeric + (td_table.a)::numeric))
   Node/s: datanode7
   Remote query: SELECT b::numeric + a::numeric AS "?column?" FROM public.td_table WHERE a = 3
(4 rows)

-- case和coalesce表达式。

对于case 和coalesce，在TD 兼容模式下的处理

● 如果所有输入都是相同的类型，并且不是unknown类型，那么解析成这种类型。

● 如果所有输入都是unknown类型则解析成text类型。

● 如果输入字符串（包括unknown，unknown当text来处理）和数字类型，那么解析成字符串类型，如果是其他不同的类型范畴，则报错。

● 如果输入类型是同一个类型范畴，则选择该类型的优先级较高的类型。

● 把所有输入转换为所选的类型。如果从给定的输入到所选的类型没有隐式转换则失败。

示例1：Union中的待定类型解析。这里，unknown类型文本'b'将被解析成text类型。

td_compatible_db=# SELECT text 'a' AS "text" UNION SELECT 'b';
text
------
a
b
(2 rows)

示例2：简单Union中的类型解析。文本1.2的类型为numeric，而且integer类型的1可以隐含地转换为numeric，因此使用这个类型。

td_compatible_db=# SELECT 1.2 AS "numeric" UNION SELECT 1;
numeric
---------
1
1.2
(2 rows)

示例3：转置Union中的类型解析。这里，因为类型real不能被隐含转换成integer，但是integer可以隐含转换成real，那么联合的结果类型将是real。

td_compatible_db=# SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);
real
------
1
2.2
(2 rows)

示例4：TD模式下，coalesce参数输入int和varchar类型，那么解析成varchar类型。ORA模式下会报错。查看coalesce参数输入int和varchar类型的查询语句的执行计划如下

td_compatible_db=# EXPLAIN VERBOSE select coalesce(a, b) FROM td_table;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
   Output: (COALESCE((td_table.a)::character varying, td_table.b))
   Node/s: All datanodes
   Remote query: SELECT COALESCE(a::character varying, b) AS "coalesce" FROM public.td_table
(4 rows)