4paradigm / 4paradigm/OpenMLDB
sql engine: fail to resolve column from right table in last join where multiple window op exists
- Dominant language
- C++
- Stars
- 1.7k
- Forks
- 331
- Avg merge
- 12d 12h
- Merged PRs (30d)
- 1
Description
example case:
```yaml
inputs:
- name: t1
columns:
- id int
- ts timestamp
- g int
- val int
indexs:
- idx:g:ts
data: |
1, 100000, 111, 21
2, 100000, 111, 10000
- name: t2
columns:
- id int
- ts timestamp
- g int
- val int
indexs:
- idx:g:ts
data: |
1, 87000, 111, 300
1, 95000, 111, 999
1, 99000, 111, 233
1, 100000, 111, 200
1, 101000, 111, 17
- name: t3
columns:
- id2 int
- ts2 timestamp
- g2 int
- val2 int
indexs:
- idx:g2:ts2
data: |
9, 88000, 111, 90
sql: |
select
id, g2, count(val) over w as cnt,
max(val) over w as mv,
min(val) over w as mi,
sum(val) over w2 as m2
from t1 last join t3 ON t1.g = t3.g2 window
w as( partition by `g` order by `ts` ROWS_RANGE BETWEEN 5s preceding and 1s preceding),
w2 as (partition by `g` order by `ts` ROWS BETWEEN 1 PRECEDING and CURRENT ROW);
batch_plan: |
expect:
columns:
- id int
- g2 int
- cnt int64
- mv int
- mi int
- m2 int
order: id
data: |
1, 111, 2, 999, 233, 21
2, 111, 2, 999, 233, 10021
```
error msg:
```
I0925 23:04:51.622352 615101952 engine_test_base.cc:401] Fail to find column id #35 in current schema context
(At /Users/danielace/Git/4paradigm/openmldb/hybridse/src/vm/sql_compiler.cc:275)
(At /Users/danielace/Git/4paradigm/openmldb/hybridse/src/vm/sql_compiler.cc:170)
(Caused by) Fail to generate physical plan batch mode
(At /Users/danielace/Git/4paradigm/openmldb/hybridse/src/vm/transform.cc:1738)
(Caused by) Fail to generate functions for physical plan
(At /Users/danielace/Git/4paradigm/openmldb/hybridse/src/vm/transform.cc:309)
(Caused by) Instantiate 0th native function "__internal_sql_codegen_5" failed at node:
PROJECT(type=WindowAggregation)
+-WINDOW(partition_keys=(g), orders=(ts ASC), range=(ts, 5000 PRECEDING, 1000 PRECEDING))
PROJECT(type=WindowAggregation, NEED_APPEND_INPUT)
+-WINDOW(partition_keys=(), orders=(ASC), rows=(ts, 1 PRECEDING, 0 CURRENT))
+-JOIN(type=LastJoin, condition=, left_keys=(), right_keys=(), index_keys=(t1.g))
DATA_PROVIDER(type=Partition, table=t3, index=idx)
DATA_PROVIDER(type=Partition, table=t1, index=idx)
(At /Users/danielace/Git/4paradigm/openmldb/hybridse/src/codegen/fn_let_ir_builder.cc:128)
(Caused by) Build expr failed at 1:
+-expr[get field]
+-input:
+-expr[id]
+-var: %55(row)
+-column_id: 35
+-column_name: g2
(At /Users/danielace/Git/4paradigm/openmldb/hybridse/src/codegen/fn_let_ir_builder.cc:213)
(Caused by) Fail to codegen project expression: #35:g2
(At /Users/danielace/Git/4paradigm/openmldb/hybridse/src/codegen/expr_ir_builder.cc:162)
(At /Users/danielace/Git/4paradigm/openmldb/hybridse/src/codegen/expr_ir_builder.cc:996)
(Caused by) Fail to resolve column #35:g2 from row
(At /Users/danielace/Git/4paradigm/openmldb/hybridse/src/vm/schemas_context.cc:277)
(Caused by) Fail to find column id #35 in current schema context
/Users/danielace/Git/4paradigm/openmldb/hybridse/src/testing/engine_test_base.cc:427: Failure
Expected equality of these values:
sql_case_.expect().success_
Which is: true
status.isOK()
Which is: false
```
Contributor guide
Research direction
The error occurs in the SQL compiler when resolving column g2 from the right table in a last join with multiple window operations. Start by examining the schema context handling in src/vm/schemas_context.cc around line 277 and the column resolution logic in src/codegen/expr_ir_builder.cc. The test case is in a YAML format; run the specific engine test to reproduce the failure. Understanding the join and window aggregation plan generation in src/vm/transform.cc is key to fixing the column reference issue.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, sql
- Domain
- databases, machine-learning
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 30/100