private static void assertUnorderedResult(ResultSet rs, String expectedResult) throws Exception {
assertEquals(expectedResult, TestUtils.FormattedResult.ResultFactory.toString(rs));
}
@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));
}