Support tuple IN VALUES for SQLite, others

Added support for composite (tuple) IN operators with SQLite, by rendering
the VALUES keyword for this backend.  As other backends such as DB2 are
known to use the same syntax, the syntax is enabled in the base compiler
using a dialect-level flag ``tuple_in_values``.   The change also includes
support for "empty IN tuple" expressions for SQLite when using "in_()"
between a tuple value and an empty set.

Fixes: #4766
Change-Id: I416e1af29b31d78f9ae06ec3c3a48ef6d6e813f5

5 files changed, 29 insertions, 12 deletions

diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index b6ca8fe3c..c9309cbad 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1023,8 +1023,11 @@ class SQLiteCompiler(compiler.SQLCompiler):
             self.process(binary.right, **kw),
         )
 
-    def visit_empty_set_expr(self, type_):
-        return "SELECT 1 FROM (SELECT 1) WHERE 1!=1"
+    def visit_empty_set_expr(self, element_types):
+        return "SELECT %s FROM (SELECT %s) WHERE 1!=1" % (
+            ", ".join("1" for type_ in element_types or [INTEGER()]),
+            ", ".join("1" for type_ in element_types or [INTEGER()]),
+        )
 
 
 class SQLiteDDLCompiler(compiler.DDLCompiler):
@@ -1391,6 +1394,7 @@ class SQLiteDialect(default.DefaultDialect):
     supports_empty_insert = False
     supports_cast = True
     supports_multivalues_insert = True
+    tuple_in_values = True
 
     default_paramstyle = "qmark"
     execution_ctx_cls = SQLiteExecutionContext
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index f6c30cbf4..b56755d62 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -75,6 +75,8 @@ class DefaultDialect(interfaces.Dialect):
 
     supports_simple_order_by_label = True
 
+    tuple_in_values = False
+
     engine_config_types = util.immutabledict(
         [
             ("convert_unicode", util.bool_or_str("force")),
@@ -812,7 +814,9 @@ class DefaultExecutionContext(interfaces.ExecutionContext):
                             for i, tuple_element in enumerate(values, 1)
                             for j, value in enumerate(tuple_element, 1)
                         ]
-                        replacement_expressions[name] = ", ".join(
+                        replacement_expressions[name] = (
+                            "VALUES " if self.dialect.tuple_in_values else ""
+                        ) + ", ".join(
                             "(%s)"
                             % ", ".join(
                                 self.compiled.bindtemplate
diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py
index 64d9f0f96..8a9f0b979 100644
--- a/lib/sqlalchemy/sql/coercions.py
+++ b/lib/sqlalchemy/sql/coercions.py
@@ -332,7 +332,9 @@ class InElementImpl(RoleImpl, roles.InElementRole):
                     o = expr._bind_param(operator, o)
                 args.append(o)
 
-            return elements.ClauseList(*args)
+            return elements.ClauseList(
+                _tuple_values=isinstance(expr, elements.Tuple), *args
+            )
 
         else:
             self._raise_for_expected(element, **kw)
@@ -354,7 +356,6 @@ class InElementImpl(RoleImpl, roles.InElementRole):
                 return element.self_group(against=operator)
 
         elif isinstance(element, elements.BindParameter) and element.expanding:
-
             if isinstance(expr, elements.Tuple):
                 element = element._with_expanding_in_types(
                     [elem.type for elem in expr]
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index ea7e890e7..740abeb3d 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -966,13 +966,17 @@ class SQLCompiler(Compiled):
             sep = " "
         else:
             sep = OPERATORS[clauselist.operator]
-        return sep.join(
+
+        text = sep.join(
             s
             for s in (
                 c._compiler_dispatch(self, **kw) for c in clauselist.clauses
             )
             if s
         )
+        if clauselist._tuple_values and self.dialect.tuple_in_values:
+            text = "VALUES " + text
+        return text
 
     def visit_case(self, clause, **kwargs):
         x = "CASE "
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 6d1174d20..e2df1adc2 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -1945,7 +1945,7 @@ class ClauseList(
         self.operator = kwargs.pop("operator", operators.comma_op)
         self.group = kwargs.pop("group", True)
         self.group_contents = kwargs.pop("group_contents", True)
-
+        self._tuple_values = kwargs.pop("_tuple_values", False)
         self._text_converter_role = text_converter_role = kwargs.pop(
             "_literal_as_text_role", roles.WhereHavingRole
         )
@@ -2011,6 +2011,8 @@ class ClauseList(
 class BooleanClauseList(ClauseList, ColumnElement):
     __visit_name__ = "clauselist"
 
+    _tuple_values = False
+
     def __init__(self, *arg, **kw):
         raise NotImplementedError(
             "BooleanClauseList has a private constructor"
@@ -2162,13 +2164,15 @@ class Tuple(ClauseList, ColumnElement):
                 [(1, 2), (5, 12), (10, 19)]
             )
 
+        .. versionchanged:: 1.3.6 Added support for SQLite IN tuples.
+
         .. warning::
 
-            The composite IN construct is not supported by all backends,
-            and is currently known to work on PostgreSQL and MySQL,
-            but not SQLite.   Unsupported backends will raise
-            a subclass of :class:`~sqlalchemy.exc.DBAPIError` when such
-            an expression is invoked.
+            The composite IN construct is not supported by all backends, and is
+            currently known to work on PostgreSQL, MySQL, and SQLite.
+            Unsupported backends will raise a subclass of
+            :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
+            invoked.
 
         """