不得不说的GaussDB(DWS）查询结果“错误”的陷阱 【这次高斯不是数学家】

        在做业务SQL开发过程中，会发现有些SQL的查询结果“错误”，不符合预期。今天就带大家了解一些看起来查询结果错误的场景，我们通过几个案例分析是SQL语句的那些地方使用不当，导致的查询结果不符合预期。

        一、聚合函数没有加order by

        我们通过一个例子来说明。假如有一张表的数据如下：

CREATE TABLE employee(ename TEXT, deptno INT);
INSERT INTO employee VALUES('ALLEN',30),('JONES',20),('MARTIN',30);
SELECT * FROM employee;
 ename  | deptno
--------+--------
 ALLEN  |     30
 MARTIN |     30
 JONES  |     20
(3 rows)

执行以下SQL:

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;

重复执行这个查询多次，会发现这个SQL的结果有时候是"1" ,有时候是“2”。

这是怎会回事呢？我们来分析一下。

string_agg函数的作用是将分组内的数据合并成一行。如上查询，string_agg(ename,','）就是将属于同一deptno分组的ename拼接起来。但是不限定拼接顺序，即对于以下SQL的执行结果：

SELECT deptno, string_agg(ename, ',') FROM employee GROUP BY deptno ORDER BY 1;

以下结果是正确的：

 deptno |  string_agg
--------+--------------
     20 | JONES
     30 | MARTIN,ALLEN
(2 rows)

以下结果也是正确的：

 deptno |  string_agg
--------+--------------
     20 | JONES
     30 | ALLEN,MARTIN
(2 rows)

那么上述SQL在做两个t1和t2表的关联的时候，就会因为结果的不稳定而输出"1"或者“2”。

为了避免这种情况，我们可以再string_agg中加入order by，避免排序不稳定导致的结果不稳定：

SELECT count(*) 
FROM 
(SELECT deptno, string_agg(ename,',' ORDER BY ename) FROM employee GROUP BY deptno)t1,
(SELECT deptno, string_agg(ename,',' ORDER BY ename) FROM employee GROUP BY deptno)t2 
WHERE t1.string_agg = t2.string_agg;

二、空字符串和NULL

现在有两套GaussDB(DWS)环境，发现完全相同的数据，完全相同的SQL语句的情况下，两条语句执行出来的查询结果居然不一样！

我们来看看具体的场景（我们对场景组了简化）：

CREATE TABLE test (a TEXT, b INT);
INSERT INTO test VALUES('', 1);
INSERT INTO test VALUES(null, 1);

执行以下查询语句：

SELECT count(*) FROM test a, test b WHERE a.a = b.a;

在集群A的查询结果是：

 count
-------
     0
(1 row)

在集群B的查询结果是：

 count
-------
     1
(1 row)

看起来非常不可思议！

这是为什么呢？这是因为GaussDB(DWS）支持多种兼容模式。当前最新版本支持TD/ORA/MYSQL三种兼容模式。

在Oracle兼容模式下，空字符串和NULL都当做NULL来对待。在TD兼容模式下，空字符串和NULL是不同的值。在Oracle兼容模式下，NULL做“=”的判断的时候全部返回FALSE，因此count结果是0。在TD兼容模式下，空字符串可以空字符串可以用"="匹配成功。

所以在TD兼容模式下，返回1，在Oracle兼容模式下，返回0.

数据库的兼容模式可以在pg_database系统表中通过datcompatibility查询得到。并且不支持修改。

类似的场景还有很多由于兼容模式不一样导致的查询结果不一致的场景，所以在做结果验证的时候，务必保证两个环境的兼容模式是一致的。

三、limit和offset的场景

有一张非常大的表，想把其中的数据导出。但是每次导出的数据量不能太大，因为数量太大，应用端处理不了。所以写了以下语句：

SELECT * FROM bigtable LIMIT 10000 OFFSET 10000;

然后每次调整OFFSET的值，来达到分批获取整张表的数据的目的。

但是这么做可能达不到预期效果。

GaussDB(DWS)是分布式架构，在这种情况下，CN节点返回的最终结果是从各个DN上获得的，各个DN上的数据达到顺序是没有办法保证的，在没有加Order By的情况下，也就没有办法保证数据的顺序是确定的。因此上述语句，没有办法达到获取整张表数据的目的。

如果要保证数据是有序的，需要加order by。但是在数据量比较大的场景下，order by会非常耗时。

因此，要通过其他手段来达到目的。比如，一般来说大表上会有分区，那么可以通过每次抽取部分分区的方法，达到每次抽取部分数据的效果；

或者表的某一字段的取值是比较均匀的分布，那么就根据这个字段的值的范围，每次取部分数据，最终达到抽取整张表的数据的效果。

四、UNION的场景

这种场景的逻辑可以简化为以下的逻辑：

(SELECT '1', * FROM t4 ORDER BY a LIMIT 10)
UNION ALL
(SELECT '2', * FROM t4 ORDER BY a LIMIT 10);

预期的结果集先输出所有第一个字段是"1"的结果，然后输出结果是"2"的结果集。但是实际效果结果集却是随机的。

这也是因为在分布式架构下，Union的两个分支是并行的，每个DN计算完结果以后，他们到达CN的时间也是不确定的，因此表现出来就是结果是随机的。

正确的写法应该是：

SELECT * FROM (
(SELECT '1', * FROM t4 ORDER BY a LIMIT 10)
UNION ALL
(SELECT '2', * FROM t4 ORDER BY a LIMIT 10)) ORDER BY 1;

五、视图和子查询中的Order BY

这种场景可以用一下的方法简单来表述：

CREATE TABLE t4(a serial ,b int);
INSERT INTO t4(b) SELECT generate_series(1,100);

首先通过子查询的方法查询表的结果：

SELECT * FROM (SELECT * FROM t4 ORDER BY a);

在子查询中加了Oder By期望最终结果是有序的。但是实际效果却是无序的。

这是因为，对于子查询来说，Order By无法影响到父查询的结果，所以结果是乱序的。

CREATE VIEW v as SELECT * FROM t4 ORDER BY a;

定义了一个视图，视图的定义里面加了Order By，期望查询视图的时候，结果是有序的。

但是实际查询视图的结果的时候发现结果却是乱序的：

SELECT * FROM v;

这是因为视图在这个查询中相当于一个子查询了，在子查询内部的Oder By，对于子查询外部都不生效，因此结果是随机的。

综上所述，子查询内部和视图中的Order BY都无法保证最终的数据是有序的。