doris
doris copied to clipboard
Published
20 hours ago •
apache
apache
[Bug] const fold not work for current_date when enable_fold_constant_by_be=false
Search before asking
- [X] I had searched in the issues and found no similar issues.
Version
1.1
What's Wrong?
const fold not work for current_date() when set enable_fold_constant_by_be=false
What You Expected?
current_date() get current date succeed both enable_fold_constant_by_be=false and enable_fold_constant_by_be=true
like below:
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 4> date_format(`date_time`, '%Y%m%d') | <slot 5> sum(`cost`) |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: <slot 4> date_format(`date_time`, '%Y%m%d') |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum(<slot 5> sum(`cost`)) |
| | group by: <slot 4> date_format(`date_time`, '%Y%m%d') |
| | cardinality=-1 |
| | |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: <slot 4> date_format(`date_time`, '%Y%m%d') |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(`cost`) |
| | group by: date_format(`date_time`, '%Y%m%d') |
| | cardinality=-1 |
| | |
| 0:OlapScanNode |
| TABLE: hour_jb_ad_effects(hour_jb_ad_effects_index1), PREAGGREGATION: ON |
**| PREDICATES: `business_type` = 256, `delivery_system_type` = 8, `date_time` >= '2022-06-12 00:00:00', `date_time` < '2022-08-12 00:00:00' |**
| partitions=3/18, tablets=48/48, tabletList=38746842,38746846,38746850 ... |
| cardinality=43537, avgRowSize=51.52066, numNodes=53 |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
How to Reproduce?
1, create table like below:
CREATE TABLE `hour_jb_ad_effects` (
`pin_id` bigint(20) NOT NULL COMMENT "",
`date_time` datetime NOT NULL COMMENT "",
`date` date NOT NULL COMMENT "",
`ad_plan_id` bigint(20) NOT NULL COMMENT "",
`ad_group_id` bigint(20) NOT NULL COMMENT "",
`material_id` bigint(20) NOT NULL COMMENT "",
`area_id` bigint(20) NOT NULL COMMENT "",
`business_type` bigint(20) NOT NULL COMMENT "",
`campaign_type` bigint(20) NOT NULL COMMENT "",
`delivery_system_type` bigint(20) NOT NULL COMMENT "",
`pos_package_id` bigint(20) NULL COMMENT "",
`dmp_id` bigint(20) NULL COMMENT "",
`dmp_crowd_ids` varchar(513) NULL COMMENT "",
`ad_billing_type` bigint(20) NULL COMMENT "",
`ad_traffic_group` int(11) NULL COMMENT "",
`mobile_type` int(11) NULL COMMENT "",
`loc` int(11) NULL COMMENT "",
`pos_id` int(11) NULL COMMENT "",
`recommend_promote_rank` int(11) NULL DEFAULT "0" COMMENT "",
`auto_bid_exp_tag` int(11) NULL DEFAULT "0" COMMENT "",
`automated_bidding_type` int(11) NULL DEFAULT "0" COMMENT "",
`ad_marketing_target` int(11) NULL DEFAULT "0" COMMENT "",
`auto_bid_target` int(11) NULL DEFAULT "0" COMMENT "",
`discover_flag` int(11) NULL DEFAULT "0" COMMENT "",
`medium_traffic_package_id` bigint(20) NULL COMMENT "",
`display_scene` bigint(20) NULL COMMENT "",
`impressions` bigint(20) SUM NULL DEFAULT "0" COMMENT "",
`clicks` bigint(20) SUM NULL DEFAULT "0" COMMENT "",
`cost` bigint(20) SUM NULL DEFAULT "0" COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(`pin_id`, `date_time`, `date`, `ad_plan_id`, `ad_group_id`, `material_id`, `area_id`, `business_type`, `campaign_type`, `delivery_system_type`, `pos_package_id`, `dmp_id`, `dmp_crowd_ids`, `ad_billing_type`, `ad_traffic_group`, `mobile_type`, `loc`, `pos_id`, `recommend_promote_rank`, `auto_bid_exp_tag`, `automated_bidding_type`, `ad_marketing_target`, `auto_bid_target`, `discover_flag`, `medium_traffic_package_id`, `display_scene`)
COMMENT "OLAP"
PARTITION BY RANGE(`date_time`)
(PARTITION p202108 VALUES [('0000-01-01 00:00:00'), ('2021-09-01 00:00:00')),
PARTITION p202109 VALUES [('2021-09-01 00:00:00'), ('2021-10-01 00:00:00')),
PARTITION p202110 VALUES [('2021-10-01 00:00:00'), ('2021-11-01 00:00:00')),
PARTITION p202111 VALUES [('2021-11-01 00:00:00'), ('2021-12-01 00:00:00')),
PARTITION p202112 VALUES [('2021-12-01 00:00:00'), ('2022-01-01 00:00:00')),
PARTITION p202201 VALUES [('2022-01-01 00:00:00'), ('2022-02-01 00:00:00')),
PARTITION p202202 VALUES [('2022-02-01 00:00:00'), ('2022-03-01 00:00:00')),
PARTITION p202203 VALUES [('2022-03-01 00:00:00'), ('2022-04-01 00:00:00')),
PARTITION p202204 VALUES [('2022-04-01 00:00:00'), ('2022-05-01 00:00:00')),
PARTITION p202205 VALUES [('2022-05-01 00:00:00'), ('2022-06-01 00:00:00')),
PARTITION p202206 VALUES [('2022-06-01 00:00:00'), ('2022-07-01 00:00:00')),
PARTITION p202207 VALUES [('2022-07-01 00:00:00'), ('2022-08-01 00:00:00')),
PARTITION p202208 VALUES [('2022-08-01 00:00:00'), ('2022-09-01 00:00:00')),
PARTITION p202209 VALUES [('2022-09-01 00:00:00'), ('2022-10-01 00:00:00')),
PARTITION p202210 VALUES [('2022-10-01 00:00:00'), ('2022-11-01 00:00:00')),
PARTITION p202211 VALUES [('2022-11-01 00:00:00'), ('2022-12-01 00:00:00')),
PARTITION p202212 VALUES [('2022-12-01 00:00:00'), ('2023-01-01 00:00:00')),
PARTITION p202301 VALUES [('2023-01-01 00:00:00'), ('2023-02-01 00:00:00')))
DISTRIBUTED BY HASH(`pin_id`) BUCKETS 16
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "5",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "16",
"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.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2"
)
2, set enable_fold_constant_by_be=false; execute sql:
explain SELECT
DATE_FORMAT(`date_time`, '%Y%m%d') `date_time_granularity`,
SUM(`cost`) `cost_SUM`
FROM
jingba.hour_jb_ad_effects
WHERE
`business_type` = 256
AND `delivery_system_type` = 8
AND `date_time` >= date_sub(CURRENT_DATE(), INTERVAL 60 DAY)
AND `date_time` < date_add(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY
DATE_FORMAT(`date_time`, '%Y%m%d')
get result:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 4> date_format(`date_time`, '%Y%m%d') | <slot 5> sum(`cost`) |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: <slot 4> date_format(`date_time`, '%Y%m%d') |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum(<slot 5> sum(`cost`)) |
| | group by: <slot 4> date_format(`date_time`, '%Y%m%d') |
| | cardinality=-1 |
| | |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: <slot 4> date_format(`date_time`, '%Y%m%d') |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(`cost`) |
| | group by: date_format(`date_time`, '%Y%m%d') |
| | cardinality=-1 |
| | |
| 0:OlapScanNode |
| TABLE: hour_jb_ad_effects(hour_jb_ad_effects_index1), PREAGGREGATION: ON |
| PREDICATES: `business_type` = 256, `delivery_system_type` = 8, `date_time` >= date_sub(current_date(), INTERVAL 60 DAY), `date_time` < date_add(current_date(), INTERVAL 1 DAY) |
| partitions=12/18, tablets=192/192, tabletList=38746842,38746846,38746850 ... |
| cardinality=117769, avgRowSize=50.97513, numNodes=60 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Anything Else?
No response
Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
Code of Conduct
- [X] I agree to follow this project's Code of Conduct
