where 条件查询返回结果不固定的问题

[wangfei]

【详述】primary key模型
【背景】select column… from table where
【业务影响】starrocks 根据SELECT count(1) FROM project_record WHERE called_num=’’ AND YEAR(ctime)=‘2021’; 查询的结果多此不一样。
1，where ltrim(called_num)=’’/rtrim(called_num)=’’ 结果一致，说明数据本身不存在左，右，或者两边都存在空格的情况
2，group by 不加where called_num=’’ 获取的某个called_num count(1)也是一致的，同样说明数据本身没问题

【StarRocks版本】例如：2.0.1
【集群规模】3fe（1 follower+2observer）+4be（fe与be混部）
【机器信息】32/128/千兆网
【附件】

[shemplle]

能发一下查询的时候的explain吗？
多试几次对比一下看一下有没有不同

[wangfei]

看了 没看到有啥区别

[wangfei]

[Natsume729]

请问该字段建表是不是int类型，查询的时候把右边的引号去掉看看结果一致嘛，新版本修复了一个int和varchar等值匹配的问题，建议升级到最新版本验证下

[wangfei]

string 类型的，去掉’'也是查出来会结果不一致

[wangfei]

已经是2.0.1版本了

[wangfei]

[shemplle]

能发送一下建表语句吗？

[wangfei]

好的 下面是建表语句
create table hd_sh_ods.project_record
(
id bigint(20) NOT NULL,
province_id int(6) NOT NULL,
partition_time date not null,
called_num string NOT NULL DEFAULT “”,
ctime DateTime NOT NULL,
cid bigint(20) ,
agent_id int(8),
project_id int(8),
city_id int(11),
batch_id int(8),
task_id int(8),
uid int(8),
event int(8),
call_state int(8),
extension string DEFAULT “”,
caller_num string DEFAULT “”,
utime DateTime ,
end_result int(8),
qc_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
begin_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
call_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
call_duration int(8),
alert_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
altert_duration int(8),
connect_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
release_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
satisfied int(11),
record_url string DEFAULT “”,
sequence_id string DEFAULT “”,
record_id string DEFAULT “”,
if_callback int(11),
call_remark string DEFAULT “”,
communicate_result int(11),
type int(8),
qc_uid int(11),
qc2_aid int(11),
qc_result int(8),
qc_remark string DEFAULT “”,
qc_status int(8),
qc2_status int(8),
called_button_info string DEFAULT “”,
transfer_button_info string DEFAULT “”,
hangup_reason string DEFAULT “”,
is_send_sms string DEFAULT “”,
area_code string DEFAULT “”,
dial_count int(8),
score int(11),
assess int(11),
qc_result2 int(11),
qc_time2 DateTime DEFAULT ‘1970-01-01 00:00:00’,
qc_remark2 string DEFAULT “”,
is_check int(8),
bonus int(8),
bonus_amount float,
is_qc_up int(11),
display_number string DEFAULT “”,
results string DEFAULT “”,
qc_up_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
custom_name string DEFAULT “”,
custom_type int(11),
phone_tail string DEFAULT “”,
order_status int(8),
birth_tail string DEFAULT “”,
dtmfinfo string DEFAULT “”,
callid int(11),
dtmfinfo_time int(8),
speech_record string DEFAULT “”,
treatment_id string DEFAULT “”,
add_black int(8),
ivr_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
ai_qc_result int(11),
fail_rule_group string DEFAULT “”,
review_result int(8),
final_result int(8),
review_aid int(11),
ai_match_degree float,
ai_qc_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
real_code int(11),
user_intention int(8),
task_type int(8),
unknown_counts int(8),
is_intervene int(11),
intervene_time DateTime DEFAULT ‘1970-01-01 00:00:00’,
is_robot int(8),
media_ip string DEFAULT “”,
media_packet_count int(11),
skip_packet_count int(11),
ext1 string DEFAULT “”,
ext2 string DEFAULT “”,
ext3 string DEFAULT “”,
ext4 string DEFAULT “”,
ext5 string DEFAULT “”,
ext6 string DEFAULT “”,
ext7 string DEFAULT “”,
ext8 string DEFAULT “”,
ext9 string DEFAULT “”,
ext10 string DEFAULT “”,
ext11 int(11),
ext12 int(11),
positions int(11),
is_force_intervene int(11),
interrupt_counts int(30),
question_counts int(11),
emotion_words string DEFAULT “”,
device_type int(11),
transfer_status int(11),
transfer_time string DEFAULT “”,
transfer_uid int(11),
project_city_id int(11),
callback_type int(8),
project_type int(11),
custom_list string DEFAULT “”,
last_sequence_id string DEFAULT “”,
own_complaint_id int(11),
unicom_complaint_id int(11),
qc_review int(8),
qc_review_remark string DEFAULT “”,
is_open_account int(11),
open_account_order_id string DEFAULT “”,
open_accout_code int(11),
open_accout_result string DEFAULT “”,
is_send_open_accout int(11),
open_accout_sms_content string DEFAULT “”,
company_id int(11),
var01 string DEFAULT “”,
var02 string DEFAULT “”,
staff_id string DEFAULT “”,
quantity_order_status int(8),
quantity_call_status int(8),
qc_time3 DateTime DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘二检审核时间’,
qc_remark3 string DEFAULT “”,
qcUp_time DateTime DEFAULT ‘1970-01-01 00:00:00’ COMMENT ‘一检提交时间’,
secondQcBatch_id int(11) COMMENT ‘二检批次id’,
qc_submit_status int(11) COMMENT ‘一检提交状态 0 ：未提交；1:已提交，二检只能看到状态为1的数据’,
qc_result3 int(8) COMMENT '二检审核状态 0:未质检 1:合格审核 2:不合格 3:通过审核 ',
index province_id (province_id) using BITMAP COMMENT ‘province_id’,
INDEX cid (cid) USING BITMAP COMMENT ‘custom_id’,
INDEX agent_id (agent_id) USING BITMAP COMMENT ‘agent_id’,
INDEX project_id (project_id) USING BITMAP COMMENT ‘project_id’,
INDEX ctime (ctime) USING BITMAP COMMENT ‘ctime’,
INDEX called_num (called_num) USING BITMAP COMMENT ‘called_num’,
INDEX city_id (city_id) USING BITMAP COMMENT ‘city_id’,
INDEX batch_id (batch_id) USING BITMAP COMMENT ‘batch_id’,
INDEX task_id (task_id) USING BITMAP COMMENT ‘task_id’,
INDEX qc_time (qc_time) USING BITMAP COMMENT ‘qc_time’,
INDEX uid (uid) USING BITMAP COMMENT ‘uid’,
INDEX call_state (call_state) USING BITMAP COMMENT ‘call_state’

)ENGINE=OLAP
PRIMARY KEY(id,province_id,partition_time)

PARTITION BY RANGE(partition_time)
(
PARTITION p201901 VALUES [(‘2019-01-01’),(‘2019-02-01’)),
PARTITION p201902 VALUES [(‘2019-02-01’),(‘2019-03-01’)),
PARTITION p201903 VALUES [(‘2019-03-01’),(‘2019-04-01’)),
PARTITION p201904 VALUES [(‘2019-04-01’),(‘2019-05-01’)),
PARTITION p201905 VALUES [(‘2019-05-01’),(‘2019-06-01’)),
PARTITION p201906 VALUES [(‘2019-06-01’),(‘2019-07-01’)),
PARTITION p201907 VALUES [(‘2019-07-01’),(‘2019-08-01’)),
PARTITION p201908 VALUES [(‘2019-08-01’),(‘2019-09-01’)),
PARTITION p201909 VALUES [(‘2019-09-01’),(‘2019-10-01’)),
PARTITION p201910 VALUES [(‘2019-10-01’),(‘2019-11-01’)),
PARTITION p201911 VALUES [(‘2019-11-01’),(‘2019-12-01’)),
PARTITION p201912 VALUES [(‘2019-12-01’),(‘2020-01-01’)),
PARTITION p202001 VALUES [(‘2020-01-01’),(‘2020-02-01’)),
PARTITION p202002 VALUES [(‘2020-02-01’),(‘2020-03-01’)),
PARTITION p202003 VALUES [(‘2020-03-01’),(‘2020-04-01’)),
PARTITION p202004 VALUES [(‘2020-04-01’),(‘2020-05-01’)),
PARTITION p202005 VALUES [(‘2020-05-01’),(‘2020-06-01’)),
PARTITION p202006 VALUES [(‘2020-06-01’),(‘2020-07-01’)),
PARTITION p202007 VALUES [(‘2020-07-01’),(‘2020-08-01’)),
PARTITION p202008 VALUES [(‘2020-08-01’),(‘2020-09-01’)),
PARTITION p202009 VALUES [(‘2020-09-01’),(‘2020-10-01’)),
PARTITION p202010 VALUES [(‘2020-10-01’),(‘2020-11-01’)),
PARTITION p202011 VALUES [(‘2020-11-01’),(‘2020-12-01’)),
PARTITION p202012 VALUES [(‘2020-12-01’),(‘2021-01-01’)),
PARTITION p202101 VALUES [(‘2021-01-01’),(‘2021-02-01’)),
PARTITION p202102 VALUES [(‘2021-02-01’),(‘2021-03-01’)),
PARTITION p202103 VALUES [(‘2021-03-01’),(‘2021-04-01’)),
PARTITION p202104 VALUES [(‘2021-04-01’),(‘2021-05-01’)),
PARTITION p202105 VALUES [(‘2021-05-01’),(‘2021-06-01’)),
PARTITION p202106 VALUES [(‘2021-06-01’),(‘2021-07-01’)),
PARTITION p202107 VALUES [(‘2021-07-01’),(‘2021-08-01’)),
PARTITION p202108 VALUES [(‘2021-08-01’),(‘2021-09-01’)),
PARTITION p202109 VALUES [(‘2021-09-01’),(‘2021-10-01’)),
PARTITION p202110 VALUES [(‘2021-10-01’),(‘2021-11-01’)),
PARTITION p202111 VALUES [(‘2021-11-01’),(‘2021-12-01’)),
PARTITION p202112 VALUES [(‘2021-12-01’),(‘2022-01-01’)),
PARTITION p202201 VALUES [(‘2022-01-01’),(‘2022-02-01’)),
PARTITION p202202 VALUES [(‘2022-02-01’),(‘2022-03-01’)),
PARTITION p202203 VALUES [(‘2022-03-01’),(‘2022-04-01’)),
PARTITION p202204 VALUES [(‘2022-04-01’),(‘2022-05-01’)),
PARTITION p202205 VALUES [(‘2022-05-01’),(‘2022-06-01’)),
PARTITION p202206 VALUES [(‘2022-06-01’),(‘2022-07-01’)),
PARTITION p202207 VALUES [(‘2022-07-01’),(‘2022-08-01’)),
PARTITION p202208 VALUES [(‘2022-08-01’),(‘2022-09-01’)),
PARTITION p202209 VALUES [(‘2022-09-01’),(‘2022-10-01’)),
PARTITION p202210 VALUES [(‘2022-10-01’),(‘2022-11-01’)),
PARTITION p202211 VALUES [(‘2022-11-01’),(‘2022-12-01’)),
PARTITION p202212 VALUES [(‘2022-12-01’),(‘2023-01-01’)),
PARTITION p202301 VALUES [(‘2023-01-01’),(‘2023-02-01’)),
PARTITION p202302 VALUES [(‘2023-02-01’),(‘2023-03-01’)),
PARTITION p202303 VALUES [(‘2023-03-01’),(‘2023-04-01’)),
PARTITION p202304 VALUES [(‘2023-04-01’),(‘2023-05-01’)),
PARTITION p202305 VALUES [(‘2023-05-01’),(‘2023-06-01’)),
PARTITION p202306 VALUES [(‘2023-06-01’),(‘2023-07-01’)),
PARTITION p202307 VALUES [(‘2023-07-01’),(‘2023-08-01’)),
PARTITION p202308 VALUES [(‘2023-08-01’),(‘2023-09-01’)),
PARTITION p202309 VALUES [(‘2023-09-01’),(‘2023-10-01’)),
PARTITION p202310 VALUES [(‘2023-10-01’),(‘2023-11-01’)),
PARTITION p202311 VALUES [(‘2023-11-01’),(‘2023-12-01’)),
PARTITION p202312 VALUES [(‘2023-12-01’),(‘2024-01-01’))
)
DISTRIBUTED BY HASH(id) BUCKETS 16
PROPERTIES(“replication_num” = “3”);

[shemplle]

方便将有问题的数据以文件形式发送过来吗？我们这边复现一下，如果复现出来是bug的话，需要提交产研那边进行改进

[wangfei]

我把查询有问题的数据(昨天的数据)插入一个表结构一样且是空的测试表里，可以查到/查全，但是原表查不到/差不全

[wangfei]

[wangfei]

[wangfei]

数据是通过routine load 加载的