Merge "Add option to disable from linting for table valued function" into main

5 files changed, 76 insertions, 4 deletions

diff --git a/doc/build/changelog/unreleased_14/7845.rst b/doc/build/changelog/unreleased_14/7845.rst
new file mode 100644
index 000000000..1cfa9cdf6
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/7845.rst
@@ -0,0 +1,11 @@
+.. change::
+    :tags: usecase, sql
+    :tickets: 7845
+
+    Added new parameter
+    :paramref:`.FunctionElement.table_valued.joins_implicitly`, for the
+    :meth:`.FunctionElement.table_valued` construct. This parameter indicates
+    that the given table-valued function implicitly joins to the table it
+    refers towards, essentially disabling the "from linting" feature, i.e. the
+    "cartesian product" warning, from taking effect due to the presence of this
+    parameter. May be used for functions such as ``func.json_each()``.
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index f8019b9c6..d3e91a8d5 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -3173,6 +3173,8 @@ class SQLCompiler(Compiled):
         return None
 
     def visit_table_valued_alias(self, element, **kw):
+        if element.joins_implicitly:
+            kw["from_linter"] = None
         if element._is_lateral:
             return self.visit_lateral(element, **kw)
         else:
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index 563b58418..9e801a99f 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -226,8 +226,16 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
          string name will be added as a column to the .c collection
          of the resulting :class:`_sql.TableValuedAlias`.
 
+        :param joins_implicitly: when True, the table valued function may be
+         used in the FROM clause without any explicit JOIN to other tables
+         in the SQL query, and no "cartesian product" warning will be generated.
+         May be useful for SQL functions such as ``func.json_each()``.
+
+         .. versionadded:: 1.4.33
+
         .. versionadded:: 1.4.0b2
 
+
         .. seealso::
 
             :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
@@ -248,6 +256,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
         new_func = self._generate()
 
         with_ordinality = kw.pop("with_ordinality", None)
+        joins_implicitly = kw.pop("joins_implicitly", None)
         name = kw.pop("name", None)
 
         if with_ordinality:
@@ -258,7 +267,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
             *expr
         )
 
-        return new_func.alias(name=name)
+        return new_func.alias(name=name, joins_implicitly=joins_implicitly)
 
     def column_valued(self, name=None):
         """Return this :class:`_functions.FunctionElement` as a column expression that
@@ -511,7 +520,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
 
         return None
 
-    def alias(self, name=None):
+    def alias(self, name=None, joins_implicitly=False):
         r"""Produce a :class:`_expression.Alias` construct against this
         :class:`.FunctionElement`.
 
@@ -553,6 +562,17 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
 
         .. versionadded:: 1.4.0b2  Added the ``.column`` accessor
 
+        :param name: alias name, will be rendered as ``AS <name>`` in the
+         FROM clause
+
+        :param joins_implicitly: when True, the table valued function may be
+         used in the FROM clause without any explicit JOIN to other tables
+         in the SQL query, and no "cartesian product" warning will be
+         generated.  May be useful for SQL functions such as
+         ``func.json_each()``.
+
+         .. versionadded:: 1.4.33
+
         .. seealso::
 
             :ref:`tutorial_functions_table_valued` -
@@ -568,7 +588,10 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative):
         """
 
         return TableValuedAlias._construct(
-            self, name, table_value_type=self.type
+            self,
+            name,
+            table_value_type=self.type,
+            joins_implicitly=joins_implicitly,
         )
 
     def select(self) -> "Select":
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index e143d1476..80433a283 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -1477,6 +1477,7 @@ class TableValuedAlias(Alias):
     _supports_derived_columns = True
     _render_derived = False
     _render_derived_w_types = False
+    joins_implicitly = False
 
     _traverse_internals = [
         ("element", InternalTraversal.dp_clauseelement),
@@ -1486,9 +1487,16 @@ class TableValuedAlias(Alias):
         ("_render_derived_w_types", InternalTraversal.dp_boolean),
     ]
 
-    def _init(self, selectable, name=None, table_value_type=None):
+    def _init(
+        self,
+        selectable,
+        name=None,
+        table_value_type=None,
+        joins_implicitly=False,
+    ):
         super(TableValuedAlias, self)._init(selectable, name=name)
 
+        self.joins_implicitly = joins_implicitly
         self._tableval_type = (
             type_api.TABLEVALUE
             if table_value_type is None
diff --git a/test/sql/test_from_linter.py b/test/sql/test_from_linter.py
index a22913868..4a4d907f9 100644
--- a/test/sql/test_from_linter.py
+++ b/test/sql/test_from_linter.py
@@ -1,6 +1,10 @@
+from sqlalchemy import column
+from sqlalchemy import func
 from sqlalchemy import Integer
+from sqlalchemy import JSON
 from sqlalchemy import select
 from sqlalchemy import sql
+from sqlalchemy import table
 from sqlalchemy import testing
 from sqlalchemy import true
 from sqlalchemy.testing import config
@@ -161,6 +165,30 @@ class TestFindUnmatchingFroms(fixtures.TablesTest):
         assert start is p3
         assert froms == {p1}
 
+    @testing.combinations(True, False, argnames="joins_implicitly")
+    def test_table_valued(self, joins_implicitly):
+        """test #7845"""
+        my_table = table(
+            "tbl",
+            column("id", Integer),
+            column("data", JSON()),
+        )
+
+        sub_dict = my_table.c.data["d"]
+        tv = func.json_each(sub_dict).table_valued(
+            "key", joins_implicitly=joins_implicitly
+        )
+        has_key = tv.c.key == "f"
+        stmt = select(my_table.c.id).where(has_key)
+        froms, start = find_unmatching_froms(stmt, my_table)
+
+        if joins_implicitly:
+            is_(start, None)
+            is_(froms, None)
+        else:
+            assert start == my_table
+            assert froms == {tv}
+
     def test_count_non_eq_comparison_operators(self):
         query = select(self.a).where(self.a.c.col_a > self.b.c.col_b)
         froms, start = find_unmatching_froms(query, self.a)