GaussDB(DWS)查询时结果不一致的常见场景和解决方法

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

场景：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

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列排序结果取第一条时，所取的数据是随机的，造成结果集不一致。

解决方法：

1
2
3
4
5

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
2
3
4
5
6

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：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

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，两次查询结果不一致。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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，出现查询结果不一致。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

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;

输出结果既可以是：

1

30 | ALLEN,MARTIN

也可能是：

1

30 |MARTIN,ALLEN

两个结果都是合理的，因此上述关联场景下，有可能出现t1这个subquery中的结果和t2这个subquery中的结果对于deptno=30时的输出结果不一致。

解决方法：

String_agg中增加order by排序，保证按顺序拼接。

1

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兼容模式）:

1
2
3
4
5

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

database2（ORA兼容模式）:

1
2
3
4
5

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

原因分析：

查询空串结果不同是由于不同数据库兼容模式下空串与null语法有差异导致。

目前，GaussDB(DWS)支持三种数据库兼容模式：Oracle、TD和MySql，不同兼容模式下语法和行为存在差异，兼容性差异说明可参考Oracle、Teradata和MySQL语法兼容性差异。

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

解决方法：

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

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

场景：add_months函数计算结果不一致。

database1:

1
2
3
4
5

SELECT add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-28 00:00:00
(1 row)

database2:

1
2
3
4
5

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中的相关选项描述。

此场景中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()并调用该函数出现结果不一致场景。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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

调用该函数。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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表或将数据备份后重新导入数据库。