4paradigm / 4paradigm/OpenMLDB

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

Đang mở
#3,942 0 bình luận 0 reaction 1 người được giao Được @aceforeverd nhận Xem trên GitHub
bug
Ngôn ngữ chính
C++
Star
1.7k
Fork
331
Merge trung bình
12 ngày 12 giờ
Pull request đã merge (30 ngày)
1

Mô tả

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

Hướng dẫn đóng góp

Mở hướng dẫn đóng góp

Hướng nghiên cứu

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.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Đánh giá

Công nghệ
docker, sql
Lĩnh vực
databases, machine-learning, testing-qa
Loại issue
Lỗi
Độ khó
4/5
Thời gian dự kiến
3-5 ngày
Mức độ hoạt động
Đình trệ
Độ rõ ràng
Khá rõ ràng
Mức phù hợp với người mới
35/100

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.