kylin-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kangkai...@apache.org
Subject [28/50] [abbrv] kylin git commit: KYLIN-2823 Trim TupleFilter after dictionary-based filter optimization
Date Mon, 04 Sep 2017 13:44:39 GMT
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/2622-2764
Commit: 47822891332aef146ae3c2b5e3adc98c23024770
Parents: 1eb3617
Author: Hongbin Ma <mahongbin@apache.org>
Authored: Wed Aug 30 16:06:18 2017 +0800
Committer: 成 <cheng.wang@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'
+                               )) 
+                       
+                       
+                       
+                       )


Mime
View raw message