logo标签列表

MYSQL JSON查询每次运行返回不同的结果

mysqlarraysjson
3
我对JSON数据运行了一个简单的选择查询，其中包含一些简单的计算，但在多次运行时返回不同的结果。我无法找出原因，也没有找到答案。从查询统计信息中可以看到构建了一个临时表，我想这个表在查询完成后未被截断/保存在某个地方，但我无法定位（更不用说截断/删除）该表。这只是一个工作假设 - 答案可能存在于设置或其他方面...有人能找到任何逻辑吗？我在本地实例上使用MySQL Workbench和MySQL DB。MySQL版本为8.0.19 MySQL Community Server-GPL。这是表格的简化版本：
CREATE TABLE regulation_entries (
    id INTEGER UNSIGNED AUTO_INCREMENT,
    employee_id VARCHAR(10) NOT NULL,
    regulation JSON NOT NULL,
    PRIMARY KEY (id)
    );

# Sample data to work with    

INSERT INTO regulation_entries VALUES
    (DEFAULT, 2, '{"entry_base_salary": "8000", "pension_pct": "0.08"}'),
    (DEFAULT, 3, '{"entry_base_salary": "10000", "pension_pct": "0.08"}'),
    (DEFAULT, 5, '{"entry_base_salary": "11000", "pension_pct": "0.08"}'),
    (DEFAULT, 8, '{"entry_base_salary": "11000", "pension_pct": "0.08"}'),
    (DEFAULT, 9, '{"entry_base_salary": "9000", "pension_pct": "0.08"}'),
    (DEFAULT, 1, '{"entry_base_salary": "14000", "pension_pct": "0.10"}'),
    (DEFAULT, 6, '{"entry_base_salary": "13000", "pension_pct": "0.08"}'),
    (DEFAULT, 7, '{"entry_base_salary": "14000", "pension_pct": "0.08"}'),
    (DEFAULT, 2, '{"base_salary_adjustment": "500"}'),
    (DEFAULT, 9, '{"base_salary_adjustment": "800"}'),
    (DEFAULT, 3, '{"base_salary_adjustment": "400"}'),
    (DEFAULT, 5, '{"base_salary_adjustment": "350"}'),
    (DEFAULT, 8, '{"base_salary_adjustment": "200"}'),
    (DEFAULT, 9, '{"base_salary_adjustment": "1250"}'),
    (DEFAULT, 1, '{"base_salary_adjustment": "-200"}'),
    (DEFAULT, 1, '{"base_salary_adjustment": "50"}'),
    (DEFAULT, 6, '{"base_salary_adjustment": "700"}'),
    (DEFAULT, 7, '{"base_salary_adjustment": "825"}');

# The query that is bugging me:

SELECT employee_id, 
        SUM(regulation->>'$.entry_base_salary') AS entry_base_salary,
        regulation->>'$.pension_pct' AS pension_pct,
        AVG(regulation->>'$.pension_pct') * SUM(regulation->>'$.entry_base_salary') AS entry_pension,
        SUM(regulation->>'$.base_salary_adjustment') AS salary_adjustments,
        SUM(regulation->>'$.entry_base_salary') + SUM(regulation->>'$.base_salary_adjustment') AS future_salary,
        AVG(regulation->>'$.pension_pct') * (SUM(regulation->>'$.entry_base_salary') + SUM(regulation->>'$.base_salary_adjustment')) AS future_pension 
    FROM sreg.regulation_entries 
    GROUP BY employee_id
    ORDER BY employee_id

当我运行查询时，我期望看到这个：
employee_id #entry_base_salary  #pension_pct    #entry_pension  #salary_adjustments #future_salary  #future_pension#
#1          #14000              #0.10           #1400           #1250               #15250          #1525
#2          #8000               #0.08           #640            #1100               #9100           #728
#3          #10000              #0.08           #800            #1000               #11000          #880
#5          #11000              #0.08           #880            #950                #11950          #956
#6          #13000              #0.08           #1040           #1900               #14900          #1192
#7          #14000              #0.08           #1120           #2025               #16025          #1282
#8          #11000              #0.08           #880            #800                #11800          #944
#9          #9000               #0.08           #720            #2650               #11650          #932

但是，如果我再次执行相同的查询，我会得到新的随机结果。例如：

employee_id #entry_base_salary  #pension_pct    #entry_pension  #salary_adjustments     #future_salary  #future_pension#

1           #14000              #0.10           #1400           #3050                   #17050          #1705
2           #8000               #0.08           #640            #2900                   #10900          #872
3           #10000              #0.08           #800            #2800                   #12800          #1024
5           #11000              #0.08           #880            #2750                   #13750          #1100
6           #13000              #0.08           #1040           #3700                   #16700          #1336
7           #14000              #0.08           #1120           #3825                   #17825          #1426
8           #11000              #0.08           #880            #2600                   #13600          #1088
9           #9000               #0.08           #720            #4450                   #13450          #1076

Another example

1           #14000              #0.10           #1400           #2.119191149652875e88   #15250          #1525
2           #8000               #0.08           #640            #2.119191149652875e88   #9100           #728
3           #10000              #0.08           #800            #2.119191149652875e88   #11000          #880
5           #11000              #0.08           #880            #2.119191149652875e88   #11950          #956
6           #13000              #0.08           #1040           #2.119191149652875e88   #14900          #1192
7           #14000              #0.08           #1120           #2.119191149652875e88   #16025          #1282
8           #11000              #0.08           #880            #2.119191149652875e88   #11800          #944
9           #9000               #0.08           #720            #2.119191149652875e88   #11650          #932

And a third

1           #14000              #0.10           #1400           #3650                   #17650          #1765
2           #8000               #0.08           #640            #3500                   #11500          #920
3           #10000              #0.08           #800            #3400                   #13400          #1072
5           #11000              #0.08           #880            #3350                   #14350          #1148
6           #13000              #0.08           #1040           #4300                   #17300          #1384
7           #14000              #0.08           #1120           #4425                   #18425          #1474
8           #11000              #0.08           #880            #3200                   #14200          #1136
9           #9000               #0.08           #720            #5050                   #14050          #1124

有人之前见过这个吗？有解释吗？或者更好的是，有人知道我需要改变什么才能获得一致的结果吗？
我只检查了员工1，但是你期望的值对我来说似乎不正确！！ - RiggsFolly
不一致性仅与“base_salary_adjustment”有关，请确保对具有该字段的相同行运行查询。 - mangusta
如果有必要的话，请问您正在运行哪个版本的MySQL？ - RiggsFolly
@mangusta 当然，但是当我按employee_id分组时，我不会确保吗？我的意思是，显然它没有，但这应该足够了吧？ - Jones
@mangusta 是的。我没有对查询、表格、公式、设置等做任何更改，也没有其他人访问数据库。我只是再次按下执行按钮。 - Jones
显示剩余7条评论
1个回答
2
这并不是关于JSON的问题。这是MySQL 8.0.18中的一个错误，在8.0.20中得到了修复。该错误可能出现在非JSON列中。https://bugs.mysql.com/bug.php?id=97920 聚合函数[sum()]返回随机数。实际上，这些数字并不完全是随机的，而是每次运行查询时都会增加。就像SUM()在临时表中累积总和，并且相同的临时表用于后续运行的查询，而不将总和清零。以下是上述错误中给出的测试用例的结果：
mysql> SELECT ym, ROUND(SUM(vb)), ROUND(SUM(vc)) FROM t_test GROUP BY ym;
+--------+----------------+----------------+
| ym     | ROUND(SUM(vb)) | ROUND(SUM(vc)) |
+--------+----------------+----------------+
| 201912 |          22675 |            227 |
+--------+----------------+----------------+

mysql> SELECT ym, ROUND(SUM(vb)), ROUND(SUM(vc)) FROM t_test GROUP BY ym;
+--------+----------------+----------------+
| ym     | ROUND(SUM(vb)) | ROUND(SUM(vc)) |
+--------+----------------+----------------+
| 201912 |          22675 |            454 |
+--------+----------------+----------------+

mysql> SELECT ym, ROUND(SUM(vb)), ROUND(SUM(vc)) FROM t_test GROUP BY ym;
+--------+----------------+----------------+
| ym     | ROUND(SUM(vb)) | ROUND(SUM(vc)) |
+--------+----------------+----------------+
| 201912 |          22675 |            682 |
+--------+----------------+----------------+

据我的测试，每次运行查询时，它都会增加227。 227是正确的结果，并且在我第一次运行查询时返回。

此外，如果要求和的基础数据不包括任何NULL，则不会出现问题。

mysql> SELECT ym, ROUND(SUM(vb)), ROUND(SUM(vc)) FROM t_test where vc is not null GROUP BY ym;
+--------+----------------+----------------+
| ym     | ROUND(SUM(vb)) | ROUND(SUM(vc)) |
+--------+----------------+----------------+
| 201912 |            252 |            227 |
+--------+----------------+----------------+

每次运行此查询都会返回正确的227值。
您的查询也是同样的情况。因为您的表达式regulation->>'$.base_salary_adjustment'在JSON不包含该键的行上返回NULL，所以使用SUM()时会出现相同的错误。
如果我修改查询将NULL转换为0，则可以得到正确的结果，并且无论运行查询多少次都不会改变。
SUM(COALESCE(regulation->>'$.base_salary_adjustment', 0)) AS salary_adjustments

每次引用该JSON键（或任何时候使用可能为NULL的表达式上的SUM（））都必须执行此操作。

我建议您升级到MySQL 8.0.20。

那么 entry_base_salary 呢？有一些行没有设置 entry_base_salary 字段，但在查询结果中仍然得到了正确的总和。顺序重要吗？ - mangusta
显然这很重要。这是一个错误，因此所有逻辑行为都是无效的。 - Bill Karwin
@BillKarwin 谢谢！我知道这里有天才能找到答案... 我感到有些安慰的是这是一个错误，所以我可以取消我的心理医生预约 ;) - Jones
LOL - MySQL一直在玩弄你！ - Bill Karwin
我告诉了一位同事关于这个 bug，他提醒我 "the rule of .20"，这是由 MySQL 的前工程经理 Stewart Smith 创造的：https://www.flamingspork.com/blog/2013/08/01/stewarts-dot-twenty-rule/ - Bill Karwin
1
@BillKarwin 我花了一些时间，因为系统表更新一直失败，但我采纳了您的建议并升级到了MySQL 8.0.20，结果非常准确。现在计算工作非常顺畅。再次感谢您的帮助！ - Jones
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接