Exemple #1
0
  /** 把query中的avg换成count,sum */
  @Override
  public IDataNodeExecutor optimize(
      IDataNodeExecutor dne,
      Map<Integer, ParameterContext> parameterSettings,
      Map<String, Object> extraCmd) {
    if (dne instanceof IMerge && ((IMerge) dne).getSubNode().size() > 1) {
      for (IDataNodeExecutor sub : ((IMerge) dne).getSubNode()) {
        expendAvgFunction(sub);
      }

      for (IDataNodeExecutor sub : ((IMerge) dne).getSubNode()) {
        this.optimize(sub, parameterSettings, extraCmd);
      }
    } else if (dne instanceof IJoin) {
      IJoin join = (IJoin) dne;
      // join函数,采取map模式,不需要处理avg展开
      // 递归处理子节点
      this.optimize(join.getLeftNode(), parameterSettings, extraCmd);
      this.optimize(join.getRightNode(), parameterSettings, extraCmd);
    } else if (dne instanceof IQuery) {
      IQuery query = (IQuery) dne;
      // 如果是子查询,采取map模式,不需要处理avg展开
      if (query.isSubQuery()) {
        this.optimize(query.getSubQuery(), parameterSettings, extraCmd); // 递归处理子节点
      }
    }

    return dne;
  }
Exemple #2
0
  // 单表非主键无索引查询
  // SCHOOL上不存在索引
  // 所以会执行全表扫描
  // 只会生成一个IQuery
  // SCHOOL=1作为valueFilter
  @Test
  public void test_单表查询_非任何索引条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("SCHOOL = 1");

    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(
        QUERY_CONCURRENCY.GROUP_CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 并行
    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IQuery);
    IQuery query = (IQuery) dne;
    Assert.assertEquals("TABLE1.SCHOOL = 1", query.getValueFilter().toString());
  }
Exemple #3
0
  @Test
  public void test_单表查询_无条件() {
    TableNode table = new TableNode("TABLE1");
    QueryTreeNode qn = table;
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(qn, null, extraCmd);

    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(QUERY_CONCURRENCY.SEQUENTIAL, ((IMerge) qc).getQueryConcurrency()); // 串行
    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IQuery);
    IQuery query = (IQuery) dne;
    Assert.assertEquals(null, query.getKeyFilter());
    Assert.assertEquals(null, query.getValueFilter());
  }
Exemple #4
0
  @Test
  public void testQuery_子查询_exist模式() throws SqlParserException {
    String sql =
        "SELECT * FROM TABLE1 WHERE  EXISTS (SELECT ID FROM TABLE2 WHERE TABLE2.NAME = TABLE1.NAME)";
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(sql, null, extraCmd, true);
    Assert.assertTrue(qc instanceof IMerge);

    IQuery query1 = (IQuery) ((IMerge) qc).getSubNodes().get(0);
    IQuery query2 = (IQuery) ((IMerge) qc).getSubNodes().get(1);

    IFunction subquery1 = (IFunction) query1.getSubqueryFilter().getArgs().get(0);
    IFunction subquery2 = (IFunction) query2.getSubqueryFilter().getArgs().get(0);
    Assert.assertTrue(subquery1 == subquery2);
    Assert.assertTrue((IQuery) subquery1.getArgs().get(0) instanceof IQuery);
  }
Exemple #5
0
  // 单表非主键索引查询
  // NAME上存在索引
  // 会生成一个Join节点
  // 左边通过NAME索引找到满足条件的PK,keyFilter应该为NAME=1
  // 与pk->data Join
  // Join类型为IndexNestLoop
  @Test
  public void test_单表查询_value条件() {
    TableNode table = new TableNode("TABLE1");
    table.query("NAME = 1");

    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(
        QUERY_CONCURRENCY.GROUP_CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 并行
    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IJoin);
    IJoin join = (IJoin) dne;
    IQuery left = (IQuery) join.getLeftNode();
    Assert.assertEquals("TABLE1._NAME.NAME = 1", left.getKeyFilter().toString());
  }
Exemple #6
0
 @Override
 public ISchematicCursor getCursor(
     ExecutionContext executionContext, IndexMeta meta, IQuery iQuery) throws TddlException {
   String actualTable = iQuery.getTableName();
   return getCursor(
       executionContext.getTransaction(), meta, executionContext.getIsolation(), actualTable);
 }
Exemple #7
0
  // @Test
  public void test_单表merge_函数下推() {
    TableNode table = new TableNode("TABLE1");
    table.select("MAX(ID) AS ID");
    table.orderBy("COUNT(ID)");
    table.groupBy("SUBSTRING(NAME,0,10)");
    table.having("COUNT(ID) > 1");
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(QUERY_CONCURRENCY.CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 并行

    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IQuery);
    IQuery query = (IQuery) dne;
    Assert.assertEquals("SUBSTRING(NAME, 0, 10)", query.getColumns().get(1).toString()); // 下推成功
    Assert.assertEquals("COUNT(ID)", query.getColumns().get(2).toString()); // 下推成功
  }
Exemple #8
0
  @Test
  public void test_单表查询_存在聚合函数_limit不下推() {
    TableNode table = new TableNode("TABLE1");
    table.limit(10, 20);
    table.select("count(distinct id)");
    QueryTreeNode qn = table;
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(qn, null, extraCmd);

    Assert.assertTrue(qc instanceof IMerge);
    Assert.assertEquals(
        QUERY_CONCURRENCY.GROUP_CONCURRENT, ((IMerge) qc).getQueryConcurrency()); // 并行
    Assert.assertEquals(10L, qc.getLimitFrom());
    Assert.assertEquals(20L, qc.getLimitTo());
    IDataNodeExecutor dne = ((IMerge) qc).getSubNodes().get(0);
    Assert.assertTrue(dne instanceof IQuery);
    IQuery query = (IQuery) dne;
    Assert.assertEquals(null, query.getLimitFrom());
    Assert.assertEquals(null, query.getLimitTo());
  }
Exemple #9
0
 // 单表or查询
 // 查询条件由or连接,
 // 由于NAME和ID上存在索引,所以会生成两个子查询
 // or的两边分别作为子查询的keyFilter
 // 由于NAME=2323的子查询为非主键索引查询
 // 所以此处会生成一个join节点
 // 最后一个merge节点用于合并子查询的结果
 @Test
 public void test_单表查询_OR条件_1() {
   TableNode table = new TableNode("TABLE1");
   table.query("NAME = 2323 OR ID=1");
   extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, true);
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
   extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, false);
   Assert.assertTrue(qc instanceof IMerge);
   Assert.assertTrue(((IMerge) qc).isUnion()); // 是union查询
   Assert.assertTrue(((IMerge) qc).getSubNodes().get(0) instanceof IQuery);
   IQuery query = (IQuery) ((IMerge) qc).getSubNodes().get(0);
   Assert.assertEquals("TABLE1.ID = 1", query.getKeyFilter().toString());
   Assert.assertTrue(((IMerge) qc).getSubNodes().get(1) instanceof IMerge);
   Assert.assertTrue(
       ((IMerge) ((IMerge) qc).getSubNodes().get(1)).getSubNodes().get(0) instanceof IJoin);
   IJoin join = (IJoin) ((IMerge) ((IMerge) qc).getSubNodes().get(1)).getSubNodes().get(0);
   Assert.assertEquals(
       "TABLE1._NAME.NAME = 2323", ((IQuery) join.getLeftNode()).getKeyFilter().toString());
 }
Exemple #10
0
  @Test
  public void testQuery_子查询_not_exist模式() throws SqlParserException {
    String sql =
        "SELECT * FROM TABLE1 WHERE NOT EXISTS (SELECT ID FROM TABLE2 WHERE TABLE2.NAME = TABLE1.NAME)";
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(sql, null, extraCmd, true);
    Assert.assertTrue(qc instanceof IMerge);

    IQuery query1 = (IQuery) ((IMerge) qc).getSubNodes().get(0);
    IQuery query2 = (IQuery) ((IMerge) qc).getSubNodes().get(1);

    IFunction subquery1 = (IFunction) query1.getSubqueryFilter().getArgs().get(0);
    IFunction subquery2 = (IFunction) query2.getSubqueryFilter().getArgs().get(0);
    Assert.assertTrue(subquery1 == subquery2);
    Assert.assertEquals("NOT", subquery1.getFunctionName());
    // 结构为: NOT(func) -> FILTER -> SUBQUERY_SCALAR(func) -> subquery
    Assert.assertTrue(
        (IQuery)
                ((IFunction) ((IBooleanFilter) subquery1.getArgs().get(0)).getArgs().get(0))
                    .getArgs()
                    .get(0)
            instanceof IQuery);
  }
Exemple #11
0
  @Test
  public void testQuery_子查询_in模式() throws SqlParserException {
    String sql =
        "SELECT ID,NAME FROM TABLE1 WHERE NAME IN (SELECT NAME FROM TABLE2 WHERE TABLE2.NAME = TABLE1.NAME) AND ID IN (1,2)";
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(sql, null, extraCmd, true);
    Assert.assertTrue(qc instanceof IMerge);

    IQuery query1 = (IQuery) ((IMerge) qc).getSubNodes().get(0);
    IQuery query2 = (IQuery) ((IMerge) qc).getSubNodes().get(1);

    IFunction subquery1 = (IFunction) ((List) query1.getSubqueryFilter().getArgs().get(1)).get(0);
    IFunction subquery2 = (IFunction) ((List) query2.getSubqueryFilter().getArgs().get(1)).get(0);
    Assert.assertTrue(subquery1.getArgs().get(0) instanceof IQuery);

    IQuery merge1 = (IQuery) subquery1.getArgs().get(0);
    IQuery merge2 = (IQuery) subquery2.getArgs().get(0);
    Assert.assertEquals(merge1.getSubqueryOnFilterId(), merge2.getSubqueryOnFilterId());
  }
Exemple #12
0
  @Test
  public void testQuery_子查询_all模式() throws SqlParserException {
    String sql =
        "SELECT * FROM TABLE1 WHERE ID > ALL (SELECT ID FROM TABLE2 WHERE TABLE2.NAME = TABLE1.NAME)";
    IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(sql, null, extraCmd, true);
    Assert.assertTrue(qc instanceof IMerge);

    IQuery query1 = (IQuery) ((IMerge) qc).getSubNodes().get(0);
    IQuery query2 = (IQuery) ((IMerge) qc).getSubNodes().get(1);

    IFunction subquery1 = (IFunction) query1.getSubqueryFilter().getArgs().get(1);
    IFunction subquery2 = (IFunction) query2.getSubqueryFilter().getArgs().get(1);
    Assert.assertTrue(subquery1 == subquery2);
    Assert.assertTrue(subquery1.getArgs().get(0) instanceof IQuery);
    IQuery merge = (IQuery) subquery1.getArgs().get(0);
    IFunction func = (IFunction) merge.getColumns().get(0);
    Assert.assertEquals("MAX", func.getFunctionName());
  }
Exemple #13
0
 // 单表复杂查询条件
 // SCHOOL=1 AND (ID=4 OR ID=3)
 // 应该展开为
 // (SCHOOL=1 AND ID=4) OR (SCHOOL=1 AND ID=3)
 @Test
 public void test_单表查询_复杂条件展开() {
   TableNode table = new TableNode("TABLE1");
   table.query("SCHOOL=1 AND (ID=4 OR ID=3)");
   extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, true);
   IQueryTree qc = (IQueryTree) optimizer.optimizeAndAssignment(table, null, extraCmd);
   extraCmd.put(ConnectionProperties.CHOOSE_INDEX_MERGE, false);
   Assert.assertTrue(qc instanceof IMerge);
   Assert.assertTrue(((IMerge) qc).isUnion()); // 是union查询
   Assert.assertTrue(((IMerge) qc).getSubNodes().get(0) instanceof IQuery);
   Assert.assertTrue(((IMerge) qc).getSubNodes().get(1) instanceof IQuery);
   IQuery query1 = (IQuery) ((IMerge) qc).getSubNodes().get(0);
   Assert.assertEquals("TABLE1.ID = 4", query1.getKeyFilter().toString());
   Assert.assertEquals("TABLE1.SCHOOL = 1", query1.getValueFilter().toString());
   IQuery query2 = (IQuery) ((IMerge) qc).getSubNodes().get(1);
   Assert.assertEquals("TABLE1.ID = 3", query2.getKeyFilter().toString());
   Assert.assertEquals("TABLE1.SCHOOL = 1", query2.getValueFilter().toString());
 }