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 <lid...@apache.org> Authored: Mon May 1 11:35:07 2017 +0800 Committer: hongbin ma <m...@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";
