为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM Database Server x64 V8
【操作系统】:YhKylin 4.19.90
【CPU】:Intel(R) Core(TM) i5-7500T
【问题描述】*:sql查询结果不正常。
测试脚本如下:
CREATE TABLE "TI"
(
"ID" BIGINT,
"NAME" BIGINT) STORAGE(ON "MAIN", CLUSTERBTR) ;
CREATE TABLE "T2"
(
"ID" BIGINT NOT NULL,
"T1_ID" BIGINT NOT NULL,
NOT CLUSTER PRIMARY KEY("ID", "T1_ID")
)STORAGE(ON "MAIN", CLUSTERBTR) ;
INSERT INTO "TI"("ID","NAME") VALUES(1,123);
INSERT INTO "TI"("ID","NAME") VALUES(2,2312);
INSERT INTO "TI"("ID","NAME") VALUES(3,2321);
INSERT INTO "TI"("ID","NAME") VALUES(0,23213);
INSERT INTO "TI"("ID","NAME") VALUES(0,23123);
INSERT INTO "TI"("ID","NAME") VALUES(4,null);
INSERT INTO "TI"("ID","NAME") VALUES(5,1);
INSERT INTO "TI"("ID","NAME") VALUES(6,1);
INSERT INTO "TI"("ID","NAME") VALUES(7,1);
INSERT INTO "T2"("ID","T1_ID") VALUES(2,1);
INSERT INTO "T2"("ID","T1_ID") VALUES(3,2);
INSERT INTO "T2"("ID","T1_ID") VALUES(4,3);
INSERT INTO "T2"("ID","T1_ID") VALUES(5,4);
select a.id, b.T1_ID
from TI a
left join T2 b on a.id = b.id
where (b.T1_ID != 10 or b.T1_ID is null);
图2的查询条件 or b.T1_ID is null 没有生效,如果删除T2表的索引查询结果是正常的。
根据你提供的脚本,在本机验证是没有问题,而且T2表只有一个主键,你是否又新增了其他索引吗?
发一下你的执行计划
没有看到T2表存在索引哈,您看看是不是补充一下。