@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);
}
private boolean hasArgsAvgFunction(IFunction func) {
for (Object args : func.getArgs()) {
if (args instanceof IFunction && ((IFunction) args).getColumnName().startsWith("AVG(")) {
return true;
}
}
return false;
}
@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());
}
@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());
}
@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);
}
/** 将Avg函数展开为sum/count */
private void expendAvgFunction(IDataNodeExecutor sub) {
if (sub instanceof IQuery || sub instanceof IJoin) {
List<ISelectable> add = new ArrayList();
List<ISelectable> remove = new ArrayList();
for (Object sel : ((IQueryTree) sub).getColumns()) {
ISelectable s = (ISelectable) sel;
if (s instanceof IFunction) {
if (s.getColumnName().startsWith("AVG(")) {
IFunction sum = (IFunction) s.copy();
sum.setExtraFunction(null);
sum.setFunctionName("SUM");
sum.setColumnName(s.getColumnName().replace("AVG(", "SUM("));
if (sum.getAlias() != null) {
sum.setAlias(sum.getAlias() + "1"); // 加个后缀1
}
IFunction count = (IFunction) s.copy();
count.setExtraFunction(null);
count.setFunctionName("COUNT");
count.setColumnName(s.getColumnName().replace("AVG(", "COUNT("));
if (count.getAlias() != null) {
count.setAlias(count.getAlias() + "2"); // 加个后缀2
}
add.add(count);
add.add(sum);
remove.add(s);
} else {
// 删除底下AVG的相关函数，比如 1 + AVG(ID)
// 目前这个只能上层来进行计算
// 可能的风险：还未支持的Function计算
if (FunctionType.Scalar.equals(((IFunction) s).getFunctionType())
&& hasArgsAvgFunction((IFunction) s)) {
remove.add(s);
}
}
}
}
if (!remove.isEmpty()) {
((IQueryTree) sub).getColumns().removeAll(remove);
((IQueryTree) sub).getColumns().addAll(add);
}
}
}
private static void findFunction(IFunction f, Parameters parameters) {
for (Object arg : f.getArgs()) {
findObject(arg, parameters);
}
}