本文主要介绍几种在用户使用中场景的结果集不稳定的问题，此类问题大部分都是由于用户使用不当或用法不合理导致，通过合理的业务使用可以避免此类问题。本文介绍几种常见的场景和解决办法供使用者参考。

1. 窗口函数中使用排序后取部分结果

1.1 典型场景

postgres=# select * from t3 order by 1,2,3;
 a | b | c 
---+---+---
 1 | 2 | 1
 1 | 2 | 2
 1 | 2 | 3
(3 rows)

postgres=# 
postgres=# 
postgres=# 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)

postgres=# 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)

1.2 问题原因

    如上所示，执行同一条语句：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列排序结果取第一条时，取的数据是随机的，造成结果集不稳定。

1.3 解决方法

    这种场景需要将取值列c列上也加到排序中，使排序结果取到的第一条数据固定。

postgres=# 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)

postgres=# 
postgres=# 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)

1.4 扩展场景：

    上述场景中，diag_code列不在row_number()的排序列中，当排序列有重复值时，而重复值里diag_code的值不一样，就会造成随机取一条，diag_code用于join，就会导致结果集不稳定。

2. 子视图/子查询中使用排序

2.1 典型场景

create table test(a serial ,b int);
insert into test(b) values(1);
insert into test(b) values select b from test;
…
insert into test(b) values 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)

2.2 问题原因

    对于视图和子查询中order by是无效的，要想保证结果的有序，必须在最外层的查询中加oder by。

2.3 解决方法

    不要在视图和子查询中使用order by，如果需要保证结果有序，需要在最外层查询使用order by。

3. 子查询limit

3.1 典型场景

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)

3.2 问题原因

    子查询中的limit会导致结果的随机，从而导致最终结果的随机。

3.3 解决方法

    要保证最终结果集的稳定，避免在子查询中使用limit。

4. 使用string_agg

4.1 典型场景

postgres=# 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)

postgres=# 
postgres=# select count(*) from
postgres-# (select deptno, string_agg(ename, ',') from employee group by deptno) t1 ,
postgres-# (select deptno, string_agg(ename, ',') from employee group by deptno) t2
postgres-# where t1.string_agg = t2.string_agg;
 count 
-------
     2
(1 row)

postgres=# 
postgres=# select count(*) from
postgres-# (select deptno, string_agg(ename, ',') from employee group by deptno) t1 ,
postgres-# (select deptno, string_agg(ename, ',') from employee group by deptno) t2
postgres-# where t1.string_agg = t2.string_agg;
 count 
-------
     1
(1 row)

postgres=# 

4.2 问题原因

    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的时候的输出结果是不一样的。

4.3 解决方法

    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;

5. 数据库兼容模式

5.1 典型场景

    database1（TD兼容模式）:

td=# select '' is null;
 isnull 
--------
 f
(1 row)

    database2（ORA兼容模式）:

postgres=# select '' is null;
 isnull 
--------
 t
(1 row)

5.2 问题原因

    本例中，是由于不同数据库兼容模式下空串与null的比较不同导致的。

    目前，DWS支持三种数据库兼容模式：Oracle、TD、MySql，不同兼容模式下语法和行为存在差异，兼容性差异说明可参考：https://support.huaweicloud.com/devg-813-dws/dws_04_0042.html，

    用户常见的TD和Oracle兼容性行为差异还可以参考：https://bbs.huaweicloud.com/blogs/176361。

    不同兼容模式下的database表现出不同的兼容性行为属于正常现象。可以通过查看select datname, datcompatibility from pg_database;确认数据库兼容性设置是否相同。

5.3 解决方法

    这种场景下只能将两个环境的database的兼容性模式设置为一致的才能解决。Database的datcompatibility属性不支持alter，只能通过新建数据库的方法，在创建数据库时指定相同的DBCOMPATIBILITY属性解决。

5.4 扩展场景

    oracle兼容模式下，空串''等价为null，而null除了is判断外，用其他比较操作的返回结果均为null，因此会有以下行为：

postgres=# select * from t1 limit 1;
 a | b | c 
---+---+---
 3 | 1 | 1
(1 row)

postgres=# select * from t1 where b = '' limit 1;
 a | b | c 
---+---+---
(0 rows)

postgres=# select * from t1 where b != '' limit 1;
 a | b | c 
---+---+---
(0 rows)

6. 数据库兼容性行为配置项behavior_compat_options设置不同

6.1 典型场景

    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)

6.2 问题原因

    数据库兼容性行为配置项behavior_compat_options不同也会导致部分行为不同，该参数选项可参考产品文档：https://support.huaweicloud.com/devg-813-dws/dws_04_0945.html中的相关选项描述。

6.3 解决方法

    需要将该参数设置为一致解决。该参数类型为USERSET类型，可session级别设置或集群级修改。

7. 自定义函数属性设置不合理

7.1 典型场景

create function get_count() returns int
SHIPPABLE
as $$
declare
    result int;
begin
    result = (select count(*) from test);  --test表是hash表
	return result;
end;
$$
language plpgsql;

    调用该函数发现以下现象：

postgres=# select get_count();
 get_count 
-----------
      2106
(1 row)

postgres=# select get_count() from t_src;
 get_count 
-----------
      1032
(1 row)

7.2 问题原因

    这是因为由于这个函数指定了SHIPPABLE的函数属性，因此生成计划时该函数会下推到DN上执行，该函数下推到DN后，由于函数定义中的test表是hash表，因此每个DN上只有该表的一部分数据，所以select count(*) from test返回的结果不是test表全量数据的结果，而是每个DN上部分数据的结果，因此导致加上from表后函数返回预期发生变化。参考：https://bbs.huaweicloud.com/blogs/250351

7.3 解决方法

    以下两种方法任选其一即可（推荐第一种方法）：

    （1）将函数改为不下推：alter function get_count() not shippable; 

    （2）将函数中用到的表改为复制表，这样每个DN上都是一份该表的全量数据，即使下推到DN执行，也能保证结果集符合预期。

8. 使用unlog表

8.1 典型场景

    用户使用unlog表后，在集群重启后，关联查询结果集异常，查看unlog表缺少部分数据。

8.2 问题原因

    如果设置max_query_retry_times为0，且在建表时指定UNLOGGED关键字，则创建的表为非日志表。在非日志表中写入的数据不会被写入到预写日志中，这样就会比普通表快很多。但是非日志表在冲突、执行操作系统重启、强制重启、切断电源操作或异常关机后会被自动截断，会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。因此当集群发生异常重启（进程重启、节点故障、集群重启）时，会导致部分内存中的数据未及时落盘，造成部分数据丢失，从而导致结果集异常。

8.3 解决方法

    unlog表在集群异常情况下的安全性无法保证，一般不能作为业务表使用，更多的场景是作为临时表使用。当出现集群故障后，为了保证数据正常，需要重建unlog表或将数据重新入库。

9. 8.2.0版本及以后，窗口函数结果集无序

9.1 典型场景

  业务使用row_number作为窗口函数，升级到8.2.0以后，结果集无序。如下图所示，两次查询得到的结果集顺序不一样：

postgres=# select * from (select a,b, row_number() over (order by b) from t) row_num;
 a  | b | row_number 
----+---+------------
  2 | 8 |          8
  2 | 8 |          9
  6 | 8 |         10
 36 | 8 |         11
  8 | 4 |          1
 38 | 4 |          2
  1 | 4 |          3
  1 | 4 |          4
  2 | 5 |          5
  9 | 5 |          6
 39 | 5 |          7
(11 rows)

postgres=# select * from (select a,b, row_number() over (order by b) from t) row_num;
 a  | b | row_number 
----+---+------------
 39 | 5 |          5
  2 | 5 |          6
  9 | 5 |          7
  8 | 4 |          1
 38 | 4 |          2
  1 | 4 |          3
  1 | 4 |          4
  6 | 8 |          8
  2 | 8 |          9
  2 | 8 |         10
 36 | 8 |         11
(11 rows)

9.2 问题原因

  这是因为在8.2.0版本在窗口函数（windowagg）支持基于value partition的执行计划，以提高窗口函数的性能。而此行为导致的一个影响就是输出结果集变成无序的，与8.1.3等版本行为发生变化。

9.3 解决方法

  需要在最外层手动添加order by解决，或者修改enable_value_partitioin为off关闭value partition的重分布计划。