@Test public void testSchemaCompletionWithComplexSubquery() throws SqlParseException { String sql = "SELECT a, b " + "FROM (" + " SELECT c, d, sum(p) " + " FROM table1 t1, DB.table2 t2 " + " WHERE t1.c > t2.d " + " GROUP BY t.e" + " HAVING sum(p) > 100" + " ORDER BY t2.f" + ") at1 " + "INNER JOIN table3 t3 " + "ON at1.c = t3.c " + "WHERE t3.d > 0 " + "ORDER BY t3.e"; String exceptSQL = "SELECT a, b " + "FROM (" + " SELECT c, d, sum(p) " + " FROM EDW.table1 t1, DB.table2 t2 " + " WHERE t1.c > t2.d " + " GROUP BY t.e" + " HAVING sum(p) > 100" + " ORDER BY t2.f" + ") at1 " + "INNER JOIN EDW.table3 t3 " + "ON at1.c = t3.c " + "WHERE t3.d > 0 " + "ORDER BY t3.e"; Assert.assertEquals(exceptSQL, PushDownUtil.schemaCompletion(sql, "EDW")); }
static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); }
PushDownUtil { static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); } }
PushDownUtil { static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); } private  PushDownUtil(); }
PushDownUtil { static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); } private  PushDownUtil(); static Pair<List<List<String>>, List<SelectedColumnMeta>> tryPushDownSelectQuery(String project, String sql,
            String defaultSchema, SQLException sqlException, boolean isPrepare); static Pair<List<List<String>>, List<SelectedColumnMeta>> tryPushDownNonSelectQuery(String project,
            String sql, String defaultSchema, boolean isPrepare); }
PushDownUtil { static String schemaCompletion(String inputSql, String schema) throws SqlParseException { if (inputSql == null || inputSql.equals("")) { return ""; } SqlNode node = CalciteParser.parse(inputSql); PushDownUtil.FromTablesVisitor ftv = new PushDownUtil.FromTablesVisitor(); node.accept(ftv); List<SqlNode> tablesWithoutSchema = ftv.getTablesWithoutSchema(); if (tablesWithoutSchema.isEmpty()) { return inputSql; } List<Pair<Integer, Integer>> tablesPos = new ArrayList<>(); for (SqlNode tables : tablesWithoutSchema) { tablesPos.add(CalciteParser.getReplacePos(tables, inputSql)); } Collections.sort(tablesPos, new Comparator<Pair<Integer, Integer>>() { @Override public int compare(Pair<Integer, Integer> o1, Pair<Integer, Integer> o2) { int r = o2.getFirst() - o1.getFirst(); return r == 0 ? o2.getSecond() - o1.getSecond() : r; } }); StrBuilder afterConvert = new StrBuilder(inputSql); for (Pair<Integer, Integer> pos : tablesPos) { String tableWithSchema = schema + "." + inputSql.substring(pos.getFirst(), pos.getSecond()); afterConvert.replace(pos.getFirst(), pos.getSecond(), tableWithSchema); } return afterConvert.toString(); } private  PushDownUtil(); static Pair<List<List<String>>, List<SelectedColumnMeta>> tryPushDownSelectQuery(String project, String sql,
            String defaultSchema, SQLException sqlException, boolean isPrepare); static Pair<List<List<String>>, List<SelectedColumnMeta>> tryPushDownNonSelectQuery(String project,
            String sql, String defaultSchema, boolean isPrepare);  }