more test queries and minor refactor
Project: http://git-wip-us.apache.org/repos/asf/kylin/repo Commit: http://git-wip-us.apache.org/repos/asf/kylin/commit/ddeb7452 Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/ddeb7452 Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/ddeb7452 Branch: refs/heads/v1.5.4-release2 Commit: ddeb7452a63279311b74fcfb5f7ed0252e3d54fd Parents: fc7281b Author: Hongbin Ma <mahong...@apache.org> Authored: Tue Sep 6 16:09:59 2016 +0800 Committer: Hongbin Ma <mahong...@apache.org> Committed: Thu Sep 8 10:15:08 2016 +0800 ---------------------------------------------------------------------- .../java/org/apache/kylin/gridtable/GTUtil.java | 63 +------- .../filter/BuiltInFunctionTupleFilter.java | 12 +- .../filter/EvaluatableFunctionTupleFilter.java | 151 +++++++++++++++++++ .../filter/EvaluatableLikeFunction.java | 114 -------------- .../kylin/metadata/filter/TupleFilter.java | 2 +- .../metadata/filter/TupleFilterSerializer.java | 4 +- .../metadata/filter/function/BuiltInMethod.java | 9 ++ .../test_kylin_inner_join_model_desc.json | 103 ++++++++----- .../test_kylin_inner_join_view_model_desc.json | 24 ++- .../test_kylin_left_join_model_desc.json | 31 ++-- .../test_kylin_left_join_view_model_desc.json | 24 ++- .../apache/kylin/query/ITKylinQueryTest.java | 25 +-- .../src/test/resources/query/sql/query45.sql | 24 --- .../src/test/resources/query/sql/query46.sql | 19 --- .../src/test/resources/query/sql/query47.sql | 19 --- .../src/test/resources/query/sql/query48.sql | 19 --- .../src/test/resources/query/sql/query55.sql | 19 --- .../src/test/resources/query/sql/query83.sql | 33 ++++ .../src/test/resources/query/sql/query84.sql | 33 ++++ .../src/test/resources/query/sql/query92.sql | 6 +- .../src/test/resources/query/sql/query93.sql | 6 +- .../src/test/resources/query/sql/query94.sql | 6 +- .../src/test/resources/query/sql/query95.sql | 6 +- .../test/resources/query/sql_like/query04.sql | 22 +++ .../test/resources/query/sql_like/query10.sql | 13 ++ .../test/resources/query/sql_like/query15.sql | 13 ++ .../test/resources/query/sql_like/query16.sql | 13 ++ .../test/resources/query/sql_like/query17.sql | 13 ++ .../test/resources/query/sql_lookup/query45.sql | 24 +++ .../test/resources/query/sql_lookup/query46.sql | 19 +++ .../test/resources/query/sql_lookup/query47.sql | 19 +++ .../test/resources/query/sql_lookup/query48.sql | 19 +++ .../test/resources/query/sql_lookup/query55.sql | 19 +++ .../test/resources/query/sql_raw/query21.sql | 24 +++ .../test/resources/query/sql_raw/query22.sql | 24 +++ .../test/resources/query/sql_raw/query23.sql | 24 +++ .../test/resources/query/sql_raw/query24.sql | 24 +++ .../test/resources/query/sql_raw/query25.sql | 24 +++ .../query/sql_raw/query26.sql.disabled | 24 +++ .../kylin/query/relnode/OLAPAggregateRel.java | 15 +- .../apache/kylin/query/relnode/OLAPContext.java | 7 + .../apache/kylin/query/relnode/OLAPJoinRel.java | 40 ++--- .../kylin/query/relnode/OLAPProjectRel.java | 6 +- .../apache/kylin/query/routing/Candidate.java | 5 +- 44 files changed, 753 insertions(+), 390 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/core-cube/src/main/java/org/apache/kylin/gridtable/GTUtil.java ---------------------------------------------------------------------- diff --git a/core-cube/src/main/java/org/apache/kylin/gridtable/GTUtil.java b/core-cube/src/main/java/org/apache/kylin/gridtable/GTUtil.java index 7406e81..8d310a3 100644 --- a/core-cube/src/main/java/org/apache/kylin/gridtable/GTUtil.java +++ b/core-cube/src/main/java/org/apache/kylin/gridtable/GTUtil.java @@ -18,7 +18,6 @@ package org.apache.kylin.gridtable; -import java.lang.reflect.InvocationTargetException; import java.nio.ByteBuffer; import java.util.Collection; import java.util.List; @@ -26,7 +25,6 @@ import java.util.Set; import org.apache.kylin.common.util.ByteArray; import org.apache.kylin.common.util.BytesUtil; -import org.apache.kylin.metadata.filter.BuiltInFunctionTupleFilter; import org.apache.kylin.metadata.filter.ColumnTupleFilter; import org.apache.kylin.metadata.filter.CompareTupleFilter; import org.apache.kylin.metadata.filter.ConstantTupleFilter; @@ -108,11 +106,11 @@ public class GTUtil { }; } - private static class GTConvertDecorator implements TupleFilterSerializer.Decorator { - private final Set<TblColRef> unevaluatableColumnCollector; - private final List<TblColRef> colMapping; - private final GTInfo info; - private final boolean encodeConstants; + protected static class GTConvertDecorator implements TupleFilterSerializer.Decorator { + protected final Set<TblColRef> unevaluatableColumnCollector; + protected final List<TblColRef> colMapping; + protected final GTInfo info; + protected final boolean encodeConstants; public GTConvertDecorator(Set<TblColRef> unevaluatableColumnCollector, List<TblColRef> colMapping, GTInfo info, boolean encodeConstants) { this.unevaluatableColumnCollector = unevaluatableColumnCollector; @@ -152,19 +150,12 @@ public class GTUtil { if (encodeConstants && filter instanceof CompareTupleFilter) { return encodeConstants((CompareTupleFilter) filter); } - if (encodeConstants && filter instanceof BuiltInFunctionTupleFilter) { - if (!((BuiltInFunctionTupleFilter) filter).hasNested()) { - return encodeConstants((BuiltInFunctionTupleFilter) filter); - } else { - throw new IllegalStateException("Nested BuiltInFunctionTupleFilter is not supported to be pushed down"); - } - } return filter; } @SuppressWarnings({ "rawtypes", "unchecked" }) - private TupleFilter encodeConstants(CompareTupleFilter oldCompareFilter) { + protected TupleFilter encodeConstants(CompareTupleFilter oldCompareFilter) { // extract ColumnFilter & ConstantFilter TblColRef externalCol = oldCompareFilter.getColumn(); @@ -258,49 +249,9 @@ public class GTUtil { return result; } - @SuppressWarnings({ "rawtypes", "unchecked" }) - private TupleFilter encodeConstants(BuiltInFunctionTupleFilter funcFilter) { - // extract ColumnFilter & ConstantFilter - TblColRef externalCol = funcFilter.getColumn(); - - if (externalCol == null) { - return funcFilter; - } - - Collection constValues = funcFilter.getConstantTupleFilter().getValues(); - if (constValues == null || constValues.isEmpty()) { - return funcFilter; - } - - BuiltInFunctionTupleFilter newFuncFilter; - try { - newFuncFilter = funcFilter.getClass().getConstructor(String.class).newInstance(funcFilter.getName()); - } catch (InstantiationException | IllegalAccessException | NoSuchMethodException | InvocationTargetException e) { - throw new RuntimeException(e); - } - newFuncFilter.addChild(new ColumnTupleFilter(externalCol)); - - int col = colMapping == null ? externalCol.getColumnDesc().getZeroBasedIndex() : colMapping.indexOf(externalCol); - - ByteArray code; - - // translate constant into code - Set newValues = Sets.newHashSet(); - for (Object value : constValues) { - code = translate(col, value, 0); - if (code == null) { - throw new IllegalStateException("Cannot serialize BuiltInFunctionTupleFilter"); - } - newValues.add(code); - } - newFuncFilter.addChild(new ConstantTupleFilter(newValues)); - - return newFuncFilter; - } - transient ByteBuffer buf; - private ByteArray translate(int col, Object value, int roundingFlag) { + protected ByteArray translate(int col, Object value, int roundingFlag) { try { buf.clear(); info.codeSystem.encodeColumnValue(col, value, roundingFlag, buf); http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/core-metadata/src/main/java/org/apache/kylin/metadata/filter/BuiltInFunctionTupleFilter.java ---------------------------------------------------------------------- diff --git a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/BuiltInFunctionTupleFilter.java b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/BuiltInFunctionTupleFilter.java index 40afb18..734b374 100644 --- a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/BuiltInFunctionTupleFilter.java +++ b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/BuiltInFunctionTupleFilter.java @@ -43,6 +43,7 @@ public class BuiltInFunctionTupleFilter extends FunctionTupleFilter { protected TupleFilter columnContainerFilter;//might be a ColumnTupleFilter(simple case) or FunctionTupleFilter(complex case like substr(lower())) protected ConstantTupleFilter constantTupleFilter; protected int colPosition; + protected int constantPosition; protected Method method; protected List<Serializable> methodParams; protected boolean isValidFunc = false; @@ -69,6 +70,10 @@ public class BuiltInFunctionTupleFilter extends FunctionTupleFilter { return constantTupleFilter; } + public TupleFilter getColumnContainerFilter() { + return columnContainerFilter; + } + public TblColRef getColumn() { if (columnContainerFilter == null) return null; @@ -81,10 +86,6 @@ public class BuiltInFunctionTupleFilter extends FunctionTupleFilter { throw new UnsupportedOperationException("Wrong type TupleFilter in FunctionTupleFilter."); } - public boolean hasNested() { - return (columnContainerFilter != null && columnContainerFilter instanceof BuiltInFunctionTupleFilter); - } - public Object invokeFunction(Object input) throws InvocationTargetException, IllegalAccessException { if (columnContainerFilter instanceof ColumnTupleFilter) methodParams.set(colPosition, (Serializable) input); @@ -107,6 +108,7 @@ public class BuiltInFunctionTupleFilter extends FunctionTupleFilter { this.constantTupleFilter = (ConstantTupleFilter) child; Serializable constVal = (Serializable) child.getValues().iterator().next(); try { + constantPosition = methodParams.size(); Class<?> clazz = Primitives.wrap(method.getParameterTypes()[methodParams.size()]); if (!Primitives.isWrapperType(clazz)) methodParams.add(constVal); @@ -131,7 +133,7 @@ public class BuiltInFunctionTupleFilter extends FunctionTupleFilter { } @Override - public Collection<String> getValues() { + public Collection<?> getValues() { throw new UnsupportedOperationException("Function filter cannot be evaluated immediately"); } http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/core-metadata/src/main/java/org/apache/kylin/metadata/filter/EvaluatableFunctionTupleFilter.java ---------------------------------------------------------------------- diff --git a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/EvaluatableFunctionTupleFilter.java b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/EvaluatableFunctionTupleFilter.java new file mode 100644 index 0000000..ff24172 --- /dev/null +++ b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/EvaluatableFunctionTupleFilter.java @@ -0,0 +1,151 @@ +/* + * 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.metadata.filter; + +import java.lang.reflect.InvocationTargetException; +import java.nio.ByteBuffer; +import java.util.Collection; +import java.util.List; + +import org.apache.kylin.common.util.ByteArray; +import org.apache.kylin.common.util.BytesUtil; +import org.apache.kylin.metadata.datatype.DataType; +import org.apache.kylin.metadata.datatype.StringSerializer; +import org.apache.kylin.metadata.model.TblColRef; +import org.apache.kylin.metadata.tuple.IEvaluatableTuple; + +import com.google.common.collect.Lists; + +public class EvaluatableFunctionTupleFilter extends BuiltInFunctionTupleFilter { + + private boolean constantsInitted = false; + + //about non-like + private List<Object> values; + private Object tupleValue; + + public EvaluatableFunctionTupleFilter(String name) { + super(name, FilterOperatorEnum.EVAL_FUNC); + values = Lists.newArrayListWithCapacity(1); + values.add(null); + } + + @Override + public boolean evaluate(IEvaluatableTuple tuple, IFilterCodeSystem cs) { + + // extract tuple value + Object tupleValue = null; + for (TupleFilter filter : this.children) { + if (!isConstant(filter)) { + filter.evaluate(tuple, cs); + tupleValue = filter.getValues().iterator().next(); + } + } + + TblColRef tblColRef = this.getColumn(); + DataType strDataType = DataType.getType("string"); + if (tblColRef.getType() != strDataType) { + throw new IllegalStateException("Only String type is allow in BuiltInFunction"); + } + ByteArray valueByteArray = (ByteArray) tupleValue; + StringSerializer serializer = new StringSerializer(strDataType); + String value = serializer.deserialize(ByteBuffer.wrap(valueByteArray.array(), valueByteArray.offset(), valueByteArray.length())); + + try { + if (isLikeFunction()) { + return (Boolean) invokeFunction(value); + } else { + this.tupleValue = invokeFunction(value); + //convert back to ByteArray format because the outer EvaluatableFunctionTupleFilter assumes input as ByteArray + ByteBuffer buffer = ByteBuffer.allocate(valueByteArray.length() * 2); + serializer.serialize((String) this.tupleValue, buffer); + this.tupleValue = new ByteArray(buffer.array(), 0, buffer.position()); + + return true; + } + } catch (InvocationTargetException | IllegalAccessException e) { + throw new RuntimeException(e); + } + } + + @Override + public Collection<?> getValues() { + this.values.set(0, tupleValue); + return values; + } + + @Override + public void serialize(IFilterCodeSystem<?> cs, ByteBuffer buffer) { + if (!isValid()) { + throw new IllegalStateException("must be valid"); + } + BytesUtil.writeUTFString(name, buffer); + } + + @Override + public void deserialize(IFilterCodeSystem<?> cs, ByteBuffer buffer) { + this.name = BytesUtil.readUTFString(buffer); + this.initMethod(); + } + + @Override + public boolean isEvaluable() { + return true; + } + + private boolean isConstant(TupleFilter filter) { + return (filter instanceof ConstantTupleFilter) || (filter instanceof DynamicTupleFilter); + } + + @Override + public Object invokeFunction(Object input) throws InvocationTargetException, IllegalAccessException { + if (isLikeFunction()) + initConstants(); + return super.invokeFunction(input); + } + + private void initConstants() { + if (constantsInitted) { + return; + } + //will replace the ByteArray pattern to String type + ByteArray byteArray = (ByteArray) methodParams.get(constantPosition); + StringSerializer s = new StringSerializer(DataType.getType("string")); + String pattern = s.deserialize(ByteBuffer.wrap(byteArray.array(), byteArray.offset(), byteArray.length())); + //TODO + //pattern = pattern.toLowerCase();//to remove upper case + methodParams.set(constantPosition, pattern); + constantsInitted = true; + } + + //even for "tolower(s)/toupper(s)/substring(like) like pattern", the like pattern can be used for index searching + public String getLikePattern() { + if (!isLikeFunction()) { + return null; + } + + initConstants(); + return (String) methodParams.get(1); + } + + public boolean isLikeFunction() { + return "like".equalsIgnoreCase(this.getName()); + } + +} http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/core-metadata/src/main/java/org/apache/kylin/metadata/filter/EvaluatableLikeFunction.java ---------------------------------------------------------------------- diff --git a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/EvaluatableLikeFunction.java b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/EvaluatableLikeFunction.java deleted file mode 100644 index 28e544b..0000000 --- a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/EvaluatableLikeFunction.java +++ /dev/null @@ -1,114 +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.metadata.filter; - -import java.lang.reflect.InvocationTargetException; -import java.nio.ByteBuffer; - -import org.apache.kylin.common.util.ByteArray; -import org.apache.kylin.common.util.BytesUtil; -import org.apache.kylin.metadata.datatype.DataType; -import org.apache.kylin.metadata.datatype.StringSerializer; -import org.apache.kylin.metadata.tuple.IEvaluatableTuple; - -/** - * typically like will be handled by BuiltInFunctionTupleFilter rather than this - */ -public class EvaluatableLikeFunction extends BuiltInFunctionTupleFilter { - - private boolean patternExtracted; - - public EvaluatableLikeFunction(String name) { - super(name, FilterOperatorEnum.LIKE); - } - - @Override - public boolean evaluate(IEvaluatableTuple tuple, IFilterCodeSystem cs) { - - // extract tuple value - Object tupleValue = null; - for (TupleFilter filter : this.children) { - if (!isConstant(filter)) { - filter.evaluate(tuple, cs); - tupleValue = filter.getValues().iterator().next(); - } - } - - // consider null case - if (cs.isNull(tupleValue)) { - return false; - } - - ByteArray valueByteArray = (ByteArray) tupleValue; - StringSerializer serializer = new StringSerializer(DataType.getType("string")); - String value = serializer.deserialize(ByteBuffer.wrap(valueByteArray.array(), valueByteArray.offset(), valueByteArray.length())); - try { - return (Boolean) invokeFunction(value); - } catch (InvocationTargetException | IllegalAccessException e) { - throw new RuntimeException(e); - } - } - - @Override - public void serialize(IFilterCodeSystem<?> cs, ByteBuffer buffer) { - if (!isValid()) { - throw new IllegalStateException("must be valid"); - } - if (methodParams.size() != 2 || methodParams.get(0) != null || methodParams.get(1) == null) { - throw new IllegalArgumentException("bad methodParams: " + methodParams); - } - BytesUtil.writeUTFString(name, buffer); - } - - @Override - public void deserialize(IFilterCodeSystem<?> cs, ByteBuffer buffer) { - this.name = BytesUtil.readUTFString(buffer); - this.initMethod(); - } - - @Override - public boolean isEvaluable() { - return true; - } - - private boolean isConstant(TupleFilter filter) { - return (filter instanceof ConstantTupleFilter) || (filter instanceof DynamicTupleFilter); - } - - @Override - public Object invokeFunction(Object input) throws InvocationTargetException, IllegalAccessException { - if (!patternExtracted) { - this.getLikePattern(); - } - return super.invokeFunction(input); - } - - //will replace the ByteArray pattern to String type - public String getLikePattern() { - if (!patternExtracted) { - ByteArray byteArray = (ByteArray) methodParams.get(1); - StringSerializer s = new StringSerializer(DataType.getType("string")); - String pattern = s.deserialize(ByteBuffer.wrap(byteArray.array(), byteArray.offset(), byteArray.length())); - methodParams.set(1, pattern); - patternExtracted = true; - } - return (String) methodParams.get(1); - } - -} http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilter.java ---------------------------------------------------------------------- diff --git a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilter.java b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilter.java index 2fb4e1f..900cc35 100644 --- a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilter.java +++ b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilter.java @@ -38,7 +38,7 @@ import com.google.common.collect.Maps; public abstract class TupleFilter { public enum FilterOperatorEnum { - EQ(1), NEQ(2), GT(3), LT(4), GTE(5), LTE(6), ISNULL(7), ISNOTNULL(8), IN(9), NOTIN(10), AND(20), OR(21), NOT(22), COLUMN(30), CONSTANT(31), DYNAMIC(32), EXTRACT(33), CASE(34), FUNCTION(35), MASSIN(36), LIKE(37); + EQ(1), NEQ(2), GT(3), LT(4), GTE(5), LTE(6), ISNULL(7), ISNOTNULL(8), IN(9), NOTIN(10), AND(20), OR(21), NOT(22), COLUMN(30), CONSTANT(31), DYNAMIC(32), EXTRACT(33), CASE(34), FUNCTION(35), MASSIN(36), EVAL_FUNC(37); private final int value; http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilterSerializer.java ---------------------------------------------------------------------- diff --git a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilterSerializer.java b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilterSerializer.java index 0aca9a1..04984f2 100644 --- a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilterSerializer.java +++ b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/TupleFilterSerializer.java @@ -188,8 +188,8 @@ public class TupleFilterSerializer { case FUNCTION: filter = new BuiltInFunctionTupleFilter(null); break; - case LIKE: - filter = new EvaluatableLikeFunction(null); + case EVAL_FUNC: + filter = new EvaluatableFunctionTupleFilter(null); break; case MASSIN: filter = new MassInTupleFilter(); http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/core-metadata/src/main/java/org/apache/kylin/metadata/filter/function/BuiltInMethod.java ---------------------------------------------------------------------- diff --git a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/function/BuiltInMethod.java b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/function/BuiltInMethod.java index 7b241cc..2f28fae 100644 --- a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/function/BuiltInMethod.java +++ b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/function/BuiltInMethod.java @@ -47,6 +47,9 @@ public enum BuiltInMethod { /** SQL {@code LIKE} function. */ public static boolean like(String s, String pattern) { + if (s == null) + return false; + final String regex = Like.sqlToRegexLike(pattern, null); return Pattern.matches(regex, s); } @@ -95,16 +98,22 @@ public enum BuiltInMethod { /** SQL SUBSTRING(string FROM ... FOR ...) function. */ public static String substring(String s, int from, int for_) { + if (s == null) + return null; return s.substring(from - 1, Math.min(from - 1 + for_, s.length())); } /** SQL UPPER(string) function. */ public static String upper(String s) { + if (s == null) + return null; return s.toUpperCase(); } /** SQL LOWER(string) function. */ public static String lower(String s) { + if (s == null) + return null; return s.toLowerCase(); } http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_model_desc.json ---------------------------------------------------------------------- diff --git a/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_model_desc.json b/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_model_desc.json index cb43d6f..6e444e5 100644 --- a/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_model_desc.json +++ b/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_model_desc.json @@ -1,35 +1,58 @@ { - "uuid" : "ff527b94-f860-44c3-8452-93b17774c647", - "name" : "test_kylin_inner_join_model_desc", - "lookups" : [ { - "table" : "EDW.TEST_CAL_DT", - "join" : { - "type" : "inner", - "primary_key" : [ "CAL_DT" ], - "foreign_key" : [ "CAL_DT" ] - } - }, { - "table" : "DEFAULT.TEST_CATEGORY_GROUPINGS", - "join" : { - "type" : "inner", - "primary_key" : [ "LEAF_CATEG_ID", "SITE_ID" ], - "foreign_key" : [ "LEAF_CATEG_ID", "LSTG_SITE_ID" ] - } - }, { - "table" : "EDW.TEST_SITES", - "join" : { - "type" : "inner", - "primary_key" : [ "SITE_ID" ], - "foreign_key" : [ "LSTG_SITE_ID" ] - } - }, { - "table" : "EDW.TEST_SELLER_TYPE_DIM", - "join" : { - "type" : "inner", - "primary_key" : [ "SELLER_TYPE_CD" ], - "foreign_key" : [ "SLR_SEGMENT_CD" ] + "uuid": "ff527b94-f860-44c3-8452-93b17774c647", + "name": "test_kylin_inner_join_model_desc", + "lookups": [ + { + "table": "EDW.TEST_CAL_DT", + "join": { + "type": "inner", + "primary_key": [ + "CAL_DT" + ], + "foreign_key": [ + "CAL_DT" + ] + } + }, + { + "table": "DEFAULT.TEST_CATEGORY_GROUPINGS", + "join": { + "type": "inner", + "primary_key": [ + "LEAF_CATEG_ID", + "SITE_ID" + ], + "foreign_key": [ + "LEAF_CATEG_ID", + "LSTG_SITE_ID" + ] + } + }, + { + "table": "EDW.TEST_SITES", + "join": { + "type": "inner", + "primary_key": [ + "SITE_ID" + ], + "foreign_key": [ + "LSTG_SITE_ID" + ] + } + }, + { + "table": "EDW.TEST_SELLER_TYPE_DIM", + "join": { + "type": "inner", + "primary_key": [ + "SELLER_TYPE_CD" + ], + "foreign_key": [ + "SLR_SEGMENT_CD" + ] + } } - } ], + ], "dimensions": [ { "table": "default.test_kylin_fact", @@ -81,16 +104,16 @@ } ], "metrics": [ - "PRICE", - "ITEM_COUNT", - "SELLER_ID" + "PRICE", + "ITEM_COUNT", + "SELLER_ID" ], - "last_modified" : 1422435345352, - "fact_table" : "DEFAULT.TEST_KYLIN_FACT", - "filter_condition" : null, - "partition_desc" : { - "partition_date_column" : "DEFAULT.TEST_KYLIN_FACT.cal_dt", - "partition_date_start" : 0, - "partition_type" : "APPEND" + "last_modified": 1422435345352, + "fact_table": "DEFAULT.TEST_KYLIN_FACT", + "filter_condition": null, + "partition_desc": { + "partition_date_column": "DEFAULT.TEST_KYLIN_FACT.cal_dt", + "partition_date_start": 0, + "partition_type": "APPEND" } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_view_model_desc.json ---------------------------------------------------------------------- diff --git a/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_view_model_desc.json b/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_view_model_desc.json index acdcab8..621eccc 100644 --- a/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_view_model_desc.json +++ b/examples/test_case_data/localmeta/model_desc/test_kylin_inner_join_view_model_desc.json @@ -6,8 +6,12 @@ "table": "EDW.V_TEST_CAL_DT", "join": { "type": "inner", - "primary_key": ["CAL_DT"], - "foreign_key": ["CAL_DT"] + "primary_key": [ + "CAL_DT" + ], + "foreign_key": [ + "CAL_DT" + ] } }, { @@ -28,16 +32,24 @@ "table": "EDW.TEST_SITES", "join": { "type": "inner", - "primary_key": ["SITE_ID"], - "foreign_key": ["LSTG_SITE_ID"] + "primary_key": [ + "SITE_ID" + ], + "foreign_key": [ + "LSTG_SITE_ID" + ] } }, { "table": "EDW.TEST_SELLER_TYPE_DIM", "join": { "type": "inner", - "primary_key": ["SELLER_TYPE_CD"], - "foreign_key": ["SLR_SEGMENT_CD"] + "primary_key": [ + "SELLER_TYPE_CD" + ], + "foreign_key": [ + "SLR_SEGMENT_CD" + ] } } ], http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_model_desc.json ---------------------------------------------------------------------- diff --git a/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_model_desc.json b/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_model_desc.json index 735ca05..dab99f9 100644 --- a/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_model_desc.json +++ b/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_model_desc.json @@ -6,8 +6,12 @@ "table": "EDW.TEST_CAL_DT", "join": { "type": "left", - "primary_key": ["CAL_DT"], - "foreign_key": ["CAL_DT"] + "primary_key": [ + "CAL_DT" + ], + "foreign_key": [ + "CAL_DT" + ] } }, { @@ -28,16 +32,24 @@ "table": "EDW.TEST_SITES", "join": { "type": "left", - "primary_key": ["SITE_ID"], - "foreign_key": ["LSTG_SITE_ID"] + "primary_key": [ + "SITE_ID" + ], + "foreign_key": [ + "LSTG_SITE_ID" + ] } }, { "table": "EDW.TEST_SELLER_TYPE_DIM", "join": { "type": "left", - "primary_key": ["SELLER_TYPE_CD"], - "foreign_key": ["SLR_SEGMENT_CD"] + "primary_key": [ + "SELLER_TYPE_CD" + ], + "foreign_key": [ + "SLR_SEGMENT_CD" + ] } } ], @@ -45,11 +57,12 @@ { "table": "default.test_kylin_fact", "columns": [ - "TRANS_ID", - "CAL_DT", "lstg_format_name", "LSTG_SITE_ID", "SLR_SEGMENT_CD", + "TRANS_ID", + "CAL_DT", + "LEAF_CATEG_ID", "SELLER_ID" ] }, @@ -83,7 +96,7 @@ ] }, { - "table": "EDW.TEST_CAL_DT", + "table": "edw.test_cal_dt", "columns": [ "cal_dt", "week_beg_dt" http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_view_model_desc.json ---------------------------------------------------------------------- diff --git a/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_view_model_desc.json b/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_view_model_desc.json index a4af260..819b8b0 100644 --- a/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_view_model_desc.json +++ b/examples/test_case_data/localmeta/model_desc/test_kylin_left_join_view_model_desc.json @@ -6,8 +6,12 @@ "table": "EDW.V_TEST_CAL_DT", "join": { "type": "left", - "primary_key": ["CAL_DT"], - "foreign_key": ["CAL_DT"] + "primary_key": [ + "CAL_DT" + ], + "foreign_key": [ + "CAL_DT" + ] } }, { @@ -28,16 +32,24 @@ "table": "EDW.TEST_SITES", "join": { "type": "left", - "primary_key": ["SITE_ID"], - "foreign_key": ["LSTG_SITE_ID"] + "primary_key": [ + "SITE_ID" + ], + "foreign_key": [ + "LSTG_SITE_ID" + ] } }, { "table": "EDW.TEST_SELLER_TYPE_DIM", "join": { "type": "left", - "primary_key": ["SELLER_TYPE_CD"], - "foreign_key": ["SLR_SEGMENT_CD"] + "primary_key": [ + "SELLER_TYPE_CD" + ], + "foreign_key": [ + "SLR_SEGMENT_CD" + ] } } ], http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java b/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java index 4657a5c..375b198 100644 --- a/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java +++ b/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java @@ -55,12 +55,13 @@ public class ITKylinQueryTest extends KylinTestBase { Map<RealizationType, Integer> priorities = Maps.newHashMap(); priorities.put(RealizationType.HYBRID, 0); priorities.put(RealizationType.CUBE, 0); + priorities.put(RealizationType.INVERTED_INDEX, 0); Candidate.setPriorities(priorities); - joinType = "inner"; + joinType = "left"; setupAll(); - + RemoveBlackoutRealizationsRule.blackouts.add("CUBE[name=test_kylin_cube_with_view_left_join_empty]"); RemoveBlackoutRealizationsRule.blackouts.add("CUBE[name=test_kylin_cube_with_view_inner_join_empty]"); } @@ -192,18 +193,25 @@ public class ITKylinQueryTest extends KylinTestBase { } @Test - public void testDimDistinctCountQuery() throws Exception { - execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_distinct_dim", null, true); + public void testTopNQuery() throws Exception { + if ("left".equalsIgnoreCase(joinType)) { + this.execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_topn", null, true); + } } @Test public void testPreciselyDistinctCountQuery() throws Exception { if ("left".equalsIgnoreCase(joinType)) { - execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/temp", null, true); + execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_distinct_precisely", null, true); } } @Test + public void testDimDistinctCountQuery() throws Exception { + execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_distinct_dim", null, true); + } + + @Test public void testStreamingTableQuery() throws Exception { execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_streaming", null, true); } @@ -276,13 +284,6 @@ public class ITKylinQueryTest extends KylinTestBase { } @Test - public void testTopNQuery() throws Exception { - if ("left".equalsIgnoreCase(joinType)) { - this.execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_topn", null, true); - } - } - - @Test public void testRawQuery() throws Exception { this.execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_raw", null, true); } http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query45.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query45.sql b/kylin-it/src/test/resources/query/sql/query45.sql deleted file mode 100644 index ea964ae..0000000 --- a/kylin-it/src/test/resources/query/sql/query45.sql +++ /dev/null @@ -1,24 +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. --- - -select count(*) as CNT from edw.test_cal_dt - - - - - http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query46.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query46.sql b/kylin-it/src/test/resources/query/sql/query46.sql deleted file mode 100644 index 3bfe9d9..0000000 --- a/kylin-it/src/test/resources/query/sql/query46.sql +++ /dev/null @@ -1,19 +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. --- - -select count(*) as CNT from test_category_groupings http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query47.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query47.sql b/kylin-it/src/test/resources/query/sql/query47.sql deleted file mode 100644 index cbd2c6d..0000000 --- a/kylin-it/src/test/resources/query/sql/query47.sql +++ /dev/null @@ -1,19 +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. --- - -select count(*) as CNT from edw.test_seller_type_dim http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query48.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query48.sql b/kylin-it/src/test/resources/query/sql/query48.sql deleted file mode 100644 index 54ddb31..0000000 --- a/kylin-it/src/test/resources/query/sql/query48.sql +++ /dev/null @@ -1,19 +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. --- - -select count(*) as CNT from edw.test_sites http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query55.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query55.sql b/kylin-it/src/test/resources/query/sql/query55.sql deleted file mode 100644 index 346a7d7..0000000 --- a/kylin-it/src/test/resources/query/sql/query55.sql +++ /dev/null @@ -1,19 +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. --- - -select count(*) as c from edw.test_cal_dt as test_cal_dt where extract(YEAR from test_cal_dt.cal_dt) = 2012 http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query83.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query83.sql b/kylin-it/src/test/resources/query/sql/query83.sql new file mode 100644 index 0000000..1fbbd33 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query83.sql @@ -0,0 +1,33 @@ +-- +-- 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 test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV + , count(1) as TRANS_CNT + from test_kylin_fact + left JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + left JOIN test_category_groupings + on test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id and + test_kylin_fact.lstg_site_id = test_category_groupings.site_id + left JOIN edw.test_sites as test_sites + on test_kylin_fact.lstg_site_id = test_sites.site_id + left JOIN edw.test_seller_type_dim as test_seller_type_dim + on test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + where test_kylin_fact.lstg_format_name='FP-GTC' + and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01' + and test_kylin_fact.cal_dt between DATE '2013-06-01' and DATE '2013-09-01' + group by test_cal_dt.week_beg_dt \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query84.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query84.sql b/kylin-it/src/test/resources/query/sql/query84.sql new file mode 100644 index 0000000..2560465 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query84.sql @@ -0,0 +1,33 @@ +-- +-- 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 test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV + , count(1) as TRANS_CNT + from test_kylin_fact + left JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + left JOIN test_category_groupings + on test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id and + test_kylin_fact.lstg_site_id = test_category_groupings.site_id + left JOIN edw.test_sites as test_sites + on test_kylin_fact.lstg_site_id = test_sites.site_id + left JOIN edw.test_seller_type_dim as test_seller_type_dim + on test_kylin_fact.slr_segment_cd = test_seller_type_dim.seller_type_cd + where test_kylin_fact.lstg_format_name='FP-GTC' + and test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01' + and test_cal_dt.cal_dt between DATE '2013-06-01' and DATE '2013-09-01' + group by test_cal_dt.week_beg_dt \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query92.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query92.sql b/kylin-it/src/test/resources/query/sql/query92.sql index e551a45..ebc07fe 100644 --- a/kylin-it/src/test/resources/query/sql/query92.sql +++ b/kylin-it/src/test/resources/query/sql/query92.sql @@ -19,11 +19,11 @@ select meta_categ_name, count(1) as cnt, sum(price) as GMV from test_kylin_fact - left JOIN edw.test_cal_dt as test_cal_dt + inner JOIN edw.test_cal_dt as test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt - left JOIN test_category_groupings + inner JOIN test_category_groupings ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id - left JOIN edw.test_sites as test_sites + inner JOIN edw.test_sites as test_sites ON test_kylin_fact.lstg_site_id = test_sites.site_id where meta_categ_name not in ('', 'a') http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query93.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query93.sql b/kylin-it/src/test/resources/query/sql/query93.sql index cc6dca5..085e0e6 100644 --- a/kylin-it/src/test/resources/query/sql/query93.sql +++ b/kylin-it/src/test/resources/query/sql/query93.sql @@ -19,11 +19,11 @@ select meta_categ_name, count(1) as cnt, sum(price) as GMV from test_kylin_fact - left JOIN edw.test_cal_dt as test_cal_dt + inner JOIN edw.test_cal_dt as test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt - left JOIN test_category_groupings + inner JOIN test_category_groupings ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id - left JOIN edw.test_sites as test_sites + inner JOIN edw.test_sites as test_sites ON test_kylin_fact.lstg_site_id = test_sites.site_id where meta_categ_name is not null http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query94.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query94.sql b/kylin-it/src/test/resources/query/sql/query94.sql index c7899fd..b39d163 100644 --- a/kylin-it/src/test/resources/query/sql/query94.sql +++ b/kylin-it/src/test/resources/query/sql/query94.sql @@ -19,11 +19,11 @@ select meta_categ_name, count(1) as cnt, sum(price) as GMV from test_kylin_fact - left JOIN edw.test_cal_dt as test_cal_dt + inner JOIN edw.test_cal_dt as test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt - left JOIN test_category_groupings + inner JOIN test_category_groupings ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id - left JOIN edw.test_sites as test_sites + inner JOIN edw.test_sites as test_sites ON test_kylin_fact.lstg_site_id = test_sites.site_id where meta_categ_name not in ('Unknown') http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql/query95.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query95.sql b/kylin-it/src/test/resources/query/sql/query95.sql index 578b93f..fd94dcc 100644 --- a/kylin-it/src/test/resources/query/sql/query95.sql +++ b/kylin-it/src/test/resources/query/sql/query95.sql @@ -19,11 +19,11 @@ select meta_categ_name, count(1) as cnt, sum(price) as GMV from test_kylin_fact - left JOIN edw.test_cal_dt as test_cal_dt + inner JOIN edw.test_cal_dt as test_cal_dt ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt - left JOIN test_category_groupings + inner JOIN test_category_groupings ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id - left JOIN edw.test_sites as test_sites + inner JOIN edw.test_sites as test_sites ON test_kylin_fact.lstg_site_id = test_sites.site_id where meta_categ_name not in ('Unknown', 'ToyHobbies', '', 'a', 'BookMagazines') http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_like/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_like/query04.sql b/kylin-it/src/test/resources/query/sql_like/query04.sql new file mode 100644 index 0000000..faf5ca3 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_like/query04.sql @@ -0,0 +1,22 @@ +-- +-- 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 upper(lstg_format_name) as lstg_format_name, count(*) as cnt from test_kylin_fact +where lower(lstg_format_name)='auction' and substring(lstg_format_name,1,3) in ('Auc') and upper(lstg_format_name) > 'AAAA' and +upper(lstg_format_name) like '%UC%' and char_length(lstg_format_name) < 10 and char_length(lstg_format_name) > 3 and lstg_format_name||'a'='Auctiona' +group by lstg_format_name \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_like/query10.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_like/query10.sql b/kylin-it/src/test/resources/query/sql_like/query10.sql new file mode 100644 index 0000000..21632f4 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_like/query10.sql @@ -0,0 +1,13 @@ + +select USER_DEFINED_FIELD3 as abc + + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + +where USER_DEFINED_FIELD3 like '%Video Game%' http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_like/query15.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_like/query15.sql b/kylin-it/src/test/resources/query/sql_like/query15.sql new file mode 100644 index 0000000..85418c7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_like/query15.sql @@ -0,0 +1,13 @@ + +select USER_DEFINED_FIELD3 as abc + + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + +where lower(USER_DEFINED_FIELD3) like '%Video Game%' http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_like/query16.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_like/query16.sql b/kylin-it/src/test/resources/query/sql_like/query16.sql new file mode 100644 index 0000000..2a484ef --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_like/query16.sql @@ -0,0 +1,13 @@ + +select USER_DEFINED_FIELD3 as abc + + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + +where lower(USER_DEFINED_FIELD3) like '%video game%' http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_like/query17.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_like/query17.sql b/kylin-it/src/test/resources/query/sql_like/query17.sql new file mode 100644 index 0000000..c6dd1ea --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_like/query17.sql @@ -0,0 +1,13 @@ + +select USER_DEFINED_FIELD3 as abc + + from test_kylin_fact +inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id + + +where upper(USER_DEFINED_FIELD3) like '%VIDEO GAME%' http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_lookup/query45.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_lookup/query45.sql b/kylin-it/src/test/resources/query/sql_lookup/query45.sql new file mode 100644 index 0000000..ea964ae --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_lookup/query45.sql @@ -0,0 +1,24 @@ +-- +-- 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 count(*) as CNT from edw.test_cal_dt + + + + + http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_lookup/query46.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_lookup/query46.sql b/kylin-it/src/test/resources/query/sql_lookup/query46.sql new file mode 100644 index 0000000..3bfe9d9 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_lookup/query46.sql @@ -0,0 +1,19 @@ +-- +-- 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 count(*) as CNT from test_category_groupings http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_lookup/query47.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_lookup/query47.sql b/kylin-it/src/test/resources/query/sql_lookup/query47.sql new file mode 100644 index 0000000..cbd2c6d --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_lookup/query47.sql @@ -0,0 +1,19 @@ +-- +-- 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 count(*) as CNT from edw.test_seller_type_dim http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_lookup/query48.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_lookup/query48.sql b/kylin-it/src/test/resources/query/sql_lookup/query48.sql new file mode 100644 index 0000000..54ddb31 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_lookup/query48.sql @@ -0,0 +1,19 @@ +-- +-- 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 count(*) as CNT from edw.test_sites http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_lookup/query55.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_lookup/query55.sql b/kylin-it/src/test/resources/query/sql_lookup/query55.sql new file mode 100644 index 0000000..346a7d7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_lookup/query55.sql @@ -0,0 +1,19 @@ +-- +-- 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 count(*) as c from edw.test_cal_dt as test_cal_dt where extract(YEAR from test_cal_dt.cal_dt) = 2012 http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_raw/query21.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_raw/query21.sql b/kylin-it/src/test/resources/query/sql_raw/query21.sql new file mode 100644 index 0000000..4905e0f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_raw/query21.sql @@ -0,0 +1,24 @@ +-- +-- 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 PRICE from test_kylin_fact inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_raw/query22.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_raw/query22.sql b/kylin-it/src/test/resources/query/sql_raw/query22.sql new file mode 100644 index 0000000..d603a25 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_raw/query22.sql @@ -0,0 +1,24 @@ +-- +-- 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 PRICE from test_kylin_fact inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id where LSTG_FORMAT_NAME = 'ABIN' http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_raw/query23.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_raw/query23.sql b/kylin-it/src/test/resources/query/sql_raw/query23.sql new file mode 100644 index 0000000..89d61db --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_raw/query23.sql @@ -0,0 +1,24 @@ +-- +-- 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 test_kylin_fact.CAL_DT,LSTG_FORMAT_NAME,PRICE from test_kylin_fact inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id where LSTG_FORMAT_NAME = 'ABIN' http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_raw/query24.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_raw/query24.sql b/kylin-it/src/test/resources/query/sql_raw/query24.sql new file mode 100644 index 0000000..46c7329 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_raw/query24.sql @@ -0,0 +1,24 @@ +-- +-- 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 test_kylin_fact.CAL_DT,LSTG_FORMAT_NAME from test_kylin_fact inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id where LSTG_FORMAT_NAME = 'ABIN' http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_raw/query25.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_raw/query25.sql b/kylin-it/src/test/resources/query/sql_raw/query25.sql new file mode 100644 index 0000000..175124f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_raw/query25.sql @@ -0,0 +1,24 @@ +-- +-- 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 test_kylin_fact.CAL_DT from test_kylin_fact inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/kylin-it/src/test/resources/query/sql_raw/query26.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_raw/query26.sql.disabled b/kylin-it/src/test/resources/query/sql_raw/query26.sql.disabled new file mode 100644 index 0000000..cab92d0 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_raw/query26.sql.disabled @@ -0,0 +1,24 @@ +-- +-- 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 LSTG_FORMAT_NAME,LSTG_SITE_ID,SLR_SEGMENT_CD,test_kylin_fact.CAL_DT,test_category_groupings.LEAF_CATEG_ID,PRICE from test_kylin_fact inner JOIN edw.test_cal_dt as test_cal_dt + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + inner JOIN test_category_groupings + ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id + inner JOIN edw.test_sites as test_sites + ON test_kylin_fact.lstg_site_id = test_sites.site_id \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/query/src/main/java/org/apache/kylin/query/relnode/OLAPAggregateRel.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/relnode/OLAPAggregateRel.java b/query/src/main/java/org/apache/kylin/query/relnode/OLAPAggregateRel.java index ba74c74..f55c86f 100644 --- a/query/src/main/java/org/apache/kylin/query/relnode/OLAPAggregateRel.java +++ b/query/src/main/java/org/apache/kylin/query/relnode/OLAPAggregateRel.java @@ -25,7 +25,6 @@ import java.util.List; import java.util.Map; import java.util.Set; -import com.google.common.collect.Sets; import org.apache.calcite.adapter.enumerable.EnumerableAggregate; import org.apache.calcite.adapter.enumerable.EnumerableConvention; import org.apache.calcite.adapter.enumerable.EnumerableRel; @@ -67,6 +66,7 @@ import org.apache.kylin.query.schema.OLAPTable; import com.google.common.base.Preconditions; import com.google.common.collect.Lists; +import com.google.common.collect.Sets; /** */ @@ -133,8 +133,7 @@ public class OLAPAggregateRel extends Aggregate implements OLAPRel { if (getGroupType() == Group.SIMPLE) { cost = super.computeSelfCost(planner, mq).multiplyBy(.05); } else { - cost = super.computeSelfCost(planner, mq).multiplyBy(.05).plus(planner.getCost(getInput(), mq)) - .multiplyBy(groupSets.size() * 1.5); + cost = super.computeSelfCost(planner, mq).multiplyBy(.05).plus(planner.getCost(getInput(), mq)).multiplyBy(groupSets.size() * 1.5); } return cost; } @@ -174,7 +173,7 @@ public class OLAPAggregateRel extends Aggregate implements OLAPRel { // Add group column indicators if (indicator) { final Set<String> containedNames = Sets.newHashSet(); - for (TblColRef groupCol: groups) { + for (TblColRef groupCol : groups) { String base = "i$" + groupCol.getName(); String name = base; int i = 0; @@ -356,6 +355,14 @@ public class OLAPAggregateRel extends Aggregate implements OLAPRel { } private AggregateCall rewriteAggregateCall(AggregateCall aggCall, FunctionDesc func) { + + //if it's not a cube, then the "needRewriteField func" should not resort to any rewrite fields, + // which do not exist at all + if (!this.context.hasPrecalculatedFields() && func.needRewriteField()) { + logger.info(func + "skip rewriteAggregateCall because no pre-aggregated field available"); + return aggCall; + } + // rebuild parameters List<Integer> newArgList = Lists.newArrayList(aggCall.getArgList()); if (func.needRewriteField()) { http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/query/src/main/java/org/apache/kylin/query/relnode/OLAPContext.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/relnode/OLAPContext.java b/query/src/main/java/org/apache/kylin/query/relnode/OLAPContext.java index c2e1b88..41a3b4d 100644 --- a/query/src/main/java/org/apache/kylin/query/relnode/OLAPContext.java +++ b/query/src/main/java/org/apache/kylin/query/relnode/OLAPContext.java @@ -28,6 +28,7 @@ import java.util.Map; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeField; +import org.apache.kylin.cube.CubeInstance; import org.apache.kylin.metadata.filter.TupleFilter; import org.apache.kylin.metadata.model.FunctionDesc; import org.apache.kylin.metadata.model.JoinDesc; @@ -38,6 +39,7 @@ import org.apache.kylin.metadata.realization.SQLDigest; import org.apache.kylin.metadata.tuple.TupleInfo; import org.apache.kylin.query.schema.OLAPSchema; import org.apache.kylin.storage.StorageContext; +import org.apache.kylin.storage.hybrid.HybridInstance; import com.google.common.collect.Lists; @@ -146,6 +148,10 @@ public class OLAPContext { return sqlDigest; } + public boolean hasPrecalculatedFields() { + return realization instanceof CubeInstance || realization instanceof HybridInstance; + } + public void resetSQLDigest() { this.sqlDigest = null; } @@ -167,6 +173,7 @@ public class OLAPContext { sortOrders.add(order); } } + public interface IAccessController { /* * @return {TupleFilter} if the filter condition exists http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java b/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java index 6dbb81a..2a143fb 100644 --- a/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java +++ b/query/src/main/java/org/apache/kylin/query/relnode/OLAPJoinRel.java @@ -48,9 +48,9 @@ import org.apache.calcite.rel.core.JoinInfo; import org.apache.calcite.rel.core.JoinRelType; import org.apache.calcite.rel.metadata.RelMetadataQuery; import org.apache.calcite.rel.type.RelDataType; -import org.apache.calcite.rel.type.RelDataTypeFactory.FieldInfoBuilder; import org.apache.calcite.rel.type.RelDataTypeField; import org.apache.calcite.rel.type.RelDataTypeFieldImpl; +import org.apache.calcite.rel.type.RelDataTypeFactory.FieldInfoBuilder; import org.apache.calcite.rex.RexCall; import org.apache.calcite.rex.RexInputRef; import org.apache.calcite.rex.RexNode; @@ -166,6 +166,7 @@ public class OLAPJoinRel extends EnumerableJoin implements OLAPRel { for (Map.Entry<TblColRef, TblColRef> columnPair : joinCol.entrySet()) { TblColRef fromCol = (rightHasSubquery ? columnPair.getKey() : columnPair.getValue()); this.context.groupByColumns.add(fromCol); + this.context.allColumns.add(fromCol); } joinCol.clear(); } @@ -295,26 +296,29 @@ public class OLAPJoinRel extends EnumerableJoin implements OLAPRel { this.rowType = this.deriveRowType(); if (this.isTopJoin && RewriteImplementor.needRewrite(this.context)) { - // find missed rewrite fields - int paramIndex = this.rowType.getFieldList().size(); - List<RelDataTypeField> newFieldList = new LinkedList<RelDataTypeField>(); - for (Map.Entry<String, RelDataType> rewriteField : this.context.rewriteFields.entrySet()) { - String fieldName = rewriteField.getKey(); - if (this.rowType.getField(fieldName, true, false) == null) { - RelDataType fieldType = rewriteField.getValue(); - RelDataTypeField newField = new RelDataTypeFieldImpl(fieldName, paramIndex++, fieldType); - newFieldList.add(newField); + if (this.context.hasPrecalculatedFields()) { + + // find missed rewrite fields + int paramIndex = this.rowType.getFieldList().size(); + List<RelDataTypeField> newFieldList = new LinkedList<RelDataTypeField>(); + for (Map.Entry<String, RelDataType> rewriteField : this.context.rewriteFields.entrySet()) { + String fieldName = rewriteField.getKey(); + if (this.rowType.getField(fieldName, true, false) == null) { + RelDataType fieldType = rewriteField.getValue(); + RelDataTypeField newField = new RelDataTypeFieldImpl(fieldName, paramIndex++, fieldType); + newFieldList.add(newField); + } } - } - // rebuild row type - FieldInfoBuilder fieldInfo = getCluster().getTypeFactory().builder(); - fieldInfo.addAll(this.rowType.getFieldList()); - fieldInfo.addAll(newFieldList); - this.rowType = getCluster().getTypeFactory().createStructType(fieldInfo); + // rebuild row type + FieldInfoBuilder fieldInfo = getCluster().getTypeFactory().builder(); + fieldInfo.addAll(this.rowType.getFieldList()); + fieldInfo.addAll(newFieldList); + this.rowType = getCluster().getTypeFactory().createStructType(fieldInfo); - // rebuild columns - this.columnRowType = this.buildColumnRowType(); + // rebuild columns + this.columnRowType = this.buildColumnRowType(); + } } } http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java b/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java index 0a8a15f..db6ec2d 100644 --- a/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java +++ b/query/src/main/java/org/apache/kylin/query/relnode/OLAPProjectRel.java @@ -37,9 +37,9 @@ import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.Project; import org.apache.calcite.rel.metadata.RelMetadataQuery; import org.apache.calcite.rel.type.RelDataType; -import org.apache.calcite.rel.type.RelDataTypeFactory.FieldInfoBuilder; import org.apache.calcite.rel.type.RelDataTypeField; import org.apache.calcite.rel.type.RelDataTypeFieldImpl; +import org.apache.calcite.rel.type.RelDataTypeFactory.FieldInfoBuilder; import org.apache.calcite.rex.RexCall; import org.apache.calcite.rex.RexInputRef; import org.apache.calcite.rex.RexLiteral; @@ -97,7 +97,7 @@ public class OLAPProjectRel extends Project implements OLAPRel { @Override public RelOptCost computeSelfCost(RelOptPlanner planner, RelMetadataQuery mq) { boolean hasRexOver = RexOver.containsOver(getProjects(), null); - return super.computeSelfCost(planner, mq).multiplyBy(.05).multiplyBy(getProjects().size() * (hasRexOver ? 50 : 1)); + return super.computeSelfCost(planner, mq).multiplyBy(.05).multiplyBy(getProjects().size() * (hasRexOver ? 50 : 1)); } @Override @@ -244,7 +244,7 @@ public class OLAPProjectRel extends Project implements OLAPRel { this.rewriting = true; // project before join or is just after OLAPToEnumerableConverter - if (!RewriteImplementor.needRewrite(this.context) || (this.hasJoin && !this.afterJoin) || this.afterAggregate) { + if (!RewriteImplementor.needRewrite(this.context) || (this.hasJoin && !this.afterJoin) || this.afterAggregate || !(this.context.hasPrecalculatedFields())) { this.columnRowType = this.buildColumnRowType(); return; } http://git-wip-us.apache.org/repos/asf/kylin/blob/ddeb7452/query/src/main/java/org/apache/kylin/query/routing/Candidate.java ---------------------------------------------------------------------- diff --git a/query/src/main/java/org/apache/kylin/query/routing/Candidate.java b/query/src/main/java/org/apache/kylin/query/routing/Candidate.java index 9ea8961..bc17721 100644 --- a/query/src/main/java/org/apache/kylin/query/routing/Candidate.java +++ b/query/src/main/java/org/apache/kylin/query/routing/Candidate.java @@ -18,6 +18,7 @@ package org.apache.kylin.query.routing; +import java.util.Collections; import java.util.Map; import org.apache.kylin.metadata.realization.CapabilityResult; @@ -40,12 +41,12 @@ public class Candidate implements Comparable<Candidate> { /** for test only */ public static void setPriorities(Map<RealizationType, Integer> priorities) { - PRIORITIES = priorities; + PRIORITIES = Collections.unmodifiableMap(priorities); } /** for test only */ public static void restorePriorities() { - PRIORITIES = DEFAULT_PRIORITIES; + PRIORITIES = Collections.unmodifiableMap(DEFAULT_PRIORITIES); } // ============================================================================
