diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/CancelUnnestWithNestedListifyRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/CancelUnnestWithNestedListifyRule.java
index 366da5a..93c4ea2 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/CancelUnnestWithNestedListifyRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/CancelUnnestWithNestedListifyRule.java
@@ -28,6 +28,7 @@
 import org.apache.commons.lang3.mutable.Mutable;
 import org.apache.commons.lang3.mutable.MutableObject;
 import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.common.utils.ListSet;
 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.ILogicalPlan;
@@ -119,6 +120,10 @@
         AbstractLogicalOperator op = (AbstractLogicalOperator) opRef.getValue();
         VariableUtilities.getUsedVariables(op, varSet);
         if (op.hasNestedPlans()) {
+            // Variables used by the parent operators should be live at op.
+            Set<LogicalVariable> localLiveVars = new ListSet<>();
+            VariableUtilities.getLiveVariables(op, localLiveVars);
+            varSet.retainAll(localLiveVars);
             AbstractOperatorWithNestedPlans aonp = (AbstractOperatorWithNestedPlans) op;
             for (ILogicalPlan p : aonp.getNestedPlans()) {
                 for (Mutable<ILogicalOperator> r : p.getRoots()) {
@@ -198,8 +203,9 @@
             return false;
         }
 
-        if (OperatorManipulationUtil.ancestorOfOperators(agg, ImmutableSet.of(LogicalOperatorTag.LIMIT,
-                LogicalOperatorTag.ORDER, LogicalOperatorTag.GROUP, LogicalOperatorTag.DISTINCT))) {
+        if (OperatorManipulationUtil.ancestorOfOperatorsExcludeCurrent(agg,
+                ImmutableSet.of(LogicalOperatorTag.LIMIT, LogicalOperatorTag.ORDER, LogicalOperatorTag.GROUP,
+                        LogicalOperatorTag.DISTINCT, LogicalOperatorTag.AGGREGATE))) {
             return false;
         }
 
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/remove_listify.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/remove_listify.sqlpp
new file mode 100644
index 0000000..d6475f3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/remove_listify.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * 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 dt as
+{
+  uid : integer
+};
+
+create  dataset collection1(dt) primary key uid;
+
+SET `rewrite_or_as_join` "false";
+WITH table23 AS(
+  SELECT f1,
+         f2
+  FROM collection1 b),
+table22 AS(
+  SELECT f1,
+         (
+           SELECT COUNT(1) FILTER(WHERE b.f2>=a.f2-5 AND b.f2<=a.f2) AS counts
+           FROM table23 b
+           WHERE b.f1=a.f1
+          )[0] counts
+  FROM table23 a)
+SELECT
+     value a
+FROM table22 a;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/remove_listify.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/remove_listify.plan
new file mode 100644
index 0000000..96d4248
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/remove_listify.plan
@@ -0,0 +1,36 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- PRE_CLUSTERED_GROUP_BY[$$141]  |PARTITIONED|
+                    {
+                      -- AGGREGATE  |LOCAL|
+                        -- AGGREGATE  |LOCAL|
+                          -- STREAM_SELECT  |LOCAL|
+                            -- ASSIGN  |LOCAL|
+                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                    }
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [$$141(ASC)]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$141]  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- HYBRID_HASH_JOIN [$$146][$$148]  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$146]  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$148]  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan
index 162e3b4..bcdb951 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan
@@ -10,22 +10,15 @@
                         {
                           -- AGGREGATE  |LOCAL|
                             -- STREAM_SELECT  |LOCAL|
-                              -- UNNEST  |LOCAL|
-                                -- MICRO_PRE_CLUSTERED_GROUP_BY[]  |LOCAL|
-                                        {
-                                          -- AGGREGATE  |LOCAL|
-                                            -- STREAM_SELECT  |LOCAL|
-                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                        }
-                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                              -- NESTED_TUPLE_SOURCE  |LOCAL|
                         }
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                     -- STABLE_SORT [$$52(ASC)]  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- HYBRID_HASH_JOIN [$$52][$$51]  |PARTITIONED|
                           -- HASH_PARTITION_EXCHANGE [$$52]  |PARTITIONED|
-                            -- STREAM_SELECT  |PARTITIONED|
-                              -- ASSIGN  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- STREAM_SELECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- DATASOURCE_SCAN (test.Customers)  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -37,4 +30,4 @@
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- DATASOURCE_SCAN (test.Orders)  |PARTITIONED|
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated_ps.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated_ps.plan
index 80134f1..9cb3421 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated_ps.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated_ps.plan
@@ -16,22 +16,15 @@
                                     {
                                       -- AGGREGATE  |LOCAL|
                                         -- STREAM_SELECT  |LOCAL|
-                                          -- UNNEST  |LOCAL|
-                                            -- MICRO_PRE_CLUSTERED_GROUP_BY[]  |LOCAL|
-                                                    {
-                                                      -- AGGREGATE  |LOCAL|
-                                                        -- STREAM_SELECT  |LOCAL|
-                                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                    }
-                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
                                     }
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- STABLE_SORT [$$52(ASC)]  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- HYBRID_HASH_JOIN [$$52][$$51]  |PARTITIONED|
                                       -- HASH_PARTITION_EXCHANGE [$$52]  |PARTITIONED|
-                                        -- STREAM_SELECT  |PARTITIONED|
-                                          -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- STREAM_SELECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                               -- DATASOURCE_SCAN (test.Customers)  |PARTITIONED|
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -58,22 +51,15 @@
                                             {
                                               -- AGGREGATE  |LOCAL|
                                                 -- STREAM_SELECT  |LOCAL|
-                                                  -- UNNEST  |LOCAL|
-                                                    -- MICRO_PRE_CLUSTERED_GROUP_BY[]  |LOCAL|
-                                                            {
-                                                              -- AGGREGATE  |LOCAL|
-                                                                -- STREAM_SELECT  |LOCAL|
-                                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                            }
-                                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
                                             }
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                         -- STABLE_SORT [$$52(ASC)]  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                             -- HYBRID_HASH_JOIN [$$52][$$51]  |PARTITIONED|
                                               -- HASH_PARTITION_EXCHANGE [$$52]  |PARTITIONED|
-                                                -- STREAM_SELECT  |PARTITIONED|
-                                                  -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- STREAM_SELECT  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- DATASOURCE_SCAN (test.Customers)  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -85,4 +71,4 @@
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- DATASOURCE_SCAN (test.Orders)  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.001.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.001.ddl.sqlpp
new file mode 100644
index 0000000..f215b9f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.001.ddl.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.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+
+USE test;
+
+CREATE collection sample_collection PRIMARY KEY (primary_key_id: string)
+WITH {
+    "storage-format": {
+        "format": "column"
+     }
+};
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.002.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.002.update.sqlpp
new file mode 100644
index 0000000..5a2e922
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.002.update.sqlpp
@@ -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.
+ */
+
+USE test;
+insert into sample_collection (
+[
+  {
+    "primary_key_id": "1",
+    "int_field1": 69114320,
+    "decimal_field1": 9610,
+    "datetime_field1": "2005-11-08 00:00:00",
+    "bool_field1": False,
+    "varchar_field1": "BPBlasoTZq",
+    "char_field1": "C"
+  }
+]
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.003.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.003.update.sqlpp
new file mode 100644
index 0000000..04490d2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.003.update.sqlpp
@@ -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.
+ */
+
+USE test;
+insert into sample_collection (
+[
+  {
+    "primary_key_id": "189",
+    "int_field1": 89463144,
+    "decimal_field1": 3490,
+    "datetime_field1": "2012-12-19 00:00:00",
+    "bool_field1": False,
+    "varchar_field1": "nfQdaGVYjH",
+    "char_field1": "C"
+  }
+]
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.004.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.004.ddl.sqlpp
new file mode 100644
index 0000000..3ad191a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.004.ddl.sqlpp
@@ -0,0 +1,23 @@
+/*
+ * 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;
+
+compact collection sample_collection;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.005.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.005.query.sqlpp
new file mode 100644
index 0000000..ffb466a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.005.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 int_field1
+from sample_collection
+where bool_field1 = false;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.1.ddl.sqlpp
new file mode 100644
index 0000000..c240470
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.1.ddl.sqlpp
@@ -0,0 +1,39 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description: This test case is to verify the fix for ASTERIXDB-3490
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+create type dt1 as {id: int};
+
+create dataset collection1(dt1) PRIMARY KEY id;
+create dataset collection2(dt1) PRIMARY KEY id;
+create dataset collection3(dt1) PRIMARY KEY id;
+create dataset collection4(dt1) PRIMARY KEY id;
+create dataset collection5(dt1) PRIMARY KEY id;
+create dataset collection6(dt1) PRIMARY KEY id;
+
+create dataset dataset0(dt1) PRIMARY KEY id;
+create dataset dataset1(dt1) PRIMARY KEY id;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.2.update.sqlpp
new file mode 100644
index 0000000..8b127ab
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.2.update.sqlpp
@@ -0,0 +1,123 @@
+/*
+ * 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;
+
+upsert into collection1
+[
+    {
+        "id":1,
+        "t1":10,
+        "t2":10,
+        "t":10,
+        "p1":3,
+        "p2":3,
+        "p":3
+    }
+];
+
+upsert into collection2
+[
+    {
+        "id":1,
+        "t1":10,
+        "t2":10,
+        "t":10,
+        "p1":3,
+        "p2":3,
+        "p":3
+    }
+];
+
+upsert into collection3
+[
+    {
+        "id":1,
+        "t1":10,
+        "t2":10,
+        "t":10,
+        "p1":3,
+        "p2":3,
+        "p":3
+    }
+];
+
+upsert into collection4
+[
+    {
+        "id":1,
+        "t1":10,
+        "t2":10,
+        "t":10,
+        "p1":3,
+        "p2":3,
+        "p":3
+    }
+];
+
+upsert into collection5
+[
+    {
+        "id":1,
+        "t1":10,
+        "t2":10,
+        "t":10,
+        "p1":3,
+        "p2":3,
+        "p":3
+    }
+];
+
+upsert into collection6
+[
+    {
+        "id":1,
+        "t1":10,
+        "t2":10,
+        "t":10,
+        "p1":3,
+        "p2":3,
+        "p":3
+    }
+];
+
+
+upsert into dataset0
+    [{
+        "id":10,
+        "a":12,
+        "y_id":10,
+        "b":20,
+        "u": 101,
+        "x_id":10,
+        "p":3
+    }];
+
+upsert into dataset1
+    [{
+        "id":1,
+        "a":12,
+        "y_id":10,
+        "b":20,
+        "u": 101,
+        "x_id":10,
+        "p":3
+    }];
+
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.3.query.sqlpp
new file mode 100644
index 0000000..f319efc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.3.query.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * 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: This test case is to verify the fix for ASTERIXDB-3490
+ */
+
+use test;
+
+SELECT value a1
+FROM collection1 a1
+WHERE (
+    SELECT  value count(*)
+    FROM collection2 a2
+    WHERE a1.t=a2.t and a1.p=3 and (
+       SELECT value count(*)
+        FROM collection3 a3
+        WHERE a2.t1=a3.t1 and a2.p1=3 and (
+            SELECT value count(*)
+                FROM collection4 a4
+                WHERE a3.t2=a4.t2 and a3.p2=3 and (
+                    SELECT value count(*)
+                        FROM collection5 a5
+                        WHERE a4.t=a5.t and a4.p=3 and (
+                        SELECT value count(*)
+                            FROM collection6 a6
+                            WHERE a5.t1=a6.t1 and a5.p1=3
+                        )[0] >= 1
+                    )[0] >= 1
+                )[0] >= 1
+        )[0] >= 1
+    )[0] >= 1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.4.query.sqlpp
new file mode 100644
index 0000000..2313a6d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.4.query.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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: This test case is to verify the fix for ASTERIXDB-3490
+ */
+
+use test;
+
+SET `rewrite_or_as_join` "false";
+SELECT DISTINCT T1.*,
+                (SELECT VALUE H.u
+                  FROM dataset1 H
+                  WHERE H.y_id = to_string(T1.x_id)
+                    AND H.a IN [12, 66, 67, 13, 26]
+                    AND H.b IN
+                        (SELECT value MAX(L.b)
+                           FROM dataset1 L
+                          WHERE L.y_id = T1.x_id
+                            AND L.a IN [12, 66, 67, 13, 26]
+                            )
+                            LIMIT 1
+                )[0] AS sub_q1,
+                b_max
+FROM (SELECT ROW_NUMBER() OVER(ORDER BY sub_q2.b_max DESC NULLS LAST) AS ROW_NUMBER,
+             T0.x_id,
+             sub_q2.b_max
+      FROM dataset0 T0 INNER JOIN
+      (SELECT y_id, MAX(b) b_max
+       FROM dataset1 T2
+       GROUP BY y_id) AS sub_q2
+      ON sub_q2.y_id = T0.id
+) T1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/remove_listify/remove_listify.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/remove_listify/remove_listify.1.ddl.sqlpp
new file mode 100644
index 0000000..498f374
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/remove_listify/remove_listify.1.ddl.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.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type dt as
+{
+  uid : integer
+};
+
+create  dataset collection1(dt) primary key uid;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/remove_listify/remove_listify.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/remove_listify/remove_listify.2.update.sqlpp
new file mode 100644
index 0000000..ac2cc1b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/remove_listify/remove_listify.2.update.sqlpp
@@ -0,0 +1,100 @@
+/*
+ * 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 collection1
+([
+
+    {
+        "uid":1,
+        "f1":101,
+        "f2": 24
+    },
+    {
+        "uid":2,
+        "f1":102,
+        "f2": 25
+    },
+    {
+        "uid":3,
+        "f1":102,
+        "f2": 25
+    },
+    {
+        "uid":4,
+        "f1":102,
+        "f2": 31
+    },
+    {
+        "uid":5,
+        "f1":103,
+        "f2": 24
+    },
+    {
+        "uid":6,
+        "f1":103,
+        "f2": 30
+    },
+    {
+        "uid":7,
+        "f1":103,
+        "f2": 31
+    },
+    {
+        "uid":8,
+        "f1":103,
+        "f2": 35
+    },
+    {
+        "uid":9,
+        "f1":103,
+        "f2": 41
+    },
+    {
+        "uid":10,
+        "f1":104,
+        "f2": 42
+    },
+    {
+        "uid":11,
+        "f1":104,
+        "f2": 24
+    },
+    {
+        "uid":12,
+        "f1":104,
+        "f2": 25
+    },
+    {
+        "uid":13,
+        "f1":105,
+        "f2": 24
+    },
+    {
+        "uid":14,
+        "f1":105,
+        "f2": 25
+    },
+    {
+        "uid":15,
+        "f1":105,
+        "f2": "40"
+    }
+])
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/remove_listify/remove_listify.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/remove_listify/remove_listify.3.query.sqlpp
new file mode 100644
index 0000000..8652bba
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/misc/remove_listify/remove_listify.3.query.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * 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 `rewrite_or_as_join` "false";
+WITH table23 AS(
+  SELECT f1,
+         f2
+  FROM collection1 b),
+table22 AS(
+  SELECT f1,
+         (
+           SELECT COUNT(1) FILTER(WHERE b.f2>=a.f2-5 AND b.f2<=a.f2) AS counts
+           FROM table23 b
+           WHERE b.f1=a.f1
+          )[0] obj
+  FROM table23 a)
+SELECT
+     a.f1, a.obj.counts
+FROM table22 a
+order by a.f1, a.obj.counts;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/api/cache-residency/cache-residency.002.regexjson b/asterixdb/asterix-app/src/test/resources/runtimets/results/api/cache-residency/cache-residency.002.regexjson
index 7c4f553..b3f8c7c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/api/cache-residency/cache-residency.002.regexjson
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/api/cache-residency/cache-residency.002.regexjson
@@ -4,7 +4,7 @@
 		"*": "*"
 	},
 	"type": "application/x-adm",
-	"results": [ "{ \"$1\": 17 }" ],
+	"results": [ "R{.*}" ],
 	"plans": "R{.*}",
 	"status": "success",
 	"metrics": {
@@ -14,7 +14,7 @@
 		"queueWaitTime": "R{.*}",
 		"resultCount": 1,
 		"resultSize": "R{.*}",
-		"processedObjects": 17,
+		"processedObjects": "R{.*}",
 		"bufferCacheHitRatio": "100.00%",
 		"bufferCachePageReadCount": 1
 	}
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.005.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.005.adm
new file mode 100644
index 0000000..ac56dc7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/column/filter/ASTERIXDB-3499/ASTERIXDB-3499.005.adm
@@ -0,0 +1,2 @@
+{ "int_field1": 69114320 }
+{ "int_field1": 89463144 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.3.adm
new file mode 100644
index 0000000..22c03b0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.3.adm
@@ -0,0 +1 @@
+{ "id": 1, "t1": 10, "t2": 10, "t": 10, "p1": 3, "p2": 3, "p": 3 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.4.adm
new file mode 100644
index 0000000..a617f17
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/misc/query-ASTERIXDB-3490/query-ASTERIXDB-3490.4.adm
@@ -0,0 +1 @@
+{ "b_max": 20, "ROW_NUMBER": 1, "x_id": 10 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/misc/remove_listify/remove_listify.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/misc/remove_listify/remove_listify.3.adm
new file mode 100644
index 0000000..a4142d4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/misc/remove_listify/remove_listify.3.adm
@@ -0,0 +1,15 @@
+{ "counts": 1, "f1": 101 }
+{ "counts": 1, "f1": 102 }
+{ "counts": 2, "f1": 102 }
+{ "counts": 2, "f1": 102 }
+{ "counts": 1, "f1": 103 }
+{ "counts": 1, "f1": 103 }
+{ "counts": 1, "f1": 103 }
+{ "counts": 2, "f1": 103 }
+{ "counts": 3, "f1": 103 }
+{ "counts": 1, "f1": 104 }
+{ "counts": 1, "f1": 104 }
+{ "counts": 2, "f1": 104 }
+{ "counts": 0, "f1": 105 }
+{ "counts": 1, "f1": 105 }
+{ "counts": 2, "f1": 105 }
\ No newline at end of file
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 f77350f..2bbc5a2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
@@ -91,6 +91,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="api">
+      <compilation-unit name="cache-residency">
+        <output-dir compare="Text">cache-residency</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="api">
       <compilation-unit name="request-param">
         <output-dir compare="Text">request-param</output-dir>
       </compilation-unit>
@@ -1660,6 +1665,11 @@
   </test-group>
   <test-group name="aggregate-sql">
     <test-case FilePath="aggregate-sql">
+      <compilation-unit name="median">
+        <output-dir compare="Text">median</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="aggregate-sql">
       <compilation-unit name="min_max_arrays">
         <output-dir compare="Text">min_max_arrays</output-dir>
       </compilation-unit>
@@ -7462,11 +7472,6 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="misc">
-      <compilation-unit name="query-ASTERIXDB-3316">
-        <output-dir compare="Text">query-ASTERIXDB-3316</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="misc">
       <compilation-unit name="query-ASTERIXDB-3334">
         <output-dir compare="Text">query-ASTERIXDB-3334</output-dir>
       </compilation-unit>
@@ -7492,13 +7497,23 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="misc">
-      <compilation-unit name="query-ASTERIXDB-3415">
-        <output-dir compare="Text">query-ASTERIXDB-3415</output-dir>
+      <compilation-unit name="query-ASTERIXDB-3481">
+        <output-dir compare="Text">query-ASTERIXDB-3481</output-dir>
       </compilation-unit>
     </test-case>
     <test-case FilePath="misc">
-      <compilation-unit name="query-ASTERIXDB-3481">
-        <output-dir compare="Text">query-ASTERIXDB-3481</output-dir>
+      <compilation-unit name="query-ASTERIXDB-3490">
+        <output-dir compare="Text">query-ASTERIXDB-3490</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="misc">
+      <compilation-unit name="query-ASTERIXDB-3316">
+        <output-dir compare="Text">query-ASTERIXDB-3316</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="misc">
+      <compilation-unit name="remove_listify">
+        <output-dir compare="Text">remove_listify</output-dir>
       </compilation-unit>
     </test-case>
     <test-case FilePath="misc">
@@ -11599,6 +11614,11 @@
   </test-group>
   <test-group name="subquery">
     <test-case FilePath="subquery">
+      <compilation-unit name="select_element">
+        <output-dir compare="Text">select_element</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="subquery">
       <compilation-unit name="aggregate_join">
         <output-dir compare="Text">aggregate_join</output-dir>
       </compilation-unit>
@@ -16429,6 +16449,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="column">
+      <compilation-unit name="filter/ASTERIXDB-3499">
+        <output-dir compare="Text">filter/ASTERIXDB-3499</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="column">
       <compilation-unit name="delete/001">
         <output-dir compare="Text">delete/001</output-dir>
       </compilation-unit>
diff --git a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/lsm/secondary/create/PrimaryScanColumnTupleWithMetaProjector.java b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/lsm/secondary/create/PrimaryScanColumnTupleWithMetaProjector.java
index 79a1860..096134a 100644
--- a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/lsm/secondary/create/PrimaryScanColumnTupleWithMetaProjector.java
+++ b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/lsm/secondary/create/PrimaryScanColumnTupleWithMetaProjector.java
@@ -18,6 +18,8 @@
  */
 package org.apache.asterix.column.operation.lsm.secondary.create;
 
+import static org.apache.hyracks.storage.am.lsm.btree.column.api.projection.ColumnProjectorType.MODIFY;
+
 import java.io.DataOutput;
 import java.io.IOException;
 import java.util.Collections;
@@ -40,7 +42,7 @@
             int numberOfPrimaryKeys, ARecordType requestedType) {
         projector = new QueryColumnWithMetaTupleProjector(datasetType, metaType, numberOfPrimaryKeys, requestedType,
                 Collections.emptyMap(), metaType, NoOpColumnFilterEvaluatorFactory.INSTANCE,
-                NoOpColumnFilterEvaluatorFactory.INSTANCE, NoOpWarningCollector.INSTANCE, null);
+                NoOpColumnFilterEvaluatorFactory.INSTANCE, NoOpWarningCollector.INSTANCE, null, MODIFY);
     }
 
     @Override
diff --git a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/lsm/secondary/upsert/UpsertPreviousColumnTupleWithMetaProjector.java b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/lsm/secondary/upsert/UpsertPreviousColumnTupleWithMetaProjector.java
index 271649a..65d6d21 100644
--- a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/lsm/secondary/upsert/UpsertPreviousColumnTupleWithMetaProjector.java
+++ b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/lsm/secondary/upsert/UpsertPreviousColumnTupleWithMetaProjector.java
@@ -18,6 +18,8 @@
  */
 package org.apache.asterix.column.operation.lsm.secondary.upsert;
 
+import static org.apache.hyracks.storage.am.lsm.btree.column.api.projection.ColumnProjectorType.MODIFY;
+
 import java.io.DataOutput;
 import java.io.IOException;
 import java.util.Collections;
@@ -43,7 +45,7 @@
         builder = new ArrayTupleBuilder(numberOfPrimaryKeys + 2);
         projector = new QueryColumnWithMetaTupleProjector(datasetType, metaType, numberOfPrimaryKeys, requestedType,
                 Collections.emptyMap(), metaType, NoOpColumnFilterEvaluatorFactory.INSTANCE,
-                NoOpColumnFilterEvaluatorFactory.INSTANCE, NoOpWarningCollector.INSTANCE, null);
+                NoOpColumnFilterEvaluatorFactory.INSTANCE, NoOpWarningCollector.INSTANCE, null, MODIFY);
     }
 
     @Override
diff --git a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/query/QueryColumnTupleProjectorFactory.java b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/query/QueryColumnTupleProjectorFactory.java
index e333405..68362e8 100644
--- a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/query/QueryColumnTupleProjectorFactory.java
+++ b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/query/QueryColumnTupleProjectorFactory.java
@@ -68,6 +68,6 @@
         // The dataset has a meta part
         return new QueryColumnWithMetaTupleProjector(datasetType, metaType, numberOfPrimaryKeys, requestedType,
                 functionCallInfo, requestedMetaType, rangeFilterEvaluatorFactory, columnFilterEvaluatorFactory,
-                warningCollector, context);
+                warningCollector, context, QUERY);
     }
 }
diff --git a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/query/QueryColumnWithMetaTupleProjector.java b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/query/QueryColumnWithMetaTupleProjector.java
index 74524fe..d7fb662 100644
--- a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/query/QueryColumnWithMetaTupleProjector.java
+++ b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/operation/query/QueryColumnWithMetaTupleProjector.java
@@ -18,8 +18,6 @@
  */
 package org.apache.asterix.column.operation.query;
 
-import static org.apache.hyracks.storage.am.lsm.btree.column.api.projection.ColumnProjectorType.QUERY;
-
 import java.io.DataOutput;
 import java.io.IOException;
 import java.util.Map;
@@ -37,6 +35,7 @@
 import org.apache.hyracks.data.std.api.IValueReference;
 import org.apache.hyracks.dataflow.common.comm.io.ArrayTupleBuilder;
 import org.apache.hyracks.dataflow.common.data.accessors.ITupleReference;
+import org.apache.hyracks.storage.am.lsm.btree.column.api.projection.ColumnProjectorType;
 import org.apache.hyracks.storage.am.lsm.btree.column.api.projection.IColumnProjectionInfo;
 
 public class QueryColumnWithMetaTupleProjector extends QueryColumnTupleProjector {
@@ -47,9 +46,9 @@
             ARecordType requestedType, Map<String, FunctionCallInformation> functionCallInfoMap,
             ARecordType requestedMetaType, IColumnRangeFilterEvaluatorFactory filterEvaluator,
             IColumnIterableFilterEvaluatorFactory columnFilterEvaluatorFactory, IWarningCollector warningCollector,
-            IHyracksTaskContext context) {
+            IHyracksTaskContext context, ColumnProjectorType projectorType) {
         super(datasetType, numberOfPrimaryKeys, requestedType, functionCallInfoMap, filterEvaluator,
-                columnFilterEvaluatorFactory, warningCollector, context, QUERY);
+                columnFilterEvaluatorFactory, warningCollector, context, projectorType);
         this.metaType = metaType;
         this.requestedMetaType = requestedMetaType;
     }
diff --git a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/values/writer/BooleanColumnValuesWriter.java b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/values/writer/BooleanColumnValuesWriter.java
index 4bd6e27..7d50cb1 100644
--- a/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/values/writer/BooleanColumnValuesWriter.java
+++ b/asterixdb/asterix-column/src/main/java/org/apache/asterix/column/values/writer/BooleanColumnValuesWriter.java
@@ -72,6 +72,7 @@
     protected void addValue(IColumnValuesReader reader) throws IOException {
         int value = reader.getBoolean() ? 1 : 0;
         booleanWriter.writeInt(value);
+        filterWriter.addLong(value);
     }
 
     @Override
diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/context/DatasetInfo.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/context/DatasetInfo.java
index c8db4cd..952185b 100644
--- a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/context/DatasetInfo.java
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/context/DatasetInfo.java
@@ -292,6 +292,20 @@
         }
     }
 
+    public void waitForFlushes() throws HyracksDataException {
+        logManager.log(waitLog);
+        synchronized (this) {
+            while (pendingFlushes > 0) {
+                try {
+                    wait();
+                } catch (InterruptedException e) {
+                    Thread.currentThread().interrupt();
+                    throw HyracksDataException.create(e);
+                }
+            }
+        }
+    }
+
     public synchronized int getPendingFlushes() {
         return pendingFlushes;
     }
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/MetadataProvider.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/MetadataProvider.java
index fe2127c..cc456e5 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/MetadataProvider.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/MetadataProvider.java
@@ -1056,8 +1056,7 @@
     }
 
     public AlgebricksAbsolutePartitionConstraint getClusterLocations() {
-        //TODO(partitioning): should this be removed and getSortedClusterLocations() is used instead?
-        return appCtx.getClusterStateManager().getClusterLocations();
+        return dataPartitioningProvider.getClusterLocations();
     }
 
     public DataPartitioningProvider getDataPartitioningProvider() {
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/physical/AbstractHashJoinPOperator.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/physical/AbstractHashJoinPOperator.java
index a594e7e..7a5fc8a 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/physical/AbstractHashJoinPOperator.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/operators/physical/AbstractHashJoinPOperator.java
@@ -166,7 +166,9 @@
                                         EquivalenceClass ecFst = eqmap.get(v2);
                                         for (LogicalVariable vset1 : set1) {
                                             if (vset1 == v2 || ecFst != null && eqmap.get(vset1) == ecFst) {
-                                                covered.add(vset1);
+                                                if (!covered.add(vset1)) {
+                                                    continue;
+                                                }
                                                 modifuppreq.add(r);
                                                 break;
                                             }
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java
index 3457751..2413ed2 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java
@@ -361,6 +361,15 @@
         return false;
     }
 
+    public static boolean ancestorOfOperatorsExcludeCurrent(ILogicalOperator op, Set<LogicalOperatorTag> tags) {
+        for (Mutable<ILogicalOperator> children : op.getInputs()) {
+            if (ancestorOfOperators(children.getValue(), tags)) {
+                return true;
+            }
+        }
+        return false;
+    }
+
     /**
      * Returns all descendants of an operator that are leaf operators
      *
diff --git a/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree-column/src/main/java/org/apache/hyracks/storage/am/lsm/btree/column/cloud/CloudColumnIndexDiskCacheManager.java b/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree-column/src/main/java/org/apache/hyracks/storage/am/lsm/btree/column/cloud/CloudColumnIndexDiskCacheManager.java
index 417322e..34d8344 100644
--- a/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree-column/src/main/java/org/apache/hyracks/storage/am/lsm/btree/column/cloud/CloudColumnIndexDiskCacheManager.java
+++ b/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree-column/src/main/java/org/apache/hyracks/storage/am/lsm/btree/column/cloud/CloudColumnIndexDiskCacheManager.java
@@ -28,7 +28,6 @@
 import org.apache.hyracks.storage.am.lsm.btree.column.cloud.buffercache.IColumnReadContext;
 import org.apache.hyracks.storage.am.lsm.btree.column.cloud.buffercache.IColumnWriteContext;
 import org.apache.hyracks.storage.am.lsm.btree.column.cloud.buffercache.read.CloudColumnReadContext;
-import org.apache.hyracks.storage.am.lsm.btree.column.cloud.buffercache.read.DefaultColumnReadContext;
 import org.apache.hyracks.storage.am.lsm.btree.column.cloud.buffercache.write.CloudColumnWriteContext;
 import org.apache.hyracks.storage.am.lsm.btree.column.cloud.sweep.ColumnSweepPlanner;
 import org.apache.hyracks.storage.am.lsm.btree.column.cloud.sweep.ColumnSweeper;
@@ -73,9 +72,8 @@
         if (projectorType == ColumnProjectorType.QUERY) {
             planner.access(projectionInfo);
         } else if (projectorType == ColumnProjectorType.MODIFY) {
-            planner.setIndexedColumns(projectionInfo);
             // Requested (and indexed) columns will be persisted if space permits
-            return DefaultColumnReadContext.INSTANCE;
+            planner.setIndexedColumns(projectionInfo);
         }
         return new CloudColumnReadContext(projectionInfo, drive, planner.getPlanCopy());
     }