You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@kylin.apache.org by li...@apache.org on 2017/02/25 08:37:35 UTC
[2/2] kylin git commit: KYLIN-2471 queries with parenthesized
sub-clause in JOIN will fail
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
Commit: 29b3f6d422f0d4c86678b574864e8823892e3924
Parents: d6fc052
Author: Hongbin Ma <ma...@apache.org>
Authored: Sat Feb 25 16:36:19 2017 +0800
Committer: Hongbin Ma <ma...@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";