4paradigm / 4paradigm/OpenMLDB
feat(online): support SQL `last join order by( filter<optimized> ) `
- Lenguaje dominante
- C++
- Estrellas
- 1.7k
- Forks
- 331
- Merge medio
- 12 d 12 h
- PR fusionados (30 d)
- 1
Descripción
```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.
Guía de contribución
Evaluación
Este issue todavía no se ha evaluado.