Support DEFAULT VALUES and VALUES(DEFAULT) individually

Fixed regression where the introduction of the INSERT syntax "INSERT...
VALUES (DEFAULT)" was not supported on some backends that do however
support "INSERT..DEFAULT VALUES", including SQLite. The two syntaxes are
now each individually supported or non-supported for each dialect, for
example MySQL supports "VALUES (DEFAULT)" but not "DEFAULT VALUES".
Support for Oracle is still not enabled as there are unresolved issues
in using RETURNING at the same time.

Fixes: #6254
Change-Id: I47959bc826e3d9d2396ccfa290eb084841b02e77

11 files changed, 64 insertions, 4 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 3966126e2..b576fa959 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2534,6 +2534,11 @@ class MySQLDialect(default.DefaultDialect):
     supports_for_update_of = False  # default for MySQL ...
     # ... may be updated to True for MySQL 8+ in initialize()
 
+    # MySQL doesn't support "DEFAULT VALUES" but *does* support
+    # "VALUES (DEFAULT)"
+    supports_default_values = False
+    supports_default_metavalue = True
+
     supports_sane_rowcount = True
     supports_sane_multi_rowcount = False
     supports_multivalues_insert = True
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 11ad61675..c25cfb781 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -1453,7 +1453,11 @@ class OracleDialect(default.DefaultDialect):
     requires_name_normalize = True
 
     supports_comments = True
+
+    # Oracle supports these syntaxes but I'm not able to get them
+    # to work with RETURNING which we usually need
     supports_default_values = False
+    supports_default_metavalue = True
     supports_empty_insert = False
 
     statement_compiler = OracleCompiler
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index d0915a0c9..c6f71c00c 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -3079,6 +3079,9 @@ class PGDialect(default.DefaultDialect):
 
     supports_comments = True
     supports_default_values = True
+
+    supports_default_metavalue = True
+
     supports_empty_insert = False
     supports_multivalues_insert = True
     default_paramstyle = "pyformat"
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 83c2a8ea7..17cd37b49 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1791,7 +1791,12 @@ class SQLiteDialect(default.DefaultDialect):
     supports_alter = False
     supports_unicode_statements = True
     supports_unicode_binds = True
+
+    # SQlite supports "DEFAULT VALUES" but *does not* support
+    # "VALUES (DEFAULT)"
     supports_default_values = True
+    supports_default_metavalue = False
+
     supports_empty_insert = False
     supports_cast = True
     supports_multivalues_insert = True
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index d45b6d7a7..0bd01c588 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -140,8 +140,18 @@ class DefaultDialect(interfaces.Dialect):
     supports_sane_multi_rowcount = True
     colspecs = {}
     default_paramstyle = "named"
+
     supports_default_values = False
+    """dialect supports INSERT... DEFAULT VALUES syntax"""
+
+    supports_default_metavalue = False
+    """dialect supports INSERT... VALUES (DEFAULT) syntax"""
+
+    # not sure if this is a real thing but the compiler will deliver it
+    # if this is the only flag enabled.
     supports_empty_insert = True
+    """dialect supports INSERT () VALUES ()"""
+
     supports_multivalues_insert = False
 
     supports_is_distinct_from = True
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 8eefa10d1..0c701cb52 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -3480,6 +3480,7 @@ class SQLCompiler(Compiled):
         if (
             not crud_params
             and not self.dialect.supports_default_values
+            and not self.dialect.supports_default_metavalue
             and not self.dialect.supports_empty_insert
         ):
             raise exc.CompileError(
diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py
index 174a1c131..5fa82bcd0 100644
--- a/lib/sqlalchemy/sql/crud.py
+++ b/lib/sqlalchemy/sql/crud.py
@@ -174,7 +174,11 @@ def _get_crud_params(compiler, stmt, compile_state, **kw):
         values = _extend_values_for_multiparams(
             compiler, stmt, compile_state, values, kw
         )
-    elif not values and compiler.for_executemany:
+    elif (
+        not values
+        and compiler.for_executemany
+        and compiler.dialect.supports_default_metavalue
+    ):
         # convert an "INSERT DEFAULT VALUES"
         # into INSERT (firstcol) VALUES (DEFAULT) which can be turned
         # into an in-place multi values.  This supports
diff --git a/lib/sqlalchemy/testing/assertions.py b/lib/sqlalchemy/testing/assertions.py
index 02137474b..9ff2f76eb 100644
--- a/lib/sqlalchemy/testing/assertions.py
+++ b/lib/sqlalchemy/testing/assertions.py
@@ -400,6 +400,7 @@ class AssertsCompiledSQL(object):
         use_default_dialect=False,
         allow_dialect_select=False,
         supports_default_values=True,
+        supports_default_metavalue=True,
         literal_binds=False,
         render_postcompile=False,
         schema_translate_map=None,
@@ -410,6 +411,7 @@ class AssertsCompiledSQL(object):
         if use_default_dialect:
             dialect = default.DefaultDialect()
             dialect.supports_default_values = supports_default_values
+            dialect.supports_default_metavalue = supports_default_metavalue
         elif allow_dialect_select:
             dialect = None
         else:
@@ -421,6 +423,7 @@ class AssertsCompiledSQL(object):
             elif dialect == "default":
                 dialect = default.DefaultDialect()
                 dialect.supports_default_values = supports_default_values
+                dialect.supports_default_metavalue = supports_default_metavalue
             elif dialect == "default_enhanced":
                 dialect = default.StrCompileDialect()
             elif isinstance(dialect, util.string_types):
diff --git a/lib/sqlalchemy/testing/assertsql.py b/lib/sqlalchemy/testing/assertsql.py
index 1bdd11585..98261a374 100644
--- a/lib/sqlalchemy/testing/assertsql.py
+++ b/lib/sqlalchemy/testing/assertsql.py
@@ -76,7 +76,11 @@ class CompiledSQL(SQLMatchRule):
 
     def _compile_dialect(self, execute_observed):
         if self.dialect == "default":
-            return DefaultDialect()
+            dialect = DefaultDialect()
+            # this is currently what tests are expecting
+            # dialect.supports_default_values = True
+            dialect.supports_default_metavalue = True
+            return dialect
         else:
             # ugh
             if self.dialect == "postgresql":
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index f82d5f065..8a70cc692 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -335,11 +335,19 @@ class SuiteRequirements(Requirements):
 
         return exclusions.only_if(
             lambda config: config.db.dialect.supports_empty_insert
-            or config.db.dialect.supports_default_values,
+            or config.db.dialect.supports_default_values
+            or config.db.dialect.supports_default_metavalue,
             "empty inserts not supported",
         )
 
     @property
+    def empty_inserts_executemany(self):
+        """target platform supports INSERT with no values, i.e.
+        INSERT DEFAULT VALUES or equivalent, within executemany()"""
+
+        return self.empty_inserts
+
+    @property
     def insert_from_select(self):
         """target platform supports INSERT from a SELECT."""
 
diff --git a/lib/sqlalchemy/testing/suite/test_insert.py b/lib/sqlalchemy/testing/suite/test_insert.py
index 35f3315c7..3c033a774 100644
--- a/lib/sqlalchemy/testing/suite/test_insert.py
+++ b/lib/sqlalchemy/testing/suite/test_insert.py
@@ -167,8 +167,21 @@ class InsertBehaviorTest(fixtures.TablesTest):
                 self.tables.autoinc_pk.c.id != None
             )
         )
+        eq_(len(r.all()), 1)
 
-        assert len(r.fetchall())
+    @requirements.empty_inserts_executemany
+    def test_empty_insert_multiple(self, connection):
+        r = connection.execute(self.tables.autoinc_pk.insert(), [{}, {}, {}])
+        assert r._soft_closed
+        assert not r.closed
+
+        r = connection.execute(
+            self.tables.autoinc_pk.select().where(
+                self.tables.autoinc_pk.c.id != None
+            )
+        )
+
+        eq_(len(r.all()), 3)
 
     @requirements.insert_from_select
     def test_insert_from_select_autoinc(self, connection):