/* 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 testInnerJoinWithSubqueryLHS() throws Exception {
ResultSet rs =
methodWatcher.executeQuery(
format(
"explain select a2.pid from (select person.pid from %s) as a3 "
+ " join %s a2 "
+ " on a2.pid = a3.pid ",
spliceTableWatcher, spliceTableWatcher2));
int count = 0;
while (rs.next()) {
count++;
if (count == 4) {
String row = rs.getString(1);
String joinStrategy =
row.substring(
row.indexOf(PLAN_LINE_LEADER) + PLAN_LINE_LEADER.length(),
row.indexOf(JOIN_STRATEGY_TERMINATOR));
Assert.assertTrue(
MERGE_SORT_JOIN.equals(joinStrategy) || BROADCAST_JOIN.equals(joinStrategy));
break;
}
}
}
@Test
public void testInnerJoinWithSubquery() throws Exception {
ResultSet rs =
methodWatcher.executeQuery(
format(
"explain select a2.pid from %s a2 join "
+ "(select person.pid from %s) as a3 "
+ " on a2.pid = a3.pid ",
spliceTableWatcher2, spliceTableWatcher));
int count = 0;
while (rs.next()) {
count++;
if (count == 4) {
String row = rs.getString(1);
String joinStrategy =
row.substring(
row.indexOf(PLAN_LINE_LEADER) + PLAN_LINE_LEADER.length(),
row.indexOf(JOIN_STRATEGY_TERMINATOR));
Assert.assertThat(
"Join strategy must be either MERGE_SORT_JOIN or BROADCAST_JOIN",
joinStrategy,
anyOf(equalTo(MERGE_SORT_JOIN), equalTo(BROADCAST_JOIN)));
break;
}
}
}
@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();
}
}