KYLIN-2681 Convert input sql's expression to computed column if computed colum defined
Project: http://git-wip-us.apache.org/repos/asf/kylin/repo Commit: http://git-wip-us.apache.org/repos/asf/kylin/commit/05631b58 Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/05631b58 Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/05631b58 Branch: refs/heads/master Commit: 05631b588c0b49bb156e85dcaf3dca27e79b700c Parents: 4f20ba3 Author: Aron.tao <245915...@qq.com> Authored: Sun Jun 25 18:45:27 2017 +0800 Committer: Hongbin Ma <m...@kyligence.io> Committed: Tue Jun 27 18:29:11 2017 +0800 ---------------------------------------------------------------------- .../query/util/CognosParenthesesEscape.java | 2 +- .../query/util/ConvertToComputedColumn.java | 353 +++++++++++++++++++ .../query/util/KeywordDefaultDirtyHack.java | 2 +- .../org/apache/kylin/query/util/QueryUtil.java | 11 +- .../query/util/CognosParenthesesEscapeTest.java | 32 +- .../query/util/ConvertToComputedColumnTest.java | 135 +++++++ .../apache/kylin/query/util/QueryUtilTest.java | 6 +- .../apache/kylin/rest/service/QueryService.java | 2 +- 8 files changed, 520 insertions(+), 23 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/kylin/blob/05631b58/query/src/main/java/org/apache/kylin/query/util/CognosParenthesesEscape.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/util/CognosParenthesesEscape.java b/query/src/main/java/org/apache/kylin/query/util/CognosParenthesesEscape.java index 6d930a5..8c6d82d 100644 --- a/query/src/main/java/org/apache/kylin/query/util/CognosParenthesesEscape.java +++ b/query/src/main/java/org/apache/kylin/query/util/CognosParenthesesEscape.java @@ -32,7 +32,7 @@ public class CognosParenthesesEscape implements QueryUtil.IQueryTransformer { private static final Pattern FROM_PATTERN = Pattern.compile("\\s+from\\s+(\\s*\\(\\s*)+(?!\\s*select\\s)", Pattern.CASE_INSENSITIVE); @Override - public String transform(String sql) { + public String transform(String sql, String project) { if (sql == null || sql.isEmpty()) { return sql; } http://git-wip-us.apache.org/repos/asf/kylin/blob/05631b58/query/src/main/java/org/apache/kylin/query/util/ConvertToComputedColumn.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/util/ConvertToComputedColumn.java b/query/src/main/java/org/apache/kylin/query/util/ConvertToComputedColumn.java new file mode 100644 index 0000000..d8f1220 --- /dev/null +++ b/query/src/main/java/org/apache/kylin/query/util/ConvertToComputedColumn.java @@ -0,0 +1,353 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.kylin.query.util; + +import java.util.ArrayList; +import java.util.Comparator; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Objects; + +import javax.annotation.Nullable; + +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlDataTypeSpec; +import org.apache.calcite.sql.SqlDynamicParam; +import org.apache.calcite.sql.SqlIdentifier; +import org.apache.calcite.sql.SqlIntervalQualifier; +import org.apache.calcite.sql.SqlLiteral; +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.SqlNodeList; +import org.apache.calcite.sql.SqlSelect; +import org.apache.calcite.sql.parser.SqlParseException; +import org.apache.calcite.sql.parser.SqlParser; +import org.apache.calcite.sql.parser.SqlParserPos; +import org.apache.calcite.sql.util.SqlVisitor; +import org.apache.commons.lang3.tuple.Pair; +import org.apache.kylin.common.KylinConfig; +import org.apache.kylin.metadata.MetadataManager; +import org.apache.kylin.metadata.model.DataModelDesc; +import org.apache.kylin.metadata.project.ProjectInstance; +import org.apache.kylin.metadata.project.ProjectManager; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +import com.google.common.base.Functions; +import com.google.common.base.Predicate; +import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableSortedMap; +import com.google.common.collect.Iterables; +import com.google.common.collect.Ordering; + +public class ConvertToComputedColumn implements QueryUtil.IQueryTransformer { + private static final Logger logger = LoggerFactory.getLogger(ConvertToComputedColumn.class); + + @Override + public String transform(String sql, String project) { + if (project == null) { + return sql; + } + ImmutableSortedMap<String, String> computedColumns = getSortedComputedColumnWithProject(project); + return replaceComputedColumn(sql, computedColumns); + } + + static String replaceComputedColumn(String inputSql, ImmutableSortedMap<String, String> computedColumn) { + if (inputSql == null) { + return ""; + } + + if (computedColumn == null || computedColumn.isEmpty()) { + return inputSql; + } + String result = inputSql; + String[] lines = inputSql.split("\n"); + List<Pair<String, String>> toBeReplacedExp = new ArrayList<>(); //{"alias":"expression"}, like {"t1":"t1.a+t1.b+t1.c"} + + for (String ccExp : computedColumn.keySet()) { + List<SqlNode> matchedNodes = new ArrayList<>(); + try { + matchedNodes = getMatchedNodes(inputSql, computedColumn.get(ccExp)); + } catch (SqlParseException e) { + logger.error("Convert to computedColumn Fail,parse sql fail ", e.getMessage()); + } + for (SqlNode node : matchedNodes) { + Pair<Integer, Integer> startEndPos = getReplacePos(lines, node); + int start = startEndPos.getLeft(); + int end = startEndPos.getRight(); + //add table alias like t1.column,if exists alias + String alias = getTableAlias(node); + toBeReplacedExp.add(Pair.of(alias, inputSql.substring(start, end))); + } + logger.debug("Computed column: " + ccExp + "'s matched list:" + toBeReplacedExp); + //replace user's input sql + for (Pair<String, String> toBeReplaced : toBeReplacedExp) { + result = result.replace(toBeReplaced.getRight(), toBeReplaced.getLeft() + ccExp); + } + } + return result; + } + + private static Pair<Integer, Integer> getReplacePos(String[] lines, SqlNode node) { + SqlParserPos pos = node.getParserPosition(); + int lineStart = pos.getLineNum(); + int columnStart = pos.getColumnNum() - 1; + int columnEnd = pos.getEndColumnNum(); + //for the case that sql is multi lines + for (int i = 0; i < lineStart - 1; i++) { + int offset = lines[i].length(); + columnStart += offset + 1; + columnEnd += offset + 1; + } + return Pair.of(columnStart, columnEnd); + } + + //Return matched node's position and its alias(if exists).If can not find matches, return an empty capacity list + private static List<SqlNode> getMatchedNodes(String inputSql, String ccExp) throws SqlParseException { + if (ccExp == null || ccExp.equals("")) { + return new ArrayList<>(); + } + ArrayList<SqlNode> toBeReplacedNodes = new ArrayList<>(); + SqlNode ccNode = getCCExpNode(ccExp); + List<SqlNode> inputNodes = getInputTreeNodes(inputSql); + + // find whether user input sql's tree node equals computed columns's define expression + for (SqlNode inputNode : inputNodes) { + if (isNodeEqual(inputNode, ccNode)) { + toBeReplacedNodes.add(inputNode); + } + } + return toBeReplacedNodes; + } + + private static List<SqlNode> getInputTreeNodes(String inputSql) throws SqlParseException { + SqlTreeVisitor stv = new SqlTreeVisitor(); + parse(inputSql).accept(stv); + return stv.getSqlNodes(); + } + + private static SqlNode getCCExpNode(String ccExp) throws SqlParseException { + ccExp = "select " + ccExp + " from t"; + return ((SqlSelect) parse(ccExp)).getSelectList().get(0); + } + + static SqlNode parse(String sql) throws SqlParseException { + SqlParser.ConfigBuilder parserBuilder = SqlParser.configBuilder(); + SqlParser sqlParser = SqlParser.create(sql, parserBuilder.build()); + return sqlParser.parseQuery(); + } + + static boolean isNodeEqual(SqlNode node0, SqlNode node1) { + if (node0 == null) { + return node1 == null; + } else if (node1 == null) { + return false; + } + + if (!Objects.equals(node0.getClass().getSimpleName(), node1.getClass().getSimpleName())) { + return false; + } + + if (node0 instanceof SqlCall) { + SqlCall thisNode = (SqlCall) node0; + SqlCall thatNode = (SqlCall) node1; + if (!thisNode.getOperator().getName().equalsIgnoreCase(thatNode.getOperator().getName())) { + return false; + } + return isNodeEqual(thisNode.getOperandList(), thatNode.getOperandList()); + } + if (node0 instanceof SqlLiteral) { + SqlLiteral thisNode = (SqlLiteral) node0; + SqlLiteral thatNode = (SqlLiteral) node1; + return Objects.equals(thisNode.getValue(), thatNode.getValue()); + } + if (node0 instanceof SqlNodeList) { + SqlNodeList thisNode = (SqlNodeList) node0; + SqlNodeList thatNode = (SqlNodeList) node1; + if (thisNode.getList().size() != thatNode.getList().size()) { + return false; + } + for (int i = 0; i < thisNode.getList().size(); i++) { + SqlNode thisChild = thisNode.getList().get(i); + final SqlNode thatChild = thatNode.getList().get(i); + if (!isNodeEqual(thisChild, thatChild)) { + return false; + } + } + return true; + } + if (node0 instanceof SqlIdentifier) { + SqlIdentifier thisNode = (SqlIdentifier) node0; + SqlIdentifier thatNode = (SqlIdentifier) node1; + // compare ignore table alias.eg: expression like "a.b + a.c + a.d" ,alias a will be ignored when compared + String name0 = thisNode.names.get(thisNode.names.size() - 1).replace("\"", ""); + String name1 = thatNode.names.get(thatNode.names.size() - 1).replace("\"", ""); + return name0.equalsIgnoreCase(name1); + } + + logger.error("Convert to computed column fail,failed to compare two nodes,unknown instance type"); + return false; + } + + private static boolean isNodeEqual(List<SqlNode> operands0, List<SqlNode> operands1) { + if (operands0.size() != operands1.size()) { + return false; + } + for (int i = 0; i < operands0.size(); i++) { + if (!isNodeEqual(operands0.get(i), operands1.get(i))) { + return false; + } + } + return true; + } + + private static String getTableAlias(SqlNode node) { + if (node instanceof SqlCall) { + SqlCall call = (SqlCall) node; + return getTableAlias(call.getOperandList()); + } + if (node instanceof SqlIdentifier) { + StringBuilder alias = new StringBuilder(""); + ImmutableList<String> names = ((SqlIdentifier) node).names; + if (names.size() >= 2) { + for (int i = 0; i < names.size() - 1; i++) { + alias.append(names.get(i)).append("."); + } + } + return alias.toString(); + } + if (node instanceof SqlNodeList) { + return ""; + } + if (node instanceof SqlLiteral) { + return ""; + } + return ""; + } + + private static String getTableAlias(List<SqlNode> operands) { + for (SqlNode operand : operands) { + return getTableAlias(operand); + } + return ""; + } + + private ImmutableSortedMap<String, String> getSortedComputedColumnWithProject(String project) { + MetadataManager metadataManager = MetadataManager.getInstance(KylinConfig.getInstanceFromEnv()); + Map<String, MetadataManager.CCInfo> ccInfoMap = metadataManager.getCcInfoMap(); + final ProjectInstance projectInstance = ProjectManager.getInstance(KylinConfig.getInstanceFromEnv()) + .getProject(project); + + Iterable<MetadataManager.CCInfo> projectCCInfo = Iterables.filter(ccInfoMap.values(), + new Predicate<MetadataManager.CCInfo>() { + @Override + public boolean apply(@Nullable MetadataManager.CCInfo ccInfo) { + return Iterables.any(ccInfo.getDataModelDescs(), new Predicate<DataModelDesc>() { + @Override + public boolean apply(@Nullable DataModelDesc model) { + return projectInstance.containsModel(model.getName()); + } + }); + } + }); + + Map<String, String> computedColumns = new HashMap<>(); + for (MetadataManager.CCInfo ccInfo : projectCCInfo) { + computedColumns.put(ccInfo.getComputedColumnDesc().getColumnName(), + ccInfo.getComputedColumnDesc().getExpression()); + } + + return getMapSortedByValue(computedColumns); + } + + static ImmutableSortedMap<String, String> getMapSortedByValue(Map<String, String> computedColumns) { + if (computedColumns == null || computedColumns.isEmpty()) { + return null; + } + + Ordering<String> ordering = Ordering.from(new Comparator<String>() { + @Override + public int compare(String o1, String o2) { + return Integer.compare(o1.replaceAll("\\s*", "").length(), o2.replaceAll("\\s*", "").length()); + } + }).reverse().nullsLast().onResultOf(Functions.forMap(computedColumns, null)).compound(Ordering.natural()); + return ImmutableSortedMap.copyOf(computedColumns, ordering); + } + +} + +class SqlTreeVisitor implements SqlVisitor<SqlNode> { + private List<SqlNode> sqlNodes; + + SqlTreeVisitor() { + this.sqlNodes = new ArrayList<>(); + } + + List<SqlNode> getSqlNodes() { + return sqlNodes; + } + + @Override + public SqlNode visit(SqlNodeList nodeList) { + sqlNodes.add(nodeList); + for (int i = 0; i < nodeList.size(); i++) { + SqlNode node = nodeList.get(i); + node.accept(this); + } + return null; + } + + @Override + public SqlNode visit(SqlLiteral literal) { + sqlNodes.add(literal); + return null; + } + + @Override + public SqlNode visit(SqlCall call) { + sqlNodes.add(call); + for (SqlNode operand : call.getOperandList()) { + if (operand != null) { + operand.accept(this); + } + } + return null; + } + + @Override + public SqlNode visit(SqlIdentifier id) { + sqlNodes.add(id); + return null; + } + + @Override + public SqlNode visit(SqlDataTypeSpec type) { + return null; + } + + @Override + public SqlNode visit(SqlDynamicParam param) { + return null; + } + + @Override + public SqlNode visit(SqlIntervalQualifier intervalQualifier) { + return null; + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/05631b58/query/src/main/java/org/apache/kylin/query/util/KeywordDefaultDirtyHack.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/util/KeywordDefaultDirtyHack.java b/query/src/main/java/org/apache/kylin/query/util/KeywordDefaultDirtyHack.java index e1398f6..23faf8e 100644 --- a/query/src/main/java/org/apache/kylin/query/util/KeywordDefaultDirtyHack.java +++ b/query/src/main/java/org/apache/kylin/query/util/KeywordDefaultDirtyHack.java @@ -21,7 +21,7 @@ package org.apache.kylin.query.util; public class KeywordDefaultDirtyHack implements QueryUtil.IQueryTransformer { @Override - public String transform(String sql) { + public String transform(String sql, String project) { // KYLIN-2108, DEFAULT is hive default database, but a sql keyword too, needs quote sql = sql.replace("DEFAULT.", "\"DEFAULT\"."); sql = sql.replace("default.", "\"default\"."); http://git-wip-us.apache.org/repos/asf/kylin/blob/05631b58/query/src/main/java/org/apache/kylin/query/util/QueryUtil.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/util/QueryUtil.java b/query/src/main/java/org/apache/kylin/query/util/QueryUtil.java index d48a26f..7794f94 100644 --- a/query/src/main/java/org/apache/kylin/query/util/QueryUtil.java +++ b/query/src/main/java/org/apache/kylin/query/util/QueryUtil.java @@ -38,14 +38,15 @@ public class QueryUtil { private static List<IQueryTransformer> queryTransformers; public interface IQueryTransformer { - String transform(String sql); + String transform(String sql, String project); } + // for mockup test public static String massageSql(String sql) { - return massageSql(sql, 0, 0); + return massageSql(sql, null, 0, 0); } - public static String massageSql(String sql, int limit, int offset) { + public static String massageSql(String sql, String project, int limit, int offset) { sql = sql.trim(); sql = sql.replace("\r", " ").replace("\n", System.getProperty("line.separator")); @@ -65,7 +66,7 @@ public class QueryUtil { initQueryTransformers(); } for (IQueryTransformer t : queryTransformers) { - sql = t.transform(sql); + sql = t.transform(sql, project); } return sql; } @@ -100,7 +101,7 @@ public class QueryUtil { private static final Pattern PTN_HAVING_ESCAPE_FUNCTION = Pattern.compile("\\{fn" + "(.*?)" + "\\}", Pattern.CASE_INSENSITIVE); @Override - public String transform(String sql) { + public String transform(String sql, String project) { Matcher m; // Case fn{ EXTRACT(...) } http://git-wip-us.apache.org/repos/asf/kylin/blob/05631b58/query/src/test/java/org/apache/kylin/query/util/CognosParenthesesEscapeTest.java ---------------------------------------------------------------------- diff --git a/query/src/test/java/org/apache/kylin/query/util/CognosParenthesesEscapeTest.java b/query/src/test/java/org/apache/kylin/query/util/CognosParenthesesEscapeTest.java index 153c097..7825dbd 100644 --- a/query/src/test/java/org/apache/kylin/query/util/CognosParenthesesEscapeTest.java +++ b/query/src/test/java/org/apache/kylin/query/util/CognosParenthesesEscapeTest.java @@ -15,6 +15,7 @@ * See the License for the specific language governing permissions and * limitations under the License. */ + package org.apache.kylin.query.util; import java.io.File; @@ -33,16 +34,18 @@ public class CognosParenthesesEscapeTest { CognosParenthesesEscape escape = new CognosParenthesesEscape(); String data = " from ((a left outer join b on a.x1 = b.y1 and a.x2=b.y2 and a.x3= b.y3) inner join c as cc on a.x1=cc.z1 ) join d dd on a.x1=d.w1 and a.x2 =d.w2 "; String expected = " from a left outer join b on a.x1 = b.y1 and a.x2=b.y2 and a.x3= b.y3 inner join c as cc on a.x1=cc.z1 join d dd on a.x1=d.w1 and a.x2 =d.w2 "; - String transformed = escape.transform(data); + String transformed = escape.transform(data, null); Assert.assertEquals(expected, transformed); } @Test public void advanced1Test() throws IOException { CognosParenthesesEscape escape = new CognosParenthesesEscape(); - String query = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query01.sql"), Charset.defaultCharset()); - String expected = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query01.sql.expected"), Charset.defaultCharset()); - String transformed = escape.transform(query); + String query = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query01.sql"), + Charset.defaultCharset()); + String expected = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query01.sql.expected"), + Charset.defaultCharset()); + String transformed = escape.transform(query, null); //System.out.println(transformed); Assert.assertEquals(expected, transformed); } @@ -50,9 +53,11 @@ public class CognosParenthesesEscapeTest { @Test public void advanced2Test() throws IOException { CognosParenthesesEscape escape = new CognosParenthesesEscape(); - String query = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query02.sql"), Charset.defaultCharset()); - String expected = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query02.sql.expected"), Charset.defaultCharset()); - String transformed = escape.transform(query); + String query = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query02.sql"), + Charset.defaultCharset()); + String expected = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query02.sql.expected"), + Charset.defaultCharset()); + String transformed = escape.transform(query, null); //System.out.println(transformed); Assert.assertEquals(expected, transformed); } @@ -60,9 +65,11 @@ public class CognosParenthesesEscapeTest { @Test public void advanced3Test() throws IOException { CognosParenthesesEscape escape = new CognosParenthesesEscape(); - String query = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query03.sql"), Charset.defaultCharset()); - String expected = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query03.sql.expected"), Charset.defaultCharset()); - String transformed = escape.transform(query); + String query = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query03.sql"), + Charset.defaultCharset()); + String expected = FileUtils.readFileToString(new File("src/test/resources/query/cognos/query03.sql.expected"), + Charset.defaultCharset()); + String transformed = escape.transform(query, null); //System.out.println(transformed); Assert.assertEquals(expected, transformed); } @@ -70,11 +77,12 @@ public class CognosParenthesesEscapeTest { @Test public void proguardTest() throws IOException { CognosParenthesesEscape escape = new CognosParenthesesEscape(); - Collection<File> files = FileUtils.listFiles(new File("../kylin-it/src/test/resources"), new String[] { "sql" }, true); + Collection<File> files = FileUtils.listFiles(new File("../kylin-it/src/test/resources"), new String[] { "sql" }, + true); for (File f : files) { System.out.println("checking " + f.getAbsolutePath()); String query = FileUtils.readFileToString(f, Charset.defaultCharset()); - String transformed = escape.transform(query); + String transformed = escape.transform(query, null); Assert.assertEquals(query, transformed); } } http://git-wip-us.apache.org/repos/asf/kylin/blob/05631b58/query/src/test/java/org/apache/kylin/query/util/ConvertToComputedColumnTest.java ---------------------------------------------------------------------- diff --git a/query/src/test/java/org/apache/kylin/query/util/ConvertToComputedColumnTest.java b/query/src/test/java/org/apache/kylin/query/util/ConvertToComputedColumnTest.java new file mode 100644 index 0000000..c3efe8d --- /dev/null +++ b/query/src/test/java/org/apache/kylin/query/util/ConvertToComputedColumnTest.java @@ -0,0 +1,135 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.kylin.query.util; + +import java.util.HashMap; +import java.util.Map; + +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.SqlSelect; +import org.apache.calcite.sql.parser.SqlParseException; +import org.junit.Assert; +import org.junit.Test; + +import com.google.common.collect.ImmutableSortedMap; + +public class ConvertToComputedColumnTest { + @Test + public void testEqual() throws SqlParseException { + String sql0 = "select a.a + a.b + a.c from t as a"; + String sql1 = "select (((a . a + a.b + a.c))) from t as a"; + String sql2 = "select (a + b) + c from t"; + String sql3 = "select a.a + (a.b + a.c) from t as a"; + + SqlNode sn0 = getSelectNode(sql0); + SqlNode sn1 = getSelectNode(sql1); + SqlNode sn2 = getSelectNode(sql2); + SqlNode sn3 = getSelectNode(sql3); + + Assert.assertEquals(true, ConvertToComputedColumn.isNodeEqual(sn0, sn1)); + Assert.assertEquals(true, ConvertToComputedColumn.isNodeEqual(sn0, sn2)); + Assert.assertEquals(false, ConvertToComputedColumn.isNodeEqual(sn0, sn3)); + } + + @Test + public void testErrorCase() { + //computed column is null or empty + String sql = "select a from t"; + Map<String, String> map = new HashMap<>(); + ImmutableSortedMap<String, String> computedColumns = ConvertToComputedColumn.getMapSortedByValue(map); + Assert.assertEquals("select a from t", ConvertToComputedColumn.replaceComputedColumn(sql, null)); + Assert.assertEquals("select a from t", ConvertToComputedColumn.replaceComputedColumn(sql, computedColumns)); + + //input is null or empty or parse error + String sql1 = ""; + String sql2 = "select sum(a from t"; + Map<String, String> map2 = new HashMap<>(); + map2.put("cc", "a + b"); + ImmutableSortedMap<String, String> computedColumns2 = ConvertToComputedColumn.getMapSortedByValue(map2); + Assert.assertEquals("", ConvertToComputedColumn.replaceComputedColumn(null, computedColumns2)); + Assert.assertEquals("", ConvertToComputedColumn.replaceComputedColumn(sql1, computedColumns2)); + Assert.assertEquals("select sum(a from t", + ConvertToComputedColumn.replaceComputedColumn(sql2, computedColumns2)); + } + + @Test + public void testReplaceComputedColumn() throws SqlParseException { + String sql0 = "select (\"DB\".\"t1\" . \"a\" + DB.t1.b + DB.t1.c) as c, substring(substring(lstg_format_name,1,3),1,3) as d from table1 as t1 group by t1.a+ t1.b + t1.c having t1.a+t1.b+t1.c > 100 order by t1.a +t1.b +t1.c"; + String sql1 = "select sum(sum(a)) from t"; + String sql2 = "select t1.a + t1.b as aa, t2.c + t2.d as bb from table1 t1,table2 t2 where t1.a + t1.b > t2.c + t2.d order by t1.a + t1.b"; + String sql3 = "select substring(substring(lstg_format_name,1,3),1,3) from a"; + + String expr0 = "a + b + c"; + String expr1 = "sum(a)"; + String expr2 = "a + b"; + String expr3 = "c + d"; + String expr = "substring(substring(lstg_format_name,1,3),1,3)"; + + Map<String, String> map = new HashMap<>(); + map.put("cc0", expr0); + map.put("cc1", expr1); + map.put("cc2", expr2); + map.put("cc3", expr3); + map.put("cc", expr); + + ImmutableSortedMap<String, String> computedColumns = ConvertToComputedColumn.getMapSortedByValue(map); + Assert.assertEquals( + "select (DB.t1.cc0) as c, cc as d from table1 as t1 group by T1.cc0 having T1.cc0 > 100 order by T1.cc0", + ConvertToComputedColumn.replaceComputedColumn(sql0, computedColumns)); + Assert.assertEquals("select sum(cc1) from t", + ConvertToComputedColumn.replaceComputedColumn(sql1, computedColumns)); + Assert.assertEquals( + "select T1.cc2 as aa, T2.cc3 as bb from table1 t1,table2 t2 where T1.cc2 > T2.cc3 order by T1.cc2", + ConvertToComputedColumn.replaceComputedColumn(sql2, computedColumns)); + Assert.assertEquals("select cc from a", ConvertToComputedColumn.replaceComputedColumn(sql3, computedColumns)); + + } + + private static SqlNode getSelectNode(String sql) throws SqlParseException { + return ((SqlSelect) ConvertToComputedColumn.parse(sql)).getSelectList().get(0); + } + + @Test + public void testTwoCCHasSameSubExp() { + String sql0 = "select a + b + c from t order by a + b"; + + String expr0 = "a + b"; + String expr1 = "a + b + c"; + + Map<String, String> map = new HashMap<>(); + map.put("cc1", expr0); + map.put("cc0", expr1); + ImmutableSortedMap<String, String> computedColumns = ConvertToComputedColumn.getMapSortedByValue(map); + Assert.assertEquals("select cc0 from t order by cc1", + ConvertToComputedColumn.replaceComputedColumn(sql0, computedColumns)); + + //é²æ¢æ·»å ç顺åºé æå½±å + String expr11 = "a + b + c"; + String expr00 = "a + b"; + + Map<String, String> map2 = new HashMap<>(); + map2.put("cc0", expr11); + map2.put("cc1", expr00); + ImmutableSortedMap<String, String> computedColumns1 = ConvertToComputedColumn.getMapSortedByValue(map2); + Assert.assertEquals("select cc0 from t order by cc1", + ConvertToComputedColumn.replaceComputedColumn(sql0, computedColumns1)); + + } + +} http://git-wip-us.apache.org/repos/asf/kylin/blob/05631b58/query/src/test/java/org/apache/kylin/query/util/QueryUtilTest.java ---------------------------------------------------------------------- diff --git a/query/src/test/java/org/apache/kylin/query/util/QueryUtilTest.java b/query/src/test/java/org/apache/kylin/query/util/QueryUtilTest.java index a1edd89..f168d1e 100644 --- a/query/src/test/java/org/apache/kylin/query/util/QueryUtilTest.java +++ b/query/src/test/java/org/apache/kylin/query/util/QueryUtilTest.java @@ -40,12 +40,12 @@ public class QueryUtilTest extends LocalFileMetadataTestCase { public void testMassageSql() { { String sql = "select ( date '2001-09-28' + interval floor(1.2) day) from test_kylin_fact"; - String s = QueryUtil.massageSql(sql, 0, 0); + String s = QueryUtil.massageSql(sql, null, 0, 0); Assert.assertEquals("select ( date '2001-09-28' + interval '1' day) from test_kylin_fact", s); } { String sql = "select ( date '2001-09-28' + interval floor(2) month) from test_kylin_fact group by ( date '2001-09-28' + interval floor(2) month)"; - String s = QueryUtil.massageSql(sql, 0, 0); + String s = QueryUtil.massageSql(sql, null, 0, 0); Assert.assertEquals("select ( date '2001-09-28' + interval '2' month) from test_kylin_fact group by ( date '2001-09-28' + interval '2' month)", s); } } @@ -54,7 +54,7 @@ public class QueryUtilTest extends LocalFileMetadataTestCase { public void testKeywordDefaultDirtyHack() { { String sql = "select * from DEFAULT.TEST_KYLIN_FACT"; - String s = QueryUtil.massageSql(sql, 0, 0); + String s = QueryUtil.massageSql(sql, null, 0, 0); Assert.assertEquals("select * from \"DEFAULT\".TEST_KYLIN_FACT", s); } } http://git-wip-us.apache.org/repos/asf/kylin/blob/05631b58/server-base/src/main/java/org/apache/kylin/rest/service/QueryService.java ---------------------------------------------------------------------- diff --git a/server-base/src/main/java/org/apache/kylin/rest/service/QueryService.java b/server-base/src/main/java/org/apache/kylin/rest/service/QueryService.java index d6554fc..8d18901 100644 --- a/server-base/src/main/java/org/apache/kylin/rest/service/QueryService.java +++ b/server-base/src/main/java/org/apache/kylin/rest/service/QueryService.java @@ -471,7 +471,7 @@ public class QueryService extends BasicService { return fakeResponse; } - String correctedSql = QueryUtil.massageSql(sqlRequest.getSql(), sqlRequest.getLimit(), sqlRequest.getOffset()); + String correctedSql = QueryUtil.massageSql(sqlRequest.getSql(), sqlRequest.getProject(), sqlRequest.getLimit(), sqlRequest.getOffset()); if (!correctedSql.equals(sqlRequest.getSql())) { logger.info("The corrected query: " + correctedSql);
