4paradigm / 4paradigm/OpenMLDB

feat(online): support SQL `last join order by( filter<optimized> ) `

オープン
#3,599 コメント 0 件 リアクション 0 件 担当者 1 名 @aceforeverd が担当を希望しています GitHub で見る
execute-engine
主要言語
C++
スター
1.7k
フォーク
331
平均マージ
12日 12時間
マージ済み PR(30日)
1

説明

```yaml
- id: 6
mode: batch-request-unsupport,request-unsupport
desc: |
LASTJOIN(SimpleOps(FILTER)) hit differnt index with order by,
can not online because join & filter use different indexs
sql: |
SELECT
t1.c1,
t1.c2,
t2.c1r as c21,
t2.c3 as c32
FROM t1 last join (select c1 as c1r, c2 as c2r, c3, c4 as c4r from t2 where c1 == 'aa') t2 order by c4r
on t1.c2 = t2.c2r;
inputs:
- name: t1
columns: ["c1 string","c2 int","c3 bigint","c4 timestamp"]
indexs: ["index1:c1:c4"]
rows:
- ["aa",2,3,1590738989000]
- ["bb",21,31,1590738990000]
- ["cc",41,51,1590738991000]
- name: t2
columns: ["c1 string","c2 int","c3 bigint","c4 timestamp"]
indexs: ["index1:c1:c4", "index2:c2:c4"]
rows:
- ["aa",2,8,1590738989000]
- ["aa",10,12,1590738989100]
- ["bb",21,131,1590738990000]
- ["dd",41,151,1590738991000]
request_plan: |
expect:
columns:
- c1 string
- c2 int
- c21 string
- c32 int64
order: c1
data: |
aa, 2, aa, 8
bb, 21, NULL, NULL
cc, 41, NULL, NULL
```

the SQL fails to compile because order by for last join exists
```
I1116 07:38:12.202760 88076 group_and_sort_optimized.cc:779] keys optimized: (c1)
I1116 07:38:12.203022 88076 group_and_sort_optimized.cc:781] keys and order optimized: keys=(t2.c2r), order=(c4r ASC)
I1116 07:38:12.203500 88076 transform.cc:1983] After optimization:
SIMPLE_PROJECT(sources=(t1.c1, t1.c2, t2.c1r -> c21, t2.c3 -> c32))
REQUEST_JOIN(type=LastJoin, right_sort=(c4r ASC), condition=, left_keys=(t1.c2), right_keys=(t2.c2r), index_keys=)
DATA_PROVIDER(request=t1)
RENAME(name=t2)
SIMPLE_PROJECT(sources=(c1 -> c1r, c2 -> c2r, c3, c4 -> c4r))
FILTER_BY(condition=, left_keys=(), right_keys=(), index_keys=(aa))
DATA_PROVIDER(type=Partition, table=t2, index=index1)
I1116 07:38:12.203665 88076 engine_test_base.cc:397] SQL Compile take 39.932 milliseconds
I1116 07:38:12.203693 88076 engine_test_base.cc:400] Last Join node order by hasn't been optimized
(At ../hybridse/src/vm/sql_compiler.cc:279)
(At ../hybridse/src/vm/sql_compiler.cc:182)
(Caused by) Fail to transform physical plan on request mode
(At ../hybridse/src/vm/transform.cc:1984)
(At ../hybridse/src/vm/transform.cc:1864)
(Caused by) Fail to support physical plan in performance sensitive mode
(At ../hybridse/src/vm/transform.cc:1947)
(At ../hybridse/src/vm/transform.cc:1922)
(At ../hybridse/src/vm/transform.cc:1723)
(Caused by) Last Join node order by hasn't been optimized
../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
```

But since FILTER node already optimized the table, for LASTJOIN with order by, the overhead of ORDER BY is simply resort for segment `cc == 'aa'` on table t2. Guess that's sufficient for online request mode, as long as segment 'aa' is not really large.

コントリビューションガイド

コントリビューションガイドを開く

調査の方向性

The issue is about enabling LAST JOIN with ORDER BY in online request mode when the FILTER is already optimized. Start by examining the error in hybridse/src/vm/transform.cc around line 1723 and the group_and_sort_optimized.cc file to understand the current optimization logic. The goal is to allow ORDER BY when the filtered segment is small, ensuring the join and filter use compatible indexes. Run the provided SQL test case to verify the fix.

索引モデルが issue の本文から書いたものです。

評価

技術スタック
cpp, sql
領域
databases, machine-learning
issue の種類
機能追加
難易度
4/5
見積もり時間
3〜5日
活発さ
停滞
明瞭さ
おおむね明確
初心者へのやさしさ
45/100

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。