IT mysqltest failed with avg result precision #30402
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
may introduced by ##30010
in ci https://ci.pingcap.net/blue/organizations/jenkins/tidb_ghpr_integration_common_test/detail/tidb_ghpr_integration_common_test/8134/pipeline
[2021-12-03T03:18:47.266Z] time="2021-12-03T11:18:46+08:00" level=error msg="run test [window_functions] err: sql:SELECT t3_id, AVG(t3_id) OVER w,\nCUME_DIST() OVER w,\nk FROM t3\nWINDOW w AS (PARTITION BY k ORDER BY t3_id\nRANGE BETWEEN 4 PRECEDING AND 2 PRECEDING);: failed to run query \n\"SELECT t3_id, AVG(t3_id) OVER w,\nCUME_DIST() OVER w,\nk FROM t3\nWINDOW w AS (PARTITION BY k ORDER BY t3_id\nRANGE BETWEEN 4 PRECEDING AND 2 PRECEDING);\" \n around line 386, \nwe need(543):\nSELECT t3_id, AVG(t3_id) OVER w,\nCUME_DIST() OVER w,\nk FROM t3\nWINDOW w AS (PARTITION BY k ORDER BY t3_id\nRANGE BETWEEN 4 PRECEDING AND 2 PRECEDING);\nt3_id\tAVG(t3_id) OVER w\tCUME_DIST() OVER w\tk\n0\tNULL\t0.2\t0\n0\tNULL\t0.2\t0\n2\t0.0000\t0.4\t0\n2\t0.0000\t0.4\t0\n4\t1.0000\t0.6\t0\n4\t1.0000\t0.6\t0\n6\t3.0000\t0.8\t0\n6\t3.0000\t0.8\t0\n8\t5.0000\t1\t0\n8\t5.0000\t1\t0\n1\tNULL\t0.2\t1\n1\tNULL\t0.2\t1\n3\t1.0000\t0.4\t1\n3\t1.0000\t0.4\t1\n5\t2.0000\t0.6\t1\n5\t2.0000\t0.6\t1\n7\t4.0000\t0.8\t1\n7\t4.0000\t0.8\t1\n9\t6.0000\t1\t1\n9\t6.0000\t1\t1\nReuse of already evaluated peer of current row due to dynamic_ag\nbut got(543):\nSELECT t3_id, AVG(t3_id) OVER w,\nCUME_DIST() OVER w,\nk FROM t3\nWINDOW w AS (PARTITION BY k ORDER BY t3_id\nRANGE BETWEEN 4 PRECEDING AND 2 PRECEDING);\nt3_id\tAVG(t3_id) OVER w\tCUME_DIST() OVER w\tk\n0\tNULL\t0.2\t0\n0\tNULL\t0.2\t0\n2\t0.00000000\t0.4\t0\n2\t0.00000000\t0.4\t0\n4\t1.00000000\t0.6\t0\n4\t1.00000000\t0.6\t0\n6\t3.00000000\t0.8\t0\n6\t3.00000000\t0.8\t0\n8\t5.00000000\t1\t0\n8\t5.00000000\t1\t0\n1\tNULL\t0.2\t1\n1\tNULL\t0.2\t1\n3\t1.00000000\t0.4\t1\n3\t1.00000000\t0.4\t1\n5\t2.00000000\t0.6\t1\n5\t2.00000000\t0.6\t1\n7\t4.00000000\t0.8\t1\n7\t4.00000000\t0.8\t1\n9\t6.00000000\t1\t1\n9\t6.00000000\t1\t1\n\n"
[2021-12-03T03:18:47.266Z] time="2021-12-03T11:18:46+08:00" level=error msg="run test [window_min_max] err: sql:SELECT AVG(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max FROM t1\nWINDOW w AS (PARTITION BY sex);: failed to run query \n\"SELECT AVG(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max FROM t1\nWINDOW w AS (PARTITION BY sex);\" \n around line 131, \nwe need(200):\nSELECT AVG(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max FROM t1\nWINDOW w AS (PARTITION BY sex);\nAVG(id) OVER w\tmin\tmax\n3.0000\t2\t4\n3.0000\t2\t4\n3.0000\t2\t4\n3.0000\t1\t5\n3.0000\t1\t5\nSELECT * FROM (\nSELE\nbut got(200):\nSELECT AVG(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max FROM t1\nWINDOW w AS (PARTITION BY sex);\nAVG(id) OVER w\tmin\tmax\n3.00000000\t2\t4\n3.00000000\t2\t4\n3.00000000\t2\t4\n3.00000000\t1\t5\n3.00000000\t1\t5\n\n"