版本：2.1.4
问题描述：
现有两个sql，
--sql1:可以命中分区
explain
select * from dwd.xdqc_dialog where begin_time
between STR_TO_DATE('2024-08-14 00:00:00','%Y-%m-%d') and STR_TO_DATE('2024-08-28 23:59:59','%Y-%m-%d')
--sql2:不可以命中分区
explain
select * from dwd.xdqc_dialog
WHERE begin_time BETWEEN str_to_date('2024-08-14 00:00:00','%Y-%m-%d %H:%i:%s')
AND str_to_date('2024-08-28 23:59:59','%Y-%m-%d %H:%i:%s')
建表语句：
CREATE TABLE dwd.xdqc_dialog
(
`begin_time` DATETIME(3),
`platform` VARCHAR(512),
`seller_nick` VARCHAR(512),
`snick` VARCHAR(512),
`cnick` VARCHAR(512),
`_id` VARCHAR(512),
`room_id` VARCHAR(512),
`group` VARCHAR(512),
`channel` VARCHAR(512),
`real_buyer_nick` VARCHAR(512),
`open_uid` VARCHAR(512),
`date` INT,
`end_time` DATETIME(3),
`is_after_sale` TINYINT,
`is_inside` TINYINT,
`employee_name` VARCHAR(512),
`s_emotion_type` ARRAY< SMALLINT >,
`s_emotion_rule_id` ARRAY< VARCHAR (512)>,
`s_emotion_score` ARRAY< INT >,
`s_emotion_count` ARRAY< INT >,
`c_emotion_type` ARRAY< SMALLINT >,
`c_emotion_rule_id` ARRAY< VARCHAR (512)>,
`c_emotion_score` ARRAY< INT >,
`c_emotion_count` ARRAY< INT >,
`emotions` ARRAY< VARCHAR (512)>,
`abnormals_type` ARRAY< SMALLINT >,
`abnormals_rule_id` ARRAY< VARCHAR (512)>,
`abnormals_score` ARRAY< INT >,
`abnormals_count` ARRAY< INT >,
`excellents_type` ARRAY< SMALLINT >,
`excellents_rule_id` ARRAY< VARCHAR (512)>,
`excellents_score` ARRAY< INT >,
`excellents_count` ARRAY< INT >,
`qc_word_source` ARRAY< TINYINT >,
`qc_word_word` ARRAY< VARCHAR (1024)>,
`qc_word_count` ARRAY< INT >,
`qc_word_is_robot` ARRAY< INT >,
`qid` ARRAY< BIGINT >,
`service_evaluations_eval_code` ARRAY< BIGINT >,
`service_evaluations_open_uid` ARRAY< VARCHAR (512)>,
`service_evaluations_eval_time` ARRAY< VARCHAR (512)>,
`service_evaluations_send_time` ARRAY< VARCHAR (512)>,
`service_evaluations_source` ARRAY< BIGINT >,
`service_evaluations_message_id` ARRAY< VARCHAR (512)>,
`service_evaluations_desc` ARRAY< VARCHAR (8192)>,
`mark` VARCHAR(65533),
`mark_time` DATETIME(3),
`message_marks_id` ARRAY< VARCHAR (512)>,
`message_marks_mark` ARRAY< VARCHAR (8192)>,
`mark_judge` INT,
`mark_score` INT,
`mark_score_add` INT,
`mark_ids` ARRAY< VARCHAR (512)>,
`last_mark_id` VARCHAR(512),
`human_check` TINYINT,
`tag_score_stats_id` ARRAY< VARCHAR (512)>,
`tag_score_stats_score` ARRAY< INT >,
`tag_score_stats_count` ARRAY< INT >,
`tag_score_stats_md` ARRAY< TINYINT >,
`tag_score_stats_mm` ARRAY< TINYINT >,
`tag_score_add_stats_id` ARRAY< VARCHAR (512)>,
`tag_score_add_stats_score` ARRAY< INT >,
`tag_score_add_stats_count` ARRAY< INT >,
`tag_score_add_stats_md` ARRAY< TINYINT >,
`tag_score_add_stats_mm` ARRAY< TINYINT >,
`rule_stats_id` ARRAY< VARCHAR (512)>,
`rule_stats_score` ARRAY< INT >,
`rule_stats_count` ARRAY< INT >,
`rule_add_stats_id` ARRAY< VARCHAR (512)>,
`rule_add_stats_score` ARRAY< INT >,
`rule_add_stats_count` ARRAY< INT >,
`xrule_stats_id` ARRAY< VARCHAR (512)>,
`xrule_stats_score` ARRAY< INT >,
`xrule_stats_count` ARRAY< INT >,
`top_xrules_id` ARRAY< VARCHAR (512)>,
`top_xrules_score` ARRAY< INT >,
`top_xrules_count` ARRAY< INT >,
`score` INT,
`score_add` INT,
`question_count` INT,
`answer_count` INT,
`first_answer_time` DATETIME(3),
`qa_time_sum` INT,
`qa_round_sum` INT,
`focus_goods_id` VARCHAR(512),
`is_remind` TINYINT,
`task_list_id` VARCHAR(512),
`read_mark` ARRAY< VARCHAR(8192)>,
`last_msg_id` VARCHAR(512),
`consulte_transfor_v2` INT,
`order_info_id` ARRAY< VARCHAR (512)>,
`order_info_status` ARRAY< VARCHAR (512)>,
`order_info_payment` ARRAY< FLOAT >,
`order_info_time` ARRAY< BIGINT >,
`order_info_history_status` ARRAY<ARRAY< VARCHAR (512)>>,
`order_info_history_time` ARRAY<ARRAY< BIGINT >>,
`plat_goods_ids` ARRAY< VARCHAR (512)>,
`intel_score` INT,
`remind_ntype` VARCHAR(512),
`first_follow_up_time` DATETIME(3),
`is_follow_up_remind` TINYINT,
`emotion_detect_mode` INT,
`has_withdraw_robot_msg` TINYINT,
`is_order_matched` TINYINT,
`suspected_positive_emotion` TINYINT,
`suspected_problem` TINYINT,
`suspected_excellent` TINYINT,
`has_after` TINYINT,
`cnick_customize_rule` ARRAY< VARCHAR (512)>,
`update_time` DATETIME(3),
`wx_rule_stats_id` ARRAY< VARCHAR (512)>,
`wx_rule_stats_score` ARRAY< INT >,
`wx_rule_stats_count` ARRAY< INT >,
`wx_rule_add_stats_id` ARRAY< VARCHAR (512)>,
`wx_rule_add_stats_score` ARRAY< INT >,
`wx_rule_add_stats_count` ARRAY< INT >,
`remark` VARCHAR(65533),
`qc_task_flag` INT,
`do_rule_process` INT,
`qc_norm_id` VARCHAR (512),
`desc` VARCHAR (65533)
)
UNIQUE KEY(`begin_time`,`platform`, `seller_nick`, `snick`, `cnick`, `_id`)
PARTITION BY RANGE(`begin_time`)()
DISTRIBUTED BY HASH(`platform`, `seller_nick`,`snick`,`cnick`) BUCKETS AUTO
PROPERTIES (
"enable_unique_key_merge_on_write" = "true",
"replication_allocation" = "tag.location.default: 3",
"dynamic_partition.enable" = "true",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "365",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"estimate_partition_size" = "5G"
);
explain 执行结果：
--sql1:
PREDICATES: (__DORIS_DELETE_SIGN__[#121] = 0) AND (begin_time[#0] >= '2024-08-14 00:00:00') AND (begin_time[#0] <= '2024-08-28 00:00:00')
partitions=15/1241 (p20240814,p20240815,p20240816,p20240817,p20240818,p20240819,p20240820,p20240821,p20240822,p20240823,p20240824,p20240825,p20240826,p20240827,p20240828)
tablets=30/30, tabletList=10920356,10920360,13224403 ...
--sql2:
PREDICATES: (__DORIS_DELETE_SIGN__[#121] = 0) AND (CAST(begin_time[#0] AS DATETIMEV2(6)) >= '2024-08-14 00:00:00') AND (CAST(begin_time[#0] AS DATETIMEV2(6)) <= '2024-08-28 23:59:59')
partitions=1217/1241 (p20210503,p20210504,p20210505,p20210506,p20210507,p20210508,p20210509,p20210510,p20210511,p20210512,p20210513,p20210514,p20210515,p20210516,p20210517,p20210518,p20210519,p20210520,p20210521,p20210522,p20210523,p20210524,p20210525,p20210526,p20210527,p20210528,p20210529,p20210530,p20210531,p20210601,p20210602,p20210603,p20210604,p20210605,p20210606,p20210607,p20210608,p20210609,p20210610,p20210611,p20210612,p20210613,p20210614,p20210615,p20210616,p20210617,p20210618,p20210619,p20210620,p20210621,p20210622,p20210623,p20210624,p20210625,p20210626,p20210627,p20210628,p20210629,p20210630,p20210701,p20210702,p20210703,p20210704,p20210705,p20210706,p20210707,p20210708,p20210709,p20210710,p20210711,p20210712,p20210713,p20210714,p20210715,p20210716,p20210717,p20210718,p20210719,p20210720,p20210721,p20210722,p20210723,p20210724,p20210725,p20210726,p20210727,p20210728,p20210729,p20210730,p20210731,p20210801,p20210802,p20210803,p20210804,p20210805,p20210806,p20210807,p20210808,p20210809,p20210810,p20210811,p20210812,p20210813,p20210814,p20210815,p20210816,p20210817,p20210818,p20210819,p20210820,p20210821,p20210822,p20210823,p20210824,p20210825,p20210826,p20210827,p20210828,p20210829,p20210830,p20210831,p20210901,p20210902,p20210903,p20210904,p20210905,p20210906,p20210907,p20210908,p20210909,p20210910,p20210911,p20210912,p20210913,p20210914,p20210915,p20210916,p20210917,p20210918,p20210919,p20210920,p20210921,p20210922,p20210923,p20210924,p20210925,p20210926,p20210927,p20210928,p20210929,p20210930,p20211001,p20211002,p20211003,p20211004,p20211005,p20211006,p20211007,p20211008,p20211009,p20211010,p20211011,p20211012,p20211013,p20211014,p20211015,p20211016,p20211017,p20211018,p20211019,p20211020,p20211021,p20211022,p20211023,p20211024,p20211025,p20211026,p20211027,p20211028,p20211029,p20211030,p20211031,p20211101,p20211102,p20211103,p20211104,p20211105,p20211106,p20211107,p20211108,p20211109,p20211110,p20211111,p20211112,p20211113,p20211114,p20211115,p20211116,p20211117,p20211118,p20211119,p20211120,p20211121,p20211122,p20211123,p20211124,p20211125,p20211126,p20211127,p20211128,p20211129,p20211130,p20211201,p20211202,p20211203,p20211204,p20211205,p20211206,p20211207,p20211208,p20211209,p20211210,p20211211,p20211212,p20211213,p20211214,p20211215,p20211216,p20211217,p20211218,p20211219,p20211220,p20211221,p20211222,p20211223,p20211224,p20211225,p20211226,p20211227,p20211228,p20211229,p20211230,p20211231,p20220101,p20220102,p20220103,p20220104,p20220105,p20220106,p20220107,p20220108,p20220109,p20220110,p20220111,p20220112,p20220113,p20220114,p20220115,p20220116,p20220117,p20220118,p20220119,p20220120,p20220121,p20220122,p20220123,p20220124,p20220125,p20220126,p20220127,p20220128,p20220129,p20220130,p20220131,p20220201,p20220202,p20220203,p20220204,p20220205,p20220206,p20220207,p20220208,p20220209,p20220210,p20220211,p20220212,p20220213,p20220214,p20220215,p20220216,p20220217,p20220218,p20220219,p20220220,p20220221,p20220222,p20220223,p20220224,p20220225,p20220226,p20220227,p20220228,p20220301,p20220302,p20220303,p20220304,p20220305,p20220306,p20220307,p20220308,p20220309,p20220310,p20220311,p20220312,p20220313,p20220314,p20220315,p20220316,p20220317,p20220318,p20220319,p20220320,p20220321,p20220322,p20220323,p20220324,p20220325,p20220326,p20220327,p20220328,p20220329,p20220330,p20220331,p20220401,p20220402,p20220403,p20220404,p20220405,p20220406,p20220407,p20220408,p20220409,p20220410,p20220411,p20220412,p20220413,p20220414,p20220415,p20220416,p20220417,p20220418,p20220419,p20220420,p20220421,p20220422,p20220423,p20220424,p20220425,p20220426,p20220427,p20220428,p20220429,p20220430,p20220501,p20220502,p20220503,p20220504,p20220505,p20220506,p20220507,p20220508,p20220509,p20220510,p20220511,p20220512,p20220513,p20220514,p20220515,p20220516,p20220517,p20220518,p20220519,p20220520,p20220521,p20220522,p20220523,p20220524,p20220525,p20220526,p20220527,p20220528,p20220529,p20220530,p20220531,p20220601,p20220602,p20220603,p20220604,p20220605,p20220606,p20220607,p20220608,p20220609,p20220610,p20220611,p20220612,p20220613,p20220614,p20220615,p20220616,p20220617,p20220618,p20220619,p20220620,p20220621,p20220622,p20220623,p20220624,p20220625,p20220626,p20220627,p20220628,p20220629,p20220630,p20220701,p20220702,p20220703,p20220704,p20220705,p20220706,p20220707,p20220708,p20220709,p20220710,p20220711,p20220712,p20220713,p20220714,p20220715,p20220716,p20220717,p20220718,p20220719,p20220720,p20220721,p20220722,p20220723,p20220724,p20220725,p20220726,p20220727,p20220728,p20220729,p20220730,p20220731,p20220801,p20220802,p20220803,p20220804,p20220805,p20220806,p20220807,p20220808,p20220809,p20220810,p20220811,p20220812,p20220813,p20220814,p20220815,p20220816,p20220817,p20220818,p20220819,p20220820,p20220821,p20220822,p20220823,p20220824,p20220825,p20220826,p20220827,p20220828,p20220829,p20220830,p20220831,p20220901,p20220902,p20220903,p20220904,p20220905,p20220906,p20220907,p20220908,p20220909,p20220910,p20220911,p20220912,p20220913,p20220914,p20220915,p20220916,p20220917,p20220918,p20220919,p20220920,p20220921,p20220922,p20220923,p20220924,p20220925,p20220926,p20220927,p20220928,p20220929,p20220930,p20221001,p20221002,p20221003,p20221004,p20221005,p20221006,p20221007,p20221008,p20221009,p20221010,p20221011,p20221012,p20221013,p20221014,p20221015,p20221016,p20221017,p20221018,p20221019,p20221020,p20221021,p20221022,p20221023,p20221024,p20221025,p20221026,p20221027,p20221028,p20221029,p20221030,p20221031,p20221101,p20221102,p20221103,p20221104,p20221105,p20221106,p20221107,p20221108,p20221109,p20221110,p20221111,p20221112,p20221113,p20221114,p20221115,p20221116,p20221117,p20221118,p20221119,p20221120,p20221121,p20221122,p20221123,p20221124,p20221125,p20221126,p20221127,p20221128,p20221129,p20221130,p20221201,p20221202,p20221203,p20221204,p20221205,p20221206,p20221207,p20221208,p20221209,p20221210,p20221211,p20221212,p20221213,p20221214,p20221215,p20221216,p20221217,p20221218,p20221219,p20221220,p20221221,p20221222,p20221223,p20221224,p20221225,p20221226,p20221227,p20221228,p20221229,p20221230,p20221231,p20230101,p20230102,p20230103,p20230104,p20230105,p20230106,p20230107,p20230108,p20230109,p20230110,p20230111,p20230112,p20230113,p20230114,p20230115,p20230116,p20230117,p20230118,p20230119,p20230120,p20230121,p20230122,p20230123,p20230124,p20230125,p20230126,p20230127,p20230128,p20230129,p20230130,p20230131,p20230201,p20230202,p20230203,p20230204,p20230205,p20230206,p20230207,p20230208,p20230209,p20230210,p20230211,p20230212,p20230213,p20230214,p20230215,p20230216,p20230217,p20230218,p20230219,p20230220,p20230221,p20230222,p20230223,p20230224,p20230225,p20230226,p20230227,p20230228,p20230301,p20230302,p20230303,p20230304,p20230305,p20230306,p20230307,p20230308,p20230309,p20230310,p20230311,p20230312,p20230313,p20230314,p20230315,p20230316,p20230317,p20230318,p20230319,p20230320,p20230321,p20230322,p20230323,p20230324,p20230325,p20230326,p20230327,p20230328,p20230329,p20230330,p20230331,p20230401,p20230402,p20230403,p20230404,p20230405,p20230406,p20230407,p20230408,p20230409,p20230410,p20230411,p20230412,p20230413,p20230414,p20230415,p20230416,p20230417,p20230418,p20230419,p20230420,p20230421,p20230422,p20230423,p20230424,p20230425,p20230426,p20230427,p20230428,p20230429,p20230430,p20230501,p20230502,p20230503,p20230504,p20230505,p20230506,p20230507,p20230508,p20230509,p20230510,p20230511,p20230512,p20230513,p20230514,p20230515,p20230516,p20230517,p20230518,p20230519,p20230520,p20230521,p20230522,p20230523,p20230524,p20230525,p20230526,p20230527,p20230528,p20230529,p20230530,p20230531,p20230601,p20230602,p20230603,p20230604,p20230605,p20230606,p20230607,p20230608,p20230609,p20230610,p20230611,p20230612,p20230613,p20230614,p20230615,p20230616,p20230617,p20230618,p20230619,p20230620,p20230621,p20230622,p20230623,p20230624,p20230625,p20230626,p20230627,p20230628,p20230629,p20230630,p20230701,p20230702,p20230703,p20230704,p20230705,p20230706,p20230707,p20230708,p20230709,p20230710,p20230711,p20230712,p20230713,p20230714,p20230715,p20230716,p20230717,p20230718,p20230719,p20230720,p20230721,p20230722,p20230723,p20230724,p20230725,p20230726,p20230727,p20230728,p20230729,p20230730,p20230731,p20230801,p20230802,p20230803,p20230804,p20230805,p20230806,p20230807,p20230808,p20230809,p20230810,p20230811,p20230812,p20230813,p20230814,p20230815,p20230816,p20230817,p20230818,p20230819,p20230820,p20230821,p20230822,p20230823,p20230824,p20230825,p20230826,p20230827,p20230828,p20230829,p20230830,p20230831,p20230901,p20230902,p20230903,p20230904,p20230905,p20230906,p20230907,p20230908,p20230909,p20230910,p20230911,p20230912,p20230913,p20230914,p20230915,p20230916,p20230917,p20230918,p20230919,p20230920,p20230921,p20230922,p20230923,p20230924,p20230925,p20230926,p20230927,p20230928,p20230929,p20230930,p20231001,p20231002,p20231003,p20231004,p20231005,p20231006,p20231007,p20231008,p20231009,p20231010,p20231011,p20231012,p20231013,p20231014,p20231015,p20231016,p20231017,p20231018,p20231019,p20231020,p20231021,p20231022,p20231023,p20231024,p20231025,p20231026,p20231027,p20231028,p20231029,p20231030,p20231031,p20231101,p20231102,p20231103,p20231104,p20231105,p20231106,p20231107,p20231108,p20231109,p20231110,p20231111,p20231112,p20231113,p20231114,p20231115,p20231116,p20231117,p20231118,p20231119,p20231120,p20231121,p20231122,p20231123,p20231124,p20231125,p20231126,p20231127,p20231128,p20231129,p20231130,p20231201,p20231202,p20231203,p20231204,p20231205,p20231206,p20231207,p20231208,p20231209,p20231210,p20231211,p20231212,p20231213,p20231214,p20231215,p20231216,p20231217,p20231218,p20231219,p20231220,p20231221,p20231222,p20231223,p20231224,p20231225,p20231226,p20231227,p20231228,p20231229,p20231230,p20231231,p20240101,p20240102,p20240103,p20240104,p20240105,p20240106,p20240107,p20240108,p20240109,p20240110,p20240111,p20240112,p20240113,p20240116,p20240117,p20240118,p20240119,p20240120,p20240121,p20240122,p20240123,p20240124,p20240125,p20240126,p20240127,p20240128,p20240129,p20240130,p20240131,p20240201,p20240202,p20240203,p20240204,p20240205,p20240206,p20240207,p20240208,p20240209,p20240210,p20240211,p20240212,p20240213,p20240214,p20240215,p20240216,p20240217,p20240218,p20240219,p20240220,p20240221,p20240222,p20240223,p20240224,p20240225,p20240226,p20240227,p20240228,p20240229,p20240301,p20240302,p20240303,p20240304,p20240305,p20240306,p20240307,p20240308,p20240309,p20240310,p20240311,p20240312,p20240313,p20240315,p20240316,p20240317,p20240318,p20240319,p20240320,p20240321,p20240322,p20240323,p20240324,p20240325,p20240326,p20240327,p20240328,p20240329,p20240331,p20240401,p20240402,p20240403,p20240405,p20240406,p20240407,p20240408,p20240409,p20240410,p20240411,p20240412,p20240413,p20240414,p20240415,p20240416,p20240417,p20240418,p20240419,p20240420,p20210502,p20210501,p20240729,p20240730,p20240731,p20240801,p20240802,p20240803,p20240114,p20240115,p20240314,p20240804,p20240330,p20240404,p20240421,p20240422,p20240423,p20240424,p20240425,p20240426,p20240427,p20240428,p20240429,p20240430,p20240501,p20240502,p20240503,p20240504,p20240505,p20240506,p20240507,p20240508,p20240509,p20240510,p20240511,p20240512,p20240513,p20240514,p20240515,p20240516,p20240517,p20240518,p20240519,p20240520,p20240521,p20240522,p20240523,p20240524,p20240525,p20240526,p20240527,p20240528,p20240529,p20240530,p20240531,p20240601,p20240602,p20240603,p20240604,p20240605,p20240606,p20240607,p20240608,p20240609,p20240610,p20240611,p20240612,p20240613,p20240614,p20240615,p20240616,p20240617,p20240618,p20240619,p20240620,p20240621,p20240622,p20240623,p20240624,p20240625,p20240626,p20240627,p20240628,p20240629,p20240630,p20240701,p20240702,p20240703,p20240704,p20240705,p20240706,p20240707,p20240708,p20240709,p20240710,p20240711,p20240712,p20240713,p20240714,p20240715,p20240716,p20240717,p20240718,p20240719,p20240720,p20240721,p20240722,p20240723,p20240724,p20240725,p20240726,p20240727,p20240728,p20240805,p20240806,p20240807,p20240808,p20240809,p20240810,p20240811,p20240812,p20240813,p20240814,p20240815,p20240816,p20240817,p20240818,p20240819,p20240820,p20240821,p20240822,p20240823,p20240824,p20240825,p20240826,p20240827,p20240828,p20240829)
tablets=2433/2433, tabletList=5005389,5005393,8364157 ...
描述结果：PREDICATES: (__DORIS_DELETE_SIGN__[#121] = 0) AND (CAST(begin_time[#0] AS DATETIMEV2(6)) >= '2024-08-14 00:00:00') AND (CAST(begin_time[#0] AS DATETIMEV2(6)) <= '2024-08-28 23:59:59') sql2的语法解析会把分区键 begin_time 转化为 CAST(begin_time[#0] AS DATETIMEV2(6)) 然后匹配 where 条件。
