KYLIN-2471 queries with parenthesized sub-clause in JOIN will fail
Project: http://git-wip-us.apache.org/repos/asf/kylin/repo Commit: http://git-wip-us.apache.org/repos/asf/kylin/commit/29b3f6d4 Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/29b3f6d4 Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/29b3f6d4 Branch: refs/heads/master-hbase0.98 Commit: 29b3f6d422f0d4c86678b574864e8823892e3924 Parents: d6fc052 Author: Hongbin Ma <mahong...@apache.org> Authored: Sat Feb 25 16:36:19 2017 +0800 Committer: Hongbin Ma <mahong...@apache.org> Committed: Sat Feb 25 16:36:45 2017 +0800 ---------------------------------------------------------------------- .../rest/util/CognosParenthesesEscape.java | 83 ++++++++++++++++++++ .../rest/util/CognosParentesesEscapeTest.java | 61 ++++++++++++++ .../src/test/resources/query/cognos/query01.sql | 28 +++++++ .../resources/query/cognos/query01.sql.expected | 28 +++++++ 4 files changed, 200 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/kylin/blob/29b3f6d4/server-base/src/main/java/org/apache/kylin/rest/util/CognosParenthesesEscape.java ---------------------------------------------------------------------- diff --git a/server-base/src/main/java/org/apache/kylin/rest/util/CognosParenthesesEscape.java b/server-base/src/main/java/org/apache/kylin/rest/util/CognosParenthesesEscape.java new file mode 100644 index 0000000..33f4b63 --- /dev/null +++ b/server-base/src/main/java/org/apache/kylin/rest/util/CognosParenthesesEscape.java @@ -0,0 +1,83 @@ +/* + * 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.rest.util; + +import java.util.LinkedList; +import java.util.List; +import java.util.UUID; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import org.apache.kylin.common.util.Pair; +import org.apache.kylin.rest.util.QueryUtil.IQueryTransformer; + +/** + * from (a join b on a.x = b.y) join c + * + * similar in https://issues.apache.org/jira/browse/CALCITE-35 + * + * we'll find such pattern and remove the parentheses + */ +public class CognosParenthesesEscape implements IQueryTransformer { + + private static final String S0 = "\\s*"; + private static final String S1 = "\\s"; + private static final String SM = "\\s+"; + private static final String TABLE_OR_COLUMN_NAME = "[\\w\\\"\\'\\.]+"; + private static final String TABLE_NAME_WITH_OPTIONAL_ALIAS = TABLE_OR_COLUMN_NAME + "((\\s+as)?\\s+" + TABLE_OR_COLUMN_NAME + ")?"; + private static final String JOIN = "(\\s+inner|\\s+((left|right|full)(\\s+outer)?))?\\s+join";// as per http://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server + private static final String EQUAL_CONDITION = SM + TABLE_OR_COLUMN_NAME + S0 + "=" + S0 + TABLE_OR_COLUMN_NAME; + private static final String PARENTHESE_PATTERN_STR = "\\(" + S0 + // ( + TABLE_NAME_WITH_OPTIONAL_ALIAS + // a + JOIN + SM + // join + TABLE_NAME_WITH_OPTIONAL_ALIAS + //b + SM + "on" + EQUAL_CONDITION + "(\\s+and" + EQUAL_CONDITION + ")*" + // on a.x = b.y [and a.x2 = b.y2] + S0 + "\\)";// ) + private static final Pattern PARENTTHESES_PATTERN = Pattern.compile(PARENTHESE_PATTERN_STR, Pattern.CASE_INSENSITIVE); + + private static int identifierNum = 0; + + @Override + public String transform(String sql) { + Matcher m; + List<Pair<String, String>> matches = new LinkedList<>(); + while (true) { + m = PARENTTHESES_PATTERN.matcher(sql); + if (!m.find()) + break; + + String oneParentheses = m.group(0); + String identifier = generateRandomName(); + matches.add(new Pair<String, String>(identifier, oneParentheses.substring(1, oneParentheses.length() - 1))); + sql = sql.substring(0, m.start()) + identifier + sql.substring(m.end()); + } + + for (int i = matches.size() - 1; i >= 0; i--) { + sql = sql.replaceAll(matches.get(i).getKey(), matches.get(i).getValue()); + } + + return sql; + } + + private String generateRandomName() { + UUID uuid = UUID.randomUUID(); + return uuid.toString().replace("-", "_") + "_" + (identifierNum++); + } + +} http://git-wip-us.apache.org/repos/asf/kylin/blob/29b3f6d4/server-base/src/test/java/org/apache/kylin/rest/util/CognosParentesesEscapeTest.java ---------------------------------------------------------------------- diff --git a/server-base/src/test/java/org/apache/kylin/rest/util/CognosParentesesEscapeTest.java b/server-base/src/test/java/org/apache/kylin/rest/util/CognosParentesesEscapeTest.java new file mode 100644 index 0000000..76df219 --- /dev/null +++ b/server-base/src/test/java/org/apache/kylin/rest/util/CognosParentesesEscapeTest.java @@ -0,0 +1,61 @@ +/* + * 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.rest.util; + +import java.io.File; +import java.io.IOException; +import java.nio.charset.Charset; +import java.util.Collection; + +import org.apache.commons.io.FileUtils; +import org.junit.Assert; +import org.junit.Test; + +public class CognosParentesesEscapeTest { + + @Test + public void basicTest() { + CognosParenthesesEscape escape = new CognosParenthesesEscape(); + String data = "((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 = "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); + Assert.assertEquals(expected, transformed); + } + + @Test + public void advancedTest() 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); + //System.out.println(transformed); + Assert.assertEquals(expected, transformed); + } + + @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); + for (File f : files) { + System.out.println("checking " + f.getAbsolutePath()); + String query = FileUtils.readFileToString(f, Charset.defaultCharset()); + String transformed = escape.transform(query); + Assert.assertEquals(query, transformed); + } + } +} http://git-wip-us.apache.org/repos/asf/kylin/blob/29b3f6d4/server-base/src/test/resources/query/cognos/query01.sql ---------------------------------------------------------------------- diff --git a/server-base/src/test/resources/query/cognos/query01.sql b/server-base/src/test/resources/query/cognos/query01.sql new file mode 100644 index 0000000..a76b4e1 --- /dev/null +++ b/server-base/src/test/resources/query/cognos/query01.sql @@ -0,0 +1,28 @@ +-- +-- 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. +-- + +SELECT "TABLE1"."DIM1_1" "DIM1_1" + ,"TABLE2"."DIM2_1" "DIM2_1" + ,SUM("FACT"."M1") "M1" + ,SUM("FACT"."M2") "M2" + FROM ("COGNOS"."FACT" "FACT" LEFT OUTER JOIN "COGNOS"."TABLE1" + "TABLE1" ON "FACT"."FK_1" = "TABLE1"."PK_1") + LEFT OUTER JOIN "COGNOS"."TABLE2" "TABLE2" + ON "FACT"."FK_2" = "TABLE2"."PK_2" + GROUP BY "TABLE2"."DIM2_1" + ,"TABLE1"."DIM1_1"; http://git-wip-us.apache.org/repos/asf/kylin/blob/29b3f6d4/server-base/src/test/resources/query/cognos/query01.sql.expected ---------------------------------------------------------------------- diff --git a/server-base/src/test/resources/query/cognos/query01.sql.expected b/server-base/src/test/resources/query/cognos/query01.sql.expected new file mode 100644 index 0000000..c8005fc --- /dev/null +++ b/server-base/src/test/resources/query/cognos/query01.sql.expected @@ -0,0 +1,28 @@ +-- +-- 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. +-- + +SELECT "TABLE1"."DIM1_1" "DIM1_1" + ,"TABLE2"."DIM2_1" "DIM2_1" + ,SUM("FACT"."M1") "M1" + ,SUM("FACT"."M2") "M2" + FROM "COGNOS"."FACT" "FACT" LEFT OUTER JOIN "COGNOS"."TABLE1" + "TABLE1" ON "FACT"."FK_1" = "TABLE1"."PK_1" + LEFT OUTER JOIN "COGNOS"."TABLE2" "TABLE2" + ON "FACT"."FK_2" = "TABLE2"."PK_2" + GROUP BY "TABLE2"."DIM2_1" + ,"TABLE1"."DIM1_1";