Search in sources :

Example 61 with VoltTableRow

use of org.voltdb.VoltTableRow in project voltdb by VoltDB.

the class neworder method run.

public VoltTable[] run(short w_id, byte d_id, int c_id, TimestampType timestamp, int[] item_id, short[] supware, int[] quantity) throws VoltAbortException {
    assert item_id.length > 0;
    assert item_id.length == supware.length;
    assert item_id.length == quantity.length;
    // CHEAT: Validate all items to see if we will need to abort.
    // Also determine if this is an all local order or not
    final VoltTableRow[] items = new VoltTableRow[item_id.length];
    boolean isAllLocal = true;
    for (int i = 0; i < item_id.length; ++i) {
        isAllLocal = isAllLocal && supware[i] == w_id;
        voltQueueSQL(getItemInfo, item_id[i]);
    }
    voltQueueSQL(getWarehouseTaxRate, w_id);
    voltQueueSQL(getDistrict, d_id, w_id);
    voltQueueSQL(getCustomer, w_id, d_id, c_id);
    final VoltTable[] itemresults = voltExecuteSQL();
    assert itemresults.length == item_id.length + 3;
    for (int i = 0; i < item_id.length; ++i) {
        if (itemresults[i].getRowCount() == 0) {
            // TPCC defines 1% of neworder gives a wrong itemid, causing rollback.
            throw new VoltAbortException(Constants.INVALID_ITEM_MESSAGE);
        }
        assert itemresults[i].getRowCount() == 1;
        items[i] = itemresults[i].fetchRow(0);
    }
    //final VoltTable[] backgroundInfo = executeSQL();
    VoltTable customer = itemresults[item_id.length + 2];
    final double w_tax = itemresults[item_id.length].fetchRow(0).getDouble(0);
    final int D_TAX_COL = 0, D_NEXT_O_ID = 1;
    final int C_DISCOUNT = 0;
    final VoltTableRow tempRow = itemresults[item_id.length + 1].fetchRow(0);
    final double d_tax = tempRow.getDouble(D_TAX_COL);
    final double c_discount = itemresults[item_id.length + 2].fetchRow(0).getDouble(C_DISCOUNT);
    final long d_next_o_id = tempRow.getLong(D_NEXT_O_ID);
    final long ol_cnt = item_id.length;
    final long all_local = isAllLocal ? 1 : 0;
    voltQueueSQL(incrementNextOrderId, d_next_o_id + 1, d_id, w_id);
    voltQueueSQL(createOrder, d_next_o_id, d_id, w_id, c_id, timestamp, Constants.NULL_CARRIER_ID, ol_cnt, all_local);
    voltQueueSQL(createNewOrder, d_next_o_id, d_id, w_id);
    voltExecuteSQL();
    // values the client is missing: i_name, s_quantity, brand_generic, i_price, ol_amount
    final VoltTable item_data = item_data_template.clone(2048);
    double total = 0;
    for (int i = 0; i < item_id.length; ++i) {
        final long ol_supply_w_id = supware[i];
        final long ol_i_id = item_id[i];
        // One getStockInfo SQL statement for each district
        voltQueueSQL(getStockInfo[d_id - 1], ol_i_id, ol_supply_w_id);
    }
    final VoltTable[] stockresults = voltExecuteSQL();
    assert stockresults.length == item_id.length;
    for (int i = 0; i < item_id.length; ++i) {
        final long ol_number = i + 1;
        final long ol_supply_w_id = supware[i];
        final long ol_i_id = item_id[i];
        final long ol_quantity = quantity[i];
        assert stockresults[i].getRowCount() == 1 : "Cannot find stock info for item; should not happen with valid database";
        final VoltTableRow itemInfo = items[i];
        final VoltTableRow stockInfo = stockresults[i].fetchRow(0);
        final int I_PRICE = 0, I_NAME = 1, I_DATA = 2;
        final byte[] i_name = itemInfo.getStringAsBytes(I_NAME);
        final byte[] i_data = itemInfo.getStringAsBytes(I_DATA);
        final double i_price = itemInfo.getDouble(I_PRICE);
        final int S_QUANTITY = 0, S_DATA = 1, S_YTD = 2, S_ORDER_CNT = 3, S_REMOTE_CNT = 4, S_DIST_XX = 5;
        long s_quantity = stockInfo.getLong(S_QUANTITY);
        long s_ytd = stockInfo.getLong(S_YTD);
        long s_order_cnt = stockInfo.getLong(S_ORDER_CNT);
        long s_remote_cnt = stockInfo.getLong(S_REMOTE_CNT);
        final byte[] s_data = stockInfo.getStringAsBytes(S_DATA);
        // Fetches data from the s_dist_[d_id] column
        final byte[] s_dist_xx = stockInfo.getStringAsBytes(S_DIST_XX);
        // Update stock
        s_ytd += ol_quantity;
        if (s_quantity >= ol_quantity + 10) {
            s_quantity = s_quantity - ol_quantity;
        } else {
            s_quantity = s_quantity + 91 - ol_quantity;
        }
        s_order_cnt++;
        if (ol_supply_w_id != w_id)
            s_remote_cnt++;
        // TODO(evanj): Faster to do s_ytd and s_order_cnt increment in SQL?
        // Saves fetching those columns the first time
        voltQueueSQL(updateStock, s_quantity, s_ytd, s_order_cnt, s_remote_cnt, ol_i_id, ol_supply_w_id);
        byte[] brand_generic;
        if (indexOf(i_data, Constants.ORIGINAL_BYTES) != -1 && indexOf(s_data, Constants.ORIGINAL_BYTES) != -1) {
            brand_generic = new byte[] { 'B' };
        } else {
            brand_generic = new byte[] { 'G' };
        }
        //Transaction profile states to use "ol_quantity * i_price"
        final double ol_amount = ol_quantity * i_price;
        total += ol_amount;
        voltQueueSQL(createOrderLine, d_next_o_id, d_id, w_id, ol_number, ol_i_id, ol_supply_w_id, timestamp, ol_quantity, ol_amount, s_dist_xx);
        // Add the info to be returned
        item_data.addRow(i_name, s_quantity, brand_generic, i_price, ol_amount);
    }
    voltExecuteSQL();
    // Adjust the total for the discount
    total *= (1 - c_discount) * (1 + w_tax + d_tax);
    // pack up values the client is missing (see TPC-C 2.4.3.5)
    final VoltTable misc = misc_template.clone(256);
    misc.addRow(w_tax, d_tax, d_next_o_id, total);
    return new VoltTable[] { customer, misc, item_data };
}
Also used : VoltTableRow(org.voltdb.VoltTableRow) VoltTable(org.voltdb.VoltTable)

Example 62 with VoltTableRow

use of org.voltdb.VoltTableRow in project voltdb by VoltDB.

the class ostatByCustomerName method run.

public VoltTable[] run(short w_id, byte d_id, String c_last) {
    voltQueueSQL(getCustomersByLastName, w_id, d_id, c_last);
    VoltTable customers = voltExecuteSQL()[0];
    // Get the midpoint customer's id
    final int namecnt = customers.getRowCount();
    final int index = (namecnt - 1) / 2;
    final VoltTableRow customer = customers.fetchRow(index);
    final long c_id = customer.getLong(C_ID_IDX);
    // Build an VoltTable with a single customer row
    final VoltTable customerResultTable = result_template.clone(1024);
    customerResultTable.addRow(c_id, customer.getStringAsBytes(1), customer.getStringAsBytes(2), customer.getStringAsBytes(3), customer.getDouble(4));
    // Do the rest of the work
    return getOrderStatus(w_id, d_id, c_id, customerResultTable);
}
Also used : VoltTable(org.voltdb.VoltTable) VoltTableRow(org.voltdb.VoltTableRow)

Example 63 with VoltTableRow

use of org.voltdb.VoltTableRow in project voltdb by VoltDB.

the class paymentByCustomerIdC method run.

public VoltTable[] run(short w_id, byte d_id, double h_amount, short c_w_id, byte c_d_id, int c_id, TimestampType timestamp) {
    // assert (w_id == c_w_id); cross partition should be supported (at least in future)
    voltQueueSQL(getCustomersByCustomerId, c_id, c_d_id, c_w_id);
    final VoltTableRow customer = voltExecuteSQL()[0].fetchRow(0);
    return processPayment(w_id, d_id, c_w_id, c_d_id, c_id, h_amount, customer, timestamp);
}
Also used : VoltTableRow(org.voltdb.VoltTableRow)

Example 64 with VoltTableRow

use of org.voltdb.VoltTableRow in project voltdb by VoltDB.

the class TestJoinsSuite method subtestIndexOuterJoin.

/**
     * Two table left and right NLIJ
     * @throws NoConnectionsException
     * @throws IOException
     * @throws ProcCallException
     */
private void subtestIndexOuterJoin(Client client, String joinOp) throws Exception {
    client.callProcedure("R2.INSERT", 1, 1);
    client.callProcedure("R2.INSERT", 2, 2);
    client.callProcedure("R2.INSERT", 3, 3);
    client.callProcedure("R2.INSERT", 4, 4);
    String query;
    VoltTable result;
    // R2 1st joined with R3 null
    // R2 2nd joined with R3 null
    // R2 3rd joined with R3 null
    // R2 4th joined with R3 null
    query = "SELECT * FROM R2 LEFT JOIN R3 " + "ON R3.A " + joinOp + " R2.A " + "ORDER BY R2.A";
    result = client.callProcedure("@AdHoc", query).getResults()[0];
    assertEquals(4, result.getRowCount());
    VoltTableRow row = result.fetchRow(2);
    assertEquals(3, row.getLong(1));
    client.callProcedure("R3.INSERT", 1, 1);
    client.callProcedure("R3.INSERT", 2, 2);
    client.callProcedure("R3.INSERT", 5, 5);
    // R2 1st joined with R3 1st
    // R2 2nd joined with R3 2nd
    // R2 3rd joined with R3 null
    // R2 4th joined with R3 null
    query = "SELECT * FROM R2 LEFT JOIN R3 " + "ON R3.A " + joinOp + " R2.A";
    validateRowCount(client, query, 4);
    query = "SELECT * FROM R3 RIGHT JOIN R2 " + "ON R3.A " + joinOp + " R2.A";
    validateRowCount(client, query, 4);
    // Same as above but with partitioned table
    client.callProcedure("P2.INSERT", 1, 1);
    client.callProcedure("P2.INSERT", 2, 2);
    client.callProcedure("P2.INSERT", 3, 3);
    client.callProcedure("P2.INSERT", 4, 4);
    query = "SELECT * FROM P2 LEFT JOIN R3 " + "ON R3.A = P2.A";
    validateRowCount(client, query, 4);
    // R2 1st joined with R3 NULL R2.C < 0
    // R2 2nd joined with R3 null R2.C < 0
    // R2 3rd joined with R3 null R2.C < 0
    // R2 4th joined with R3 null R2.C < 0
    query = "SELECT * FROM R2 LEFT JOIN R3 " + "ON R3.A " + joinOp + " R2.A AND R2.C < 0";
    validateRowCount(client, query, 4);
    query = "SELECT * FROM R3 RIGHT JOIN R2 " + "ON R3.A " + joinOp + " R2.A AND R2.C < 0";
    validateRowCount(client, query, 4);
    // Same as above but with partitioned table
    query = "SELECT * FROM P2 LEFT JOIN R3 " + "ON R3.A " + joinOp + " P2.A AND P2.E < 0";
    // R2 1st joined with R3 null eliminated by R3.A > 1
    // R2 2nd joined with R3 2nd
    // R2 3rd joined with R3 null
    // R2 4th joined with R3 null
    query = "SELECT * FROM R2 LEFT JOIN R3 " + "ON R3.A " + joinOp + " R2.A AND R3.A > 1";
    validateRowCount(client, query, 4);
    query = "SELECT * FROM R3 RIGHT JOIN R2 " + "ON R3.A " + joinOp + " R2.A AND R3.A > 1";
    validateRowCount(client, query, 4);
    // R2 1st joined with R3 1st  but eliminated by  R3.A IS NULL
    // R2 2nd joined with R3 2nd  but eliminated by  R3.A IS NULL
    // R2 3rd joined with R3 null
    // R2 4th joined with R3 null
    query = "SELECT * FROM R2 LEFT JOIN R3 " + "ON R3.A " + joinOp + " R2.A WHERE R3.A IS NULL";
    if (joinOp.equals("=") || !isHSQL()) {
        //// PENDING HSQL flaw investigation
        validateRowCount(client, query, 2);
    } else {
        result = client.callProcedure("@AdHoc", query).getResults()[0];
        System.out.println("Ignoring erroneous(?) HSQL baseline: " + result);
        if (2 == result.getRowCount()) {
            System.out.println("The HSQL error MAY have been solved. Consider simplifying this test.");
        }
    }
    query = "SELECT * FROM R3 RIGHT JOIN R2 " + "ON R3.A " + joinOp + " R2.A WHERE R3.A IS NULL";
    if (isHSQL()) {
        //// PENDING HSQL flaw investigation
        result = client.callProcedure("@AdHoc", query).getResults()[0];
        System.out.println("Ignoring erroneous(?) HSQL baseline: " + result);
        if (2 == result.getRowCount()) {
            System.out.println("The HSQL error MAY have been solved. Consider simplifying this test.");
        }
    } else {
        validateRowCount(client, query, 2);
    }
    // Same as above but with partitioned table
    query = "SELECT * FROM R3 RIGHT JOIN P2 " + "ON R3.A " + joinOp + " P2.A WHERE R3.A IS NULL";
    if (isHSQL()) {
        //// PENDING HSQL flaw investigation
        result = client.callProcedure("@AdHoc", query).getResults()[0];
        System.out.println("Ignoring erroneous(?) HSQL baseline: " + result);
        if (2 == result.getRowCount()) {
            System.out.println("The HSQL error MAY have been solved. Consider simplifying this test.");
        }
    } else {
        validateRowCount(client, query, 2);
    }
    // R2 1st eliminated by R2.C < 0
    // R2 2nd eliminated by R2.C < 0
    // R2 3rd eliminated by R2.C < 0
    // R2 4th eliminated by R2.C < 0
    query = "SELECT * FROM R2 LEFT JOIN R3 " + "ON R3.A " + joinOp + " R2.A WHERE R2.C < 0";
    validateRowCount(client, query, 0);
    // Same as above but with partitioned table
    query = "SELECT * FROM P2 LEFT JOIN R3 " + "ON R3.A " + joinOp + " P2.A WHERE P2.E < 0";
    validateRowCount(client, query, 0);
    // Outer table index scan
    // R3 1st eliminated by R3.A > 0 where filter
    // R3 2nd joined with R3 2
    // R3 3rd joined with R2 null
    query = "select * FROM R3 LEFT JOIN R2 " + "ON R3.A " + joinOp + " R2.A WHERE R3.A > 1";
    validateRowCount(client, query, 2);
}
Also used : VoltTable(org.voltdb.VoltTable) VoltTableRow(org.voltdb.VoltTableRow)

Example 65 with VoltTableRow

use of org.voltdb.VoltTableRow in project voltdb by VoltDB.

the class TestJoinsSuite method subtestSeqOuterJoin.

/**
     * Two table left and right NLJ
     * @throws NoConnectionsException
     * @throws IOException
     * @throws ProcCallException
     */
private void subtestSeqOuterJoin(Client client, String joinOp) throws Exception {
    client.callProcedure("R1.INSERT", 1, 1, 1);
    client.callProcedure("R1.INSERT", 1, 2, 1);
    client.callProcedure("R1.INSERT", 2, 2, 2);
    client.callProcedure("R1.INSERT", -1, 3, 3);
    // R1 1st joined with R2 null
    // R1 2nd joined with R2 null
    // R1 3rd joined with R2 null
    // R1 4th joined with R2 null
    String query;
    VoltTable result;
    query = "SELECT * FROM R1 LEFT JOIN R2 " + "ON R1.A " + joinOp + " R2.C";
    result = client.callProcedure("@AdHoc", query).getResults()[0];
    //* enable to debug */ System.out.println(result);
    assertEquals(4, result.getRowCount());
    VoltTableRow row = result.fetchRow(2);
    assertEquals(2, row.getLong(1));
    client.callProcedure("R2.INSERT", 1, 1);
    client.callProcedure("R2.INSERT", 1, 3);
    client.callProcedure("R2.INSERT", 3, null);
    // R1 1st joined with R2 1st
    // R1 2nd joined with R2 1st
    // R1 3rd joined with R2 null
    // R1 4th joined with R2 null
    query = "SELECT * FROM R1 LEFT JOIN R2 " + "ON R1.A " + joinOp + " R2.C";
    validateRowCount(client, query, 4);
    query = "SELECT * FROM R2 RIGHT JOIN R1 " + "ON R1.A " + joinOp + " R2.C";
    validateRowCount(client, query, 4);
    // Same as above but with partitioned table
    client.callProcedure("P1.INSERT", 1, 1);
    client.callProcedure("P1.INSERT", 1, 2);
    client.callProcedure("P1.INSERT", 2, 2);
    client.callProcedure("P1.INSERT", -1, 3);
    query = "SELECT * FROM P1 LEFT JOIN R2 " + "ON P1.A " + joinOp + " R2.C";
    validateRowCount(client, query, 4);
    // R1 1st joined with R2 with R2 1st
    // R1 2nd joined with R2 null (failed R1.C = 1)
    // R1 3rd joined with R2 null (failed  R1.A " + joinOp + " R2.C)
    // R1 4th3rd joined with R2 null (failed  R1.A " + joinOp + " R2.C)
    query = "SELECT * FROM R1 LEFT JOIN R2 " + "ON R1.A " + joinOp + " R2.C AND R1.C = 1";
    validateRowCount(client, query, 4);
    query = "SELECT * FROM R2 RIGHT JOIN R1 " + "ON R1.A " + joinOp + " R2.C AND R1.C = 1";
    validateRowCount(client, query, 4);
    // Same as above but with partitioned table
    query = "SELECT * FROM R2 RIGHT JOIN P1 " + "ON P1.A " + joinOp + " R2.C AND P1.C = 1";
    validateRowCount(client, query, 4);
    // R1 1st joined with R2 null - eliminated by the second join condition
    // R1 2nd joined with R2 null
    // R1 3rd joined with R2 null
    // R1 4th joined with R2 null
    query = "SELECT * FROM R1 LEFT JOIN R2 " + "ON R1.A " + joinOp + " R2.C AND R2.A = 100";
    validateRowCount(client, query, 4);
    // R1 1st - joined with R2 not null and eliminated by the filter condition
    // R1 2nd - joined with R2 not null and eliminated by the filter condition
    // R1 3rd - joined with R2 null
    // R1 4th - joined with R2 null
    query = "SELECT * FROM R1 LEFT JOIN R2 " + "ON R1.A " + joinOp + " R2.C WHERE R2.A IS NULL";
    validateRowCount(client, query, 2);
    // Same as above but with partitioned table
    query = "SELECT * FROM P1 LEFT JOIN R2 " + "ON P1.A " + joinOp + " R2.C WHERE R2.A IS NULL";
    validateRowCount(client, query, 2);
    // R1 1st - joined with R2 1st row
    // R1 2nd - joined with R2 null eliminated by the filter condition
    // R1 3rd - joined with R2 null eliminated by the filter condition
    // R1 4th - joined with R2 null eliminated by the filter condition
    query = "SELECT * FROM R1 LEFT JOIN R2 " + "ON R1.A " + joinOp + " R2.C WHERE R1.C = 1";
    validateRowCount(client, query, 1);
    // R1 1st - eliminated by the filter condition
    // R1 2nd - eliminated by the filter condition
    // R1 3rd - eliminated by the filter condition
    // R1 3rd - joined with the R2 null
    query = "SELECT * FROM R1 LEFT JOIN R2 " + "ON R1.A " + joinOp + " R2.C WHERE R1.A = -1";
    validateRowCount(client, query, 1);
    //* enable to debug */ System.out.println(result);
    // Same as above but with partitioned table
    query = "SELECT * FROM P1 LEFT JOIN R2 " + "ON P1.A " + joinOp + " R2.C WHERE P1.A = -1";
    validateRowCount(client, query, 1);
    //* enable to debug */ System.out.println(result);
    // R1 1st - joined with the R2
    // R1 1st - joined with the R2
    // R1 2nd - eliminated by the filter condition
    // R1 3rd - eliminated by the filter condition
    query = "SELECT * FROM R1 LEFT JOIN R2 " + "ON R1.A " + joinOp + " R2.C WHERE R1.A = 1";
    validateRowCount(client, query, 2);
    // R1 1st - eliminated by the filter condition
    // R1 2nd - eliminated by the filter condition
    // R1 3rd - joined with R2 null and pass the filter
    // R1 4th - joined with R2 null and pass the filter
    query = "SELECT * FROM R1 LEFT JOIN R2 " + "ON R1.A " + joinOp + " R2.C WHERE R2.A is NULL";
    validateRowCount(client, query, 2);
}
Also used : VoltTable(org.voltdb.VoltTable) VoltTableRow(org.voltdb.VoltTableRow)

Aggregations

VoltTableRow (org.voltdb.VoltTableRow)65 VoltTable (org.voltdb.VoltTable)57 Client (org.voltdb.client.Client)23 ProcCallException (org.voltdb.client.ProcCallException)11 TimestampType (org.voltdb.types.TimestampType)7 NoConnectionsException (org.voltdb.client.NoConnectionsException)6 IOException (java.io.IOException)5 VoltAbortException (org.voltdb.VoltProcedure.VoltAbortException)5 Date (java.util.Date)4 WorkWithBigString (org.voltdb_testprocs.regressionsuites.sqlfeatureprocs.WorkWithBigString)4 UnsupportedEncodingException (java.io.UnsupportedEncodingException)2 ArrayList (java.util.ArrayList)2 Random (java.util.Random)2 ClientResponse (org.voltdb.client.ClientResponse)2 File (java.io.File)1 FileOutputStream (java.io.FileOutputStream)1 PrintStream (java.io.PrintStream)1 BigDecimal (java.math.BigDecimal)1 HashSet (java.util.HashSet)1 ByteBuilder (org.voltdb.benchmark.tpcc.procedures.ByteBuilder)1