Stock Level Transaction

Input

• unique constant value of (w_id, d_id)
• threshold = rand(10, 20)

Start Transaction

GetOId

select D_NEXT_O_ID
from DISTRICT
where D_W_ID = w_id
  and D_ID = d_id
limit 1

{
  "operators": {
    "load": {
       "type": "GetTable",
       "name": "DISTRICT"
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "D_W_ID", "vtype": 0 /*Int*/, "value": %(w_id)d},
         {"type": "EQ", "in": 0, "f": "D_ID", "vtype": 0 /*Int*/, "value": %(d_id)d}
       ]
    },
    "project": {
       "type": "ProjectionScan",
       "fields": ["D_NEXT_O_ID"]
    }
  },
  "edges": [["load", "select"], ["select", "project"]]
}

bla

Client:

max_id = d_next_o_id;
min_id = d_next_o_id - 20;

(min_id <= id < max_id ... ???how does sqls between work???)

select COUNT(*)
from ORDER_LINE, STOCK
where OL_I_ID = S_I_ID
  and OL_W_ID = w_id
  and OL_D_ID = d_id
  and OL_O_ID between min_in and max_id
  and S_W_ID = w_id
  and S_QUANTITY < threshold

{
  "operators": {
    "load1": {
       "type": "GetTable",
       "name": "ORDER_LINE"
    },
    "load2": {
       "type": "GetTable",
       "name": "STOCK"
    },
    "join": {
       "type":"JoinScan",
       "predicates": [
         {"type": 3, "input_left": 0, "field_left": "OL_I_ID", "input_right": 1, "field_right": "S_I_ID"}
       ]
    },
    "select": {
       "type": "SimpleTableScan",
       "predicates": [
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "OL_W_ID", "vtype": 0, "value": %(w_id)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "OL_D_ID", "vtype": 0, "value": %(d_id)d},
         {"type": "AND"},
         {"type": "GT", "in": 0, "f": "OL_O_ID", "vtype": 0, "value": %(o_id1)d},
         {"type": "AND"},
         {"type": "LT", "in": 0, "f": "OL_O_ID", "vtype": 0, "value": %(o_id2)d},
         {"type": "AND"},
         {"type": "EQ", "in": 0, "f": "S_W_ID", "vtype": 0, "value": %(w_id)d},
         {"type": "LT", "in": 0, "f": "S_QUANTITY", "vtype": 0, "value": %(threshold)d}
       ]
    },
    "groupby": {
       "type": "GroupByScan",
       "fields": [],
       "functions": [
         {"type": "COUNT", "field": "OL_I_ID", "distinct": true, "as": "distinct_count"}
       ]
    }
  },
  "edges": [["load1", "join"], ["load2", "join"], ["join", "select"], ["select", "groupby"]]
}

Commit

Output

TPC