/* Regression test for DB-3614 */
@Test
@Category(SlowTest.class)
@Ignore("-sf- takes way too long to fail and interferes rest of build")
public void testTenTableJoinExplainDuration() throws Exception {
int size = 10;
List<String> tables = new ArrayList<String>(size);
List<String> joins = new ArrayList<String>(size - 1);
for (int i = 0; i < size; i++) {
methodWatcher.executeUpdate(format("create table tentab%s (c1 int primary key)", i));
methodWatcher.executeUpdate(format("insert into tentab%s values (1)", i));
tables.add(format("tentab%s", i));
if (i > 0) {
joins.add(format("tentab%s.c1 = tentab%s.c1", i, i - 1));
}
}
System.out.println("Tables created");
final String fromClause = Joiner.on(", ").join(tables);
final String joinCriteria = Joiner.on(" AND ").join(joins);
ExecutorService es =
Executors.newSingleThreadExecutor(
new ThreadFactory() {
@Override
public Thread newThread(Runnable r) {
Thread t = new Thread(r);
t.setDaemon(true);
return t;
}
});
try {
final CyclicBarrier startLatch = new CyclicBarrier(2);
final CountDownLatch finishLatch = new CountDownLatch(1);
Future<Void> f =
es.submit(
new Callable<Void>() {
@Override
public Void call() throws Exception {
String query =
format("EXPLAIN SELECT * FROM %s WHERE %s ", fromClause, joinCriteria);
startLatch.await();
try {
ResultSet rs = methodWatcher.executeQuery(query);
// Loose check that explain statement took a few seconds or less,
// because we're just making sure the short circuit logic in
// OptimizerImpl.checkTimeout() blocks this from taking several minutes.
Assert.assertTrue("Explain did not return result!", rs.next());
} finally {
finishLatch.countDown();
}
return null;
}
});
System.out.println("Starting wait");
startLatch.await();
f.get(1, TimeUnit.SECONDS);
System.out.println("Finished waiting");
} finally {
System.out.println("shutting down");
}
}
@Test
public void correlatedWhereSubqueryWithJoinInSubquery() throws Exception {
methodWatcher.executeUpdate("create table A (a1 int, a2 int)");
methodWatcher.executeUpdate("create table B (b1 int)");
methodWatcher.executeUpdate("create table C (c1 int, c2 int)");
methodWatcher.executeUpdate("create table D (d1 int)");
methodWatcher.executeUpdate("insert into A values(1,1),(2,2),(3,3),(4,4),(4,100)");
methodWatcher.executeUpdate("insert into B values(1),(2),(3),(4)");
methodWatcher.executeUpdate("insert into C values(1,1),(2,2),(3,100),(4, 100)");
methodWatcher.executeUpdate("insert into D values(1),(2),(3),(4)");
ResultSet rs =
methodWatcher.executeQuery(
""
+ "select a1,b1 from "
+ "A "
+ "join B on A.a1=B.b1 "
+ "where "
+ "A.a2 = (select max(c2) "
+ " from "
+ " C "
+ " join D on C.c1=D.d1 "
+ " where C.c1 = A.a1)");
assertUnorderedResult(
rs, "" + "A1 |B1 |\n" + "--------\n" + " 1 | 1 |\n" + " 2 | 2 |\n" + " 4 | 4 |");
}
@Test
public void nullSubqueryCompare() throws Exception {
classWatcher.executeUpdate(
"create table works8 (EMPNUM VARCHAR(3) NOT NULL, PNUM VARCHAR(3) NOT NULL,HOURS DECIMAL(5))");
classWatcher.executeUpdate(
"insert into works8 values "
+ "('E1','P1',40), ('E1','P2',20), ('E1','P3',80), ('E1','P4',20), ('E1','P5',12), "
+ "('E1','P6',12), ('E2','P1',40), ('E2','P2',80), ('E3','P2',20), ('E4','P2',20), "
+ "('E4','P4',40), ('E4','P5',80), ('E8','P8',NULL)");
ResultSet rs =
methodWatcher.executeQuery(
"SELECT EMPNUM, PNUM FROM works8 WHERE HOURS > (SELECT W2.HOURS FROM works8 W2 WHERE W2.EMPNUM = 'E8')");
assertFalse(rs.next());
}
@Test
public void testDoublyNestedNotExistsSubquery() throws Exception {
methodWatcher.executeUpdate("SET SCHEMA " + schemaWatcher.schemaName);
ResultSet rs =
methodWatcher.executeQuery(
"SELECT STAFF.EMPNAME\n"
+ " FROM STAFF\n"
+ " WHERE NOT EXISTS\n"
+ " (SELECT *\n"
+ " FROM PROJ\n"
+ " WHERE NOT EXISTS\n"
+ " (SELECT *\n"
+ " FROM WORKS\n"
+ " WHERE STAFF.EMPNUM = WORKS.EMPNUM\n"
+ " AND WORKS.PNUM=PROJ.PNUM))");
assertEquals("the returned resultset has no entry!", true, rs.next());
assertEquals("The returned result is not correct!", "Alice", rs.getString(1));
assertEquals("The returned resultset has more than 1 entry!", false, rs.next());
}
@BeforeClass
public static void createdSharedTables() throws Exception {
classWatcher.executeUpdate("create table T1 (k int, l int)");
classWatcher.executeUpdate(
"insert into T1 values (0,1),(0,1),(1,2),(2,3),(2,3),(3,4),(4,5),(4,5),(5,6),(6,7),(6,7),(7,8),(8,9),(8,9),(9,10)");
classWatcher.executeUpdate("create table T2 (k int, l int)");
classWatcher.executeUpdate(
"insert into T2 values (0,1),(1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,8),(8,9),(9,10)");
classWatcher.executeUpdate("create table T3 (i int)");
classWatcher.executeUpdate("insert into T3 values (10),(20)");
classWatcher.executeUpdate("create table T4 (i int)");
classWatcher.executeUpdate("insert into T4 values (30),(40)");
classWatcher.executeUpdate("create table T5 (k int)");
classWatcher.executeUpdate("insert into T5 values (2)");
classWatcher.executeUpdate("create table sT1 (toid int,rd int)");
classWatcher.executeUpdate(
"insert into sT1 values (1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2)");
classWatcher.executeUpdate("create table sT2 (userid int,pmnew int,pmtotal int)");
classWatcher.executeUpdate("insert into sT2 values (1,0,0),(2,0,0)");
classWatcher.executeUpdate(
"CREATE TABLE STAFF\n"
+ " (EMPNUM VARCHAR(3) NOT NULL,\n"
+ " EMPNAME VARCHAR(20),\n"
+ " GRADE DECIMAL(4),\n"
+ " CITY VARCHAR(15))");
classWatcher.executeUpdate(
"CREATE TABLE PROJ\n"
+ " (PNUM VARCHAR(3) NOT NULL,\n"
+ " PNAME VARCHAR(20),\n"
+ " PTYPE CHAR(6),\n"
+ " BUDGET DECIMAL(9),\n"
+ " CITY VARCHAR(15)) ");
classWatcher.executeUpdate(
"CREATE TABLE WORKS\n"
+ " (EMPNUM VARCHAR(3) NOT NULL,\n"
+ " PNUM VARCHAR(3) NOT NULL,\n"
+ " HOURS DECIMAL(5)\n"
+ " )");
classWatcher.getStatement().executeUpdate("insert into STAFF VALUES ('E1','Alice',12,'Deale')");
classWatcher
.getStatement()
.executeUpdate("insert into STAFF VALUES ('E2','Betty',10,'Vienna')");
classWatcher
.getStatement()
.executeUpdate("insert into STAFF VALUES ('E3','Carmen',13,'Vienna')");
classWatcher.getStatement().executeUpdate("insert into STAFF VALUES ('E4','Don',12,'Deale')");
classWatcher.getStatement().executeUpdate("insert into STAFF VALUES ('E5','Ed',13,'Akron')");
classWatcher
.getStatement()
.executeUpdate("insert into PROJ VALUES ('P1','MXSS','Design',10000,'Deale')");
classWatcher
.getStatement()
.executeUpdate("insert into PROJ VALUES ('P2','CALM','Code',30000,'Vienna')");
classWatcher
.getStatement()
.executeUpdate("insert into PROJ VALUES ('P3','SDP','Test',30000,'Tampa')");
classWatcher
.getStatement()
.executeUpdate("insert into PROJ VALUES ('P4','SDP','Design',20000,'Deale')");
classWatcher
.getStatement()
.executeUpdate("insert into PROJ VALUES ('P5','IRM','Test',10000,'Vienna')");
classWatcher
.getStatement()
.executeUpdate("insert into PROJ VALUES ('P6','PAYR','Design',50000,'Deale')");
classWatcher
.getStatement()
.executeUpdate(
"insert into WORKS VALUES ('E1','P1',40), ('E1','P3',80), ('E1','P2',20), ('E1','P4',20), ('E1','P5',12), ('E1','P6',12)");
classWatcher
.getStatement()
.executeUpdate("insert into WORKS VALUES ('E2','P1',40), ('E2','P2',80)");
classWatcher.getStatement().executeUpdate("insert into WORKS VALUES ('E3','P2',20)");
classWatcher
.getStatement()
.executeUpdate("insert into WORKS VALUES ('E4','P2',20), ('E4','P4',40), ('E4','P5',80)");
TestUtils.executeSqlFile(
classWatcher.getOrCreateConnection(), "test_data/employee.sql", SCHEMA);
TestUtils.executeSql(
classWatcher.getOrCreateConnection(),
""
+ "create table tWithNulls1 (c1 int, c2 int); \n"
+ "create table tWithNulls2 (c1 int, c2 int); \n"
+ "insert into tWithNulls1 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10); \n"
+ "insert into tWithNulls2 values (null, null), (1,1), (null, null), (2,1), (3,1), (10,10); ",
SCHEMA);
}
@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));
}
