# DWS查询时结果不一致的常见场景和解决方法
DWS中使用SQL语句查询时会出现同一条查询语句返回结果不一致的问题，此类问题大部分都是由于语法使用不当或用法不合理导致，通过合理的业务使用可以避免此类问题。以下列举了几种常见的查询结果不一致的场景和对应的解决办法供参考：
#### 窗口函数中使用排序后取部分结果
**场景：**
窗口函数row_number()中使用排序后查询表t3的c列，两次查询结果不同。
```
SELECT * FROM t3 order by 1,2,3;
 a | b | c 
---+---+--- 
 1 | 2 | 1 
 1 | 2 | 2 
 1 | 2 | 3
(3 rows) 
SELECT c,rn FROM (select c,row_number() over(order by a,b) as rn from t3) where rn = 1; 
 c | rn 
---+---- 
1  |  1
(1 row) 
SELECT c,rn FROM (select c,row_number() over(order by a,b) as rn from t3) where rn = 1;
 c | rn 
---+---- 
 3 |  1
(1 row)
```
**原因分析：**
如上所示，执行同一条语句：*select c,rn from (select c,row_number() over(order by a,b) as rn from t3) where rn = 1;* 两次查询结果不同，因为在窗口函数的排序列a、b上存在重复值1、2且重复值在c列上的值不同，就会导致每次按照a，b列排序结果取第一条时，所取的数据是随机的，造成结果集不一致。
**解决方法：**
该场景需要将取值列c列也加到排序中，使排序结果获取的第一条数据固定。
```
SELECT c,rn FROM (select c,row_number() over(order by a,b,c) as rn from t3) where rn = 1;
 c | rn 
---+----
 1 |  1
(1 row)
```
#### 子视图/子查询中使用排序
**场景**：
创建表test和视图v后，子查询中使用排序查询表test，出现查询结果不一致。
```
CREATE TABLE test(a serial ,b int);
INSERT INTO test(b) VALUES(1);
INSERT INTO test(b) SELECT b FROM test;
…
INSERT INTO test(b) SELECT b FROM test;
CREATE VIEW v as SELECT * FROM test ORDER BY a;
```
问题SQL：
```
SELECT * FROM v limit 1;
 a | b
---+---
 3 | 1
(1 row)
SELECT * FROM (select * from test order by a) limit 10;
 a  | b
----+---
 14 | 1
(1 row)
SELECT * FROM test order by a limit 10;
 a | b
---+---
 1 | 1
(1 row)
```
**原因分析**：
在子视图和子查询中，ORDER BY子句是无效的。
**解决方法：**
不建议在子视图和子查询中使用order by，若要保证结果有序，需在最外层查询中使用order by。
#### 子查询limit
**场景：**子查询中使用limit，两次查询结果不一致。
```
SELECT * FROM (select a from test limit 1 ) order by 1;
 a
---
 5
(1 row)
SELECT * FROM (select a from test limit 1 ) order by 1;
 a
---
 1
(1 row)
```
**原因分析：**
子查询中的limit会导致获取随机结果，从而最终查询结果为随机提取。
**解决方法：**
要保证最终查询结果的稳定，需避免在子查询中使用limit。
#### 使用string_agg
**场景** **：**使用string_agg查询表employee，出现查询结果不一致。
```
SELECT * FROM employee;
 empno | ename  |   job   | mgr  |      hiredate       |  sal  | comm | deptno 
-------+--------+---------+------+---------------------+-------+------+--------
  7654 | MARTIN | SALEMAN | 7698 | 2022-11-08 00:00:00 | 12000 | 1400 |     30
  7566 | JONES  | MANAGER | 7839 | 2022-11-08 00:00:00 | 32000 |    0 |     20
  7499 | ALLEN  | SALEMAN | 7698 | 2022-11-08 00:00:00 | 16000 |  300 |     30
(3 rows)
SELECT count(*) FROM (select deptno, string_agg(ename, ',') from employee group by deptno) t1, (select deptno, string_agg(ename, ',') from employee group by deptno) t2 where t1.string_agg = t2.string_agg;
 count 
-------
     2
(1 row)
SELECT count(*) FROM (select deptno, string_agg(ename, ',') from employee group by deptno) t1, (select deptno, string_agg(ename, ',') from employee group by deptno) t2 where t1.string_agg = t2.string_agg;
 count 
-------
     1
(1 row)
```
**原因分析：**
string_agg函数的作用是将组内的数据合并成一行，但是如果某用户的用法是string_agg(ename, ',') ，结果集就是不稳定的，因为没有指定组合的顺序。例如，上述语句中，对于*select deptno, string_agg(ename, ',') from employee group by deptno;*
输出结果既可以是：
```
30 | ALLEN,MARTIN
```
也可能是：
```
30 |MARTIN,ALLEN
```
两个结果都是合理的，因此上述关联场景下，有可能出现t1这个subquery中的结果和t2这个subquery中的结果对于deptno=30时的输出结果不一致。
**解决方法：**
string_agg中增加order by排序，保证按顺序拼接。
```
SELECT count(*) FROM (select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t1 ,(select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t2 where t1.string_agg = t2.string_agg;
```
#### 数据库兼容模式
**场景：**在数据库中查询空串结果不一致。
database1（TD兼容模式）:
```
td=# select '' is null;
 isnull 
--------
 f
(1 row)
```
database2（ORA兼容模式）:
```
ora=# select '' is null;
 isnull 
--------
 t
(1 row)
```
**原因分析：**
查询空串结果不同是由于不同数据库兼容模式下空串与null语法有差异导致。
目前，DWS支持三种数据库兼容模式：Oracle、TD和MySQL，不同兼容模式下语法和行为存在差异，兼容性差异说明可参考[Oracle、Teradata和MySQL语法兼容性差异](https://support.huaweicloud.com/devg-dws/dws_04_0042.html)。
不同兼容模式下的database表现出不同的兼容性行为属于正常现象。可以通过查看*select datname, datcompatibility from pg_database;*确认数据库兼容性设置是否相同。
**解决方法：**
这种场景下只能将两个database的兼容性模式设置为一致的才能解决。Database的DBCOMPATIBILITY属性不支持ALTER，只能通过新建数据库的方法，在创建数据库时指定相同的DBCOMPATIBILITY属性解决。
#### 数据库兼容性行为配置项behavior_compat_options设置不同
**场景：**add_months函数计算结果不一致。
database1:
```
SELECT add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-28 00:00:00
(1 row)
```
database2:
```
SELECT add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-31 00:00:00
(1 row)
```
**原因分析**：
数据库兼容性配置项behavior_compat_options不同会导致部分行为不同，该参数选项可参考[behavior_compat_options](https://support.huaweicloud.com/devg-dws/dws_04_0945.html#section17)中的相关选项描述。
此场景中behavior_compat_options参数的end_month_calculate选项控制add_months函数的计算逻辑。设置end_month_calculate配置项时，如果param1的日期（Day字段）为月末，并且param1的日期（Day字段）比result月份的月末日期小，计算结果中的日期字段（Day字段）和result的月末日期保持一致。
**解决方法**：
需要将数据库中参数behavior_compat_options的兼容性配置项设置为一致。该参数类型为USERSET类型，可session级别设置或集群级修改。
#### 自定义函数属性设置不合理
**场景：**自定义函数get_count()并调用该函数出现结果不一致场景。
```
CREATE FUNCTION get_count() returns int
SHIPPABLE
as $$
declare
    result int;
begin
    result = (select count(*) from test);  --test表是hash表
return result;
end;
$$
language plpgsql;
```
调用该函数。
```
SELECT get_count();
 get_count 
-----------
      2106
(1 row)
SELECT get_count() FROM t_src;
 get_count 
-----------
      1032
(1 row)
```
**原因分析：**
由于该函数指定了SHIPPABLE的函数属性，因此生成计划时该函数会下推到DN上执行，该函数下推到DN后，由于函数定义中的test表是hash表，因此每个DN上只有该表的一部分数据，所以select count(\*) from test; 返回的结果不是test表全量数据的结果，而是每个DN上部分数据的结果，因此导致加上from表后函数返回预期发生变化。
**解决方法**：
以下两种方法任选其一即可（推荐第一种方法）：
1. 将函数改为不下推：ALTER FUNCTION get_count() not shippable;
2. 将函数中用到的表改为复制表，这样每个DN上都是一份该表的全量数据，即使下推到DN执行，也能保证结果集符合预期。
 
#### 使用UNLOGGED表
**场景**：
使用unlogged表后，在集群重启后，关联查询结果集异常，查看unlogged表缺少部分数据。
**原因分析：**
如果设置max_query_retry_times为0，且在建表时指定UNLOGGED关键字，则创建的表为非日志表。在非日志表中写入的数据不会被写入到预写日志中，这样就会比普通表快很多。但是非日志表在冲突、执行操作系统重启、强制重启、切断电源操作或异常关机后会被自动截断，会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。因此当集群发生异常重启（进程重启、节点故障、集群重启）时，会导致部分内存中的数据未及时落盘，造成部分数据丢失，从而导致结果集异常。
**解决方法：**
unlogged表在集群异常情况下的安全性无法保证，一般不能作为业务表使用，更多的场景是作为临时表使用。当出现集群故障后，为了保证数据正常，需要重建unlogged表或将数据备份后重新导入数据库。