为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【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;

optimizer_or_nbexp 参数改成2试试