注册

创建索引后查询结果不正常

Tiám 2023/12/05 664 11 已解决

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【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表的索引查询结果是正常的。

图片.png
图片.png

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