TPCC Consistency 约束验证SQLs

8月 21st, 2012

TPCC Consistency 约束验证SQLs

以下,是按照TPCC标准,撰写的验证Consistency的12条SQL,每条SQL返回结果为Empty Sets时,说明此验证通过。

以下所有的SQL,在MySQL + TNT引擎 + TPCC-MySQL + 10-warehouses下执行成功(除了Condition 10,11,12,最终的SQL未能撰写成功)。

Consistency Conditions

————————— Consistency Condition 1 –——————————–

— Entries in the WAREHOUSE and DISTRICT tables must satisfy the relationship:

— W_YTD = sum(D_YTD)

— for each warehouse defined by (W_ID = D_W_ID).

———————————————————————————————-

select *

from

    (select t1.w_id, t1.w_ytd, sum(t2.d_ytd), t1.w_ytd – sum(t2.d_ytd) as diff

    from warehouse t1, district t2

    where t1.w_id = t2.d_w_id

    group by w_id) t3

where diff != 2700000.00;

注一:由于Percona提供的MySQL TPCC的开源测试工具,在装载warehouse表数据时有误。w_ytd列初始化按照标准应该是300,000;而Percona TPCC工具装载成了3,000,000。因此此SQL与Consistency Condition 1的要求不一致。在10-warehouses下,二者差距为2,700,000。

————————- Consistency Condition 2 —————————————-

— Entries in the DISTRICT, ORDER, and NEW-ORDER tables must satisfy the relationship:

— D_NEXT_O_ID – 1 = max(O_ID) = max(NO_O_ID)

— for each district defined by (D_W_ID = O_W_ID = NO_W_ID) and (D_ID = O_D_ID = NO_D_ID).

— This condition does not apply to the NEW-ORDER table for any districts

— which have no outstanding new orders (i.e., the number of rows is zero).

—————————————————————————————————-

select t1.d_w_id, t1.d_id, t1.max_o_id1, t2.max_o_id2, t3.max_o_id3

from

    (select d_w_id, d_id, d_next_o_id – 1 as max_o_id1

    from district) t1,

    (select o_w_id, o_d_id, max(o_id) as max_o_id2

    from orders

    group by o_w_id, o_d_id) t2,

    (select no_w_id, no_d_id, max(no_o_id) as max_o_id3

    from new_orders

    group by no_w_id, no_d_id) t3

where t1.d_w_id = t2.o_w_id

and t1.d_w_id = t3.no_w_id

and t1.d_id = t2.o_d_id

and t1.d_id = t3.no_d_id

and (t1.max_o_id1 != t2.max_o_id2 or t1.max_o_id1 != t3.max_o_id3 or t2.max_o_id2 != t3.max_o_id3);

————————- Consistency Condition 3 —————————————-

— Entries in the NEW-ORDER table must satisfy the relationship:

— max(NO_O_ID) – min(NO_O_ID) + 1 = [number of rows in the NEW-ORDER table for this district]

— for each district defined by NO_W_ID and NO_D_ID. This condition does not apply to

— any districts which have no outstanding new orders (i.e., the number of rows is zero).

—————————————————————————————————-

select t2.*, t2.maxmin – t2.tot

from (

    select t1.no_w_id, t1.no_d_id, max(no_o_id) – min(no_o_id) + 1 as maxmin, count(*) as tot

    from new_orders t1

    group by t1.no_w_id, t1.no_d_id

) t2

where t2.maxmin – t2.tot != 0

order by t2.no_w_id, t2.no_d_id;

————————- Consistency Condition 4 —————————————-

— Entries in the ORDER and ORDER-LINE tables must satisfy the relationship:

— sum(O_OL_CNT) = [number of rows in the ORDER-LINE table for this district]

— for each district defined by (O_W_ID = OL_W_ID) and (O_D_ID = OL_D_ID).

——————————————————————————————

select t1.o_w_id, t1.o_d_id, t1.tot_cnt, t2.tot, t1.tot_cnt – t2.tot

from

    (select o_w_id, o_d_id, sum(o_ol_cnt) as tot_cnt

    from orders

    group by o_w_id, o_d_id) t1,

    (select ol_w_id, ol_d_id, count(*) as tot

    from order_line

    group by ol_w_id, ol_d_id) t2

where t1.o_w_id = t2.ol_w_id

and t1.o_d_id = t2.ol_d_id

and t1.tot_cnt – t2.tot != 0

order by t1.o_w_id, t1.o_d_id;

————————- Consistency Condition 5 —————————————-

— For any row in the ORDER table, O_CARRIER_ID is set to a null value if and only if

— there is a corresponding row in the NEW-ORDER table defined by

— (O_W_ID, O_D_ID, O_ID) = (NO_W_ID, NO_D_ID, NO_O_ID).

——————————————————————————————

select * from

    (select count(*) as tot_null_cnt1

    from orders

    where o_carrier_id is null) t3,

    (select count(*) as tot_null_cnt

    from orders t1, new_orders t2

    where t1.o_w_id = t2.no_w_id

    and t1.o_d_id = t2.no_d_id

    and t1.o_id = t2.no_o_id

    and t1.o_carrier_id is null) t4,

    (select count(*) as tot_not_null_cnt

    from orders t6, new_orders t7

    where t6.o_w_id = t7.no_w_id

    and t6.o_d_id = t7.no_d_id

    and t6.o_id = t7.no_o_id

    and t6.o_carrier_id is not null) t5

where t4.tot_null_cnt – t3.tot_null_cnt1 != 0 or t5.tot_not_null_cnt != 0;

————————- Consistency Condition 6 —————————————-

— For any row in the ORDER table, O_OL_CNT must equal the number of rows in the

— ORDER-LINE table for the corresponding order defined by

— (O_W_ID, O_D_ID, O_ID) = (OL_W_ID, OL_D_ID, OL_O_ID).

——————————————————————————————

select *

from

    (select t1.o_w_id, t1.o_d_id, t1.o_id, min(t1.o_ol_cnt) as min_ol_cnt, count(*) as ol_cnts

    from orders t1, order_line t2

    where t1.o_w_id = t2.ol_w_id

    and t1.o_d_id = t2.ol_d_id

    and t1.o_id = t2.ol_o_id

    group by t1.o_w_id, t1.o_d_id, t1.o_id) t3

where min_ol_cnt != ol_cnts;

————————- Consistency Condition 7 —————————————-

— For any row in the ORDER-LINE table, OL_DELIVERY_D is set to a null date/time

— if and only if the corresponding row in the ORDER table defined by

— (O_W_ID, O_D_ID, O_ID) = (OL_W_ID, OL_D_ID, OL_O_ID) has O_CARRIER_ID set to a null value.

——————————————————————————————

select t1.o_w_id, t1.o_d_id, t1.o_id, t1.o_carrier_id, t2.ol_delivery_d

from orders t1, order_line t2

where t1.o_w_id = t2.ol_w_id

and t1.o_d_id = t2.ol_d_id

and t1.o_id = t2.ol_o_id

and ((t1.o_carrier_id is null and t2.ol_delivery_d is not null)

    or (t1.o_carrier_id is not null and t2.ol_delivery_d is null));

————————- Consistency Condition 8 —————————————-

— Entries in the WAREHOUSE and HISTORY tables must satisfy the relationship:

— W_YTD = sum(H_AMOUNT)

— for each warehouse defined by (W_ID = H_W_ID).

——————————————————————————————

select t1.w_id, t1.w_ytd, t2.sum_amount, t1.w_ytd – t2.sum_amount

from

    (select w_id, w_ytd from warehouse) t1,

    (select h_w_id, sum(h_amount) as sum_amount

    from history

    group by h_w_id) t2

where t1.w_id = t2.h_w_id

and t1.w_ytd != t2.sum_amount

and t1.w_ytd – t2.sum_amount != 2700000.00;

注二:原因同Consistency Condition 1。

————————- Consistency Condition 9 —————————————-

— Entries in the DISTRICT and HISTORY tables must satisfy the relationship:

— D_YTD = sum(H_AMOUNT)

— for each district defined by (D_W_ID, D_ID) = (H_W_ID, H_D_ID).

——————————————————————————————

select t1.d_w_id, t1.d_id, t1.d_ytd, t2.sum_amount

from

    (select d_w_id, d_id, d_ytd from district) t1,

    (select h_w_id, h_d_id, sum(h_amount) as sum_amount

    from history

    group by h_w_id, h_d_id) t2

where t1.d_w_id = t2.h_w_id

and t1.d_id = t2.h_d_id

and t1.d_ytd != t2.sum_amount;

————————- Consistency Condition 10 —————————————

— Entries in the CUSTOMER, HISTORY, ORDER, and ORDER-LINE tables must satisfy the relationship:

—    C_BALANCE = sum(OL_AMOUNT) – sum(H_AMOUNT)

— where:

—    H_AMOUNT is selected by (C_W_ID, C_D_ID, C_ID) = (H_C_W_ID, H_C_D_ID, H_C_ID)

— and

—    OL_AMOUNT is selected by:

—    (OL_W_ID, OL_D_ID, OL_O_ID) = (O_W_ID, O_D_ID, O_ID) and

—    (O_W_ID, O_D_ID, O_C_ID) = (C_W_ID, C_D_ID, C_ID) and

—    (OL_DELIVERY_D is not a null value)

——————————————————————————————

select c_w_id, c_d_id, c_id, c_balance

from customer

where c_w_id = 1 and c_d_id = 1 and c_id = 2;

select t1.c_w_id, t1.c_d_id, t1.c_id, sum(t2.h_amount)

from customer t1, history t2

where t1.c_w_id = t2.h_c_w_id

and t1.c_d_id = t2.h_c_d_id

and t1.c_id = t2.h_c_id

and t1.c_w_id = 1 and t1.c_d_id = 1 and t1.c_id = 2

group by t1.c_w_id, t1.c_d_id, t1.c_id;

select t1.c_w_id, t1.c_d_id, t1.c_id, sum(t3.ol_amount) as sum_ol_amount

from customer t1, orders t2, order_line t3

where t3.ol_w_id = t2.o_w_id

and t3.ol_d_id = t2.o_d_id

and t3.ol_o_id = t2.o_id

and t2.o_w_id = t1.c_w_id

and t2.o_d_id = t1.c_d_id

and t2.o_c_id = t1.c_id

and t3.ol_delivery_d is not null

and t1.c_w_id = 1 and t1.c_d_id = 1 and t1.c_id = 2

group by t1.c_w_id, t1.c_d_id, t1.c_id;

注三此SQL,连同以下的SQL 11,SQL 12,都没有完全按照TPCC的标准写出,也未能验证数据的正确性。暂时还不知是由于Percona TPCC工具的问题,或是本人SQL撰写能力不足所导致,望大家指点!

————————- Consistency Condition 11 —————————————

— Entries in the CUSTOMER, ORDER and NEW-ORDER tables must satisfy the relationship:

—    (count(*) from ORDER) – (count(*) from NEW-ORDER) = 2100

— for each district defined by (O_W_ID, O_D_ID) = (NO_W_ID, NO_D_ID) = (C_W_ID, C_D_ID).

——————————————————————————————

select o_w_id, o_d_id, count(*) from orders, customer

where o_w_id = c_w_id

and o_d_id = c_d_id

group by o_w_id, o_d_id

order by o_w_id, o_d_id;

select no_w_id, no_d_id, count(*) from new_orders

group by no_w_id, no_d_id

order by no_w_id, no_d_id;

select c_w_id, c_d_id, count(*) from customer

group by c_w_id, c_d_id

order by c_w_id, c_d_id;

select t1.o_w_id, t1.o_d_id, t1.orders_cnt, t2.new_orders_cnt, t1.orders_cnt – t2.new_orders_cnt as diff

from

    (select o_w_id, o_d_id, count(*) as orders_cnt

    from orders, customer

    where o_w_id = c_w_id

    and o_d_id = c_d_id

    group by o_w_id, o_d_id) t1,

    (select no_w_id, no_d_id, count(*) as new_orders_cnt

    from new_orders, customer

    where no_w_id = c_w_id

    and no_d_id = c_d_id

    group by no_w_id, no_d_id) t2

where t1.o_w_id = t2.no_w_id

and t1.o_d_id = t2.no_d_id

and t1.orders_cnt – t2.new_orders_cnt != 2100;

————————- Consistency Condition 12 —————————————

— Entries in the CUSTOMER and ORDER-LINE tables must satisfy the relationship:

—    C_BALANCE + C_YTD_PAYMENT = sum(OL_AMOUNT)

— for any randomly selected customers and where OL_DELIVERY_D is not set to a null date/time.

——————————————————————————————

select t4.c_w_id, t4.c_d_id, t4.c_id, t4.sum_ol_amount, t5.sum_bp, t5.sum_bp – t4.sum_ol_amount

from

    (select t1.c_w_id, t1.c_d_id, t1.c_id, sum(t3.ol_amount) as sum_ol_amount

    from customer t1, orders t2, order_line t3

    where t3.ol_w_id = t2.o_w_id

    and t3.ol_d_id = t2.o_d_id

    and t3.ol_o_id = t2.o_id

    and t2.o_w_id = t1.c_w_id

    and t2.o_d_id = t1.c_d_id

    and t2.o_c_id = t1.c_id

    and t3.ol_delivery_d is not null

    and t1.c_id = 2 and t1.c_w_id = 1 and t1.c_d_id = 1

    group by t1.c_w_id, t1.c_d_id, t1.c_id) t4,

    (select c_id, c_w_id, c_d_id, sum(c_balance + c_ytd_payment) as sum_bp

    from customer

    where c_id = 2 and c_w_id = 1 and c_d_id = 1) t5

where t4.c_w_id = t5.c_w_id

and t4.c_d_id = t5.c_d_id

and t4.c_id = t5.c_id;

select t3.c_id, t3.c_sum, t4.tot_amount, t3.c_sum – t4.tot_amount

from

    (select c_id, c_w_id, c_d_id, c_balance + c_ytd_payment as c_sum

    from customer) t3,

    (select t1.o_c_id, t1.o_w_id, t1.o_d_id, sum(ol_amount) as tot_amount

    from orders t1, order_line t2

    where t1.o_id = t2.ol_o_id

    and t1.o_d_id = t2.ol_d_id

    and t1.o_w_id = t2.ol_w_id

    and t2.ol_delivery_d is not null

    group by t1.o_c_id, t1.o_w_id, t1.o_d_id

    ) t4

where t3.c_id = t4.o_c_id

and t3.c_w_id = t4.o_w_id

and t3.c_d_id = t4.o_d_id

and t3.c_sum != t4.tot_amount;

select t3.c_id, t3.c_sum, t4.tot_amount, t3.c_sum – t4.tot_amount

from

    (select c_id, c_w_id, c_d_id, c_balance + c_ytd_payment as c_sum

    from customer where c_id = 1 and c_w_id = 1 and c_d_id = 1) t3,

    (select t1.o_c_id, t1.o_w_id, t1.o_d_id, sum(ol_amount) as tot_amount

    from orders t1, order_line t2

    where t1.o_id = t2.ol_o_id

    and t1.o_d_id = t2.ol_d_id

    and t1.o_w_id = t2.ol_w_id

    and t1.o_c_id = 1 and t1.o_w_id = 1 and t1.o_d_id = 1

    and t2.ol_delivery_d is not null

    group by t1.o_c_id, t1.o_w_id, t1.o_d_id

    ) t4

where t3.c_id = t4.o_c_id

and t3.c_w_id = t4.o_w_id

and t3.c_d_id = t4.o_d_id

and t3.c_sum != t4.tot_amount;

参考资料

[1] https://code.launchpad.net/~percona-dev/perconatools/tpcc-mysql    Percona TPC工具地址

[2] http://www.tpc.org/tpcc/                                        TPCC Home

[3] http://www.tpc.org/tpcc/spec/tpcc_current.pdf                    TPCC最新版本文档

[4] http://www.infor.uva.es/~diego/tpcc-uva.html                  TPCC-UVa: A free, open-source implementation of the TPC-C Benchmark (For PostgreSQL)

目前还没有任何评论.