diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/schema/ExpectedSchemaBuilder.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/schema/ExpectedSchemaBuilder.java
index e442d64..23da417 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/schema/ExpectedSchemaBuilder.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/pushdown/schema/ExpectedSchemaBuilder.java
@@ -56,6 +56,11 @@
 
     public boolean setSchemaFromExpression(AbstractFunctionCallExpression expr, LogicalVariable producedVar,
             IVariableTypeEnvironment typeEnv) throws AlgebricksException {
+        return buildExpectedSchemaNodes(expr, producedVar, typeEnv);
+    }
+
+    public boolean setSchemaFromCalculatedExpression(AbstractFunctionCallExpression expr, LogicalVariable producedVar,
+            IVariableTypeEnvironment typeEnv) throws AlgebricksException {
         //Parent always nested
         AbstractComplexExpectedSchemaNode parent = (AbstractComplexExpectedSchemaNode) buildNestedNode(expr, typeEnv);
         if (parent != null) {
@@ -104,6 +109,71 @@
         return varToNode.get(variable);
     }
 
+    private boolean buildExpectedSchemaNodes(ILogicalExpression expr, LogicalVariable producedVar,
+            IVariableTypeEnvironment typeEnv) throws AlgebricksException {
+        return buildNestedNodes(expr, producedVar, typeEnv);
+    }
+
+    private boolean buildNestedNodes(ILogicalExpression expr, LogicalVariable producedVar,
+            IVariableTypeEnvironment typeEnv) throws AlgebricksException {
+        //The current node expression
+        boolean changed = false;
+        if (expr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
+            return false;
+        }
+        AbstractFunctionCallExpression myExpr = (AbstractFunctionCallExpression) expr;
+        if (!SUPPORTED_FUNCTIONS.contains(myExpr.getFunctionIdentifier()) || noArgsOrFirstArgIsConstant(myExpr)) {
+            // Check if the function consists of the Supported Functions
+            for (Mutable<ILogicalExpression> arg : myExpr.getArguments()) {
+                changed |= buildNestedNodes(arg.getValue(), producedVar, typeEnv);
+            }
+            return changed;
+        }
+        // if the child is not a function expression, then just one node.
+        if (BuiltinFunctions.ARRAY_STAR.equals(myExpr.getFunctionIdentifier())
+                || BuiltinFunctions.SCAN_COLLECTION.equals(myExpr.getFunctionIdentifier())) {
+            // these supported function won't have second child
+            IExpectedSchemaNode expectedSchemaNode = buildNestedNode(expr, typeEnv);
+            if (expectedSchemaNode != null) {
+                changed |=
+                        setSchemaFromCalculatedExpression((AbstractFunctionCallExpression) expr, producedVar, typeEnv);
+            }
+        } else {
+            ILogicalExpression childExpr = myExpr.getArguments().get(1).getValue();
+            if (childExpr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
+                // must be a variable or constant
+                IExpectedSchemaNode expectedSchemaNode = buildNestedNode(expr, typeEnv);
+                if (expectedSchemaNode != null) {
+                    changed |= setSchemaFromCalculatedExpression((AbstractFunctionCallExpression) expr, producedVar,
+                            typeEnv);
+                }
+            } else {
+                // as the childExpr is a function.
+                // if the function had been evaluated at compile time, it would have been
+                // evaluated at this stage of compilation.
+                // eg: field-access(t.r.p, substring("name",2,4))
+                // this will be evaluated to field-access(t.r.p, "me") at compile time itself.
+                // since the execution reached this branch, this means the childExpr
+                // need to be evaluated at runtime, hence the childExpr should also be checked
+                // for possible pushdown.
+                // eg: field-access(t.r.p, substring(x.y.age_field, 0, 4))
+                ILogicalExpression parentExpr = myExpr.getArguments().get(0).getValue();
+                IExpectedSchemaNode parentExpectedNode = buildNestedNode(parentExpr, typeEnv);
+                if (parentExpectedNode != null) {
+                    changed |= setSchemaFromCalculatedExpression((AbstractFunctionCallExpression) parentExpr,
+                            producedVar, typeEnv);
+                }
+                changed |= buildNestedNodes(childExpr, producedVar, typeEnv);
+            }
+        }
+        return changed;
+    }
+
+    private boolean noArgsOrFirstArgIsConstant(AbstractFunctionCallExpression myExpr) {
+        List<Mutable<ILogicalExpression>> args = myExpr.getArguments();
+        return args.isEmpty() || args.get(0).getValue().getExpressionTag() == LogicalExpressionTag.CONSTANT;
+    }
+
     private IExpectedSchemaNode buildNestedNode(ILogicalExpression expr, IVariableTypeEnvironment typeEnv)
             throws AlgebricksException {
         //The current node expression
@@ -142,11 +212,6 @@
         return null;
     }
 
-    private boolean noArgsOrFirstArgIsConstant(AbstractFunctionCallExpression myExpr) {
-        List<Mutable<ILogicalExpression>> args = myExpr.getArguments();
-        return args.isEmpty() || args.get(0).getValue().getExpressionTag() == LogicalExpressionTag.CONSTANT;
-    }
-
     private IExpectedSchemaNode changeNodeForVariable(LogicalVariable sourceVar,
             AbstractFunctionCallExpression parentExpression, ILogicalExpression expression) {
         //Get the associated node with the sourceVar (if any)
diff --git a/asterixdb/asterix-app/data/hdfs/parquet/friends.json b/asterixdb/asterix-app/data/hdfs/parquet/friends.json
new file mode 100644
index 0000000..d708ad9
--- /dev/null
+++ b/asterixdb/asterix-app/data/hdfs/parquet/friends.json
@@ -0,0 +1 @@
+{ "id": "1", "name": "Monica", "x": { "y": { "age_field": "age" } }, "t": { "r": { "p": { "age": "26" } } } }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/external_dataset/ExternalDatasetTestUtils.java b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/external_dataset/ExternalDatasetTestUtils.java
index 7e7b6f5..c94fd60 100644
--- a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/external_dataset/ExternalDatasetTestUtils.java
+++ b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/external_dataset/ExternalDatasetTestUtils.java
@@ -395,6 +395,7 @@
         loadData(generatedDataBasePath, "", "heterogeneous_1.parquet", definition, definitionSegment, false, false);
         loadData(generatedDataBasePath, "", "heterogeneous_2.parquet", definition, definitionSegment, false, false);
         loadData(generatedDataBasePath, "", "parquetTypes.parquet", definition, definitionSegment, false, false);
+        loadData(generatedDataBasePath, "", "friends.parquet", definition, definitionSegment, false, false);
 
         Collection<File> files =
                 IoUtil.getMatchingFiles(Paths.get(generatedDataBasePath + "/external-filter"), PARQUET_FILTER);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.01.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.01.ddl.sqlpp
new file mode 100644
index 0000000..a601a8d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.01.ddl.sqlpp
@@ -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.
+ */
+/*
+* Description  : Field access pushdown
+* Expected Res : Success
+* Date         : June 22nd 2020
+*/
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+
+USE test;
+
+
+CREATE TYPE ParquetType as {
+};
+
+CREATE EXTERNAL DATASET ParquetDataset(ParquetType) USING %adapter%
+(
+  %template%,
+  ("container"="playground"),
+  ("definition"="parquet-data/reviews"),
+  ("include"="*friends.parquet"),
+  ("format" = "parquet")
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.02.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.02.query.sqlpp
new file mode 100644
index 0000000..e72d412
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.02.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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;
+
+SET `compiler.external.field.pushdown` "true";
+
+EXPLAIN
+SELECT t.r.g, `field-access-by-name`(t.r.p, x.y.age_field)
+FROM ParquetDataset;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.03.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.03.query.sqlpp
new file mode 100644
index 0000000..d15ba8d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.03.query.sqlpp
@@ -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.
+ */
+
+USE test;
+
+SET `compiler.external.field.pushdown` "true";
+
+SELECT t.r.g, `field-access-by-name`(t.r.p, x.y.age_field)
+FROM ParquetDataset;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.1.ddl.sqlpp
new file mode 100644
index 0000000..92ebdfd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.1.ddl.sqlpp
@@ -0,0 +1,67 @@
+/*
+ * 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 test;
+
+create type t1 as {
+    _id: uuid
+    };
+
+create dataset dat1(t1) primary key _id autogenerated;
+
+CREATE VIEW dat2 (id STRING, a STRING) DEFAULT NULL PRIMARY KEY (id) NOT ENFORCED AS
+SELECT
+    s.id,
+    a
+FROM dat1  s
+    LET
+        w1 = (
+            SELECT SUM(x.a) , COUNT(x) , SUM(x.b) ,
+                SUM(x.c), SUM(x.d)
+            FROM s.a x
+        )[0],
+        w2 = (
+            SELECT SUM(x.a) , COUNT(x)
+            FROM s.b x
+        )[0],
+        w3 = (SELECT SUM(x.a) FROM s.c x),
+        w4= (SELECT count(*) FROM dat1);
+
+CREATE VIEW dat3 (id STRING, a STRING, d INT) DEFAULT NULL PRIMARY KEY (id) NOT ENFORCED AS
+SELECT
+    s.id,
+    a,
+    s.d
+FROM dat1  s
+    LET
+        w1 = (
+            SELECT SUM(x.a) , COUNT(x) , SUM(x.b) ,
+                SUM(x.c), SUM(x.d)
+            FROM s.a x
+        )[0],
+        w2 = (
+            SELECT SUM(x.a) , COUNT(x)
+            FROM s.b x
+        )[0],
+        w3 = (SELECT SUM(x.a) FROM s.c x),
+        w4= (SELECT RAW count(*) FROM s.e)[0]
+WHERE s.d <= w4;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.2.update.sqlpp
new file mode 100644
index 0000000..5901544
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.2.update.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;
+
+insert into dat1 ([
+{ "id": "1", "a": "2", "c":{ "b": 111 } , "d" :8  , "e" : [1] },
+    { "id": "2", "a": "3", "c":{ "b": 222 }  , "d" :7 , "e" : [2,3]  },
+    { "id": "3", "a": "4", "c":{ "b": 333 } , "d" :6 , "e" : [4,5,6]  },
+    { "id": "4", "a": "5","c": { "b": 444 } , "d" :5  , "e" : [7,8,9,10] },
+    { "id": "5", "a": "6", "c":{ "b": 555 } , "d" :4 , "e" : [10,11,12,13,14]  },
+    { "id": "6", "a": "7","c": { "b": 666 } , "d" :3 , "e" : [13,14,15,16,17,18]  },
+    { "id": "7", "a": "8","c": { "b": 777 } , "d" :2 , "e" : [16,17,18,19,20,21,22] },
+    { "id": "8", "a": "9", "c":{ "b": 888 } , "d" :1 , "e" : [19,20,21,22,23,24,25,26] }
+    ]);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.query.sqlpp
new file mode 100644
index 0000000..a7397a0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.query.sqlpp
@@ -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.
+ */
+
+
+use test;
+
+
+EXPLAIN
+SELECT id FROM dat2;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.query.sqlpp
new file mode 100644
index 0000000..7aa5872
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.query.sqlpp
@@ -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.
+ */
+
+
+use test;
+
+
+
+SELECT * FROM dat2 order by id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.query.sqlpp
new file mode 100644
index 0000000..d71470c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.query.sqlpp
@@ -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.
+ */
+
+
+use test;
+
+EXPLAIN
+SELECT * FROM dat3;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.query.sqlpp
new file mode 100644
index 0000000..deacf2f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.query.sqlpp
@@ -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 test;
+
+
+SELECT * FROM dat3 order by id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.02.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.02.plan
new file mode 100644
index 0000000..4806a28
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.02.plan
@@ -0,0 +1 @@
+"distribute result [$$24] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0]\n-- DISTRIBUTE_RESULT  |PARTITIONED|\n  exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0]\n  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|\n    project ([$$24]) [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0]\n    -- STREAM_PROJECT  |PARTITIONED|\n      assign [$$24] <- [{\"g\": $$25.getField(\"g\"), \"$1\": $$25.getField(\"p\").getField(\"$$ParquetDataset.getField(\"x\").getField(\"y\").getField(\"age_field\")\")}] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0]\n      -- ASSIGN  |PARTITIONED|\n        assign [$$25] <- [$$ParquetDataset.getField(\"t\").getField(\"r\")] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0]\n        -- ASSIGN  |PARTITIONED|\n          exchange [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0]\n          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|\n            data-scan []<-[$$ParquetDataset] <- test.ParquetDataset project ({t:{r:{p:any,g:any}},x:{y:{age_field:any}}}) [cardinality: 1000000.0, op-cost: 1000000.0, total-cost: 1000000.0]\n            -- DATASOURCE_SCAN  |PARTITIONED|\n              exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]\n              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|\n                empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]\n                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|\n"
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.03.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.03.adm
new file mode 100644
index 0000000..2246335
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/external-dataset/common/parquet/ASTERIXDB-3540/ASTERIXDB-3540.03.adm
@@ -0,0 +1 @@
+{ "$1": "26" }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan
new file mode 100644
index 0000000..9e9db49
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan
@@ -0,0 +1,20 @@
+distribute result [$$311] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    assign [$$311] <- [{"id": $$345}] project: [$$311] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+    -- ASSIGN  |PARTITIONED|
+      select (not(is-null($$345))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+      -- STREAM_SELECT  |PARTITIONED|
+        assign [$$345] <- [string-default-null($$s.getField("id"))] project: [$$345] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+        -- ASSIGN  |PARTITIONED|
+          project ([$$s]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+          -- STREAM_PROJECT  |PARTITIONED|
+            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              data-scan []<-[$$314, $$s] <- test.dat1 [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+              -- DATASOURCE_SCAN  |PARTITIONED|
+                exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.adm
new file mode 100644
index 0000000..934eb579
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.4.adm
@@ -0,0 +1,8 @@
+{ "dat2": { "id": "1", "a": "2" } }
+{ "dat2": { "id": "2", "a": "3" } }
+{ "dat2": { "id": "3", "a": "4" } }
+{ "dat2": { "id": "4", "a": "5" } }
+{ "dat2": { "id": "5", "a": "6" } }
+{ "dat2": { "id": "6", "a": "7" } }
+{ "dat2": { "id": "7", "a": "8" } }
+{ "dat2": { "id": "8", "a": "9" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan
new file mode 100644
index 0000000..6f838fa
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan
@@ -0,0 +1,35 @@
+distribute result [$$317] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    assign [$$317] <- [{"dat3": {"id": $$351, "a": string-default-null($$345), "d": int64-default-null($$318)}}] project: [$$317] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+    -- ASSIGN  |PARTITIONED|
+      select (le($$318, get-item($$295, 0))) project: [$$351, $$345, $$318] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+      -- STREAM_SELECT  |PARTITIONED|
+        project ([$$351, $$345, $$318, $$295]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+        -- STREAM_PROJECT  |PARTITIONED|
+          subplan {
+                    aggregate [$$295] <- [listify($$330)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                    -- AGGREGATE  |LOCAL|
+                      aggregate [$$330] <- [agg-sql-count(1)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                      -- AGGREGATE  |LOCAL|
+                        unnest $$333 <- scan-collection($$343) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                        -- UNNEST  |LOCAL|
+                          nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                 } [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+          -- SUBPLAN  |PARTITIONED|
+            select (not(is-null($$351))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+            -- STREAM_SELECT  |PARTITIONED|
+              assign [$$351, $$345, $$318, $$343] <- [string-default-null($$s.getField("id")), $$s.getField("a"), $$s.getField("d"), $$s.getField("e")] project: [$$351, $$345, $$318, $$343] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+              -- ASSIGN  |PARTITIONED|
+                project ([$$s]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                -- STREAM_PROJECT  |PARTITIONED|
+                  exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    data-scan []<-[$$319, $$s] <- test.dat1 [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.adm
new file mode 100644
index 0000000..65999fc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.6.adm
@@ -0,0 +1,4 @@
+{ "dat3": { "id": "5", "a": "6", "d": 4 } }
+{ "dat3": { "id": "6", "a": "7", "d": 3 } }
+{ "dat3": { "id": "7", "a": "8", "d": 2 } }
+{ "dat3": { "id": "8", "a": "9", "d": 1 } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan
new file mode 100644
index 0000000..8d2d305
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan
@@ -0,0 +1,20 @@
+distribute result [$$311] [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  exchange [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    assign [$$311] <- [{"id": $$345}] project: [$$311] [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+    -- ASSIGN  |PARTITIONED|
+      select (not(is-null($$345))) [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+      -- STREAM_SELECT  |PARTITIONED|
+        assign [$$345] <- [string-default-null($$s.getField("id"))] project: [$$345] [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+        -- ASSIGN  |PARTITIONED|
+          project ([$$s]) [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+          -- STREAM_PROJECT  |PARTITIONED|
+            exchange [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              data-scan []<-[$$314, $$s] <- test.dat1 [cardinality: 8.0, op-cost: 8.0, total-cost: 8.0]
+              -- DATASOURCE_SCAN  |PARTITIONED|
+                exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan
new file mode 100644
index 0000000..efa194b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan
@@ -0,0 +1,35 @@
+distribute result [$$317] [cardinality: 4.0, op-cost: 0.0, total-cost: 8.0]
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  exchange [cardinality: 4.0, op-cost: 0.0, total-cost: 8.0]
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    assign [$$317] <- [{"dat3": {"id": $$351, "a": string-default-null($$345), "d": int64-default-null($$318)}}] project: [$$317] [cardinality: 4.0, op-cost: 0.0, total-cost: 8.0]
+    -- ASSIGN  |PARTITIONED|
+      select (le($$318, get-item($$295, 0))) project: [$$351, $$345, $$318] [cardinality: 4.0, op-cost: 0.0, total-cost: 8.0]
+      -- STREAM_SELECT  |PARTITIONED|
+        project ([$$351, $$345, $$318, $$295]) [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+        -- STREAM_PROJECT  |PARTITIONED|
+          subplan {
+                    aggregate [$$295] <- [listify($$330)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                    -- AGGREGATE  |LOCAL|
+                      aggregate [$$330] <- [agg-sql-count(1)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                      -- AGGREGATE  |LOCAL|
+                        unnest $$333 <- scan-collection($$343) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                        -- UNNEST  |LOCAL|
+                          nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                 } [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+          -- SUBPLAN  |PARTITIONED|
+            select (not(is-null($$351))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+            -- STREAM_SELECT  |PARTITIONED|
+              assign [$$351, $$345, $$318, $$343] <- [string-default-null($$s.getField("id")), $$s.getField("a"), $$s.getField("d"), $$s.getField("e")] project: [$$351, $$345, $$318, $$343] [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+              -- ASSIGN  |PARTITIONED|
+                project ([$$s]) [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+                -- STREAM_PROJECT  |PARTITIONED|
+                  exchange [cardinality: 8.0, op-cost: 0.0, total-cost: 8.0]
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    data-scan []<-[$$319, $$s] <- test.dat1 [cardinality: 8.0, op-cost: 8.0, total-cost: 8.0]
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_column/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_column/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan
new file mode 100644
index 0000000..34d7663
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_column/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.3.plan
@@ -0,0 +1,20 @@
+distribute result [$$311] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    assign [$$311] <- [{"id": $$345}] project: [$$311] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+    -- ASSIGN  |PARTITIONED|
+      select (not(is-null($$345))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+      -- STREAM_SELECT  |PARTITIONED|
+        assign [$$345] <- [string-default-null($$s.getField("id"))] project: [$$345] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+        -- ASSIGN  |PARTITIONED|
+          project ([$$s]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+          -- STREAM_PROJECT  |PARTITIONED|
+            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              data-scan []<-[$$314, $$s] <- test.dat1 project ({id:any}) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+              -- DATASOURCE_SCAN  |PARTITIONED|
+                exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_column/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_column/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan
new file mode 100644
index 0000000..c94e333
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_column/subquery/query-ASTERIXDB-3538/query-ASTERIXDB-3538.5.plan
@@ -0,0 +1,35 @@
+distribute result [$$317] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    assign [$$317] <- [{"dat3": {"id": $$351, "a": string-default-null($$345), "d": int64-default-null($$318)}}] project: [$$317] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+    -- ASSIGN  |PARTITIONED|
+      select (le($$318, get-item($$295, 0))) project: [$$351, $$345, $$318] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+      -- STREAM_SELECT  |PARTITIONED|
+        project ([$$351, $$345, $$318, $$295]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+        -- STREAM_PROJECT  |PARTITIONED|
+          subplan {
+                    aggregate [$$295] <- [listify($$330)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                    -- AGGREGATE  |LOCAL|
+                      aggregate [$$330] <- [agg-sql-count(1)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                      -- AGGREGATE  |LOCAL|
+                        unnest $$333 <- scan-collection($$343) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                        -- UNNEST  |LOCAL|
+                          nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                 } [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+          -- SUBPLAN  |PARTITIONED|
+            select (not(is-null($$351))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+            -- STREAM_SELECT  |PARTITIONED|
+              assign [$$351, $$345, $$318, $$343] <- [string-default-null($$s.getField("id")), $$s.getField("a"), $$s.getField("d"), $$s.getField("e")] project: [$$351, $$345, $$318, $$343] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+              -- ASSIGN  |PARTITIONED|
+                project ([$$s]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                -- STREAM_PROJECT  |PARTITIONED|
+                  exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    data-scan []<-[$$319, $$s] <- test.dat1 project ({a:any,d:any,e:[any],id:any}) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                    -- DATASOURCE_SCAN  |PARTITIONED|
+                      exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
index bbeb517..4b8d802 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
@@ -11818,6 +11818,11 @@
         <output-dir compare="Text">query-ASTERIXDB-3006</output-dir>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="subquery">
+      <compilation-unit name="query-ASTERIXDB-3538">
+        <output-dir compare="Text">query-ASTERIXDB-3538</output-dir>
+      </compilation-unit>
+    </test-case>
   </test-group>
   <test-group name="subset-collection">
     <test-case FilePath="subset-collection">
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_external_dataset_s3.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_external_dataset_s3.xml
index c31cb5e..8a5151c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_external_dataset_s3.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_external_dataset_s3.xml
@@ -391,6 +391,12 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="external-dataset">
+      <compilation-unit name="common/parquet/ASTERIXDB-3540">
+        <placeholder name="adapter" value="S3" />
+        <output-dir compare="Clean-JSON">common/parquet/ASTERIXDB-3540</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="external-dataset">
       <compilation-unit name="common/parquet/array-access-pushdown">
         <placeholder name="adapter" value="S3" />
         <output-dir compare="Text">common/parquet/array-access-pushdown</output-dir>
diff --git a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java
index 84346fd..ab6d91a 100644
--- a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java
+++ b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java
@@ -269,6 +269,10 @@
                     isTransformed = true;
                 }
                 return winOp.getVariables().size() + winOp.getNestedPlans().size();
+            case SUBPLAN:
+                ListSet<LogicalVariable> producedVars = new ListSet<>();
+                VariableUtilities.getProducedVariables(op, producedVars);
+                return producedVars.size();
             default:
                 break;
         }
diff --git a/hyracks-fullstack/hyracks/hyracks-dataflow-common/src/main/java/org/apache/hyracks/dataflow/common/utils/FrameDebugUtils.java b/hyracks-fullstack/hyracks/hyracks-dataflow-common/src/main/java/org/apache/hyracks/dataflow/common/utils/FrameDebugUtils.java
index d09b890..66ba62c 100644
--- a/hyracks-fullstack/hyracks/hyracks-dataflow-common/src/main/java/org/apache/hyracks/dataflow/common/utils/FrameDebugUtils.java
+++ b/hyracks-fullstack/hyracks/hyracks-dataflow-common/src/main/java/org/apache/hyracks/dataflow/common/utils/FrameDebugUtils.java
@@ -147,7 +147,7 @@
             }
             sb.append("}");
         }
-        sb.append("\n");
+        sb.append("]\n");
     }
 
     /**