Improve type detection for Values / Tuple

Fixed issue in new :class:`_sql.Values` construct where passing tuples of
objects would fall back to per-value type detection rather than making use
of the :class:`_schema.Column` objects passed directly to
:class:`_sql.Values` that tells SQLAlchemy what the expected type is. This
would lead to issues for objects such as enumerations and numpy strings
that are not actually necessary since the expected type is given.

note this changes NullType() to raise CompileError for
literal_processor; NullType() does not imply the actual value
NULL as much as it does "unknown type" so this should make failure
modes more clear.

Fixes: #5785
Change-Id: Ifbf5e78373102380b301098f30e15011efa98b5e

4 files changed, 32 insertions, 6 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 46f111d2c..a734bb582 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2610,7 +2610,9 @@ class SQLCompiler(Compiled):
 
         v = "VALUES %s" % ", ".join(
             self.process(
-                elements.Tuple(*elem).self_group(),
+                elements.Tuple(
+                    types=element._column_types, *elem
+                ).self_group(),
                 literal_binds=element.literal_binds,
             )
             for chunk in element._data
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index ab8701dd6..75c1fc1bf 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2497,11 +2497,28 @@ class Tuple(ClauseList, ColumnElement):
         """
         sqltypes = util.preloaded.sql_sqltypes
 
-        clauses = [
-            coercions.expect(roles.ExpressionElementRole, c) for c in clauses
-        ]
-        self.type = sqltypes.TupleType(*[arg.type for arg in clauses])
+        types = kw.pop("types", None)
+        if types is None:
+            clauses = [
+                coercions.expect(roles.ExpressionElementRole, c)
+                for c in clauses
+            ]
+        else:
+            if len(types) != len(clauses):
+                raise exc.ArgumentError(
+                    "Wrong number of elements for %d-tuple: %r "
+                    % (len(types), clauses)
+                )
+            clauses = [
+                coercions.expect(
+                    roles.ExpressionElementRole,
+                    c,
+                    type_=typ if not typ._isnull else None,
+                )
+                for typ, c in zip(types, clauses)
+            ]
 
+        self.type = sqltypes.TupleType(*[arg.type for arg in clauses])
         super(Tuple, self).__init__(*clauses, **kw)
 
     @property
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index d60afdbac..b49fe92df 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -2397,6 +2397,10 @@ class Values(Generative, FromClause):
         self.literal_binds = kw.pop("literal_binds", False)
         self.named_with_column = self.name is not None
 
+    @property
+    def _column_types(self):
+        return [col.type for col in self._column_args]
+
     @_generative
     def alias(self, name, **kw):
         """Return a new :class:`_expression.Values`
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 581573d17..09c7388ab 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -3074,7 +3074,9 @@ class NullType(TypeEngine):
 
     def literal_processor(self, dialect):
         def process(value):
-            return "NULL"
+            raise exc.CompileError(
+                "Don't know how to render literal SQL value: %r" % value
+            )
 
         return process
 
@@ -3131,6 +3133,7 @@ else:
     _type_map[unicode] = Unicode()  # noqa
     _type_map[str] = String()
 
+
 _type_map_get = _type_map.get