[Bug]:select聚合函数avg错误；并且select用例执行时间异常-oGRAC-AtomGit

已关闭

[Bug]:select聚合函数avg错误；并且select用例执行时间异常 #348

hcs创建于 9月4日关闭于 19 天前

hcs成员

9月4日创建

测试类型

SQL功能

测试版本

7.0.0LTS

问题描述

操作系统和硬件信息

任意

测试环境

oGRAC单节点

被测功能

预置条件

操作步骤

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id FOR UPDATE;
 
DROP TABLE IF EXISTS je_win_avg_left;
DROP TABLE IF EXISTS je_win_avg_right;
 
CREATE TABLE je_win_avg_left (k INT);
CREATE TABLE je_win_avg_right (k INT);
 
INSERT INTO je_win_avg_left
SELECT CASE WHEN a.n <= 50 THEN 1 ELSE (a.n - 51) * 200 + b.n END
FROM (SELECT LEVEL n FROM sys_dummy CONNECT BY LEVEL <= 100) a,
     (SELECT LEVEL n FROM sys_dummy CONNECT BY LEVEL <= 200) b;
 
INSERT INTO je_win_avg_right
SELECT (a.n - 1) * 200 + b.n + 1000
FROM (SELECT LEVEL n FROM sys_dummy CONNECT BY LEVEL <= 100) a,
     (SELECT LEVEL n FROM sys_dummy CONNECT BY LEVEL <= 200) b;
 
ANALYZE TABLE je_win_avg_left COMPUTE STATISTICS;
ANALYZE TABLE je_win_avg_right COMPUTE STATISTICS;
 
EXPLAIN
SELECT DISTINCT AVG(DISTINCT l.k) OVER () AS avg_key
FROM je_win_avg_left l
LEFT JOIN je_win_avg_right r
  ON l.k = r.k OR r.k > 5;
 
SELECT DISTINCT AVG(DISTINCT l.k) OVER () AS avg_key
FROM je_win_avg_left l
LEFT JOIN je_win_avg_right r
  ON l.k = r.k OR r.k > 5;
 
DROP TABLE je_win_avg_left;
DROP TABLE je_win_avg_right;

预期输出

5000.5

实际输出

2500.75

日志信息

提单组织

内部开发

测试代码

hcs成员

9月4日添加了label：bug

opengauss_bot成员

9月4日评论：

This issue requires an assignee. Since you haven't specified one, we've assigned TestManager as the default assignee for this issue.

opengauss_bot成员

9月4日将 TestManager 设为负责人

opengauss_bot成员

9月4日添加了label：sig/StorageEngine

opengauss_bot成员

9月4日评论：

Welcome To openGauss Community

Hey @Nerifish , thanks for your contribution to the community.

Bot Usage Manual

I'm the Bot here serving you. You can find the instructions on how to interact with me at Here . That means you can comment below every pull request or issue to trigger Bot Commands. You can self-configure the PR merge rules for this repository. For more details, please refer to Here.

Contact Guide

If you have any questions, please contact the SIG: StorageEngine ,
and any of the maintainers: @CarrotGo, @chendong76, @chenxiaobin19, @congzhou2603, @dodders, @hwworkholic, @jemappellehc, @libiao2024, @muyulinzhong, @quemingjian, @shenzheng4, @shirley_zhengx, @superlchf, @totaj, @wlff234, @wofanzheng, @ywzq1161327784 ,
and any of the committers: @CityYard, @libiao2024, @lijieac, @theothersideofsea, @wang_bowen, @weithu, @xiong_xjun, @yuanyazhi, @zhangzq131 .

hcs成员

9月4日将 Nerifish 设为负责人，移除负责人 TestManager

hcs成员

9月4日 issue优先级由无优先级改变为不重要

hcs成员

9月4日关联了pull request：修复select语句avg聚合函数数据丢失问题，leftjoin超时问题

hcs成员

9月4日修改了issue 的描述

hcs成员

29 天前修改标题为 “[Bug]:select聚合函数avg错误；并且select用例执行时间异常”，原标题为“[Bug]:select聚合函数avg错误，用例超时”

hcs成员

29 天前修改了issue 的描述

liuzhen001成员

24 天前关联了看板：openGauss 7.0.0-LTS

hcs成员

24 天前评论：

hcs成员

24 天前 issue状态由待办的改变为待回归

wuchenlong成员

19 天前评论：

回归版本：7.0.0-LTS / archive B023（oGRAC Release 7.0.0-RC3 f72e371 build 2026-09-12 12:12:04）
回归环境：oGRAC 双节点（20.20.20.104 / 20.20.20.106），db=ONLINE，节点 20.20.20.104 执行
结论：通过（AVG(DISTINCT ...) OVER() 结果为 5000.5，不再返回错误值 2500.75；整段用例约 1s 完成）

说明：按 issue 中 je_win_avg_left/right 建表与聚合查询步骤验证（跳过步骤开头无关的 t1/t2 FOR UPDATE 示例句）。

完整回归 SQL

DROP TABLE IF EXISTS je_win_avg_left;
DROP TABLE IF EXISTS je_win_avg_right;

CREATE TABLE je_win_avg_left (k INT);
CREATE TABLE je_win_avg_right (k INT);

INSERT INTO je_win_avg_left
SELECT CASE WHEN a.n <= 50 THEN 1 ELSE (a.n - 51) * 200 + b.n END
FROM (SELECT LEVEL n FROM sys_dummy CONNECT BY LEVEL <= 100) a,
     (SELECT LEVEL n FROM sys_dummy CONNECT BY LEVEL <= 200) b;

INSERT INTO je_win_avg_right
SELECT (a.n - 1) * 200 + b.n + 1000
FROM (SELECT LEVEL n FROM sys_dummy CONNECT BY LEVEL <= 100) a,
     (SELECT LEVEL n FROM sys_dummy CONNECT BY LEVEL <= 200) b;

ANALYZE TABLE je_win_avg_left COMPUTE STATISTICS;
ANALYZE TABLE je_win_avg_right COMPUTE STATISTICS;

SELECT DISTINCT AVG(DISTINCT l.k) OVER () AS avg_key
FROM je_win_avg_left l
LEFT JOIN je_win_avg_right r
  ON l.k = r.k OR r.k > 5;

DROP TABLE je_win_avg_left;
DROP TABLE je_win_avg_right;

实际输出

AVG_KEY
----------------------------------------
5000.5

1 rows fetched.

期望值 5000.5，与缺陷值 2500.75 不符；执行时间正常。

wuchenlong成员

19 天前 issue状态由待回归改变为已验收

wuchenlong成员

19 天前关闭了 issue