diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
index 2706ac3..3bd8f42 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
@@ -848,6 +848,10 @@
             throw new CompilationException(ErrorCode.UNKNOWN_FUNCTION, sourceLoc, signature.getName());
         }
 
+        if (fcall.hasAggregateFilterExpr()) {
+            throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_USE_OF_FILTER_CLAUSE, sourceLoc);
+        }
+
         // Put hints into function call expr.
         if (fcall.hasHints()) {
             for (IExpressionAnnotation hint : fcall.getHints()) {
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
index 59642cb..b689e8f 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
@@ -1090,6 +1090,10 @@
         boolean allowFromFirstLast = isWin && BuiltinFunctions.builtinFunctionHasProperty(fi,
                 BuiltinFunctions.WindowFunctionProperty.ALLOW_FROM_FIRST_LAST);
 
+        if (winExpr.hasAggregateFilterExpr()) {
+            throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_USE_OF_FILTER_CLAUSE, sourceLoc);
+        }
+
         Mutable<ILogicalOperator> currentOpRef = tupSource;
 
         List<Mutable<ILogicalExpression>> partExprListOut = Collections.emptyList();
diff --git a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGGroupingSetsIT.java b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGGroupingSetsIT.java
index 838d980..b1125a5 100644
--- a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGGroupingSetsIT.java
+++ b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/runtime/SqlppRQGGroupingSetsIT.java
@@ -292,7 +292,12 @@
             selectClause.append(String.format("GROUPING(%s) AS grp", String.join(",", randomize(allColumns, random))))
                     .append(',');
         }
-        selectClause.append(String.format("SUM(%s) AS agg_sum", UNIQUE_1));
+        String agg = String.format("SUM(%s)", UNIQUE_1);
+        if (random.nextInt(3) == 0) {
+            int filterLimit = 1 + random.nextInt(9999);
+            agg = String.format("%s FILTER(WHERE %s < %d)", agg, UNIQUE_2, filterLimit);
+        }
+        selectClause.append(String.format("%s AS agg_sum", agg));
 
         String groupingElementText = groupingElements.isEmpty() ? "()" : String.join(",", groupingElements);
         String groupbyClause = String.format("GROUP BY %s", groupingElementText);
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.3.sqlpp
new file mode 100644
index 0000000..f9f7539
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.3.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * 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 tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothous        : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+/*
+ * Test aggregate filter without GROUP BY clause
+ */
+
+select
+  count(*) filter(where four > 0) as cnt,
+  sum(four) filter(where four > 0) as sm
+from tenk;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.4.sqlpp
new file mode 100644
index 0000000..bfd70f6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.4.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothous        : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+/*
+ * Test aggregate FILTER with simple GROUP BY clause
+ */
+
+select two,
+  count(*) filter(where four > 0) as cnt,
+  sum(four) filter(where four > 0) as sm
+from tenk
+group by two
+order by two;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.5.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.5.sqlpp
new file mode 100644
index 0000000..065b6dc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.5.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothous        : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+/*
+ * Test aggregate FILTER with grouping sets in GROUP BY clause
+ */
+
+select two,
+  count(*) filter(where four > 0) as cnt,
+  sum(four) filter(where four > 0) as sm
+from tenk
+group by rollup(two)
+order by two;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.6.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.6.sqlpp
new file mode 100644
index 0000000..8f3bd56
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.6.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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 tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothous        : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+/*
+ * Test aggregate FILTER in window function call,
+ * without GROUP BY clause
+ */
+
+select twothous, fivethous, tenthous,
+  count(tenthous) over(order by tenthous) as cnt,
+  sum(tenthous) over(order by tenthous) as sm,
+  count(tenthous) filter(where twothous = 0) over(order by tenthous) as cnt_filter,
+  sum(tenthous) filter(where twothous = 0) over(order by tenthous) as sm_filter
+from tenk
+where thousand = 0
+order by tenthous;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.7.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.7.sqlpp
new file mode 100644
index 0000000..61efd1c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.7.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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 tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothous        : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+/*
+ * Test aggregate FILTER in window function call,
+ * with GROUP BY clause
+ */
+
+select twenty,
+  sum(ten) as sm,
+  sum(sum(ten)) over (order by twenty) as sm_sm,
+  sum(sum(ten)) filter(where twenty < 10) over (order by twenty) as sm_sm_where_twenty_lt_10,
+  sum(sum(ten)) filter(where sum(ten) < 3000) over (order by twenty) as sm_sm_where_sm_lt_3k
+from tenk
+group by twenty
+order by twenty;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.8.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.8.sqlpp
new file mode 100644
index 0000000..2d74690
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/aggregate-subclause/agg_filter_01/agg_filter_01.8.sqlpp
@@ -0,0 +1,54 @@
+/*
+ * 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 tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothous        : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
+
+/*
+ * Test FILTER with complex expression
+ */
+
+select two,
+  sum(four) filter(where every x in [four, four + four] satisfies x > 0) as sm
+from tenk
+group by two
+order by two;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.3.plan
new file mode 100644
index 0000000..34aaec9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.3.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- STREAM_PROJECT  |UNPARTITIONED|
+          -- SUBPLAN  |UNPARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- AGGREGATE  |LOCAL|
+                        -- STREAM_SELECT  |UNPARTITIONED|
+                          -- ASSIGN  |UNPARTITIONED|
+                            -- ASSIGN  |UNPARTITIONED|
+                              -- UNNEST  |UNPARTITIONED|
+                                -- NESTED_TUPLE_SOURCE  |UNPARTITIONED|
+                  }
+            -- SUBPLAN  |UNPARTITIONED|
+                    {
+                      -- AGGREGATE  |LOCAL|
+                        -- AGGREGATE  |LOCAL|
+                          -- STREAM_SELECT  |UNPARTITIONED|
+                            -- ASSIGN  |UNPARTITIONED|
+                              -- ASSIGN  |UNPARTITIONED|
+                                -- UNNEST  |UNPARTITIONED|
+                                  -- NESTED_TUPLE_SOURCE  |UNPARTITIONED|
+                    }
+              -- AGGREGATE  |UNPARTITIONED|
+                -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- DATASOURCE_SCAN  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.4.plan
new file mode 100644
index 0000000..4282df2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.4.plan
@@ -0,0 +1,38 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$two(ASC) ]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$66]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STABLE_SORT [$$66(ASC)]  |PARTITIONED|
+                -- HASH_PARTITION_EXCHANGE [$$66]  |PARTITIONED|
+                  -- PRE_CLUSTERED_GROUP_BY[$$55]  |PARTITIONED|
+                          {
+                            -- AGGREGATE  |LOCAL|
+                              -- STREAM_SELECT  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                          }
+                          {
+                            -- AGGREGATE  |LOCAL|
+                              -- STREAM_SELECT  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                          }
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STABLE_SORT [$$55(ASC)]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.5.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.5.plan
new file mode 100644
index 0000000..92f10c3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.5.plan
@@ -0,0 +1,94 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- SORT_MERGE_EXCHANGE [$$128(ASC) ]  |PARTITIONED|
+        -- STABLE_SORT [$$128(ASC)]  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- UNION_ALL  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ASSIGN  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- PRE_CLUSTERED_GROUP_BY[$$133]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STABLE_SORT [$$133(ASC)]  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$133]  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$112]  |PARTITIONED|
+                                          {
+                                            -- AGGREGATE  |LOCAL|
+                                              -- STREAM_SELECT  |LOCAL|
+                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          }
+                                          {
+                                            -- AGGREGATE  |LOCAL|
+                                              -- STREAM_SELECT  |LOCAL|
+                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          }
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STABLE_SORT [$$112(ASC)]  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- REPLICATE  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- DATASOURCE_SCAN  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ASSIGN  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- PRE_CLUSTERED_GROUP_BY[$$136]  |PARTITIONED|
+                                    {
+                                      -- AGGREGATE  |LOCAL|
+                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                    }
+                                    {
+                                      -- AGGREGATE  |LOCAL|
+                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                    }
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- STABLE_SORT [$$136(ASC)]  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$136]  |PARTITIONED|
+                                    -- PRE_CLUSTERED_GROUP_BY[$$113]  |PARTITIONED|
+                                            {
+                                              -- AGGREGATE  |LOCAL|
+                                                -- STREAM_SELECT  |LOCAL|
+                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                            }
+                                            {
+                                              -- AGGREGATE  |LOCAL|
+                                                -- STREAM_SELECT  |LOCAL|
+                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                            }
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- STABLE_SORT [$$113(ASC)]  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- REPLICATE  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- DATASOURCE_SCAN  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.6.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.6.plan
new file mode 100644
index 0000000..17f9f91
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.6.plan
@@ -0,0 +1,26 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- WINDOW  |UNPARTITIONED|
+                {
+                  -- AGGREGATE  |UNPARTITIONED|
+                    -- STREAM_SELECT  |UNPARTITIONED|
+                      -- NESTED_TUPLE_SOURCE  |UNPARTITIONED|
+                }
+          -- WINDOW  |UNPARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- SORT_MERGE_EXCHANGE [$$tenk.tenthous(ASC) ]  |PARTITIONED|
+              -- STABLE_SORT [$$tenk.tenthous(ASC)]  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- STREAM_SELECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.7.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.7.plan
new file mode 100644
index 0000000..8cb579b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.7.plan
@@ -0,0 +1,44 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- STREAM_PROJECT  |UNPARTITIONED|
+          -- WINDOW  |UNPARTITIONED|
+                  {
+                    -- AGGREGATE  |UNPARTITIONED|
+                      -- STREAM_SELECT  |UNPARTITIONED|
+                        -- NESTED_TUPLE_SOURCE  |UNPARTITIONED|
+                  }
+            -- STREAM_PROJECT  |UNPARTITIONED|
+              -- WINDOW  |UNPARTITIONED|
+                      {
+                        -- AGGREGATE  |UNPARTITIONED|
+                          -- STREAM_SELECT  |UNPARTITIONED|
+                            -- NESTED_TUPLE_SOURCE  |UNPARTITIONED|
+                      }
+                -- STREAM_PROJECT  |UNPARTITIONED|
+                  -- WINDOW  |UNPARTITIONED|
+                          {
+                            -- AGGREGATE  |LOCAL|
+                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                          }
+                    -- SORT_MERGE_EXCHANGE [$$twenty(ASC) ]  |PARTITIONED|
+                      -- SORT_GROUP_BY[$$187]  |PARTITIONED|
+                              {
+                                -- AGGREGATE  |LOCAL|
+                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                              }
+                        -- HASH_PARTITION_EXCHANGE [$$187]  |PARTITIONED|
+                          -- SORT_GROUP_BY[$$169]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.8.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.8.plan
new file mode 100644
index 0000000..6dd7373
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate-subclause/agg_filter_01/agg_filter_01.8.plan
@@ -0,0 +1,28 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$two(ASC) ]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$62]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- STREAM_SELECT  |LOCAL|
+                        -- SUBPLAN  |LOCAL|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- STREAM_SELECT  |LOCAL|
+                                      -- UNNEST  |LOCAL|
+                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STABLE_SORT [$$62(ASC)]  |PARTITIONED|
+                -- HASH_PARTITION_EXCHANGE [$$62]  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- DATASOURCE_SCAN  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.1.ddl.sqlpp
new file mode 100644
index 0000000..e4895f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.1.ddl.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.
+ */
+
+/*
+ * Test various combinations of grouping sets
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as closed {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  thousand        : integer,
+  twothous        : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk(tenkType) primary key unique2;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.2.update.sqlpp
new file mode 100644
index 0000000..2d7e768
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.2.update.sqlpp
@@ -0,0 +1,22 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+load  dataset tenk using localfs ((`path`=`asterix_nc1://data/tenk.tbl`),(`format`=`delimited-text`),(`delimiter`=`|`));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.3.query.sqlpp
new file mode 100644
index 0000000..354b3b4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.3.query.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test aggregate filter without GROUP BY clause
+ */
+
+use test;
+
+select
+  count(*) filter(where four > 0) as cnt,
+  sum(four) filter(where four > 0) as sm
+from tenk;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.4.query.sqlpp
new file mode 100644
index 0000000..3ea455c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.4.query.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test aggregate FILTER with simple GROUP BY clause
+ */
+
+use test;
+
+select two,
+  count(*) filter(where four > 0) as cnt,
+  sum(four) filter(where four > 0) as sm
+from tenk
+group by two
+order by two;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.5.query.sqlpp
new file mode 100644
index 0000000..d497b7d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.5.query.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test aggregate FILTER with grouping sets in GROUP BY clause
+ */
+
+use test;
+
+select two,
+  count(*) filter(where four > 0) as cnt,
+  sum(four) filter(where four > 0) as sm
+from tenk
+group by rollup(two)
+order by two;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.6.query.sqlpp
new file mode 100644
index 0000000..65e18a0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.6.query.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test aggregate FILTER in window function call,
+ * without GROUP BY clause
+ */
+
+use test;
+
+select twothous, fivethous, tenthous,
+  count(tenthous) over(order by tenthous) as cnt,
+  sum(tenthous) over(order by tenthous) as sm,
+  count(tenthous) filter(where twothous = 0) over(order by tenthous) as cnt_filter,
+  sum(tenthous) filter(where twothous = 0) over(order by tenthous) as sm_filter
+from tenk
+where thousand = 0
+order by tenthous;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.7.query.sqlpp
new file mode 100644
index 0000000..fd746b4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.7.query.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test aggregate FILTER in window function call,
+ * with GROUP BY clause
+ */
+
+use test;
+
+select twenty,
+  sum(ten) as sm,
+  sum(sum(ten)) over (order by twenty) as sm_sm,
+  sum(sum(ten)) filter(where twenty < 10) over (order by twenty) as sm_sm_where_twenty_lt_10,
+  sum(sum(ten)) filter(where sum(ten) < 3000) over (order by twenty) as sm_sm_where_sm_lt_3k
+from tenk
+group by twenty
+order by twenty;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.8.query.sqlpp
new file mode 100644
index 0000000..0ded863
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_01.8.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test FILTER with complex expression
+ */
+
+use test;
+
+select two,
+  sum(four) filter(where every x in [four, four + four] satisfies x > 0) as sm
+from tenk
+group by two
+order by two;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02.neg.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02.neg.5.query.sqlpp
new file mode 100644
index 0000000..1a2bed4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02.neg.5.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Negative: FILTER used in SQL++ aggregate function call (strict_*)
+ */
+
+select m,
+  strict_sum((select value gi.r from g as gi)) filter(where strict_count(g) > 2)
+from range(1, 10) r
+group by r % 2 as m group as g;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.1.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.1.query.sqlpp
new file mode 100644
index 0000000..7f65998
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.1.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.
+ */
+
+/*
+ * Negative: FILTER used in non-aggregate function call
+ */
+
+select to_double(r) filter(where r > 5)
+from range(1, 10) r;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.2.query.sqlpp
new file mode 100644
index 0000000..ce12917
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.2.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.
+ */
+
+/*
+ * Negative: FILTER used in non-aggregate windows function call
+ */
+
+select rank() filter(where r > 5) over (order by r)
+from range(1, 10) r;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.3.query.sqlpp
new file mode 100644
index 0000000..8d91562
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.3.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.
+ */
+
+/*
+ * Negative: FILTER in SQL++ aggregate function call. Invalid identifier.
+ */
+
+select m, array_sum(r) filter(where r > 5)
+from range(1, 10) r
+group by r % 2 as m;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.4.query.sqlpp
new file mode 100644
index 0000000..7c58c6d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-subclause/agg_filter_02_neg/agg_filter_02_neg.4.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Negative: FILTER used in SQL++ aggregate function call (array_*)
+ */
+
+select m,
+  array_sum((select value gi.r from g as gi)) filter(where array_count(g) > 2)
+from range(1, 10) r
+group by r % 2 as m group as g;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.3.adm
new file mode 100644
index 0000000..58ca85e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.3.adm
@@ -0,0 +1 @@
+{ "cnt": 7500, "sm": 15000 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.4.adm
new file mode 100644
index 0000000..06f5e2b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.4.adm
@@ -0,0 +1,2 @@
+{ "two": 0, "cnt": 2500, "sm": 5000 }
+{ "two": 1, "cnt": 5000, "sm": 10000 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.5.adm
new file mode 100644
index 0000000..d65a86d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.5.adm
@@ -0,0 +1,3 @@
+{ "two": null, "cnt": 7500, "sm": 15000 }
+{ "two": 0, "cnt": 2500, "sm": 5000 }
+{ "two": 1, "cnt": 5000, "sm": 10000 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.6.adm
new file mode 100644
index 0000000..44b4513
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.6.adm
@@ -0,0 +1,10 @@
+{ "twothous": 0, "fivethous": 0, "tenthous": 0, "cnt": 1, "sm": 0, "cnt_filter": 1, "sm_filter": 0 }
+{ "twothous": 1000, "fivethous": 1000, "tenthous": 1000, "cnt": 2, "sm": 1000, "cnt_filter": 1, "sm_filter": 0 }
+{ "twothous": 0, "fivethous": 2000, "tenthous": 2000, "cnt": 3, "sm": 3000, "cnt_filter": 2, "sm_filter": 2000 }
+{ "twothous": 1000, "fivethous": 3000, "tenthous": 3000, "cnt": 4, "sm": 6000, "cnt_filter": 2, "sm_filter": 2000 }
+{ "twothous": 0, "fivethous": 4000, "tenthous": 4000, "cnt": 5, "sm": 10000, "cnt_filter": 3, "sm_filter": 6000 }
+{ "twothous": 1000, "fivethous": 0, "tenthous": 5000, "cnt": 6, "sm": 15000, "cnt_filter": 3, "sm_filter": 6000 }
+{ "twothous": 0, "fivethous": 1000, "tenthous": 6000, "cnt": 7, "sm": 21000, "cnt_filter": 4, "sm_filter": 12000 }
+{ "twothous": 1000, "fivethous": 2000, "tenthous": 7000, "cnt": 8, "sm": 28000, "cnt_filter": 4, "sm_filter": 12000 }
+{ "twothous": 0, "fivethous": 3000, "tenthous": 8000, "cnt": 9, "sm": 36000, "cnt_filter": 5, "sm_filter": 20000 }
+{ "twothous": 1000, "fivethous": 4000, "tenthous": 9000, "cnt": 10, "sm": 45000, "cnt_filter": 5, "sm_filter": 20000 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.7.adm
new file mode 100644
index 0000000..7614e6e4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.7.adm
@@ -0,0 +1,20 @@
+{ "twenty": 0, "sm": 0, "sm_sm": 0, "sm_sm_where_twenty_lt_10": 0, "sm_sm_where_sm_lt_3k": 0 }
+{ "twenty": 1, "sm": 500, "sm_sm": 500, "sm_sm_where_twenty_lt_10": 500, "sm_sm_where_sm_lt_3k": 500 }
+{ "twenty": 2, "sm": 1000, "sm_sm": 1500, "sm_sm_where_twenty_lt_10": 1500, "sm_sm_where_sm_lt_3k": 1500 }
+{ "twenty": 3, "sm": 1500, "sm_sm": 3000, "sm_sm_where_twenty_lt_10": 3000, "sm_sm_where_sm_lt_3k": 3000 }
+{ "twenty": 4, "sm": 2000, "sm_sm": 5000, "sm_sm_where_twenty_lt_10": 5000, "sm_sm_where_sm_lt_3k": 5000 }
+{ "twenty": 5, "sm": 2500, "sm_sm": 7500, "sm_sm_where_twenty_lt_10": 7500, "sm_sm_where_sm_lt_3k": 7500 }
+{ "twenty": 6, "sm": 3000, "sm_sm": 10500, "sm_sm_where_twenty_lt_10": 10500, "sm_sm_where_sm_lt_3k": 7500 }
+{ "twenty": 7, "sm": 3500, "sm_sm": 14000, "sm_sm_where_twenty_lt_10": 14000, "sm_sm_where_sm_lt_3k": 7500 }
+{ "twenty": 8, "sm": 4000, "sm_sm": 18000, "sm_sm_where_twenty_lt_10": 18000, "sm_sm_where_sm_lt_3k": 7500 }
+{ "twenty": 9, "sm": 4500, "sm_sm": 22500, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 7500 }
+{ "twenty": 10, "sm": 0, "sm_sm": 22500, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 7500 }
+{ "twenty": 11, "sm": 500, "sm_sm": 23000, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 8000 }
+{ "twenty": 12, "sm": 1000, "sm_sm": 24000, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 9000 }
+{ "twenty": 13, "sm": 1500, "sm_sm": 25500, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 10500 }
+{ "twenty": 14, "sm": 2000, "sm_sm": 27500, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 12500 }
+{ "twenty": 15, "sm": 2500, "sm_sm": 30000, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 15000 }
+{ "twenty": 16, "sm": 3000, "sm_sm": 33000, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 15000 }
+{ "twenty": 17, "sm": 3500, "sm_sm": 36500, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 15000 }
+{ "twenty": 18, "sm": 4000, "sm_sm": 40500, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 15000 }
+{ "twenty": 19, "sm": 4500, "sm_sm": 45000, "sm_sm_where_twenty_lt_10": 22500, "sm_sm_where_sm_lt_3k": 15000 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.8.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.8.adm
new file mode 100644
index 0000000..a0630c4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-subclause/agg_filter_01/agg_filter_01.8.adm
@@ -0,0 +1,2 @@
+{ "two": 0, "sm": 5000 }
+{ "two": 1, "sm": 10000 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.01.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.01.ast
new file mode 100644
index 0000000..66ac84b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.01.ast
@@ -0,0 +1,22 @@
+DataverseUse test
+TypeDecl tenkType [
+  closed RecordType {
+    unique1 : integer,
+    unique2 : integer,
+    two : integer,
+    four : integer,
+    ten : integer,
+    twenty : integer,
+    hundred : integer,
+    thousand : integer,
+    twothous : integer,
+    fivethous : integer,
+    tenthous : integer,
+    odd100 : integer,
+    even100 : integer,
+    stringu1 : string,
+    stringu2 : string,
+    string4 : string
+  }
+]
+DatasetDecl tenk(tenkType) partitioned by [[unique2]]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.02.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.02.ast
new file mode 100644
index 0000000..916a59e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.02.ast
@@ -0,0 +1 @@
+DataverseUse test
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.03.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.03.ast
new file mode 100644
index 0000000..af7fbff
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.03.ast
@@ -0,0 +1,67 @@
+DataverseUse test
+Query:
+SELECT [
+FunctionCall asterix.sql-count@1[
+  (
+    SELECT ELEMENT [
+    LiteralExpr [LONG] [1]
+    ]
+    FROM [      Variable [ Name=#1 ]
+      AS Variable [ Name=#2 ]
+    ]
+    Where
+      OperatorExpr [
+        FieldAccessor [
+          FieldAccessor [
+            Variable [ Name=#2 ]
+            Field=tenk
+          ]
+          Field=four
+        ]
+        >
+        LiteralExpr [LONG] [0]
+      ]
+  )
+]
+cnt
+FunctionCall asterix.sql-sum@1[
+  (
+    SELECT ELEMENT [
+    FieldAccessor [
+      FieldAccessor [
+        Variable [ Name=#3 ]
+        Field=tenk
+      ]
+      Field=four
+    ]
+    ]
+    FROM [      Variable [ Name=#1 ]
+      AS Variable [ Name=#3 ]
+    ]
+    Where
+      OperatorExpr [
+        FieldAccessor [
+          FieldAccessor [
+            Variable [ Name=#3 ]
+            Field=tenk
+          ]
+          Field=four
+        ]
+        >
+        LiteralExpr [LONG] [0]
+      ]
+  )
+]
+sm
+]
+FROM [  FunctionCall asterix.dataset@1[
+    LiteralExpr [STRING] [test.tenk]
+  ]
+  AS Variable [ Name=$tenk ]
+]
+Group All
+  GROUP AS Variable [ Name=#1 ]
+  (
+    tenk:=Variable [ Name=$tenk ]
+  )
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.04.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.04.ast
new file mode 100644
index 0000000..11d3ca1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.04.ast
@@ -0,0 +1,79 @@
+DataverseUse test
+Query:
+SELECT [
+Variable [ Name=$two ]
+two
+FunctionCall asterix.sql-count@1[
+  (
+    SELECT ELEMENT [
+    LiteralExpr [LONG] [1]
+    ]
+    FROM [      Variable [ Name=#1 ]
+      AS Variable [ Name=#2 ]
+    ]
+    Where
+      OperatorExpr [
+        FieldAccessor [
+          FieldAccessor [
+            Variable [ Name=#2 ]
+            Field=tenk
+          ]
+          Field=four
+        ]
+        >
+        LiteralExpr [LONG] [0]
+      ]
+  )
+]
+cnt
+FunctionCall asterix.sql-sum@1[
+  (
+    SELECT ELEMENT [
+    FieldAccessor [
+      FieldAccessor [
+        Variable [ Name=#3 ]
+        Field=tenk
+      ]
+      Field=four
+    ]
+    ]
+    FROM [      Variable [ Name=#1 ]
+      AS Variable [ Name=#3 ]
+    ]
+    Where
+      OperatorExpr [
+        FieldAccessor [
+          FieldAccessor [
+            Variable [ Name=#3 ]
+            Field=tenk
+          ]
+          Field=four
+        ]
+        >
+        LiteralExpr [LONG] [0]
+      ]
+  )
+]
+sm
+]
+FROM [  FunctionCall asterix.dataset@1[
+    LiteralExpr [STRING] [test.tenk]
+  ]
+  AS Variable [ Name=$tenk ]
+]
+Groupby
+  Variable [ Name=$two ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$tenk ]
+    Field=two
+  ]
+  GROUP AS Variable [ Name=#1 ]
+  (
+    tenk:=Variable [ Name=$tenk ]
+  )
+
+Orderby
+  Variable [ Name=$two ]
+  ASC
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.05.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.05.ast
new file mode 100644
index 0000000..28919af
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.05.ast
@@ -0,0 +1,83 @@
+DataverseUse test
+Query:
+SELECT [
+Variable [ Name=$two ]
+two
+FunctionCall asterix.sql-count@1[
+  (
+    SELECT ELEMENT [
+    LiteralExpr [LONG] [1]
+    ]
+    FROM [      Variable [ Name=#1 ]
+      AS Variable [ Name=#2 ]
+    ]
+    Where
+      OperatorExpr [
+        FieldAccessor [
+          FieldAccessor [
+            Variable [ Name=#2 ]
+            Field=tenk
+          ]
+          Field=four
+        ]
+        >
+        LiteralExpr [LONG] [0]
+      ]
+  )
+]
+cnt
+FunctionCall asterix.sql-sum@1[
+  (
+    SELECT ELEMENT [
+    FieldAccessor [
+      FieldAccessor [
+        Variable [ Name=#3 ]
+        Field=tenk
+      ]
+      Field=four
+    ]
+    ]
+    FROM [      Variable [ Name=#1 ]
+      AS Variable [ Name=#3 ]
+    ]
+    Where
+      OperatorExpr [
+        FieldAccessor [
+          FieldAccessor [
+            Variable [ Name=#3 ]
+            Field=tenk
+          ]
+          Field=four
+        ]
+        >
+        LiteralExpr [LONG] [0]
+      ]
+  )
+]
+sm
+]
+FROM [  FunctionCall asterix.dataset@1[
+    LiteralExpr [STRING] [test.tenk]
+  ]
+  AS Variable [ Name=$tenk ]
+]
+Groupby
+  GROUPING SET (
+    Variable [ Name=$two ]
+    :=
+    FieldAccessor [
+      Variable [ Name=$tenk ]
+      Field=two
+    ]
+  )
+  GROUPING SET (
+  ),
+  GROUP AS Variable [ Name=#1 ]
+  (
+    tenk:=Variable [ Name=$tenk ]
+  )
+
+Orderby
+  Variable [ Name=$two ]
+  ASC
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.06.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.06.ast
new file mode 100644
index 0000000..60815f4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.06.ast
@@ -0,0 +1,102 @@
+DataverseUse test
+Query:
+SELECT [
+FieldAccessor [
+  Variable [ Name=$tenk ]
+  Field=twothous
+]
+twothous
+FieldAccessor [
+  Variable [ Name=$tenk ]
+  Field=fivethous
+]
+fivethous
+FieldAccessor [
+  Variable [ Name=$tenk ]
+  Field=tenthous
+]
+tenthous
+WINDOW test.count@1[
+  Variable [ Name=$tenthous ]
+]
+OVER (
+  ORDER BY
+    FieldAccessor [
+      Variable [ Name=$tenk ]
+      Field=tenthous
+    ]
+    ASC
+)
+cnt
+WINDOW test.sum@1[
+  Variable [ Name=$tenthous ]
+]
+OVER (
+  ORDER BY
+    FieldAccessor [
+      Variable [ Name=$tenk ]
+      Field=tenthous
+    ]
+    ASC
+)
+sm
+WINDOW test.count@1[
+  Variable [ Name=$tenthous ]
+]
+  FILTER (WHERE
+    OperatorExpr [
+      Variable [ Name=$twothous ]
+      =
+      LiteralExpr [LONG] [0]
+    ]
+  )
+OVER (
+  ORDER BY
+    FieldAccessor [
+      Variable [ Name=$tenk ]
+      Field=tenthous
+    ]
+    ASC
+)
+cnt_filter
+WINDOW test.sum@1[
+  Variable [ Name=$tenthous ]
+]
+  FILTER (WHERE
+    OperatorExpr [
+      Variable [ Name=$twothous ]
+      =
+      LiteralExpr [LONG] [0]
+    ]
+  )
+OVER (
+  ORDER BY
+    FieldAccessor [
+      Variable [ Name=$tenk ]
+      Field=tenthous
+    ]
+    ASC
+)
+sm_filter
+]
+FROM [  FunctionCall asterix.dataset@1[
+    LiteralExpr [STRING] [test.tenk]
+  ]
+  AS Variable [ Name=$tenk ]
+]
+Where
+  OperatorExpr [
+    FieldAccessor [
+      Variable [ Name=$tenk ]
+      Field=thousand
+    ]
+    =
+    LiteralExpr [LONG] [0]
+  ]
+Orderby
+  FieldAccessor [
+    Variable [ Name=$tenk ]
+    Field=tenthous
+  ]
+  ASC
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.07.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.07.ast
new file mode 100644
index 0000000..48f5c9d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.07.ast
@@ -0,0 +1,145 @@
+DataverseUse test
+Query:
+SELECT [
+Variable [ Name=$twenty ]
+twenty
+FunctionCall asterix.sql-sum@1[
+  (
+    SELECT ELEMENT [
+    FieldAccessor [
+      FieldAccessor [
+        Variable [ Name=#2 ]
+        Field=tenk
+      ]
+      Field=ten
+    ]
+    ]
+    FROM [      Variable [ Name=#1 ]
+      AS Variable [ Name=#2 ]
+    ]
+  )
+]
+sm
+WINDOW test.sum@1[
+  FunctionCall asterix.sql-sum@1[
+    (
+      SELECT ELEMENT [
+      FieldAccessor [
+        FieldAccessor [
+          Variable [ Name=#3 ]
+          Field=tenk
+        ]
+        Field=ten
+      ]
+      ]
+      FROM [        Variable [ Name=#1 ]
+        AS Variable [ Name=#3 ]
+      ]
+    )
+  ]
+]
+OVER (
+  ORDER BY
+    Variable [ Name=$twenty ]
+    ASC
+)
+sm_sm
+WINDOW test.sum@1[
+  FunctionCall asterix.sql-sum@1[
+    (
+      SELECT ELEMENT [
+      FieldAccessor [
+        FieldAccessor [
+          Variable [ Name=#4 ]
+          Field=tenk
+        ]
+        Field=ten
+      ]
+      ]
+      FROM [        Variable [ Name=#1 ]
+        AS Variable [ Name=#4 ]
+      ]
+    )
+  ]
+]
+  FILTER (WHERE
+    OperatorExpr [
+      Variable [ Name=$twenty ]
+      <
+      LiteralExpr [LONG] [10]
+    ]
+  )
+OVER (
+  ORDER BY
+    Variable [ Name=$twenty ]
+    ASC
+)
+sm_sm_where_twenty_lt_10
+WINDOW test.sum@1[
+  FunctionCall asterix.sql-sum@1[
+    (
+      SELECT ELEMENT [
+      FieldAccessor [
+        FieldAccessor [
+          Variable [ Name=#5 ]
+          Field=tenk
+        ]
+        Field=ten
+      ]
+      ]
+      FROM [        Variable [ Name=#1 ]
+        AS Variable [ Name=#5 ]
+      ]
+    )
+  ]
+]
+  FILTER (WHERE
+    OperatorExpr [
+      FunctionCall asterix.sql-sum@1[
+        (
+          SELECT ELEMENT [
+          FieldAccessor [
+            FieldAccessor [
+              Variable [ Name=#6 ]
+              Field=tenk
+            ]
+            Field=ten
+          ]
+          ]
+          FROM [            Variable [ Name=#1 ]
+            AS Variable [ Name=#6 ]
+          ]
+        )
+      ]
+      <
+      LiteralExpr [LONG] [3000]
+    ]
+  )
+OVER (
+  ORDER BY
+    Variable [ Name=$twenty ]
+    ASC
+)
+sm_sm_where_sm_lt_3k
+]
+FROM [  FunctionCall asterix.dataset@1[
+    LiteralExpr [STRING] [test.tenk]
+  ]
+  AS Variable [ Name=$tenk ]
+]
+Groupby
+  Variable [ Name=$twenty ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$tenk ]
+    Field=twenty
+  ]
+  GROUP AS Variable [ Name=#1 ]
+  (
+    tenk:=Variable [ Name=$tenk ]
+  )
+
+Orderby
+  Variable [ Name=$twenty ]
+  ASC
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.08.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.08.ast
new file mode 100644
index 0000000..311e37f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/aggregate-subclause/agg_filter_01/agg_filter_1.08.ast
@@ -0,0 +1,83 @@
+DataverseUse test
+Query:
+SELECT [
+Variable [ Name=$two ]
+two
+FunctionCall asterix.sql-sum@1[
+  (
+    SELECT ELEMENT [
+    FieldAccessor [
+      FieldAccessor [
+        Variable [ Name=#2 ]
+        Field=tenk
+      ]
+      Field=four
+    ]
+    ]
+    FROM [      Variable [ Name=#1 ]
+      AS Variable [ Name=#2 ]
+    ]
+    Where
+      QuantifiedExpression EVERY [
+        [Variable [ Name=$x ]
+        In
+          OrderedListConstructor [
+            FieldAccessor [
+              FieldAccessor [
+                Variable [ Name=#2 ]
+                Field=tenk
+              ]
+              Field=four
+            ]
+            OperatorExpr [
+              FieldAccessor [
+                FieldAccessor [
+                  Variable [ Name=#2 ]
+                  Field=tenk
+                ]
+                Field=four
+              ]
+              +
+              FieldAccessor [
+                FieldAccessor [
+                  Variable [ Name=#2 ]
+                  Field=tenk
+                ]
+                Field=four
+              ]
+            ]
+          ]
+        ]
+        Satifies [
+          OperatorExpr [
+            Variable [ Name=$x ]
+            >
+            LiteralExpr [LONG] [0]
+          ]
+        ]
+      ]
+  )
+]
+sm
+]
+FROM [  FunctionCall asterix.dataset@1[
+    LiteralExpr [STRING] [test.tenk]
+  ]
+  AS Variable [ Name=$tenk ]
+]
+Groupby
+  Variable [ Name=$two ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$tenk ]
+    Field=two
+  ]
+  GROUP AS Variable [ Name=#1 ]
+  (
+    tenk:=Variable [ Name=$tenk ]
+  )
+
+Orderby
+  Variable [ Name=$two ]
+  ASC
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index ed56b6e..abee116 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -2862,6 +2862,23 @@
       </compilation-unit>
     </test-case>
   </test-group>
+  <test-group name="aggregate-subclause">
+    <test-case FilePath="aggregate-subclause">
+      <compilation-unit name="agg_filter_01">
+        <output-dir compare="Text">agg_filter_01</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="aggregate-subclause">
+      <compilation-unit name="agg_filter_02_neg">
+        <output-dir compare="Text">agg_filter_01</output-dir>
+        <expected-error>ASX1116: Illegal use of aggregate FILTER clause</expected-error>
+        <expected-error>ASX1116: Illegal use of aggregate FILTER clause</expected-error>
+        <expected-error>ASX1073: Cannot resolve alias reference for undefined identifier r</expected-error>
+        <expected-error>ASX1116: Illegal use of aggregate FILTER clause</expected-error>
+        <expected-error>ASX1116: Illegal use of aggregate FILTER clause</expected-error>
+      </compilation-unit>
+    </test-case>
+  </test-group>
   <test-group name="array_fun">
     <test-case FilePath="array_fun">
       <compilation-unit name="array_prepend">
@@ -5395,7 +5412,7 @@
     <test-case FilePath="global-aggregate">
       <compilation-unit name="q12_error">
         <output-dir compare="Text">q01</output-dir>
-        <expected-error>The parameter * can only be used in COUNT().</expected-error>
+        <expected-error>The parameter * can only be used in count().</expected-error>
       </compilation-unit>
     </test-case>
     <test-case FilePath="global-aggregate">
@@ -11687,13 +11704,13 @@
     <test-case FilePath="user-defined-functions">
       <compilation-unit name="bad-function-ddl-9">
         <output-dir compare="Text">bad-function-ddl-9</output-dir>
-        <expected-error>function experiments.function_that_does_not_exist@0 is not defined</expected-error>
+        <expected-error>function experiments.function_that_does_not_exist@0 which is undefined</expected-error>
       </compilation-unit>
     </test-case>
     <test-case FilePath="user-defined-functions">
       <compilation-unit name="bad-function-ddl-10">
         <output-dir compare="Text">bad-function-ddl-10</output-dir>
-        <expected-error>function experiments.f0@2 is not defined</expected-error>
+        <expected-error>function experiments.f0@2 which is undefined</expected-error>
       </compilation-unit>
     </test-case>
     <test-case FilePath="user-defined-functions">
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp_parser.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp_parser.xml
index e9b1de0..9c4bb41 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp_parser.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp_parser.xml
@@ -868,6 +868,13 @@
       </compilation-unit>
     </test-case>
   </test-group>
+  <test-group name="aggregate-subclause">
+    <test-case FilePath="aggregate-subclause">
+      <compilation-unit name="agg_filter_01">
+        <output-dir compare="AST">agg_filter_01</output-dir>
+      </compilation-unit>
+    </test-case>
+  </test-group>
   <test-group name="boolean">
     <test-case FilePath="boolean">
       <compilation-unit name="and_01">
diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
index 97d653d..2bb62b1 100644
--- a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
@@ -205,6 +205,7 @@
     public static final int COMPILATION_GROUPING_SETS_OVERFLOW = 1113;
     public static final int COMPILATION_GROUPING_OPERATION_INVALID_ARG = 1114;
     public static final int COMPILATION_UNEXPECTED_ALIAS = 1115;
+    public static final int COMPILATION_ILLEGAL_USE_OF_FILTER_CLAUSE = 1116;
 
     // Feed errors
     public static final int DATAFLOW_ILLEGAL_STATE = 3001;
diff --git a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
index 2de5f48..fa0f469 100644
--- a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
+++ b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
@@ -200,6 +200,7 @@
 1113 = Too many grouping sets in group by clause: %1$s. Maximum allowed: %2$s.
 1114 = Invalid argument to grouping() function
 1115 = Unexpected alias: %1$s
+1116 = Illegal use of aggregate FILTER clause
 
 # Feed Errors
 3001 = Illegal state.
diff --git a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
index e19e2ec..d2ec756 100644
--- a/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
+++ b/asterixdb/asterix-doc/src/main/markdown/sqlpp/3_query.md
@@ -1266,7 +1266,7 @@
 Rather, the `COUNT` query above is using a special "sugared" function symbol that the query compiler
 will rewrite as follows:
 
-    SELECT uid AS uid, ARRAY_COUNT( (SELECT VALUE 1 FROM `$1` as g) ) AS msgCnt
+    SELECT uid AS uid, ARRAY_COUNT( (SELECT VALUE 1 FROM `$1` AS g) ) AS msgCnt
     FROM GleambookMessages msg
     GROUP BY msg.authorId AS uid
     GROUP AS `$1`(msg AS msg);
@@ -1298,6 +1298,23 @@
 Note that the `ARRAY_AGG` function symbol is rewritten simply to return the result of the generated subquery,
 without applying any built-in function.
 
+SQL aggregate function calls optionally support a FILTER subclause.
+
+##### Example
+
+    SELECT uid, COUNT(*) FILTER (WHERE msg.message LIKE "%awesome%") AS msgCnt
+    FROM GleambookMessages msg
+    GROUP BY msg.authorId AS uid;
+
+The query compiler rewrites this query to use the built-in aggregate as follows:
+
+    SELECT uid AS uid, ARRAY_COUNT( (SELECT VALUE 1 FROM `$1` AS g WHERE g.msg.message LIKE "%awesome%") ) AS msgCnt
+    FROM GleambookMessages msg
+    GROUP BY msg.authorId AS uid
+    GROUP AS `$1`(msg AS msg);
+
+Note that the FILTER subclause is not supported for built-in aggregate function calls.
+
 ### <a id="SQL-92_compliant_gby">SQL-92 Compliant GROUP BY Aggregations</a>
 The query language provides full support for SQL-92 `GROUP BY` aggregation queries.
 The following query is such an example:
diff --git a/asterixdb/asterix-lang-aql/src/main/java/org/apache/asterix/lang/aql/rewrites/visitor/AqlBuiltinFunctionRewriteVisitor.java b/asterixdb/asterix-lang-aql/src/main/java/org/apache/asterix/lang/aql/rewrites/visitor/AqlBuiltinFunctionRewriteVisitor.java
index 4f422d4..5b52ef5 100644
--- a/asterixdb/asterix-lang-aql/src/main/java/org/apache/asterix/lang/aql/rewrites/visitor/AqlBuiltinFunctionRewriteVisitor.java
+++ b/asterixdb/asterix-lang-aql/src/main/java/org/apache/asterix/lang/aql/rewrites/visitor/AqlBuiltinFunctionRewriteVisitor.java
@@ -41,7 +41,9 @@
             newExprList.add(expr.accept(this, arg));
         }
         callExpr.setExprList(newExprList);
+        if (callExpr.hasAggregateFilterExpr()) {
+            callExpr.setAggregateFilterExpr(callExpr.getAggregateFilterExpr().accept(this, arg));
+        }
         return callExpr;
     }
-
 }
diff --git a/asterixdb/asterix-lang-aql/src/main/java/org/apache/asterix/lang/aql/visitor/base/AbstractAqlSimpleExpressionVisitor.java b/asterixdb/asterix-lang-aql/src/main/java/org/apache/asterix/lang/aql/visitor/base/AbstractAqlSimpleExpressionVisitor.java
index 16bf19e..b8c3470 100644
--- a/asterixdb/asterix-lang-aql/src/main/java/org/apache/asterix/lang/aql/visitor/base/AbstractAqlSimpleExpressionVisitor.java
+++ b/asterixdb/asterix-lang-aql/src/main/java/org/apache/asterix/lang/aql/visitor/base/AbstractAqlSimpleExpressionVisitor.java
@@ -177,6 +177,9 @@
     @Override
     public Expression visit(CallExpr callExpr, ILangExpression arg) throws CompilationException {
         callExpr.setExprList(visit(callExpr.getExprList(), arg));
+        if (callExpr.hasAggregateFilterExpr()) {
+            callExpr.setAggregateFilterExpr(visit(callExpr.getAggregateFilterExpr(), arg));
+        }
         return callExpr;
     }
 
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/expression/CallExpr.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/expression/CallExpr.java
index 6582b9d..d0c29d5 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/expression/CallExpr.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/expression/CallExpr.java
@@ -30,10 +30,16 @@
 public class CallExpr extends AbstractExpression {
     private FunctionSignature functionSignature;
     private List<Expression> exprList;
+    private Expression aggFilterExpr;
 
     public CallExpr(FunctionSignature functionSignature, List<Expression> exprList) {
+        this(functionSignature, exprList, null);
+    }
+
+    public CallExpr(FunctionSignature functionSignature, List<Expression> exprList, Expression aggFilterExpr) {
         this.functionSignature = functionSignature;
         this.exprList = exprList;
+        this.aggFilterExpr = aggFilterExpr;
     }
 
     public FunctionSignature getFunctionSignature() {
@@ -44,6 +50,14 @@
         return exprList;
     }
 
+    public boolean hasAggregateFilterExpr() {
+        return aggFilterExpr != null;
+    }
+
+    public Expression getAggregateFilterExpr() {
+        return aggFilterExpr;
+    }
+
     @Override
     public Kind getKind() {
         return Kind.CALL_EXPRESSION;
@@ -57,6 +71,10 @@
         this.exprList = exprList;
     }
 
+    public void setAggregateFilterExpr(Expression aggFilterExpr) {
+        this.aggFilterExpr = aggFilterExpr;
+    }
+
     @Override
     public <R, T> R accept(ILangVisitor<R, T> visitor, T arg) throws CompilationException {
         return visitor.visit(this, arg);
@@ -69,7 +87,7 @@
 
     @Override
     public int hashCode() {
-        return Objects.hash(exprList, functionSignature);
+        return Objects.hash(exprList, aggFilterExpr, functionSignature);
     }
 
     @Override
@@ -81,6 +99,7 @@
             return false;
         }
         CallExpr target = (CallExpr) object;
-        return Objects.equals(exprList, target.exprList) && Objects.equals(functionSignature, target.functionSignature);
+        return Objects.equals(exprList, target.exprList) && Objects.equals(aggFilterExpr, target.aggFilterExpr)
+                && Objects.equals(functionSignature, target.functionSignature);
     }
 }
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/AbstractInlineUdfsVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/AbstractInlineUdfsVisitor.java
index 282be2f..984ceb4 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/AbstractInlineUdfsVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/AbstractInlineUdfsVisitor.java
@@ -132,10 +132,16 @@
     }
 
     @Override
-    public Boolean visit(CallExpr pf, List<FunctionDecl> arg) throws CompilationException {
-        Pair<Boolean, List<Expression>> p = inlineUdfsInExprList(pf.getExprList(), arg);
-        pf.setExprList(p.second);
-        return p.first;
+    public Boolean visit(CallExpr callExpr, List<FunctionDecl> arg) throws CompilationException {
+        Pair<Boolean, List<Expression>> p = inlineUdfsInExprList(callExpr.getExprList(), arg);
+        callExpr.setExprList(p.second);
+        boolean changed = p.first;
+        if (callExpr.hasAggregateFilterExpr()) {
+            Pair<Boolean, Expression> be = inlineUdfsInExpr(callExpr.getAggregateFilterExpr(), arg);
+            callExpr.setAggregateFilterExpr(be.second);
+            changed |= be.first;
+        }
+        return changed;
     }
 
     @Override
@@ -289,6 +295,10 @@
         if (implem == null) {
             return new Pair<>(r, expr);
         } else {
+            if (f.hasAggregateFilterExpr()) {
+                throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_USE_OF_FILTER_CLAUSE,
+                        f.getSourceLocation());
+            }
             // Rewrite the function body itself (without setting unbounded variables to dataset access).
             // TODO(buyingyi): throw an exception for recursive function definition or limit the stack depth.
             implem.setFuncBody(rewriteFunctionBody(implem));
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/CloneAndSubstituteVariablesVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/CloneAndSubstituteVariablesVisitor.java
index 8d37e41..567dd4a 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/CloneAndSubstituteVariablesVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/CloneAndSubstituteVariablesVisitor.java
@@ -143,12 +143,19 @@
     }
 
     @Override
-    public Pair<ILangExpression, VariableSubstitutionEnvironment> visit(CallExpr pf,
+    public Pair<ILangExpression, VariableSubstitutionEnvironment> visit(CallExpr callExpr,
             VariableSubstitutionEnvironment env) throws CompilationException {
-        List<Expression> exprList = VariableCloneAndSubstitutionUtil.visitAndCloneExprList(pf.getExprList(), env, this);
-        CallExpr f = new CallExpr(pf.getFunctionSignature(), exprList);
-        f.setSourceLocation(pf.getSourceLocation());
-        f.addHints(pf.getHints());
+        List<Expression> exprList =
+                VariableCloneAndSubstitutionUtil.visitAndCloneExprList(callExpr.getExprList(), env, this);
+        Expression newFilterExpr = null;
+        if (callExpr.hasAggregateFilterExpr()) {
+            Pair<ILangExpression, VariableSubstitutionEnvironment> paf =
+                    callExpr.getAggregateFilterExpr().accept(this, env);
+            newFilterExpr = (Expression) paf.first;
+        }
+        CallExpr f = new CallExpr(callExpr.getFunctionSignature(), exprList, newFilterExpr);
+        f.setSourceLocation(callExpr.getSourceLocation());
+        f.addHints(callExpr.getHints());
         return new Pair<>(f, env);
     }
 
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/FormatPrintVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/FormatPrintVisitor.java
index c9ec456..1de5dcf 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/FormatPrintVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/FormatPrintVisitor.java
@@ -226,6 +226,11 @@
                 callExpr.getFunctionSignature().getName()) + "(");
         printDelimitedExpressions(callExpr.getExprList(), COMMA, step);
         out.print(")");
+        if (callExpr.hasAggregateFilterExpr()) {
+            out.println(" FILTER ( WHERE ");
+            callExpr.getAggregateFilterExpr().accept(this, step + 1);
+            out.println(skip(step) + ")");
+        }
         return null;
     }
 
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/GatherFunctionCallsVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/GatherFunctionCallsVisitor.java
index 73f489c..3d7b467 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/GatherFunctionCallsVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/GatherFunctionCallsVisitor.java
@@ -19,7 +19,7 @@
 
 package org.apache.asterix.lang.common.visitor;
 
-import java.util.HashSet;
+import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
@@ -57,14 +57,17 @@
 
 public class GatherFunctionCallsVisitor extends AbstractQueryExpressionVisitor<Void, Void> {
 
-    protected final Set<CallExpr> calls = new HashSet<>();
+    protected final Set<CallExpr> calls = new LinkedHashSet<>();
 
     @Override
-    public Void visit(CallExpr pf, Void arg) throws CompilationException {
-        calls.add(pf);
-        for (Expression e : pf.getExprList()) {
+    public Void visit(CallExpr callExpr, Void arg) throws CompilationException {
+        calls.add(callExpr);
+        for (Expression e : callExpr.getExprList()) {
             e.accept(this, arg);
         }
+        if (callExpr.hasAggregateFilterExpr()) {
+            callExpr.getAggregateFilterExpr().accept(this, arg);
+        }
         return null;
     }
 
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/QueryPrintVisitor.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/QueryPrintVisitor.java
index fd7ef7a..f83a89e 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/QueryPrintVisitor.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/visitor/QueryPrintVisitor.java
@@ -140,20 +140,26 @@
     }
 
     @Override
-    public Void visit(CallExpr pf, Integer step) throws CompilationException {
-        return printFunctionCall(pf.getFunctionSignature(), pf.getFunctionSignature().getArity(), pf.getExprList(),
-                step);
+    public Void visit(CallExpr callExpr, Integer step) throws CompilationException {
+        return printFunctionCall(callExpr.getFunctionSignature(), callExpr.getFunctionSignature().getArity(),
+                callExpr.getExprList(), callExpr.getAggregateFilterExpr(), step);
     }
 
-    protected Void printFunctionCall(FunctionSignature fs, int arity, List<Expression> argList, Integer step)
-            throws CompilationException {
+    protected Void printFunctionCall(FunctionSignature fs, int arity, List<Expression> argList,
+            Expression aggFilterExpr, Integer step) throws CompilationException {
         out.print(skip(step) + "FunctionCall ");
         printFunctionSignature(out, fs, arity);
         out.println("[");
         for (Expression expr : argList) {
             expr.accept(this, step + 1);
         }
-        out.println(skip(step) + "]");
+        out.print(skip(step) + "]");
+        if (aggFilterExpr != null) {
+            out.println(" filter [");
+            aggFilterExpr.accept(this, step + 1);
+            out.print(skip(step) + "]");
+        }
+        out.println();
         return null;
     }
 
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/expression/WindowExpression.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/expression/WindowExpression.java
index e0a6c6b..9b603cd 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/expression/WindowExpression.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/expression/WindowExpression.java
@@ -39,6 +39,7 @@
 
     private FunctionSignature functionSignature;
     private List<Expression> exprList;
+    private Expression aggFilterExpr;
 
     private List<Expression> partitionList;
     private List<Expression> orderbyList;
@@ -57,7 +58,7 @@
     private Boolean ignoreNulls;
     private Boolean fromLast;
 
-    public WindowExpression(FunctionSignature functionSignature, List<Expression> exprList,
+    public WindowExpression(FunctionSignature functionSignature, List<Expression> exprList, Expression aggFilterExpr,
             List<Expression> partitionList, List<Expression> orderbyList,
             List<OrderbyClause.OrderModifier> orderbyModifierList, FrameMode frameMode,
             FrameBoundaryKind frameStartKind, Expression frameStartExpr, FrameBoundaryKind frameEndKind,
@@ -68,6 +69,7 @@
         }
         this.functionSignature = functionSignature;
         this.exprList = exprList;
+        this.aggFilterExpr = aggFilterExpr;
         this.partitionList = partitionList;
         this.orderbyList = orderbyList;
         this.orderbyModifierList = orderbyModifierList;
@@ -110,6 +112,18 @@
         this.exprList = exprList;
     }
 
+    public boolean hasAggregateFilterExpr() {
+        return aggFilterExpr != null;
+    }
+
+    public Expression getAggregateFilterExpr() {
+        return aggFilterExpr;
+    }
+
+    public void setAggregateFilterExpr(Expression filterExpr) {
+        this.aggFilterExpr = filterExpr;
+    }
+
     public boolean hasPartitionList() {
         return partitionList != null && !partitionList.isEmpty();
     }
@@ -244,7 +258,7 @@
 
     @Override
     public int hashCode() {
-        return Objects.hash(functionSignature, exprList, ExpressionUtils.emptyIfNull(partitionList),
+        return Objects.hash(functionSignature, exprList, aggFilterExpr, ExpressionUtils.emptyIfNull(partitionList),
                 ExpressionUtils.emptyIfNull(orderbyList), ExpressionUtils.emptyIfNull(orderbyModifierList), frameMode,
                 frameStartKind, frameStartExpr, frameEndKind, frameEndExpr, frameExclusionKind, windowVar,
                 ExpressionUtils.emptyIfNull(windowFieldList), ignoreNulls, fromLast);
@@ -261,6 +275,7 @@
         }
         WindowExpression target = (WindowExpression) object;
         return Objects.equals(functionSignature, target.functionSignature) && Objects.equals(exprList, target.exprList)
+                && Objects.equals(aggFilterExpr, target.aggFilterExpr)
                 && Objects.equals(ExpressionUtils.emptyIfNull(partitionList),
                         ExpressionUtils.emptyIfNull(target.partitionList))
                 && Objects.equals(ExpressionUtils.emptyIfNull(orderbyList),
@@ -290,6 +305,9 @@
         if (ignoreNulls != null && ignoreNulls) {
             sb.append(" IGNORE NULLS");
         }
+        if (aggFilterExpr != null) {
+            sb.append(" FILTER (WHERE ").append(aggFilterExpr).append(')');
+        }
         sb.append(" OVER ");
         if (hasWindowVar()) {
             sb.append(windowVar);
@@ -346,7 +364,7 @@
         ROWS("rows"),
         GROUPS("groups");
 
-        private String text;
+        private final String text;
 
         FrameMode(String text) {
             this.text = text;
@@ -365,7 +383,7 @@
         BOUNDED_PRECEDING("preceding"),
         BOUNDED_FOLLOWING("following");
 
-        private String text;
+        private final String text;
 
         FrameBoundaryKind(String text) {
             this.text = text;
@@ -383,7 +401,7 @@
         TIES("ties"),
         NO_OTHERS("no others");
 
-        private String text;
+        private final String text;
 
         FrameExclusionKind(String text) {
             this.text = text;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
index 1ccea2a..1674940 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
@@ -489,6 +489,9 @@
                     p.first.accept(this, arg);
                 }
             }
+            if (winExpr.hasAggregateFilterExpr()) {
+                winExpr.getAggregateFilterExpr().accept(this, arg);
+            }
             for (Expression expr : winExpr.getExprList()) {
                 expr.accept(this, arg);
             }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/AbstractSqlppExpressionExtractionVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/AbstractSqlppExpressionExtractionVisitor.java
index 6b57089..d251fb1 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/AbstractSqlppExpressionExtractionVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/AbstractSqlppExpressionExtractionVisitor.java
@@ -23,7 +23,6 @@
 import java.util.ArrayList;
 import java.util.Deque;
 import java.util.List;
-import java.util.function.Predicate;
 
 import org.apache.asterix.common.exceptions.CompilationException;
 import org.apache.asterix.lang.common.base.AbstractClause;
@@ -42,8 +41,6 @@
 
 /**
  * Base class for visitors that extract expressions into LET clauses.
- * Subclasses should call {@link #extractExpressionsFromList(List, int, Predicate)} or
- * {@link #extractExpression(Expression)} to perform the extraction.
  */
 abstract class AbstractSqlppExpressionExtractionVisitor extends AbstractSqlppSimpleExpressionVisitor {
 
@@ -142,35 +139,6 @@
         fromBindingList.clear();
     }
 
-    protected List<Expression> extractExpressionsFromList(List<Expression> exprList, int limit,
-            Predicate<Expression> exprTest) {
-        StackElement outElement = stack.peek();
-        if (outElement == null) {
-            return null;
-        }
-        int n = exprList.size();
-        List<Expression> newExprList = new ArrayList<>(n);
-        for (int i = 0; i < n; i++) {
-            Expression expr = exprList.get(i);
-            Expression newExpr = i < limit && exprTest.test(expr) ? extractExpressionImpl(expr, outElement) : expr;
-            newExprList.add(newExpr);
-        }
-        return newExprList;
-    }
-
-    protected Expression extractExpression(Expression expr) {
-        StackElement outLetList = stack.peek();
-        return outLetList != null ? extractExpressionImpl(expr, outLetList) : null;
-    }
-
-    private VariableExpr extractExpressionImpl(Expression expr, StackElement outElement) {
-        VarIdentifier v = context.newVariable();
-        VariableExpr vExpr = new VariableExpr(v);
-        vExpr.setSourceLocation(expr.getSourceLocation());
-        outElement.extractionList.add(new Pair<>(expr, v));
-        return vExpr;
-    }
-
     abstract void handleUnsupportedClause(FromClause clause) throws CompilationException;
 
     protected final class StackElement {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/Sql92AggregateFunctionVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/Sql92AggregateFunctionVisitor.java
index cb6b396..f93e843 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/Sql92AggregateFunctionVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/Sql92AggregateFunctionVisitor.java
@@ -30,8 +30,10 @@
 import org.apache.asterix.common.exceptions.CompilationException;
 import org.apache.asterix.common.exceptions.ErrorCode;
 import org.apache.asterix.common.functions.FunctionSignature;
+import org.apache.asterix.lang.common.base.AbstractClause;
 import org.apache.asterix.lang.common.base.Expression;
 import org.apache.asterix.lang.common.base.ILangExpression;
+import org.apache.asterix.lang.common.clause.WhereClause;
 import org.apache.asterix.lang.common.expression.CallExpr;
 import org.apache.asterix.lang.common.expression.FieldAccessor;
 import org.apache.asterix.lang.common.expression.VariableExpr;
@@ -58,7 +60,14 @@
  * <code>SUM(e.salary + i.bonus)</code>
  * is turned into
  * <code>array_sum( (FROM g AS gi SELECT ELEMENT gi.e.salary + gi.i.bonus) )</code>
- * where <code>g</code> is a 'group as' variable
+ * where <code>g</code> is a 'group as' variable.
+ * <br/>
+ * If the SQL-92 aggregate function call contains a filter expression then that filter expression
+ * becomes a WHERE clause. <br/>
+ * For example
+ * <code>SUM(e.salary + i.bonus) FILTER (WHERE e.dept = 100)</code>
+ * is turned into
+ * <code>array_sum( (FROM g AS gi WHERE gi.e.dept = 100 SELECT ELEMENT gi.e.salary + gi.i.bonus) )</code>
  */
 class Sql92AggregateFunctionVisitor extends AbstractSqlppSimpleExpressionVisitor {
 
@@ -87,78 +96,74 @@
 
     @Override
     public Expression visit(CallExpr callExpr, ILangExpression arg) throws CompilationException {
-        List<Expression> newExprList = new ArrayList<>();
         FunctionSignature signature = callExpr.getFunctionSignature();
-        boolean aggregate = FunctionMapUtil.isSql92AggregateFunction(signature);
-        boolean rewritten = false;
-        for (Expression expr : callExpr.getExprList()) {
-            Expression newExpr = aggregate ? wrapAggregationArgument(expr, groupVar, groupVarFieldMap,
-                    preGroupContextVars, preGroupUnmappedVars, outerVars, context) : expr;
-            rewritten |= newExpr != expr;
-            newExprList.add(newExpr.accept(this, arg));
+        if (FunctionMapUtil.isSql92AggregateFunction(signature)) {
+            rewriteSql92AggregateFunction(callExpr, arg);
+            return callExpr;
+        } else {
+            return super.visit(callExpr, arg);
         }
-        if (rewritten) {
-            // Rewrites the SQL-92 function name to core functions,
-            // e.g., SUM --> array_sum
-            callExpr.setFunctionSignature(FunctionMapUtil.sql92ToCoreAggregateFunction(signature));
-        }
-        callExpr.setExprList(newExprList);
-        return callExpr;
     }
 
-    static Expression wrapAggregationArgument(Expression expr, Expression groupVar,
+    private void rewriteSql92AggregateFunction(CallExpr callExpr, ILangExpression arg) throws CompilationException {
+        FunctionSignature signature = callExpr.getFunctionSignature();
+        List<Expression> argList = callExpr.getExprList();
+        if (argList.size() != 1) {
+            // binary SQL-92 aggregate functions are not yet supported
+            throw new CompilationException(ErrorCode.COMPILATION_INVALID_PARAMETER_NUMBER, callExpr.getSourceLocation(),
+                    signature.getName(), argList.size());
+        }
+        Expression filterExpr = callExpr.getAggregateFilterExpr();
+        Expression expr = argList.get(0);
+        Expression newExpr = wrapAggregationArgument(expr, filterExpr, groupVar, groupVarFieldMap, preGroupContextVars,
+                preGroupUnmappedVars, outerVars, context);
+        List<Expression> newExprList = new ArrayList<>(1);
+        newExprList.add(newExpr.accept(this, arg));
+        // Rewrites the SQL-92 function name to core functions,
+        // e.g., SUM --> array_sum
+        callExpr.setFunctionSignature(FunctionMapUtil.sql92ToCoreAggregateFunction(signature));
+        callExpr.setExprList(newExprList);
+        callExpr.setAggregateFilterExpr(null);
+    }
+
+    static Expression wrapAggregationArgument(Expression expr, Expression filterExpr, Expression groupVar,
             Map<VariableExpr, Identifier> groupVarFieldMap, Collection<VariableExpr> preGroupContextVars,
             Collection<VariableExpr> preGroupUnmappedVars, Collection<VariableExpr> outerVars,
             LangRewritingContext context) throws CompilationException {
         SourceLocation sourceLoc = expr.getSourceLocation();
-        Set<VariableExpr> freeVars = SqlppRewriteUtil.getFreeVariable(expr);
 
-        VariableExpr fromBindingVar = new VariableExpr(context.newVariable());
-        fromBindingVar.setSourceLocation(sourceLoc);
-        FromTerm fromTerm = new FromTerm(groupVar, fromBindingVar, null, null);
+        // From clause
+        VariableExpr groupItemVar = new VariableExpr(context.newVariable());
+        groupItemVar.setSourceLocation(sourceLoc);
+
+        FromTerm fromTerm = new FromTerm(groupVar, groupItemVar, null, null);
         fromTerm.setSourceLocation(sourceLoc);
         FromClause fromClause = new FromClause(Collections.singletonList(fromTerm));
         fromClause.setSourceLocation(sourceLoc);
 
-        // Maps field variable expressions to field accesses.
-        Map<Expression, Expression> varExprMap = new HashMap<>();
-        for (VariableExpr usedVar : freeVars) {
-            // Reference to a field in the group variable.
-            if (groupVarFieldMap.containsKey(usedVar)) {
-                // Rewrites to a reference to a field in the group variable.
-                FieldAccessor fa =
-                        new FieldAccessor(fromBindingVar, new VarIdentifier(groupVarFieldMap.get(usedVar).getValue()));
-                fa.setSourceLocation(usedVar.getSourceLocation());
-                varExprMap.put(usedVar, fa);
-            } else if (outerVars.contains(usedVar)) {
-                // Do nothing
-            } else if (preGroupUnmappedVars != null && preGroupUnmappedVars.contains(usedVar)) {
-                throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_USE_OF_IDENTIFIER, sourceLoc,
-                        SqlppVariableUtil.toUserDefinedVariableName(usedVar.getVar().getValue()).getValue());
-            } else {
-                // Rewrites to a reference to a single field in the group variable.
-                VariableExpr preGroupVar = VariableCheckAndRewriteVisitor.pickContextVar(preGroupContextVars, usedVar);
-                Identifier groupVarField = groupVarFieldMap.get(preGroupVar);
-                if (groupVarField == null) {
-                    throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, sourceLoc);
-                }
-                FieldAccessor faInner = new FieldAccessor(fromBindingVar, groupVarField);
-                faInner.setSourceLocation(usedVar.getSourceLocation());
-                Expression faOuter = VariableCheckAndRewriteVisitor.generateFieldAccess(faInner, usedVar.getVar(),
-                        usedVar.getSourceLocation());
-                varExprMap.put(usedVar, faOuter);
-            }
+        // Where clause if filter expression is present
+
+        List<AbstractClause> whereClauseList = null;
+        if (filterExpr != null) {
+            Expression newFilterExpr = rewriteAggregationArgumentExpr(filterExpr, groupItemVar, groupVarFieldMap,
+                    preGroupContextVars, preGroupUnmappedVars, outerVars, context);
+            WhereClause whereClause = new WhereClause(newFilterExpr);
+            whereClause.setSourceLocation(sourceLoc);
+            whereClauseList = new ArrayList<>(1);
+            whereClauseList.add(whereClause);
         }
 
         // Select clause.
-        SelectElement selectElement =
-                new SelectElement(SqlppRewriteUtil.substituteExpression(expr, varExprMap, context));
+        Expression newExpr = rewriteAggregationArgumentExpr(expr, groupItemVar, groupVarFieldMap, preGroupContextVars,
+                preGroupUnmappedVars, outerVars, context);
+
+        SelectElement selectElement = new SelectElement(newExpr);
         selectElement.setSourceLocation(sourceLoc);
         SelectClause selectClause = new SelectClause(selectElement, null, false);
         selectClause.setSourceLocation(sourceLoc);
 
         // Construct the select expression.
-        SelectBlock selectBlock = new SelectBlock(selectClause, fromClause, null, null, null);
+        SelectBlock selectBlock = new SelectBlock(selectClause, fromClause, whereClauseList, null, null);
         selectBlock.setSourceLocation(sourceLoc);
         SelectSetOperation selectSetOperation = new SelectSetOperation(new SetOperationInput(selectBlock, null), null);
         selectSetOperation.setSourceLocation(sourceLoc);
@@ -166,4 +171,42 @@
         selectExpr.setSourceLocation(sourceLoc);
         return selectExpr;
     }
+
+    private static Expression rewriteAggregationArgumentExpr(Expression expr, VariableExpr groupItemVar,
+            Map<VariableExpr, Identifier> groupVarFieldMap, Collection<VariableExpr> preGroupContextVars,
+            Collection<VariableExpr> preGroupUnmappedVars, Collection<VariableExpr> outerVars,
+            LangRewritingContext context) throws CompilationException {
+        // Maps field variable expressions to field accesses.
+        Set<VariableExpr> freeVars = SqlppRewriteUtil.getFreeVariable(expr);
+        Map<Expression, Expression> varExprMap = new HashMap<>();
+        for (VariableExpr usedVar : freeVars) {
+            // Reference to a field in the group variable.
+            if (groupVarFieldMap.containsKey(usedVar)) {
+                // Rewrites to a reference to a field in the group variable.
+                FieldAccessor fa =
+                        new FieldAccessor(groupItemVar, new VarIdentifier(groupVarFieldMap.get(usedVar).getValue()));
+                fa.setSourceLocation(usedVar.getSourceLocation());
+                varExprMap.put(usedVar, fa);
+            } else if (outerVars.contains(usedVar)) {
+                // Do nothing
+            } else if (preGroupUnmappedVars != null && preGroupUnmappedVars.contains(usedVar)) {
+                throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_USE_OF_IDENTIFIER,
+                        expr.getSourceLocation(),
+                        SqlppVariableUtil.toUserDefinedVariableName(usedVar.getVar().getValue()).getValue());
+            } else {
+                // Rewrites to a reference to a single field in the group variable.
+                VariableExpr preGroupVar = VariableCheckAndRewriteVisitor.pickContextVar(preGroupContextVars, usedVar);
+                Identifier groupVarField = groupVarFieldMap.get(preGroupVar);
+                if (groupVarField == null) {
+                    throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, expr.getSourceLocation());
+                }
+                FieldAccessor faInner = new FieldAccessor(groupItemVar, groupVarField);
+                faInner.setSourceLocation(usedVar.getSourceLocation());
+                Expression faOuter = VariableCheckAndRewriteVisitor.generateFieldAccess(faInner, usedVar.getVar(),
+                        usedVar.getSourceLocation());
+                varExprMap.put(usedVar, faOuter);
+            }
+        }
+        return SqlppRewriteUtil.substituteExpression(expr, varExprMap, context);
+    }
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppCaseRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppCaseRewriteVisitor.java
index 24a9c1f..cfe41ed 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppCaseRewriteVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppCaseRewriteVisitor.java
@@ -29,6 +29,7 @@
 import org.apache.asterix.lang.common.clause.GroupbyClause;
 import org.apache.asterix.lang.common.expression.CallExpr;
 import org.apache.asterix.lang.common.expression.IfExpr;
+import org.apache.asterix.lang.common.expression.VariableExpr;
 import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
 import org.apache.asterix.lang.common.struct.VarIdentifier;
 import org.apache.asterix.lang.sqlpp.clause.FromClause;
@@ -87,9 +88,12 @@
         public Expression visit(CallExpr callExpr, ILangExpression arg) throws CompilationException {
             CallExpr resultExpr = (CallExpr) super.visit(callExpr, arg);
             if (FunctionMapUtil.isSql92AggregateFunction(resultExpr.getFunctionSignature())) {
-                Expression newExpr = extractExpression(resultExpr);
-                if (newExpr != null) {
-                    return newExpr;
+                StackElement stackElement = stack.peek();
+                if (stackElement != null && stackElement.getSelectBlock().hasGroupbyClause()) {
+                    VarIdentifier v = stackElement.addPendingLetClause(resultExpr);
+                    VariableExpr vExpr = new VariableExpr(v);
+                    vExpr.setSourceLocation(callExpr.getSourceLocation());
+                    return vExpr;
                 }
             }
             return resultExpr;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppGroupingSetsVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppGroupingSetsVisitor.java
index 4e517da..a6892c1 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppGroupingSetsVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppGroupingSetsVisitor.java
@@ -204,6 +204,10 @@
             throw new CompilationException(ErrorCode.COMPILATION_INVALID_NUM_OF_ARGS, callExpr.getSourceLocation(),
                     BuiltinFunctions.GROUPING.getName());
         }
+        if (callExpr.hasAggregateFilterExpr()) {
+            throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_USE_OF_FILTER_CLAUSE,
+                    callExpr.getSourceLocation());
+        }
         long result = 0;
         for (Expression argExpr : argList) {
             int v;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppInlineUdfsVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppInlineUdfsVisitor.java
index 513d33c..4fb17f0 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppInlineUdfsVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppInlineUdfsVisitor.java
@@ -270,6 +270,11 @@
             winExpr.setWindowFieldList(inlinedList.second);
             inlined |= inlinedList.first;
         }
+        if (winExpr.hasAggregateFilterExpr()) {
+            Pair<Boolean, Expression> inlinedExpr = inlineUdfsInExpr(winExpr.getAggregateFilterExpr(), funcs);
+            winExpr.setAggregateFilterExpr(inlinedExpr.second);
+            inlined |= inlinedExpr.first;
+        }
         Pair<Boolean, List<Expression>> inlinedList = inlineUdfsInExprList(winExpr.getExprList(), funcs);
         winExpr.setExprList(inlinedList.second);
         inlined |= inlinedList.first;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppListInputFunctionRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppListInputFunctionRewriteVisitor.java
index 2e65292..643bbb2 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppListInputFunctionRewriteVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppListInputFunctionRewriteVisitor.java
@@ -42,7 +42,7 @@
             newExprList.add(expr.accept(this, arg));
         }
         callExpr.setExprList(newExprList);
-        return FunctionMapUtil.normalizedListInputFunctions(callExpr);
+        FunctionMapUtil.normalizedListInputFunctions(callExpr);
+        return callExpr;
     }
-
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppWindowAggregationSugarVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppWindowAggregationSugarVisitor.java
index a5f43b6..6d8e52e 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppWindowAggregationSugarVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppWindowAggregationSugarVisitor.java
@@ -27,6 +27,7 @@
 import java.util.Set;
 
 import org.apache.asterix.common.exceptions.CompilationException;
+import org.apache.asterix.common.exceptions.ErrorCode;
 import org.apache.asterix.common.functions.FunctionSignature;
 import org.apache.asterix.lang.common.base.Expression;
 import org.apache.asterix.lang.common.base.ILangExpression;
@@ -89,17 +90,28 @@
             winExpr.setFunctionSignature(new FunctionSignature(winfi));
             if (BuiltinFunctions.builtinFunctionHasProperty(winfi,
                     BuiltinFunctions.WindowFunctionProperty.HAS_LIST_ARG)) {
-                wrapAggregationArguments(winExpr, 1);
+                if (winExpr.hasAggregateFilterExpr()) {
+                    throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_USE_OF_FILTER_CLAUSE,
+                            winExpr.getSourceLocation());
+                }
+                wrapAggregationArgument(winExpr, null);
             }
         } else if (FunctionMapUtil.isSql92AggregateFunction(signature)) {
+            if (winExpr.getExprList().size() != 1) {
+                // binary SQL-92 aggregate functions are not yet supported
+                throw new CompilationException(ErrorCode.COMPILATION_INVALID_PARAMETER_NUMBER,
+                        winExpr.getSourceLocation(), signature.getName(), winExpr.getExprList().size());
+            }
+            wrapAggregationArgument(winExpr, winExpr.getAggregateFilterExpr());
+            winExpr.setAggregateFilterExpr(null);
             winExpr.setFunctionSignature(FunctionMapUtil.sql92ToCoreAggregateFunction(signature));
-            wrapAggregationArguments(winExpr, winExpr.getExprList().size());
         }
 
         return super.visit(winExpr, arg);
     }
 
-    void wrapAggregationArguments(WindowExpression winExpr, int limit) throws CompilationException {
+    private void wrapAggregationArgument(WindowExpression winExpr, Expression aggFilterExpr)
+            throws CompilationException {
         VariableExpr winVar = winExpr.getWindowVar();
 
         Map<VariableExpr, Set<? extends Scope.SymbolAnnotation>> liveAnnotatedVars =
@@ -112,15 +124,15 @@
         Map<VariableExpr, Identifier> winVarFieldMap =
                 SqlppGroupByAggregationSugarVisitor.createGroupVarFieldMap(winFieldList);
 
+        //binary SQL-92 aggregates are not yet supported, so we just need to rewrite the first argument
         List<Expression> exprList = winExpr.getExprList();
-        int n = exprList.size();
-        List<Expression> newExprList = new ArrayList<>(n);
-        for (int i = 0; i < n; i++) {
-            Expression expr = exprList.get(i);
-            Expression newExpr = i < limit ? Sql92AggregateFunctionVisitor.wrapAggregationArgument(expr, winVar,
-                    winVarFieldMap, liveContextVars, null, liveVars, context) : expr;
-            newExprList.add(newExpr);
-        }
+        Expression aggArgExpr = exprList.get(0);
+        Expression newAggArgExpr = Sql92AggregateFunctionVisitor.wrapAggregationArgument(aggArgExpr, aggFilterExpr,
+                winVar, winVarFieldMap, liveContextVars, null, liveVars, context);
+
+        List<Expression> newExprList = new ArrayList<>(exprList);
+        newExprList.set(0, newAggArgExpr);
+
         winExpr.setExprList(newExprList);
     }
 
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppWindowRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppWindowRewriteVisitor.java
index cbc3ac9..6e5ebba 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppWindowRewriteVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlppWindowRewriteVisitor.java
@@ -75,24 +75,37 @@
         FunctionSignature signature = winExpr.getFunctionSignature();
         FunctionIdentifier winfi = FunctionMapUtil.getInternalWindowFunction(signature);
         if (winfi != null) {
+            if (winExpr.hasAggregateFilterExpr()) {
+                throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_USE_OF_FILTER_CLAUSE,
+                        winExpr.getSourceLocation());
+            }
             rewriteSpecificWindowFunctions(winfi, winExpr);
             if (BuiltinFunctions.builtinFunctionHasProperty(winfi,
                     BuiltinFunctions.WindowFunctionProperty.HAS_LIST_ARG)) {
-                List<Expression> newExprList = extractExpressionsFromList(winExpr.getExprList(), 1,
-                        SqlppWindowRewriteVisitor::isExtractableExpression);
-                if (newExprList == null) {
-                    throw new CompilationException(ErrorCode.COMPILATION_ERROR, winExpr.getSourceLocation(), "");
-                }
-                winExpr.setExprList(newExprList);
+                extractListArgument(winExpr);
             }
         } else if (FunctionMapUtil.isSql92AggregateFunction(signature)) {
-            List<Expression> newExprList = extractExpressionsFromList(winExpr.getExprList(),
-                    winExpr.getExprList().size(), SqlppWindowRewriteVisitor::isExtractableExpression);
-            if (newExprList == null) {
+            if (winExpr.hasAggregateFilterExpr()) {
+                Expression aggFilterExpr = winExpr.getAggregateFilterExpr();
+                if (isExtractableArgument(aggFilterExpr)) {
+                    VariableExpr newAggFilterExpr = extractExpression(aggFilterExpr);
+                    if (newAggFilterExpr == null) {
+                        throw new CompilationException(ErrorCode.COMPILATION_ERROR, winExpr.getSourceLocation(), "");
+                    }
+                    winExpr.setAggregateFilterExpr(newAggFilterExpr);
+                }
+            }
+            if (winExpr.getExprList().size() != 1) {
+                // binary SQL-92 aggregates are not yet supported
                 throw new CompilationException(ErrorCode.COMPILATION_ERROR, winExpr.getSourceLocation(), "");
             }
-            winExpr.setExprList(newExprList);
-        } else if (!FunctionMapUtil.isCoreAggregateFunction(signature)) {
+            extractListArgument(winExpr);
+        } else if (FunctionMapUtil.isCoreAggregateFunction(signature)) {
+            if (winExpr.hasAggregateFilterExpr()) {
+                throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_USE_OF_FILTER_CLAUSE,
+                        winExpr.getSourceLocation());
+            }
+        } else {
             throw new CompilationException(ErrorCode.COMPILATION_EXPECTED_WINDOW_FUNCTION, winExpr.getSourceLocation(),
                     signature.getName());
         }
@@ -100,30 +113,32 @@
         return winExpr;
     }
 
-    private List<Expression> extractExpressions(List<Expression> exprList, int limit) {
+    private void extractListArgument(WindowExpression winExpr) throws CompilationException {
+        List<Expression> argExprList = winExpr.getExprList();
+        Expression argExpr0 = argExprList.get(0);
+        if (isExtractableArgument(argExpr0)) {
+            VariableExpr newArgExpr0 = extractExpression(argExpr0);
+            if (newArgExpr0 == null) {
+                throw new CompilationException(ErrorCode.COMPILATION_ERROR, winExpr.getSourceLocation(), "");
+            }
+            List<Expression> newArgExprList = new ArrayList<>(argExprList);
+            newArgExprList.set(0, newArgExpr0);
+            winExpr.setExprList(newArgExprList);
+        }
+    }
+
+    private VariableExpr extractExpression(Expression expr) {
         StackElement stackElement = stack.peek();
         if (stackElement == null) {
             return null;
         }
-        int n = exprList.size();
-        List<Expression> newExprList = new ArrayList<>(n);
-        for (int i = 0; i < n; i++) {
-            Expression expr = exprList.get(i);
-            Expression newExpr;
-            if (i < limit && isExtractableExpression(expr)) {
-                VarIdentifier v = stackElement.addPendingLetClause(expr);
-                VariableExpr vExpr = new VariableExpr(v);
-                vExpr.setSourceLocation(expr.getSourceLocation());
-                newExpr = vExpr;
-            } else {
-                newExpr = expr;
-            }
-            newExprList.add(newExpr);
-        }
-        return newExprList;
+        VarIdentifier v = stackElement.addPendingLetClause(expr);
+        VariableExpr vExpr = new VariableExpr(v);
+        vExpr.setSourceLocation(expr.getSourceLocation());
+        return vExpr;
     }
 
-    protected static boolean isExtractableExpression(Expression expr) {
+    private boolean isExtractableArgument(Expression expr) {
         switch (expr.getKind()) {
             case LITERAL_EXPRESSION:
             case VARIABLE_EXPRESSION:
@@ -145,7 +160,7 @@
      * Add a copy of the first argument as the last argument for all functions
      * that have {@link BuiltinFunctions.WindowFunctionProperty#HAS_LIST_ARG} modifier.
      * The first argument will then be rewritten by
-     * {@link SqlppWindowAggregationSugarVisitor#wrapAggregationArguments(WindowExpression, int)}.
+     * {@link SqlppWindowAggregationSugarVisitor#wrapAggregationArgument(WindowExpression, int, Expression)}.
      * The new last argument will be handled by expression to plan translator
      * </li>
      * </ul>
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java
index 8190d3b..cf8469f 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java
@@ -248,13 +248,14 @@
 
     @Override
     public Expression visit(WindowExpression winExpr, ILangExpression arg) throws CompilationException {
-        // skip variables inside list arguments of window functions (will be resolved by SqlppWindowExpressionVisitor)
+        // skip variables inside list and agg-filter arguments of window functions
+        // (will be resolved by SqlppWindowExpressionVisitor)
         FunctionSignature fs = winExpr.getFunctionSignature();
         FunctionIdentifier winfi = FunctionMapUtil.getInternalWindowFunction(fs);
         if (winfi != null) {
             if (BuiltinFunctions.builtinFunctionHasProperty(winfi,
                     BuiltinFunctions.WindowFunctionProperty.HAS_LIST_ARG)) {
-                visitWindowExpressionExcludingExprList(winExpr, arg);
+                visitWindowExpressionExcludingExprListAndAggFilter(winExpr, arg);
                 List<Expression> exprList = winExpr.getExprList();
                 List<Expression> newExprList = new ArrayList<>(exprList.size());
                 Iterator<Expression> i = exprList.iterator();
@@ -264,12 +265,15 @@
                 }
                 winExpr.setExprList(newExprList);
                 return winExpr;
+            } else {
+                return super.visit(winExpr, arg);
             }
         } else if (FunctionMapUtil.isSql92AggregateFunction(fs)) {
-            visitWindowExpressionExcludingExprList(winExpr, arg);
+            visitWindowExpressionExcludingExprListAndAggFilter(winExpr, arg);
             return winExpr;
+        } else {
+            return super.visit(winExpr, arg);
         }
-        return super.visit(winExpr, arg);
     }
 
     static VariableExpr pickContextVar(Collection<VariableExpr> contextVars, VariableExpr usedVar)
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/FunctionMapUtil.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/FunctionMapUtil.java
index b092f4a..95ca9b1 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/FunctionMapUtil.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/FunctionMapUtil.java
@@ -162,20 +162,18 @@
      *
      * @param callExpr
      *            The input call expression.
-     * @return a new call expression that calls the corresponding AsterixDB internal function.
      */
-    public static CallExpr normalizedListInputFunctions(CallExpr callExpr) {
+    public static void normalizedListInputFunctions(CallExpr callExpr) {
         FunctionSignature fs = callExpr.getFunctionSignature();
         String internalFuncName = LIST_INPUT_FUNCTION_MAP.get(fs.getName().toLowerCase());
         if (internalFuncName == null) {
-            return callExpr;
+            return;
         }
         callExpr.setFunctionSignature(new FunctionSignature(FunctionConstants.ASTERIX_DV, internalFuncName, 1));
         ListConstructor listConstr =
                 new ListConstructor(ListConstructor.Type.ORDERED_LIST_CONSTRUCTOR, callExpr.getExprList());
         listConstr.setSourceLocation(callExpr.getSourceLocation());
         callExpr.setExprList(new ArrayList<>(Collections.singletonList(listConstr)));
-        return callExpr;
     }
 
     /**
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/CheckSql92AggregateVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/CheckSql92AggregateVisitor.java
index b5107bc..a2dafbd 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/CheckSql92AggregateVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/CheckSql92AggregateVisitor.java
@@ -187,6 +187,9 @@
                 return true;
             }
         }
+        if (pf.hasAggregateFilterExpr() && pf.getAggregateFilterExpr().accept(this, parentSelectBlock)) {
+            return true;
+        }
         return false;
     }
 
@@ -307,6 +310,7 @@
                 || (winExpr.hasFrameStartExpr() && winExpr.getFrameStartExpr().accept(this, arg))
                 || (winExpr.hasFrameEndExpr() && winExpr.getFrameEndExpr().accept(this, arg))
                 || (winExpr.hasWindowFieldList() && visitFieldList(winExpr.getWindowFieldList(), arg))
+                || (winExpr.hasAggregateFilterExpr() && winExpr.getAggregateFilterExpr().accept(this, arg))
                 || visitExprList(winExpr.getExprList(), arg);
     }
 
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/CheckSubqueryVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/CheckSubqueryVisitor.java
index 18c2b35..bc31e62 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/CheckSubqueryVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/CheckSubqueryVisitor.java
@@ -305,12 +305,14 @@
                 || (winExpr.hasFrameStartExpr() && visit(winExpr.getFrameStartExpr(), arg))
                 || (winExpr.hasFrameEndExpr() && visit(winExpr.getFrameEndExpr(), arg))
                 || (winExpr.hasWindowFieldList() && visitFieldList(winExpr.getWindowFieldList(), arg))
+                || (winExpr.hasAggregateFilterExpr() && winExpr.getAggregateFilterExpr().accept(this, arg))
                 || visitExprList(winExpr.getExprList(), arg);
     }
 
     @Override
     public Boolean visit(CallExpr callExpr, ILangExpression arg) throws CompilationException {
-        return visitExprList(callExpr.getExprList(), arg);
+        return visitExprList(callExpr.getExprList(), arg)
+                || (callExpr.hasAggregateFilterExpr() && visit(callExpr.getAggregateFilterExpr(), arg));
     }
 
     private boolean visit(ILangExpression expr, ILangExpression arg) throws CompilationException {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java
index e661ac1..70ebcfc 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/DeepCopyVisitor.java
@@ -441,7 +441,9 @@
         for (Expression expr : callExpr.getExprList()) {
             newExprList.add((Expression) expr.accept(this, arg));
         }
-        CallExpr copy = new CallExpr(callExpr.getFunctionSignature(), newExprList);
+        Expression newFilterExpr = callExpr.hasAggregateFilterExpr()
+                ? (Expression) callExpr.getAggregateFilterExpr().accept(this, arg) : null;
+        CallExpr copy = new CallExpr(callExpr.getFunctionSignature(), newExprList, newFilterExpr);
         copy.setSourceLocation(callExpr.getSourceLocation());
         copy.addHints(callExpr.getHints());
         return copy;
@@ -516,6 +518,8 @@
     @Override
     public ILangExpression visit(WindowExpression winExpr, Void arg) throws CompilationException {
         List<Expression> newExprList = copyExprList(winExpr.getExprList(), arg);
+        Expression newAggFilterExpr = winExpr.hasAggregateFilterExpr()
+                ? (Expression) winExpr.getAggregateFilterExpr().accept(this, arg) : null;
         List<Expression> newPartitionList =
                 winExpr.hasPartitionList() ? copyExprList(winExpr.getPartitionList(), arg) : null;
         List<Expression> newOrderbyList = winExpr.hasOrderByList() ? copyExprList(winExpr.getOrderbyList(), arg) : null;
@@ -529,10 +533,11 @@
                 winExpr.hasWindowVar() ? (VariableExpr) winExpr.getWindowVar().accept(this, arg) : null;
         List<Pair<Expression, Identifier>> newWindowFieldList =
                 winExpr.hasWindowFieldList() ? copyFieldList(winExpr.getWindowFieldList(), arg) : null;
-        WindowExpression copy = new WindowExpression(winExpr.getFunctionSignature(), newExprList, newPartitionList,
-                newOrderbyList, newOrderbyModifierList, winExpr.getFrameMode(), winExpr.getFrameStartKind(),
-                newFrameStartExpr, winExpr.getFrameEndKind(), newFrameEndExpr, winExpr.getFrameExclusionKind(),
-                newWindowVar, newWindowFieldList, winExpr.getIgnoreNulls(), winExpr.getFromLast());
+        WindowExpression copy =
+                new WindowExpression(winExpr.getFunctionSignature(), newExprList, newAggFilterExpr, newPartitionList,
+                        newOrderbyList, newOrderbyModifierList, winExpr.getFrameMode(), winExpr.getFrameStartKind(),
+                        newFrameStartExpr, winExpr.getFrameEndKind(), newFrameEndExpr, winExpr.getFrameExclusionKind(),
+                        newWindowVar, newWindowFieldList, winExpr.getIgnoreNulls(), winExpr.getFromLast());
         copy.setSourceLocation(winExpr.getSourceLocation());
         copy.addHints(winExpr.getHints());
         return copy;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/FreeVariableVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/FreeVariableVisitor.java
index e1ff4a7..0c14688 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/FreeVariableVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/FreeVariableVisitor.java
@@ -390,6 +390,9 @@
         for (Expression expr : callExpr.getExprList()) {
             expr.accept(this, freeVars);
         }
+        if (callExpr.hasAggregateFilterExpr()) {
+            callExpr.getAggregateFilterExpr().accept(this, freeVars);
+        }
         return null;
     }
 
@@ -461,6 +464,9 @@
             }
         }
         visit(winExpr.getExprList(), freeVars);
+        if (winExpr.hasAggregateFilterExpr()) {
+            winExpr.getAggregateFilterExpr().accept(this, freeVars);
+        }
         if (winExpr.hasWindowVar()) {
             freeVars.remove(winExpr.getWindowVar());
         }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
index d7ad1cb..7c9a4a7 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
@@ -250,21 +250,23 @@
     }
 
     @Override
-    public Void visit(CallExpr pf, Integer step) throws CompilationException {
-        FunctionSignature functionSignature = pf.getFunctionSignature();
-        FunctionSignature normalizedFunctionSignature =
-                FunctionMapUtil.normalizeBuiltinFunctionSignature(functionSignature, false, pf.getSourceLocation());
+    public Void visit(CallExpr callExpr, Integer step) throws CompilationException {
+        FunctionSignature functionSignature = callExpr.getFunctionSignature();
+        FunctionSignature normalizedFunctionSignature = FunctionMapUtil
+                .normalizeBuiltinFunctionSignature(functionSignature, false, callExpr.getSourceLocation());
         if (BuiltinFunctions.isBuiltinCompilerFunction(normalizedFunctionSignature, true)) {
             functionSignature = normalizedFunctionSignature;
         }
         //TODO(MULTI_PART_DATAVERSE_NAME):temporary workaround to preserve AST reference results
         if (FunctionUtil.isBuiltinDatasetFunction(functionSignature)) {
-            String singleArg = pf.getExprList().stream().map(LiteralExpr.class::cast).map(LiteralExpr::getValue)
+            String singleArg = callExpr.getExprList().stream().map(LiteralExpr.class::cast).map(LiteralExpr::getValue)
                     .map(StringLiteral.class::cast).map(StringLiteral::getValue).collect(Collectors.joining("."));
             printFunctionCall(functionSignature, 1,
-                    Collections.singletonList(new LiteralExpr(new StringLiteral(singleArg))), step);
+                    Collections.singletonList(new LiteralExpr(new StringLiteral(singleArg))),
+                    callExpr.getAggregateFilterExpr(), step);
         } else {
-            printFunctionCall(functionSignature, functionSignature.getArity(), pf.getExprList(), step);
+            printFunctionCall(functionSignature, functionSignature.getArity(), callExpr.getExprList(),
+                    callExpr.getAggregateFilterExpr(), step);
         }
         return null;
     }
@@ -376,6 +378,11 @@
                 printFieldList(step + 1, winExpr.getWindowFieldList());
             }
         }
+        if (winExpr.hasAggregateFilterExpr()) {
+            out.println(skip(step + 1) + "FILTER (WHERE");
+            winExpr.getAggregateFilterExpr().accept(this, step + 2);
+            out.println(skip(step + 1) + ')');
+        }
         out.println(skip(step) + "OVER (");
         if (winExpr.hasPartitionList()) {
             out.println(skip(step + 1) + "PARTITION BY");
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
index 20fd0f5..418a960 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
@@ -406,6 +406,8 @@
             VariableSubstitutionEnvironment env) throws CompilationException {
         List<Expression> newExprList =
                 VariableCloneAndSubstitutionUtil.visitAndCloneExprList(winExpr.getExprList(), env, this);
+        Expression newAggFilterExpr = winExpr.hasAggregateFilterExpr()
+                ? (Expression) winExpr.getAggregateFilterExpr().accept(this, env).first : null;
         List<Expression> newPartitionList = winExpr.hasPartitionList()
                 ? VariableCloneAndSubstitutionUtil.visitAndCloneExprList(winExpr.getPartitionList(), env, this) : null;
         List<Expression> newOrderbyList = winExpr.hasOrderByList()
@@ -421,10 +423,10 @@
         List<Pair<Expression, Identifier>> newWindowFieldList = winExpr.hasWindowFieldList()
                 ? VariableCloneAndSubstitutionUtil.substInFieldList(winExpr.getWindowFieldList(), env, this) : null;
         WindowExpression newWinExpr =
-                new WindowExpression(winExpr.getFunctionSignature(), newExprList, newPartitionList, newOrderbyList,
-                        newOrderbyModifierList, winExpr.getFrameMode(), winExpr.getFrameStartKind(), newFrameStartExpr,
-                        winExpr.getFrameEndKind(), newFrameEndExpr, winExpr.getFrameExclusionKind(), newWindowVar,
-                        newWindowFieldList, winExpr.getIgnoreNulls(), winExpr.getFromLast());
+                new WindowExpression(winExpr.getFunctionSignature(), newExprList, newAggFilterExpr, newPartitionList,
+                        newOrderbyList, newOrderbyModifierList, winExpr.getFrameMode(), winExpr.getFrameStartKind(),
+                        newFrameStartExpr, winExpr.getFrameEndKind(), newFrameEndExpr, winExpr.getFrameExclusionKind(),
+                        newWindowVar, newWindowFieldList, winExpr.getIgnoreNulls(), winExpr.getFromLast());
         newWinExpr.setSourceLocation(winExpr.getSourceLocation());
         newWinExpr.addHints(winExpr.getHints());
         return new Pair<>(newWinExpr, env);
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java
index bf71c05..8798dfa 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppFormatPrintVisitor.java
@@ -343,42 +343,43 @@
 
     @Override
     public Void visit(WindowExpression windowExpr, Integer step) throws CompilationException {
-        out.print(skip(step) + "window ");
-        out.print(generateFullName(windowExpr.getFunctionSignature().getDataverseName(),
+        out.println(skip(step) + "window " + generateFullName(windowExpr.getFunctionSignature().getDataverseName(),
                 windowExpr.getFunctionSignature().getName()) + "(");
-        printDelimitedExpressions(windowExpr.getExprList(), COMMA, step);
-        out.print(")");
-        out.print(skip(step) + " over ");
-        if (windowExpr.hasWindowVar()) {
-            windowExpr.getWindowVar().accept(this, step + 2);
-            out.print(skip(step) + "as ");
+        printDelimitedExpressions(windowExpr.getExprList(), COMMA, step + 2);
+        out.println(")");
+        if (windowExpr.hasAggregateFilterExpr()) {
+            out.println(skip(step + 1) + "filter where (");
+            windowExpr.getAggregateFilterExpr().accept(this, step + 2);
+            out.println();
         }
-        out.print("(");
+        out.print(skip(step + 1) + "over ");
+        if (windowExpr.hasWindowVar()) {
+            windowExpr.getWindowVar().accept(this, step + 1);
+            out.print(" as ");
+        }
+        out.println("(");
         if (windowExpr.hasPartitionList()) {
-            List<Expression> partitionList = windowExpr.getPartitionList();
-            for (int i = 0, ln = partitionList.size(); i < ln; i++) {
-                if (i > 0) {
-                    out.print(COMMA);
-                }
-                Expression partExpr = partitionList.get(i);
-                partExpr.accept(this, step + 2);
-            }
+            printDelimitedExpressions(windowExpr.getPartitionList(), COMMA, step + 2);
+            out.println();
         }
         if (windowExpr.hasOrderByList()) {
-            out.print(skip(step) + " order by ");
+            out.print(skip(step + 1) + "order by ");
             printDelimitedObyExpressions(windowExpr.getOrderbyList(), windowExpr.getOrderbyModifierList(), step + 2);
+            out.println();
         }
         if (windowExpr.hasFrameDefinition()) {
-            out.println(skip(step) + windowExpr.getFrameMode());
+            out.println(skip(step + 1) + windowExpr.getFrameMode());
             if (windowExpr.hasFrameStartExpr()) {
                 windowExpr.getFrameStartExpr().accept(this, step + 2);
+                out.println();
             }
-            out.println(skip(step) + windowExpr.getFrameStartKind());
+            out.println(skip(step + 1) + windowExpr.getFrameStartKind());
             if (windowExpr.hasFrameEndExpr()) {
                 windowExpr.getFrameEndExpr().accept(this, step + 2);
+                out.println();
             }
-            out.println(skip(step) + windowExpr.getFrameEndKind());
-            out.println(skip(step) + "exclude " + windowExpr.getFrameExclusionKind());
+            out.println(skip(step + 1) + windowExpr.getFrameEndKind());
+            out.println(skip(step + 1) + "exclude " + windowExpr.getFrameExclusionKind());
         }
         out.println(skip(step) + ")");
         return null;
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppContainsExpressionVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppContainsExpressionVisitor.java
index 6495742..8e8e959 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppContainsExpressionVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppContainsExpressionVisitor.java
@@ -297,12 +297,14 @@
                 || (winExpr.hasFrameStartExpr() && visit(winExpr.getFrameStartExpr(), arg))
                 || (winExpr.hasFrameEndExpr() && visit(winExpr.getFrameEndExpr(), arg))
                 || (winExpr.hasWindowFieldList() && visitFieldList(winExpr.getWindowFieldList(), arg))
+                || (winExpr.hasAggregateFilterExpr() && visit(winExpr.getAggregateFilterExpr(), arg))
                 || visitExprList(winExpr.getExprList(), arg);
     }
 
     @Override
     public Boolean visit(CallExpr callExpr, T arg) throws CompilationException {
-        return visitExprList(callExpr.getExprList(), arg);
+        return visitExprList(callExpr.getExprList(), arg)
+                || (callExpr.hasAggregateFilterExpr() && visit(callExpr.getAggregateFilterExpr(), arg));
     }
 
     private boolean visit(ILangExpression expr, T arg) throws CompilationException {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppExpressionScopingVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppExpressionScopingVisitor.java
index db85d73..f0a9d9a 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppExpressionScopingVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppExpressionScopingVisitor.java
@@ -239,7 +239,7 @@
         // or an outer scope query) should still be visible.
         Scope newScope = new Scope(scopeChecker, scopeChecker.getPrecedingScope());
         // Puts all group-by variables into the symbol set of the new scope.
-        Set<VariableExpr> gbyKeyVars = new HashSet<>(); // bindings from prior grouping sets //TODO:FIXME:GBY:REVISIT
+        Set<VariableExpr> gbyKeyVars = new HashSet<>(); // bindings from prior grouping sets
         for (List<GbyVariableExpressionPair> gbyPairList : gc.getGbyPairList()) {
             for (GbyVariableExpressionPair gbyKeyVarExpr : gbyPairList) {
                 gbyKeyVarExpr.setExpr(visit(gbyKeyVarExpr.getExpr(), gc));
@@ -391,16 +391,22 @@
 
     @Override
     public Expression visit(WindowExpression winExpr, ILangExpression arg) throws CompilationException {
-        visitWindowExpressionExcludingExprList(winExpr, arg);
+        visitWindowExpressionExcludingExprListAndAggFilter(winExpr, arg);
         if (winExpr.hasWindowVar()) {
             Scope preScope = scopeChecker.getCurrentScope();
             Scope newScope = scopeChecker.extendCurrentScope();
             VariableExpr windowVar = winExpr.getWindowVar();
             addNewVarSymbolToScope(newScope, windowVar.getVar(), windowVar.getSourceLocation());
             winExpr.setExprList(visit(winExpr.getExprList(), arg));
+            if (winExpr.hasAggregateFilterExpr()) {
+                winExpr.setAggregateFilterExpr(visit(winExpr.getAggregateFilterExpr(), arg));
+            }
             scopeChecker.replaceCurrentScope(preScope);
         } else {
             winExpr.setExprList(visit(winExpr.getExprList(), arg));
+            if (winExpr.hasAggregateFilterExpr()) {
+                winExpr.setAggregateFilterExpr(visit(winExpr.getAggregateFilterExpr(), arg));
+            }
         }
         return winExpr;
     }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppSimpleExpressionVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppSimpleExpressionVisitor.java
index 3d39dc0..6dacea6 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppSimpleExpressionVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/base/AbstractSqlppSimpleExpressionVisitor.java
@@ -306,6 +306,9 @@
     @Override
     public Expression visit(CallExpr callExpr, ILangExpression arg) throws CompilationException {
         callExpr.setExprList(visit(callExpr.getExprList(), arg));
+        if (callExpr.hasAggregateFilterExpr()) {
+            callExpr.setAggregateFilterExpr(visit(callExpr.getAggregateFilterExpr(), arg));
+        }
         return callExpr;
     }
 
@@ -322,12 +325,15 @@
 
     @Override
     public Expression visit(WindowExpression winExpr, ILangExpression arg) throws CompilationException {
-        visitWindowExpressionExcludingExprList(winExpr, arg);
+        visitWindowExpressionExcludingExprListAndAggFilter(winExpr, arg);
         winExpr.setExprList(visit(winExpr.getExprList(), arg));
+        if (winExpr.hasAggregateFilterExpr()) {
+            winExpr.setAggregateFilterExpr(visit(winExpr.getAggregateFilterExpr(), arg));
+        }
         return winExpr;
     }
 
-    protected void visitWindowExpressionExcludingExprList(WindowExpression winExpr, ILangExpression arg)
+    protected void visitWindowExpressionExcludingExprListAndAggFilter(WindowExpression winExpr, ILangExpression arg)
             throws CompilationException {
         if (winExpr.hasPartitionList()) {
             winExpr.setPartitionList(visit(winExpr.getPartitionList(), arg));
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index ca5b1d8..7105a42 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -3375,50 +3375,58 @@
 
 Expression FunctionCallExpr() throws ParseException:
 {
-  Expression resultExpr;
-  CallExpr callExpr;
   List<Expression> argList = new ArrayList<Expression>();
-  Expression tmp = null;
-  int arity = 0;
+  Expression argExpr = null;
   FunctionName funcName = null;
   boolean star = false;
   boolean distinct = false;
+  Expression filterExpr = null;
+  WindowExpression windowExpr = null;
 }
 {
   funcName = FunctionName()
   <LEFTPAREN> (
     ( <DISTINCT> { distinct = true; } )?
-    ( tmp = Expression() | <MUL> { star = true; } )
+    ( argExpr = Expression() | <MUL> { star = true; } )
     {
-      if(star){
-        if(!funcName.function.equalsIgnoreCase("count")){
-           throw new SqlppParseException(getSourceLocation(token), "The parameter * can only be used in COUNT().");
+      if (star) {
+        if (funcName.function.equalsIgnoreCase(BuiltinFunctions.SCALAR_COUNT.getName())) {
+          argExpr = new LiteralExpr(new LongIntegerLiteral(1L));
+        } else {
+          throw new SqlppParseException(getSourceLocation(token),
+            "The parameter * can only be used in " + BuiltinFunctions.SCALAR_COUNT.getName() + "().");
         }
-        argList.add(new LiteralExpr(new LongIntegerLiteral(1L)));
-      } else {
-         argList.add(tmp);
       }
-      arity ++;
+      argList.add(argExpr);
     }
-  (<COMMA> tmp = Expression()
+  (<COMMA> argExpr = Expression()
     {
-      argList.add(tmp);
-      arity++;
+      argList.add(argExpr);
     }
   )*)? <RIGHTPAREN>
-    {
-      String name = funcName.function;
-      if (distinct) {
-        name += FunctionMapUtil.DISTINCT_AGGREGATE_SUFFIX;
-      }
-      // TODO use funcName.library
-      String fqFunctionName = funcName.library == null ? name : funcName.library + "#" + name;
-      FunctionSignature signature
-        = lookupFunctionSignature(funcName.dataverse, fqFunctionName, arity);
-      if (signature == null) {
-        signature = new FunctionSignature(funcName.dataverse, fqFunctionName, arity);
-      }
-      callExpr = FunctionMapUtil.normalizedListInputFunctions(new CallExpr(signature,argList));
+
+  {
+    String name = funcName.function;
+    if (distinct) {
+      name += FunctionMapUtil.DISTINCT_AGGREGATE_SUFFIX;
+    }
+    String fqFunctionName = funcName.library == null ? name : funcName.library + "#" + name;
+    int arity = argList.size();
+    FunctionSignature signature = lookupFunctionSignature(funcName.dataverse, fqFunctionName, arity);
+    if (signature == null) {
+      signature = new FunctionSignature(funcName.dataverse, fqFunctionName, arity);
+    }
+  }
+
+  ( <FILTER> <LEFTPAREN> <WHERE> filterExpr = Expression() <RIGHTPAREN> )?
+
+  ( LOOKAHEAD(5) windowExpr = WindowExpr(signature, argList, filterExpr) )?
+
+  {
+    if (windowExpr != null) {
+      return windowExpr;
+    } else {
+      CallExpr callExpr = new CallExpr(signature, argList, filterExpr);
       if (funcName.hint != null) {
         switch (funcName.hint) {
           case INDEXED_NESTED_LOOP_JOIN_HINT:
@@ -3429,18 +3437,15 @@
             break;
         }
       }
+      FunctionMapUtil.normalizedListInputFunctions(callExpr);
       callExpr.setSourceLocation(funcName.sourceLoc);
-      resultExpr = callExpr;
+      return callExpr;
     }
-
-  ( LOOKAHEAD(5) resultExpr = WindowExpr(callExpr.getFunctionSignature(), callExpr.getExprList()) )?
-
-  {
-     return resultExpr;
   }
 }
 
-WindowExpression WindowExpr(FunctionSignature signature, List<Expression> argList) throws ParseException:
+WindowExpression WindowExpr(FunctionSignature signature, List<Expression> argList, Expression aggFilterExpr)
+  throws ParseException:
 {
   Boolean fromLast = null, ignoreNulls = null;
 }
@@ -3481,12 +3486,12 @@
   )?
   <OVER>
   {
-    return OverClause(signature, argList, token, fromLast, ignoreNulls);
+    return OverClause(signature, argList, aggFilterExpr, token, fromLast, ignoreNulls);
   }
 }
 
-WindowExpression OverClause(FunctionSignature signature, List<Expression> argList, Token startToken, Boolean fromLast,
-    Boolean ignoreNulls) throws ParseException:
+WindowExpression OverClause(FunctionSignature signature, List<Expression> argList, Expression aggFilterExpr,
+    Token startToken, Boolean fromLast, Boolean ignoreNulls) throws ParseException:
 {
   Expression partitionExpr = null;
   List<Expression> partitionExprs = new ArrayList<Expression>();
@@ -3546,10 +3551,10 @@
   )?
   <RIGHTPAREN>
   {
-    WindowExpression winExp = new WindowExpression(signature, argList, partitionExprs, orderbyList, orderbyModifierList,
-      frameMode, frameStartKind, frameStartExpr, frameEndKind, frameEndExpr, frameExclusionKind, windowVar,
-      windowFieldList, ignoreNulls, fromLast);
-    return addSourceLocation(winExp, startToken);
+    WindowExpression winExpr = new WindowExpression(signature, argList, aggFilterExpr, partitionExprs, orderbyList,
+      orderbyModifierList, frameMode, frameStartKind, frameStartExpr, frameEndKind, frameEndExpr, frameExclusionKind,
+      windowVar, windowFieldList, ignoreNulls, fromLast);
+    return addSourceLocation(winExpr, startToken);
   }
 }