sql 查询结果的验证总结

思路分析

• 解析sql语义，通过编程语言得出正确结果，再与sql执行结果对比（工作量大直接放弃）

 

• 通过原生spark sql执行得出结果做对比，（可行，问题点就在于如何对比两个结果）

对比sql结果是否正确分为两点：1.两者执行sql结果行数一致 且sne的sql结果的每行是否存在于spark sql执行结果中，（小数的验证）

 

2.sne sql结果排序是否正确（没有排序的sql无需校验）

 

需解决问题

sql结果的每行对比（小数）

 

对于浮点类型的字段，我们是允许有精度损失的，误差diff的理论验证方案应该是

 

vs == vv OR abs(vs - vv) / max(vs, vv) < delta

 

这里vs为SNE执行结果，vv为spark 执行结果，delta可以先设置为万分之一。

 

decimal的数据类型要求数值绝对相等

 

 

sne sql结果排序是否正确

 

示例sql

 

简化sql：

 

select a, b, c from t order by c, a desc limit 3

 

	spark结果			SNE结果
行号	a	b	c	a	b	c
1	XX	4	1	XX	4	1
2	B	5	2	B	7	2
3	B	6	2	B	5	2
4	B	7	2
5	A	11	3.6

 

spark结果与SNE结果 2，3行字符不相等但是sne结果也是正确符合预期的

 

 

对比方法考虑如下两种情况

 

• 排序key无小数

	spark结果			SNE结果
行号	a	b	c	a	b	c
1	XX	4	1	XX	4	1
2	B	5	2	B	7	2
3	B	6	2	B	5	2
4	B	7	2
5	A	11	3

 

验证方法：spark执行sql去掉limit，当两行字符不相等时

 

• • • 首先验证两边排序字段是否相等，对上例就是判断a和c字段是否相等，如果不相等，直接判定SNE结果不正确。
    • 从spark结果未进行过对比的行开始找到所有排序字段和此行排序字段相等并且没有参与过对比的行（暂时还没有和sne结果吻合的行）的集合，记为sortSet。
    • 从sortSet中找一行和当前sne结果相等的行，如果找不到，直接判定SNE不正确。如果找到，标记此行已经做过对比。

 

备注：不去掉limit可能存在sne某行结果不在spark的limit范围中

 

 

• 排序key 有小数

select a, b, c from t order by c, a desc limit 3

 

	spark结果			SNE结果
行号	a	b	c	a	b	c
1	XX	4	1.2	XX	4	1.2
2	D	5	2.4	B	7	2.3999999
3	C	6	2.4	D	5	2.4
4	B	7	2.4
5	A	11	3.6

 

验证方法：spark执行sql去掉limit，当两行字符不相等时

 

• • • 首先验证两边主排序字段是否相等，对上例就是判断c字段是否相等，如果不相等，直接判定SNE结果不正确。
    • 从spark结果未进行过对比的行开始找到所有排序字段和此行排序字段相等并且没有参与过对比的行的集合，记为sortSet。
    • 从sortSet中找一行和ls相等的行，如果找不到，直接判定SNE不正确。如果找到，标记此行已经做过对比。
    • 进一步验证SNE结果集的排序正确性。具体来说就是n行和n+1行的数据是满足排序关系的(正序/倒序)。如果不满足排序关系则直接判定SNE不正确。

 

备注：1.只对比主排序因为可能因为小数的误差导致排序不匹配，例如上图结果的第二行

 

2.主排序字段小数有误差也应在精度损失范围中，理论上主排序字段对比一定通过

 

3.不验证结果排序正确性的话理论上可能会出现主排序字段外的字段排序不对的情况

 

 

最终验证方案：

通过对比SNE和SPARK 执行结果来验证SNE执行结果的正确性，具体对比任务分解如下

 

1.短路验证

直接对比未修改SQL的SNE和SPARK执行结果，可以在部分情况下快速得出结论。

 

• 检查返回行数，如果返回行数不相等，那SNE结果一定不正确。
• 检查每一行的所有字段，如果两者的值都相等，那么可以认为SNE执行结果一定是正确的。

下述复杂验证机制中没有进行返回结果行数的验证，所以该短路验证的返回行数验证是无法省略的。

 

 

2.字段一致性

首先对于浮点类型的字段，我们是允许有精度损失的，误差diff的理论验证方案应该是

 

vs == vv OR abs(vs - vv) / max(vs - vv) < delta

 

这里vs为SNE执行结果，vv为spark 执行结果，delta可以先设置为万分之一。

 

下述提到字段值相等时，对于浮点型字段指的就是误差在预期范围内。

 

对于其他类型的字段，直接判断相等即可。

 

 

3.对应的行字符 不一致时

 

有排序算子SQL验证

为描述简单，order by包括的字段称为排序集，select包括的字段称为选择集，可能包括几种情况

 

排序集等于选择集，比如：select a, b from t order by b desc, a

 

排序集 ⊆选择集，比如：select a, b from t order by a

 

选择集 ⊆ 排序集，比如：select a from t order by a, b

 

排序集和选择集互不包含，比如：select a, c from t order by a, b

 

 

有limit并且排序集 ⊆ 选择集

为了简化分析，以如下SQL为例

 

select a, b, c from t order by c, a desc limit 3

 

下面是一个假想的执行结果

 

	spark结果（去掉limit）			SNE结果
行号	a	b	c	a	b	c
1	XX	4	1.2	XX	4	1.2
2	D	5	2.4	B	7	2.3999999
3	C	6	2.4	D	5	2.4
4	B	7	2.4
5	A	11	3.6

 

可以看到，这个SNE执行结果在我们允许的误差范围内是正确的，但是如果SNE和Vanilla的结果都只看前3行的话，是无法判断SNE结果是否正确的。所以这种SQL的验证，Vanilla对应的SQL要把limit去掉。

 

最终可以将这种类型的SQL的验证算法总结如下：

 

• 核心思想为从上到下对比每一行执行结果的正确性，记lv和ls分别为Vanilla和SNE当前行的行号，slv为Vanilla结果中使用过的行号集合。下述ls前进一行的意思是ls加1，lv前进一行的意思是：do { lv += 1; } while(lv in slv)。
• 如果lv行和ls行的每个字段都相等，那么lv和ls都前进一行。
• 如果存在不相等字段，否则分为两种情况进行处理
  • 排序字段中包括浮点型。
    • 那么首先验证两边主排序字段是否相等，对上例就是判断c(lv)和c(ls)是否相等。如果不相等，直接判定SNE结果不正确。
    • 从lv行开始找到所有主排序字段和ls行主排序字段相等并且不在slv中的行的集合，记为sortSet。
    • 首先从sortSet中找一行和ls相等的行，如果找不到，直接判定SNE不正确。如果找到，记为flv，并将flv加入slv。
    • 进一步验证SNE结果集的排序正确性。具体来说就是ls行和ls+1行的数据是满足排序关系的。如果不满足排序关系则直接判定SNE不正确。
  • 排序字段中不包括浮点型。
    • 那么首先验证两边排序字段是否相等，对上例就是判断c(lv)和c(ls)是否相等，a(lv)和c(ls)是否相等。如果不相等，直接判定SNE结果不正确。
    • 从lv行开始找到所有排序字段和ls行排序字段相等并且不在slv中的行的集合，记为sortSet。
    • 从sortSet中找一行和ls相等的行，如果找不到，直接判定SNE不正确。如果找到，记为flv，并将flv加入slv。
  • 如果通过上述验证，则ls前进一行。
• 继续上述判定直到ls遍历完所有SNE结果

有limit并且选择集等于排序集

这种情况可以认为是上述case的特殊情况，所以按照上述case验证即可。

 

有limit并且选择集 ⊆ 排序集

select a, b from t order by c, a desc，b limit 3

 

	spark结果		SNE结果
行号	a	b	a	b
1	XX	4	XX	4
2	D	5	B	7
3	C	6	D	5
4	B	7
5	A	11

 

这种情况下需要改下SNE版本的SQL，不然无法判断结果是否正确。以上述SQL为例，如果select中不包括b、c，那是无法判断SNE结果是否正确的。

 

所以这种情况需要修改两个版本的SQL增加select字段，使得选择集等于排序集，这样就和上述case一致了。

 

有limit并且排序集和选择集互不包含

这种情况也需要修改两个版本的SQL增加select字段，使得排序集 ⊆ 选择集，这样也和前述case一致了。

 

 

没有limit

上述方案是针对有limit的情况设计的，不过也同样适用于没有limit的情况。