[Bug] Incorrect partition pruning for RANGE partition on date_trunc expression with manual partitions returns empty result

[yonchicy]

Search before asking

• I had searched in the issues and found no similar issues.

Version

• commit id : 61c910f
• commit msg: fix Fix stream scan normalization and snapshot/reset pruning ([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?

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?

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?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[zclllyybb]

Breakwater-GitHub-Analysis-Slot: slot_87297ff7f6de
This content is generated by AI for reference only.

Initial analysis: this looks like a real FE partition-pruning correctness bug, not a data-routing failure.

What I checked on the reported commit 61c910f2cb:

• The table metadata keeps both the partition expression and the underlying partition column. For this DDL, the partition expression is date_trunc(TIME_STAMP, 'month'), while the underlying partition column is TIME_STAMP.
• The insert/load path is expected to route by the stored partition expression. BindSink.LegacyExprTranslator#createPartitionExprList() builds the sink partition expression list from olapTable.getPartitionInfo().getPartitionExprs(), so 2001-01-07 is routed by date_trunc('2001-01-07', 'month') = '2001-01-01'.
• The static Nereids pruning path does not model that expression domain. PruneOlapScanPartition#getPartitionSlots() builds pruning slots from partitionInfo.getPartitionColumns(), so the pruning slot is the raw TIME_STAMP slot.
• PartitionPruner then extracts TIME_STAMP > '2001-01-06' as a raw-column range, while SortedPartitionRanges / OneRangePartitionEvaluator evaluate it against RangePartitionItem bounds that are in the partition-expression domain.

That makes the issue's example unsafe. Partition p_2000 is [2000-01-01, 2001-01-05) in the date_trunc(TIME_STAMP, 'month') domain. It is wrong to treat that as the possible raw TIME_STAMP range. The inverse image of this expression range still includes raw dates such as 2001-01-07, because their truncated month is 2001-01-01. Therefore pruning p_2000 for TIME_STAMP > '2001-01-06' can produce an empty result even though the row exists.

There is already a similar safety principle in runtime-filter partition pruning: RuntimeFilterPartitionPruneClassifier rejects automatic partition expressions containing function calls with the reason that the automatic partition expression boundary is not modeled. Static partition pruning appears to lack an equivalent guard or expression-domain conversion for this case.

Recommended next steps:

• Reproduce with EXPLAIN / profile and confirm that p_2000 is pruned for select * from date_table where TIME_STAMP > '2001-01-06'.
• Add a regression test using the issue DDL and query. The test should assert the query returns 2001-01-07, and preferably also check that the scan does not prune away p_2000.
• Fix the correctness boundary before adding more aggressive pruning. A conservative fix is to skip raw-column static range pruning when PartitionInfo.enableAutomaticPartition() is true and the range partition expression contains a function call, unless the pruning code can prove a safe mapping between the query predicate domain and the partition-expression domain.
• If the intended product behavior is to allow manual partitions under AUTO PARTITION BY RANGE (date_trunc(...)), the DDL path should also validate that manually specified range boundaries align with the function granularity. In this example, p_2001 with [2001-01-06, 2001-01-08) is effectively unreachable for a monthly date_trunc expression. However, DDL validation alone is not enough unless the pruning code also avoids comparing raw-column predicates directly with expression-domain bounds.

Useful missing confirmation from the reporter would be the EXPLAIN output, selected partition count/name from the plan or profile, and whether the result becomes correct with static partition pruning disabled or by explicitly scanning PARTITION(p_2000).