https://github.com/StarRocks/starrocks/issues/10326
SQL 语句:
select * from
(
select * from personinfo A
where 1=1
and personid in (
SELECT distinct persons.PersonId FROM personinfo persons
left join personassign jobAssign on persons.PersonId=jobAssign.PersonId
where persons.IsDeleted = 0 AND lower(jobAssign.Tenant) =‘hronehrcore’
AND( jobAssign.EffectDate <= now() AND ( jobAssign.ExpireDate >= now() OR jobAssign.ExpireDate IS NULL)
and jobAssign.IsDeleted = 0) AND IsValidSeq = 1
AND (( jobAssign.UnitId IN (‘250ebe43-f848-4299-838c-5a028cc054c7’,‘4b2a937d-f769-4370-9f08-45e94aaf9525’,‘4b7c66ca-f559-43a9-aa52-73bb2a73ce69’,‘92582740-0749-42f4-a71a-38c38105f09a’,‘ab43522c-f3e5-4076-b736-e63aca47b5e8’,‘ac750c9e-5558-40e5-a19b-17655a45e098’,‘e434ebfd-de91-41fb-801d-33d3a661cb32’,‘f99d07c8-1107-4ef1-b989-9220250857ea’)))
AND jobAssign.EmploymentId in (
select FIRST_VALUE(id) over(PARTITION By PersonId order BY HireDate DESC)
FROM personemployee WHERE IsDeleted = 0 AND lower(Tenant) =‘hronehrcore’
AND HireDate<now())
)
and personid in (
SELECT distinct persons.PersonId
FROM personinfo
left join personassign jobAssign on persons.PersonId=jobAssign.PersonId
where persons.IsDeleted = 0 AND lower(jobAssign.Tenant) =‘hronehrcore’ AND(jobAssign.EffectDate <= now()
AND ( jobAssign.ExpireDate >= now() OR jobAssign.ExpireDate IS NULL) and jobAssign.IsDeleted = 0)
AND IsValidSeq = 1 AND jobAssign.Status=1
)
)vresult limit 0,25
重现步骤:
单独查里面的sql 能够查到数据
整体查询, 使用limit 0, 25分页后 查不到数据
去掉 and personid in 任意一个条件, limit 0, 25能够查到数据
调换 and personid in 条件换成 and tenant in (换其他字段查), limit 0, 25能够查到数据
期望能够直接执行这个sql 查出结果 或者通过调整配置来解决问题
StarRocks version (Required)
2.3.0-RC01 [e14245a]