4paradigm / 4paradigm/OpenMLDB
negative `order by` value overflow in window query
- Dominant language
- C++
- Stars
- 1.7k
- Forks
- 331
- Avg merge
- 12d 12h
- Merged PRs (30d)
- 1
Description
**Bug Description**
```yaml
- id: 38
desc: |
window ts < 0 ?
inputs:
- name: t1
columns:
- id int
- ts int64
- g int
- val int
indexs:
- idx:g
data: |
1, -3000, 111, 21
2, -2000, 111, 22
3, -1000, 111, 23
4, 0, 111, 24
5, 1000, 111, 25
sql: |
select
id, ts,
count(val) over w as cnt,
max(val) over w as mv,
min(val) over w as mi
from t1 window w as(
partition by `g` order by `ts`
ROWS_RANGE between 2000 PRECEDING and 0 preceding);
expect:
columns:
- id int
- ts int64
- cnt int64
- mv int
- mi int
order: id
data: |
1, -3000, 1, 21, 21
2, -2000, 2, 22, 21
3, -1000, 3, 23, 21
4, 0, 3, 24, 22
5, 1000, 3, 25, 23
```
decision must make:
- if it is allowed for `int*` column to be the `ts` value of index
- if not, above case will only run in batch mode (`order by` not optimized)
this is a extended case of #2177
Contributor guide
Research direction
The issue involves window queries with negative timestamp ordering in OpenMLDB. Start by examining the index optimization logic for window queries, particularly around the handling of int64 columns as timestamp values. Look at the test case provided and related issue #2177. Determine if int* columns are allowed as ts values in indexes and how overflow is managed. Run the existing test suite to see current behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases, machine-learning
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100