带一个正确的条件,查不出数据

[Hby118]

select * from test.problematic_table
where 1=1
and rqtyp = '13' and apsta in ('20','90') and prcnr='WC2023080400118'

用上述sql查不出数据
但是把'13' 改成 13 或者 rqtyp like '13' 就可以查到这一条 rqtyp 是字符串
或者把 and prcnr='WC2023080400118' 去掉 也能查到这一条

我试过不能复现这个问题,而且也是偶发的,第一次发现,删表,重新同步,能查询查到,今天又发现一个查不到的
不知道是哪里的问题,
数据是从datax同步过来的,那边是pg,数据类型也是字符串

下面的建表和insert 但是不能复现
CREATE TABLE test.problematic_table (
rqdat date NULL COMMENT '申请时间',
prcnr varchar(32) NULL COMMENT '加班单号',
id varchar(36) NULL,
rqusr varchar(32) NULL COMMENT '申请人',
rqtyp varchar(32) NULL COMMENT '申请类型',
oapnr varchar(32) NULL COMMENT 'oa流程编号',
apsta varchar(32) NULL COMMENT '审核状态',
rqsta varchar(32) NULL COMMENT '单据状态',
pnalt varchar(32) NULL COMMENT '立讯工号',
orgeh varchar(30) NULL COMMENT '部门code',
cuser varchar(32) NULL COMMENT '创建人',
cdtim datetime NULL COMMENT '创建时间',
uuser varchar(32) NULL COMMENT '更新人',
udtim datetime NULL COMMENT '更新日期',
title varchar(300) NULL COMMENT '标题',
applyfs varchar(10) NULL COMMENT 'A单人B批导C自动生成D工作台申请',
attachments varchar(2000) NULL COMMENT '附件(外出)',
group_id varchar(32) NULL COMMENT 'oa流单号',
creation_date datetime NULL COMMENT '数据上传时间',
created_by varchar(255) NULL
) ENGINE=OLAP
UNIQUE KEY(rqdat, prcnr)
COMMENT 'OLAP'
PARTITION BY RANGE(rqdat)
(PARTITION p20230615 VALUES [('2023-06-15'), ('2023-06-16')),
PARTITION p20230616 VALUES [('2023-06-16'), ('2023-06-17')),
PARTITION p20230617 VALUES [('2023-06-17'), ('2023-06-18')),
PARTITION p20230618 VALUES [('2023-06-18'), ('2023-06-19')),
PARTITION p20230619 VALUES [('2023-06-19'), ('2023-06-20')),
PARTITION p20230620 VALUES [('2023-06-20'), ('2023-06-21')),
PARTITION p20230621 VALUES [('2023-06-21'), ('2023-06-22')),
PARTITION p20230622 VALUES [('2023-06-22'), ('2023-06-23')),
PARTITION p20230623 VALUES [('2023-06-23'), ('2023-06-24')),
PARTITION p20230624 VALUES [('2023-06-24'), ('2023-06-25')),
PARTITION p20230625 VALUES [('2023-06-25'), ('2023-06-26')),
PARTITION p20230626 VALUES [('2023-06-26'), ('2023-06-27')),
PARTITION p20230627 VALUES [('2023-06-27'), ('2023-06-28')),
PARTITION p20230628 VALUES [('2023-06-28'), ('2023-06-29')),
PARTITION p20230629 VALUES [('2023-06-29'), ('2023-06-30')),
PARTITION p20230630 VALUES [('2023-06-30'), ('2023-07-01')),
PARTITION p20230701 VALUES [('2023-07-01'), ('2023-07-02')),
PARTITION p20230702 VALUES [('2023-07-02'), ('2023-07-03')),
PARTITION p20230703 VALUES [('2023-07-03'), ('2023-07-04')),
PARTITION p20230704 VALUES [('2023-07-04'), ('2023-07-05')),
PARTITION p20230705 VALUES [('2023-07-05'), ('2023-07-06')),
PARTITION p20230706 VALUES [('2023-07-06'), ('2023-07-07')),
PARTITION p20230707 VALUES [('2023-07-07'), ('2023-07-08')),
PARTITION p20230708 VALUES [('2023-07-08'), ('2023-07-09')),
PARTITION p20230709 VALUES [('2023-07-09'), ('2023-07-10')),
PARTITION p20230710 VALUES [('2023-07-10'), ('2023-07-11')),
PARTITION p20230711 VALUES [('2023-07-11'), ('2023-07-12')),
PARTITION p20230712 VALUES [('2023-07-12'), ('2023-07-13')),
PARTITION p20230713 VALUES [('2023-07-13'), ('2023-07-14')),
PARTITION p20230714 VALUES [('2023-07-14'), ('2023-07-15')),
PARTITION p20230715 VALUES [('2023-07-15'), ('2023-07-16')),
PARTITION p20230716 VALUES [('2023-07-16'), ('2023-07-17')),
PARTITION p20230717 VALUES [('2023-07-17'), ('2023-07-18')),
PARTITION p20230718 VALUES [('2023-07-18'), ('2023-07-19')),
PARTITION p20230719 VALUES [('2023-07-19'), ('2023-07-20')),
PARTITION p20230720 VALUES [('2023-07-20'), ('2023-07-21')),
PARTITION p20230721 VALUES [('2023-07-21'), ('2023-07-22')),
PARTITION p20230722 VALUES [('2023-07-22'), ('2023-07-23')),
PARTITION p20230723 VALUES [('2023-07-23'), ('2023-07-24')),
PARTITION p20230724 VALUES [('2023-07-24'), ('2023-07-25')),
PARTITION p20230725 VALUES [('2023-07-25'), ('2023-07-26')),
PARTITION p20230726 VALUES [('2023-07-26'), ('2023-07-27')),
PARTITION p20230727 VALUES [('2023-07-27'), ('2023-07-28')),
PARTITION p20230728 VALUES [('2023-07-28'), ('2023-07-29')),
PARTITION p20230729 VALUES [('2023-07-29'), ('2023-07-30')),
PARTITION p20230730 VALUES [('2023-07-30'), ('2023-07-31')),
PARTITION p20230731 VALUES [('2023-07-31'), ('2023-08-01')),
PARTITION p20230801 VALUES [('2023-08-01'), ('2023-08-02')),
PARTITION p20230802 VALUES [('2023-08-02'), ('2023-08-03')),
PARTITION p20230803 VALUES [('2023-08-03'), ('2023-08-04')),
PARTITION p20230804 VALUES [('2023-08-04'), ('2023-08-05')),
PARTITION p20230805 VALUES [('2023-08-05'), ('2023-08-06')),
PARTITION p20230806 VALUES [('2023-08-06'), ('2023-08-07')),
PARTITION p20230807 VALUES [('2023-08-07'), ('2023-08-08')))
DISTRIBUTED BY HASH(rqdat, prcnr) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "10",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"dynamic_partition.storage_medium" = "HDD",
"in_memory" = "false",
"storage_format" = "V2",
"disable_auto_compaction" = "false"
);

insert into test.problematic_table values ('2023-08-04','WC2023080400118','1687368114600640514','0000','13','','20','10','0000','0000','0000','2023-08-04 15:41:23','0000','2023-08-04 15:43:01','0000','','','','2023-08-04 15:50:10','0000');

[Hby118]

又试了 trim(rqtyp)= '13' 和replace 都可以查出来....
但是这个13没有空格啊

[LemonLiTree]

which doris version?

[Hby118]

不好意思忘记说了 1.2.4

[CLinYushen]

我想我也遇到了这个问题，我使用的版本是selectdb2.1.1.
我使用stream load的方式导入本地文件，发现账号等长数字（超过16位的纯数字组成）的字符串无法正常查询，会截取前16位忽略后续的数字（查询出前16位数字相同的账号）。即使我通过cast函数转义为char类型或者varchar类型并将该字段插入到新表中也会存在这个问题。而trim后就可以正常查询。后面发现可能是在导入过程中本地导入文件该字段存在空格导致，在导入时将该字段提前trim并cast为char类型后，便不存在这个问题。
这个现象奇怪在本地文件中该字段后面多了两个空格，后续即使我在查询过程中给该字段再补充两个空格也没法查询出来。