@Test
public void test_两表join_单库单表_单表会优化为下推() {
TableNode table = new TableNode("TABLE1");
JoinNode join = table.join("TABLE8", "NAME", "NAME");
join.query("TABLE1.ID = 0");
IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
Assert.assertTrue(qc instanceof IJoin);
Assert.assertEquals(QUERY_CONCURRENCY.SEQUENTIAL, ((IJoin) qc).getQueryConcurrency()); // 串行
}
@Test
public void test_两表join_单表_单库多表_生成JoinMergeJoin() {
TableNode table = new TableNode("TABLE8");
JoinNode join = table.join("TABLE1", "ID", "ID");
join.query("TABLE1.ID IN (0,1)");
IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
Assert.assertTrue(qc instanceof IMerge);
Assert.assertEquals(
QUERY_CONCURRENCY.GROUP_CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 串行
}
// 两表Join查询，右表连接键为主键，右表为主键查询
// 开启了join merge join
// 右表主键索引的查询，Join列也索引列，应该选择IndexNestLoop
// 会是一个(table1 join table2 index ) join table2 key 的多级join
@Test
public void test_两表Join_主键索引_存在主键索引条件() {
TableNode table = new TableNode("TABLE1");
JoinNode join = table.join("TABLE2", "ID", "ID");
join.query("TABLE2.ID IN (1,2)");
IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
Assert.assertTrue(qc instanceof IMerge);
Assert.assertTrue(((IMerge) qc).getSubNodes().get(0) instanceof IJoin);
IJoin subJoin = (IJoin) ((IMerge) qc).getSubNodes().get(0);
Assert.assertEquals(JoinStrategy.INDEX_NEST_LOOP, subJoin.getJoinStrategy());
}
@Test
public void test_两表join_orderby_groupby_limit条件() {
TableNode table = new TableNode("TABLE1");
JoinNode join = table.join("TABLE2", "ID", "ID");
join.select(
OptimizerUtils.createColumnFromString("TABLE1.ID AS JID"),
OptimizerUtils.createColumnFromString("CONCAT(TABLE1.NAME,TABLE1.SCHOOL) AS JNAME"));
join.orderBy("JID");
join.groupBy("JNAME");
join.having("COUNT(JID) > 0");
IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
Assert.assertTrue(qc instanceof IMerge);
Assert.assertEquals(QUERY_CONCURRENCY.CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 串行
}
@Test
public void test_两表join_单独limit条件_不做并行() {
TableNode table = new TableNode("TABLE1");
JoinNode join = table.join("TABLE2", "ID", "ID");
join.select(
OptimizerUtils.createColumnFromString("TABLE1.ID AS JID"),
OptimizerUtils.createColumnFromString("CONCAT(TABLE1.NAME,TABLE1.SCHOOL) AS JNAME"));
join.limit(10, 20);
IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
Assert.assertTrue(qc instanceof IMerge);
Assert.assertEquals(QUERY_CONCURRENCY.SEQUENTIAL, ((IMerge) qc).getQueryConcurrency()); // 串行
IJoin jn = (IJoin) ((IMerge) qc).getSubNodes().get(0);
Assert.assertEquals("0", jn.getLimitFrom().toString());
Assert.assertEquals("30", jn.getLimitTo().toString());
}
// 两表Join查询，右表连接键为主键，右表为二级索引查询
// 开启了join merge join
// 右表二级索引的查询，Join列也是二级索引索引，应该选择NestLoop
// 会是一个(table1 index join table1 index ) join (table2 index join table2
// key)的多级join
@Test
public void test_两表Join_二级索引_存在二级索引条件() {
TableNode table = new TableNode("TABLE1");
JoinNode join = table.join("TABLE2", "NAME", "NAME");
join.query("TABLE2.NAME = 1");
IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(join, null, extraCmd);
Assert.assertTrue(qc instanceof IJoin);
Assert.assertTrue(((IJoin) qc).getLeftNode() instanceof IJoin);
Assert.assertTrue(((IJoin) qc).getRightNode() instanceof IMerge);
Assert.assertEquals(JoinStrategy.INDEX_NEST_LOOP, ((IJoin) qc).getJoinStrategy());
IJoin subJoin = (IJoin) ((IJoin) qc).getLeftNode();
Assert.assertTrue(((IJoin) subJoin).getLeftNode() instanceof IMerge);
Assert.assertTrue(((IJoin) subJoin).getRightNode() instanceof IMerge);
Assert.assertEquals(JoinStrategy.INDEX_NEST_LOOP, subJoin.getJoinStrategy());
}