Java JoinNode.select方法代码示例

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入方法依赖的package包/类
@Test
public void test_insertSelect查询_select为join查询_生成merge_设置where条件() {
    KVIndexNode table1 = new KVIndexNode("TABLE1");

    KVIndexNode table2 = new KVIndexNode("TABLE2");
    table2.keyQuery("ID IN (2,3)");
    KVIndexNode table3 = new KVIndexNode("TABLE3");
    table3.keyQuery("ID IN (2,3)");
    JoinNode join = table2.join(table3, "ID", "ID");
    join.select("TABLE2.*");

    InsertNode insert = new InsertNode(table1);
    insert.setSelectNode(join);
    insert.build();
    ASTNode qtn = shard(insert);

    Assert.assertTrue(qtn instanceof MergeNode);
    Assert.assertTrue(((MergeNode) qtn).getChild() instanceof InsertNode);
    Assert.assertEquals(2, ((MergeNode) qtn).getChildren().size());
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入方法依赖的package包/类
@Test
public void test_where条件下推_多级join() {
    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");
    TableNode table3 = new TableNode("TABLE3");

    JoinNode join = table1.join(table2);
    join.select("TABLE1.ID AS ID , TABLE1.NAME AS NAME");
    join.query("TABLE1.ID>5 AND TABLE2.ID<10 AND TABLE1.NAME = TABLE2.NAME");
    join.build();

    JoinNode nextJoin = join.join(table3);
    nextJoin.query("TABLE1.NAME = 6 AND TABLE1.ID = TABLE3.ID");
    nextJoin.build();
    FilterPusher.optimize(nextJoin);

    Assert.assertEquals("(TABLE1.NAME = 6 AND TABLE1.ID > 5)", ((JoinNode) nextJoin.getLeftNode()).getLeftNode()
        .getWhereFilter()
        .toString());
    Assert.assertEquals("(TABLE2.ID < 10 AND TABLE2.NAME = 6)", ((JoinNode) nextJoin.getLeftNode()).getRightNode()
        .getWhereFilter()
        .toString());
    Assert.assertEquals("TABLE1.ID = TABLE3.ID", nextJoin.getJoinFilter().get(0).toString());
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入方法依赖的package包/类
@Test
public void test_join条件下推_子表_case5_函数不下推() {
    TableNode table1 = new TableNode("TABLE1");
    TableNode table2 = new TableNode("TABLE2");
    table1.alias("A");
    table2.alias("B");

    JoinNode join = table1.join(table2);
    join.setJoinStrategy(JoinStrategy.INDEX_NEST_LOOP);
    join.select("A.ID AS CID, (A.NAME + A.SCHOOL) AS NAME");
    join.orderBy("CID ");
    join.orderBy("NAME"); // 这里的name为select中的函数
    join.build();

    OrderByPusher.optimize(join);

    Assert.assertEquals(0, table1.getOrderBys().size());
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入方法依赖的package包/类
public static JoinNode convertPlanToAst(IJoin join) {
    JoinNode node = new JoinNode();
    node.setRightNode(convertPlanToAst(join.getRightNode()));
    node.setLeftNode(convertPlanToAst(join.getLeftNode()));
    node.setJoinStrategy(join.getJoinStrategy());
    if (join.getLeftOuter() && join.getRightOuter()) {
        node.setInnerJoin();
    } else if (join.getLeftOuter()) {
        node.setLeftOuterJoin();
    } else if (join.getRightOuter()) {
        node.setRightOuterJoin();
    } else {
        node.setOuterJoin();
    }

    int size = join.getLeftJoinOnColumns().size();
    for (int i = 0; i < size; i++) {
        node.addJoinKeys(join.getLeftJoinOnColumns().get(i), join.getRightJoinOnColumns().get(i));
    }
    node.setOrderBys(join.getOrderBys());
    node.setLimitFrom(join.getLimitFrom());
    node.setLimitTo(join.getLimitTo());
    node.setConsistent(join.getConsistent());
    node.setResultFilter(join.getValueFilter());
    node.having(join.getHavingFilter());
    node.setAlias(join.getAlias());
    node.setGroupBys(join.getGroupBys());
    node.setSubQuery(join.isSubQuery());
    node.setOtherJoinOnFilter(join.getOtherJoinOnFilter());
    node.select((join.getColumns()));
    node.setAllWhereFilter(join.getWhereFilter());
    node.setExistAggregate(join.isExistAggregate());
    node.executeOn(join.getDataNode());
    node.setSubqueryOnFilterId(join.getSubqueryOnFilterId());
    node.setSubqueryFilter(join.getSubqueryFilter());
    node.build();
    node = node.deepCopy();
    return node;
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入方法依赖的package包/类
@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());// 串行
}
 

import com.taobao.tddl.optimizer.core.ast.query.JoinNode; //导入方法依赖的package包/类
@Test
public void test_Join左是子查询_右是TableNode_转化为最左树() {
    TableNode table1 = new TableNode("TABLE1");
    QueryNode query = new QueryNode(table1);
    query.build();

    TableNode table2 = new TableNode("TABLE2");

    JoinNode join = table1.join(table2, "NAME", "NAME");
    join.setJoinStrategy(JoinStrategy.INDEX_NEST_LOOP);
    join.query("TABLE1.NAME = 1 AND TABLE1.ID > 3 AND TABLE1.SCHOOL = 1");// 原本条件应该是加在join下的，这里省区推导的过程
    join.select("(TABLE2.ID + TABLE2.NAME) AS NEWNAME"); // 设置为函数
    join.orderBy("TABLE1.SCHOOL", false);// 增加一个隐藏列
    join.groupBy("NEWNAME");
    join.build();

    QueryTreeNode qn = FilterPusher.optimize(join);// 先把条件推导子节点上，构建子节点join
    build(table1);
    build(table2);
    qn = qn.convertToJoinIfNeed();

    Assert.assertTrue(qn instanceof JoinNode);
    Assert.assertEquals("TABLE2$_NAME.ID = TABLE2.ID", ((JoinNode) qn).getJoinFilter().get(0).toString());

    Assert.assertTrue(((JoinNode) qn).getLeftNode() instanceof JoinNode);
    Assert.assertEquals("TABLE1.NAME = TABLE2$_NAME.NAME",
        ((JoinNode) ((JoinNode) qn).getLeftNode()).getJoinFilter().get(0).toString());

    Assert.assertTrue(((JoinNode) ((JoinNode) qn).getLeftNode()).getLeftNode() instanceof JoinNode);
    JoinNode jn = (JoinNode) ((JoinNode) ((JoinNode) qn).getLeftNode()).getLeftNode();
    Assert.assertEquals("TABLE1._NAME.NAME = 1", jn.getLeftNode().getKeyFilter().toString());
    Assert.assertEquals("TABLE1._NAME.ID > 3", jn.getLeftNode().getResultFilter().toString());
    Assert.assertEquals("TABLE1.SCHOOL = 1", jn.getRightNode().getResultFilter().toString());
    Assert.assertEquals("TABLE1._NAME.ID = TABLE1.ID", jn.getJoinFilter().get(0).toString());
}