diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
index ae0b2f6..ad5283a 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
@@ -48,7 +48,6 @@
 import org.apache.asterix.optimizer.rules.IntroduceRapidFrameFlushProjectAssignRule;
 import org.apache.asterix.optimizer.rules.IntroduceSecondaryIndexInsertDeleteRule;
 import org.apache.asterix.optimizer.rules.IntroduceStaticTypeCastForInsertRule;
-import org.apache.asterix.optimizer.rules.IntroduceUnionRule;
 import org.apache.asterix.optimizer.rules.IntroduceUnnestForCollectionToSequenceRule;
 import org.apache.asterix.optimizer.rules.LoadRecordFieldsRule;
 import org.apache.asterix.optimizer.rules.NestGroupByRule;
@@ -78,7 +77,6 @@
 import org.apache.hyracks.algebricks.core.rewriter.base.HeuristicOptimizer;
 import org.apache.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule;
 import org.apache.hyracks.algebricks.rewriter.rules.BreakSelectIntoConjunctsRule;
-import org.apache.hyracks.algebricks.rewriter.rules.ComplexJoinInferenceRule;
 import org.apache.hyracks.algebricks.rewriter.rules.ComplexUnnestToProductRule;
 import org.apache.hyracks.algebricks.rewriter.rules.ConsolidateAssignsRule;
 import org.apache.hyracks.algebricks.rewriter.rules.ConsolidateSelectsRule;
@@ -110,6 +108,7 @@
 import org.apache.hyracks.algebricks.rewriter.rules.PushSubplanWithAggregateDownThroughProductRule;
 import org.apache.hyracks.algebricks.rewriter.rules.PushUnnestDownThroughUnionRule;
 import org.apache.hyracks.algebricks.rewriter.rules.ReinferAllTypesRule;
+import org.apache.hyracks.algebricks.rewriter.rules.RemoveCartesianProductWithEmptyBranchRule;
 import org.apache.hyracks.algebricks.rewriter.rules.RemoveRedundantGroupByDecorVars;
 import org.apache.hyracks.algebricks.rewriter.rules.RemoveRedundantVariablesRule;
 import org.apache.hyracks.algebricks.rewriter.rules.RemoveUnnecessarySortMergeExchange;
@@ -183,7 +182,6 @@
         condPushDownAndJoinInference.add(new CancelUnnestWithNestedListifyRule());
         condPushDownAndJoinInference.add(new SimpleUnnestToProductRule());
         condPushDownAndJoinInference.add(new ComplexUnnestToProductRule());
-        condPushDownAndJoinInference.add(new ComplexJoinInferenceRule());
         condPushDownAndJoinInference.add(new DisjunctivePredicateToJoinRule());
         condPushDownAndJoinInference.add(new PushSelectIntoJoinRule());
         condPushDownAndJoinInference.add(new IntroJoinInsideSubplanRule());
@@ -222,7 +220,6 @@
         fieldLoads.add(new ConstantFoldingRule());
         fieldLoads.add(new RemoveRedundantSelectRule());
         fieldLoads.add(new FeedScanCollectionToUnnest());
-        fieldLoads.add(new ComplexJoinInferenceRule());
         fieldLoads.add(new InlineSubplanInputForNestedTupleSourceRule());
         return fieldLoads;
     }
@@ -244,8 +241,7 @@
         consolidation.add(new CountVarToCountOneRule());
         consolidation.add(new RemoveUnusedAssignAndAggregateRule());
         consolidation.add(new RemoveRedundantGroupByDecorVars());
-        //unionRule => PushUnnestDownUnion => RemoveRedundantListifyRule cause these rules are correlated
-        consolidation.add(new IntroduceUnionRule());
+        //PushUnnestDownUnion => RemoveRedundantListifyRule cause these rules are correlated
         consolidation.add(new PushUnnestDownThroughUnionRule());
         consolidation.add(new RemoveRedundantListifyRule());
         return consolidation;
@@ -274,6 +270,7 @@
         planCleanupRules.add(new IntroduceDynamicTypeCastRule());
         planCleanupRules.add(new IntroduceDynamicTypeCastForExternalFunctionRule());
         planCleanupRules.add(new RemoveUnusedAssignAndAggregateRule());
+        planCleanupRules.add(new RemoveCartesianProductWithEmptyBranchRule());
         return planCleanupRules;
     }
 
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/FuzzyJoinRule.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/FuzzyJoinRule.java
index 99a418e..427eda7 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/FuzzyJoinRule.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/FuzzyJoinRule.java
@@ -266,7 +266,7 @@
         context.setVarCounter(counter.get());
 
         LogicalOperatorDeepCopyWithNewVariablesVisitor deepCopyVisitor = new LogicalOperatorDeepCopyWithNewVariablesVisitor(
-                context);
+                context, context);
 
         translator.addOperatorToMetaScope(new Identifier("#LEFT"), leftInputOp);
         translator.addVariableToMetaScope(new Identifier("$$LEFT"), leftInputVar);
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceUnionRule.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceUnionRule.java
deleted file mode 100644
index 51d04a6..0000000
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceUnionRule.java
+++ /dev/null
@@ -1,127 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *   http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied.  See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-package org.apache.asterix.optimizer.rules;
-
-import java.util.ArrayList;
-import java.util.List;
-
-import org.apache.commons.lang3.mutable.Mutable;
-
-import org.apache.asterix.om.functions.AsterixBuiltinFunctions;
-import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
-import org.apache.hyracks.algebricks.common.utils.Triple;
-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.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.operators.logical.AbstractBinaryJoinOperator;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.AssignOperator;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities;
-import org.apache.hyracks.algebricks.core.algebra.util.OperatorPropertiesUtil;
-import org.apache.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule;
-
-/**
- * @author kereno, ecarm002, ildar.absalyamov
- *         Generates a union operator and puts it instead of "assign <- [function-call: asterix:union]"
- *         Before rule:
- *         ============
- *         assign [var] <- [asterix:union(left_branch, right_branch)]
- *         join (TRUE)
- *         left_branch
- *         right_branch
- *         After rule:
- *         ============
- *         union (left_branch, right_branch, result_var)
- *         left_branch
- *         right_branch
- */
-public class IntroduceUnionRule implements IAlgebraicRewriteRule {
-
-    @Override
-    public boolean rewritePre(Mutable<ILogicalOperator> opRef, IOptimizationContext context) {
-        return false;
-    }
-
-    @Override
-    public boolean rewritePost(Mutable<ILogicalOperator> opRef, IOptimizationContext context)
-            throws AlgebricksException {
-
-        if (!opRef.getValue().getOperatorTag().equals(LogicalOperatorTag.ASSIGN)) {
-            return false;
-        }
-
-        AssignOperator assignUnion = (AssignOperator) opRef.getValue();
-
-        if (assignUnion.getExpressions().get(0).getValue().getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL)
-            return false;
-
-        AbstractFunctionCallExpression u = (AbstractFunctionCallExpression) assignUnion.getExpressions().get(0)
-                .getValue();
-        if (!AsterixBuiltinFunctions.UNION.equals(u.getFunctionIdentifier())) {
-            return false;
-        }
-
-        //Retrieving the logical variables for the union from the two aggregates which are inputs to the join
-        Mutable<ILogicalOperator> join = assignUnion.getInputs().get(0);
-
-        LogicalOperatorTag tag1 = join.getValue().getOperatorTag();
-        if (tag1 != LogicalOperatorTag.INNERJOIN && tag1 != LogicalOperatorTag.LEFTOUTERJOIN) {
-            return false;
-        }
-        AbstractBinaryJoinOperator join1 = (AbstractBinaryJoinOperator) join.getValue();
-        ILogicalExpression cond1 = join1.getCondition().getValue();
-        // don't try to push a product down
-        if (!OperatorPropertiesUtil.isAlwaysTrueCond(cond1)) {
-            return false;
-        }
-
-        List<Mutable<ILogicalOperator>> joinInputs = join.getValue().getInputs();
-
-        Mutable<ILogicalOperator> left_branch = joinInputs.get(0);
-        Mutable<ILogicalOperator> right_branch = joinInputs.get(1);
-
-        List<LogicalVariable> input1Var = new ArrayList<LogicalVariable>();
-        VariableUtilities.getProducedVariables(left_branch.getValue(), input1Var);
-
-        List<LogicalVariable> input2Var = new ArrayList<LogicalVariable>();
-        VariableUtilities.getProducedVariables(right_branch.getValue(), input2Var);
-
-        List<Triple<LogicalVariable, LogicalVariable, LogicalVariable>> varMap = new ArrayList<Triple<LogicalVariable, LogicalVariable, LogicalVariable>>(
-                1);
-        Triple<LogicalVariable, LogicalVariable, LogicalVariable> triple = new Triple<LogicalVariable, LogicalVariable, LogicalVariable>(
-                input1Var.get(0), input2Var.get(0), assignUnion.getVariables().get(0));
-        varMap.add(triple);
-        UnionAllOperator unionOp = new UnionAllOperator(varMap);
-
-        unionOp.getInputs().add(left_branch);
-        unionOp.getInputs().add(right_branch);
-
-        context.computeAndSetTypeEnvironmentForOperator(unionOp);
-
-        opRef.setValue(unionOp);
-
-        return true;
-
-    }
-}
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java
index d8e4c6a..824fe7a 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/InvertedIndexAccessMethod.java
@@ -610,13 +610,13 @@
 
         // Create first copy.
         LogicalOperatorDeepCopyWithNewVariablesVisitor firstDeepCopyVisitor = new LogicalOperatorDeepCopyWithNewVariablesVisitor(
-                context, newProbeSubTreeVarMap);
+                context, context, newProbeSubTreeVarMap);
         ILogicalOperator newProbeSubTree = firstDeepCopyVisitor.deepCopy(probeSubTree.root);
         inferTypes(newProbeSubTree, context);
         Mutable<ILogicalOperator> newProbeSubTreeRootRef = new MutableObject<ILogicalOperator>(newProbeSubTree);
         // Create second copy.
         LogicalOperatorDeepCopyWithNewVariablesVisitor secondDeepCopyVisitor = new LogicalOperatorDeepCopyWithNewVariablesVisitor(
-                context, joinInputSubTreeVarMap);
+                context, context, joinInputSubTreeVarMap);
         ILogicalOperator joinInputSubTree = secondDeepCopyVisitor.deepCopy(probeSubTree.root);
         inferTypes(joinInputSubTree, context);
         probeSubTree.rootRef.setValue(joinInputSubTree);
@@ -694,7 +694,7 @@
 
         // Copy the scan subtree in indexSubTree.
         LogicalOperatorDeepCopyWithNewVariablesVisitor deepCopyVisitor = new LogicalOperatorDeepCopyWithNewVariablesVisitor(
-                context);
+                context, context);
         ILogicalOperator scanSubTree = deepCopyVisitor.deepCopy(indexSubTree.root);
 
         Map<LogicalVariable, LogicalVariable> copyVarMap = deepCopyVisitor.getInputToOutputVariableMapping();
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 b3e07e5..beebe0f 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
@@ -402,7 +402,7 @@
             return op;
         }
         LogicalOperatorDeepCopyWithNewVariablesVisitor deepCopyVisitor = new LogicalOperatorDeepCopyWithNewVariablesVisitor(
-                context);
+                context, context);
         ILogicalOperator copiedInputOperator = deepCopyVisitor.deepCopy(subplanInputOperator);
 
         // Updates the primary key info in the copied plan segment.
@@ -668,9 +668,7 @@
 
     private void subtituteVariables(ILogicalOperator op) throws AlgebricksException {
         VariableUtilities.substituteVariables(op, subplanInputVarToCurrentVarMap, context);
-        for (Pair<LogicalVariable, LogicalVariable> pair : varMapIntroducedByRewriting) {
-            VariableUtilities.substituteVariables(op, pair.first, pair.second, context);
-        }
+        VariableUtilities.substituteVariables(op, varMapIntroducedByRewriting, context);
     }
 
     private void updateInputToOutputVarMapping(LogicalVariable oldVar, LogicalVariable newVar, boolean inNts) {
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlExpressionToPlanTranslator.java b/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlExpressionToPlanTranslator.java
index 097edbd..48c9a7f 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlExpressionToPlanTranslator.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlExpressionToPlanTranslator.java
@@ -19,6 +19,7 @@
 package org.apache.asterix.translator;
 
 import java.util.ArrayList;
+import java.util.Iterator;
 import java.util.List;
 
 import org.apache.asterix.algebra.base.ILangExpressionToPlanTranslator;
@@ -41,17 +42,17 @@
 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.common.utils.Triple;
 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.LogicalVariable;
-import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression;
-import org.apache.hyracks.algebricks.core.algebra.expressions.ScalarFunctionCallExpression;
+import org.apache.hyracks.algebricks.core.algebra.expressions.UnnestingFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.AssignOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.DistinctOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.NestedTupleSourceOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.ProjectOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
 
 /**
@@ -133,7 +134,7 @@
             result = produceFlworPlan(noFlworClause, isTop, resOpRef, rRes.second);
         }
         if (!isTop) {
-            context.existSubplan();
+            context.exitSubplan();
         }
 
         return result;
@@ -163,22 +164,53 @@
     @Override
     public Pair<ILogicalOperator, LogicalVariable> visit(UnionExpr unionExpr, Mutable<ILogicalOperator> tupSource)
             throws AsterixException {
-        //Translate the AQL union into an assign [var] <- [function-call: asterix:union, Args:[..]]
-        //The rule "IntroduceUnionRule" will translates this assign operator into the UnionAll operator.
-        Mutable<ILogicalOperator> ts = tupSource;
-        LogicalVariable assignedVar = context.newVar();
-        List<Mutable<ILogicalExpression>> inputVars = new ArrayList<Mutable<ILogicalExpression>>();
+        List<Mutable<ILogicalOperator>> inputOpRefsToUnion = new ArrayList<>();
+        List<LogicalVariable> vars = new ArrayList<>();
         for (Expression e : unionExpr.getExprs()) {
-            Pair<ILogicalOperator, LogicalVariable> op_var = e.accept(this, ts);
-            ts = new MutableObject<ILogicalOperator>(op_var.first);
-            VariableReferenceExpression var = new VariableReferenceExpression(op_var.second);
-            inputVars.add(new MutableObject<ILogicalExpression>(var));
+            // Visits the expression of one branch.
+            Pair<ILogicalOperator, LogicalVariable> opAndVar = e.accept(this, tupSource);
+
+            // Creates an unnest operator.
+            LogicalVariable unnestVar = context.newVar();
+            List<Mutable<ILogicalExpression>> args = new ArrayList<>();
+            args.add(new MutableObject<ILogicalExpression>(new VariableReferenceExpression(opAndVar.second)));
+            UnnestOperator unnestOp = new UnnestOperator(unnestVar,
+                    new MutableObject<ILogicalExpression>(new UnnestingFunctionCallExpression(
+                            FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.SCAN_COLLECTION), args)));
+            unnestOp.getInputs().add(new MutableObject<ILogicalOperator>(opAndVar.first));
+            inputOpRefsToUnion.add(new MutableObject<ILogicalOperator>(unnestOp));
+            vars.add(unnestVar);
         }
-        AbstractFunctionCallExpression union = new ScalarFunctionCallExpression(
-                FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.UNION), inputVars);
-        AssignOperator a = new AssignOperator(assignedVar, new MutableObject<ILogicalExpression>(union));
-        a.getInputs().add(ts);
-        return new Pair<ILogicalOperator, LogicalVariable>(a, assignedVar);
+
+        // Creates a tree of binary union-all operators.
+        UnionAllOperator topUnionAllOp = null;
+        LogicalVariable topUnionVar = null;
+        Iterator<Mutable<ILogicalOperator>> inputOpRefIterator = inputOpRefsToUnion.iterator();
+        Mutable<ILogicalOperator> leftInputBranch = inputOpRefIterator.next();
+        Iterator<LogicalVariable> inputVarIterator = vars.iterator();
+        LogicalVariable leftInputVar = inputVarIterator.next();
+
+        while (inputOpRefIterator.hasNext()) {
+            // Generates the variable triple <leftVar, rightVar, outputVar> .
+            topUnionVar = context.newVar();
+            Triple<LogicalVariable, LogicalVariable, LogicalVariable> varTriple = new Triple<>(leftInputVar,
+                    inputVarIterator.next(), topUnionVar);
+            List<Triple<LogicalVariable, LogicalVariable, LogicalVariable>> varTriples = new ArrayList<>();
+            varTriples.add(varTriple);
+
+            // Creates a binary union-all operator.
+            topUnionAllOp = new UnionAllOperator(varTriples);
+            topUnionAllOp.getInputs().add(leftInputBranch);
+            topUnionAllOp.getInputs().add(inputOpRefIterator.next());
+
+            // Re-assigns leftInputBranch and leftInputVar.
+            leftInputBranch = new MutableObject<ILogicalOperator>(topUnionAllOp);
+            leftInputVar = topUnionVar;
+        }
+
+        Pair<ILogicalOperator, LogicalVariable> result = aggListifyForSubquery(topUnionVar,
+                new MutableObject<ILogicalOperator>(topUnionAllOp), false);
+        return result;
     }
 
     private Pair<ILogicalOperator, LogicalVariable> produceFlworPlan(boolean noForClause, boolean isTop,
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java b/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
index a1a6d4b..dd4d6db 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
@@ -21,8 +21,12 @@
 import java.io.File;
 import java.io.IOException;
 import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
 import java.util.Iterator;
 import java.util.List;
+import java.util.Map;
+import java.util.Set;
 import java.util.concurrent.atomic.AtomicLong;
 
 import org.apache.asterix.algebra.base.ILangExpressionToPlanTranslator;
@@ -112,6 +116,7 @@
 import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
 import org.apache.hyracks.algebricks.core.algebra.functions.IFunctionInfo;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractOperatorWithNestedPlans;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AggregateOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AssignOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.DataSourceScanOperator;
@@ -129,6 +134,8 @@
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.SinkOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
+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.LocalOrderProperty;
 import org.apache.hyracks.algebricks.core.algebra.properties.OrderColumn;
@@ -403,6 +410,7 @@
         }
         globalPlanRoots.add(new MutableObject<ILogicalOperator>(topOp));
         ILogicalPlan plan = new ALogicalPlanImpl(globalPlanRoots);
+        eliminateSharedOperatorReferenceForPlan(plan);
         return plan;
     }
 
@@ -721,7 +729,7 @@
         SelectOperator sel1 = new SelectOperator(
                 new MutableObject<ILogicalExpression>(new VariableReferenceExpression(varCond)), false, null);
         sel1.getInputs().add(new MutableObject<ILogicalOperator>(pThen.first));
-        context.existSubplan();
+        context.exitSubplan();
 
         context.enterSubplan();
         Pair<ILogicalOperator, LogicalVariable> pElse = ifexpr.getElseExpr().accept(this, nestedSource);
@@ -730,7 +738,7 @@
                 new MutableObject<ILogicalExpression>(new VariableReferenceExpression(varCond)));
         SelectOperator sel2 = new SelectOperator(new MutableObject<ILogicalExpression>(notVarCond), false, null);
         sel2.getInputs().add(new MutableObject<ILogicalOperator>(pElse.first));
-        context.existSubplan();
+        context.exitSubplan();
 
         ILogicalPlan p1 = new ALogicalPlanImpl(new MutableObject<ILogicalOperator>(sel1));
         sp.getNestedPlans().add(p1);
@@ -1228,7 +1236,7 @@
         return k == Kind.LITERAL_EXPRESSION || k == Kind.LIST_CONSTRUCTOR_EXPRESSION
                 || k == Kind.RECORD_CONSTRUCTOR_EXPRESSION || k == Kind.VARIABLE_EXPRESSION || k == Kind.CALL_EXPRESSION
                 || k == Kind.OP_EXPRESSION || k == Kind.FIELD_ACCESSOR_EXPRESSION || k == Kind.INDEX_ACCESSOR_EXPRESSION
-                || k == Kind.UNARY_EXPRESSION || k == Kind.UNION_EXPRESSION;
+                || k == Kind.UNARY_EXPRESSION;
     }
 
     protected <T> List<T> mkSingletonArrayList(T item) {
@@ -1238,11 +1246,12 @@
     }
 
     protected ILogicalExpression makeUnnestExpression(ILogicalExpression expr) {
+        List<Mutable<ILogicalExpression>> argRefs = new ArrayList<>();
+        argRefs.add(new MutableObject<ILogicalExpression>(expr));
         switch (expr.getExpressionTag()) {
             case VARIABLE: {
                 return new UnnestingFunctionCallExpression(
-                        FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.SCAN_COLLECTION),
-                        new MutableObject<ILogicalExpression>(expr));
+                        FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.SCAN_COLLECTION), argRefs);
             }
             case FUNCTION_CALL: {
                 AbstractFunctionCallExpression fce = (AbstractFunctionCallExpression) expr;
@@ -1250,8 +1259,7 @@
                     return expr;
                 } else {
                     return new UnnestingFunctionCallExpression(
-                            FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.SCAN_COLLECTION),
-                            new MutableObject<ILogicalExpression>(expr));
+                            FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.SCAN_COLLECTION), argRefs);
                 }
             }
             default: {
@@ -1277,4 +1285,99 @@
         return false;
     }
 
+    /**
+     * Eliminate shared operator references in a query plan.
+     * Deep copy a new query plan subtree whenever there is a shared operator reference.
+     *
+     * @param plan,
+     *            the query plan.
+     * @throws AsterixException
+     */
+    private void eliminateSharedOperatorReferenceForPlan(ILogicalPlan plan) throws AsterixException {
+        for (Mutable<ILogicalOperator> opRef : plan.getRoots()) {
+            Set<Mutable<ILogicalOperator>> opRefSet = new HashSet<>();
+            eliminateSharedOperatorReference(opRef, opRefSet);
+        }
+    }
+
+    /**
+     * Eliminate shared operator references in a query plan rooted at <code>currentOpRef.getValue()</code>.
+     * Deep copy a new query plan subtree whenever there is a shared operator reference.
+     *
+     * @param currentOpRef,
+     *            the operator reference to consider
+     * @param opRefSet,
+     *            the set storing seen operator references so far.
+     * @return a mapping that maps old variables to new variables, for the ancestors of
+     *         <code>currentOpRef</code> to replace variables properly.
+     * @throws AsterixException
+     */
+    private Map<LogicalVariable, LogicalVariable> eliminateSharedOperatorReference(
+            Mutable<ILogicalOperator> currentOpRef, Set<Mutable<ILogicalOperator>> opRefSet) throws AsterixException {
+        try {
+            opRefSet.add(currentOpRef);
+            AbstractLogicalOperator currentOperator = (AbstractLogicalOperator) currentOpRef.getValue();
+
+            // Recursively eliminates shared references in nested plans.
+            if (currentOperator.hasNestedPlans()) {
+                // Since a nested plan tree itself can never be shared with another nested plan tree in
+                // another operator, the operation called in the if block does not need to replace
+                // any variables further for <code>currentOpRef.getValue()</code> nor its ancestor.
+                AbstractOperatorWithNestedPlans opWithNestedPlan = (AbstractOperatorWithNestedPlans) currentOperator;
+                for (ILogicalPlan plan : opWithNestedPlan.getNestedPlans()) {
+                    for (Mutable<ILogicalOperator> rootRef : plan.getRoots()) {
+                        Set<Mutable<ILogicalOperator>> nestedOpRefSet = new HashSet<>();
+                        eliminateSharedOperatorReference(rootRef, nestedOpRefSet);
+                    }
+                }
+            }
+
+            int childIndex = 0;
+            Map<LogicalVariable, LogicalVariable> varMap = new HashMap<>();
+            for (Mutable<ILogicalOperator> childRef : currentOperator.getInputs()) {
+                if (opRefSet.contains(childRef)) {
+                    // There is a shared operator reference in the query plan.
+                    // Deep copies the child plan.
+                    LogicalOperatorDeepCopyWithNewVariablesVisitor visitor = new LogicalOperatorDeepCopyWithNewVariablesVisitor(
+                            context, null);
+                    ILogicalOperator newChild = childRef.getValue().accept(visitor, null);
+                    Map<LogicalVariable, LogicalVariable> cloneVarMap = visitor.getInputToOutputVariableMapping();
+
+                    // Substitute variables according to the deep copy which generates new variables.
+                    VariableUtilities.substituteVariables(currentOperator, cloneVarMap, null);
+                    varMap.putAll(cloneVarMap);
+
+                    // Sets the new child.
+                    childRef = new MutableObject<ILogicalOperator>(newChild);
+                    currentOperator.getInputs().set(childIndex, childRef);
+                }
+
+                // Recursively eliminate shared operator reference for the operator subtree,
+                // even if it is a deep copy of some other one.
+                Map<LogicalVariable, LogicalVariable> childVarMap = eliminateSharedOperatorReference(childRef,
+                        opRefSet);
+                // Substitute variables according to the new subtree.
+                VariableUtilities.substituteVariables(currentOperator, childVarMap, null);
+
+                // Updates mapping like <$a, $b> in varMap to <$a, $c>, where there is a mapping <$b, $c>
+                // in childVarMap.
+                for (Map.Entry<LogicalVariable, LogicalVariable> entry : varMap.entrySet()) {
+                    LogicalVariable newVar = childVarMap.get(entry.getValue());
+                    if (newVar != null) {
+                        entry.setValue(newVar);
+                    }
+                }
+                varMap.putAll(childVarMap);
+                ++childIndex;
+            }
+
+            // Only retain live variables for parent operators to substitute variables.
+            Set<LogicalVariable> liveVars = new HashSet<>();
+            VariableUtilities.getLiveVariables(currentOperator, liveVars);
+            varMap.values().retainAll(liveVars);
+            return varMap;
+        } catch (AlgebricksException e) {
+            throw new AsterixException(e);
+        }
+    }
 }
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java b/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
index a708cf6..b3d6bea 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
@@ -20,9 +20,11 @@
 
 import java.util.ArrayList;
 import java.util.List;
+import java.util.Stack;
 
 import org.apache.asterix.algebra.base.ILangExpressionToPlanTranslator;
 import org.apache.asterix.common.exceptions.AsterixException;
+import org.apache.asterix.lang.common.base.Clause.ClauseType;
 import org.apache.asterix.lang.common.base.Expression;
 import org.apache.asterix.lang.common.base.Expression.Kind;
 import org.apache.asterix.lang.common.clause.LetClause;
@@ -72,7 +74,6 @@
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AggregateOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AssignOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.DistinctOperator;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.EmptyTupleSourceOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.InnerJoinOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.NestedTupleSourceOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.OuterUnnestOperator;
@@ -92,6 +93,7 @@
  */
 class SqlppExpressionToPlanTranslator extends LangExpressionToPlanTranslator implements ILangExpressionToPlanTranslator,
         ISqlppVisitor<Pair<ILogicalOperator, LogicalVariable>, Mutable<ILogicalOperator>> {
+    private Stack<Mutable<ILogicalOperator>> uncorrelatedLeftBranchStack = new Stack<Mutable<ILogicalOperator>>();
 
     public SqlppExpressionToPlanTranslator(AqlMetadataProvider metadataProvider, int currentVarCounter)
             throws AlgebricksException {
@@ -145,7 +147,7 @@
         Pair<ILogicalOperator, LogicalVariable> result = produceSelectPlan(selectExpression.isSubquery(), currentOpRef,
                 select.second);
         if (selectExpression.isSubquery()) {
-            context.existSubplan();
+            context.exitSubplan();
         }
         return result;
     }
@@ -230,7 +232,14 @@
         Mutable<ILogicalOperator> topOpRef = new MutableObject<ILogicalOperator>(unnestOp);
         if (fromTerm.hasCorrelateClauses()) {
             for (AbstractBinaryCorrelateClause correlateClause : fromTerm.getCorrelateClauses()) {
-                topOpRef = new MutableObject<ILogicalOperator>(correlateClause.accept(this, topOpRef).first);
+                if (correlateClause.getClauseType() == ClauseType.UNNEST_CLAUSE) {
+                    // Correlation is allowed.
+                    topOpRef = new MutableObject<ILogicalOperator>(correlateClause.accept(this, topOpRef).first);
+                } else {
+                    // Correlation is dis-allowed.
+                    uncorrelatedLeftBranchStack.push(topOpRef);
+                    topOpRef = new MutableObject<ILogicalOperator>(correlateClause.accept(this, tupSource).first);
+                }
             }
         }
         return new Pair<ILogicalOperator, LogicalVariable>(topOpRef.getValue(), fromVar);
@@ -239,12 +248,13 @@
     @Override
     public Pair<ILogicalOperator, LogicalVariable> visit(JoinClause joinClause, Mutable<ILogicalOperator> inputRef)
             throws AsterixException {
+        Mutable<ILogicalOperator> leftInputRef = uncorrelatedLeftBranchStack.pop();
         if (joinClause.getJoinType() == JoinType.INNER) {
             Pair<ILogicalOperator, LogicalVariable> rightBranch = generateUnnestForBinaryCorrelateRightBranch(
-                    joinClause, new MutableObject<ILogicalOperator>(new EmptyTupleSourceOperator()));
+                    joinClause, inputRef);
             // A join operator with condition TRUE.
             AbstractBinaryJoinOperator joinOperator = new InnerJoinOperator(
-                    new MutableObject<ILogicalExpression>(ConstantExpression.TRUE), inputRef,
+                    new MutableObject<ILogicalExpression>(ConstantExpression.TRUE), leftInputRef,
                     new MutableObject<ILogicalOperator>(rightBranch.first));
             Mutable<ILogicalOperator> joinOpRef = new MutableObject<ILogicalOperator>(joinOperator);
 
@@ -260,7 +270,7 @@
             SubplanOperator subplanOp = new SubplanOperator();
             Mutable<ILogicalOperator> ntsRef = new MutableObject<ILogicalOperator>(
                     new NestedTupleSourceOperator(new MutableObject<ILogicalOperator>(subplanOp)));
-            subplanOp.getInputs().add(inputRef);
+            subplanOp.getInputs().add(leftInputRef);
 
             // Enters the translation for a subplan.
             context.enterSubplan();
@@ -322,7 +332,7 @@
             aggOp.getInputs().add(new MutableObject<ILogicalOperator>(currentTopOp));
 
             // Exits the translation of a subplan.
-            context.existSubplan();
+            context.exitSubplan();
 
             // Sets the nested subplan of the subplan operator.
             ILogicalPlan subplan = new ALogicalPlanImpl(new MutableObject<ILogicalOperator>(aggOp));
diff --git a/asterix-algebra/src/main/java/org/apache/asterix/translator/TranslationContext.java b/asterix-algebra/src/main/java/org/apache/asterix/translator/TranslationContext.java
index 84a01b5..71de5ac 100644
--- a/asterix-algebra/src/main/java/org/apache/asterix/translator/TranslationContext.java
+++ b/asterix-algebra/src/main/java/org/apache/asterix/translator/TranslationContext.java
@@ -24,9 +24,10 @@
 
 import org.apache.asterix.lang.common.expression.VariableExpr;
 import org.apache.hyracks.algebricks.core.algebra.base.Counter;
+import org.apache.hyracks.algebricks.core.algebra.base.IVariableContext;
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
 
-public final class TranslationContext {
+public final class TranslationContext implements IVariableContext {
 
     private Counter varCounter;
 
@@ -39,10 +40,24 @@
         this.varCounter = varCounter;
     }
 
+    @Override
     public int getVarCounter() {
         return varCounter.get();
     }
 
+    @Override
+    public LogicalVariable newVar() {
+        varCounter.inc();
+        LogicalVariable var = new LogicalVariable(varCounter.get());
+        currentVarMap.put(varCounter.get(), var);
+        return var;
+    }
+
+    @Override
+    public void setVarCounter(int count) {
+        varCounter.set(count);
+    }
+
     public boolean isTopFlwor() {
         return topFlwor;
     }
@@ -73,13 +88,6 @@
         currentVarMap.put(v.getVar().getId(), var);
     }
 
-    public LogicalVariable newVar() {
-        varCounter.inc();
-        LogicalVariable var = new LogicalVariable(varCounter.get());
-        currentVarMap.put(varCounter.get(), var);
-        return var;
-    }
-
     /**
      * Within a subplan, an unbounded variable can be rebound in
      * the group-by operator. But the rebinding only exists
@@ -96,7 +104,7 @@
     /***
      * This method marks that the translation exits a subplan.
      */
-    public void existSubplan() {
+    public void exitSubplan() {
         if (!stack.isEmpty()) {
             currentVarMap = stack.pop();
         }
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1047.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1047.aql
new file mode 100644
index 0000000..4210fc3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1047.aql
@@ -0,0 +1,40 @@
+/*
+ * 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-1047.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type DBLPType as open {
+  id: int64,
+  dblpid: string,
+  title: string,
+  authors: string,
+  misc: string
+}
+
+create dataset DBLP(DBLPType) primary key id;
+
+
+(for $d in dataset DBLP where $d.id = 1 return $d)
+  union
+(for $d in dataset DBLP where $d.authors = "Alfred V. Aho John E. Hopcroft Jeffrey D. Ullman" return $d);
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-2.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-2.aql
new file mode 100644
index 0000000..82d1430
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-2.aql
@@ -0,0 +1,28 @@
+/*
+ * 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-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+
+for $tt in ($aa union $bb)
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-3.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-3.aql
new file mode 100644
index 0000000..2ab92dc
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-3.aql
@@ -0,0 +1,25 @@
+/*
+ * 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-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+return $aa union $bb;
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-4.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-4.aql
new file mode 100644
index 0000000..48f7c2a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205-4.aql
@@ -0,0 +1,30 @@
+/*
+ * 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-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+let $dd := [{"fa":4, "fb":5}, {"fa":6, "fb":7}]
+let $ccc := $aa union $bb union $dd
+
+for $tt in $ccc
+order by $tt.fa, $tt.fb
+return $tt;
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205.aql b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205.aql
new file mode 100644
index 0000000..763aba1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/query-ASTERIXDB-1205.aql
@@ -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.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+let $ccc := $aa union $bb
+
+for $tt in $ccc
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset.aql b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset.aql
new file mode 100644
index 0000000..7a55ee5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset.aql
@@ -0,0 +1,35 @@
+/*
+ * 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 TinySocial if exists;
+create dataverse TinySocial;
+use dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
+create dataset FacebookUsers2(FacebookUserType)
+primary key id;
+
+
+let $c := dataset("FacebookUsers") union dataset("FacebookUsers2")
+for $res in $c order by $res.id return $res
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset2.aql b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset2.aql
new file mode 100644
index 0000000..049652f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset2.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 TinySocial if exists;
+create dataverse TinySocial;
+use dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
+create dataset FacebookUsers2(FacebookUserType)
+primary key id;
+
+dataset("FacebookUsers") union dataset("FacebookUsers2");
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset3.aql b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset3.aql
new file mode 100644
index 0000000..edb429f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/union_dataset3.aql
@@ -0,0 +1,35 @@
+/*
+ * 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 TinySocial if exists;
+create dataverse TinySocial;
+use dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
+create dataset FacebookUsers2(FacebookUserType)
+primary key id;
+
+
+for $res in (dataset("FacebookUsers") union dataset("FacebookUsers2"))
+order by $res.id return $res
diff --git a/asterix-app/src/test/resources/optimizerts/queries/union/union_query.aql b/asterix-app/src/test/resources/optimizerts/queries/union/union_query.aql
new file mode 100644
index 0000000..3e6ebaf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/union/union_query.aql
@@ -0,0 +1,41 @@
+/*
+ * 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 TinySocial if exists;
+create dataverse TinySocial;
+use dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create type FacebookMessageType as open {
+        message-id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
+create dataset FacebookMessages(FacebookMessageType)
+primary key message-id;
+
+let $t1 := for $t in dataset FacebookUsers return $t.id
+let $t2 := for $s in dataset FacebookMessages return $s.message-id
+let $c := $t1 union $t2
+for $res in $c distinct by $res order by $res return $res
diff --git a/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan b/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan
index 1f99eee..9d17ca8 100644
--- a/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan
@@ -2,8 +2,8 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$100(DESC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$100(DESC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$89(DESC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$89(DESC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- EXTERNAL_GROUP_BY[$$119]  |PARTITIONED|
                       {
@@ -19,7 +19,7 @@
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                       -- STREAM_PROJECT  |PARTITIONED|
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- HYBRID_HASH_JOIN [$$81, $$98][$$115, $$88]  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$94, $$81][$$88, $$115]  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ASSIGN  |PARTITIONED|
@@ -66,8 +66,8 @@
                                                     -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- HYBRID_HASH_JOIN [$$89][$$87]  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
+                                                          -- HYBRID_HASH_JOIN [$$90][$$87]  |PARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$90]  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 -- ASSIGN  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/q08_group_by.plan b/asterix-app/src/test/resources/optimizerts/results/q08_group_by.plan
index 601091e..1aca471 100644
--- a/asterix-app/src/test/resources/optimizerts/results/q08_group_by.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/q08_group_by.plan
@@ -31,8 +31,8 @@
                               -- HASH_PARTITION_EXCHANGE [$$81]  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- HYBRID_HASH_JOIN [$$92][$$82]  |PARTITIONED|
-                                      -- HASH_PARTITION_EXCHANGE [$$92]  |PARTITIONED|
+                                    -- HYBRID_HASH_JOIN [$$94][$$82]  |PARTITIONED|
+                                      -- HASH_PARTITION_EXCHANGE [$$94]  |PARTITIONED|
                                         -- STREAM_SELECT  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ASSIGN  |PARTITIONED|
@@ -43,8 +43,8 @@
                                       -- HASH_PARTITION_EXCHANGE [$$82]  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- HYBRID_HASH_JOIN [$$89][$$83]  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
+                                            -- HYBRID_HASH_JOIN [$$91][$$83]  |PARTITIONED|
+                                              -- HASH_PARTITION_EXCHANGE [$$91]  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -54,8 +54,8 @@
                                               -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- HYBRID_HASH_JOIN [$$87][$$84]  |PARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$87]  |PARTITIONED|
+                                                    -- HYBRID_HASH_JOIN [$$89][$$84]  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- ASSIGN  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/q09_group_by.plan b/asterix-app/src/test/resources/optimizerts/results/q09_group_by.plan
index fc2abd2..9f48723 100644
--- a/asterix-app/src/test/resources/optimizerts/results/q09_group_by.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/q09_group_by.plan
@@ -15,7 +15,7 @@
               -- HASH_PARTITION_EXCHANGE [$$84]  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- HYBRID_HASH_JOIN [$$64, $$63][$$69, $$84]  |PARTITIONED|
+                    -- HYBRID_HASH_JOIN [$$63, $$64][$$84, $$69]  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ASSIGN  |PARTITIONED|
@@ -30,8 +30,8 @@
                               -- HASH_PARTITION_EXCHANGE [$$65]  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- HYBRID_HASH_JOIN [$$77][$$66]  |PARTITIONED|
-                                      -- HASH_PARTITION_EXCHANGE [$$77]  |PARTITIONED|
+                                    -- HYBRID_HASH_JOIN [$$72][$$66]  |PARTITIONED|
+                                      -- HASH_PARTITION_EXCHANGE [$$72]  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ASSIGN  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |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 5062719..28625aa 100644
--- a/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
@@ -3,12 +3,12 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$7(ASC) ]  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$89]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$86]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$89(ASC)] HASH:[$$89]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$86(ASC)] HASH:[$$86]  |PARTITIONED|
               -- SORT_GROUP_BY[$$59]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
@@ -21,46 +21,48 @@
                         -- STREAM_SELECT  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- PRE_CLUSTERED_GROUP_BY[$$86]  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$83]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
                                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$86(ASC)] HASH:[$$86]  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$62]  |PARTITIONED|
-                                          {
-                                            -- AGGREGATE  |LOCAL|
-                                              -- STREAM_SELECT  |LOCAL|
-                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                          }
+                                -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$62(ASC)]  |PARTITIONED|
+                                      -- PRE_CLUSTERED_GROUP_BY[$$65, $$11, $$62]  |PARTITIONED|
+                                              {
+                                                -- AGGREGATE  |LOCAL|
+                                                  -- STREAM_SELECT  |LOCAL|
+                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                              }
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- STREAM_PROJECT  |PARTITIONED|
+                                          -- STABLE_SORT [$$65(ASC), $$65(ASC), $$62(ASC)]  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- HYBRID_HASH_JOIN [$$67][$$68]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$67]  |PARTITIONED|
-                                                  -- ASSIGN  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ASSIGN  |PARTITIONED|
-                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- HYBRID_HASH_JOIN [$$83][$$82]  |PARTITIONED|
-                                                              -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
-                                                                -- UNNEST  |UNPARTITIONED|
-                                                                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                                              -- HASH_PARTITION_EXCHANGE [$$82]  |PARTITIONED|
-                                                                -- ASSIGN  |PARTITIONED|
-                                                                  -- STREAM_PROJECT  |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|
-                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        -- ASSIGN  |PARTITIONED|
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$68]  |PARTITIONED|
-                                                  -- ASSIGN  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- EMPTY_TUPLE_SOURCE  |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/split-materialization-above-join.plan b/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan
index 19eaf25..f22f71a 100644
--- a/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan
@@ -8,151 +8,153 @@
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- HYBRID_HASH_JOIN [$$94, $$93][$$26, $$25]  |PARTITIONED|
-                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- NESTED_LOOP  |PARTITIONED|
-                          -- BROADCAST_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|
-                                  -- SPLIT  |PARTITIONED|
-                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- DATASOURCE_SCAN  |PARTITIONED|
-                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    -- HYBRID_HASH_JOIN [$$26][$$94]  |PARTITIONED|
                       -- HASH_PARTITION_EXCHANGE [$$26]  |PARTITIONED|
-                        -- PRE_CLUSTERED_GROUP_BY[$$122, $$124]  |PARTITIONED|
-                                {
-                                  -- AGGREGATE  |LOCAL|
-                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                }
+                        -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- STABLE_SORT [$$122(ASC), $$124(ASC)]  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$122, $$124]  |PARTITIONED|
-                                -- STREAM_PROJECT  |PARTITIONED|
-                                  -- ASSIGN  |PARTITIONED|
-                                    -- STREAM_SELECT  |PARTITIONED|
-                                      -- STREAM_PROJECT  |PARTITIONED|
-                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- HYBRID_HASH_JOIN [$$12][$$23]  |PARTITIONED|
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$93][$$25]  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$25]  |PARTITIONED|
+                                -- PRE_CLUSTERED_GROUP_BY[$$122, $$124]  |PARTITIONED|
+                                        {
+                                          -- AGGREGATE  |LOCAL|
+                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                        }
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- STABLE_SORT [$$122(ASC), $$124(ASC)]  |PARTITIONED|
+                                      -- HASH_PARTITION_EXCHANGE [$$122, $$124]  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- STREAM_SELECT  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
-                                                -- ASSIGN  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- SPLIT  |PARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
-                                                        -- UNNEST  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- PRE_CLUSTERED_GROUP_BY[$$97]  |PARTITIONED|
-                                                                    {
-                                                                      -- AGGREGATE  |LOCAL|
-                                                                        -- STREAM_SELECT  |LOCAL|
-                                                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                    }
-                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- STABLE_SORT [$$97(ASC), $$21(ASC)]  |PARTITIONED|
-                                                                  -- HASH_PARTITION_EXCHANGE [$$97]  |PARTITIONED|
-                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
-                                                                          -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
-                                                                            -- STREAM_PROJECT  |PARTITIONED|
-                                                                              -- UNNEST  |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 [$$18]  |PARTITIONED|
-                                                                            -- ASSIGN  |PARTITIONED|
-                                                                              -- RUNNING_AGGREGATE  |PARTITIONED|
-                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                  -- SORT_MERGE_EXCHANGE [$$105(ASC), $$18(ASC) ]  |PARTITIONED|
-                                                                                    -- STABLE_SORT [$$105(ASC), $$18(ASC)]  |PARTITIONED|
-                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                        -- PRE_CLUSTERED_GROUP_BY[$$130]  |PARTITIONED|
-                                                                                                {
-                                                                                                  -- AGGREGATE  |LOCAL|
-                                                                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                                }
-                                                                                          -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$130(ASC)] HASH:[$$130]  |PARTITIONED|
-                                                                                            -- SORT_GROUP_BY[$$17]  |PARTITIONED|
-                                                                                                    {
-                                                                                                      -- AGGREGATE  |LOCAL|
-                                                                                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                                    }
-                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                  -- UNNEST  |PARTITIONED|
-                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                      -- ASSIGN  |PARTITIONED|
-                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                            -- SPLIT  |PARTITIONED|
-                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- SPLIT  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
-                                                  -- UNNEST  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- HYBRID_HASH_JOIN [$$12][$$23]  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- PRE_CLUSTERED_GROUP_BY[$$97]  |PARTITIONED|
-                                                              {
-                                                                -- AGGREGATE  |LOCAL|
-                                                                  -- STREAM_SELECT  |LOCAL|
-                                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                              }
-                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- STABLE_SORT [$$97(ASC), $$21(ASC)]  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$97]  |PARTITIONED|
-                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
-                                                                    -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
-                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                        -- UNNEST  |PARTITIONED|
-                                                                          -- ASSIGN  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- SPLIT  |PARTITIONED|
+                                                              -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                                                -- UNNEST  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- PRE_CLUSTERED_GROUP_BY[$$97]  |PARTITIONED|
+                                                                            {
+                                                                              -- AGGREGATE  |LOCAL|
+                                                                                -- STREAM_SELECT  |LOCAL|
+                                                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                            }
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- STABLE_SORT [$$97(ASC), $$21(ASC)]  |PARTITIONED|
+                                                                          -- HASH_PARTITION_EXCHANGE [$$97]  |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 [$$18]  |PARTITIONED|
-                                                                      -- ASSIGN  |PARTITIONED|
-                                                                        -- RUNNING_AGGREGATE  |PARTITIONED|
-                                                                          -- STREAM_PROJECT  |PARTITIONED|
-                                                                            -- SORT_MERGE_EXCHANGE [$$105(ASC), $$18(ASC) ]  |PARTITIONED|
-                                                                              -- STABLE_SORT [$$105(ASC), $$18(ASC)]  |PARTITIONED|
-                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                  -- PRE_CLUSTERED_GROUP_BY[$$130]  |PARTITIONED|
-                                                                                          {
-                                                                                            -- AGGREGATE  |LOCAL|
-                                                                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                          }
-                                                                                    -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$130(ASC)] HASH:[$$130]  |PARTITIONED|
-                                                                                      -- SORT_GROUP_BY[$$17]  |PARTITIONED|
-                                                                                              {
-                                                                                                -- AGGREGATE  |LOCAL|
-                                                                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                              }
-                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
+                                                                                  -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
+                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                      -- UNNEST  |PARTITIONED|
+                                                                                        -- ASSIGN  |PARTITIONED|
                                                                                           -- STREAM_PROJECT  |PARTITIONED|
-                                                                                            -- UNNEST  |PARTITIONED|
-                                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                            -- ASSIGN  |PARTITIONED|
+                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                  -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
+                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                      -- RUNNING_AGGREGATE  |PARTITIONED|
+                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                          -- SORT_MERGE_EXCHANGE [$$102(ASC), $$18(ASC) ]  |PARTITIONED|
+                                                                                            -- STABLE_SORT [$$102(ASC), $$18(ASC)]  |PARTITIONED|
+                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                -- PRE_CLUSTERED_GROUP_BY[$$130]  |PARTITIONED|
+                                                                                                        {
+                                                                                                          -- AGGREGATE  |LOCAL|
+                                                                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                        }
+                                                                                                  -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$130(ASC)] HASH:[$$130]  |PARTITIONED|
+                                                                                                    -- SORT_GROUP_BY[$$17]  |PARTITIONED|
+                                                                                                            {
+                                                                                                              -- AGGREGATE  |LOCAL|
+                                                                                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                            }
+                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                          -- UNNEST  |PARTITIONED|
+                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                    -- SPLIT  |PARTITIONED|
+                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- SPLIT  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                                          -- UNNEST  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- PRE_CLUSTERED_GROUP_BY[$$97]  |PARTITIONED|
+                                                                      {
+                                                                        -- AGGREGATE  |LOCAL|
+                                                                          -- STREAM_SELECT  |LOCAL|
+                                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                      }
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- STABLE_SORT [$$97(ASC), $$21(ASC)]  |PARTITIONED|
+                                                                    -- HASH_PARTITION_EXCHANGE [$$97]  |PARTITIONED|
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
+                                                                            -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
+                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                -- UNNEST  |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 [$$18]  |PARTITIONED|
+                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                -- RUNNING_AGGREGATE  |PARTITIONED|
+                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                    -- SORT_MERGE_EXCHANGE [$$102(ASC), $$18(ASC) ]  |PARTITIONED|
+                                                                                      -- STABLE_SORT [$$102(ASC), $$18(ASC)]  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                          -- PRE_CLUSTERED_GROUP_BY[$$130]  |PARTITIONED|
+                                                                                                  {
+                                                                                                    -- AGGREGATE  |LOCAL|
+                                                                                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                  }
+                                                                                            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$130(ASC)] HASH:[$$130]  |PARTITIONED|
+                                                                                              -- SORT_GROUP_BY[$$17]  |PARTITIONED|
+                                                                                                      {
+                                                                                                        -- AGGREGATE  |LOCAL|
+                                                                                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                      }
+                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                   -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                      -- SPLIT  |PARTITIONED|
-                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                          -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                    -- UNNEST  |PARTITIONED|
+                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                        -- ASSIGN  |PARTITIONED|
+                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                              -- SPLIT  |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|
+                              -- SPLIT  |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/union/query-ASTERIXDB-1047.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1047.plan
new file mode 100644
index 0000000..59a50fb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1047.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- AGGREGATE  |UNPARTITIONED|
+      -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+        -- UNION_ALL  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- STREAM_PROJECT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- BTREE_SEARCH  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- STREAM_SELECT  |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/union/query-ASTERIXDB-1205-2.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-2.plan
new file mode 100644
index 0000000..1d21071
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-2.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+        -- STABLE_SORT [$$19(ASC), $$20(ASC)]  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- UNION_ALL  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-3.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-3.plan
new file mode 100644
index 0000000..a1f37e8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-3.plan
@@ -0,0 +1,15 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- AGGREGATE  |UNPARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+        -- UNION_ALL  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- STREAM_PROJECT  |UNPARTITIONED|
+              -- UNNEST  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- STREAM_PROJECT  |UNPARTITIONED|
+              -- UNNEST  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-4.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-4.plan
new file mode 100644
index 0000000..48950e8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205-4.plan
@@ -0,0 +1,27 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+        -- STABLE_SORT [$$28(ASC), $$29(ASC)]  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- UNION_ALL  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- UNION_ALL  |UNPARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                    -- ASSIGN  |UNPARTITIONED|
+                      -- STREAM_PROJECT  |UNPARTITIONED|
+                        -- UNNEST  |UNPARTITIONED|
+                          -- ASSIGN  |UNPARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                    -- ASSIGN  |UNPARTITIONED|
+                      -- STREAM_PROJECT  |UNPARTITIONED|
+                        -- UNNEST  |UNPARTITIONED|
+                          -- ASSIGN  |UNPARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205.plan b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205.plan
new file mode 100644
index 0000000..115b5b6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/query-ASTERIXDB-1205.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+        -- STABLE_SORT [$$20(ASC), $$21(ASC)]  |UNPARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+            -- UNION_ALL  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                -- ASSIGN  |UNPARTITIONED|
+                  -- STREAM_PROJECT  |UNPARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- ASSIGN  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/union_dataset.plan b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset.plan
new file mode 100644
index 0000000..fdc7d4c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset.plan
@@ -0,0 +1,21 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- SORT_MERGE_EXCHANGE [$$11(ASC) ]  |PARTITIONED|
+        -- STABLE_SORT [$$11(ASC)]  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- UNION_ALL  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |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|
+                    -- 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/union/union_dataset2.plan b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset2.plan
new file mode 100644
index 0000000..c1b97aa
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset2.plan
@@ -0,0 +1,17 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- AGGREGATE  |UNPARTITIONED|
+      -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+        -- UNION_ALL  |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|
+          -- 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|
diff --git a/asterix-app/src/test/resources/optimizerts/results/union/union_dataset3.plan b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset3.plan
new file mode 100644
index 0000000..9d51d1c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/union_dataset3.plan
@@ -0,0 +1,21 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- SORT_MERGE_EXCHANGE [$$10(ASC) ]  |PARTITIONED|
+        -- STABLE_SORT [$$10(ASC)]  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- UNION_ALL  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |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|
+                    -- 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/union/union_query.plan b/asterix-app/src/test/resources/optimizerts/results/union/union_query.plan
new file mode 100644
index 0000000..67de4c9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/union/union_query.plan
@@ -0,0 +1,19 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- SORT_MERGE_EXCHANGE [$$16(ASC) ]  |PARTITIONED|
+    -- PRE_SORTED_DISTINCT_BY  |PARTITIONED|
+      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+        -- STABLE_SORT [$$16(ASC)]  |PARTITIONED|
+          -- HASH_PARTITION_EXCHANGE [$$16]  |PARTITIONED|
+            -- UNION_ALL  |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|
+              -- 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|
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.ddl.aql
new file mode 100644
index 0000000..5586ad23c
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.ddl.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 DBLPType as open {
+  id: int64,
+  dblpid: string,
+  title: string,
+  authors: string,
+  misc: string
+}
+
+create dataset DBLP(DBLPType) primary key id;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.2.update.aql
new file mode 100644
index 0000000..bbee2a1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.2.update.aql
@@ -0,0 +1,24 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use dataverse test;
+
+load dataset DBLP
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/pub-small/dblp-small-id.txt"),("format"="delimited-text"),("delimiter"=":")) pre-sorted;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.3.query.aql
new file mode 100644
index 0000000..f30a7c4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.3.query.aql
@@ -0,0 +1,31 @@
+/*
+ * 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-1047.
+ */
+
+use dataverse test;
+
+for $i in (
+  (for $d in dataset DBLP where $d.id = 1 return $d)
+  union
+  (for $d in dataset DBLP where $d.authors = "Alfred V. Aho John E. Hopcroft Jeffrey D. Ullman" return $d)
+)
+order by $i.id
+return $i;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.1.ddl.aql
new file mode 100644
index 0000000..bd244d0
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.1.ddl.aql
@@ -0,0 +1,19 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.2.update.aql
new file mode 100644
index 0000000..042f3ce
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.2.update.aql
@@ -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/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.3.query.aql
new file mode 100644
index 0000000..82d1430
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205.3.query.aql
@@ -0,0 +1,28 @@
+/*
+ * 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-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+
+for $tt in ($aa union $bb)
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.1.ddl.aql
new file mode 100644
index 0000000..bd244d0
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.1.ddl.aql
@@ -0,0 +1,19 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.2.update.aql
new file mode 100644
index 0000000..042f3ce
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.2.update.aql
@@ -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/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.3.query.aql
new file mode 100644
index 0000000..bee2076
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205.3.query.aql
@@ -0,0 +1,30 @@
+/*
+ * 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-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+let $dd := [{"fa":4, "fb":5}, {"fa":6, "fb":7}]
+let $ccc := $aa union $bb union $dd
+
+for $tt in $ccc
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.ddl.aql
new file mode 100644
index 0000000..bd244d0
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.ddl.aql
@@ -0,0 +1,19 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.2.update.aql
new file mode 100644
index 0000000..042f3ce
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.2.update.aql
@@ -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/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.3.query.aql
new file mode 100644
index 0000000..763aba1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.3.query.aql
@@ -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.
+ */
+/**
+ * This query is to verify the fix of ASTERIXDB-1205.
+ */
+
+let $aa := [{"fa":1, "fb":1}, {"fa":2, "fb":1}]
+let $bb := [{"fa":1, "fb":0}, {"fa":1, "fb":1}, {"fa":3, "fb":1}]
+let $ccc := $aa union $bb
+
+for $tt in $ccc
+order by $tt.fa, $tt.fb
+return $tt;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.ddl.aql
new file mode 100644
index 0000000..bd244d0
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.ddl.aql
@@ -0,0 +1,19 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.2.update.aql
new file mode 100644
index 0000000..042f3ce
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.2.update.aql
@@ -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/union/query-ASTERIXDB-300/query-ASTERIXDB-300.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.3.query.aql
new file mode 100644
index 0000000..95d9968
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/query-ASTERIXDB-300/query-ASTERIXDB-300.3.query.aql
@@ -0,0 +1,35 @@
+/*
+ * 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-300.
+ */
+
+let $a := [
+{"id":1234,"name":"John Doe","age":56,"salary":50000,"dept":"HR"}
+]
+let $b := [
+{"id":3424,"name":"Roger Sanders","age":46,"salary":60000,"dept":"Publishing"}
+]
+
+let $c := $a union $b
+return
+  for $i in $c
+  order by $i.id
+  return $i
+;
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/union/union.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/union/union.3.query.aql
index 4b8b4ab..d112458 100644
--- a/asterix-app/src/test/resources/runtimets/queries/union/union/union.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/union/union/union.3.query.aql
@@ -21,4 +21,4 @@
 let $t1 := for $t in dataset FacebookUsers return $t.id
 let $t2 := for $s in dataset FacebookMessages return $s.message-id
 let $c := $t1 union $t2
-for $res in $c distinct by $res return $res
+for $res in $c distinct by $res order by $res return $res
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/union2/union.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.1.ddl.aql
new file mode 100644
index 0000000..1936ef8
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.1.ddl.aql
@@ -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.
+ */
+drop dataverse TinySocial if exists;
+create dataverse TinySocial;
+use dataverse TinySocial;
+
+create type FacebookUserType as open {
+        id: int
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key id;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/union2/union.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.2.update.aql
new file mode 100644
index 0000000..a6b70a3
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.2.update.aql
@@ -0,0 +1,22 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+use dataverse TinySocial;
+
+load dataset FacebookUsers using localfs
+(("path"="asterix_nc1://data/tinysocial/fbu.adm"),("format"="adm"));
diff --git a/asterix-app/src/test/resources/runtimets/queries/union/union2/union.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.3.query.aql
new file mode 100644
index 0000000..4b52c77
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/union/union2/union.3.query.aql
@@ -0,0 +1,22 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+use dataverse TinySocial;
+
+let $c := dataset("FacebookUsers") union dataset("FacebookUsers")
+for $res in $c order by $res.id return $res
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_04/join_q_04.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_04/join_q_04.1.ddl.sqlpp
index a0795af..7dffaf6 100644
--- a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_04/join_q_04.1.ddl.sqlpp
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_04/join_q_04.1.ddl.sqlpp
@@ -18,6 +18,7 @@
  */
 /*
  * Description  : This test case is to verify the fix for issue51
+ */
 
 drop  database test if exists;
 create  database test;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.1.ddl.sqlpp
new file mode 100644
index 0000000..0a583a4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.1.ddl.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * 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.AddressType as
+{
+  number : int64,
+  street : string,
+  city : string
+}
+
+create type test.CustomerType as
+ closed {
+  cid : int64,
+  name : string,
+  cashBack : int64,
+  age : int64?,
+  address : AddressType?,
+  lastorder : {
+      oid : int64,
+      total : float
+  }
+
+}
+
+create type test.OrderType as
+{
+  oid : int64,
+  cid : int64,
+  orderstatus : string,
+  orderpriority : string,
+  clerk : string,
+  total : float,
+  items : [int64]
+}
+
+create external table Customers(CustomerType) using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm"));
+
+create external table Orders(OrderType) using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"(("path"="asterix_nc1://data/nontagged/orderData.json"),("format"="adm"));
+
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.2.update.sqlpp
new file mode 100644
index 0000000..6c98c1e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.2.update.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * 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/custord/join_q_05/join_q_05.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.3.query.sqlpp
new file mode 100644
index 0000000..c7533ae
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_05/join_q_05.3.query.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * 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;
+
+
+WITH customer AS (SELECT ELEMENT c FROM Customers c),
+     "orders" AS (SELECT ELEMENT o FROM Orders o)
+SELECT c.name AS cust_name,
+       c.age AS cust_age,
+       o.total AS order_total,
+       [o.oid,o.cid] AS orderList
+FROM customer c JOIN orders o ON c.cid = o.cid
+ORDER BY c.name,o.total
+;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.1.ddl.sqlpp
new file mode 100644
index 0000000..821b565
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.1.ddl.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * 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.AddressType as
+{
+  number : int64,
+  street : string,
+  city : string
+}
+
+create type test.CustomerType as
+ closed {
+  cid : int64,
+  name : string,
+  cashBack : int64,
+  age : int64?,
+  address : AddressType?,
+  lastorder : {
+      oid : int64,
+      total : float
+  }
+
+}
+
+create external table Customers(CustomerType) using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm"));
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.2.update.sqlpp
new file mode 100644
index 0000000..6c98c1e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.2.update.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * 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/custord/join_q_06/join_q_06.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.3.query.sqlpp
new file mode 100644
index 0000000..c9e9876
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_06/join_q_06.3.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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;
+
+
+WITH customer AS (SELECT ELEMENT c FROM Customers c)
+
+SELECT c.name AS cust_name,
+       c.cashBack AS cust_cashBack
+FROM customer c JOIN [min((SELECT ELEMENT c.cashBack FROM customer c))] as min_cashBack
+     ON c.cashBack = min_cashBack
+ORDER BY c.cid, c.name
+;
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.1.ddl.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.1.ddl.sqlpp
new file mode 100644
index 0000000..821b565
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.1.ddl.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * 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.AddressType as
+{
+  number : int64,
+  street : string,
+  city : string
+}
+
+create type test.CustomerType as
+ closed {
+  cid : int64,
+  name : string,
+  cashBack : int64,
+  age : int64?,
+  address : AddressType?,
+  lastorder : {
+      oid : int64,
+      total : float
+  }
+
+}
+
+create external table Customers(CustomerType) using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"(("path"="asterix_nc1://data/nontagged/customerData.json"),("format"="adm"));
diff --git a/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.2.update.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.2.update.sqlpp
new file mode 100644
index 0000000..6c98c1e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.2.update.sqlpp
@@ -0,0 +1,20 @@
+/*
+ * 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/custord/join_q_07/join_q_07.3.query.sqlpp b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.3.query.sqlpp
new file mode 100644
index 0000000..05c71c2
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries_sqlpp/custord/join_q_07/join_q_07.3.query.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * 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 expected to fail because variable c
+ * is undefined for subquery "(SELECT ELEMENT c.cashBack FROM c c)".
+ */
+
+USE test;
+
+
+WITH customer AS (SELECT ELEMENT c FROM Customers c)
+
+SELECT c.name AS cust_name,
+       c.cashBack AS cust_cashBack
+FROM customer c JOIN [min((SELECT ELEMENT c.cashBack FROM c c))] as min_cashBack
+     ON c.cashBack = min_cashBack
+ORDER BY c.cid, c.name
+;
diff --git a/asterix-app/src/test/resources/runtimets/results/custord/join_q_06/join_q_06.1.adm b/asterix-app/src/test/resources/runtimets/results/custord/join_q_06/join_q_06.1.adm
new file mode 100644
index 0000000..bcbe1fb
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/custord/join_q_06/join_q_06.1.adm
@@ -0,0 +1 @@
+{ "cust_name": "Jodi Rotruck", "cust_cashBack": 100 }
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.adm
new file mode 100644
index 0000000..2a14594
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1047/query-ASTERIXDB-1047.1.adm
@@ -0,0 +1,3 @@
+{ "id": 1, "dblpid": "books/acm/kim95/AnnevelinkACFHK95", "title": "Object SQL - A Language for the Design and Implementation of Object Databases.", "authors": "Jurgen Annevelink Rafiul Ahad Amelia Carlson Daniel H. Fishman Michael L. Heytens William Kent", "misc": "2002-01-03 42-68 1995 Modern Database Systems db/books/collections/kim95.html#AnnevelinkACFHK95" }
+{ "id": 73, "dblpid": "books/aw/AhoHU74", "title": "The Design and Analysis of Computer Algorithms.", "authors": "Alfred V. Aho John E. Hopcroft Jeffrey D. Ullman", "misc": "2002-01-03 Addison-Wesley 1974 0-201-00029-6" }
+{ "id": 75, "dblpid": "books/aw/AhoHU83", "title": "Data Structures and Algorithms.", "authors": "Alfred V. Aho John E. Hopcroft Jeffrey D. Ullman", "misc": "2002-01-03 Addison-Wesley 1983 0-201-00023-7" }
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205-2.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205-2.1.adm
new file mode 100644
index 0000000..2ddaa6e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-2/query-ASTERIXDB-1205-2.1.adm
@@ -0,0 +1,5 @@
+{ "fa": 1, "fb": 0 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 2, "fb": 1 }
+{ "fa": 3, "fb": 1 }
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205-3.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205-3.1.adm
new file mode 100644
index 0000000..12a9fe6
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205-3/query-ASTERIXDB-1205-3.1.adm
@@ -0,0 +1,7 @@
+{ "fa": 1, "fb": 0 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 2, "fb": 1 }
+{ "fa": 3, "fb": 1 }
+{ "fa": 4, "fb": 5 }
+{ "fa": 6, "fb": 7 }
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.adm
new file mode 100644
index 0000000..2ddaa6e
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-1205/query-ASTERIXDB-1205.1.adm
@@ -0,0 +1,5 @@
+{ "fa": 1, "fb": 0 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 1, "fb": 1 }
+{ "fa": 2, "fb": 1 }
+{ "fa": 3, "fb": 1 }
diff --git a/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.adm b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.adm
new file mode 100644
index 0000000..d580e02
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/query-ASTERIXDB-300/query-ASTERIXDB-300.1.adm
@@ -0,0 +1 @@
+[ { "id": 1234, "name": "John Doe", "age": 56, "salary": 50000, "dept": "HR" }, { "id": 3424, "name": "Roger Sanders", "age": 46, "salary": 60000, "dept": "Publishing" } ]
diff --git a/asterix-app/src/test/resources/runtimets/results/union/union/union.1.adm b/asterix-app/src/test/resources/runtimets/results/union/union/union.1.adm
index 69a9eb0..97b3d1a 100644
--- a/asterix-app/src/test/resources/runtimets/results/union/union/union.1.adm
+++ b/asterix-app/src/test/resources/runtimets/results/union/union/union.1.adm
@@ -1,15 +1,15 @@
-6
-11
-12
-14
 1
 2
+3
 4
-13
-15
+5
+6
+7
 8
 9
 10
-3
-5
-7
+11
+12
+13
+14
+15
diff --git a/asterix-app/src/test/resources/runtimets/results/union/union2/union2.1.adm b/asterix-app/src/test/resources/runtimets/results/union/union2/union2.1.adm
new file mode 100644
index 0000000..70b4777
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/union/union2/union2.1.adm
@@ -0,0 +1,20 @@
+{ "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "user-since": datetime("2012-08-20T10:10:00.000Z"), "friend-ids": {{ 2, 3, 6, 10 }}, "employment": [ { "organization-name": "Codetechno", "start-date": date("2006-08-06") } ] }
+{ "id": 1, "alias": "Margarita", "name": "MargaritaStoddard", "user-since": datetime("2012-08-20T10:10:00.000Z"), "friend-ids": {{ 2, 3, 6, 10 }}, "employment": [ { "organization-name": "Codetechno", "start-date": date("2006-08-06") } ] }
+{ "id": 2, "alias": "Isbel", "name": "IsbelDull", "user-since": datetime("2011-01-22T10:10:00.000Z"), "friend-ids": {{ 1, 4 }}, "employment": [ { "organization-name": "Hexviafind", "start-date": date("2010-04-27") } ] }
+{ "id": 2, "alias": "Isbel", "name": "IsbelDull", "user-since": datetime("2011-01-22T10:10:00.000Z"), "friend-ids": {{ 1, 4 }}, "employment": [ { "organization-name": "Hexviafind", "start-date": date("2010-04-27") } ] }
+{ "id": 3, "alias": "Emory", "name": "EmoryUnk", "user-since": datetime("2012-07-10T10:10:00.000Z"), "friend-ids": {{ 1, 5, 8, 9 }}, "employment": [ { "organization-name": "geomedia", "start-date": date("2010-06-17"), "end-date": date("2010-01-26") } ] }
+{ "id": 3, "alias": "Emory", "name": "EmoryUnk", "user-since": datetime("2012-07-10T10:10:00.000Z"), "friend-ids": {{ 1, 5, 8, 9 }}, "employment": [ { "organization-name": "geomedia", "start-date": date("2010-06-17"), "end-date": date("2010-01-26") } ] }
+{ "id": 4, "alias": "Nicholas", "name": "NicholasStroh", "user-since": datetime("2010-12-27T10:10:00.000Z"), "friend-ids": {{ 2 }}, "employment": [ { "organization-name": "Zamcorporation", "start-date": date("2010-06-08") } ] }
+{ "id": 4, "alias": "Nicholas", "name": "NicholasStroh", "user-since": datetime("2010-12-27T10:10:00.000Z"), "friend-ids": {{ 2 }}, "employment": [ { "organization-name": "Zamcorporation", "start-date": date("2010-06-08") } ] }
+{ "id": 5, "alias": "Von", "name": "VonKemble", "user-since": datetime("2010-01-05T10:10:00.000Z"), "friend-ids": {{ 3, 6, 10 }}, "employment": [ { "organization-name": "Kongreen", "start-date": date("2010-11-27") } ] }
+{ "id": 5, "alias": "Von", "name": "VonKemble", "user-since": datetime("2010-01-05T10:10:00.000Z"), "friend-ids": {{ 3, 6, 10 }}, "employment": [ { "organization-name": "Kongreen", "start-date": date("2010-11-27") } ] }
+{ "id": 6, "alias": "Willis", "name": "WillisWynne", "user-since": datetime("2005-01-17T10:10:00.000Z"), "friend-ids": {{ 1, 3, 7 }}, "employment": [ { "organization-name": "jaydax", "start-date": date("2009-05-15") } ] }
+{ "id": 6, "alias": "Willis", "name": "WillisWynne", "user-since": datetime("2005-01-17T10:10:00.000Z"), "friend-ids": {{ 1, 3, 7 }}, "employment": [ { "organization-name": "jaydax", "start-date": date("2009-05-15") } ] }
+{ "id": 7, "alias": "Suzanna", "name": "SuzannaTillson", "user-since": datetime("2012-08-07T10:10:00.000Z"), "friend-ids": {{ 6 }}, "employment": [ { "organization-name": "Labzatron", "start-date": date("2011-04-19") } ] }
+{ "id": 7, "alias": "Suzanna", "name": "SuzannaTillson", "user-since": datetime("2012-08-07T10:10:00.000Z"), "friend-ids": {{ 6 }}, "employment": [ { "organization-name": "Labzatron", "start-date": date("2011-04-19") } ] }
+{ "id": 8, "alias": "Nila", "name": "NilaMilliron", "user-since": datetime("2008-01-01T10:10:00.000Z"), "friend-ids": {{ 3 }}, "employment": [ { "organization-name": "Plexlane", "start-date": date("2010-02-28") } ] }
+{ "id": 8, "alias": "Nila", "name": "NilaMilliron", "user-since": datetime("2008-01-01T10:10:00.000Z"), "friend-ids": {{ 3 }}, "employment": [ { "organization-name": "Plexlane", "start-date": date("2010-02-28") } ] }
+{ "id": 9, "alias": "Woodrow", "name": "WoodrowNehling", "user-since": datetime("2005-09-20T10:10:00.000Z"), "friend-ids": {{ 3, 10 }}, "employment": [ { "organization-name": "Zuncan", "start-date": date("2003-04-22"), "end-date": date("2009-12-13") } ] }
+{ "id": 9, "alias": "Woodrow", "name": "WoodrowNehling", "user-since": datetime("2005-09-20T10:10:00.000Z"), "friend-ids": {{ 3, 10 }}, "employment": [ { "organization-name": "Zuncan", "start-date": date("2003-04-22"), "end-date": date("2009-12-13") } ] }
+{ "id": 10, "alias": "Bram", "name": "BramHatch", "user-since": datetime("2010-10-16T10:10:00.000Z"), "friend-ids": {{ 1, 5, 9 }}, "employment": [ { "organization-name": "physcane", "start-date": date("2007-06-05"), "end-date": date("2011-11-05") } ] }
+{ "id": 10, "alias": "Bram", "name": "BramHatch", "user-since": datetime("2010-10-16T10:10:00.000Z"), "friend-ids": {{ 1, 5, 9 }}, "employment": [ { "organization-name": "physcane", "start-date": date("2007-06-05"), "end-date": date("2011-11-05") } ] }
diff --git a/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/custord/join_q_04/join_q_04.1.ast b/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/custord/join_q_04/join_q_04.1.ast
index e69de29..7509a02 100644
--- a/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/custord/join_q_04/join_q_04.1.ast
+++ b/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/custord/join_q_04/join_q_04.1.ast
@@ -0,0 +1,36 @@
+DataverseUse test
+TypeDecl AddressType [
+  open RecordType {
+    number : int64,
+    street : string,
+    city : string
+  }
+]
+TypeDecl CustomerType [
+  closed RecordType {
+    cid : int64,
+    name : string,
+    cashBack : int64,
+    age : int64?,
+    address : AddressType?,
+    lastorder :       open RecordType {
+        oid : int64,
+        total : float
+      }
+
+  }
+]
+TypeDecl OrderType [
+  open RecordType {
+    oid : int64,
+    cid : int64,
+    orderstatus : string,
+    orderpriority : string,
+    clerk : string,
+    total : float,
+    items : OrderedList [int64]
+
+  }
+]
+DatasetDecl Customers(CustomerType)is an external dataset
+DatasetDecl Orders(OrderType)is an external dataset
diff --git a/asterix-app/src/test/resources/runtimets/testsuite.xml b/asterix-app/src/test/resources/runtimets/testsuite.xml
index a506bd3..a3a1fba 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite.xml
@@ -177,6 +177,36 @@
                 <output-dir compare="Text">union</output-dir>
             </compilation-unit>
         </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="union2">
+                <output-dir compare="Text">union2</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-300">
+                <output-dir compare="Text">query-ASTERIXDB-300</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-1205">
+                <output-dir compare="Text">query-ASTERIXDB-1205</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-1205-2">
+                <output-dir compare="Text">query-ASTERIXDB-1205-2</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-1205-3">
+                <output-dir compare="Text">query-ASTERIXDB-1205-3</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="union">
+            <compilation-unit name="query-ASTERIXDB-1047">
+                <output-dir compare="Text">query-ASTERIXDB-1047</output-dir>
+            </compilation-unit>
+        </test-case>
     </test-group>
     <test-case FilePath="flwor">
         <compilation-unit name="let33">
diff --git a/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 2965883..d289400 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -1298,6 +1298,22 @@
             </compilation-unit>
         </test-case>
         <test-case FilePath="custord">
+            <compilation-unit name="join_q_05">
+                <output-dir compare="Text">join_q_01</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="custord">
+            <compilation-unit name="join_q_06">
+                <output-dir compare="Text">join_q_06</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="custord">
+            <compilation-unit name="join_q_07">
+                <output-dir compare="Text">join_q_06</output-dir>
+                 <expected-error>org.apache.hyracks.algebricks.common.exceptions.AlgebricksException: Could not resolve type for function-call: asterix:scan-collection, Args:[%0->$$8],please check whether the used variables has been defined!</expected-error>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="custord">
             <compilation-unit name="load-test">
                 <output-dir compare="Text">load-test</output-dir>
             </compilation-unit>
diff --git a/asterix-om/src/main/java/org/apache/asterix/om/functions/AsterixBuiltinFunctions.java b/asterix-om/src/main/java/org/apache/asterix/om/functions/AsterixBuiltinFunctions.java
index c2277fa..997dccd 100644
--- a/asterix-om/src/main/java/org/apache/asterix/om/functions/AsterixBuiltinFunctions.java
+++ b/asterix-om/src/main/java/org/apache/asterix/om/functions/AsterixBuiltinFunctions.java
@@ -701,8 +701,6 @@
     public final static FunctionIdentifier GET_POINTS_LINE_RECTANGLE_POLYGON_ACCESSOR = new FunctionIdentifier(
             FunctionConstants.ASTERIX_NS, "get-points", 1);
 
-    public final static FunctionIdentifier UNION = new FunctionIdentifier(FunctionConstants.ASTERIX_NS, "union", 2);
-
     public static final FunctionIdentifier EQ = AlgebricksBuiltinFunctions.EQ;
     public static final FunctionIdentifier LE = AlgebricksBuiltinFunctions.LE;
     public static final FunctionIdentifier GE = AlgebricksBuiltinFunctions.GE;
@@ -941,7 +939,6 @@
         addFunction(TID, AInt64TypeComputer.INSTANCE, true);
         addFunction(TIME_CONSTRUCTOR, OptionalATimeTypeComputer.INSTANCE, true);
         addPrivateFunction(TYPE_OF, null, true);
-        addPrivateFunction(UNION, UnorderedListConstructorResultType.INSTANCE, true);
         addPrivateFunction(UNORDERED_LIST_CONSTRUCTOR, UnorderedListConstructorResultType.INSTANCE, true);
         addFunction(WORD_TOKENS, OrderedListOfAStringTypeComputer.INSTANCE, true);