@Override
protected void starting(Description description) {
try {
spliceClassWatcher.setAutoCommit(true);
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) values 1,2,3,4,5,6,7,8,9,10", spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher, spliceTableWatcher));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) values 1,2,3,4,5,6,7,8,9,10", spliceTableWatcher2));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher2, spliceTableWatcher2));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher2, spliceTableWatcher2));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher2, spliceTableWatcher2));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher2, spliceTableWatcher2));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher2, spliceTableWatcher2));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher2, spliceTableWatcher2));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) select i from %s",
spliceTableWatcher2, spliceTableWatcher2));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (i) values 1,2,3,4,5,6,7,8,9,10", spliceTableWatcher3));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (r_regionkey, r_name) values "
+ "(0, 'AFRICA'), (1, 'AMERICA'), (2, 'ASIA'), (3, 'EUROPE'), (4, 'MIDDLE EAST'), "
+ "(5, 'AMERICA'), (6, 'AMERICA'), (7, 'AMERICA'), (8, 'AMERICA'), (9, 'AMERICA')",
spliceTableRegion));
spliceClassWatcher.executeUpdate(
format(
"insert into %s (n_nationkey, n_name, n_regionkey) values "
+ "(0, 'ALGERIA', 0), "
+ "(1, 'ARGENTINA', 1), "
+ "(2, 'BRAZIL', 1), "
+ "(4, 'EGYPT', 4), "
+ "(5, 'ETHIOPIA', 0), "
+ "(6, 'FRANCE', 3)",
spliceTableNation));
spliceClassWatcher.execute(
format(
"call syscs_util.COLLECT_SCHEMA_STATISTICS('%s',false)", CLASS_NAME));
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
spliceClassWatcher.closeAll();
}
}
@SuppressWarnings("unchecked")
@Test
public void createTableWithViewJoins() throws Exception {
// DB-4170: create table with data didn't work with more than one join (the view defn is
// executed)
String nameTable = "names";
String nameTableRef = spliceSchemaWatcher.schemaName + "." + nameTable;
String nameTableDef = "(id int, fname varchar(10), lname varchar(10))";
new TableDAO(methodWatcher.getOrCreateConnection())
.drop(spliceSchemaWatcher.schemaName, nameTable);
new TableCreator(methodWatcher.getOrCreateConnection())
.withCreate(format("create table %s %s", nameTableRef, nameTableDef))
.withInsert(format("insert into %s values (?,?,?)", nameTableRef))
.withRows(
rows(
row(20, "Joe", "Blow"),
row(70, "Fred", "Ziffle"),
row(60, "Floyd", "Jones"),
row(40, "Janice", "Jones")))
.create();
String empTable = "emptab";
String empTableRef = spliceSchemaWatcher.schemaName + "." + empTable;
String empTableDef = "(empnum int, dept int, salary int)";
new TableDAO(methodWatcher.getOrCreateConnection())
.drop(spliceSchemaWatcher.schemaName, empTable);
new TableCreator(methodWatcher.getOrCreateConnection())
.withCreate(format("create table %s %s", empTableRef, empTableDef))
.withInsert(format("insert into %s values (?,?,?)", empTableRef))
.withRows(rows(row(20, 1, 75000), row(70, 3, 76000), row(60, 2, 78000), row(40, 2, 52000)))
.create();
String ssnTable = "ssn";
String ssnTableRef = spliceSchemaWatcher.schemaName + "." + ssnTable;
String ssnTableDef = "(id int, ssn int)";
new TableDAO(methodWatcher.getOrCreateConnection())
.drop(spliceSchemaWatcher.schemaName, ssnTable);
new TableCreator(methodWatcher.getOrCreateConnection())
.withCreate(format("create table %s %s", ssnTableRef, ssnTableDef))
.withInsert(format("insert into %s values (?,?)", ssnTableRef))
.withRows(rows(row(20, 11199222), row(70, 33366777), row(60, 88844777), row(40, 22200555)))
.create();
String viewName = "empsal";
String viewRef = spliceSchemaWatcher.schemaName + "." + viewName;
String viewDef =
format(
"create view %s as select distinct "
+ "A.ID, A.LNAME, A.FNAME, "
+ "B.DEPT, B.SALARY, "
+ "C.SSN "
+ "FROM %s A "
+ "LEFT OUTER JOIN %s B ON A.ID = B.EMPNUM "
+ "LEFT OUTER JOIN %s C ON A.ID = C.ID ",
viewRef, nameTableRef, empTableRef, ssnTableRef);
methodWatcher.execute(viewDef);
String depsalTable = "depsal";
String depsalTableRef = spliceSchemaWatcher.schemaName + "." + depsalTable;
String depsalTableDef =
format(
"create table %s as " + "select dept, salary, ssn from %s with data",
depsalTableRef, viewRef);
new TableDAO(methodWatcher.getOrCreateConnection())
.drop(spliceSchemaWatcher.schemaName, depsalTable);
methodWatcher.executeUpdate(depsalTableDef);
String sqlText = format("select * from %s order by dept, salary", depsalTableRef);
ResultSet rs = methodWatcher.executeQuery(sqlText);
String expected =
"DEPT |SALARY | SSN |\n"
+ "------------------------\n"
+ " 1 | 75000 |11199222 |\n"
+ " 2 | 52000 |22200555 |\n"
+ " 2 | 78000 |88844777 |\n"
+ " 3 | 76000 |33366777 |";
assertEquals(
"\n" + sqlText + "\n",
expected,
TestUtils.FormattedResult.ResultFactory.toStringUnsorted(rs));
}
