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

5 files changed, 43 insertions, 0 deletions

diff --git a/doc/build/changelog/unreleased_13/4760.rst b/doc/build/changelog/unreleased_13/4760.rst
new file mode 100644
index 000000000..762cdc93a
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/4760.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: bug, sql, postgresql
+    :tickets: 4760
+
+    Fixed issue where the :class:`.array_agg` construct in combination with
+    :meth:`.FunctionElement.filter` would not produce the correct operator
+    precedence in combination with the array index operator.
+
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 735a125d7..6d1174d20 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -3808,6 +3808,12 @@ class FunctionFilter(ColumnElement):
             rows=rows,
         )
 
+    def self_group(self, against=None):
+        if operators.is_precedent(operators.filter_op, against):
+            return Grouping(self)
+        else:
+            return self
+
     @util.memoized_property
     def type(self):
         return self.func.type
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index b8bbb4525..4a1a0dcd4 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -1345,6 +1345,10 @@ def empty_notin_op(a, b):
     raise NotImplementedError()
 
 
+def filter_op(a, b):
+    raise NotImplementedError()
+
+
 def concat_op(a, b):
     return a.concat(b)
 
@@ -1448,6 +1452,7 @@ _PRECEDENCE = {
     add: 7,
     sub: 7,
     concat_op: 6,
+    filter_op: 6,
     match_op: 5,
     notmatch_op: 5,
     ilike_op: 5,
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(