qdore commented on issue #1097: In some condition use sub-query make doris return wrong value
URL: https://github.com/apache/incubator-doris/issues/1097#issuecomment-489583459
1. explain result
(1) right result:
```
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 5> | <slot 30> | |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 20:EXCHANGE |
| tuple ids: 1 10N |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 5> |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 20 |
| UNPARTITIONED |
| |
| 9:HASH JOIN |
| | join op: LEFT OUTER JOIN (BROADCAST) |
| | hash predicates: |
| | <slot 5> = <slot 29> |
| | tuple ids: 1 10N |
| | |
| |----19:EXCHANGE |
| | tuple ids: 10 |
| | |
| 11:AGGREGATE (merge finalize) |
| | group by: <slot 5> |
| | tuple ids: 1 |
| | |
| 10:EXCHANGE |
| tuple ids: 1 |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 27> |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 19 |
| UNPARTITIONED |
| |
| 8:AGGREGATE (update finalize) |
| | output: count(<slot 28>) |
| | group by: <slot 27> |
| | tuple ids: 10 |
| | |
| 18:AGGREGATE (merge serialize) |
| | group by: <slot 27>, <slot 28> |
| | tuple ids: 9 |
| | |
| 17:EXCHANGE |
| tuple ids: 9 |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 14>, <slot 15> |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 17 |
| HASH_PARTITIONED: <slot 27> |
| |
| 7:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: <slot 14>, <slot 15> |
| | tuple ids: 9 |
| | |
| 6:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | <slot 14> = <slot 23> |
| | <slot 15> = <slot 24> |
| | tuple ids: 4 7 |
| | |
| |----16:EXCHANGE |
| | tuple ids: 7 |
| | |
| 13:AGGREGATE (merge finalize) |
| | group by: <slot 14>, <slot 15> |
| | tuple ids: 4 |
| | |
| 12:EXCHANGE |
| tuple ids: 4 |
| |
| PLAN FRAGMENT 4 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 23>, <slot 24> |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 16 |
| UNPARTITIONED |
| |
| 15:AGGREGATE (merge finalize) |
| | group by: <slot 23>, <slot 24> |
| | tuple ids: 7 |
| | |
| 14:EXCHANGE |
| tuple ids: 7 |
| |
| PLAN FRAGMENT 5 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 14 |
| HASH_PARTITIONED: <slot 23>, <slot 24> |
| |
| 5:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: `event_day`, `cuid` |
| | tuple ids: 7 |
| | |
| 4:OlapScanNode |
| TABLE: visd_dws_topic_novel_wide_table_user |
| PREAGGREGATION: ON |
| PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_new` = 1.0 |
| partitions=25/25 |
| rollup: visd_dws_topic_novel_wide_table_user |
| buckets=800/800 |
| cardinality=1060653874 |
| avgRowSize=29.075388 |
| numNodes=30 |
| tuple ids: 6 |
| |
| PLAN FRAGMENT 6 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 12 |
| HASH_PARTITIONED: <slot 14>, <slot 15> |
| |
| 3:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: `event_day`, `cuid` |
| | tuple ids: 4 |
| | |
| 2:OlapScanNode |
| TABLE: visd_dws_topic_novel_wide_table_user |
| PREAGGREGATION: ON |
| PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_read_dau` = 1.0 |
| partitions=25/25 |
| rollup: visd_dws_topic_novel_wide_table_user |
| buckets=800/800 |
| cardinality=1060653874 |
| avgRowSize=29.079033 |
| numNodes=30 |
| tuple ids: 3 |
| |
| PLAN FRAGMENT 7 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 10 |
| HASH_PARTITIONED: <slot 5> |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: `event_day` |
| | tuple ids: 1 |
| | |
| 0:OlapScanNode |
| TABLE: visd_dws_topic_novel_wide_table_user |
| PREAGGREGATION: ON |
| PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_dau` = 1.0 |
| partitions=25/25 |
| rollup: visd_dws_topic_novel_wide_table_user_no_cuid_userid_rollup |
| buckets=800/800 |
| cardinality=21101622 |
| avgRowSize=170.06363 |
| numNodes=30 |
| tuple ids: 0 |
```
(2) wrong result:
```
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:date(<slot 35>) | <slot 36> | |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 21:MERGING-EXCHANGE |
| limit: 1000 |
| tuple ids: 13 |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 5> |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 21 |
| UNPARTITIONED |
| |
| 10:TOP-N |
| | order by: <slot 35> DESC |
| | offset: 0 |
| | limit: 1000 |
| | tuple ids: 13 |
| | |
| 9:HASH JOIN |
| | join op: LEFT OUTER JOIN (BROADCAST) |
| | hash predicates: |
| | <slot 5> = <slot 29> |
| | tuple ids: 1 10N |
| | |
| |----20:EXCHANGE |
| | tuple ids: 10 |
| | |
| 12:AGGREGATE (merge finalize) |
| | group by: <slot 5> |
| | tuple ids: 1 |
| | |
| 11:EXCHANGE |
| tuple ids: 1 |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 27> |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 20 |
| UNPARTITIONED |
| |
| 8:AGGREGATE (update finalize) |
| | output: count(<slot 28>) |
| | group by: <slot 27> |
| | tuple ids: 10 |
| | |
| 19:AGGREGATE (merge serialize) |
| | group by: <slot 27>, <slot 28> |
| | tuple ids: 9 |
| | |
| 18:EXCHANGE |
| tuple ids: 9 |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 14>, <slot 15> |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 18 |
| HASH_PARTITIONED: <slot 27> |
| |
| 7:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: <slot 14>, <slot 15> |
| | tuple ids: 9 |
| | |
| 6:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | <slot 14> = <slot 23> |
| | <slot 15> = <slot 24> |
| | tuple ids: 4 7 |
| | |
| |----17:EXCHANGE |
| | tuple ids: 7 |
| | |
| 14:AGGREGATE (merge finalize) |
| | group by: <slot 14>, <slot 15> |
| | tuple ids: 4 |
| | |
| 13:EXCHANGE |
| tuple ids: 4 |
| |
| PLAN FRAGMENT 4 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 23>, <slot 24> |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 17 |
| UNPARTITIONED |
| |
| 16:AGGREGATE (merge finalize) |
| | group by: <slot 23>, <slot 24> |
| | tuple ids: 7 |
| | |
| 15:EXCHANGE |
| tuple ids: 7 |
| |
| PLAN FRAGMENT 5 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 15 |
| HASH_PARTITIONED: <slot 23>, <slot 24> |
| |
| 5:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: `event_day`, `cuid` |
| | tuple ids: 7 |
| | |
| 4:OlapScanNode |
| TABLE: visd_dws_topic_novel_wide_table_user |
| PREAGGREGATION: ON |
| PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_new` = 1.0 |
| partitions=25/25 |
| rollup: visd_dws_topic_novel_wide_table_user |
| buckets=800/800 |
| cardinality=1060653874 |
| avgRowSize=29.077042 |
| numNodes=30 |
| tuple ids: 6 |
| |
| PLAN FRAGMENT 6 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 13 |
| HASH_PARTITIONED: <slot 14>, <slot 15> |
| |
| 3:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: `event_day`, `cuid` |
| | tuple ids: 4 |
| | |
| 2:OlapScanNode |
| TABLE: visd_dws_topic_novel_wide_table_user |
| PREAGGREGATION: ON |
| PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_read_dau` = 1.0 |
| partitions=25/25 |
| rollup: visd_dws_topic_novel_wide_table_user |
| buckets=800/800 |
| cardinality=1060653874 |
| avgRowSize=29.078333 |
| numNodes=30 |
| tuple ids: 3 |
| |
| PLAN FRAGMENT 7 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 11 |
| HASH_PARTITIONED: <slot 5> |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | group by: `event_day` |
| | tuple ids: 1 |
| | |
| 0:OlapScanNode |
| TABLE: visd_dws_topic_novel_wide_table_user |
| PREAGGREGATION: ON |
| PREDICATES: `sid` IN ('all'), `value` IN ('all'), `event_day` >= str_to_date('2019-04-29 00:00:00', '%Y-%m-%d %H:%i:%s'), `event_day` <= str_to_date('2019-05-06 00:00:00', '%Y-%m-%d %H:%i:%s'), `is_dau` = 1.0 |
| partitions=25/25 |
| rollup: visd_dws_topic_novel_wide_table_user_no_cuid_userid_rollup |
| buckets=800/800 |
| cardinality=21090527 |
| avgRowSize=170.1286 |
| numNodes=30 |
| tuple ids: 0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
2. remove order by event_day is ok:
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@doris.apache.org
For additional commands, e-mail: dev-help@doris.apache.org