带有十进制值之和的查询以意外值返回

Question

我正在开发一个应用程序，它使用内存中的SQLite数据库，异常情况如下：

已使用的查询：

SELECT FE1 
FROM DRC 
WHERE DTS >= '2021-04-21T17:04:44+02:00' 
AND DTS < '2021-04-21T17:20:09+02:00' 
AND (DTS IN (SELECT DTS FROM NYN WHERE CNC = '0')
  OR DTS IN (SELECT DTS FROM ESN WHERE CNC = '0')
  OR DTS IN (SELECT DTS FROM SZN WHERE CNC = '0')
  OR DTS IN (SELECT DTS FROM VBN WHERE CNC = '0') 
)

它返回时：

999999,99
999999,99
999999,99
999999,99
999999,99
999999,99
999999,99
999999,99
999999,99
999999,99
0,09
-999999,99
-999999,99
-999999,99
-999999,99
-999999,99
-999999,99
-999999,99
-999999,99
-999999,99
-999999,99
-0,09

FE3的类型是文本，因此所有的计算都应该转换为十进制(加上十进制分隔符从逗号到点的更正)。

这些值的预期和必须在转换后为0。

但是如果我运行这个查询：

SELECT SUM(CAST(REPLACE(FE1,',','.') AS DECIMAL))
FROM DRC
WHERE DTS >= '2021-04-21T17:04:44+02:00' 
AND DTS < '2021-04-21T17:20:09+02:00' 
AND (DTS IN (SELECT DTS FROM NYN WHERE CNC = '0')
  OR DTS IN (SELECT DTS FROM ESN WHERE CNC = '0')
  OR DTS IN (SELECT DTS FROM SZN WHERE CNC = '0')
  OR DTS IN (SELECT DTS FROM VBN WHERE CNC = '0')
)

结果是-1.31316482687716e-09，即使它应该是

如果在Server中运行相同的查询，则会得到正确的0值。

有什么问题吗？

Answer 1

转换FE1值的数据类型不是问题。

SQLite可以执行对浮点数的隐式转换，即使您没有显式地执行：

SELECT SUM(REPLACE(FE1,',','.')) result
FROM DRC
.......................................

也将返回-1.3131648268772E-9。

问题在于SQLite对浮点数算术运算的精确性，正如您发现的那样，这还不够好。

一种解决方法是将所有值乘以100，如果您确信所有值在聚合之前最多都有两个小数位，然后再用100除以最终结果。

SELECT SUM(REPLACE(FE1,',','.') * 100) / 100 result
FROM DRC
...................................................

将返回0。

见演示。