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/05/19 10:04:32 UTC
[05/37] kylin git commit: KYLIN-2471 support subquery for
parenthesized sub-clause in JOIN
KYLIN-2471 support subquery for parenthesized sub-clause in JOIN
Project: http://git-wip-us.apache.org/repos/asf/kylin/repo
Commit: http://git-wip-us.apache.org/repos/asf/kylin/commit/0b51f7be
Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/0b51f7be
Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/0b51f7be
Branch: refs/heads/master
Commit: 0b51f7bec9d6f721d8e6a092f23b2f1d445f7a18
Parents: 299b584
Author: Dong Li <li...@apache.org>
Authored: Mon May 1 11:35:07 2017 +0800
Committer: hongbin ma <ma...@kyligence.io>
Committed: Mon May 1 11:35:07 2017 +0800
----------------------------------------------------------------------
.../query/util/CognosParenthesesEscape.java | 114 +++++++++++--------
.../query/util/CognosParentesesEscapeTest.java | 61 ----------
.../query/util/CognosParenthesesEscapeTest.java | 81 +++++++++++++
.../src/test/resources/query/cognos/query02.sql | 27 +++++
.../resources/query/cognos/query02.sql.expected | 27 +++++
.../src/test/resources/query/cognos/query03.sql | 29 +++++
.../resources/query/cognos/query03.sql.expected | 29 +++++
7 files changed, 262 insertions(+), 106 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/kylin/blob/0b51f7be/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 70ab8da..6d930a5 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
@@ -6,77 +6,101 @@
* 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.LinkedList;
+import java.util.Collections;
+import java.util.HashMap;
import java.util.List;
-import java.util.UUID;
+import java.util.Map;
+import java.util.Stack;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
-import org.apache.kylin.common.util.Pair;
+import com.google.common.collect.Lists;
-/**
- * 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 QueryUtil.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;
+ 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) {
+ if (sql == null || sql.isEmpty()) {
+ return sql;
+ }
+
+ Map<Integer, Integer> parenthesesPairs = findParenthesesPairs(sql);
+ if (parenthesesPairs.isEmpty()) {
+ // parentheses not found
+ return sql;
+ }
+
+ List<Integer> parentheses = Lists.newArrayList();
+ StringBuilder result = new StringBuilder(sql);
+
Matcher m;
- List<Pair<String, String>> matches = new LinkedList<>();
while (true) {
- m = PARENTTHESES_PATTERN.matcher(sql);
- if (!m.find())
+ m = FROM_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());
- }
+ int i = m.end() - 1;
+ while (i > m.start()) {
+ if (sql.charAt(i) == '(') {
+ parentheses.add(i);
+ }
+ i--;
+ }
- for (int i = matches.size() - 1; i >= 0; i--) {
- sql = sql.replaceAll(matches.get(i).getKey(), matches.get(i).getValue());
+ if (m.end() < sql.length()) {
+ sql = sql.substring(m.end());
+ } else {
+ break;
+ }
}
- return sql;
+ Collections.sort(parentheses);
+ for (int i = 0; i < parentheses.size(); i++) {
+ result.deleteCharAt(parentheses.get(i) - i);
+ result.deleteCharAt(parenthesesPairs.get(parentheses.get(i)) - i - 1);
+ }
+ return result.toString();
}
- private String generateRandomName() {
- UUID uuid = UUID.randomUUID();
- return uuid.toString().replace("-", "_") + "_" + (identifierNum++);
+ private Map<Integer, Integer> findParenthesesPairs(String sql) {
+ Map<Integer, Integer> result = new HashMap<>();
+ if (sql.length() > 1) {
+ Stack<Integer> lStack = new Stack<>();
+ boolean inStrVal = false;
+ for (int i = 0; i < sql.length(); i++) {
+ switch (sql.charAt(i)) {
+ case '(':
+ if (!inStrVal) {
+ lStack.push(i);
+ }
+ break;
+ case ')':
+ if (!inStrVal && !lStack.empty()) {
+ result.put(lStack.pop(), i);
+ }
+ break;
+ case '\'':
+ inStrVal = !inStrVal;
+ break;
+ default:
+ break;
+ }
+ }
+ }
+ return result;
}
-
}
http://git-wip-us.apache.org/repos/asf/kylin/blob/0b51f7be/query/src/test/java/org/apache/kylin/query/util/CognosParentesesEscapeTest.java
----------------------------------------------------------------------
diff --git a/query/src/test/java/org/apache/kylin/query/util/CognosParentesesEscapeTest.java b/query/src/test/java/org/apache/kylin/query/util/CognosParentesesEscapeTest.java
deleted file mode 100644
index 1f62d71..0000000
--- a/query/src/test/java/org/apache/kylin/query/util/CognosParentesesEscapeTest.java
+++ /dev/null
@@ -1,61 +0,0 @@
-/*
- * 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.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/0b51f7be/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
new file mode 100644
index 0000000..153c097
--- /dev/null
+++ b/query/src/test/java/org/apache/kylin/query/util/CognosParenthesesEscapeTest.java
@@ -0,0 +1,81 @@
+/*
+ * 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.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 CognosParenthesesEscapeTest {
+
+ @Test
+ public void basicTest() {
+ 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);
+ 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);
+ //System.out.println(transformed);
+ Assert.assertEquals(expected, transformed);
+ }
+
+ @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);
+ //System.out.println(transformed);
+ Assert.assertEquals(expected, transformed);
+ }
+
+ @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);
+ //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/0b51f7be/query/src/test/resources/query/cognos/query02.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/cognos/query02.sql b/query/src/test/resources/query/cognos/query02.sql
new file mode 100644
index 0000000..935251d
--- /dev/null
+++ b/query/src/test/resources/query/cognos/query02.sql
@@ -0,0 +1,27 @@
+--
+-- 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 "X1"."DIM1_1" "DIM1_1"
+ ,"TABLE2"."DIM2_1" "DIM2_1"
+ ,SUM("FACT"."M1") "M1"
+ ,SUM("FACT"."M2") "M2"
+ FROM ("COGNOS"."FACT" "FACT" LEFT OUTER JOIN (SELECT "DIM1_1", "PK_1" FROM COGNOS"."TABLE1" WHERE DIM1_1 = '1') "X1" ON "FACT"."FK_1" = "X1"."PK_1")
+ LEFT OUTER JOIN "COGNOS"."TABLE2" "TABLE2"
+ ON "FACT"."FK_2" = "TABLE2"."PK_2"
+ GROUP BY "TABLE2"."DIM2_1"
+ ,"X1"."DIM1_1";
http://git-wip-us.apache.org/repos/asf/kylin/blob/0b51f7be/query/src/test/resources/query/cognos/query02.sql.expected
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/cognos/query02.sql.expected b/query/src/test/resources/query/cognos/query02.sql.expected
new file mode 100644
index 0000000..4d4f016
--- /dev/null
+++ b/query/src/test/resources/query/cognos/query02.sql.expected
@@ -0,0 +1,27 @@
+--
+-- 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 "X1"."DIM1_1" "DIM1_1"
+ ,"TABLE2"."DIM2_1" "DIM2_1"
+ ,SUM("FACT"."M1") "M1"
+ ,SUM("FACT"."M2") "M2"
+ FROM "COGNOS"."FACT" "FACT" LEFT OUTER JOIN (SELECT "DIM1_1", "PK_1" FROM COGNOS"."TABLE1" WHERE DIM1_1 = '1') "X1" ON "FACT"."FK_1" = "X1"."PK_1"
+ LEFT OUTER JOIN "COGNOS"."TABLE2" "TABLE2"
+ ON "FACT"."FK_2" = "TABLE2"."PK_2"
+ GROUP BY "TABLE2"."DIM2_1"
+ ,"X1"."DIM1_1";
http://git-wip-us.apache.org/repos/asf/kylin/blob/0b51f7be/query/src/test/resources/query/cognos/query03.sql
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/cognos/query03.sql b/query/src/test/resources/query/cognos/query03.sql
new file mode 100644
index 0000000..bfa483f
--- /dev/null
+++ b/query/src/test/resources/query/cognos/query03.sql
@@ -0,0 +1,29 @@
+--
+-- 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 "X1"."DIM1_1" "DIM1_1"
+ ,"TABLE2"."DIM2_1" "DIM2_1"
+ ,SUM("FACT"."M1") "M1"
+ ,SUM("FACT"."M2") "M2"
+ FROM
+ ( (
+ SELECT "F1" FROM "T1") X0 LEFT OUTER JOIN (SELECT "DIM1_1", "PK_1" FROM COGNOS"."TABLE1" WHERE DIM1_1 = '1') "X1" ON "FACT"."FK_1" = "X1"."PK_1")
+ LEFT OUTER JOIN "COGNOS"."TABLE2" "TABLE2"
+ ON "FACT"."FK_2" = "TABLE2"."PK_2"
+ GROUP BY "TABLE2"."DIM2_1"
+ ,"X1"."DIM1_1";
http://git-wip-us.apache.org/repos/asf/kylin/blob/0b51f7be/query/src/test/resources/query/cognos/query03.sql.expected
----------------------------------------------------------------------
diff --git a/query/src/test/resources/query/cognos/query03.sql.expected b/query/src/test/resources/query/cognos/query03.sql.expected
new file mode 100644
index 0000000..20215a9
--- /dev/null
+++ b/query/src/test/resources/query/cognos/query03.sql.expected
@@ -0,0 +1,29 @@
+--
+-- 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 "X1"."DIM1_1" "DIM1_1"
+ ,"TABLE2"."DIM2_1" "DIM2_1"
+ ,SUM("FACT"."M1") "M1"
+ ,SUM("FACT"."M2") "M2"
+ FROM
+ (
+ SELECT "F1" FROM "T1") X0 LEFT OUTER JOIN (SELECT "DIM1_1", "PK_1" FROM COGNOS"."TABLE1" WHERE DIM1_1 = '1') "X1" ON "FACT"."FK_1" = "X1"."PK_1"
+ LEFT OUTER JOIN "COGNOS"."TABLE2" "TABLE2"
+ ON "FACT"."FK_2" = "TABLE2"."PK_2"
+ GROUP BY "TABLE2"."DIM2_1"
+ ,"X1"."DIM1_1";