apache / apache/doris

[Bug] arithmetic rewriting may result in error result when overflow

Open
#61,761 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
15.9k
Forks
3.9k
Avg merge
2d 23h
Merged PRs (30d)
522

Description

### 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

3.1
3.0
master

### What's Wrong?

SimplifyArithmeticComparisonRule will simply arranging `ComparisonPredicate`. But, if overflow, this behavior will result in error result or trigger exception.
For example:
`date_sub(i, interval 1 day) <= '9999-12-31'` will be rewrited to `i <= date_add('9999-12-31', interval 1 day)`. But, `date_add('9999-12-31', interval 1 day)` will overflow.

### What You Expected?

when overflow, `ComparisonPredicate` should not be rewritten.

### How to Reproduce?

```
create table test_rewrite_arithmetic (`i` datetime NOT NULL) PROPERTIES ("replication_allocation" = "tag.location.default: 1");
insert into test_rewrite_arithmetic values('2026-01-20');
select date_sub(i, interval 1 day) <='9999-12-31' from test_rewrite_arithmetic;
```
3.0/3.1:
```
+--------------------------------------------+
| date_sub(i, interval 1 day) <='9999-12-31' |
+--------------------------------------------+
| NULL |
+--------------------------------------------+
```
master:
```
ERROR 1105 (HY000): errCode = 2, detailMessage = (172.19.0.2)[E-218]Operation day_add of 9999-12-31 00:00:00, 1 out of range
```

### 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)

Contributor guide

Open the contributing guide

Research direction

Start by tracing SimplifyArithmeticComparisonRule using the supplied date_sub/date_add overflow query as a reproducer. Check the rewrite path for boundary values such as '9999-12-31'; done means the ComparisonPredicate is left unchanged on overflow and the query no longer returns an erroneous result or triggers an exception.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
50/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.