4paradigm / 4paradigm/OpenMLDB

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

Abierto
#3,599 0 comentarios 0 reacciones 1 asignado Reclamado por @aceforeverd Ver en GitHub
execute-engine
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

Abrir la guía de contribución

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.