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/47822891 Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/47822891 Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/47822891 Branch: refs/heads/master Commit: 47822891332aef146ae3c2b5e3adc98c23024770 Parents: 1eb3617 Author: Hongbin Ma <mahong...@apache.org> Authored: Wed Aug 30 16:06:18 2017 +0800 Committer: æ <cheng.w...@kyligence.io> Committed: Wed Aug 30 17:40:03 2017 +0800 ---------------------------------------------------------------------- .../java/org/apache/kylin/gridtable/GTUtil.java | 24 +++- .../metadata/filter/ConstantTupleFilter.java | 14 +++ .../filter/FilterOptimizeTransformer.java | 35 +++++- .../filter/FilterOptimizeTransformerTest.java | 121 +++++++++++++++++++ .../storage/gtrecord/DictGridTableTest.java | 108 +++++++++++------ .../src/test/resources/query/sql/query106.sql | 101 ++++++++++++++++ .../src/test/resources/query/sql/query107.sql | 102 ++++++++++++++++ 7 files changed, 459 insertions(+), 46 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/kylin/blob/47822891/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..5c9dfe3 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,15 @@ 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 +90,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 +132,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 +154,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/47822891/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..e4f8b2e 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,18 @@ 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/47822891/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..96b377b 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,7 +28,12 @@ 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 */ @@ -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/47822891/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..f70d551 --- /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/47822891/core-storage/src/test/java/org/apache/kylin/storage/gtrecord/DictGridTableTest.java ---------------------------------------------------------------------- diff --git a/core-storage/src/test/java/org/apache/kylin/storage/gtrecord/DictGridTableTest.java b/core-storage/src/test/java/org/apache/kylin/storage/gtrecord/DictGridTableTest.java index 15d2a28..e4767e0 100644 --- a/core-storage/src/test/java/org/apache/kylin/storage/gtrecord/DictGridTableTest.java +++ b/core-storage/src/test/java/org/apache/kylin/storage/gtrecord/DictGridTableTest.java @@ -124,7 +124,8 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { { LogicalTupleFilter filter = and(timeComp0, ageComp1); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(1, r.size());//scan range are [close,close] assertEquals("[null, 10]-[1421193600000, 10]", r.get(0).toString()); @@ -133,33 +134,40 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { } { LogicalTupleFilter filter = and(timeComp2, ageComp1); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(0, r.size()); } { LogicalTupleFilter filter = and(timeComp4, ageComp1); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(0, r.size()); } { LogicalTupleFilter filter = and(timeComp5, ageComp1); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(0, r.size()); } { - LogicalTupleFilter filter = or(and(timeComp2, ageComp1), and(timeComp1, ageComp1), and(timeComp6, ageComp1)); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), info.colRef(0), filter); + LogicalTupleFilter filter = or(and(timeComp2, ageComp1), and(timeComp1, ageComp1), + and(timeComp6, ageComp1)); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(1, r.size()); assertEquals("[1421193600000, 10]-[null, 10]", r.get(0).toString()); - assertEquals("[[null, 10, null, null, null], [1421193600000, 10, null, null, null]]", r.get(0).fuzzyKeys.toString()); + assertEquals("[[null, 10, null, null, null], [1421193600000, 10, null, null, null]]", + r.get(0).fuzzyKeys.toString()); } { LogicalTupleFilter filter = or(timeComp2, timeComp1, timeComp6); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(1, r.size()); assertEquals("[1421193600000, null]-[null, null]", r.get(0).toString()); @@ -168,14 +176,16 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { { //skip FALSE filter LogicalTupleFilter filter = and(ageComp1, ConstantTupleFilter.FALSE); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(0, r.size()); } { //TRUE or FALSE filter LogicalTupleFilter filter = or(ConstantTupleFilter.TRUE, ConstantTupleFilter.FALSE); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(1, r.size()); assertEquals("[null, null]-[null, null]", r.get(0).toString()); @@ -183,7 +193,8 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { { //TRUE or other filter LogicalTupleFilter filter = or(ageComp1, ConstantTupleFilter.TRUE); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(segmentStart, segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(1, r.size()); assertEquals("[null, null]-[null, null]", r.get(0).toString()); @@ -196,7 +207,8 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { { LogicalTupleFilter filter = and(timeComp0, ageComp1); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(new ByteArray(), segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(new ByteArray(), segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(1, r.size());//scan range are [close,close] assertEquals("[null, 10]-[1421193600000, 10]", r.get(0).toString()); @@ -206,7 +218,8 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { { LogicalTupleFilter filter = and(timeComp5, ageComp1); - CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(new ByteArray(), segmentEnd), info.colRef(0), filter); + CubeScanRangePlanner planner = new CubeScanRangePlanner(info, Pair.newPair(new ByteArray(), segmentEnd), + info.colRef(0), filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(0, r.size());//scan range are [close,close] } @@ -251,7 +264,8 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { // merge too many ranges { - LogicalTupleFilter filter = or(and(timeComp4, ageComp1), and(timeComp4, ageComp2), and(timeComp4, ageComp3)); + LogicalTupleFilter filter = or(and(timeComp4, ageComp1), and(timeComp4, ageComp2), + and(timeComp4, ageComp3)); CubeScanRangePlanner planner = new CubeScanRangePlanner(info, null, null, filter); List<GTScanRange> r = planner.planScanRanges(); assertEquals(3, r.size()); @@ -266,7 +280,10 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { @Test public void verifyFirstRow() throws IOException { - doScanAndVerify(table, new GTScanRequestBuilder().setInfo(table.getInfo()).setRanges(null).setDimensions(null).setFilterPushDown(null).createGTScanRequest(), "[1421193600000, 30, Yang, 10, 10.5]", // + doScanAndVerify(table, + new GTScanRequestBuilder().setInfo(table.getInfo()).setRanges(null).setDimensions(null) + .setFilterPushDown(null).createGTScanRequest(), + "[1421193600000, 30, Yang, 10, 10.5]", // "[1421193600000, 30, Luke, 10, 10.5]", // "[1421280000000, 20, Dong, 10, 10.5]", // "[1421280000000, 20, Jason, 10, 10.5]", // @@ -299,12 +316,18 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { LogicalTupleFilter fNotPlusUnevaluatable = not(unevaluatable(info.colRef(1))); LogicalTupleFilter filter = and(fComp, fUnevaluatable, fNotPlusUnevaluatable); - GTScanRequest req = new GTScanRequestBuilder().setInfo(info).setRanges(null).setDimensions(null).setAggrGroupBy(setOf(0)).setAggrMetrics(setOf(3)).setAggrMetricsFuncs(new String[] { "sum" }).setFilterPushDown(filter).createGTScanRequest(); + GTScanRequest req = new GTScanRequestBuilder().setInfo(info).setRanges(null).setDimensions(null) + .setAggrGroupBy(setOf(0)).setAggrMetrics(setOf(3)).setAggrMetricsFuncs(new String[] { "sum" }) + .setFilterPushDown(filter).createGTScanRequest(); // note the unEvaluatable column 1 in filter is added to group by - assertEquals("GTScanRequest [range=[[null, null]-[null, null]], columns={0, 1, 3}, filterPushDown=AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], [null], [null]], aggrGroupBy={0, 1}, aggrMetrics={3}, aggrMetricsFuncs=[sum]]", req.toString()); + assertEquals( + "GTScanRequest [range=[[null, null]-[null, null]], columns={0, 1, 3}, filterPushDown=AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], [null], [null]], aggrGroupBy={0, 1}, aggrMetrics={3}, aggrMetricsFuncs=[sum]]", + req.toString()); - doScanAndVerify(table, useDeserializedGTScanRequest(req), "[1421280000000, 20, null, 20, null]", "[1421280000000, 30, null, 10, null]", "[1421366400000, 20, null, 20, null]", "[1421366400000, 30, null, 20, null]", "[1421452800000, 10, null, 10, null]"); + doScanAndVerify(table, useDeserializedGTScanRequest(req), "[1421280000000, 20, null, 20, null]", + "[1421280000000, 30, null, 10, null]", "[1421366400000, 20, null, 20, null]", + "[1421366400000, 30, null, 20, null]", "[1421452800000, 10, null, 10, null]"); } @Test @@ -315,26 +338,34 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { CompareTupleFilter fComp2 = compare(info.colRef(1), FilterOperatorEnum.GT, enc(info, 1, "10")); LogicalTupleFilter filter = and(fComp1, fComp2); - GTScanRequest req = new GTScanRequestBuilder().setInfo(info).setRanges(null).setDimensions(null).setAggrGroupBy(setOf(0)).setAggrMetrics(setOf(3)).setAggrMetricsFuncs(new String[] { "sum" }).setFilterPushDown(filter).createGTScanRequest(); + GTScanRequest req = new GTScanRequestBuilder().setInfo(info).setRanges(null).setDimensions(null) + .setAggrGroupBy(setOf(0)).setAggrMetrics(setOf(3)).setAggrMetricsFuncs(new String[] { "sum" }) + .setFilterPushDown(filter).createGTScanRequest(); // note the evaluatable column 1 in filter is added to returned columns but not in group by - assertEquals("GTScanRequest [range=[[null, null]-[null, null]], columns={0, 1, 3}, filterPushDown=AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.1 GT [\\x00]], aggrGroupBy={0}, aggrMetrics={3}, aggrMetricsFuncs=[sum]]", req.toString()); + assertEquals( + "GTScanRequest [range=[[null, null]-[null, null]], columns={0, 1, 3}, filterPushDown=AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.1 GT [\\x00]], aggrGroupBy={0}, aggrMetrics={3}, aggrMetricsFuncs=[sum]]", + req.toString()); - doScanAndVerify(table, useDeserializedGTScanRequest(req), "[1421280000000, 20, null, 30, null]", "[1421366400000, 20, null, 40, null]"); + doScanAndVerify(table, useDeserializedGTScanRequest(req), "[1421280000000, 20, null, 30, null]", + "[1421366400000, 20, null, 40, null]"); } @Test public void verifyAggregateAndHavingFilter() throws IOException { GTInfo info = table.getInfo(); - + TblColRef havingCol = TblColRef.newInnerColumn("SUM_OF_BIGDECIMAL", InnerDataTypeEnum.LITERAL); havingCol.getColumnDesc().setId("1"); // point to the first aggregated measure CompareTupleFilter havingFilter = compare(havingCol, FilterOperatorEnum.GT, "20"); - - GTScanRequest req = new GTScanRequestBuilder().setInfo(info).setRanges(null).setDimensions(null).setAggrGroupBy(setOf(1)).setAggrMetrics(setOf(4)).setAggrMetricsFuncs(new String[] { "sum" }).setHavingFilterPushDown(havingFilter).createGTScanRequest(); - - doScanAndVerify(table, useDeserializedGTScanRequest(req), "[null, 20, null, null, 42.0]", "[null, 30, null, null, 52.5]"); + + GTScanRequest req = new GTScanRequestBuilder().setInfo(info).setRanges(null).setDimensions(null) + .setAggrGroupBy(setOf(1)).setAggrMetrics(setOf(4)).setAggrMetricsFuncs(new String[] { "sum" }) + .setHavingFilterPushDown(havingFilter).createGTScanRequest(); + + doScanAndVerify(table, useDeserializedGTScanRequest(req), "[null, 20, null, null, 42.0]", + "[null, 30, null, null, 52.5]"); } - + @Test @Ignore public void testFilterScannerPerf() throws IOException { @@ -356,9 +387,11 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { } @SuppressWarnings("unused") - private void testFilterScannerPerfInner(GridTable table, GTInfo info, LogicalTupleFilter filter) throws IOException { + private void testFilterScannerPerfInner(GridTable table, GTInfo info, LogicalTupleFilter filter) + throws IOException { long start = System.currentTimeMillis(); - GTScanRequest req = new GTScanRequestBuilder().setInfo(info).setRanges(null).setDimensions(null).setFilterPushDown(filter).createGTScanRequest(); + GTScanRequest req = new GTScanRequestBuilder().setInfo(info).setRanges(null).setDimensions(null) + .setFilterPushDown(filter).createGTScanRequest(); IGTScanner scanner = table.scan(req); int i = 0; for (GTRecord r : scanner) { @@ -366,7 +399,8 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { } scanner.close(); long end = System.currentTimeMillis(); - System.out.println((end - start) + "ms with filter cache enabled=" + FilterResultCache.ENABLED + ", " + i + " rows"); + System.out.println( + (end - start) + "ms with filter cache enabled=" + FilterResultCache.ENABLED + ", " + i + " rows"); } @Test @@ -386,7 +420,9 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { colMapping.add(extColB); TupleFilter newFilter = GTUtil.convertFilterColumnsAndConstants(filter, info, colMapping, null); - assertEquals("AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.1 EQ [\\x00]]", newFilter.toString()); + assertEquals( + "AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.1 EQ [\\x00]]", + newFilter.toString()); } @Test @@ -407,7 +443,9 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { // $1<"9" round up to $1<"10" TupleFilter newFilter = GTUtil.convertFilterColumnsAndConstants(filter, info, colMapping, null); - assertEquals("AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.1 LT [\\x00]]", newFilter.toString()); + assertEquals( + "AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.1 LT [\\x00]]", + newFilter.toString()); } @Test @@ -428,7 +466,7 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { // $1<="9" round down to FALSE TupleFilter newFilter = GTUtil.convertFilterColumnsAndConstants(filter, info, colMapping, null); - assertEquals("AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], []]", newFilter.toString()); + assertEquals(ConstantTupleFilter.FALSE, newFilter); } @Test @@ -449,7 +487,9 @@ public class DictGridTableTest extends LocalFileMetadataTestCase { // $1 in ("9", "10", "15") has only "10" left TupleFilter newFilter = GTUtil.convertFilterColumnsAndConstants(filter, info, colMapping, null); - assertEquals("AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.1 IN [\\x00]]", newFilter.toString()); + assertEquals( + "AND [UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0 GT [\\x00\\x00\\x01J\\xE5\\xBD\\x5C\\x00], UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.1 IN [\\x00]]", + newFilter.toString()); } private void doScanAndVerify(GridTable table, GTScanRequest req, String... verifyRows) throws IOException { http://git-wip-us.apache.org/repos/asf/kylin/blob/47822891/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/47822891/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' + )) + + + + )