注册

DM8中包含DENSE_RANK的视图/CTE执行GROUP BY ROLLUP时,相同空字符串被错误拆分为两个分组

Xy.Xie_WHU 2026/07/12 296 2

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:
DM Database Server 64 V8
DB Version: 0x7000d
03134284552-20260414-322369-20221
Msg Version: 1
Gsu level(5) cnt: 102
DM 管理工具
【操作系统】:
Windows 11 64 位
【CPU】:
Intel x86_64
【问题描述】*:
在 DM8 中,视图或 CTE 的查询结果包含 DENSE_RANK 窗口函数时,对其执行 GROUP BY ROLLUP,两个完全相同的空字符串会被错误拆分为两个不同分组。

将相同的查询结果通过 CTAS 物化到 ON COMMIT PRESERVE ROWS 全局临时表后,再执行相同的 GROUP BY ROLLUP,两个空字符串可以正确合并为一个分组。

结果对比如下:

视图: 4行,相同空字符串被拆分为两个分组
CTE: 4行,相同空字符串被拆分为两个分组
CTAS临时表:3行,相同空字符串正确合并

复现脚本
CREATE SCHEMA database0;
SET SCHEMA database0;
drop table if EXISTS T_DM_14;
drop view if EXISTS V_DM_14;
drop table if EXISTS TEMP_DM_14;
drop table if EXISTS TEMPOCPR_DM_14;

CREATE TABLE T_DM_14 (C0 VARCHAR2(16), C1 DATETIME, C2 TIME);
INSERT INTO T_DM_14 (C0, C1, C2) VALUES ('', TIMESTAMP '2000-02-29 23:59:59', NULL);
INSERT INTO T_DM_14 (C0, C1, C2) VALUES (' ', TIMESTAMP '1986-05-23 09:06:38', TIME '08:27:25');
INSERT INTO T_DM_14 (C0, C1, C2) VALUES ('', TIMESTAMP '1975-12-24 16:31:56', TIME '11:12:55');

CREATE VIEW V_DM_14 (VC_0, VC_1) AS SELECT C0 AS VC_0, DENSE_RANK() OVER (ORDER BY C0 ASC NULLS LAST, C1 ASC NULLS LAST, C2 ASC NULLS LAST) AS VC_1 FROM T_DM_14;
SELECT VC_0, COUNT(*), SUM(CAST((VC_1) AS DECIMAL(38,12))), AVG(CAST((VC_1) AS DECIMAL(38,12))) FROM V_DM_14 GROUP BY ROLLUP(VC_0);
-- row = 4

WITH CTE AS (SELECT C0 AS VC_0, DENSE_RANK() OVER (ORDER BY C0 ASC NULLS LAST, C1 ASC NULLS LAST, C2 ASC NULLS LAST) AS VC_1 FROM T_DM_14) SELECT VC_0, COUNT(*), SUM(CAST((VC_1) AS DECIMAL(38,12))), AVG(CAST((VC_1) AS DECIMAL(38,12))) FROM CTE GROUP BY ROLLUP(VC_0);
-- row = 4

CREATE GLOBAL TEMPORARY TABLE TEMP_DM_14 ON COMMIT PRESERVE ROWS AS SELECT C0 AS VC_0, DENSE_RANK() OVER (ORDER BY C0 ASC NULLS LAST, C1 ASC NULLS LAST, C2 ASC NULLS LAST) AS VC_1 FROM T_DM_14;
SELECT VC_0, COUNT(*), SUM(CAST((VC_1) AS DECIMAL(38,12))), AVG(CAST((VC_1) AS DECIMAL(38,12))) FROM TEMP_DM_14 GROUP BY ROLLUP(VC_0);
-- row = 3

回答 0
暂无回答
扫一扫
联系客服