4paradigm / 4paradigm/OpenMLDB

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

Offen
#3,942 0 Kommentare 0 Reaktionen 1 zugewiesene Person Beansprucht von @aceforeverd Auf GitHub ansehen
bug
Vorherrschende Sprache
C++
Sterne
1.7k
Forks
331
Ø Merge
12 T. 12 Std.
Gemergte PRs (30 T.)
1

Beschreibung

**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.

Beitragsleitfaden

Beitragsleitfaden öffnen

Rechercherichtung

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.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Bewertung

Tech-Stack
docker, sql
Bereich
databases, machine-learning, testing-qa
Issue-Typ
Bug
Schwierigkeit
4/5
Geschätzter Aufwand
3-5 Tage
Aktivitätsstatus
Veraltet
Klarheit
Größtenteils klar
Anfängerfreundlichkeit
35/100

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.