Support DEFAULT VALUES and VALUES(DEFAULT) individually

zzzeek · zzzeek · commit 94a1c523984e · 2021-04-14T12:51:58.000-04:00
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
diff --git a/doc/build/changelog/unreleased_14/6254.rst b/doc/build/changelog/unreleased_14/6254.rst
@@ -0,0 +1,10 @@
+.. change::
+    :tags: bug, regression, sql, sqlite
+    :tickets: 6254
+
+    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 has also been enabled.
diff --git 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
@@ -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
@@ -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
@@ -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
@@ -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
@@ -3480,6 +3480,7 @@ def visit_insert(self, insert_stmt, **kw):
         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
@@ -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
@@ -400,6 +400,7 @@ def assert_compile(
         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 @@ def assert_compile(
         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 @@ def assert_compile(
             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
@@ -76,7 +76,11 @@ def _compare_sql(self, execute_observed, received_statement):
 
     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
@@ -335,10 +335,18 @@ def empty_inserts(self):
 
         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
@@ -167,8 +167,21 @@ def test_empty_insert(self, connection):
                 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):
diff --git a/test/engine/test_deprecations.py b/test/engine/test_deprecations.py
@@ -1589,6 +1589,7 @@ def test_positional_dicts(self, connection):
             [(5, "some name"), (6, "some other name")],
         )
 
+    @testing.requires.empty_inserts
     def test_single_scalar(self, connection):
 
         users = self.tables.users_autoinc
diff --git a/test/orm/inheritance/test_basic.py b/test/orm/inheritance/test_basic.py
@@ -1835,7 +1835,8 @@ def test_ordering(self):
             testing.db,
             sess.flush,
             Conditional(
-                testing.db.dialect.insert_executemany_returning,
+                testing.db.dialect.insert_executemany_returning
+                and testing.db.dialect.supports_default_metavalue,
                 [
                     CompiledSQL(
                         "INSERT INTO a (id) VALUES (DEFAULT)", [{}, {}, {}, {}]
diff --git a/test/requirements.py b/test/requirements.py
@@ -846,6 +846,11 @@ def empty_strings_text(self):
             ["oracle"], "oracle converts empty strings to a blank space"
         )
 
+    @property
+    def empty_inserts_executemany(self):
+        # waiting on https://jira.mariadb.org/browse/CONPY-152
+        return skip_if(["mariadb+mariadbconnector"]) + self.empty_inserts
+
     @property
     def expressions_against_unbounded_text(self):
         """target database supports use of an unbounded textual field in a
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
@@ -2,7 +2,6 @@
 import itertools
 
 import sqlalchemy as sa
-from sqlalchemy import Boolean
 from sqlalchemy import cast
 from sqlalchemy import DateTime
 from sqlalchemy import exc
@@ -1062,33 +1061,18 @@ def test_autoincrement(self, connection):
         )
 
 
-class EmptyInsertTest(fixtures.TestBase):
-    __backend__ = True
-
-    @testing.fails_on("oracle", "FIXME: unknown")
-    def test_empty_insert(self, metadata, connection):
-        t1 = Table(
-            "t1",
-            metadata,
-            Column("is_true", Boolean, server_default=("1")),
-        )
-        metadata.create_all(connection)
-        connection.execute(t1.insert())
-        eq_(
-            1,
-            connection.scalar(select(func.count(text("*"))).select_from(t1)),
-        )
-        eq_(True, connection.scalar(t1.select()))
-
-
 class AutoIncrementTest(fixtures.TestBase):
 
     __backend__ = True
 
     @testing.requires.empty_inserts
     def test_autoincrement_single_col(self, metadata, connection):
         single = Table(
-            "single", self.metadata, Column("id", Integer, primary_key=True)
+            "single",
+            self.metadata,
+            Column(
+                "id", Integer, primary_key=True, test_needs_autoincrement=True
+            ),
         )
         self.metadata.create_all(connection)
 
diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py
@@ -951,7 +951,9 @@ def test_supports_empty_insert_true_executemany_mode(self):
         table1 = self.tables.mytable
 
         dialect = default.DefaultDialect()
-        dialect.supports_empty_insert = dialect.supports_default_values = True
+        dialect.supports_empty_insert = False
+        dialect.supports_default_values = True
+        dialect.supports_default_metavalue = True
 
         stmt = table1.insert().values({})
         self.assert_compile(
@@ -961,6 +963,14 @@ def test_supports_empty_insert_true_executemany_mode(self):
             for_executemany=True,
         )
 
+        dialect.supports_default_metavalue = False
+        self.assert_compile(
+            stmt,
+            "INSERT INTO mytable DEFAULT VALUES",
+            dialect=dialect,
+            for_executemany=True,
+        )
+
     def test_supports_empty_insert_false(self):
         table1 = self.tables.mytable
 
