注册

Bug上报:查询语句,OR逻辑运算符嵌套在not()中时,查询结果与预期不符not(("ItemId" is null or "ItemId" = ''))

Ljw 2025/11/15 199 1

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:
DM Database Server 64 V8
DB Version: 0x7000d
03134284368-20250423-270902-20149
Msg Version: 43
Mode: NORLMAL
CASE_SENSITIVE: 1
注:容器版本
镜像:dm8_single:dm8_20250506_rev270902_x86_rh6_64
【操作系统】:
Ubuntu 24.04.2 LTS
Docker version 28.1.1, build 4eba377
【CPU】:
【问题描述】*:
当not()中包含 OR 逻辑运算符时,查询结果与预期不符,下面已提供两个验证sql,A形式判断条件 和 B形式判断条件。

正情况下B形式,查询结果不应当包含"ItemId"为null或者为''的数据行,但实际情况却查出了包含null的数据行

正情况下A形式,查询结果 会包含"ItemId"为空字符串的记录,但实际情况却反而没有包含任何 null 或 '' 的记录。

-- 条件A形式,not((c."ItemId" is null and c."ItemId" = '')) SELECT t.* FROM ( SELECT a."vehicleId" as1, a."plate" as2, a."platecolor" as3 -- ……其余列省略…… FROM TSP_BASICS."vehicle" a INNER JOIN TSP_BASICS."vehicleType" b ON a."vehicleTypeId" = b."vehicleTypeId" AND (b."isDelete" = 0) LEFT JOIN TSP_BASICS."xj_SpecialVehiclePriceItem" c ON to_char(a."vehicleSpec") = c."VehicleSpecification" WHERE (a."sharingCenterId" = '690055dbf3378fc6df918ad8') AND (a."vehicleTypeId" = '607e927cd85be606703d77b3') AND (((a."vehicleId") in ('20250315031','20250315033','20250315034','20250315035',))) AND (a."isDelete" = 0 AND a."isEnable" = 1) AND (b."isDelete" = 0) AND (b."costType" > 0) AND (not((c."ItemId" is null and c."ItemId" = ''))) AND (b."costVehicle" > 0) AND (a."isDelete" = 0) ORDER BY a."plate" ) t WHERE ROWNUM < 1000000; --- -- 条件B形式,not((c."ItemId" is null or c."ItemId" = '')) SELECT t.* FROM ( SELECT a."vehicleId" as1, a."plate" as2, a."platecolor" as3 -- ……其余列省略…… FROM TSP_BASICS."vehicle" a INNER JOIN TSP_BASICS."vehicleType" b ON a."vehicleTypeId" = b."vehicleTypeId" AND (b."isDelete" = 0) LEFT JOIN TSP_BASICS."xj_SpecialVehiclePriceItem" c ON to_char(a."vehicleSpec") = c."VehicleSpecification" WHERE (a."sharingCenterId" = '690055dbf3378fc6df918ad8') AND (a."vehicleTypeId" = '607e927cd85be606703d77b3') AND (((a."vehicleId") in ('20250315031','20250315033','20250315034','20250315035',))) AND (a."isDelete" = 0 AND a."isEnable" = 1) AND (b."isDelete" = 0) AND (b."costType" > 0) AND (not((c."ItemId" is null or c."ItemId" = ''))) AND (b."costVehicle" > 0) AND (a."isDelete" = 0) ORDER BY a."plate" ) t WHERE ROWNUM < 1000000;
回答 0
暂无回答
扫一扫
联系客服