self_group() for FunctionFilter

Fixed issue where the :class:`.array_agg` construct in combination with
:meth:`.FunctionElement.filter` would not produce the correct operator
precedence between the FILTER keyword and the array index operator.

Fixes: #4760
Change-Id: Ic662cd3da3330554ec673bafd80495b3f1506098

2 files changed, 24 insertions, 0 deletions

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 13e4aaad5..b65361bda 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1435,6 +1435,21 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "FROM table1 AS foo",
         )
 
+    def test_array_agg_w_filter_subscript(self):
+        series = func.generate_series(1, 100).alias("series")
+        series_col = column("series")
+        query = select(
+            [func.array_agg(series_col).filter(series_col % 2 == 0)[3]]
+        ).select_from(series)
+        self.assert_compile(
+            query,
+            "SELECT (array_agg(series) FILTER "
+            "(WHERE series %% %(series_1)s = %(param_1)s))[%(param_2)s] "
+            "AS anon_1 FROM "
+            "generate_series(%(generate_series_1)s, %(generate_series_2)s) "
+            "AS series",
+        )
+
     def test_delete_extra_froms(self):
         t1 = table("t1", column("c1"))
         t2 = table("t2", column("c1"))
diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py
index e0efb1008..d0e68f1e3 100644
--- a/test/sql/test_functions.py
+++ b/test/sql/test_functions.py
@@ -557,6 +557,15 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "mytable.myid > :myid_1)",
         )
 
+    def test_funcfilter_arrayagg_subscript(self):
+        num = column("q")
+        self.assert_compile(
+            func.array_agg(num).filter(num % 2 == 0)[1],
+            "(array_agg(q) FILTER (WHERE q %% %(q_1)s = "
+            "%(param_1)s))[%(param_2)s]",
+            dialect="postgresql",
+        )
+
     def test_funcfilter_label(self):
         self.assert_compile(
             select(