You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "guanghuan96 (via GitHub)" <gi...@apache.org> on 2024/01/03 02:37:42 UTC
[I] [Bug] where条件带in的子查询过滤出现少数据问题 [doris]
guanghuan96 opened a new issue, #29443:
URL: https://github.com/apache/doris/issues/29443
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.
### Version
2.0.3
### What's Wrong?
完整sql语句如下:
```
select
count(distinct employee_no) as employee_no
from retail_dim.dim_ehr_org_staff_position_all_1229
where employee_no in (
select distinct employee_no
from retail_dim.dim_ehr_org_staff_position_all_1229
where office_start_date = '2023-12-01'
)
```
-- 问题描述:
where条件里的子查询查出的结果集条数为:2012条
select count(distinct employee_no)
from retail_dim.dim_ehr_org_staff_position_all_1229
where office_start_date = '2023-12-01'
但是外面整个语句查询数量却少了一半,只有918条
select
count(distinct employee_no)
from retail_dim.dim_ehr_org_staff_position_all_1229
where employee_no in (
select distinct employee_no
from retail_dim.dim_ehr_org_staff_position_all_1229
where office_start_date = '2023-12-01'
)
```
CREATE TABLE `dim_ehr_org_staff_position_all_1229` (
`employee_no` char(20) NULL COMMENT '员工工号',
`organ_new_no` char(20) NULL COMMENT '组织编码',
`position_no` char(10) NULL COMMENT '岗位编号',
`office_start_date` date NULL COMMENT '任职开始日期',
`office_status` tinyint(4) NULL COMMENT '在本组织任职状态(1:在职,0:失效)',
`employee_name` char(50) NULL COMMENT '员工姓名',
`parent_employee_no` char(10) NULL COMMENT '上级领导编码',
`parent_employee_name` varchar(50) NULL COMMENT '上级领导姓名',
`unit_type` char(20) NULL COMMENT '组织类型',
`organ_new_name` varchar(200) NULL COMMENT '组织名称',
`position_name` char(50) NULL COMMENT '岗位名称',
`position_type` tinyint(4) NULL COMMENT '岗位类型(01:管理线,02:陈列线)',
`brand_unit_no` char(20) NULL COMMENT '品牌部编码',
`region_no` char(10) NULL COMMENT '小区编码',
`region_name` char(30) NULL COMMENT '小区名称',
`office_end_date` date NULL COMMENT '任职结束日期',
`update_time` datetime NULL COMMENT '更新时间'
) ENGINE=OLAP
UNIQUE KEY(`employee_no`, `organ_new_no`, `position_no`, `office_start_date`, `office_status`)
COMMENT '员工任职拉链表'
DISTRIBUTED BY HASH(`employee_no`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);
```
-- 执行计划如下:
PLAN FRAGMENT 0
OUTPUT EXPRS:
employee_no[#46]
PARTITION: UNPARTITIONED
HAS_COLO_PLAN_NODE: false
VRESULT SINK
809:VAGGREGATE (merge finalize)
| output: count(employee_no[#45])[#46]
| group by:
| cardinality=1
|
806:VEXCHANGE
offset: 0
PLAN FRAGMENT 1
PARTITION: HASH_PARTITIONED: employee_no[#21]
HAS_COLO_PLAN_NODE: true
STREAM DATA SINK
EXCHANGE ID: 806
UNPARTITIONED
803:VAGGREGATE (update serialize)
| output: partial_count(employee_no[#44])[#45]
| group by:
| cardinality=1
|
800:VAGGREGATE (merge serialize)
| group by: employee_no[#43]
| cardinality=42,546
|
797:VAGGREGATE (update serialize)
| group by: employee_no[#42]
| cardinality=85,092
|
794:VHASH JOIN
| join op: LEFT SEMI JOIN(COLOCATE[])[]
| equal join conjunct: employee_no[#40] = employee_no[#20]
| runtime filters: RF000[in_or_bloom] <- employee_no[#20](21273/32768/1048576)
| cardinality=170,185
| vec output tuple id: 6
| vIntermediate tuple ids: 5
| hash output slot ids: 40
|
|----791:VAGGREGATE (update finalize)
| | group by: employee_no[#19]
| | cardinality=21,273
| |
| 781:VOlapScanNode
| TABLE: default_cluster:retail_dim.dim_ehr_org_staff_position_all_1229(dim_ehr_org_staff_position_all_1229), PREAGGREGATION: OFF. Reason: __DORIS_DELETE_SIGN__ is used as conjuncts.
| PREDICATES: office_start_date[#3] = '2023-12-01' AND __DORIS_DELETE_SIGN__[#17] = 0
| partitions=1/1 (dim_ehr_org_staff_position_all_1229), tablets=1/1, tabletList=4534320
| cardinality=170185, avgRowSize=121.58043, numNodes=3
| pushAggOp=NONE
| projections: employee_no[#0]
| project output tuple id: 1
|
774:VOlapScanNode
TABLE: default_cluster:retail_dim.dim_ehr_org_staff_position_all_1229(dim_ehr_org_staff_position_all_1229), PREAGGREGATION: OFF. Reason: __DORIS_DELETE_SIGN__ is used as conjuncts.
PREDICATES: __DORIS_DELETE_SIGN__[#38] = 0
runtime filters: RF000[in_or_bloom] -> employee_no[#21]
partitions=1/1 (dim_ehr_org_staff_position_all_1229), tablets=1/1, tabletList=4534320
cardinality=170185, avgRowSize=121.58043, numNodes=3
pushAggOp=NONE
projections: employee_no[#21]
project output tuple id: 4
### What You Expected?
期望得到正确的结果:
整个sql查询出来的结果应是2012条
### How to Reproduce?
_No response_
### Anything Else?
_No response_
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] where条件带in的子查询过滤出现少数据问题 [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29443:
URL: https://github.com/apache/doris/issues/29443#issuecomment-3287227898
We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] where条件带in的子查询过滤出现少数据问题 [doris]
Posted by "morningman (via GitHub)" <gi...@apache.org>.
morningman closed issue #29443: [Bug] where条件带in的子查询过滤出现少数据问题
URL: https://github.com/apache/doris/issues/29443
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org