4paradigm / 4paradigm/OpenMLDB

Get incorrect result from rewrite sql when deploying with openmldb docker image but not for onebox

Abierto
#3,942 0 comentarios 0 reacciones 1 asignado Reclamado por @aceforeverd Ver en GitHub
bug
Lenguaje dominante
C++
Estrellas
1.7k
Forks
331
Merge medio
12 d 12 h
PR fusionados (30 d)
1

Descripción

**Bug Description**

We are using openmldb 0.9.0 docker image to setup the cluster with `./init.sh`. The following sql will get incorrect result.

```
create database if not exists tpcc;

use tpcc;

create table orders (  o_w_id       integer,  o_d_id       integer,  o_id         integer,  o_c_id       integer,  o_carrier_id integer,  o_ol_cnt     integer,  o_all_local  integer,  o_entry_d    timestamp,  INDEX(KEY=o_c_id, TS=o_entry_d));

SELECT o_id, avg_7d_cnt, avg_15d_cnt, avg_30d_cnt, max_7d_cnt, max_15d_cnt, max_30d_cnt, min_7d_cnt, min_15d_cnt, min_30d_cnt
FROM (
SELECT o_id,
CAST(avg(o_ol_cnt) OVER w_1 AS DOUBLE) AS avg_7d_cnt,
CAST(avg(o_ol_cnt) OVER w_3 AS DOUBLE) AS avg_15d_cnt,
CAST(avg(o_ol_cnt) OVER w_5 AS DOUBLE) AS avg_30d_cnt,
max(o_ol_cnt) OVER w_1 AS max_7d_cnt,
max(o_ol_cnt) OVER w_3 AS max_15d_cnt,
max(o_ol_cnt) OVER w_5 AS max_30d_cnt,
min(o_ol_cnt) OVER w_1 AS min_7d_cnt,
min(o_ol_cnt) OVER w_3 AS min_15d_cnt,
min(o_ol_cnt) OVER w_5 AS min_30d_cnt,
label FROM (
SELECT 1 AS o_w_id, 1 AS o_d_id, 1 AS o_id, 930 AS o_c_id, 2 AS o_carrier_id, 11 AS o_ol_cnt, 1 AS o_all_local, timestamp("2024-05-25 17:00:26") AS O_ENTRY_D, 0 as label
UNION ALL
SELECT *, 1 as label FROM orders
) t
WINDOW
w_1 AS (PARTITION BY o_c_id ORDER BY o_entry_d ROWS between 1 PRECEDING and current row),
w_3 AS (PARTITION BY o_c_id ORDER BY o_entry_d ROWS between 3 PRECEDING and current row),
w_5 AS (PARTITION BY o_c_id ORDER BY o_entry_d ROWS between 5 PRECEDING and current row)
) t WHERE label = 0;
```

However, we can get the correct result when using onebox to deploy.

Guía de contribución

Abrir la guía de contribución

Línea de trabajo

The issue involves a SQL query that yields different results between a Docker cluster deployment and a onebox deployment. Start by examining the SQL rewrite logic in the query engine, particularly the window function handling. Look for differences in deployment configurations or environment-specific code paths. Run the provided SQL in both setups to reproduce the discrepancy, then trace through the query execution to identify where the rewrite diverges.

Escrito por el modelo de indexación a partir del texto del issue.

Evaluación

Stack tecnológico
docker, sql
Área
databases, machine-learning, testing-qa
Tipo de issue
Error
Dificultad
4/5
Tiempo estimado
3-5 días
Estado de actividad
Estancado
Claridad
Bastante claro
Aptitud para principiantes
35/100

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.