/* This file is part of VoltDB.
* Copyright (C) 2008-2017 VoltDB Inc.
*
* Permission is hereby granted, free of charge, to any person obtaining
* a copy of this software and associated documentation files (the
* "Software"), to deal in the Software without restriction, including
* without limitation the rights to use, copy, modify, merge, publish,
* distribute, sublicense, and/or sell copies of the Software, and to
* permit persons to whom the Software is furnished to do so, subject to
* the following conditions:
*
* The above copyright notice and this permission notice shall be
* included in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
* IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
* OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
* ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
* OTHER DEALINGS IN THE SOFTWARE.
*/
package org.voltdb.regressionsuites;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Stack;
import org.apache.commons.lang3.StringUtils;
import org.voltdb.BackendTarget;
import org.voltdb.VoltTable;
import org.voltdb.client.Client;
import org.voltdb.client.ClientResponse;
import org.voltdb.client.NoConnectionsException;
import org.voltdb.client.ProcCallException;
import org.voltdb.compiler.VoltProjectBuilder;
public class TestInsertIntoSelectSuite extends RegressionSuite {
public TestInsertIntoSelectSuite(String name) {
super(name);
}
static final String vcDefault = "dachshund";
static final long intDefault = 121;
static private class ProcedureTemplate {
static List<String> partitionedSourceTables = null;
static List<String> replicatedSourceTables = null;
static List<String> partitionedAndReplicatedSourceTables = null;
private static List<String> getPartitionedSourceTables() {
if (partitionedSourceTables == null) {
partitionedSourceTables = new ArrayList<String>(2);
partitionedSourceTables.add("source_p1");
partitionedSourceTables.add("source_p2");
}
return partitionedSourceTables;
}
private static List<String> getReplicatedSourceTables() {
if (replicatedSourceTables == null) {
replicatedSourceTables = new ArrayList<String>(2);
replicatedSourceTables.add("source_r1");
replicatedSourceTables.add("source_r2");
}
return replicatedSourceTables;
}
private static List<String> getPartitionedAndReplicatedSourceTables() {
if (partitionedAndReplicatedSourceTables == null) {
partitionedAndReplicatedSourceTables = new ArrayList<String>();
partitionedAndReplicatedSourceTables.addAll(getPartitionedSourceTables());
partitionedAndReplicatedSourceTables.addAll(getReplicatedSourceTables());
}
return partitionedAndReplicatedSourceTables;
}
private static final String partitionedTargetTable = "target_p";
private static final String replicatedTargetTable = "target_r";
// Instance Data
private final String m_queryFormat;
private final String m_label;
private int m_comboCounter = 0;
private final Map<String, List<String>> m_procNameToStmts = new HashMap<>();
ProcedureTemplate(String label, String queryFormat) {
m_queryFormat = queryFormat;
m_label = label;
generateStatements();
}
private void formatQueryAndGenerateStatements(String queryFormat,
boolean partitionProcedure,
String targetTable,
Collection<String> sourceTables1,
Collection<String> sourceTables2) {
Stack<String> formatStack = new Stack<String>();
formatStack.push(queryFormat);
while (! formatStack.empty()) {
String format = formatStack.pop();
int numTablesNeeded = StringUtils.countMatches(format, "%s");
if (numTablesNeeded > 0) {
Collection<String> whichSet = null;
if (numTablesNeeded == 2 ) {
// First table should be from
whichSet = sourceTables1;
} else {
whichSet = sourceTables2;
}
for (String sourceTable : whichSet) {
String newFormat = format.replaceFirst("%s", sourceTable);
formatStack.push(newFormat);
}
} else {
generateStatementsForProcedure(partitionProcedure, targetTable, format);
}
}
}
private void generateStatements() {
int numParams = StringUtils.countMatches(m_queryFormat, "?");
if (numParams > 0) {
// generate stored procedures from this template that insert into a partitioned table,
// selecting from both partitioned and replicated tables.
// partitioned the stored procedures
formatQueryAndGenerateStatements(m_queryFormat, true, partitionedTargetTable,
getPartitionedAndReplicatedSourceTables(), getPartitionedAndReplicatedSourceTables());
}
// As above, except that stored procedures are not marked as single-partition
formatQueryAndGenerateStatements(m_queryFormat, false, partitionedTargetTable,
getPartitionedSourceTables(), getPartitionedAndReplicatedSourceTables());
// generated procedures that insert into replicated tables, selecting from replicated tables
formatQueryAndGenerateStatements(m_queryFormat, false, replicatedTargetTable,
getReplicatedSourceTables(), getReplicatedSourceTables());
}
private String generateProcedureName(boolean partitioned, String targetTable) {
String procName = "insert_into_select_" + m_label + "_" + targetTable;
String combo = String.format("_combo%02d", (Object)m_comboCounter);
m_comboCounter++;
procName += combo;
if (partitioned) {
procName += "_partitioned";
}
return procName;
}
private void generateStatementsForProcedure(boolean partitionProcedure, String targetTable, String query) {
String procName = generateProcedureName(partitionProcedure, targetTable);
// Create a map
//
// procedureName -> Ad hoc statement
// create procedure statement (and maybe partition procedure statement)
// verify procedure
// result set produced by HSQL?
StringBuilder adHocStmt = new StringBuilder();
adHocStmt.append("INSERT INTO " + targetTable + "\n");
adHocStmt.append(" " + query + ";\n");
StringBuilder insertProc = new StringBuilder();
insertProc.append("\nCREATE PROCEDURE " + procName + " AS\n");
insertProc.append(adHocStmt.toString());
if (partitionProcedure) {
insertProc.append("PARTITION PROCEDURE " + procName + " ON TABLE " + targetTable + " COLUMN bi;\n");
}
StringBuilder verifyProc = new StringBuilder();
verifyProc.append("\nCREATE PROCEDURE verify_" + procName + " AS \n");
verifyProc.append(" " + query + "\n");
verifyProc.append(" ORDER BY 1, 2, 3, 4;\n");
ArrayList<String> stmts = new ArrayList<>();
stmts.add(adHocStmt.toString());
stmts.add(insertProc.toString());
stmts.add(verifyProc.toString());
m_procNameToStmts.put(procName, stmts);
}
Map<String, List<String>> getGeneratedStatements() {
return m_procNameToStmts;
}
}
static final ProcedureTemplate procedureTemplates[] = new ProcedureTemplate[] {
new ProcedureTemplate("simple",
"select * from %s where bi = ?"),
new ProcedureTemplate("simple_noparam",
"select * from %s"),
new ProcedureTemplate("join",
"select t1.bi, t1.vc, t2.ii, t2.ti " +
"from %s as t1 inner join %s as t2 on t1.bi = t2.bi and t1.ii = t2.ii " +
"where t1.bi = ?"),
new ProcedureTemplate("join_noparam",
"select t1.bi, t1.vc, t2.ii, t2.ti " +
"from %s as t1 inner join %s as t2 on t1.bi = t2.bi and t1.ii = t2.ii"),
new ProcedureTemplate("subquery",
"select * " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from %s) as t1_subq " +
"where t1_subq.bi = ?"),
new ProcedureTemplate("subquery_noparam",
"select * " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from %s) as t1_subq"),
new ProcedureTemplate("subquery_inner_filter",
"select * " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from %s where bi = ?) as t1_subq"),
new ProcedureTemplate("subquery_inner_filter_noparam",
"select * " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from %s) as t1_subq"),
new ProcedureTemplate("subquery_join",
"select t1_subq.bi, t1_subq.vc, t2.ii, t2.ti " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from %s) as t1_subq " +
"inner join %s as t2 on t1_subq.bi = t2.bi and t1_subq.ii = t2.ii " +
"where t1_subq.bi = ?"),
new ProcedureTemplate("subquery_join_noparam",
"select t1_subq.bi, t1_subq.vc, t2.ii, t2.ti " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from %s) as t1_subq " +
"inner join %s as t2 on t1_subq.bi = t2.bi and t1_subq.ii = t2.ii"),
new ProcedureTemplate("join_two_subqueries",
"select t1_subq.bi, t1_subq.vc, t2_subq.ii, t2_subq.ti " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from %s) as t1_subq " +
"inner join (select bi, '2nd_subq + ' || vc as vc, ii, ti from %s) as t2_subq " +
"on t1_subq.bi = t2_subq.bi and t1_subq.ii = t2_subq.ii " +
"where t1_subq.bi = ?"),
new ProcedureTemplate("join_two_subqueries_noparam",
"select t1_subq.bi, t1_subq.vc, t2_subq.ii, t2_subq.ti " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from %s) as t1_subq " +
"inner join (select bi, '2nd_subq + ' || vc as vc, ii, ti from %s) as t2_subq " +
"on t1_subq.bi = t2_subq.bi and t1_subq.ii = t2_subq.ii"),
new ProcedureTemplate("nest_subqueries",
"select t1_subq.bi, t1_subq.vc, t2_subq.ii, t2_subq.ti " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from " +
"(select bi, 'nested ' || vc as vc, ii, ti from %s) as t1_subq_subq) as t1_subq " +
"inner join (select bi, '2nd_subq + ' || vc as vc, ii, ti from %s) as t2_subq " +
"on t1_subq.bi = t2_subq.bi and t1_subq.ii = t2_subq.ii " +
"where t1_subq.bi = ?"),
new ProcedureTemplate("nest_subqueries_noparam",
"select t1_subq.bi, t1_subq.vc, t2_subq.ii, t2_subq.ti " +
"from (select bi, 'subq + ' || vc as vc, ii, ti from " +
"(select bi, 'nested ' || vc as vc, ii, ti from %s) as t1_subq_subq) as t1_subq " +
"inner join (select bi, '2nd_subq + ' || vc as vc, ii, ti from %s) as t2_subq " +
"on t1_subq.bi = t2_subq.bi and t1_subq.ii = t2_subq.ii")
};
private static Map<String, List<String>> generatedStmtMap = null;
private static Map<String, List<String>> mapOfAllGeneratedStatements() {
if (generatedStmtMap == null) {
generatedStmtMap = new HashMap<>();
for (ProcedureTemplate t : procedureTemplates) {
for (Map.Entry<String, List<String>> e : t.getGeneratedStatements().entrySet()) {
generatedStmtMap.put(e.getKey(), e.getValue());
}
}
}
return generatedStmtMap;
}
private static int numberOfParametersNeeded(String procName) {
List<String> stmts = mapOfAllGeneratedStatements().get(procName);
int numParams = StringUtils.countMatches(stmts.get(0), "?");
return numParams;
}
private static List<String> generatedProcedures() {
List<String> procs = new ArrayList<>();
for (List<String> stmts : mapOfAllGeneratedStatements().values()) {
procs.add(stmts.get(1));
procs.add(stmts.get(2));
}
return procs;
}
static private String generateSchema() {
StringBuilder sb = new StringBuilder();
// Target tables: 1 partitioned, 1 replicated
// Source tables: 2 partitioned, 2 replicated
sb.append(
"CREATE TABLE target_p (bi bigint not null," +
"vc varchar(100) default '" + vcDefault +"'," +
"ii integer default " + intDefault + "," +
"ti tinyint default " + intDefault + " not null);" +
"partition table target_p on column bi;" +
"CREATE TABLE target_r (bi bigint not null," +
"vc varchar(100) default '" + vcDefault +"'," +
"ii integer default " + intDefault + "," +
"ti tinyint default " + intDefault + " not null);" +
"CREATE TABLE source_p1 (bi bigint not null," +
"vc varchar(100)," +
"ii integer," +
"ti tinyint);" +
"partition table source_p1 on column bi;" +
"CREATE TABLE source_p2 (bi bigint not null," +
"vc varchar(100)," +
"ii integer," +
"ti tinyint);" +
"partition table source_p2 on column bi;" +
"CREATE TABLE source_r1 (bi bigint not null," +
"vc varchar(100)," +
"ii integer," +
"ti tinyint);" +
"CREATE TABLE source_r2 (bi bigint not null," +
"vc varchar(4)," +
"ii integer," +
"ti tinyint);"
);
sb.append(
// select all rows from target tables, to verify inserted rows
"create procedure get_all_target_p_rows as select * from target_p order by bi, vc, ii, ti;" +
"create procedure get_all_target_r_rows as select * from target_r order by bi, vc, ii, ti;" +
// A very simple insert into select statement
"create procedure insert_p_source_p as insert into target_p (bi, vc, ii, ti) select * from source_p1 where bi = ?;" +
"partition procedure insert_p_source_p on table target_p column bi;" +
// an insert into select statement that makes use of default values
"create procedure insert_p_use_defaults as insert into target_p (bi, ti) select bi, ti from source_p1 where bi = ?;" +
"partition procedure insert_p_use_defaults on table target_p column bi;" +
// an insert into select statement with unordered columns
"create procedure insert_p_use_defaults_reorder as insert into target_p (ti, bi) select ti, bi from source_p1 where bi = ?;" +
"partition procedure insert_p_use_defaults_reorder on table target_p column bi;" +
// group by in the subquery
"create procedure insert_p_source_p_agg as insert into target_p (bi, vc, ii, ti) " +
"select bi, max(vc), max(ii), min(ti)" + " from source_p1 where bi = ? group by bi;" +
"partition procedure insert_p_source_p_agg on table target_p column bi;" +
// transpose ti, ii, columns so there are implicit integer->tinyint and tinyint->integer casts
"create procedure insert_p_source_p_cast as insert into target_p (bi, vc, ti, ii) select * from source_p1 where bi = ?;" +
"partition procedure insert_p_source_p_cast on table target_p column bi;" +
// source_p2.ii contains values that will not fit into tinyint, so this procedure should throw an out-of-range conversion exception
"create procedure insert_p_source_p_cast_out_of_range as " +
"insert into target_p (bi, vc, ti, ii) " +
"select * from source_p2 where bi = ?;" +
"partition procedure insert_p_source_p_cast_out_of_range on table target_p column bi;" +
// Implicit string->int and int->string conversion.
"create procedure insert_p_source_p_nonsensical_cast as insert into target_p (bi, ii, vc, ti) select * from source_p1 where bi = ?;" +
"partition procedure insert_p_source_p_nonsensical_cast on table target_p column bi;" +
// Target table and source table the same
"create procedure select_and_insert_into_source as " +
"insert into source_p1 (bi, vc, ti, ii) select bi, vc, ti, 1000 * ii from source_p1 where bi = ? order by bi, ti;" +
"partition procedure select_and_insert_into_source on table source_p1 column bi;" +
// HSQL seems to want a cast for the parameter
// Note that there is no filter in source_r2
"create procedure insert_param_in_select_list as " +
"insert into target_p (bi, vc, ii, ti) " +
"select cast(? as bigint), vc, ii, ti from source_r2 order by ii;" +
"partition procedure insert_param_in_select_list on table target_p column bi;" +
// try to insert into the wrong partition
"create procedure insert_wrong_partition as " +
"insert into target_p (bi, ti) select ti, cast(? as tinyint) from source_r2; " +
"partition procedure insert_wrong_partition on table target_p column bi; " +
// try to violate a not null constraint
"create procedure insert_select_violate_constraint as " +
"insert into target_p (bi, ti) " +
"select bi, case ti when 55 then null else ti end from source_p1 where bi = ? order by ti asc;" +
"partition procedure insert_select_violate_constraint on table target_p column bi; " +
""
);
// Generate CREATE STORED PROCEDURE, PARTITION STORED PROCEDURE statements from each procedure template,
// as well as verify procedures for checking results
for (String proc : generatedProcedures()) {
sb.append(proc);
}
return sb.toString();
}
static public junit.framework.Test suite() {
VoltServerConfig config = null;
final MultiConfigSuiteBuilder builder = new MultiConfigSuiteBuilder(TestInsertIntoSelectSuite.class);
final VoltProjectBuilder project = new VoltProjectBuilder();
try {
project.addLiteralSchema(generateSchema());
} catch (IOException error) {
fail(error.getMessage());
}
boolean success;
// JNI
config = new LocalCluster("iisf-onesite.jar", 1, 1, 0, BackendTarget.NATIVE_EE_JNI);
success = config.compile(project);
assertTrue(success);
builder.addServerConfig(config);
// CLUSTER (disable to opt for speed over coverage...
config = new LocalCluster("iisf-cluster.jar", 2, 3, 1, BackendTarget.NATIVE_EE_JNI);
success = config.compile(project);
assertTrue(success);
builder.addServerConfig(config);
// ... disable for speed) */
config = new LocalCluster("iisf-hsql.jar", 1, 1, 0, BackendTarget.HSQLDB_BACKEND);
success = config.compile(project);
assert(success);
builder.addServerConfig(config);
return builder;
}
private static void clearTargetTables(Client client) throws Exception {
ClientResponse resp = client.callProcedure("@AdHoc", "delete from target_p");
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("@AdHoc", "delete from target_r");
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
}
private static void clearTables(Client client) throws Exception {
ClientResponse resp = client.callProcedure("@AdHoc", "delete from source_p1");
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("@AdHoc", "delete from source_p2");
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("@AdHoc", "delete from source_r1");
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("@AdHoc", "delete from source_r2");
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
clearTargetTables(client);
}
private static void initializeTables(Client client) throws Exception {
ClientResponse resp = null;
clearTables(client);
for (int i=0; i < 10; i++) {
resp = client.callProcedure("SOURCE_P1.insert", i, Long.toHexString(i), i, i);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_P1.insert", i, Long.toHexString(-i), -i, -i);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_P1.insert", i, Long.toHexString(i * 11), i * 11, i * 11);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_P1.insert", i, Long.toHexString(i * -11), i * -11, i * -11);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_R1.insert", i, Long.toHexString(i), i, i);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_R1.insert", i, Long.toHexString(-i), -i, -i);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_R1.insert", i, Long.toHexString(i * 11), i * 11, i * 11);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_R1.insert", i, Long.toHexString(i * -11), i * -11, i * -11);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
int j = i + 5;
resp = client.callProcedure("SOURCE_P2.insert", j, Long.toHexString(j), j, j);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_P2.insert", j, Long.toHexString(-j), -j, -j);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_P2.insert", j, Long.toHexString(j * 11), j * 11, (j * 11) % 128);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_P2.insert", j, Long.toHexString(j * -11), j * -11, -((j * 11) % 128));
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_R2.insert", j, Long.toHexString(j), j, j);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_R2.insert", j, Long.toHexString(-j).substring(0, 3), -j, -j);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_R2.insert", j, Long.toHexString(j * 11), j * 11, (j * 11) % 128);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
resp = client.callProcedure("SOURCE_R2.insert", j, Long.toHexString(j * -11).substring(0, 3), j * -11, -((j * 11) % 128));
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
}
}
private static VoltTable getRows(Client client, String adHocQuery) throws NoConnectionsException, IOException, ProcCallException {
ClientResponse resp = client.callProcedure("@AdHoc", adHocQuery);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
return resp.getResults()[0];
}
public void testPartitionedTableSimple() throws Exception
{
final Client client = getClient();
ClientResponse resp;
// Running the procedure with the first parameter (100) will cause 0 rows to be inserted
// The second parameter (5) will insert 4 rows into the target table
long[] params = new long[] {100, 5};
String[] procs = new String[] {"insert_p_source_p", "insert_p_source_p_cast"};
for (long param : params) {
for (String proc : procs) {
initializeTables(client);
resp = client.callProcedure(proc, param);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
long numRowsInserted = resp.getResults()[0].asScalarLong();
// verify that the corresponding rows in both tables are the same
String selectAllSource = "select * from source_p1 where bi = " + param + " order by bi, ii";
String selectAllTarget = "select * from target_p order by bi, ii";
resp = client.callProcedure("@AdHoc", selectAllSource);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
VoltTable sourceRows = resp.getResults()[0];
resp = client.callProcedure("@AdHoc", selectAllTarget);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
VoltTable targetRows = resp.getResults()[0];
int i = 0;
while(targetRows.advanceRow()) {
assertEquals(true, sourceRows.advanceRow());
assertEquals(sourceRows.getLong(0), targetRows.getLong(0));
assertEquals(sourceRows.getString(1), targetRows.getString(1));
assertEquals(sourceRows.getLong(2), targetRows.getLong(2));
assertEquals(sourceRows.getLong(3), targetRows.getLong(3));
i++;
}
assertEquals(numRowsInserted, i);
}
}
}
public void testSelectWithAggregation() throws Exception {
final Client client = getClient();
final long partitioningValue = 7;
initializeTables(client);
ClientResponse resp = client.callProcedure("insert_p_source_p_agg", partitioningValue);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
validateTableOfScalarLongs(resp.getResults()[0], new long[] {1});
resp = client.callProcedure("@AdHoc", "select * from target_p order by bi");
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
VoltTable targetRows = resp.getResults()[0];
assertTrue(targetRows.advanceRow());
assertEquals(partitioningValue, targetRows.getLong(0));
assertEquals(Long.toHexString(-partitioningValue), targetRows.getString(1));
assertEquals(partitioningValue * 11, targetRows.getLong(2));
assertEquals(partitioningValue * -11, targetRows.getLong(3));
assertFalse(targetRows.advanceRow());
}
public void testOutOfRangeImplicitCasts() throws Exception {
final Client client = getClient();
final long partitioningValue = 14;
initializeTables(client);
verifyProcFails(client, "out of range", "insert_p_source_p_cast_out_of_range", partitioningValue);
}
public void testNonsensicalCasts() throws Exception {
final Client client = getClient();
final long partitioningValue = 5;
initializeTables(client);
verifyProcFails(client, "invalid character value",
"insert_p_source_p_nonsensical_cast", partitioningValue);
}
public void testInsertIntoSelectWithDefaults() throws Exception {
final Client client = getClient();
ClientResponse resp;
long partitioningValue = 8;
// Both inserts use the select to produce values only for a subset of columns.
String[] procs = new String[] {"insert_p_use_defaults", "insert_p_use_defaults_reorder"};
for (String proc : procs) {
initializeTables(client);
resp = client.callProcedure(proc, partitioningValue);
validateTableOfScalarLongs(resp.getResults()[0], new long[] {4});
String selectSp1 = "select * from source_p1 where bi = ? order by bi, ti";
String selectTarget = "select * from target_p order by bi, ti";
resp = client.callProcedure("@AdHoc", selectTarget);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
VoltTable targetRows = resp.getResults()[0];
resp = client.callProcedure("@AdHoc", selectSp1, partitioningValue);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
VoltTable sp1Rows = resp.getResults()[0];
while (targetRows.advanceRow()) {
assertTrue(sp1Rows.advanceRow());
assertEquals(sp1Rows.getLong(0), targetRows.getLong(0));
assertEquals(vcDefault, targetRows.getString(1));
assertEquals(intDefault, targetRows.getLong(2));
assertEquals(sp1Rows.getLong(3), targetRows.getLong(3));
}
assertFalse(sp1Rows.advanceRow());
}
}
public void testInsertIntoSelectSameTable() throws Exception {
final Client client = getClient();
initializeTables(client);
final long partitioningValue = 3;
ClientResponse resp = client.callProcedure("select_and_insert_into_source", partitioningValue);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
validateTableOfScalarLongs(resp.getResults()[0], new long[] {4});
String selectOrigRows = "select * from source_p1 where bi = ? and abs(ii) < 1000 order by bi, ii";
String selectNewRows = "select * from source_p1 where bi = ? and abs(ii) > 1000 order by bi, ii";
resp = client.callProcedure("@AdHoc", selectOrigRows, partitioningValue);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
VoltTable origRows = resp.getResults()[0];
resp = client.callProcedure("@AdHoc", selectNewRows, partitioningValue);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
VoltTable newRows = resp.getResults()[0];
while (origRows.advanceRow()) {
assertTrue(newRows.advanceRow());
assertEquals(origRows.getLong(0), newRows.getLong(0));
assertEquals(origRows.getString(1), newRows.getString(1));
assertEquals(origRows.getLong(2) * 1000, newRows.getLong(2));
assertEquals(origRows.getLong(3), newRows.getLong(3));
}
assertFalse(newRows.advanceRow());
}
public void testSelectListParam() throws Exception {
final Client client = getClient();
initializeTables(client);
final long partitioningValue = 7;
ClientResponse resp = client.callProcedure("insert_param_in_select_list", partitioningValue);
assertEquals(ClientResponse.SUCCESS, resp.getStatus());
// tables should be identical except for "bi"
VoltTable sourceRows = getRows(client, "select * from source_r2 order by ii");
VoltTable targetRows = getRows(client, "select * from target_p order by ii");
//fail("target: " + targetRows);
while (sourceRows.advanceRow()) {
assertTrue(targetRows.advanceRow());
assertEquals(partitioningValue, targetRows.getLong(0));
assertEquals(sourceRows.getString(1), targetRows.getString(1));
assertEquals(sourceRows.getLong(2), targetRows.getLong(2));
assertEquals(sourceRows.getLong(3), targetRows.getLong(3));
}
assertFalse(targetRows.advanceRow());
}
public void testViolateConstraint() throws Exception {
final Client client = getClient();
initializeTables(client);
final long partitioningValue = 5;
verifyProcFails(client, "CONSTRAINT VIOLATION", "insert_select_violate_constraint", partitioningValue);
// the insert statement violated a constraint so there should still be no data in the table
validateTableOfLongs(client , "select count(*) from target_p", new long[][] {{0}});
}
public void testInsertWrongPartitionFails() throws Exception {
if (m_config.getNodeCount() > 1) {
Client client = getClient();
initializeTables(client);
final long partitioningValue = 9;
verifyProcFails(client, "Mispartitioned tuple in single-partition insert statement.",
"insert_wrong_partition", partitioningValue);
}
}
public void testFailureToPlan() throws Exception {
// queries which try to copy rows from one partition to another should fail
Client client = getClient();
initializeTables(client);
verifyStmtFails(client, "insert into target_p " +
"select sp1.bi, sp2.vc, sp1.ii, sp2.ti " +
"from source_p1 as sp1 inner join source_p2 as sp2 " +
"on sp1.ii = sp2.ii",
"This query is not plannable. The planner cannot guarantee that all rows would be in a single partition.");
verifyStmtFails(client, "insert into target_r " +
"select sr1.bi, sr1.vc, sr1.ii, sr1.ti " +
"from source_r1 as sr1, source_p1 as sp1 where sr1.bi = sp1.bi",
"statement may not access partitioned data for insertion into replicated table");
verifyStmtFails(client, "insert into target_p (vc, ii, ti) " +
"select vc, ii, ti from source_p1",
"Partitioning column must be assigned a value produced " +
"by the subquery in an INSERT INTO ... SELECT statement.");
verifyStmtFails(client, "insert into target_p " +
"select bi + 1, vc, ii, ti from source_p1",
"Partitioning could not be determined for INSERT INTO ... SELECT statement");
// two fragment plan for subquery
verifyStmtFails(client, "insert into target_p " +
"select max(bi), max(vc), ii, min(ti) from source_p2 " +
"group by source_p2.ii",
"INSERT INTO ... SELECT statement subquery is too complex");
}
public void testSelectListConstants() throws Exception {
Client client = getClient();
// This statements illustrate existing limitations
// of partitioning inference.
//
// Constants in the select list of the subquery
// do not help refine partitioning.
// In this example, the subquery is multipart, but
// we are only inserting into one partition---only two fragments
// are required in this plan.
verifyStmtFails(client, "insert into target_p " +
"select 9, vc, ii, ti " +
"from source_p1 as sp1",
"Partitioning could not be determined");
// this whole statement should be single-partition!
verifyStmtFails(client, "insert into target_p " +
"select 9, vc, ii, ti " +
"from source_p1 as sp1 where sp1.bi = 9",
"Partitioning could not be determined");
// Note however that this issue is not specific to
// INSERT INTO ... SELECT. This fails to plan as well:
verifyStmtFails(client,
"select count(*) " +
"from target_p " +
"inner join " +
"(select 9 as bi, vc, ii, ti from source_p1) as ins_sq " +
"on target_p.bi = ins_sq.bi",
"This query is not plannable. "
+ "The planner cannot guarantee that all rows would be in a single partition.");
}
public void testInsertIntoSelectGeneratedProcs() throws Exception
{
Set<Map.Entry<String, List<String>>> allEntries = mapOfAllGeneratedStatements().entrySet();
System.out.println("\n\nRUNNING testInsertIntoSelectGeneratedProcs with " +
allEntries.size() + " stored procedures\n\n");
final Client client = getClient();
initializeTables(client);
for (long partitioningValue = 4; partitioningValue < 11; partitioningValue++) {
for (Map.Entry<String, List<String>> e : allEntries) {
clearTargetTables(client);
// The strategy here is:
// Insert rows via stored procedure that invokes INSERT INTO ... <some_query>.
// Select the inserted rows back, compare with the table produced by <some_query>,
// verify the tables are equal.
// Do the same verification with ad hoc SQL.
String proc = e.getKey();
boolean needsParams = (numberOfParametersNeeded(proc) > 0);
String prefix = "Assertion failed running stored procedure " + proc + ": ";
// insert rows with stored procedure
ClientResponse resp;
if (needsParams) {
resp = client.callProcedure(proc, partitioningValue);
}
else {
resp = client.callProcedure(proc);
}
assertEquals(prefix + "procedure call failed", ClientResponse.SUCCESS, resp.getStatus());
VoltTable insertResult = resp.getResults()[0];
insertResult.advanceRow();
// make sure we actually inserted something
long numRowsInserted = insertResult.getLong(0);
// fetch the rows we just inserted
if (proc.contains("target_p")) {
resp = client.callProcedure("get_all_target_p_rows");
}
else {
resp = client.callProcedure("get_all_target_r_rows");
}
assertEquals(prefix + "could not fetch rows of target table", ClientResponse.SUCCESS, resp.getStatus());
VoltTable actualRows = resp.getResults()[0];
if (needsParams) {
resp = client.callProcedure("verify_" + proc, partitioningValue);
}
else {
resp = client.callProcedure("verify_" + proc);
}
// Fetch the rows we expect to have inserted
assertEquals(prefix + "could not verify rows of target table", ClientResponse.SUCCESS, resp.getStatus());
VoltTable expectedRows = resp.getResults()[0];
assertTablesAreEqual(prefix, expectedRows, actualRows);
int actualNumRows = actualRows.getRowCount();
assertEquals(prefix + "insert statement returned " + numRowsInserted + " but only " + actualNumRows + " rows selected from target table",
actualNumRows, numRowsInserted);
// Now try the corresponding ad hoc statement
String adHocQuery = e.getValue().get(0);
prefix = "Assertion failed running ad hoc SQL: " + adHocQuery;
clearTargetTables(client);
// insert rows with stored procedure
if (needsParams) {
resp = client.callProcedure("@AdHoc", adHocQuery, partitioningValue);
}
else {
resp = client.callProcedure("@AdHoc", adHocQuery);
}
assertEquals(prefix + "ad hoc statement failed", ClientResponse.SUCCESS, resp.getStatus());
insertResult = resp.getResults()[0];
insertResult.advanceRow();
numRowsInserted = insertResult.getLong(0);
// fetch the rows we just inserted
if (proc.contains("target_p")) {
resp = client.callProcedure("get_all_target_p_rows");
}
else {
resp = client.callProcedure("get_all_target_r_rows");
}
assertEquals(prefix + "could not fetch rows of target table", ClientResponse.SUCCESS, resp.getStatus());
actualRows = resp.getResults()[0];
expectedRows.resetRowPosition();
assertTablesAreEqual(prefix, expectedRows, actualRows);
actualNumRows = actualRows.getRowCount();
assertEquals(prefix + "insert statement returned " + numRowsInserted + " but only " + actualNumRows + " rows selected from target table",
actualNumRows, numRowsInserted);
}
}
}
}