Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
tobegit3hub opened this issue May 30, 2024 · 0 comments
Assignees
Labels
bug Something isn't working

Comments

@tobegit3hub
Copy link
Collaborator

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.

@tobegit3hub tobegit3hub added the bug Something isn't working label May 30, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants