diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/base/RuleCollections.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/base/RuleCollections.java
index f14fff8..587cbc5 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/base/RuleCollections.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/base/RuleCollections.java
@@ -36,6 +36,7 @@
 import edu.uci.ics.asterix.optimizer.rules.IntroduceStaticTypeCastRule;
 import edu.uci.ics.asterix.optimizer.rules.LoadRecordFieldsRule;
 import edu.uci.ics.asterix.optimizer.rules.NestGroupByRule;
+import edu.uci.ics.asterix.optimizer.rules.NestedSubplanToJoinRule;
 import edu.uci.ics.asterix.optimizer.rules.PullPositionalVariableFromUnnestRule;
 import edu.uci.ics.asterix.optimizer.rules.PushAggFuncIntoStandaloneAggregateRule;
 import edu.uci.ics.asterix.optimizer.rules.PushAggregateIntoGroupbyRule;
@@ -163,6 +164,7 @@
     public final static List<IAlgebraicRewriteRule> buildLoadFieldsRuleCollection() {
         List<IAlgebraicRewriteRule> fieldLoads = new LinkedList<IAlgebraicRewriteRule>();
         fieldLoads.add(new LoadRecordFieldsRule());
+        fieldLoads.add(new NestedSubplanToJoinRule());
         fieldLoads.add(new PushFieldAccessRule());
         // fieldLoads.add(new ByNameToByHandleFieldAccessRule()); -- disabled
         fieldLoads.add(new ByNameToByIndexFieldAccessRule());
@@ -192,6 +194,7 @@
         consolidation.add(new CountVarToCountOneRule());
         consolidation.add(new RemoveUnusedAssignAndAggregateRule());
         consolidation.add(new RemoveRedundantGroupByDecorVars());
+        consolidation.add(new NestedSubplanToJoinRule());
         return consolidation;
     }
 
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/NestedSubplanToJoinRule.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/NestedSubplanToJoinRule.java
new file mode 100644
index 0000000..376efc4
--- /dev/null
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/NestedSubplanToJoinRule.java
@@ -0,0 +1,108 @@
+/*
+ * Copyright 2009-2013 by The Regents of the University of California
+ * Licensed 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 from
+ * 
+ *     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 edu.uci.ics.asterix.optimizer.rules;
+
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+import org.apache.commons.lang3.mutable.Mutable;
+import org.apache.commons.lang3.mutable.MutableObject;
+
+import edu.uci.ics.hyracks.algebricks.common.exceptions.AlgebricksException;
+import edu.uci.ics.hyracks.algebricks.core.algebra.base.ILogicalExpression;
+import edu.uci.ics.hyracks.algebricks.core.algebra.base.ILogicalOperator;
+import edu.uci.ics.hyracks.algebricks.core.algebra.base.ILogicalPlan;
+import edu.uci.ics.hyracks.algebricks.core.algebra.base.IOptimizationContext;
+import edu.uci.ics.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
+import edu.uci.ics.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import edu.uci.ics.hyracks.algebricks.core.algebra.expressions.ConstantExpression;
+import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator;
+import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.AbstractOperatorWithNestedPlans;
+import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.EmptyTupleSourceOperator;
+import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.LeftOuterJoinOperator;
+import edu.uci.ics.hyracks.algebricks.core.algebra.util.OperatorPropertiesUtil;
+import edu.uci.ics.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule;
+
+public class NestedSubplanToJoinRule implements IAlgebraicRewriteRule {
+
+    @Override
+    public boolean rewritePre(Mutable<ILogicalOperator> opRef, IOptimizationContext context) throws AlgebricksException {
+        return false;
+    }
+
+    @Override
+    public boolean rewritePost(Mutable<ILogicalOperator> opRef, IOptimizationContext context)
+            throws AlgebricksException {
+        if (context.checkIfInDontApplySet(this, opRef.getValue()))
+            return false;
+        context.addToDontApplySet(this, opRef.getValue());
+
+        ILogicalOperator op1 = opRef.getValue();
+        if (op1.getInputs().size() == 0) {
+            return false;
+        }
+
+        boolean rewritten = false;
+        for (int index = 0; index < op1.getInputs().size(); index++) {
+            AbstractLogicalOperator child = (AbstractLogicalOperator) op1.getInputs().get(index).getValue();
+            if (child.getOperatorTag() != LogicalOperatorTag.SUBPLAN) {
+                continue;
+            }
+
+            AbstractOperatorWithNestedPlans subplan = (AbstractOperatorWithNestedPlans) child;
+            Set<LogicalVariable> freeVars = new HashSet<LogicalVariable>();
+            OperatorPropertiesUtil.getFreeVariablesInSubplans(subplan, freeVars);
+            if (!freeVars.isEmpty()) {
+                continue;
+            }
+
+            //we only deals with the first subplan for now
+            ILogicalOperator subplanInput = subplan.getInputs().get(0).getValue();
+            List<ILogicalPlan> nestedPlans = subplan.getNestedPlans();
+            ILogicalPlan nestedPlan = nestedPlans.get(0);
+            List<Mutable<ILogicalOperator>> nestedRoots = nestedPlan.getRoots();
+
+            //expend the input and roots into a DAG of nested loop joins
+            Mutable<ILogicalExpression> expr = new MutableObject<ILogicalExpression>(ConstantExpression.TRUE);
+            Mutable<ILogicalOperator> nestedRootRef = nestedRoots.get(0);
+            ILogicalOperator join = new LeftOuterJoinOperator(expr, new MutableObject<ILogicalOperator>(subplanInput),
+                    nestedRootRef);
+
+            //rewrite the nested tuple source to be empty tuple source
+            rewriteNestedTupleSource(nestedRootRef);
+
+            for (int i = 1; i < nestedRoots.size(); i++) {
+                join = new LeftOuterJoinOperator(expr, new MutableObject<ILogicalOperator>(join), nestedRoots.get(i));
+            }
+            op1.getInputs().get(index).setValue(join);
+            context.computeAndSetTypeEnvironmentForOperator(join);
+            rewritten = true;
+        }
+        return rewritten;
+    }
+
+    private void rewriteNestedTupleSource(Mutable<ILogicalOperator> nestedRootRef) {
+        AbstractLogicalOperator nestedRoot = (AbstractLogicalOperator) nestedRootRef.getValue();
+        if (nestedRoot.getOperatorTag() == LogicalOperatorTag.NESTEDTUPLESOURCE) {
+            nestedRootRef.setValue(new EmptyTupleSourceOperator());
+        }
+        List<Mutable<ILogicalOperator>> inputs = nestedRoot.getInputs();
+        for (Mutable<ILogicalOperator> input : inputs) {
+            rewriteNestedTupleSource(input);
+        }
+    }
+}
diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggFuncIntoStandaloneAggregateRule.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggFuncIntoStandaloneAggregateRule.java
index c5a1cb0..bee8c40 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggFuncIntoStandaloneAggregateRule.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggFuncIntoStandaloneAggregateRule.java
@@ -111,19 +111,7 @@
         
         // The assign now just "renames" the variable to make sure the upstream plan still works.
         srcAssignExprRef.setValue(new VariableReferenceExpression(aggVar));
-
-        // Create a new assign for a TRUE variable.
-        LogicalVariable trueVar = context.newVar();
-        AssignOperator trueAssignOp = new AssignOperator(trueVar, new MutableObject<ILogicalExpression>(ConstantExpression.TRUE));
         
-        ILogicalOperator aggInput = aggOp.getInputs().get(0).getValue();
-        aggOp.getInputs().get(0).setValue(trueAssignOp);
-        trueAssignOp.getInputs().add(new MutableObject<ILogicalOperator>(aggInput));
-        
-        // Set partitioning variable.
-        aggOp.setPartitioningVariable(trueVar);
-        
-        context.computeAndSetTypeEnvironmentForOperator(trueAssignOp);
         context.computeAndSetTypeEnvironmentForOperator(aggOp);
         context.computeAndSetTypeEnvironmentForOperator(assignOp);
         
diff --git a/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue196/query-issue196.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue196/query-issue196.1.ddl.aql
new file mode 100644
index 0000000..59e814a
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue196/query-issue196.1.ddl.aql
@@ -0,0 +1,22 @@
+/*
+ * Description  : This test case is to verify the fix for issue196
+ 				: https://code.google.com/p/asterixdb/issues/detail?id=196
+ * Expected Res : Success
+ * Date         : 5th May 2013
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type testtype1 as open {
+id : int32
+}
+
+create type testtype2 as open {
+id : int32
+}
+
+create dataset t1(testtype1) primary key id;
+create dataset t2(testtype2) primary key id;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue196/query-issue196.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue196/query-issue196.2.update.aql
new file mode 100644
index 0000000..18756f9
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue196/query-issue196.2.update.aql
@@ -0,0 +1,20 @@
+/*
+ * Description  : This test case is to verify the fix for issue196
+ 				: https://code.google.com/p/asterixdb/issues/detail?id=196
+ * Expected Res : Success
+ * Date         : 5th May 2013
+ */
+
+use dataverse test;
+
+insert into dataset t1({"id":24});
+insert into dataset t1({"id":23});
+insert into dataset t1({"id":21});
+insert into dataset t1({"id":44});
+insert into dataset t1({"id":64});
+
+insert into dataset t2({"id":24});
+insert into dataset t2({"id":23});
+insert into dataset t2({"id":21});
+insert into dataset t2({"id":44});
+insert into dataset t2({"id":64});
diff --git a/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue196/query-issue196.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue196/query-issue196.3.query.aql
new file mode 100644
index 0000000..43d1980
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/open-closed/query-issue196/query-issue196.3.query.aql
@@ -0,0 +1,12 @@
+/*
+ * Description  : This test case is to verify the fix for issue196
+ 				: https://code.google.com/p/asterixdb/issues/detail?id=196
+ * Expected Res : Success
+ * Date         : 5th May 2013
+ */
+
+use dataverse test;
+
+let $a := (for $l in dataset('t1') order by $l.id return $l)
+let $b := (for $m in dataset('t2') order by $m.id return $m)
+return {"a":$a,"b":$b}
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/runtimets/results/open-closed/query-issue196/query-issue196.1.adm b/asterix-app/src/test/resources/runtimets/results/open-closed/query-issue196/query-issue196.1.adm
new file mode 100644
index 0000000..f93766f
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/open-closed/query-issue196/query-issue196.1.adm
@@ -0,0 +1 @@
+{ "a": [ { "id": 21 }, { "id": 23 }, { "id": 24 }, { "id": 44 }, { "id": 64 } ], "b": [ { "id": 21 }, { "id": 23 }, { "id": 24 }, { "id": 44 }, { "id": 64 } ] }
diff --git a/asterix-app/src/test/resources/runtimets/testsuite.xml b/asterix-app/src/test/resources/runtimets/testsuite.xml
index 3314b75..e39d2b0 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite.xml
@@ -2681,6 +2681,11 @@
         <output-dir compare="Text">query-issue343-2</output-dir>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="open-closed">
+      <compilation-unit name="query-issue196">
+        <output-dir compare="Text">query-issue196</output-dir>
+      </compilation-unit>
+    </test-case>
   </test-group>
   <test-group name="quantifiers">
     <test-case FilePath="quantifiers">