From 209d1913cc702665252f3d2bc1b38ce3a0b9e3eb Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 14 Jul 2025 16:13:03 -0400 Subject: [PATCH] extend Values from HasCTE The :func:`_sql.values` construct gains a new method :meth:`_sql.Values.cte`, which allows creation of a named, explicit-columns :class:`.CTE` against an unnamed ``VALUES`` expression, producing a syntax that allows column-oriented selection from a ``VALUES`` construct on modern versions of PostgreSQL, SQLite, and MariaDB. Fixes: #12734 Change-Id: I4a0146418420cce3cbbda4e50f5eb32206dc696b --- doc/build/changelog/unreleased_20/12734.rst | 9 + doc/build/core/selectable.rst | 1 + .../sql/_selectable_constructors.py | 74 ++++++-- lib/sqlalchemy/sql/compiler.py | 17 +- lib/sqlalchemy/sql/elements.py | 5 +- lib/sqlalchemy/sql/selectable.py | 22 ++- lib/sqlalchemy/testing/requirements.py | 7 + lib/sqlalchemy/testing/suite/test_cte.py | 26 +++ test/requirements.py | 18 ++ test/sql/test_compare.py | 9 +- test/sql/test_values.py | 177 ++++++++++++++---- 11 files changed, 306 insertions(+), 59 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/12734.rst diff --git a/doc/build/changelog/unreleased_20/12734.rst b/doc/build/changelog/unreleased_20/12734.rst new file mode 100644 index 0000000000..ab6a52bff4 --- /dev/null +++ b/doc/build/changelog/unreleased_20/12734.rst @@ -0,0 +1,9 @@ +.. change:: + :tags: usecase, sql + :tickets: 12734 + + The :func:`_sql.values` construct gains a new method :meth:`_sql.Values.cte`, + which allows creation of a named, explicit-columns :class:`.CTE` against an + unnamed ``VALUES`` expression, producing a syntax that allows column-oriented + selection from a ``VALUES`` construct on modern versions of PostgreSQL, SQLite, + and MariaDB. diff --git a/doc/build/core/selectable.rst b/doc/build/core/selectable.rst index e81c88cc49..886bb1dfda 100644 --- a/doc/build/core/selectable.rst +++ b/doc/build/core/selectable.rst @@ -154,6 +154,7 @@ The classes here are generated using the constructors listed at .. autoclass:: Values :members: + :inherited-members: ClauseElement, FromClause, HasTraverseInternals, Selectable .. autoclass:: ScalarValues :members: diff --git a/lib/sqlalchemy/sql/_selectable_constructors.py b/lib/sqlalchemy/sql/_selectable_constructors.py index b97b7b3b19..129806204b 100644 --- a/lib/sqlalchemy/sql/_selectable_constructors.py +++ b/lib/sqlalchemy/sql/_selectable_constructors.py @@ -688,28 +688,75 @@ def values( name: Optional[str] = None, literal_binds: bool = False, ) -> Values: - r"""Construct a :class:`_expression.Values` construct. + r"""Construct a :class:`_expression.Values` construct representing the + SQL ``VALUES`` clause. - The column expressions and the actual data for - :class:`_expression.Values` are given in two separate steps. The - constructor receives the column expressions typically as - :func:`_expression.column` constructs, - and the data is then passed via the - :meth:`_expression.Values.data` method as a list, - which can be called multiple - times to add more data, e.g.:: + + The column expressions and the actual data for :class:`_expression.Values` + are given in two separate steps. The constructor receives the column + expressions typically as :func:`_expression.column` constructs, and the + data is then passed via the :meth:`_expression.Values.data` method as a + list, which can be called multiple times to add more data, e.g.:: from sqlalchemy import column from sqlalchemy import values from sqlalchemy import Integer from sqlalchemy import String + value_expr = ( + values( + column("id", Integer), + column("name", String), + ) + .data([(1, "name1"), (2, "name2")]) + .data([(3, "name3")]) + ) + + Would represent a SQL fragment like:: + + VALUES(1, "name1"), (2, "name2"), (3, "name3") + + The :class:`_sql.values` construct has an optional + :paramref:`_sql.values.name` field; when using this field, the + PostgreSQL-specific "named VALUES" clause may be generated:: + value_expr = values( - column("id", Integer), - column("name", String), - name="my_values", + column("id", Integer), column("name", String), name="somename" ).data([(1, "name1"), (2, "name2"), (3, "name3")]) + When selecting from the above construct, the name and column names will + be listed out using a PostgreSQL-specific syntax:: + + >>> print(value_expr.select()) + SELECT somename.id, somename.name + FROM (VALUES (:param_1, :param_2), (:param_3, :param_4), + (:param_5, :param_6)) AS somename (id, name) + + For a more database-agnostic means of SELECTing named columns from a + VALUES expression, the :meth:`.Values.cte` method may be used, which + produces a named CTE with explicit column names against the VALUES + construct within; this syntax works on PostgreSQL, SQLite, and MariaDB:: + + value_expr = ( + values( + column("id", Integer), + column("name", String), + ) + .data([(1, "name1"), (2, "name2"), (3, "name3")]) + .cte() + ) + + Rendering as:: + + >>> print(value_expr.select()) + WITH anon_1(id, name) AS + (VALUES (:param_1, :param_2), (:param_3, :param_4), (:param_5, :param_6)) + SELECT anon_1.id, anon_1.name + FROM anon_1 + + .. versionadded:: 2.0.42 Added the :meth:`.Values.cte` method to + :class:`.Values` + :param \*columns: column expressions, typically composed using :func:`_expression.column` objects. @@ -721,5 +768,6 @@ def values( the data values inline in the SQL output, rather than using bound parameters. - """ + """ # noqa: E501 + return Values(*columns, literal_binds=literal_binds, name=name) diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index a46fcca2d9..dccdfe7426 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -4133,7 +4133,7 @@ class SQLCompiler(Compiled): if cte.recursive: self.ctes_recursive = True text = self.preparer.format_alias(cte, cte_name) - if cte.recursive: + if cte.recursive or cte.element.name_cte_columns: col_source = cte.element # TODO: can we get at the .columns_plus_names collection @@ -4354,7 +4354,13 @@ class SQLCompiler(Compiled): ) return f"VALUES {tuples}" - def visit_values(self, element, asfrom=False, from_linter=None, **kw): + def visit_values( + self, element, asfrom=False, from_linter=None, visiting_cte=None, **kw + ): + + if element._independent_ctes: + self._dispatch_independent_ctes(element, kw) + v = self._render_values(element, **kw) if element._unnamed: @@ -4375,7 +4381,12 @@ class SQLCompiler(Compiled): name if name is not None else "(unnamed VALUES element)" ) - if name: + if visiting_cte is not None and visiting_cte.element is element: + if element._is_lateral: + raise exc.CompileError( + "Can't use a LATERAL VALUES expression inside of a CTE" + ) + elif name: kw["include_table"] = False v = "%s(%s)%s (%s)" % ( lateral, diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 84f813be5f..6dc1aae9d0 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -91,6 +91,7 @@ if typing.TYPE_CHECKING: from ._typing import _InfoType from ._typing import _PropagateAttrsType from ._typing import _TypeEngineArgument + from .base import _EntityNamespace from .base import ColumnSet from .cache_key import _CacheKeyTraversalType from .cache_key import CacheKey @@ -502,8 +503,8 @@ class ClauseElement( self = self._is_clone_of return self - @property - def entity_namespace(self): + @util.ro_non_memoized_property + def entity_namespace(self) -> _EntityNamespace: raise AttributeError( "This SQL expression has no entity namespace " "with which to filter from." diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 349f189302..d50053695b 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -2173,7 +2173,7 @@ class CTE( def _init( self, - selectable: Select[Unpack[TupleAny]], + selectable: HasCTE, *, name: Optional[str] = None, recursive: bool = False, @@ -2531,6 +2531,14 @@ class HasCTE(roles.HasCTERole, SelectsRows): _independent_ctes: Tuple[CTE, ...] = () _independent_ctes_opts: Tuple[_CTEOpts, ...] = () + name_cte_columns: bool = False + """indicates if this HasCTE as contained within a CTE should compel the CTE + to render the column names of this object in the WITH clause. + + .. versionadded:: 2.0.42 + + """ + @_generative def add_cte(self, *ctes: CTE, nest_here: bool = False) -> Self: r"""Add one or more :class:`_sql.CTE` constructs to this statement. @@ -3320,7 +3328,7 @@ class ForUpdateArg(ClauseElement): self.of = None -class Values(roles.InElementRole, Generative, LateralFromClause): +class Values(roles.InElementRole, HasCTE, Generative, LateralFromClause): """Represent a ``VALUES`` construct that can be used as a FROM element in a statement. @@ -3341,7 +3349,9 @@ class Values(roles.InElementRole, Generative, LateralFromClause): ("_data", InternalTraversal.dp_dml_multi_values), ("name", InternalTraversal.dp_string), ("literal_binds", InternalTraversal.dp_boolean), - ] + ] + HasCTE._has_ctes_traverse_internals + + name_cte_columns = True def __init__( self, @@ -3365,6 +3375,10 @@ class Values(roles.InElementRole, Generative, LateralFromClause): def _column_types(self) -> List[TypeEngine[Any]]: return [col.type for col in self._column_args] + @util.ro_non_memoized_property + def _all_selected_columns(self) -> _SelectIterable: + return self._column_args + @_generative def alias(self, name: Optional[str] = None, flat: bool = False) -> Self: """Return a new :class:`_expression.Values` @@ -3394,7 +3408,7 @@ class Values(roles.InElementRole, Generative, LateralFromClause): return self @_generative - def lateral(self, name: Optional[str] = None) -> LateralFromClause: + def lateral(self, name: Optional[str] = None) -> Self: """Return a new :class:`_expression.Values` with the lateral flag set, so that it renders as LATERAL. diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index 2f208ec008..1991208883 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -315,6 +315,13 @@ class SuiteRequirements(Requirements): return exclusions.closed() + @property + def ctes_with_values(self): + """target database supports CTES that ride on top of a VALUES + clause.""" + + return exclusions.closed() + @property def ctes_on_dml(self): """target database supports CTES which consist of INSERT, UPDATE diff --git a/lib/sqlalchemy/testing/suite/test_cte.py b/lib/sqlalchemy/testing/suite/test_cte.py index 4e4d420faa..e6e852bee5 100644 --- a/lib/sqlalchemy/testing/suite/test_cte.py +++ b/lib/sqlalchemy/testing/suite/test_cte.py @@ -10,11 +10,13 @@ from .. import fixtures from ..assertions import eq_ from ..schema import Column from ..schema import Table +from ... import column from ... import ForeignKey from ... import Integer from ... import select from ... import String from ... import testing +from ... import values class CTETest(fixtures.TablesTest): @@ -209,3 +211,27 @@ class CTETest(fixtures.TablesTest): ).fetchall(), [(1, "d1", None), (5, "d5", 3)], ) + + @testing.variation("values_named", [True, False]) + @testing.variation("cte_named", [True, False]) + @testing.variation("literal_binds", [True, False]) + @testing.requires.ctes_with_values + def test_values_named_via_cte( + self, connection, values_named, cte_named, literal_binds + ): + + cte1 = ( + values( + column("col1", String), + column("col2", Integer), + literal_binds=bool(literal_binds), + name="some name" if values_named else None, + ) + .data([("a", 2), ("b", 3)]) + .cte("cte1" if cte_named else None) + ) + + stmt = select(cte1) + + rows = connection.execute(stmt).all() + eq_(rows, [("a", 2), ("b", 3)]) diff --git a/test/requirements.py b/test/requirements.py index 72b609f21f..02c95a352e 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -740,6 +740,24 @@ class DefaultRequirements(SuiteRequirements): ] ) + @property + def ctes_with_values(self): + return only_on( + [ + lambda config: against(config, "mysql") + and ( + ( + config.db.dialect._is_mariadb + and config.db.dialect._mariadb_normalized_version_info + >= (10, 2) + ) + ), + "mariadb>10.2", + "postgresql", + "sqlite>=3.8.3", + ] + ) + @property def ctes_with_update_delete(self): """target database supports CTES that ride on top of a normal UPDATE diff --git a/test/sql/test_compare.py b/test/sql/test_compare.py index 9c9bde1dac..4add5027c9 100644 --- a/test/sql/test_compare.py +++ b/test/sql/test_compare.py @@ -1661,7 +1661,14 @@ class HasCacheKeySubclass(fixtures.TestBase): | {"type_", "modifier", "element"}, ), "Values": ( - {"_column_args", "literal_binds", "name", "_data"}, + { + "_column_args", + "literal_binds", + "name", + "_data", + "_independent_ctes", + "_independent_ctes_opts", + }, {"columns", "name", "literal_binds"}, ), "_FrameClause": ( diff --git a/test/sql/test_values.py b/test/sql/test_values.py index 7f0c8a74a0..9ac3fa71b2 100644 --- a/test/sql/test_values.py +++ b/test/sql/test_values.py @@ -5,6 +5,7 @@ from sqlalchemy import Enum from sqlalchemy import exc from sqlalchemy import ForeignKey from sqlalchemy import Integer +from sqlalchemy import select from sqlalchemy import String from sqlalchemy import Table from sqlalchemy import table @@ -13,8 +14,6 @@ from sqlalchemy import true from sqlalchemy import values from sqlalchemy.engine import default from sqlalchemy.sql import func -from sqlalchemy.sql import select -from sqlalchemy.sql import Values from sqlalchemy.sql.compiler import FROM_LINTING from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import expect_raises_message @@ -62,7 +61,7 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): ) def test_wrong_number_of_elements(self): - v1 = Values( + v1 = values( column("CaseSensitive", Integer), column("has spaces", String), name="Spaces and Cases", @@ -164,7 +163,7 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): ) def test_column_quoting(self): - v1 = Values( + v1 = values( column("CaseSensitive", Integer), column("has spaces", String), column("number", Integer), @@ -183,7 +182,7 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): """test #9772""" people = self.tables.people - table_value_constructor = Values( + table_value_constructor = values( Column("v1", Integer), name="tvc" ).data( [ @@ -204,9 +203,9 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): "AS tvc (v1)) AS anon_1 FROM people", ) - def test_values_in_cte_params(self): + def test_values_in_select_cte_params(self): cte1 = select( - Values( + values( column("col1", String), column("col2", Integer), name="temp_table", @@ -243,9 +242,9 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): dialect=dialect, ) - def test_values_in_cte_literal_binds(self): + def test_values_in_select_cte_literal_binds(self): cte1 = select( - Values( + values( column("col1", String), column("col2", Integer), name="temp_table", @@ -267,6 +266,109 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): checkparams={"col1_1": "q"}, ) + @testing.variation("values_named", [True, False]) + @testing.variation("cte_named", [True, False]) + @testing.variation("literal_binds", [True, False]) + def test_direct_cte(self, values_named, cte_named, literal_binds): + """test #12734""" + + cte1 = ( + values( + column("col1", String), + column("col2", Integer), + literal_binds=bool(literal_binds), + name="some name" if values_named else None, + ) + .data([("a", 2), ("b", 3)]) + .cte("cte1" if cte_named else None) + ) + + stmt = select(cte1.c.col1) + + if cte_named: + cte_name = "cte1" + elif values_named: + cte_name = "some_name_1" + else: + cte_name = "anon_1" + + if literal_binds: + params = "('a', 2), ('b', 3)" + else: + params = "(:param_1, :param_2), (:param_3, :param_4)" + self.assert_compile( + stmt, + f"WITH {cte_name}(col1, col2) AS " + f"(VALUES {params}) " + f"SELECT {cte_name}.col1 FROM {cte_name}", + checkparams=( + { + "param_1": "a", + "param_2": 2, + "param_3": "b", + "param_4": 3, + } + if not literal_binds + else {} + ), + ) + + def test_add_cte_one(self): + cte1 = ( + values( + column("col1", String), + column("col2", Integer), + name="some_name", + ).data([("a", 2), ("b", 3)]) + ).add_cte(select(1).cte()) + + self.assert_compile( + cte1.select(), + "WITH anon_1 AS (SELECT 1) " + "SELECT some_name.col1, some_name.col2 FROM " + "(VALUES (:param_1, :param_2), " + "(:param_3, :param_4)) AS some_name (col1, col2)", + ) + + def test_add_cte_two(self): + cte1 = ( + ( + values( + column("col1", String), + column("col2", Integer), + name="some_name", + ).data([("a", 2), ("b", 3)]) + ) + .add_cte(select(1).cte()) + .cte() + ) + + self.assert_compile( + cte1.select(), + "WITH anon_1 AS (SELECT 1), some_name_1(col1, col2) AS " + "(VALUES (:param_1, :param_2), (:param_3, :param_4)) " + "SELECT some_name_1.col1, some_name_1.col2 FROM some_name_1", + ) + + def test_no_cte_with_lateral(self): + values_ = ( + values( + column("col1", String), + column("col2", Integer), + name="some_name", + ) + .data([("a", 2), ("b", 3)]) + .lateral() + ) + + cte = values_.cte() + + with expect_raises_message( + exc.CompileError, + "Can't use a LATERAL VALUES expression inside of a CTE", + ): + cte.select().compile() + @testing.fixture def literal_parameter_fixture(self): def go(literal_binds, omit=None): @@ -279,7 +381,7 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): for idx in omit: cols[idx] = column(cols[idx].name) - return Values( + return values( *cols, name="myvalues", literal_binds=literal_binds ).data([(1, "textA", 99), (2, "textB", 88)]) @@ -325,7 +427,7 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): for idx in omit: cols[idx] = column(cols[idx].name) - return Values( + return values( *cols, name="myvalues", literal_binds=literal_binds ).data( [ @@ -416,17 +518,17 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): def test_anon_alias(self): people = self.tables.people - values = ( - Values( + values_ = ( + values( column("bookcase_id", Integer), column("bookcase_owner_id", Integer), ) .data([(1, 1), (2, 1), (3, 2), (3, 3)]) .alias() ) - stmt = select(people, values).select_from( + stmt = select(people, values_).select_from( people.join( - values, values.c.bookcase_owner_id == people.c.people_id + values_, values_.c.bookcase_owner_id == people.c.people_id ) ) self.assert_compile( @@ -441,12 +543,12 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): def test_with_join_unnamed(self): people = self.tables.people - values = Values( + values_ = values( column("column1", Integer), column("column2", Integer), ).data([(1, 1), (2, 1), (3, 2), (3, 3)]) - stmt = select(people, values).select_from( - people.join(values, values.c.column2 == people.c.people_id) + stmt = select(people, values_).select_from( + people.join(values_, values_.c.column2 == people.c.people_id) ) self.assert_compile( stmt, @@ -469,14 +571,14 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): def test_with_join_named(self): people = self.tables.people - values = Values( + values_ = values( column("bookcase_id", Integer), column("bookcase_owner_id", Integer), name="bookcases", ).data([(1, 1), (2, 1), (3, 2), (3, 3)]) - stmt = select(people, values).select_from( + stmt = select(people, values_).select_from( people.join( - values, values.c.bookcase_owner_id == people.c.people_id + values_, values_.c.bookcase_owner_id == people.c.people_id ) ) self.assert_compile( @@ -501,17 +603,18 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): def test_with_aliased_join(self): people = self.tables.people - values = ( - Values( + values_ = ( + values( column("bookcase_id", Integer), column("bookcase_owner_id", Integer), ) .data([(1, 1), (2, 1), (3, 2), (3, 3)]) .alias("bookcases") ) - stmt = select(people, values).select_from( + + stmt = select(people, values_).select_from( people.join( - values, values.c.bookcase_owner_id == people.c.people_id + values_, values_.c.bookcase_owner_id == people.c.people_id ) ) self.assert_compile( @@ -536,15 +639,15 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): def test_with_standalone_aliased_join(self): people = self.tables.people - values = Values( + values_ = values( column("bookcase_id", Integer), column("bookcase_owner_id", Integer), ).data([(1, 1), (2, 1), (3, 2), (3, 3)]) - values = alias(values, "bookcases") + values_ = alias(values_, "bookcases") - stmt = select(people, values).select_from( + stmt = select(people, values_).select_from( people.join( - values, values.c.bookcase_owner_id == people.c.people_id + values_, values_.c.bookcase_owner_id == people.c.people_id ) ) self.assert_compile( @@ -569,8 +672,8 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): def test_lateral(self): people = self.tables.people - values = ( - Values( + values_ = ( + values( column("bookcase_id", Integer), column("bookcase_owner_id", Integer), name="bookcases", @@ -578,7 +681,9 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): .data([(1, 1), (2, 1), (3, 2), (3, 3)]) .lateral() ) - stmt = select(people, values).select_from(people.join(values, true())) + stmt = select(people, values_).select_from( + people.join(values_, true()) + ) self.assert_compile( stmt, "SELECT people.people_id, people.age, people.name, " @@ -601,12 +706,12 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): def test_from_linting_named(self): people = self.tables.people - values = Values( + values_ = values( column("bookcase_id", Integer), column("bookcase_owner_id", Integer), name="bookcases", ).data([(1, 1), (2, 1), (3, 2), (3, 3)]) - stmt = select(people, values) + stmt = select(people, values_) with testing.expect_warnings( r"SELECT statement has a cartesian product between FROM " @@ -617,11 +722,11 @@ class ValuesTest(fixtures.TablesTest, AssertsCompiledSQL): def test_from_linting_unnamed(self): people = self.tables.people - values = Values( + values_ = values( column("bookcase_id", Integer), column("bookcase_owner_id", Integer), ).data([(1, 1), (2, 1), (3, 2), (3, 3)]) - stmt = select(people, values) + stmt = select(people, values_) with testing.expect_warnings( r"SELECT statement has a cartesian product between FROM " -- 2.47.2