Implement ScalarValue

Added :class:`_expression.ScalarValues` that can be used as a column
element allowing using :class:`_expression.Values` inside IN clauses
or in conjunction with ``ANY`` or ``ALL`` collection aggregates.
This new class is generated using the method
:meth:`_expression.Values.scalar_values`.
The :class:`_expression.Values` instance is now coerced to a
:class:`_expression.ScalarValues` when used in a ``IN`` or ``NOT IN``
operation.

Fixes: #6289
Change-Id: Iac22487ccb01553684b908e54d01c0687fa739f1

4 files changed, 78 insertions, 16 deletions

diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py
index f48a3ccb0..9c3e7480a 100644
--- a/lib/sqlalchemy/sql/coercions.py
+++ b/lib/sqlalchemy/sql/coercions.py
@@ -41,6 +41,7 @@ from .. import util
 from ..util.typing import Literal
 
 if typing.TYPE_CHECKING:
+    # elements lambdas schema selectable are set by __init__
     from . import elements
     from . import lambdas
     from . import schema
@@ -354,11 +355,7 @@ def expect(
 
     if not isinstance(
         element,
-        (
-            elements.CompilerElement,
-            schema.SchemaItem,
-            schema.FetchedValue,
-        ),
+        (elements.CompilerElement, schema.SchemaItem, schema.FetchedValue),
     ):
         resolved = None
 
@@ -773,10 +770,15 @@ class ExpressionElementImpl(_ColumnCoercions, RoleImpl):
                 self._raise_for_expected(element, err=err)
 
     def _raise_for_expected(self, element, argname=None, resolved=None, **kw):
-        if isinstance(element, roles.AnonymizedFromClauseRole):
+        # select uses implicit coercion with warning instead of raising
+        if isinstance(element, selectable.Values):
             advice = (
-                "To create a "
-                "column expression from a FROM clause row "
+                "To create a column expression from a VALUES clause, "
+                "use the .scalar_values() method."
+            )
+        elif isinstance(element, roles.AnonymizedFromClauseRole):
+            advice = (
+                "To create a column expression from a FROM clause row "
                 "as a whole, use the .table_valued() method."
             )
         else:
@@ -886,6 +888,8 @@ class InElementImpl(RoleImpl):
             element.expand_op = operator
 
             return element
+        elif isinstance(element, selectable.Values):
+            return element.scalar_values()
         else:
             return element
 
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 17aafddad..9e4422fbd 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -3612,9 +3612,9 @@ class SQLCompiler(Compiled):
 
         return text
 
-    def visit_values(self, element, asfrom=False, from_linter=None, **kw):
+    def _render_values(self, element, **kw):
         kw.setdefault("literal_binds", element.literal_binds)
-        v = "VALUES %s" % ", ".join(
+        tuples = ", ".join(
             self.process(
                 elements.Tuple(
                     types=element._column_types, *elem
@@ -3624,6 +3624,10 @@ class SQLCompiler(Compiled):
             for chunk in element._data
             for elem in chunk
         )
+        return f"VALUES {tuples}"
+
+    def visit_values(self, element, asfrom=False, from_linter=None, **kw):
+        v = self._render_values(element, **kw)
 
         if element._unnamed:
             name = None
@@ -3661,6 +3665,9 @@ class SQLCompiler(Compiled):
                 v = "%s(%s)" % (lateral, v)
         return v
 
+    def visit_scalar_values(self, element, **kw):
+        return f"({self._render_values(element, **kw)})"
+
     def get_render_as_alias_suffix(self, alias_name_text):
         return " AS " + alias_name_text
 
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 914d2b326..6a9bd74ca 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -97,7 +97,6 @@ if typing.TYPE_CHECKING:
     from .selectable import _SelectIterable
     from .selectable import FromClause
     from .selectable import NamedFromClause
-    from .selectable import Select
     from .sqltypes import TupleType
     from .type_api import TypeEngine
     from .visitors import _CloneCallableType
@@ -860,13 +859,17 @@ class SQLCoreOperations(Generic[_T], ColumnOperators, TypingOnly):
 
         def in_(
             self,
-            other: Union[Sequence[Any], BindParameter[Any], Select[Any]],
+            other: Union[
+                Sequence[Any], BindParameter[Any], roles.InElementRole
+            ],
         ) -> BinaryExpression[bool]:
             ...
 
         def not_in(
             self,
-            other: Union[Sequence[Any], BindParameter[Any], Select[Any]],
+            other: Union[
+                Sequence[Any], BindParameter[Any], roles.InElementRole
+            ],
         ) -> BinaryExpression[bool]:
             ...
 
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index fcffc324f..97336d416 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -3127,7 +3127,7 @@ class ForUpdateArg(ClauseElement):
 SelfValues = typing.TypeVar("SelfValues", bound="Values")
 
 
-class Values(Generative, LateralFromClause):
+class Values(roles.InElementRole, Generative, LateralFromClause):
     """Represent a ``VALUES`` construct that can be used as a FROM element
     in a statement.
 
@@ -3228,8 +3228,7 @@ class Values(Generative, LateralFromClause):
     @_generative
     def data(self: SelfValues, values: List[Tuple[Any, ...]]) -> SelfValues:
         """Return a new :class:`_expression.Values` construct,
-        adding the given data
-        to the data list.
+        adding the given data to the data list.
 
         E.g.::
 
@@ -3244,6 +3243,15 @@ class Values(Generative, LateralFromClause):
         self._data += (values,)
         return self
 
+    def scalar_values(self) -> ScalarValues:
+        """Returns a scalar ``VALUES`` construct that can be used as a
+        COLUMN element in a statement.
+
+        .. versionadded:: 2.0.0b4
+
+        """
+        return ScalarValues(self._column_args, self._data, self.literal_binds)
+
     def _populate_column_collection(self) -> None:
         for c in self._column_args:
             self._columns.add(c)
@@ -3254,6 +3262,46 @@ class Values(Generative, LateralFromClause):
         return [self]
 
 
+class ScalarValues(roles.InElementRole, GroupedElement, ColumnElement[Any]):
+    """Represent a scalar ``VALUES`` construct that can be used as a
+    COLUMN element in a statement.
+
+    The :class:`_expression.ScalarValues` object is created from the
+    :meth:`_expression.Values.scalar_values` method. It's also
+    automatically generated when a :class:`_expression.Values` is used in
+    an ``IN`` or ``NOT IN`` condition.
+
+    .. versionadded:: 2.0.0b4
+
+    """
+
+    __visit_name__ = "scalar_values"
+
+    _traverse_internals: _TraverseInternalsType = [
+        ("_column_args", InternalTraversal.dp_clauseelement_list),
+        ("_data", InternalTraversal.dp_dml_multi_values),
+        ("literal_binds", InternalTraversal.dp_boolean),
+    ]
+
+    def __init__(
+        self,
+        columns: Sequence[ColumnClause[Any]],
+        data: Tuple[List[Tuple[Any, ...]], ...],
+        literal_binds: bool,
+    ):
+        super().__init__()
+        self._column_args = columns
+        self._data = data
+        self.literal_binds = literal_binds
+
+    @property
+    def _column_types(self):
+        return [col.type for col in self._column_args]
+
+    def __clause_element__(self):
+        return self
+
+
 SelfSelectBase = TypeVar("SelfSelectBase", bound=Any)