Repository: kylin Updated Branches: refs/heads/2.0.x 3fac37fa8 -> 3f1c498f3
KYLIN-2823 Trim TupleFilter after dictionary-based filter optimization Project: http://git-wip-us.apache.org/repos/asf/kylin/repo Commit: http://git-wip-us.apache.org/repos/asf/kylin/commit/3f1c498f Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/3f1c498f Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/3f1c498f Branch: refs/heads/2.0.x Commit: 3f1c498f3d5814f28cc9da5ac2d697d90153887a Parents: 3fac37f Author: Hongbin Ma <mahong...@apache.org> Authored: Wed Aug 30 16:44:27 2017 +0800 Committer: Hongbin Ma <mahong...@apache.org> Committed: Wed Aug 30 16:44:27 2017 +0800 ---------------------------------------------------------------------- .../java/org/apache/kylin/gridtable/GTUtil.java | 26 +++- .../metadata/filter/ConstantTupleFilter.java | 17 +++ .../filter/FilterOptimizeTransformer.java | 37 ++++-- .../filter/FilterOptimizeTransformerTest.java | 121 +++++++++++++++++++ .../src/test/resources/query/sql/query106.sql | 101 ++++++++++++++++ .../src/test/resources/query/sql/query107.sql | 102 ++++++++++++++++ 6 files changed, 391 insertions(+), 13 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/kylin/blob/3f1c498f/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 7a7e4e6..49c3824 100755 --- a/core-cube/src/main/java/org/apache/kylin/gridtable/GTUtil.java +++ b/core-cube/src/main/java/org/apache/kylin/gridtable/GTUtil.java @@ -30,6 +30,7 @@ import org.apache.kylin.common.util.BytesUtil; import org.apache.kylin.metadata.filter.ColumnTupleFilter; import org.apache.kylin.metadata.filter.CompareTupleFilter; import org.apache.kylin.metadata.filter.ConstantTupleFilter; +import org.apache.kylin.metadata.filter.FilterOptimizeTransformer; import org.apache.kylin.metadata.filter.IFilterCodeSystem; import org.apache.kylin.metadata.filter.TupleFilter; import org.apache.kylin.metadata.filter.TupleFilterSerializer; @@ -64,7 +65,17 @@ public class GTUtil { public static TupleFilter convertFilterColumnsAndConstants(TupleFilter rootFilter, GTInfo info, // List<TblColRef> colMapping, Set<TblColRef> unevaluatableColumnCollector) { Map<TblColRef, Integer> map = colListToMap(colMapping); - return convertFilter(rootFilter, info, map, true, unevaluatableColumnCollector); + + TupleFilter filter = convertFilter(rootFilter, info, map, true, unevaluatableColumnCollector); + + // optimize the filter: after translating with dictionary, some filters become determined + // e.g. + // ( a = 'value_in_dict' OR a = 'value_not_in_dict') will become (a = 'value_in_dict' OR ConstantTupleFilter.FALSE) + // use the following to further trim the filter to (a = 'value_in_dict') + // The goal is to avoid too many children after flatten filter step + filter = new FilterOptimizeTransformer().transform(filter); + return filter; + } protected static Map<TblColRef, Integer> colListToMap(List<TblColRef> colMapping) { @@ -81,8 +92,9 @@ public class GTUtil { final Set<TblColRef> unevaluatableColumnCollector) { IFilterCodeSystem<ByteArray> filterCodeSystem = wrap(info.codeSystem.getComparator()); - - GTConvertDecorator decorator = new GTConvertDecorator(unevaluatableColumnCollector, colMapping, info, encodeConstants); + + GTConvertDecorator decorator = new GTConvertDecorator(unevaluatableColumnCollector, colMapping, info, + encodeConstants); byte[] bytes = TupleFilterSerializer.serialize(rootFilter, decorator, filterCodeSystem); return TupleFilterSerializer.deserialize(bytes, filterCodeSystem); @@ -122,14 +134,15 @@ public class GTUtil { protected final GTInfo info; protected final boolean encodeConstants; - public GTConvertDecorator(Set<TblColRef> unevaluatableColumnCollector, Map<TblColRef, Integer> colMapping, GTInfo info, boolean encodeConstants) { + public GTConvertDecorator(Set<TblColRef> unevaluatableColumnCollector, Map<TblColRef, Integer> colMapping, + GTInfo info, boolean encodeConstants) { this.unevaluatableColumnCollector = unevaluatableColumnCollector; this.colMapping = colMapping; this.info = info; this.encodeConstants = encodeConstants; buf = ByteBuffer.allocate(info.getMaxColumnLength()); } - + protected int mapCol(TblColRef col) { Integer i = colMapping.get(col); return i == null ? -1 : i; @@ -143,7 +156,8 @@ public class GTUtil { // In case of NOT(unEvaluatableFilter), we should immediately replace it as TRUE, // Otherwise, unEvaluatableFilter will later be replace with TRUE and NOT(unEvaluatableFilter) // will always return FALSE. - if (filter.getOperator() == TupleFilter.FilterOperatorEnum.NOT && !TupleFilter.isEvaluableRecursively(filter)) { + if (filter.getOperator() == TupleFilter.FilterOperatorEnum.NOT + && !TupleFilter.isEvaluableRecursively(filter)) { TupleFilter.collectColumns(filter, unevaluatableColumnCollector); return ConstantTupleFilter.TRUE; } http://git-wip-us.apache.org/repos/asf/kylin/blob/3f1c498f/core-metadata/src/main/java/org/apache/kylin/metadata/filter/ConstantTupleFilter.java ---------------------------------------------------------------------- diff --git a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/ConstantTupleFilter.java b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/ConstantTupleFilter.java index 61d87e8..070bcfc 100644 --- a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/ConstantTupleFilter.java +++ b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/ConstantTupleFilter.java @@ -108,4 +108,21 @@ public class ConstantTupleFilter extends TupleFilter { } } + @Override + public boolean equals(Object o) { + if (this == o) + return true; + if (o == null || getClass() != o.getClass()) + return false; + + ConstantTupleFilter that = (ConstantTupleFilter) o; + + return constantValues.equals(that.constantValues); + } + + @Override + public int hashCode() { + return constantValues.hashCode(); + } + } http://git-wip-us.apache.org/repos/asf/kylin/blob/3f1c498f/core-metadata/src/main/java/org/apache/kylin/metadata/filter/FilterOptimizeTransformer.java ---------------------------------------------------------------------- diff --git a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/FilterOptimizeTransformer.java b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/FilterOptimizeTransformer.java index 9ef9c2c..bf85708 100755 --- a/core-metadata/src/main/java/org/apache/kylin/metadata/filter/FilterOptimizeTransformer.java +++ b/core-metadata/src/main/java/org/apache/kylin/metadata/filter/FilterOptimizeTransformer.java @@ -28,8 +28,13 @@ import com.google.common.collect.Lists; /** * optimize the filter if possible, not limited to: - * 1. prune filters like (a = ? OR 1 = 1) - * + * + * 1. replace filters like (a = 10 OR 1 = 1) to ConstantTupleFilter.TRUE + * 2. replace filters like (a = 10 AND 1 = 2) to ConstantTupleFilter.FALSE + * + * 3. replace filter like (a = 10 AND ConstantTupleFilter.TRUE) to (a = 10) + * 4. replace filter like (a = 10 OR ConstantTupleFilter.FALSE) to (a = 10) + * * is a first type transformer defined in ITupleFilterTransformer */ public class FilterOptimizeTransformer implements ITupleFilterTransformer { @@ -66,23 +71,41 @@ public class FilterOptimizeTransformer implements ITupleFilterTransformer { if (logicalTupleFilter.getOperator() == TupleFilter.FilterOperatorEnum.OR) { @SuppressWarnings("unchecked") - ListIterator<TupleFilter> childIterator = (ListIterator<TupleFilter>) logicalTupleFilter.getChildren().listIterator(); + ListIterator<TupleFilter> childIterator = (ListIterator<TupleFilter>) logicalTupleFilter.getChildren() + .listIterator(); while (childIterator.hasNext()) { TupleFilter next = childIterator.next(); - if (ConstantTupleFilter.TRUE == next) { + if (ConstantTupleFilter.TRUE.equals(next)) { logger.debug("Optimized {{}} to ConstantTupleFilter.TRUE", logicalTupleFilter); return ConstantTupleFilter.TRUE; } + + if (ConstantTupleFilter.FALSE.equals(next)) { + childIterator.remove(); + } + } + + if (logicalTupleFilter.getChildren().size() == 0) { + return ConstantTupleFilter.FALSE; } } else if (logicalTupleFilter.getOperator() == TupleFilter.FilterOperatorEnum.AND) { @SuppressWarnings("unchecked") - ListIterator<TupleFilter> childIterator = (ListIterator<TupleFilter>) logicalTupleFilter.getChildren().listIterator(); + ListIterator<TupleFilter> childIterator = (ListIterator<TupleFilter>) logicalTupleFilter.getChildren() + .listIterator(); while (childIterator.hasNext()) { TupleFilter next = childIterator.next(); - if (ConstantTupleFilter.FALSE == next) { + if (ConstantTupleFilter.FALSE.equals(next)) { logger.debug("Optimized {{}} to ConstantTupleFilter.FALSE", logicalTupleFilter); return ConstantTupleFilter.FALSE; } + + if (ConstantTupleFilter.TRUE.equals(next)) { + childIterator.remove(); + } + } + + if (logicalTupleFilter.getChildren().size() == 0) { + return ConstantTupleFilter.TRUE; } } @@ -116,7 +139,7 @@ public class FilterOptimizeTransformer implements ITupleFilterTransformer { if (newFilters.size() == 1) { return newFilters.get(0); } - + CaseTupleFilter newCaseTupleFilter = new CaseTupleFilter(); newCaseTupleFilter.addChildren(newFilters); return newCaseTupleFilter; http://git-wip-us.apache.org/repos/asf/kylin/blob/3f1c498f/core-metadata/src/test/java/org/apache/kylin/metadata/filter/FilterOptimizeTransformerTest.java ---------------------------------------------------------------------- diff --git a/core-metadata/src/test/java/org/apache/kylin/metadata/filter/FilterOptimizeTransformerTest.java b/core-metadata/src/test/java/org/apache/kylin/metadata/filter/FilterOptimizeTransformerTest.java new file mode 100644 index 0000000..832feb0 --- /dev/null +++ b/core-metadata/src/test/java/org/apache/kylin/metadata/filter/FilterOptimizeTransformerTest.java @@ -0,0 +1,121 @@ +/* + * 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 org.junit.Assert; +import org.junit.Test; + +public class FilterOptimizeTransformerTest { + @Test + public void transformTest0() throws Exception { + TupleFilter or = new LogicalTupleFilter(TupleFilter.FilterOperatorEnum.AND); + TupleFilter a = new CompareTupleFilter(TupleFilter.FilterOperatorEnum.EQ); + TupleFilter b = ConstantTupleFilter.TRUE; + + or.addChild(a); + or.addChild(b); + or = new FilterOptimizeTransformer().transform(or); + Assert.assertEquals(1, or.children.size()); + } + + + + @Test + public void transformTest1() throws Exception { + TupleFilter or = new LogicalTupleFilter(TupleFilter.FilterOperatorEnum.OR); + TupleFilter a = new CompareTupleFilter(TupleFilter.FilterOperatorEnum.EQ); + TupleFilter b = ConstantTupleFilter.FALSE; + + or.addChild(a); + or.addChild(b); + or = new FilterOptimizeTransformer().transform(or); + Assert.assertEquals(1, or.children.size()); + } + + @Test + public void transformTest2() throws Exception { + TupleFilter or = new LogicalTupleFilter(TupleFilter.FilterOperatorEnum.OR); + TupleFilter a = ConstantTupleFilter.FALSE; + TupleFilter b = ConstantTupleFilter.FALSE; + + or.addChild(a); + or.addChild(b); + or = new FilterOptimizeTransformer().transform(or); + Assert.assertEquals(ConstantTupleFilter.FALSE, or); + } + + @Test + public void transformTest3() throws Exception { + TupleFilter or = new LogicalTupleFilter(TupleFilter.FilterOperatorEnum.OR); + TupleFilter a = ConstantTupleFilter.TRUE; + TupleFilter b = ConstantTupleFilter.TRUE; + + or.addChild(a); + or.addChild(b); + or = new FilterOptimizeTransformer().transform(or); + Assert.assertEquals(ConstantTupleFilter.TRUE, or); + } + + @Test + public void transformTest4() throws Exception { + TupleFilter or = new LogicalTupleFilter(TupleFilter.FilterOperatorEnum.AND); + TupleFilter a = ConstantTupleFilter.FALSE; + TupleFilter b = ConstantTupleFilter.FALSE; + + or.addChild(a); + or.addChild(b); + or = new FilterOptimizeTransformer().transform(or); + Assert.assertEquals(ConstantTupleFilter.FALSE, or); + } + + @Test + public void transformTest5() throws Exception { + TupleFilter or = new LogicalTupleFilter(TupleFilter.FilterOperatorEnum.AND); + TupleFilter a = ConstantTupleFilter.TRUE; + TupleFilter b = ConstantTupleFilter.TRUE; + + or.addChild(a); + or.addChild(b); + or = new FilterOptimizeTransformer().transform(or); + Assert.assertEquals(ConstantTupleFilter.TRUE, or); + } + + @Test + public void transformTest6() throws Exception { + TupleFilter or = new LogicalTupleFilter(TupleFilter.FilterOperatorEnum.AND); + TupleFilter a = ConstantTupleFilter.FALSE; + TupleFilter b = ConstantTupleFilter.TRUE; + + or.addChild(a); + or.addChild(b); + or = new FilterOptimizeTransformer().transform(or); + Assert.assertEquals(ConstantTupleFilter.FALSE, or); + } + + @Test + public void transformTest7() throws Exception { + TupleFilter or = new LogicalTupleFilter(TupleFilter.FilterOperatorEnum.OR); + TupleFilter a = ConstantTupleFilter.FALSE; + TupleFilter b = ConstantTupleFilter.TRUE; + + or.addChild(a); + or.addChild(b); + or = new FilterOptimizeTransformer().transform(or); + Assert.assertEquals(ConstantTupleFilter.TRUE, or); + } +} http://git-wip-us.apache.org/repos/asf/kylin/blob/3f1c498f/kylin-it/src/test/resources/query/sql/query106.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query106.sql b/kylin-it/src/test/resources/query/sql/query106.sql new file mode 100644 index 0000000..d8faaaf --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query106.sql @@ -0,0 +1,101 @@ +-- +-- 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 x + +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 ( META_CATEG_NAME IN ('jenny','esrzongguan','Baby') + + AND ( META_CATEG_NAME IN + ('non_existing_dict_value1', 'Baby', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + 'administrators' IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1' + )) + + and + + ( META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'Baby', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + 'administrators' IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1' + )) + + + + ) http://git-wip-us.apache.org/repos/asf/kylin/blob/3f1c498f/kylin-it/src/test/resources/query/sql/query107.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql/query107.sql b/kylin-it/src/test/resources/query/sql/query107.sql new file mode 100644 index 0000000..4836f4c --- /dev/null +++ b/kylin-it/src/test/resources/query/sql/query107.sql @@ -0,0 +1,102 @@ +-- +-- 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 x + +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 ( META_CATEG_NAME IN ('jenny','esrzongguan','Baby') + + AND ( META_CATEG_NAME IN + ('non_existing_dict_value1', 'Baby', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + 'administrators' IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1' + )) + + and + + ( META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + + META_CATEG_NAME IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', 'non_existing_dict_value2', + 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3', 'non_existing_dict_value3' + ) OR + 'administrators' IN + ('non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', + 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1', 'non_existing_dict_value1' + )) + + + + )
