为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【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 中,将包含 ROUND(DOUBLE, 2) 结果的查询通过 CTAS 物化到 ON COMMIT PRESERVE ROWS 全局临时表后,使用相关 NOT EXISTS 查询时出现错误结果。
同一个源查询通过视图和 CTE 执行时均返回32行,但通过全局临时表执行时返回48行。
复现脚本
CREATE SCHEMA database0;
SET SCHEMA database0;
drop table if EXISTS T_DM_1897;
drop view if EXISTS V_DM_1897;
drop table if EXISTS TEMP_DM_1897;
drop table if EXISTS TEMPOCPR_DM_1897;
CREATE TABLE T_DM_1897 (C0 NUMBER(10,2), C1 VARCHAR(512), C2 VARCHAR(16), C3 DOUBLE, C4 BLOB, C5 DOUBLE, C6 VARCHAR(8), C7 FLOAT);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (46570685.80, 'POINT (0 0)', 'red', NULL, NULL, 1000000000000.0, NULL, NULL);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (-89553115.81, 'POINT (0 0)', NULL, 791003.405001223, NULL, -594864.27458651, 'A ', 0.3);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (6733231.08, 'POINT (1.5 -2.5)', 'BLUE', -97527.625100878, 0x6ca390254ecdfd2afa46ee9a281b8cb6f90d637c805b4c4f5a3280bd427d7b3d06e492c68dba86678b9c0e546f59aec5a6fad68e8d299f28d8b31d, 0.1, '1F6VWPS', 1e-12);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (NULL, 'POLYGON ((0 0, 0 1, 1 1, 1 0, 0 0))', 'RED', 0.1, 0x00ff, 0.0, 'O3VDcE3I', 0.0);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (-83934994.57, 'POINT (1.5 -2.5)', 'red', 505479.967675731, 0x5c340b6b74f32b0ca87770dd4e0c6bdea26931740ce295beb0, 860541.897705908, '', 0.3);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (NULL, 'LINESTRING (0 0, 1 1)', NULL, 0.2, 0x00, 0.0, 'A', 29788.835633539);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (-0.01, 'POINT (0 0)', '', 630140.619904737, 0x00, NULL, '', -88743.222349307);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (-69094926.46, 'POINT (1.5 -2.5)', 'RED', 0.1, 0x11e4339b188a827e1c7c075c13be793eb7782d614bad3a52aad7aff1509f1a419b9df8029fb67165c15139cd1edfdcb860caea991f5d6824, 593981.294695359, 'ickrK_Nx', -0.0);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (-45689624.62, 'POINT (1.5 -2.5)', 'BLUE', -937470.265596759, 0xd171f20e8b37bf3a7f48c05b5ef9d8, -184260.240199871, '\t', -0.0);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (89119619.91, 'POINT (0 0)', '', 3.141592653589793, 0x00, NULL, '_', -174201.282006004);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (32184251.40, 'POLYGON ((0 0, 0 1, 1 1, 1 0, 0 0))', NULL, NULL, 0x00, 954945.500788776, 'A', -0.0);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (-0.01, NULL, 'GREEN', -289938.420198205, 0xff, -852002.89461012, 'ECH10', 326408.697771715);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (0, 'POINT (0 0)', NULL, -634625.877624257, NULL, 209458.356708897, 'JXs', 1000000000000.0);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (-0.01, 'LINESTRING (0 0, 1 1)', NULL, 0.0, 0x17d60d6b3c1c04e963405ab64cc6bd1ba530b675bcbacb8aeef5335c7fd56702faf8664489890cb3e4ea4fd193572c0d563ed7db7b01e7bcbde1b2971a59cf22, -497923.924877321, NULL, 951065.561283139);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (10099643.63, 'LINESTRING (0 0, 1 1)', 'BLUE', -563206.180952976, 0xb29b79bc575da57ff9c210b3a45b770ea50aff45d26f932fe2a0a65317b89d, NULL, '', 0.2);
INSERT INTO T_DM_1897 (C0, C1, C2, C3, C4, C5, C6, C7) VALUES (-21725468.55, 'POINT (0 0)', '', -1e-12, 0xb2727f033c9974bcb9241979e9c65883ae870257c1028ff8375d50e0effdadd2e7354514ebc803c8d25231223ef3de2c85a74efb, -703254.499743208, 'hw', NULL);
CREATE VIEW V_DM_1897 (VC_0, VC_1, VC_2, VC_3) AS SELECT CEIL(A.C5) AS VC_0, LENGTH(A.C4) AS VC_1, A.C1 AS VC_2, ROUND(A.C3, 2) AS VC_3 FROM T_DM_1897 A CROSS JOIN T_DM_1897 B WHERE (A.C7 IS NOT NULL) OR (LOWER(A.C2) = LOWER(A.C2));
SELECT VC_0, VC_3, VC_1, VC_2 FROM V_DM_1897 A WHERE NOT EXISTS (SELECT 1 FROM V_DM_1897 B WHERE A.VC_3=B.VC_3);
-- row 32
WITH CTE AS (SELECT CEIL(A.C5) AS VC_0, LENGTH(A.C4) AS VC_1, A.C1 AS VC_2, ROUND(A.C3, 2) AS VC_3 FROM T_DM_1897 A CROSS JOIN T_DM_1897 B WHERE (A.C7 IS NOT NULL) OR (LOWER(A.C2) = LOWER(A.C2))) SELECT VC_0, VC_3, VC_1, VC_2 FROM CTE A WHERE NOT EXISTS (SELECT 1 FROM CTE B WHERE A.VC_3=B.VC_3);
-- row 32
CREATE GLOBAL TEMPORARY TABLE TEMP_DM_1897 ON COMMIT PRESERVE ROWS AS SELECT CEIL(A.C5) AS VC_0, LENGTH(A.C4) AS VC_1, A.C1 AS VC_2, ROUND(A.C3, 2) AS VC_3 FROM T_DM_1897 A CROSS JOIN T_DM_1897 B WHERE (A.C7 IS NOT NULL) OR (LOWER(A.C2) = LOWER(A.C2));
SELECT VC_0, VC_3, VC_1, VC_2 FROM TEMP_DM_1897 A WHERE NOT EXISTS (SELECT 1 FROM TEMP_DM_1897 B WHERE A.VC_3=B.VC_3);
-- row 48

感谢您的反馈,我们会跟进分析。