4paradigm / 4paradigm/OpenMLDB

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

Open
#3,942 0 comments 0 reactions 1 assignee Claimed by @aceforeverd View on GitHub
bug
Dominant language
C++
Stars
1.7k
Forks
331
Avg merge
12d 12h
Merged PRs (30d)
1

Description

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

Contributor guide

Open the contributing guide

Research direction

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.

Written by the indexing model from the issue text.

Assessment

Tech stack
docker, sql
Domain
databases, machine-learning, testing-qa
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.