diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/AddEquivalenceClassForRecordConstructorRule.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/AddEquivalenceClassForRecordConstructorRule.java
index 218146f..b2271a1 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/AddEquivalenceClassForRecordConstructorRule.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/AddEquivalenceClassForRecordConstructorRule.java
@@ -37,6 +37,7 @@
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalExpressionTag;
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ConstantExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ScalarFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
@@ -85,7 +86,7 @@
         for (int exprIndex = 0; exprIndex < exprRefs.size(); ++exprIndex) {
             ILogicalExpression expr = exprRefs.get(exprIndex).getValue();
             if (expr.getExpressionTag() == LogicalExpressionTag.FUNCTION_CALL) {
-                ScalarFunctionCallExpression funcExpr = (ScalarFunctionCallExpression) expr;
+                AbstractFunctionCallExpression funcExpr = (AbstractFunctionCallExpression) expr;
                 FunctionIdentifier fid = funcExpr.getFunctionIdentifier();
                 if (fid == AsterixBuiltinFunctions.CLOSED_RECORD_CONSTRUCTOR
                         || fid == AsterixBuiltinFunctions.OPEN_RECORD_CONSTRUCTOR) {
@@ -99,7 +100,7 @@
 
     @SuppressWarnings("unchecked")
     private boolean propagateEquivalenceClassesForRecordConstructor(LogicalVariable recordVar,
-            ScalarFunctionCallExpression funcExpr, AssignOperator assignOp, IOptimizationContext context) {
+            AbstractFunctionCallExpression funcExpr, AssignOperator assignOp, IOptimizationContext context) {
         List<Mutable<ILogicalExpression>> argRefs = funcExpr.getArguments();
         boolean changed = false;
         // Only odd position arguments are field value expressions.
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/UnnestToDataScanRule.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/UnnestToDataScanRule.java
index 5af6ccc..1f965d6 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/UnnestToDataScanRule.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/UnnestToDataScanRule.java
@@ -131,7 +131,7 @@
                 List<Mutable<ILogicalOperator>> scanInpList = scan.getInputs();
                 scanInpList.addAll(unnest.getInputs());
                 opRef.setValue(scan);
-                addPrimaryKey(variables, context);
+                addPrimaryKey(variables, dataSource, context);
                 context.computeAndSetTypeEnvironmentForOperator(scan);
 
                 // Adds equivalence classes --- one equivalent class between a primary key
@@ -173,15 +173,15 @@
                 v.add(unnest.getVariable());
 
                 String csLocations = metadataProvider.getConfig().get(FeedActivityDetails.COLLECT_LOCATIONS);
-                DataSourceScanOperator scan = new DataSourceScanOperator(v,
-                        createFeedDataSource(asid, targetDataset, sourceFeedName, subscriptionLocation,
-                                metadataProvider, policy, outputType,
-                                null /* TODO(Abdullah): to figure out the meta type name*/, csLocations));
+                AqlDataSource dataSource = createFeedDataSource(asid, targetDataset, sourceFeedName,
+                        subscriptionLocation, metadataProvider, policy, outputType,
+                        null /* TODO(Abdullah): to figure out the meta type name*/, csLocations);
+                DataSourceScanOperator scan = new DataSourceScanOperator(v, dataSource);
 
                 List<Mutable<ILogicalOperator>> scanInpList = scan.getInputs();
                 scanInpList.addAll(unnest.getInputs());
                 opRef.setValue(scan);
-                addPrimaryKey(v, context);
+                addPrimaryKey(v, dataSource, context);
                 context.computeAndSetTypeEnvironmentForOperator(scan);
 
                 return true;
@@ -192,12 +192,12 @@
         return false;
     }
 
-    public void addPrimaryKey(List<LogicalVariable> scanVariables, IOptimizationContext context) {
-        int n = scanVariables.size();
-        List<LogicalVariable> head = new ArrayList<LogicalVariable>(scanVariables.subList(0, n - 1));
-        List<LogicalVariable> tail = new ArrayList<LogicalVariable>(1);
-        tail.add(scanVariables.get(n - 1));
-        FunctionalDependency pk = new FunctionalDependency(head, tail);
+    private void addPrimaryKey(List<LogicalVariable> scanVariables, AqlDataSource dataSource,
+            IOptimizationContext context) {
+        List<LogicalVariable> primaryKey = dataSource.getPrimaryKeyVariables(scanVariables);
+        List<LogicalVariable> tail = new ArrayList<LogicalVariable>();
+        tail.addAll(scanVariables);
+        FunctionalDependency pk = new FunctionalDependency(primaryKey, tail);
         context.addPrimaryKey(pk);
     }
 
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
index fce84d1..8a9bb3f 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
@@ -218,7 +218,8 @@
         }
     }
 
-    protected boolean matchesOperatorPattern(Mutable<ILogicalOperator> opRef, IOptimizationContext context) {
+    protected boolean matchesOperatorPattern(Mutable<ILogicalOperator> opRef, IOptimizationContext context)
+            throws AlgebricksException {
         // First check that the operator is a join and its condition is a function call.
         AbstractLogicalOperator op1 = (AbstractLogicalOperator) opRef.getValue();
         if (context.checkIfInDontApplySet(this, op1)) {
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
index bca7e04..5b9a9b6 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
@@ -137,7 +137,7 @@
 
     private boolean intersectAllSecondaryIndexes(List<Pair<IAccessMethod, Index>> chosenIndexes,
             Map<IAccessMethod, AccessMethodAnalysisContext> analyzedAMs, IOptimizationContext context)
-            throws AlgebricksException {
+                    throws AlgebricksException {
         Pair<IAccessMethod, Index> chosenIndex = null;
         Optional<Pair<IAccessMethod, Index>> primaryIndex = chosenIndexes.stream()
                 .filter(pair -> pair.second.isPrimaryIndex()).findFirst();
@@ -205,7 +205,8 @@
         return lop;
     }
 
-    protected boolean matchesOperatorPattern(Mutable<ILogicalOperator> opRef, IOptimizationContext context) {
+    protected boolean matchesOperatorPattern(Mutable<ILogicalOperator> opRef, IOptimizationContext context)
+            throws AlgebricksException {
         // First check that the operator is a select and its condition is a function call.
         AbstractLogicalOperator op1 = (AbstractLogicalOperator) opRef.getValue();
         if (context.checkIfInDontApplySet(this, op1)) {
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java
index 4865710..8313504 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java
@@ -43,6 +43,7 @@
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractUnnestOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.DataSourceScanOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestMapOperator;
+import org.apache.hyracks.algebricks.core.algebra.util.OperatorPropertiesUtil;
 
 /**
  * Operator subtree that matches the following patterns, and provides convenient access to its nodes:
@@ -77,7 +78,7 @@
     public List<Dataset> ixJoinOuterAdditionalDatasets = null;
     public List<ARecordType> ixJoinOuterAdditionalRecordTypes = null;
 
-    public boolean initFromSubTree(Mutable<ILogicalOperator> subTreeOpRef) {
+    public boolean initFromSubTree(Mutable<ILogicalOperator> subTreeOpRef) throws AlgebricksException {
         reset();
         rootRef = subTreeOpRef;
         root = subTreeOpRef.getValue();
@@ -96,11 +97,14 @@
                 return initializeDataSource(subTreeOpRef);
             }
             // Match (assign | unnest)+.
-            while (subTreeOp.getOperatorTag() == LogicalOperatorTag.ASSIGN
-                    || subTreeOp.getOperatorTag() == LogicalOperatorTag.UNNEST) {
-                assignsAndUnnestsRefs.add(subTreeOpRef);
-                assignsAndUnnests.add(subTreeOp);
-
+            while ((subTreeOp.getOperatorTag() == LogicalOperatorTag.ASSIGN
+                    || subTreeOp.getOperatorTag() == LogicalOperatorTag.UNNEST)) {
+                if (OperatorPropertiesUtil.isStatefulAssign(subTreeOp)) {
+                    return false;
+                } else {
+                    assignsAndUnnestsRefs.add(subTreeOpRef);
+                    assignsAndUnnests.add(subTreeOp);
+                }
                 subTreeOpRef = subTreeOp.getInputs().get(0);
                 subTreeOp = (AbstractLogicalOperator) subTreeOpRef.getValue();
             };
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java
index 10ef1f6..09dd2b2 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineAllNtsInSubplanVisitor.java
@@ -19,11 +19,13 @@
 package org.apache.asterix.optimizer.rules.subplan;
 
 import java.util.ArrayList;
+import java.util.Collections;
 import java.util.HashMap;
 import java.util.HashSet;
 import java.util.Iterator;
 import java.util.List;
 import java.util.Map;
+import java.util.Map.Entry;
 import java.util.Set;
 
 import org.apache.asterix.lang.common.util.FunctionUtil;
@@ -79,6 +81,7 @@
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.LogicalOperatorDeepCopyWithNewVariablesVisitor;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities;
 import org.apache.hyracks.algebricks.core.algebra.plan.ALogicalPlanImpl;
+import org.apache.hyracks.algebricks.core.algebra.properties.FunctionalDependency;
 import org.apache.hyracks.algebricks.core.algebra.util.OperatorManipulationUtil;
 import org.apache.hyracks.algebricks.core.algebra.visitors.IQueryOperatorVisitor;
 
@@ -418,10 +421,11 @@
 
         // Updates the primary key info in the copied plan segment.
         Map<LogicalVariable, LogicalVariable> varMap = deepCopyVisitor.getInputToOutputVariableMapping();
-        context.updatePrimaryKeys(varMap);
-
+        addPrimaryKeys(varMap);
+        Pair<ILogicalOperator, Set<LogicalVariable>> primaryOpAndVars = EquivalenceClassUtils
+                .findOrCreatePrimaryKeyOpAndVariables(copiedInputOperator, true, context);
         correlatedKeyVars.clear();
-        correlatedKeyVars.addAll(EquivalenceClassUtils.findFDHeaderVariables(context, subplanInputOperator));
+        correlatedKeyVars.addAll(primaryOpAndVars.second);
         // Update key variables and input-output-var mapping.
         for (Map.Entry<LogicalVariable, LogicalVariable> entry : varMap.entrySet()) {
             LogicalVariable oldVar = entry.getKey();
@@ -432,7 +436,7 @@
             }
             updateInputToOutputVarMapping(oldVar, newVar, true);
         }
-        return copiedInputOperator;
+        return primaryOpAndVars.first;
     }
 
     @Override
@@ -726,4 +730,24 @@
         return clonedOrderExprs;
     }
 
+    private void addPrimaryKeys(Map<LogicalVariable, LogicalVariable> varMap) {
+        for (Entry<LogicalVariable, LogicalVariable> entry : varMap.entrySet()) {
+            List<LogicalVariable> dependencyVars = context.findPrimaryKey(entry.getKey());
+            if (dependencyVars == null) {
+                // No key dependencies
+                continue;
+            }
+            List<LogicalVariable> newDependencies = new ArrayList<>();
+            for (LogicalVariable dependencyVar : dependencyVars) {
+                LogicalVariable newDependencyVar = varMap.get(dependencyVar);
+                if (newDependencyVar == null) {
+                    continue;
+                }
+                newDependencies.add(newDependencyVar);
+            }
+            context.addPrimaryKey(
+                    new FunctionalDependency(newDependencies, Collections.singletonList(entry.getValue())));
+        }
+    }
+
 }
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineSubplanInputForNestedTupleSourceRule.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineSubplanInputForNestedTupleSourceRule.java
index 9f03ffa..c018e8e 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineSubplanInputForNestedTupleSourceRule.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineSubplanInputForNestedTupleSourceRule.java
@@ -337,20 +337,25 @@
             return traverseNonSubplanOperator(subplanOp, context);
         }
         Mutable<ILogicalOperator> inputOpRef = subplanOp.getInputs().get(0);
-        ILogicalOperator inputOp = inputOpRef.getValue();
+        ILogicalOperator inputOpBackup = inputOpRef.getValue();
+        // Creates parameters for the left outer join operator.
+        Pair<ILogicalOperator, Set<LogicalVariable>> primaryOpAndVars = EquivalenceClassUtils
+                .findOrCreatePrimaryKeyOpAndVariables(inputOpBackup, true, context);
+        ILogicalOperator inputOp = primaryOpAndVars.first;
+        Set<LogicalVariable> primaryKeyVars = primaryOpAndVars.second;
+        inputOpRef.setValue(inputOp);
+        Set<LogicalVariable> inputLiveVars = new HashSet<LogicalVariable>();
+        VariableUtilities.getLiveVariables(inputOp, inputLiveVars);
+
         Pair<Map<LogicalVariable, LogicalVariable>, List<Pair<IOrder, Mutable<ILogicalExpression>>>> varMapAndOrderExprs = SubplanFlatteningUtil
                 .inlineAllNestedTupleSource(subplanOp, context);
         Map<LogicalVariable, LogicalVariable> varMap = varMapAndOrderExprs.first;
         if (varMap == null) {
+            inputOpRef.setValue(inputOpBackup);
             // Traverses the operator as if it is not a subplan.
             return traverseNonSubplanOperator(subplanOp, context);
         }
 
-        // Creates parameters for the left outer join operator.
-        Set<LogicalVariable> inputLiveVars = new HashSet<LogicalVariable>();
-        VariableUtilities.getLiveVariables(inputOp, inputLiveVars);
-        Set<LogicalVariable> fdCoveringVars = EquivalenceClassUtils.findFDHeaderVariables(context, inputOp);
-
         Mutable<ILogicalOperator> rightInputOpRef = subplanOp.getNestedPlans().get(0).getRoots().get(0).getValue()
                 .getInputs().get(0);
         ILogicalOperator rightInputOp = rightInputOpRef.getValue();
@@ -365,7 +370,7 @@
 
         // Constructs the join predicate for the leftOuter join.
         List<Mutable<ILogicalExpression>> joinPredicates = new ArrayList<Mutable<ILogicalExpression>>();
-        for (LogicalVariable liveVar : fdCoveringVars) {
+        for (LogicalVariable liveVar : primaryKeyVars) {
             List<Mutable<ILogicalExpression>> arguments = new ArrayList<Mutable<ILogicalExpression>>();
             arguments.add(new MutableObject<ILogicalExpression>(new VariableReferenceExpression(liveVar)));
             LogicalVariable rightVar = varMap.get(liveVar);
@@ -378,7 +383,7 @@
         ILogicalExpression joinExpr = joinPredicates.size() > 1
                 ? new ScalarFunctionCallExpression(FunctionUtil.getFunctionInfo(AlgebricksBuiltinFunctions.AND),
                         joinPredicates)
-                : joinPredicates.get(0).getValue();
+                : joinPredicates.size() > 0 ? joinPredicates.get(0).getValue() : ConstantExpression.TRUE;
         LeftOuterJoinOperator leftOuterJoinOp = new LeftOuterJoinOperator(
                 new MutableObject<ILogicalExpression>(joinExpr), inputOpRef, rightInputOpRef);
         OperatorManipulationUtil.computeTypeEnvironmentBottomUp(rightInputOp, context);
@@ -391,7 +396,7 @@
         GroupByOperator groupbyOp = new GroupByOperator(groupByList, groupByDecorList, nestedPlans);
 
         Map<LogicalVariable, LogicalVariable> replacedVarMap = new HashMap<>();
-        for (LogicalVariable liveVar : fdCoveringVars) {
+        for (LogicalVariable liveVar : primaryKeyVars) {
             LogicalVariable newVar = context.newVar();
             groupByList.add(new Pair<LogicalVariable, Mutable<ILogicalExpression>>(newVar,
                     new MutableObject<ILogicalExpression>(new VariableReferenceExpression(liveVar))));
@@ -399,7 +404,7 @@
             replacedVarMap.put(liveVar, newVar);
         }
         for (LogicalVariable liveVar : inputLiveVars) {
-            if (fdCoveringVars.contains(liveVar)) {
+            if (primaryKeyVars.contains(liveVar)) {
                 continue;
             }
             groupByDecorList.add(new Pair<LogicalVariable, Mutable<ILogicalExpression>>(null,
@@ -458,7 +463,7 @@
     private Pair<Boolean, Map<LogicalVariable, LogicalVariable>> applySpecialFlattening(Mutable<ILogicalOperator> opRef,
             IOptimizationContext context) throws AlgebricksException {
         SubplanOperator subplanOp = (SubplanOperator) opRef.getValue();
-        ILogicalOperator inputOp = subplanOp.getInputs().get(0).getValue();
+        Mutable<ILogicalOperator> inputOpRef = subplanOp.getInputs().get(0);
         Map<LogicalVariable, LogicalVariable> replacedVarMap = new HashMap<>();
 
         // Recursively applies this rule to the nested plan of the subplan operator,
@@ -466,27 +471,33 @@
         Pair<Boolean, Map<LogicalVariable, LogicalVariable>> result = rewriteSubplanOperator(
                 subplanOp.getNestedPlans().get(0).getRoots().get(0), context);
 
+        ILogicalOperator inputOpBackup = inputOpRef.getValue();
+        // Gets live variables and covering variables from the subplan's input operator.
+        Pair<ILogicalOperator, Set<LogicalVariable>> primaryOpAndVars = EquivalenceClassUtils
+                .findOrCreatePrimaryKeyOpAndVariables(inputOpBackup, false, context);
+        ILogicalOperator inputOp = primaryOpAndVars.first;
+        Set<LogicalVariable> primaryKeyVars = primaryOpAndVars.second;
+        inputOpRef.setValue(inputOp);
+        Set<LogicalVariable> liveVars = new HashSet<>();
+        VariableUtilities.getLiveVariables(inputOp, liveVars);
+
         Pair<Set<LogicalVariable>, Mutable<ILogicalOperator>> notNullVarsAndTopJoinRef = SubplanFlatteningUtil
                 .inlineLeftNtsInSubplanJoin(subplanOp, context);
         if (notNullVarsAndTopJoinRef.first == null) {
+            inputOpRef.setValue(inputOpBackup);
             return new Pair<Boolean, Map<LogicalVariable, LogicalVariable>>(false, replacedVarMap);
         }
 
         Set<LogicalVariable> notNullVars = notNullVarsAndTopJoinRef.first;
         Mutable<ILogicalOperator> topJoinRef = notNullVarsAndTopJoinRef.second;
 
-        // Gets live variables and covering variables from the subplan's input operator.
-        Set<LogicalVariable> fdCoveringVars = EquivalenceClassUtils.findFDHeaderVariables(context, inputOp);
-        Set<LogicalVariable> liveVars = new HashSet<>();
-        VariableUtilities.getLiveVariables(inputOp, liveVars);
-
         // Creates a group-by operator.
         List<Pair<LogicalVariable, Mutable<ILogicalExpression>>> groupByList = new ArrayList<Pair<LogicalVariable, Mutable<ILogicalExpression>>>();
         List<Pair<LogicalVariable, Mutable<ILogicalExpression>>> groupByDecorList = new ArrayList<Pair<LogicalVariable, Mutable<ILogicalExpression>>>();
         GroupByOperator groupbyOp = new GroupByOperator(groupByList, groupByDecorList, subplanOp.getNestedPlans());
 
         Map<LogicalVariable, LogicalVariable> gbyVarMap = new HashMap<LogicalVariable, LogicalVariable>();
-        for (LogicalVariable coverVar : fdCoveringVars) {
+        for (LogicalVariable coverVar : primaryKeyVars) {
             LogicalVariable newVar = context.newVar();
             gbyVarMap.put(coverVar, newVar);
             groupByList.add(new Pair<LogicalVariable, Mutable<ILogicalExpression>>(newVar,
@@ -495,7 +506,7 @@
             replacedVarMap.put(coverVar, newVar);
         }
         for (LogicalVariable liveVar : liveVars) {
-            if (fdCoveringVars.contains(liveVar)) {
+            if (primaryKeyVars.contains(liveVar)) {
                 continue;
             }
             groupByDecorList.add(new Pair<LogicalVariable, Mutable<ILogicalExpression>>(null,
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/util/EquivalenceClassUtils.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/util/EquivalenceClassUtils.java
index 242e15b..28f1fca 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/util/EquivalenceClassUtils.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/util/EquivalenceClassUtils.java
@@ -19,6 +19,8 @@
 
 package org.apache.asterix.optimizer.rules.util;
 
+import java.util.ArrayList;
+import java.util.Collections;
 import java.util.HashMap;
 import java.util.HashSet;
 import java.util.List;
@@ -33,20 +35,20 @@
 import org.apache.asterix.om.constants.AsterixConstantValue;
 import org.apache.asterix.om.functions.AsterixBuiltinFunctions;
 import org.apache.asterix.om.types.ARecordType;
-import org.apache.asterix.om.types.ATypeTag;
-import org.apache.asterix.om.types.IAType;
 import org.apache.commons.lang3.mutable.MutableObject;
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.common.utils.Pair;
 import org.apache.hyracks.algebricks.core.algebra.base.EquivalenceClass;
 import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
 import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
 import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext;
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ConstantExpression;
-import org.apache.hyracks.algebricks.core.algebra.expressions.IVariableTypeEnvironment;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ScalarFunctionCallExpression;
+import org.apache.hyracks.algebricks.core.algebra.expressions.StatefulFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AssignOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.PrimaryKeyVariablesVisitor;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities;
 import org.apache.hyracks.algebricks.core.algebra.properties.FunctionalDependency;
 import org.apache.hyracks.algebricks.rewriter.util.PhysicalOptimizationsUtil;
@@ -133,48 +135,60 @@
     /**
      * Find the header variables that can imply all subplan-local live variables at <code>operator</code>.
      *
-     * @param context
-     *            the optimization context.
      * @param operator
      *            the operator of interest.
-     * @return a set of covering variables that can imply all subplan-local live variables at <code>operator</code>.
+     * @param usedForCorrelationJoin
+     *            whether the generated primary key will be used for a join that recovers the correlation.
+     * @param context
+     *            the optimization context.
+     * @return Pair<ILogicalOperator, Set<LogicalVariable>>, an operator (which is either the original parameter
+     *         <code>operator</code> or a newly created operator) and
+     *         a set of primary key variables at the operator.
      * @throws AlgebricksException
      */
-    public static Set<LogicalVariable> findFDHeaderVariables(IOptimizationContext context, ILogicalOperator operator)
-            throws AlgebricksException {
-        PhysicalOptimizationsUtil.computeFDsAndEquivalenceClasses((AbstractLogicalOperator) operator, context);
-        List<FunctionalDependency> fds = context.getFDList(operator);
-        context.clearAllFDAndEquivalenceClasses();
+    public static Pair<ILogicalOperator, Set<LogicalVariable>> findOrCreatePrimaryKeyOpAndVariables(
+            ILogicalOperator operator, boolean usedForCorrelationJoin, IOptimizationContext context)
+                    throws AlgebricksException {
+        computePrimaryKeys(operator, context);
 
         Set<LogicalVariable> liveVars = new HashSet<>();
         VariableUtilities.getSubplanLocalLiveVariables(operator, liveVars);
 
-        Set<LogicalVariable> key = new HashSet<>();
-        Set<LogicalVariable> cover = new HashSet<>();
-        for (FunctionalDependency fd : fds) {
-            List<LogicalVariable> head = fd.getHead();
-            head.retainAll(liveVars);
-            key.addAll(head);
-            cover.addAll(fd.getTail());
-            if (cover.containsAll(liveVars)) {
-                return key;
+        Set<LogicalVariable> primaryKeyVars = new HashSet<>();
+        Set<LogicalVariable> noKeyVars = new HashSet<>();
+        for (LogicalVariable liveVar : liveVars) {
+            List<LogicalVariable> keyVars = context.findPrimaryKey(liveVar);
+            if (keyVars != null) {
+                keyVars.retainAll(liveVars);
+            }
+            if ((keyVars == null || keyVars.isEmpty())) {
+                noKeyVars.add(liveVar);
+            } else {
+                primaryKeyVars.addAll(keyVars);
             }
         }
-        if (cover.containsAll(liveVars)) {
-            return key;
+        primaryKeyVars.retainAll(liveVars);
+        if (primaryKeyVars.containsAll(noKeyVars)) {
+            return new Pair<ILogicalOperator, Set<LogicalVariable>>(operator, primaryKeyVars);
         } else {
-            IVariableTypeEnvironment env = context.getOutputTypeEnvironment(operator);
-            Set<LogicalVariable> keyVars = new HashSet<>();
-            for (LogicalVariable var : liveVars) {
-                IAType type = (IAType) env.getVarType(var);
-                ATypeTag typeTag = type.getTypeTag();
-                if (typeTag == ATypeTag.RECORD || typeTag == ATypeTag.ORDEREDLIST
-                        || typeTag == ATypeTag.UNORDEREDLIST) {
-                    continue;
-                }
-                keyVars.add(var);
-            }
-            return keyVars;
+            LogicalVariable assignVar = context.newVar();
+            ILogicalOperator assignOp = new AssignOperator(assignVar,
+                    new MutableObject<ILogicalExpression>(usedForCorrelationJoin
+                            ? new StatefulFunctionCallExpression(
+                                    FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.CREATE_QUERY_UID), null)
+                            : new ScalarFunctionCallExpression(
+                                    FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.CREATE_QUERY_UID))));
+            assignOp.getInputs().add(new MutableObject<ILogicalOperator>(operator));
+            context.addPrimaryKey(new FunctionalDependency(Collections.singletonList(assignVar),
+                    new ArrayList<LogicalVariable>(liveVars)));
+            context.computeAndSetTypeEnvironmentForOperator(assignOp);
+            return new Pair<ILogicalOperator, Set<LogicalVariable>>(assignOp, Collections.singleton(assignVar));
         }
     }
+
+    private static void computePrimaryKeys(ILogicalOperator op, IOptimizationContext ctx) throws AlgebricksException {
+        PrimaryKeyVariablesVisitor visitor = new PrimaryKeyVariablesVisitor();
+        PhysicalOptimizationsUtil.visitOperatorAndItsDescendants(op, visitor, ctx);
+    }
+
 }
diff --git a/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1168.aql b/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1168.aql
new file mode 100644
index 0000000..4df51c6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1168.aql
@@ -0,0 +1,33 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TType as closed
+{ id: int64, content: string }
+
+create dataset TData (TType) primary key id;
+
+
+let $ps := ["b","a", "b","c","c"]
+for $p in $ps
+return
+{ "p":$p, "match": for $x in dataset TData where $x.content = $p return $x.id }
diff --git a/asterix-app/src/test/resources/optimizerts/queries/udfs/query-ASTERIXDB-1020.aql b/asterix-app/src/test/resources/optimizerts/queries/udfs/query-ASTERIXDB-1020.aql
index 17e50df..c980445 100644
--- a/asterix-app/src/test/resources/optimizerts/queries/udfs/query-ASTERIXDB-1020.aql
+++ b/asterix-app/src/test/resources/optimizerts/queries/udfs/query-ASTERIXDB-1020.aql
@@ -50,7 +50,7 @@
     let $dangerzone := create-circle($emergency.epicenter,$emergency.radius)
     where (some $user in dataset userLocations satisfies
     $user.user-id = $userid and spatial-intersect($dangerzone,$user.location))
-return { "shelter locations":"for $shelter in dataset tornadoShelters return $shelter.location"}
+return { "shelter locations": for $shelter in dataset tornadoShelters return $shelter.location}
 };
 
 
diff --git a/asterix-app/src/test/resources/optimizerts/results/external-indexing/leftouterjoin-probe-sidx-with-join-btree-sidx_01.plan b/asterix-app/src/test/resources/optimizerts/results/external-indexing/leftouterjoin-probe-sidx-with-join-btree-sidx_01.plan
index e40816f..654dcf1 100644
--- a/asterix-app/src/test/resources/optimizerts/results/external-indexing/leftouterjoin-probe-sidx-with-join-btree-sidx_01.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/external-indexing/leftouterjoin-probe-sidx-with-join-btree-sidx_01.plan
@@ -2,40 +2,44 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$27(ASC), $$28(ASC) ]  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$19, $$21]  |PARTITIONED|
-                  {
-                    -- AGGREGATE  |LOCAL|
-                      -- STREAM_SELECT  |LOCAL|
-                        -- NESTED_TUPLE_SOURCE  |LOCAL|
-                  }
+        -- SORT_MERGE_EXCHANGE [$$19(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$19(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              -- STABLE_SORT [$$19(ASC), $$21(ASC), $$22(ASC)]  |PARTITIONED|
-                -- HASH_PARTITION_EXCHANGE [$$19, $$21]  |PARTITIONED|
-                  -- STREAM_SELECT  |PARTITIONED|
-                    -- ASSIGN  |PARTITIONED|
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- EXTERNAL_LOOKUP  |PARTITIONED|
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- STABLE_SORT [$$34(ASC), $$35(ASC)]  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- PRE_CLUSTERED_GROUP_BY[$$26]  |PARTITIONED|
+                          {
+                            -- AGGREGATE  |LOCAL|
+                              -- STREAM_SELECT  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                          }
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$26(ASC), $$22(ASC)]  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$26]  |PARTITIONED|
+                          -- STREAM_SELECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- STREAM_PROJECT  |PARTITIONED|
+                                  -- EXTERNAL_LOOKUP  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- BTREE_SEARCH  |PARTITIONED|
-                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                      -- STABLE_SORT [$$34(ASC), $$35(ASC)]  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
-                                            -- ASSIGN  |PARTITIONED|
-                                              -- STREAM_SELECT  |PARTITIONED|
-                                                -- ASSIGN  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- EXTERNAL_LOOKUP  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- STABLE_SORT [$$31(ASC), $$32(ASC)]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- BTREE_SEARCH  |PARTITIONED|
+                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ASSIGN  |PARTITIONED|
+                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- EXTERNAL_LOOKUP  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- BTREE_SEARCH  |PARTITIONED|
+                                                                -- STABLE_SORT [$$31(ASC), $$32(ASC)]  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- ASSIGN  |PARTITIONED|
-                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- BTREE_SEARCH  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- ASSIGN  |PARTITIONED|
+                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1168.plan b/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1168.plan
new file mode 100644
index 0000000..b4d6934
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1168.plan
@@ -0,0 +1,31 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- PRE_CLUSTERED_GROUP_BY[$$12]  |PARTITIONED|
+                    {
+                      -- AGGREGATE  |LOCAL|
+                        -- STREAM_SELECT  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                    }
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [$$12(ASC)]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$12]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- HYBRID_HASH_JOIN [$$1][$$11]  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$1]  |PARTITIONED|
+                            -- ASSIGN  |UNPARTITIONED|
+                              -- STREAM_PROJECT  |UNPARTITIONED|
+                                -- UNNEST  |UNPARTITIONED|
+                                  -- ASSIGN  |UNPARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$11]  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan b/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
index 28625aa..59cfd60 100644
--- a/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
@@ -3,66 +3,61 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$7(ASC) ]  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$86]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$83]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$86(ASC)] HASH:[$$86]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$59]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$83(ASC)] HASH:[$$83]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$11]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                       }
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                   -- STREAM_PROJECT  |PARTITIONED|
-                    -- ASSIGN  |PARTITIONED|
+                    -- STREAM_SELECT  |PARTITIONED|
                       -- STREAM_PROJECT  |PARTITIONED|
-                        -- STREAM_SELECT  |PARTITIONED|
-                          -- STREAM_PROJECT  |PARTITIONED|
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- SORT_GROUP_BY[$$83]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- PRE_CLUSTERED_GROUP_BY[$$80]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$80(ASC)] HASH:[$$80]  |PARTITIONED|
+                              -- PRE_CLUSTERED_GROUP_BY[$$75]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
-                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          -- STREAM_SELECT  |LOCAL|
+                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
-                                  -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STABLE_SORT [$$75(ASC)]  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- PRE_CLUSTERED_GROUP_BY[$$65, $$11, $$62]  |PARTITIONED|
-                                              {
-                                                -- AGGREGATE  |LOCAL|
-                                                  -- STREAM_SELECT  |LOCAL|
-                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                              }
+                                      -- STREAM_PROJECT  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- STABLE_SORT [$$65(ASC), $$65(ASC), $$62(ASC)]  |PARTITIONED|
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- STABLE_SORT [$$65(ASC), $$65(ASC), $$62(ASC)]  |PARTITIONED|
-                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- HYBRID_HASH_JOIN [$$68][$$69]  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$68]  |PARTITIONED|
-                                                          -- ASSIGN  |PARTITIONED|
-                                                            -- ASSIGN  |PARTITIONED|
-                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- HYBRID_HASH_JOIN [$$65][$$11]  |PARTITIONED|
-                                                                  -- HASH_PARTITION_EXCHANGE [$$65]  |PARTITIONED|
-                                                                    -- UNNEST  |UNPARTITIONED|
-                                                                      -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                                                  -- HASH_PARTITION_EXCHANGE [$$11]  |PARTITIONED|
-                                                                    -- ASSIGN  |PARTITIONED|
-                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                        -- ASSIGN  |PARTITIONED|
-                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                            -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$69]  |PARTITIONED|
-                                                          -- ASSIGN  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$68][$$69]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$68]  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- HYBRID_HASH_JOIN [$$65][$$11]  |PARTITIONED|
+                                                          -- HASH_PARTITION_EXCHANGE [$$65]  |PARTITIONED|
+                                                            -- UNNEST  |UNPARTITIONED|
+                                                              -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                                          -- HASH_PARTITION_EXCHANGE [$$11]  |PARTITIONED|
                                                             -- STREAM_PROJECT  |PARTITIONED|
-                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$69]  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1018.plan b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1018.plan
index 1e7e505..3b7f231 100644
--- a/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1018.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1018.plan
@@ -4,71 +4,69 @@
       -- ASSIGN  |PARTITIONED|
         -- STREAM_PROJECT  |PARTITIONED|
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-            -- HYBRID_HASH_JOIN [$$34][$$50]  |PARTITIONED|
+            -- HYBRID_HASH_JOIN [$$34][$$47]  |PARTITIONED|
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                     -- DATASOURCE_SCAN  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-              -- HASH_PARTITION_EXCHANGE [$$50]  |PARTITIONED|
+              -- HASH_PARTITION_EXCHANGE [$$47]  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ASSIGN  |PARTITIONED|
                     -- STREAM_PROJECT  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- PRE_CLUSTERED_GROUP_BY[$$55, $$24, $$54]  |PARTITIONED|
+                        -- PRE_CLUSTERED_GROUP_BY[$$51, $$24, $$52]  |PARTITIONED|
                                 {
                                   -- AGGREGATE  |LOCAL|
                                     -- STREAM_SELECT  |LOCAL|
                                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                                 }
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- STABLE_SORT [$$55(ASC), $$24(ASC), $$54(ASC)]  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$55, $$24, $$54]  |PARTITIONED|
+                            -- STABLE_SORT [$$51(ASC), $$24(ASC), $$52(ASC)]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$51, $$24, $$52]  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- NESTED_LOOP  |PARTITIONED|
                                       -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                        -- STREAM_PROJECT  |PARTITIONED|
-                                          -- STREAM_SELECT  |PARTITIONED|
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- PRE_CLUSTERED_GROUP_BY[$$49, $$35]  |PARTITIONED|
-                                                      {
-                                                        -- AGGREGATE  |LOCAL|
-                                                          -- STREAM_SELECT  |LOCAL|
-                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                      }
-                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  -- STABLE_SORT [$$49(ASC), $$35(ASC)]  |PARTITIONED|
-                                                    -- HASH_PARTITION_EXCHANGE [$$49, $$35]  |PARTITIONED|
-                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- HYBRID_HASH_JOIN [$$47][$$38]  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$47]  |PARTITIONED|
-                                                              -- NESTED_LOOP  |PARTITIONED|
-                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                    -- ASSIGN  |PARTITIONED|
-                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- STREAM_SELECT  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- PRE_CLUSTERED_GROUP_BY[$$35, $$46]  |PARTITIONED|
+                                                    {
+                                                      -- AGGREGATE  |LOCAL|
+                                                        -- STREAM_SELECT  |LOCAL|
+                                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                    }
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- STABLE_SORT [$$35(ASC), $$46(ASC)]  |PARTITIONED|
+                                                  -- HASH_PARTITION_EXCHANGE [$$35, $$46]  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- HYBRID_HASH_JOIN [$$44][$$38]  |PARTITIONED|
+                                                          -- HASH_PARTITION_EXCHANGE [$$44]  |PARTITIONED|
+                                                            -- NESTED_LOOP  |PARTITIONED|
+                                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
                                                                   -- ASSIGN  |PARTITIONED|
-                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                      -- ASSIGN  |PARTITIONED|
-                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                          -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$38]  |PARTITIONED|
-                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                -- ASSIGN  |PARTITIONED|
-                                                                  -- STREAM_PROJECT  |PARTITIONED|
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                       -- DATASOURCE_SCAN  |PARTITIONED|
                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ASSIGN  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                          -- HASH_PARTITION_EXCHANGE [$$38]  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ASSIGN  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1019.plan b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1019.plan
index 3bf96d9..db30fa1 100644
--- a/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1019.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1019.plan
@@ -12,71 +12,69 @@
                       -- ASSIGN  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- HYBRID_HASH_JOIN [$$38][$$54]  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$38][$$51]  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- DATASOURCE_SCAN  |PARTITIONED|
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$54]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$51]  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ASSIGN  |PARTITIONED|
                                     -- STREAM_PROJECT  |PARTITIONED|
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- PRE_CLUSTERED_GROUP_BY[$$59, $$24, $$58]  |PARTITIONED|
+                                        -- PRE_CLUSTERED_GROUP_BY[$$56, $$24, $$55]  |PARTITIONED|
                                                 {
                                                   -- AGGREGATE  |LOCAL|
                                                     -- STREAM_SELECT  |LOCAL|
                                                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                                                 }
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- STABLE_SORT [$$59(ASC), $$24(ASC), $$58(ASC)]  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$59, $$24, $$58]  |PARTITIONED|
+                                            -- STABLE_SORT [$$56(ASC), $$24(ASC), $$55(ASC)]  |PARTITIONED|
+                                              -- HASH_PARTITION_EXCHANGE [$$56, $$24, $$55]  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- NESTED_LOOP  |PARTITIONED|
                                                       -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                          -- STREAM_SELECT  |PARTITIONED|
-                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              -- PRE_CLUSTERED_GROUP_BY[$$53, $$39]  |PARTITIONED|
-                                                                      {
-                                                                        -- AGGREGATE  |LOCAL|
-                                                                          -- STREAM_SELECT  |LOCAL|
-                                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                      }
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- STABLE_SORT [$$53(ASC), $$39(ASC)]  |PARTITIONED|
-                                                                    -- HASH_PARTITION_EXCHANGE [$$53, $$39]  |PARTITIONED|
-                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                          -- HYBRID_HASH_JOIN [$$51][$$42]  |PARTITIONED|
-                                                                            -- HASH_PARTITION_EXCHANGE [$$51]  |PARTITIONED|
-                                                                              -- NESTED_LOOP  |PARTITIONED|
-                                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                                    -- ASSIGN  |PARTITIONED|
-                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- STREAM_SELECT  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- PRE_CLUSTERED_GROUP_BY[$$50, $$39]  |PARTITIONED|
+                                                                    {
+                                                                      -- AGGREGATE  |LOCAL|
+                                                                        -- STREAM_SELECT  |LOCAL|
+                                                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                    }
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- STABLE_SORT [$$50(ASC), $$39(ASC)]  |PARTITIONED|
+                                                                  -- HASH_PARTITION_EXCHANGE [$$50, $$39]  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- HYBRID_HASH_JOIN [$$48][$$42]  |PARTITIONED|
+                                                                          -- HASH_PARTITION_EXCHANGE [$$48]  |PARTITIONED|
+                                                                            -- NESTED_LOOP  |PARTITIONED|
+                                                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
                                                                                   -- ASSIGN  |PARTITIONED|
-                                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                                      -- ASSIGN  |PARTITIONED|
-                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                          -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                            -- HASH_PARTITION_EXCHANGE [$$42]  |PARTITIONED|
-                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                -- ASSIGN  |PARTITIONED|
-                                                                                  -- STREAM_PROJECT  |PARTITIONED|
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                       -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                          -- HASH_PARTITION_EXCHANGE [$$42]  |PARTITIONED|
+                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- ASSIGN  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1020.plan b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1020.plan
index 7cf5bc4..ef95877 100644
--- a/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1020.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1020.plan
@@ -1,63 +1,58 @@
 -- DISTRIBUTE_RESULT  |PARTITIONED|
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    -- STREAM_PROJECT  |PARTITIONED|
-      -- ASSIGN  |PARTITIONED|
+    -- NESTED_LOOP  |PARTITIONED|
+      -- BROADCAST_EXCHANGE  |PARTITIONED|
         -- STREAM_PROJECT  |PARTITIONED|
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-            -- HYBRID_HASH_JOIN [$$26][$$38]  |PARTITIONED|
+            -- DATASOURCE_SCAN  |PARTITIONED|
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                -- ASSIGN  |PARTITIONED|
-                  -- STREAM_PROJECT  |PARTITIONED|
-                    -- ASSIGN  |PARTITIONED|
+                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- NESTED_LOOP  |PARTITIONED|
+          -- BROADCAST_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- STREAM_SELECT  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$32]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- STREAM_SELECT  |LOCAL|
+                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- SPLIT  |PARTITIONED|
-                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- STABLE_SORT [$$32(ASC)]  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$32]  |PARTITIONED|
                             -- STREAM_PROJECT  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- DATASOURCE_SCAN  |PARTITIONED|
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-              -- HASH_PARTITION_EXCHANGE [$$38]  |PARTITIONED|
-                -- STREAM_PROJECT  |PARTITIONED|
-                  -- STREAM_SELECT  |PARTITIONED|
-                    -- STREAM_PROJECT  |PARTITIONED|
-                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- PRE_CLUSTERED_GROUP_BY[$$27, $$36]  |PARTITIONED|
-                                {
-                                  -- AGGREGATE  |LOCAL|
-                                    -- STREAM_SELECT  |LOCAL|
-                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                }
-                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- STABLE_SORT [$$27(ASC), $$36(ASC)]  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$27, $$36]  |PARTITIONED|
                                 -- NESTED_LOOP  |PARTITIONED|
                                   -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                    -- SPLIT  |PARTITIONED|
-                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- STREAM_PROJECT  |PARTITIONED|
-                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- DATASOURCE_SCAN  |PARTITIONED|
-                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- DATASOURCE_SCAN  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- STREAM_PROJECT  |PARTITIONED|
-                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- NESTED_LOOP  |PARTITIONED|
-                                          -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- ASSIGN  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- STREAM_SELECT  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                   -- DATASOURCE_SCAN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- ASSIGN  |PARTITIONED|
-                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                -- STREAM_SELECT  |PARTITIONED|
-                                                  -- ASSIGN  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- STREAM_PROJECT  |UNPARTITIONED|
+              -- ASSIGN  |UNPARTITIONED|
+                -- AGGREGATE  |UNPARTITIONED|
+                  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.1.ddl.sqlpp
new file mode 100644
index 0000000..2a24740
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.1.ddl.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * 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.
+ */
+
+DROP DATABASE test IF EXISTS;
+CREATE DATABASE test;
+USE test;
+
+
+CREATE TYPE OrderType AS CLOSED {
+  o_orderkey: int32,
+  o_custkey: int32,
+  o_orderstatus: string,
+  o_totalprice: double,
+  o_orderdate: string,
+  o_orderpriority: string,
+  o_clerk: string,
+  o_shippriority: int32,
+  o_comment: string
+}
+
+CREATE TYPE CustomerType AS CLOSED {
+  c_custkey: int32,
+  c_name: string,
+  c_address: string,
+  c_nationkey: int32,
+  c_phone: string,
+  c_acctbal: double,
+  c_mktsegment: string,
+  c_comment: string
+}
+
+
+CREATE EXTERNAL TABLE Customers(CustomerType) USING "localfs"
+(("path"="asterix_nc1://data/tpch0.001/customer.tbl"),
+("input-format"="text-input-format"),("format"="delimited-text"),("delimiter"="|"));
+
+CREATE EXTERNAL TABLE Orders(OrderType) USING "localfs"
+(("path"="asterix_nc1://data/tpch0.001/orders.tbl"),
+("input-format"="text-input-format"),("format"="delimited-text"),("delimiter"="|"));
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.2.update.sqlpp
new file mode 100644
index 0000000..7220975
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.2.update.sqlpp
@@ -0,0 +1,18 @@
+/*
+ * 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.
+ */
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.3.query.sqlpp
new file mode 100644
index 0000000..b78ab6f
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.3.query.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1168.
+ */
+
+USE test;
+
+FROM Customers c
+LET orders = (
+    FROM Orders o
+    WHERE c.c_nationkey = 5 and o.o_custkey = c.c_custkey
+    SELECT ELEMENT o
+  )
+SELECT c.c_custkey customer_name, orders orders
+ORDER BY customer_name;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.1.ddl.sqlpp
new file mode 100644
index 0000000..29cefa5
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.1.ddl.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * 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.
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+  "screen-name" : string,
+  lang : string,
+  "friends-count" : int64,
+  "statuses-count" : int64,
+  name : string,
+  "followers-count" : int64
+}
+
+create type test.TweetMessageType as
+ closed {
+  tweetid : int64,
+  user : TwitterUserType,
+  "sender-location" : point,
+  "send-time" : datetime,
+  "referred-topics" : {{string}},
+  "message-text" : string,
+  countA : int64,
+  countB : int64
+}
+
+create external  table TweetMessages(TweetMessageType) using "hdfs"(("hdfs"="hdfs://127.0.0.1:31888"),("path"="/asterix/tw_for_indexleftouterjoin.adm"),("input-format"="text-input-format"),("format"="adm"));
+
+create  index IdIx  on TweetMessages (tweetid) type btree;
+
+create  index msgCountAIx  on TweetMessages (countA) type btree;
+
+create  index msgCountBIx  on TweetMessages (countB) type btree;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.2.update.sqlpp
new file mode 100644
index 0000000..042f3ce
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.2.update.sqlpp
@@ -0,0 +1,18 @@
+/*
+ * 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.
+ */
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.3.query.sqlpp
new file mode 100644
index 0000000..7a520a3
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.3.query.sqlpp
@@ -0,0 +1,39 @@
+/*
+ * 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.
+ */
+
+/**
+ * This query is to verify the fix of ASTERIXDB-1168.
+ */
+
+use test;
+
+
+select element {
+        'tweetid1': t1.tweetid,
+        'count1': t1.countA,
+        't2info': (
+            select element {'tweetid2':t2.tweetid,'count2':t2.countB}
+            from  TweetMessages as t2
+            where t1.countA = 2 and t1.countA /*+ indexnl */  = t2.countB
+            order by t2.tweetid
+        )}
+from  TweetMessages as t1
+where (t1.tweetid < test.int64('10'))
+order by t1.tweetid
+;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/gby_inline/gby_inline.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/gby_inline/gby_inline.3.query.sqlpp
index 549377e..955aa4c 100644
--- a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/gby_inline/gby_inline.3.query.sqlpp
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/gby_inline/gby_inline.3.query.sqlpp
@@ -29,4 +29,5 @@
     WHERE cnationkey = 5 and o.o_custkey = ccustkey
     SELECT ELEMENT o
   )
-SELECT ccustkey customer_name, orders orders;
\ No newline at end of file
+SELECT ccustkey customer_name, orders orders
+ORDER BY customer_name;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.1.ddl.sqlpp
new file mode 100644
index 0000000..6e9dcd1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.1.ddl.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop database test if exists;
+create database test;
+use test;
+
+create type TType as closed
+{ id: int64, content: string }
+
+create table TData (TType) primary key id;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.2.update.sqlpp
new file mode 100644
index 0000000..9274fe6
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+use test;
+
+insert into TData ( [
+{'id':1, 'content':'a'}
+,
+{'id':2, 'content': 'b'}
+,
+{'id':3, 'content':'c'}
+]);
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.3.query.sqlpp
new file mode 100644
index 0000000..1df4dc2
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.3.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/** This query is to verify the fix for ASTERIXDB-1168. */
+
+use test;
+
+WITH ps AS ['b', 'a', 'b', 'c', 'c']
+FROM ps AS p
+SELECT p AS p, (FROM TData AS x WHERE x.content = p SELECT ELEMENT x.id) AS match
+ORDER BY p;
diff --git a/asterix-app/src/test/resources/runtimets/results/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.1.adm b/asterix-app/src/test/resources/runtimets/results/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.1.adm
new file mode 100644
index 0000000..9ad1ffa
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/subquery/decorrelate_with_unique_id/decorrelate_with_unique_id.1.adm
@@ -0,0 +1,150 @@
+{ "customer_name": 1i32, "orders": [  ] }
+{ "customer_name": 2i32, "orders": [  ] }
+{ "customer_name": 3i32, "orders": [  ] }
+{ "customer_name": 4i32, "orders": [  ] }
+{ "customer_name": 5i32, "orders": [  ] }
+{ "customer_name": 6i32, "orders": [  ] }
+{ "customer_name": 7i32, "orders": [  ] }
+{ "customer_name": 8i32, "orders": [  ] }
+{ "customer_name": 9i32, "orders": [  ] }
+{ "customer_name": 10i32, "orders": [ { "o_orderkey": 227i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 46076.46d, "o_orderdate": "1995-11-10", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000919", "o_shippriority": 0i32, "o_comment": " express instructions. slyly regul" }, { "o_orderkey": 517i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 82197.79d, "o_orderdate": "1997-04-07", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000359", "o_shippriority": 0i32, "o_comment": "slyly pending deposits cajole quickly packages. furiou" }, { "o_orderkey": 902i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 37348.62d, "o_orderdate": "1994-07-27", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000811", "o_shippriority": 0i32, "o_comment": "yly final requests over the furiously regula" }, { "o_orderkey": 1218i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 99834.47d, "o_orderdate": "1994-06-20", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000994", "o_shippriority": 0i32, "o_comment": "s cajole. special, silent deposits about the theo" }, { "o_orderkey": 1222i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 47623.94d, "o_orderdate": "1993-02-05", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000811", "o_shippriority": 0i32, "o_comment": "theodolites use quickly even accounts. carefully final asympto" }, { "o_orderkey": 1223i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 26714.67d, "o_orderdate": "1996-05-25", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000238", "o_shippriority": 0i32, "o_comment": "posits was blithely fr" }, { "o_orderkey": 1860i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 9103.4d, "o_orderdate": "1996-04-04", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000556", "o_shippriority": 0i32, "o_comment": "osits. quickly bold deposits according to " }, { "o_orderkey": 1890i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 202364.58d, "o_orderdate": "1996-12-18", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000627", "o_shippriority": 0i32, "o_comment": "romise final, regular deposits. regular fox" }, { "o_orderkey": 2562i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 136360.37d, "o_orderdate": "1992-08-01", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000467", "o_shippriority": 0i32, "o_comment": "elets. pending dolphins promise slyly. bo" }, { "o_orderkey": 3204i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 41573.42d, "o_orderdate": "1992-12-26", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000693", "o_shippriority": 0i32, "o_comment": "ess somas cajole slyly. pending accounts cajole" }, { "o_orderkey": 3428i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 88047.04d, "o_orderdate": "1996-04-07", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000953", "o_shippriority": 0i32, "o_comment": "lar excuses. slyly pending ideas detect p" }, { "o_orderkey": 3618i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 136954.81d, "o_orderdate": "1997-12-13", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000894", "o_shippriority": 0i32, "o_comment": ". ideas run carefully. thin, pending " }, { "o_orderkey": 3751i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 202917.72d, "o_orderdate": "1994-04-27", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000925", "o_shippriority": 0i32, "o_comment": "sheaves. express, unusual t" }, { "o_orderkey": 3843i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 34035.17d, "o_orderdate": "1997-01-04", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000693", "o_shippriority": 0i32, "o_comment": "eodolites; slyly unusual accounts nag boldly " }, { "o_orderkey": 3911i32, "o_custkey": 10i32, "o_orderstatus": "P", "o_totalprice": 35019.95d, "o_orderdate": "1995-03-17", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000818", "o_shippriority": 0i32, "o_comment": "he fluffily final forges haggle slyly according to the blithely" }, { "o_orderkey": 4032i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 62497.51d, "o_orderdate": "1998-02-26", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000686", "o_shippriority": 0i32, "o_comment": "iresias sleep slyly regular ideas. quickly unusual" }, { "o_orderkey": 4097i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 134308.04d, "o_orderdate": "1996-05-24", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000475", "o_shippriority": 0i32, "o_comment": "ickly under the even accounts. even packages after the furiously express" }, { "o_orderkey": 4388i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 69668.22d, "o_orderdate": "1996-03-28", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000715", "o_shippriority": 0i32, "o_comment": "ts wake against the carefully final accounts. sly" }, { "o_orderkey": 4421i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 258779.02d, "o_orderdate": "1997-04-04", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000246", "o_shippriority": 0i32, "o_comment": "t the pending warhorses. express waters a" }, { "o_orderkey": 4449i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 48206.14d, "o_orderdate": "1998-02-08", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000035", "o_shippriority": 0i32, "o_comment": "ourts are carefully even deposits. pending " }, { "o_orderkey": 4867i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 9741.03d, "o_orderdate": "1992-05-21", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000891", "o_shippriority": 0i32, "o_comment": "ss the slyly regular dependencies. fluffily regular deposits within the car" }, { "o_orderkey": 5123i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 11850.45d, "o_orderdate": "1998-02-10", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000776", "o_shippriority": 0i32, "o_comment": "ic requests. furiously ironic packages grow above the express, ironic inst" }, { "o_orderkey": 5220i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 24844.39d, "o_orderdate": "1992-07-30", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000051", "o_shippriority": 0i32, "o_comment": " final packages. ideas detect slyly around" } ] }
+{ "customer_name": 11i32, "orders": [  ] }
+{ "customer_name": 12i32, "orders": [  ] }
+{ "customer_name": 13i32, "orders": [  ] }
+{ "customer_name": 14i32, "orders": [  ] }
+{ "customer_name": 15i32, "orders": [  ] }
+{ "customer_name": 16i32, "orders": [  ] }
+{ "customer_name": 17i32, "orders": [  ] }
+{ "customer_name": 18i32, "orders": [  ] }
+{ "customer_name": 19i32, "orders": [  ] }
+{ "customer_name": 20i32, "orders": [  ] }
+{ "customer_name": 21i32, "orders": [  ] }
+{ "customer_name": 22i32, "orders": [  ] }
+{ "customer_name": 23i32, "orders": [  ] }
+{ "customer_name": 24i32, "orders": [  ] }
+{ "customer_name": 25i32, "orders": [  ] }
+{ "customer_name": 26i32, "orders": [  ] }
+{ "customer_name": 27i32, "orders": [  ] }
+{ "customer_name": 28i32, "orders": [  ] }
+{ "customer_name": 29i32, "orders": [  ] }
+{ "customer_name": 30i32, "orders": [  ] }
+{ "customer_name": 31i32, "orders": [  ] }
+{ "customer_name": 32i32, "orders": [  ] }
+{ "customer_name": 33i32, "orders": [  ] }
+{ "customer_name": 34i32, "orders": [  ] }
+{ "customer_name": 35i32, "orders": [  ] }
+{ "customer_name": 36i32, "orders": [  ] }
+{ "customer_name": 37i32, "orders": [  ] }
+{ "customer_name": 38i32, "orders": [  ] }
+{ "customer_name": 39i32, "orders": [  ] }
+{ "customer_name": 40i32, "orders": [  ] }
+{ "customer_name": 41i32, "orders": [  ] }
+{ "customer_name": 42i32, "orders": [  ] }
+{ "customer_name": 43i32, "orders": [  ] }
+{ "customer_name": 44i32, "orders": [  ] }
+{ "customer_name": 45i32, "orders": [  ] }
+{ "customer_name": 46i32, "orders": [  ] }
+{ "customer_name": 47i32, "orders": [  ] }
+{ "customer_name": 48i32, "orders": [  ] }
+{ "customer_name": 49i32, "orders": [  ] }
+{ "customer_name": 50i32, "orders": [  ] }
+{ "customer_name": 51i32, "orders": [  ] }
+{ "customer_name": 52i32, "orders": [  ] }
+{ "customer_name": 53i32, "orders": [  ] }
+{ "customer_name": 54i32, "orders": [  ] }
+{ "customer_name": 55i32, "orders": [  ] }
+{ "customer_name": 56i32, "orders": [  ] }
+{ "customer_name": 57i32, "orders": [  ] }
+{ "customer_name": 58i32, "orders": [  ] }
+{ "customer_name": 59i32, "orders": [  ] }
+{ "customer_name": 60i32, "orders": [  ] }
+{ "customer_name": 61i32, "orders": [  ] }
+{ "customer_name": 62i32, "orders": [  ] }
+{ "customer_name": 63i32, "orders": [  ] }
+{ "customer_name": 64i32, "orders": [  ] }
+{ "customer_name": 65i32, "orders": [  ] }
+{ "customer_name": 66i32, "orders": [  ] }
+{ "customer_name": 67i32, "orders": [  ] }
+{ "customer_name": 68i32, "orders": [  ] }
+{ "customer_name": 69i32, "orders": [  ] }
+{ "customer_name": 70i32, "orders": [  ] }
+{ "customer_name": 71i32, "orders": [  ] }
+{ "customer_name": 72i32, "orders": [  ] }
+{ "customer_name": 73i32, "orders": [  ] }
+{ "customer_name": 74i32, "orders": [  ] }
+{ "customer_name": 75i32, "orders": [  ] }
+{ "customer_name": 76i32, "orders": [  ] }
+{ "customer_name": 77i32, "orders": [  ] }
+{ "customer_name": 78i32, "orders": [  ] }
+{ "customer_name": 79i32, "orders": [  ] }
+{ "customer_name": 80i32, "orders": [  ] }
+{ "customer_name": 81i32, "orders": [  ] }
+{ "customer_name": 82i32, "orders": [  ] }
+{ "customer_name": 83i32, "orders": [  ] }
+{ "customer_name": 84i32, "orders": [  ] }
+{ "customer_name": 85i32, "orders": [ { "o_orderkey": 69i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 162176.23d, "o_orderdate": "1994-06-04", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000330", "o_shippriority": 0i32, "o_comment": " depths atop the slyly thin deposits detect among the furiously silent accou" }, { "o_orderkey": 704i32, "o_custkey": 85i32, "o_orderstatus": "O", "o_totalprice": 56210.26d, "o_orderdate": "1996-11-21", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000682", "o_shippriority": 0i32, "o_comment": "blithely pending platelets wake alongside of the final, iron" }, { "o_orderkey": 1699i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 66408.29d, "o_orderdate": "1993-12-30", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000125", "o_shippriority": 0i32, "o_comment": "jole blithely. furiously un" }, { "o_orderkey": 2437i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 143411.69d, "o_orderdate": "1993-04-21", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000578", "o_shippriority": 0i32, "o_comment": ". theodolites wake slyly-- ironic, pending platelets above the carefully exp" }, { "o_orderkey": 2630i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 127132.51d, "o_orderdate": "1992-10-24", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000712", "o_shippriority": 0i32, "o_comment": "inal theodolites. ironic instructions s" }, { "o_orderkey": 2982i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 55582.94d, "o_orderdate": "1995-03-19", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000402", "o_shippriority": 0i32, "o_comment": "lyly. express theodolites affix slyly after the slyly speci" }, { "o_orderkey": 3108i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 63278.0d, "o_orderdate": "1993-08-05", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000574", "o_shippriority": 0i32, "o_comment": "s packages haggle furiously am" }, { "o_orderkey": 3776i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 150349.92d, "o_orderdate": "1992-11-20", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000698", "o_shippriority": 0i32, "o_comment": "efully even platelets slee" }, { "o_orderkey": 4480i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 28658.26d, "o_orderdate": "1994-03-31", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000534", "o_shippriority": 0i32, "o_comment": "press, bold deposits boost blit" }, { "o_orderkey": 4513i32, "o_custkey": 85i32, "o_orderstatus": "O", "o_totalprice": 119820.38d, "o_orderdate": "1996-03-15", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000154", "o_shippriority": 0i32, "o_comment": "ests. final, final ideas" }, { "o_orderkey": 4708i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 56998.36d, "o_orderdate": "1994-10-01", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000383", "o_shippriority": 0i32, "o_comment": "ly thinly even accounts. unusu" }, { "o_orderkey": 4865i32, "o_custkey": 85i32, "o_orderstatus": "O", "o_totalprice": 162113.46d, "o_orderdate": "1997-06-07", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000418", "o_shippriority": 0i32, "o_comment": "sits boost stealthily above the bl" }, { "o_orderkey": 4896i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 93206.35d, "o_orderdate": "1992-08-22", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000622", "o_shippriority": 0i32, "o_comment": "sly pending deposits. final accounts boost above the sly, even" }, { "o_orderkey": 4999i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 98643.17d, "o_orderdate": "1993-06-26", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000504", "o_shippriority": 0i32, "o_comment": " dolphins cajole blithely above the sly " }, { "o_orderkey": 5184i32, "o_custkey": 85i32, "o_orderstatus": "O", "o_totalprice": 209155.48d, "o_orderdate": "1998-07-20", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000250", "o_shippriority": 0i32, "o_comment": "nding accounts detect final, even" }, { "o_orderkey": 5830i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 28223.57d, "o_orderdate": "1993-03-25", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000233", "o_shippriority": 0i32, "o_comment": "lites haggle. ironic, ironic instructions maintain blit" } ] }
+{ "customer_name": 86i32, "orders": [  ] }
+{ "customer_name": 87i32, "orders": [  ] }
+{ "customer_name": 88i32, "orders": [  ] }
+{ "customer_name": 89i32, "orders": [  ] }
+{ "customer_name": 90i32, "orders": [  ] }
+{ "customer_name": 91i32, "orders": [  ] }
+{ "customer_name": 92i32, "orders": [  ] }
+{ "customer_name": 93i32, "orders": [  ] }
+{ "customer_name": 94i32, "orders": [  ] }
+{ "customer_name": 95i32, "orders": [  ] }
+{ "customer_name": 96i32, "orders": [  ] }
+{ "customer_name": 97i32, "orders": [  ] }
+{ "customer_name": 98i32, "orders": [  ] }
+{ "customer_name": 99i32, "orders": [  ] }
+{ "customer_name": 100i32, "orders": [  ] }
+{ "customer_name": 101i32, "orders": [  ] }
+{ "customer_name": 102i32, "orders": [  ] }
+{ "customer_name": 103i32, "orders": [  ] }
+{ "customer_name": 104i32, "orders": [  ] }
+{ "customer_name": 105i32, "orders": [  ] }
+{ "customer_name": 106i32, "orders": [  ] }
+{ "customer_name": 107i32, "orders": [  ] }
+{ "customer_name": 108i32, "orders": [  ] }
+{ "customer_name": 109i32, "orders": [  ] }
+{ "customer_name": 110i32, "orders": [  ] }
+{ "customer_name": 111i32, "orders": [  ] }
+{ "customer_name": 112i32, "orders": [  ] }
+{ "customer_name": 113i32, "orders": [  ] }
+{ "customer_name": 114i32, "orders": [  ] }
+{ "customer_name": 115i32, "orders": [  ] }
+{ "customer_name": 116i32, "orders": [  ] }
+{ "customer_name": 117i32, "orders": [  ] }
+{ "customer_name": 118i32, "orders": [  ] }
+{ "customer_name": 119i32, "orders": [  ] }
+{ "customer_name": 120i32, "orders": [  ] }
+{ "customer_name": 121i32, "orders": [  ] }
+{ "customer_name": 122i32, "orders": [  ] }
+{ "customer_name": 123i32, "orders": [  ] }
+{ "customer_name": 124i32, "orders": [  ] }
+{ "customer_name": 125i32, "orders": [  ] }
+{ "customer_name": 126i32, "orders": [  ] }
+{ "customer_name": 127i32, "orders": [  ] }
+{ "customer_name": 128i32, "orders": [  ] }
+{ "customer_name": 129i32, "orders": [  ] }
+{ "customer_name": 130i32, "orders": [  ] }
+{ "customer_name": 131i32, "orders": [  ] }
+{ "customer_name": 132i32, "orders": [  ] }
+{ "customer_name": 133i32, "orders": [  ] }
+{ "customer_name": 134i32, "orders": [  ] }
+{ "customer_name": 135i32, "orders": [  ] }
+{ "customer_name": 136i32, "orders": [  ] }
+{ "customer_name": 137i32, "orders": [  ] }
+{ "customer_name": 138i32, "orders": [  ] }
+{ "customer_name": 139i32, "orders": [  ] }
+{ "customer_name": 140i32, "orders": [  ] }
+{ "customer_name": 141i32, "orders": [  ] }
+{ "customer_name": 142i32, "orders": [  ] }
+{ "customer_name": 143i32, "orders": [  ] }
+{ "customer_name": 144i32, "orders": [  ] }
+{ "customer_name": 145i32, "orders": [  ] }
+{ "customer_name": 146i32, "orders": [  ] }
+{ "customer_name": 147i32, "orders": [  ] }
+{ "customer_name": 148i32, "orders": [  ] }
+{ "customer_name": 149i32, "orders": [  ] }
+{ "customer_name": 150i32, "orders": [  ] }
diff --git a/asterix-app/src/test/resources/runtimets/results/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.1.adm b/asterix-app/src/test/resources/runtimets/results/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.1.adm
new file mode 100644
index 0000000..e4ab16f
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/subquery/decorrelate_with_unique_id_2/decorrelate_with_unique_id_2.1.adm
@@ -0,0 +1,9 @@
+{ "tweetid1": 1, "count1": 1, "t2info": [  ] }
+{ "tweetid1": 2, "count1": 2, "t2info": [ { "tweetid2": 60, "count2": 2 } ] }
+{ "tweetid1": 3, "count1": 3, "t2info": [  ] }
+{ "tweetid1": 4, "count1": 4, "t2info": [  ] }
+{ "tweetid1": 5, "count1": 5, "t2info": [  ] }
+{ "tweetid1": 6, "count1": 6, "t2info": [  ] }
+{ "tweetid1": 7, "count1": 7, "t2info": [  ] }
+{ "tweetid1": 8, "count1": 8, "t2info": [  ] }
+{ "tweetid1": 9, "count1": 9, "t2info": [  ] }
diff --git a/asterix-app/src/test/resources/runtimets/results/subquery/gby_inline/gby_inline.1.adm b/asterix-app/src/test/resources/runtimets/results/subquery/gby_inline/gby_inline.1.adm
index f90f5db..9ad1ffa 100644
--- a/asterix-app/src/test/resources/runtimets/results/subquery/gby_inline/gby_inline.1.adm
+++ b/asterix-app/src/test/resources/runtimets/results/subquery/gby_inline/gby_inline.1.adm
@@ -1,150 +1,150 @@
-{ "customer_name": 6i32, "orders": [  ] }
-{ "customer_name": 11i32, "orders": [  ] }
-{ "customer_name": 12i32, "orders": [  ] }
-{ "customer_name": 14i32, "orders": [  ] }
-{ "customer_name": 21i32, "orders": [  ] }
-{ "customer_name": 23i32, "orders": [  ] }
-{ "customer_name": 26i32, "orders": [  ] }
-{ "customer_name": 30i32, "orders": [  ] }
-{ "customer_name": 33i32, "orders": [  ] }
-{ "customer_name": 38i32, "orders": [  ] }
-{ "customer_name": 45i32, "orders": [  ] }
-{ "customer_name": 47i32, "orders": [  ] }
-{ "customer_name": 49i32, "orders": [  ] }
-{ "customer_name": 51i32, "orders": [  ] }
-{ "customer_name": 58i32, "orders": [  ] }
-{ "customer_name": 60i32, "orders": [  ] }
-{ "customer_name": 70i32, "orders": [  ] }
-{ "customer_name": 72i32, "orders": [  ] }
-{ "customer_name": 77i32, "orders": [  ] }
-{ "customer_name": 88i32, "orders": [  ] }
-{ "customer_name": 89i32, "orders": [  ] }
-{ "customer_name": 92i32, "orders": [  ] }
-{ "customer_name": 93i32, "orders": [  ] }
-{ "customer_name": 103i32, "orders": [  ] }
-{ "customer_name": 105i32, "orders": [  ] }
-{ "customer_name": 109i32, "orders": [  ] }
-{ "customer_name": 115i32, "orders": [  ] }
-{ "customer_name": 118i32, "orders": [  ] }
-{ "customer_name": 125i32, "orders": [  ] }
-{ "customer_name": 126i32, "orders": [  ] }
-{ "customer_name": 135i32, "orders": [  ] }
-{ "customer_name": 138i32, "orders": [  ] }
-{ "customer_name": 141i32, "orders": [  ] }
-{ "customer_name": 147i32, "orders": [  ] }
 { "customer_name": 1i32, "orders": [  ] }
 { "customer_name": 2i32, "orders": [  ] }
+{ "customer_name": 3i32, "orders": [  ] }
 { "customer_name": 4i32, "orders": [  ] }
+{ "customer_name": 5i32, "orders": [  ] }
+{ "customer_name": 6i32, "orders": [  ] }
+{ "customer_name": 7i32, "orders": [  ] }
+{ "customer_name": 8i32, "orders": [  ] }
+{ "customer_name": 9i32, "orders": [  ] }
+{ "customer_name": 10i32, "orders": [ { "o_orderkey": 227i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 46076.46d, "o_orderdate": "1995-11-10", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000919", "o_shippriority": 0i32, "o_comment": " express instructions. slyly regul" }, { "o_orderkey": 517i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 82197.79d, "o_orderdate": "1997-04-07", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000359", "o_shippriority": 0i32, "o_comment": "slyly pending deposits cajole quickly packages. furiou" }, { "o_orderkey": 902i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 37348.62d, "o_orderdate": "1994-07-27", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000811", "o_shippriority": 0i32, "o_comment": "yly final requests over the furiously regula" }, { "o_orderkey": 1218i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 99834.47d, "o_orderdate": "1994-06-20", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000994", "o_shippriority": 0i32, "o_comment": "s cajole. special, silent deposits about the theo" }, { "o_orderkey": 1222i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 47623.94d, "o_orderdate": "1993-02-05", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000811", "o_shippriority": 0i32, "o_comment": "theodolites use quickly even accounts. carefully final asympto" }, { "o_orderkey": 1223i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 26714.67d, "o_orderdate": "1996-05-25", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000238", "o_shippriority": 0i32, "o_comment": "posits was blithely fr" }, { "o_orderkey": 1860i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 9103.4d, "o_orderdate": "1996-04-04", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000556", "o_shippriority": 0i32, "o_comment": "osits. quickly bold deposits according to " }, { "o_orderkey": 1890i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 202364.58d, "o_orderdate": "1996-12-18", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000627", "o_shippriority": 0i32, "o_comment": "romise final, regular deposits. regular fox" }, { "o_orderkey": 2562i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 136360.37d, "o_orderdate": "1992-08-01", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000467", "o_shippriority": 0i32, "o_comment": "elets. pending dolphins promise slyly. bo" }, { "o_orderkey": 3204i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 41573.42d, "o_orderdate": "1992-12-26", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000693", "o_shippriority": 0i32, "o_comment": "ess somas cajole slyly. pending accounts cajole" }, { "o_orderkey": 3428i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 88047.04d, "o_orderdate": "1996-04-07", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000953", "o_shippriority": 0i32, "o_comment": "lar excuses. slyly pending ideas detect p" }, { "o_orderkey": 3618i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 136954.81d, "o_orderdate": "1997-12-13", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000894", "o_shippriority": 0i32, "o_comment": ". ideas run carefully. thin, pending " }, { "o_orderkey": 3751i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 202917.72d, "o_orderdate": "1994-04-27", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000925", "o_shippriority": 0i32, "o_comment": "sheaves. express, unusual t" }, { "o_orderkey": 3843i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 34035.17d, "o_orderdate": "1997-01-04", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000693", "o_shippriority": 0i32, "o_comment": "eodolites; slyly unusual accounts nag boldly " }, { "o_orderkey": 3911i32, "o_custkey": 10i32, "o_orderstatus": "P", "o_totalprice": 35019.95d, "o_orderdate": "1995-03-17", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000818", "o_shippriority": 0i32, "o_comment": "he fluffily final forges haggle slyly according to the blithely" }, { "o_orderkey": 4032i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 62497.51d, "o_orderdate": "1998-02-26", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000686", "o_shippriority": 0i32, "o_comment": "iresias sleep slyly regular ideas. quickly unusual" }, { "o_orderkey": 4097i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 134308.04d, "o_orderdate": "1996-05-24", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000475", "o_shippriority": 0i32, "o_comment": "ickly under the even accounts. even packages after the furiously express" }, { "o_orderkey": 4388i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 69668.22d, "o_orderdate": "1996-03-28", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000715", "o_shippriority": 0i32, "o_comment": "ts wake against the carefully final accounts. sly" }, { "o_orderkey": 4421i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 258779.02d, "o_orderdate": "1997-04-04", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000246", "o_shippriority": 0i32, "o_comment": "t the pending warhorses. express waters a" }, { "o_orderkey": 4449i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 48206.14d, "o_orderdate": "1998-02-08", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000035", "o_shippriority": 0i32, "o_comment": "ourts are carefully even deposits. pending " }, { "o_orderkey": 4867i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 9741.03d, "o_orderdate": "1992-05-21", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000891", "o_shippriority": 0i32, "o_comment": "ss the slyly regular dependencies. fluffily regular deposits within the car" }, { "o_orderkey": 5123i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 11850.45d, "o_orderdate": "1998-02-10", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000776", "o_shippriority": 0i32, "o_comment": "ic requests. furiously ironic packages grow above the express, ironic inst" }, { "o_orderkey": 5220i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 24844.39d, "o_orderdate": "1992-07-30", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000051", "o_shippriority": 0i32, "o_comment": " final packages. ideas detect slyly around" } ] }
+{ "customer_name": 11i32, "orders": [  ] }
+{ "customer_name": 12i32, "orders": [  ] }
 { "customer_name": 13i32, "orders": [  ] }
+{ "customer_name": 14i32, "orders": [  ] }
 { "customer_name": 15i32, "orders": [  ] }
 { "customer_name": 16i32, "orders": [  ] }
+{ "customer_name": 17i32, "orders": [  ] }
+{ "customer_name": 18i32, "orders": [  ] }
 { "customer_name": 19i32, "orders": [  ] }
 { "customer_name": 20i32, "orders": [  ] }
+{ "customer_name": 21i32, "orders": [  ] }
 { "customer_name": 22i32, "orders": [  ] }
+{ "customer_name": 23i32, "orders": [  ] }
 { "customer_name": 24i32, "orders": [  ] }
+{ "customer_name": 25i32, "orders": [  ] }
+{ "customer_name": 26i32, "orders": [  ] }
+{ "customer_name": 27i32, "orders": [  ] }
+{ "customer_name": 28i32, "orders": [  ] }
 { "customer_name": 29i32, "orders": [  ] }
+{ "customer_name": 30i32, "orders": [  ] }
 { "customer_name": 31i32, "orders": [  ] }
+{ "customer_name": 32i32, "orders": [  ] }
+{ "customer_name": 33i32, "orders": [  ] }
+{ "customer_name": 34i32, "orders": [  ] }
 { "customer_name": 35i32, "orders": [  ] }
+{ "customer_name": 36i32, "orders": [  ] }
+{ "customer_name": 37i32, "orders": [  ] }
+{ "customer_name": 38i32, "orders": [  ] }
+{ "customer_name": 39i32, "orders": [  ] }
+{ "customer_name": 40i32, "orders": [  ] }
+{ "customer_name": 41i32, "orders": [  ] }
+{ "customer_name": 42i32, "orders": [  ] }
+{ "customer_name": 43i32, "orders": [  ] }
+{ "customer_name": 44i32, "orders": [  ] }
+{ "customer_name": 45i32, "orders": [  ] }
 { "customer_name": 46i32, "orders": [  ] }
+{ "customer_name": 47i32, "orders": [  ] }
 { "customer_name": 48i32, "orders": [  ] }
+{ "customer_name": 49i32, "orders": [  ] }
+{ "customer_name": 50i32, "orders": [  ] }
+{ "customer_name": 51i32, "orders": [  ] }
 { "customer_name": 52i32, "orders": [  ] }
+{ "customer_name": 53i32, "orders": [  ] }
+{ "customer_name": 54i32, "orders": [  ] }
 { "customer_name": 55i32, "orders": [  ] }
 { "customer_name": 56i32, "orders": [  ] }
 { "customer_name": 57i32, "orders": [  ] }
+{ "customer_name": 58i32, "orders": [  ] }
 { "customer_name": 59i32, "orders": [  ] }
+{ "customer_name": 60i32, "orders": [  ] }
+{ "customer_name": 61i32, "orders": [  ] }
 { "customer_name": 62i32, "orders": [  ] }
 { "customer_name": 63i32, "orders": [  ] }
 { "customer_name": 64i32, "orders": [  ] }
 { "customer_name": 65i32, "orders": [  ] }
-{ "customer_name": 68i32, "orders": [  ] }
-{ "customer_name": 71i32, "orders": [  ] }
-{ "customer_name": 73i32, "orders": [  ] }
-{ "customer_name": 75i32, "orders": [  ] }
-{ "customer_name": 81i32, "orders": [  ] }
-{ "customer_name": 91i32, "orders": [  ] }
-{ "customer_name": 97i32, "orders": [  ] }
-{ "customer_name": 98i32, "orders": [  ] }
-{ "customer_name": 100i32, "orders": [  ] }
-{ "customer_name": 106i32, "orders": [  ] }
-{ "customer_name": 114i32, "orders": [  ] }
-{ "customer_name": 119i32, "orders": [  ] }
-{ "customer_name": 121i32, "orders": [  ] }
-{ "customer_name": 122i32, "orders": [  ] }
-{ "customer_name": 127i32, "orders": [  ] }
-{ "customer_name": 129i32, "orders": [  ] }
-{ "customer_name": 130i32, "orders": [  ] }
-{ "customer_name": 131i32, "orders": [  ] }
-{ "customer_name": 140i32, "orders": [  ] }
-{ "customer_name": 143i32, "orders": [  ] }
-{ "customer_name": 144i32, "orders": [  ] }
-{ "customer_name": 149i32, "orders": [  ] }
-{ "customer_name": 8i32, "orders": [  ] }
-{ "customer_name": 9i32, "orders": [  ] }
-{ "customer_name": 10i32, "orders": [ { "o_orderkey": 227i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 46076.46d, "o_orderdate": "1995-11-10", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000919", "o_shippriority": 0i32, "o_comment": " express instructions. slyly regul" }, { "o_orderkey": 517i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 82197.79d, "o_orderdate": "1997-04-07", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000359", "o_shippriority": 0i32, "o_comment": "slyly pending deposits cajole quickly packages. furiou" }, { "o_orderkey": 902i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 37348.62d, "o_orderdate": "1994-07-27", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000811", "o_shippriority": 0i32, "o_comment": "yly final requests over the furiously regula" }, { "o_orderkey": 1218i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 99834.47d, "o_orderdate": "1994-06-20", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000994", "o_shippriority": 0i32, "o_comment": "s cajole. special, silent deposits about the theo" }, { "o_orderkey": 1222i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 47623.94d, "o_orderdate": "1993-02-05", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000811", "o_shippriority": 0i32, "o_comment": "theodolites use quickly even accounts. carefully final asympto" }, { "o_orderkey": 1223i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 26714.67d, "o_orderdate": "1996-05-25", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000238", "o_shippriority": 0i32, "o_comment": "posits was blithely fr" }, { "o_orderkey": 1860i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 9103.4d, "o_orderdate": "1996-04-04", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000556", "o_shippriority": 0i32, "o_comment": "osits. quickly bold deposits according to " }, { "o_orderkey": 1890i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 202364.58d, "o_orderdate": "1996-12-18", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000627", "o_shippriority": 0i32, "o_comment": "romise final, regular deposits. regular fox" }, { "o_orderkey": 2562i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 136360.37d, "o_orderdate": "1992-08-01", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000467", "o_shippriority": 0i32, "o_comment": "elets. pending dolphins promise slyly. bo" }, { "o_orderkey": 3204i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 41573.42d, "o_orderdate": "1992-12-26", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000693", "o_shippriority": 0i32, "o_comment": "ess somas cajole slyly. pending accounts cajole" }, { "o_orderkey": 3428i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 88047.04d, "o_orderdate": "1996-04-07", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000953", "o_shippriority": 0i32, "o_comment": "lar excuses. slyly pending ideas detect p" }, { "o_orderkey": 3618i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 136954.81d, "o_orderdate": "1997-12-13", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000894", "o_shippriority": 0i32, "o_comment": ". ideas run carefully. thin, pending " }, { "o_orderkey": 3751i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 202917.72d, "o_orderdate": "1994-04-27", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000925", "o_shippriority": 0i32, "o_comment": "sheaves. express, unusual t" }, { "o_orderkey": 3843i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 34035.17d, "o_orderdate": "1997-01-04", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000693", "o_shippriority": 0i32, "o_comment": "eodolites; slyly unusual accounts nag boldly " }, { "o_orderkey": 3911i32, "o_custkey": 10i32, "o_orderstatus": "P", "o_totalprice": 35019.95d, "o_orderdate": "1995-03-17", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000818", "o_shippriority": 0i32, "o_comment": "he fluffily final forges haggle slyly according to the blithely" }, { "o_orderkey": 4032i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 62497.51d, "o_orderdate": "1998-02-26", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000686", "o_shippriority": 0i32, "o_comment": "iresias sleep slyly regular ideas. quickly unusual" }, { "o_orderkey": 4097i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 134308.04d, "o_orderdate": "1996-05-24", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000475", "o_shippriority": 0i32, "o_comment": "ickly under the even accounts. even packages after the furiously express" }, { "o_orderkey": 4388i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 69668.22d, "o_orderdate": "1996-03-28", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000715", "o_shippriority": 0i32, "o_comment": "ts wake against the carefully final accounts. sly" }, { "o_orderkey": 4421i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 258779.02d, "o_orderdate": "1997-04-04", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000246", "o_shippriority": 0i32, "o_comment": "t the pending warhorses. express waters a" }, { "o_orderkey": 4449i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 48206.14d, "o_orderdate": "1998-02-08", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000035", "o_shippriority": 0i32, "o_comment": "ourts are carefully even deposits. pending " }, { "o_orderkey": 4867i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 9741.03d, "o_orderdate": "1992-05-21", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000891", "o_shippriority": 0i32, "o_comment": "ss the slyly regular dependencies. fluffily regular deposits within the car" }, { "o_orderkey": 5123i32, "o_custkey": 10i32, "o_orderstatus": "O", "o_totalprice": 11850.45d, "o_orderdate": "1998-02-10", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000776", "o_shippriority": 0i32, "o_comment": "ic requests. furiously ironic packages grow above the express, ironic inst" }, { "o_orderkey": 5220i32, "o_custkey": 10i32, "o_orderstatus": "F", "o_totalprice": 24844.39d, "o_orderdate": "1992-07-30", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000051", "o_shippriority": 0i32, "o_comment": " final packages. ideas detect slyly around" } ] }
-{ "customer_name": 25i32, "orders": [  ] }
-{ "customer_name": 28i32, "orders": [  ] }
-{ "customer_name": 34i32, "orders": [  ] }
-{ "customer_name": 37i32, "orders": [  ] }
-{ "customer_name": 40i32, "orders": [  ] }
-{ "customer_name": 41i32, "orders": [  ] }
-{ "customer_name": 43i32, "orders": [  ] }
 { "customer_name": 66i32, "orders": [  ] }
 { "customer_name": 67i32, "orders": [  ] }
+{ "customer_name": 68i32, "orders": [  ] }
 { "customer_name": 69i32, "orders": [  ] }
+{ "customer_name": 70i32, "orders": [  ] }
+{ "customer_name": 71i32, "orders": [  ] }
+{ "customer_name": 72i32, "orders": [  ] }
+{ "customer_name": 73i32, "orders": [  ] }
+{ "customer_name": 74i32, "orders": [  ] }
+{ "customer_name": 75i32, "orders": [  ] }
 { "customer_name": 76i32, "orders": [  ] }
+{ "customer_name": 77i32, "orders": [  ] }
+{ "customer_name": 78i32, "orders": [  ] }
 { "customer_name": 79i32, "orders": [  ] }
+{ "customer_name": 80i32, "orders": [  ] }
+{ "customer_name": 81i32, "orders": [  ] }
+{ "customer_name": 82i32, "orders": [  ] }
+{ "customer_name": 83i32, "orders": [  ] }
 { "customer_name": 84i32, "orders": [  ] }
 { "customer_name": 85i32, "orders": [ { "o_orderkey": 69i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 162176.23d, "o_orderdate": "1994-06-04", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000330", "o_shippriority": 0i32, "o_comment": " depths atop the slyly thin deposits detect among the furiously silent accou" }, { "o_orderkey": 704i32, "o_custkey": 85i32, "o_orderstatus": "O", "o_totalprice": 56210.26d, "o_orderdate": "1996-11-21", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000682", "o_shippriority": 0i32, "o_comment": "blithely pending platelets wake alongside of the final, iron" }, { "o_orderkey": 1699i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 66408.29d, "o_orderdate": "1993-12-30", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000125", "o_shippriority": 0i32, "o_comment": "jole blithely. furiously un" }, { "o_orderkey": 2437i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 143411.69d, "o_orderdate": "1993-04-21", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000578", "o_shippriority": 0i32, "o_comment": ". theodolites wake slyly-- ironic, pending platelets above the carefully exp" }, { "o_orderkey": 2630i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 127132.51d, "o_orderdate": "1992-10-24", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000712", "o_shippriority": 0i32, "o_comment": "inal theodolites. ironic instructions s" }, { "o_orderkey": 2982i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 55582.94d, "o_orderdate": "1995-03-19", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000402", "o_shippriority": 0i32, "o_comment": "lyly. express theodolites affix slyly after the slyly speci" }, { "o_orderkey": 3108i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 63278.0d, "o_orderdate": "1993-08-05", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000574", "o_shippriority": 0i32, "o_comment": "s packages haggle furiously am" }, { "o_orderkey": 3776i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 150349.92d, "o_orderdate": "1992-11-20", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000698", "o_shippriority": 0i32, "o_comment": "efully even platelets slee" }, { "o_orderkey": 4480i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 28658.26d, "o_orderdate": "1994-03-31", "o_orderpriority": "4-NOT SPECIFIED", "o_clerk": "Clerk#000000534", "o_shippriority": 0i32, "o_comment": "press, bold deposits boost blit" }, { "o_orderkey": 4513i32, "o_custkey": 85i32, "o_orderstatus": "O", "o_totalprice": 119820.38d, "o_orderdate": "1996-03-15", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000154", "o_shippriority": 0i32, "o_comment": "ests. final, final ideas" }, { "o_orderkey": 4708i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 56998.36d, "o_orderdate": "1994-10-01", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000383", "o_shippriority": 0i32, "o_comment": "ly thinly even accounts. unusu" }, { "o_orderkey": 4865i32, "o_custkey": 85i32, "o_orderstatus": "O", "o_totalprice": 162113.46d, "o_orderdate": "1997-06-07", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000418", "o_shippriority": 0i32, "o_comment": "sits boost stealthily above the bl" }, { "o_orderkey": 4896i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 93206.35d, "o_orderdate": "1992-08-22", "o_orderpriority": "1-URGENT", "o_clerk": "Clerk#000000622", "o_shippriority": 0i32, "o_comment": "sly pending deposits. final accounts boost above the sly, even" }, { "o_orderkey": 4999i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 98643.17d, "o_orderdate": "1993-06-26", "o_orderpriority": "2-HIGH", "o_clerk": "Clerk#000000504", "o_shippriority": 0i32, "o_comment": " dolphins cajole blithely above the sly " }, { "o_orderkey": 5184i32, "o_custkey": 85i32, "o_orderstatus": "O", "o_totalprice": 209155.48d, "o_orderdate": "1998-07-20", "o_orderpriority": "5-LOW", "o_clerk": "Clerk#000000250", "o_shippriority": 0i32, "o_comment": "nding accounts detect final, even" }, { "o_orderkey": 5830i32, "o_custkey": 85i32, "o_orderstatus": "F", "o_totalprice": 28223.57d, "o_orderdate": "1993-03-25", "o_orderpriority": "3-MEDIUM", "o_clerk": "Clerk#000000233", "o_shippriority": 0i32, "o_comment": "lites haggle. ironic, ironic instructions maintain blit" } ] }
 { "customer_name": 86i32, "orders": [  ] }
+{ "customer_name": 87i32, "orders": [  ] }
+{ "customer_name": 88i32, "orders": [  ] }
+{ "customer_name": 89i32, "orders": [  ] }
+{ "customer_name": 90i32, "orders": [  ] }
+{ "customer_name": 91i32, "orders": [  ] }
+{ "customer_name": 92i32, "orders": [  ] }
+{ "customer_name": 93i32, "orders": [  ] }
 { "customer_name": 94i32, "orders": [  ] }
 { "customer_name": 95i32, "orders": [  ] }
 { "customer_name": 96i32, "orders": [  ] }
+{ "customer_name": 97i32, "orders": [  ] }
+{ "customer_name": 98i32, "orders": [  ] }
 { "customer_name": 99i32, "orders": [  ] }
+{ "customer_name": 100i32, "orders": [  ] }
+{ "customer_name": 101i32, "orders": [  ] }
 { "customer_name": 102i32, "orders": [  ] }
+{ "customer_name": 103i32, "orders": [  ] }
+{ "customer_name": 104i32, "orders": [  ] }
+{ "customer_name": 105i32, "orders": [  ] }
+{ "customer_name": 106i32, "orders": [  ] }
+{ "customer_name": 107i32, "orders": [  ] }
 { "customer_name": 108i32, "orders": [  ] }
+{ "customer_name": 109i32, "orders": [  ] }
+{ "customer_name": 110i32, "orders": [  ] }
 { "customer_name": 111i32, "orders": [  ] }
+{ "customer_name": 112i32, "orders": [  ] }
 { "customer_name": 113i32, "orders": [  ] }
+{ "customer_name": 114i32, "orders": [  ] }
+{ "customer_name": 115i32, "orders": [  ] }
 { "customer_name": 116i32, "orders": [  ] }
+{ "customer_name": 117i32, "orders": [  ] }
+{ "customer_name": 118i32, "orders": [  ] }
+{ "customer_name": 119i32, "orders": [  ] }
+{ "customer_name": 120i32, "orders": [  ] }
+{ "customer_name": 121i32, "orders": [  ] }
+{ "customer_name": 122i32, "orders": [  ] }
+{ "customer_name": 123i32, "orders": [  ] }
 { "customer_name": 124i32, "orders": [  ] }
+{ "customer_name": 125i32, "orders": [  ] }
+{ "customer_name": 126i32, "orders": [  ] }
+{ "customer_name": 127i32, "orders": [  ] }
 { "customer_name": 128i32, "orders": [  ] }
+{ "customer_name": 129i32, "orders": [  ] }
+{ "customer_name": 130i32, "orders": [  ] }
+{ "customer_name": 131i32, "orders": [  ] }
 { "customer_name": 132i32, "orders": [  ] }
 { "customer_name": 133i32, "orders": [  ] }
 { "customer_name": 134i32, "orders": [  ] }
+{ "customer_name": 135i32, "orders": [  ] }
 { "customer_name": 136i32, "orders": [  ] }
-{ "customer_name": 139i32, "orders": [  ] }
-{ "customer_name": 150i32, "orders": [  ] }
-{ "customer_name": 3i32, "orders": [  ] }
-{ "customer_name": 5i32, "orders": [  ] }
-{ "customer_name": 7i32, "orders": [  ] }
-{ "customer_name": 17i32, "orders": [  ] }
-{ "customer_name": 18i32, "orders": [  ] }
-{ "customer_name": 27i32, "orders": [  ] }
-{ "customer_name": 32i32, "orders": [  ] }
-{ "customer_name": 36i32, "orders": [  ] }
-{ "customer_name": 39i32, "orders": [  ] }
-{ "customer_name": 42i32, "orders": [  ] }
-{ "customer_name": 44i32, "orders": [  ] }
-{ "customer_name": 50i32, "orders": [  ] }
-{ "customer_name": 53i32, "orders": [  ] }
-{ "customer_name": 54i32, "orders": [  ] }
-{ "customer_name": 61i32, "orders": [  ] }
-{ "customer_name": 74i32, "orders": [  ] }
-{ "customer_name": 78i32, "orders": [  ] }
-{ "customer_name": 80i32, "orders": [  ] }
-{ "customer_name": 82i32, "orders": [  ] }
-{ "customer_name": 83i32, "orders": [  ] }
-{ "customer_name": 87i32, "orders": [  ] }
-{ "customer_name": 90i32, "orders": [  ] }
-{ "customer_name": 101i32, "orders": [  ] }
-{ "customer_name": 104i32, "orders": [  ] }
-{ "customer_name": 107i32, "orders": [  ] }
-{ "customer_name": 110i32, "orders": [  ] }
-{ "customer_name": 112i32, "orders": [  ] }
-{ "customer_name": 117i32, "orders": [  ] }
-{ "customer_name": 120i32, "orders": [  ] }
-{ "customer_name": 123i32, "orders": [  ] }
 { "customer_name": 137i32, "orders": [  ] }
+{ "customer_name": 138i32, "orders": [  ] }
+{ "customer_name": 139i32, "orders": [  ] }
+{ "customer_name": 140i32, "orders": [  ] }
+{ "customer_name": 141i32, "orders": [  ] }
 { "customer_name": 142i32, "orders": [  ] }
+{ "customer_name": 143i32, "orders": [  ] }
+{ "customer_name": 144i32, "orders": [  ] }
 { "customer_name": 145i32, "orders": [  ] }
 { "customer_name": 146i32, "orders": [  ] }
+{ "customer_name": 147i32, "orders": [  ] }
 { "customer_name": 148i32, "orders": [  ] }
+{ "customer_name": 149i32, "orders": [  ] }
+{ "customer_name": 150i32, "orders": [  ] }
diff --git a/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.1.adm b/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.1.adm
new file mode 100644
index 0000000..8bdf5fb
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-1168/query-ASTERIXDB-1168.1.adm
@@ -0,0 +1,5 @@
+{ "p": "a", "match": [ 1 ] }
+{ "p": "b", "match": [ 2 ] }
+{ "p": "b", "match": [ 2 ] }
+{ "p": "c", "match": [ 3 ] }
+{ "p": "c", "match": [ 3 ] }
diff --git a/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index c7caed6..985cbcd 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -4973,6 +4973,21 @@
                 <output-dir compare="Text">gby_inline</output-dir>
             </compilation-unit>
         </test-case>
+        <test-case FilePath="subquery">
+            <compilation-unit name="query-ASTERIXDB-1168">
+                <output-dir compare="Text">query-ASTERIXDB-1168</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="subquery">
+            <compilation-unit name="decorrelate_with_unique_id">
+                <output-dir compare="Text">decorrelate_with_unique_id</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="subquery">
+            <compilation-unit name="decorrelate_with_unique_id_2">
+                <output-dir compare="Text">decorrelate_with_unique_id_2</output-dir>
+            </compilation-unit>
+        </test-case>
     </test-group>
     <test-group name="subset-collection">
         <test-case FilePath="subset-collection">
diff --git a/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/AqlDataSource.java b/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/AqlDataSource.java
index dcd10b7..e2605ec 100644
--- a/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/AqlDataSource.java
+++ b/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/AqlDataSource.java
@@ -225,4 +225,12 @@
         }
     }
 
+    public List<LogicalVariable> getPrimaryKeyVariables(List<LogicalVariable> dataScanVariables) {
+        if (hasMeta()) {
+            return new ArrayList<>(dataScanVariables.subList(0, dataScanVariables.size() - 2));
+        } else {
+            return new ArrayList<>(dataScanVariables.subList(0, dataScanVariables.size() - 1));
+        }
+    }
+
 }