yonchicy opened a new issue, #65606: URL: https://github.com/apache/doris/issues/65606
### 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 - commit id : 61c910f2cb - commit msg: [fix](fe) Fix stream scan normalization and snapshot/reset pruning (#65468) ### What's Wrong? A query returns an empty result on a table using `AUTO PARTITION BY RANGE` with a partition expression: date_trunc(`TIME_STAMP`, 'month') The inserted row should satisfy the query predicate, but it is not returned. The table is partitioned by the truncated month value, while the query predicate is on the original TIME_STAMP column. It looks like partition pruning may incorrectly prune the partition that actually contains the row. How to Reproduce? ```sql create database db1; use db1; CREATE TABLE `date_table` ( `TIME_STAMP` datev2 NOT NULL ) ENGINE=OLAP DUPLICATE KEY(`TIME_STAMP`) AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month')) ( PARTITION p_2000 VALUES [('2000-01-01'), ('2001-01-05')), PARTITION p_2001 VALUES [('2001-01-06'), ('2001-01-08')) ) DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1" ); insert into `date_table` values('2001-01-07'); select * from `date_table` where `TIME_STAMP` > '2001-01-06'; ``` The query returns an empty result. ### What You Expected? The query should return: 2001-01-07 ### How to Reproduce? ```sql create database db1; use db1; CREATE TABLE `date_table` ( `TIME_STAMP` datev2 NOT NULL ) ENGINE=OLAP DUPLICATE KEY(`TIME_STAMP`) AUTO PARTITION BY RANGE (date_trunc(`TIME_STAMP`, 'month')) ( PARTITION p_2000 VALUES [('2000-01-01'), ('2001-01-05')), PARTITION p_2001 VALUES [('2001-01-06'), ('2001-01-08')) ) DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1" ); insert into `date_table` values('2001-01-07'); select * from `date_table` where `TIME_STAMP` > '2001-01-06'; ``` ### Anything Else? ### Because: date_trunc('2001-01-07', 'month') = '2001-01-01' So the row should be routed to partition p_2000, whose range is: [('2000-01-01'), ('2001-01-05')) Although the partition key value is 2001-01-01, the original column value is still 2001-01-07, so the predicate: TIME_STAMP > '2001-01-06' should match this row. This seems related to partition pruning for expression-based RANGE partitions. The planner may be comparing the predicate on the original column TIME_STAMP directly with the partition boundaries of the expression date_trunc(TIME_STAMP, 'month'), causing the partition containing the matching row to be pruned incorrectly. If partition pruning is disabled or if the target partition is scanned directly, the row may be visible. This suggests the data is inserted successfully, but the scan plan selects the wrong partition set. ### Possible modification approaches: - Strengthen semantic checks: disallow manual partition specification when using auto partition, or restrict the granularity of manually defined partitions to align with date_trunc. - Or refactor the implementation of date_trunc, or even the entire partition expression, to make it more flexible. I would like to know if the community has any thoughts on this. I am willing to contribute relevant code. ### 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
