IanMeta opened a new issue, #29533: URL: https://github.com/apache/doris/issues/29533
### 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.0 ### What's Wrong? When querying a table with time partitions, using time format like 'YYYY-mm-dd hh:mm:ss' would scan the correct partitions in query plan, but using format like 'YYYY/mm/dd hh:mm:ss' or 'YYYY-mm-ddThh:mm:ss' would perform a whole table scan but still yield the correct results. ### What You Expected? The query plan should scan the correct partition based on the filter regardless of the format. ### How to Reproduce? 1. create the table ``` CREATE TABLE test_table ( AreaId bigint(20) NULL, BranchId bigint(20) NULL, CompanyId bigint(20) NULL, __time datetime NULL, value bigint(20) SUM NULL, ) ENGINE=OLAP AGGREGATE KEY(AreaId, BranchId, CompanyId, __time) COMMENT 'OLAP' PARTITION BY RANGE(__time) ( PARTITION p202310 VALUES [('2023-10-01 00:00:00'), ('2023-11-01 00:00:00')), PARTITION p202311 VALUES [('2023-11-01 00:00:00'), ('2023-12-01 00:00:00')), PARTITION p202312 VALUES [('2023-12-01 00:00:00'), ('2024-01-01 00:00:00')), PARTITION p202401 VALUES [('2024-01-01 00:00:00'), ('2024-02-01 00:00:00')), PARTITION p202402 VALUES [('2024-02-01 00:00:00'), ('2024-03-01 00:00:00')), PARTITION p202403 VALUES [('2024-03-01 00:00:00'), ('2024-04-01 00:00:00')), PARTITION p202404 VALUES [('2024-04-01 00:00:00'), ('2024-05-01 00:00:00')) ) DISTRIBUTED BY HASH(AreaId, BranchId, CompanyId) BUCKETS 32 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "is_being_synced" = "false", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "MONTH", "dynamic_partition.time_zone" = "Etc/UTC", "dynamic_partition.start" = "-2147483648", "dynamic_partition.end" = "3", "dynamic_partition.prefix" = "p", "dynamic_partition.replication_allocation" = "tag.location.default: 1", "dynamic_partition.buckets" = "32", "dynamic_partition.create_history_partition" = "true", "dynamic_partition.history_partition_num" = "450", "dynamic_partition.hot_partition_num" = "0", "dynamic_partition.reserved_history_periods" = "NULL", "dynamic_partition.storage_policy" = "", "dynamic_partition.storage_medium" = "HDD", "dynamic_partition.start_day_of_month" = "1", "storage_format" = "V2", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" ); ``` 2. Insert some data ``` INSERT INTO test_table (AreaId, BranchId, CompanyId, __time, value) VALUES (1, 1, 1, '2023-10-15 10:00:00', 1000), (2, 2, 2, '2023-10-15 12:00:00', 2000), (3, 3, 3, '2023-10-15 14:00:00', 3000); INSERT INTO test_table (AreaId, BranchId, CompanyId, __time, value) VALUES (1, 1, 1, '2023-11-15 10:00:00', 1500), (2, 2, 2, '2023-11-15 12:00:00', 2500), (3, 3, 3, '2023-11-15 14:00:00', 3500); INSERT INTO test_table (AreaId, BranchId, CompanyId, __time, value) VALUES (1, 1, 1, '2023-12-15 10:00:00', 1200), (2, 2, 2, '2023-12-15 12:00:00', 2200), (3, 3, 3, '2023-12-15 14:00:00', 3200); INSERT INTO test_table (AreaId, BranchId, CompanyId, __time, value) VALUES (1, 1, 1, '2024-01-15 10:00:00', 1800), (2, 2, 2, '2024-01-15 12:00:00', 2800), (3, 3, 3, '2024-01-15 14:00:00', 3800); INSERT INTO test_table (AreaId, BranchId, CompanyId, __time, value) VALUES (1, 1, 1, '2024-02-15 10:00:00', 1400), (2, 2, 2, '2024-02-15 12:00:00', 2400), (3, 3, 3, '2024-02-15 14:00:00', 3400); INSERT INTO test_table (AreaId, BranchId, CompanyId, __time, value) VALUES (1, 1, 1, '2024-03-15 10:00:00', 1600), (2, 2, 2, '2024-03-15 12:00:00', 2600), (3, 3, 3, '2024-03-15 14:00:00', 3600); INSERT INTO test_table (AreaId, BranchId, CompanyId, __time, value) VALUES (1, 1, 1, '2024-04-15 10:00:00', 1300), (2, 2, 2, '2024-04-15 12:00:00', 2300), (3, 3, 3, '2024-04-15 14:00:00', 3300); ``` 3. The query below partitions correctly: ``` explain select * from test_table where __time between '2024-03-01 00:00:00' and '2024-03-30 23:59:59'; ``` ``` partitions=1/454, tablets=32/32, tabletList=20735387,20735389,20735391 ... ``` 4. The query below does not: ``` explain select * from test_table where __time between '2024-03-01T00:00:00' and '2024-03-30T23:59:59'; ``` ``` partitions=7/454, tablets=224/224, tabletList=20735451,20735453,20735455 ... ``` 5. same for the queries below: ``` explain select * from test_table where __time between convert_tz('2024-03-01 00:00:00', '+08:00', 'America/Los_Angeles') and convert_tz('2024-03-01 00:00:00', '+08:00', 'America/Los_Angeles'); explain select * from test_table where __time between CAST('2024-03-01T00:00:00' as datetime) and CAST('2024-03-30T23:59:59' as datetime); ``` ### 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](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-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
