ensure RETURNING renders in stringify w/ no server version

just in my own testing, if I say insert().return_defaults()
and stringify, I should see it, so make sure all the dialects
default to "insert_returning" etc. , with downgrade on
server version check.

Change-Id: Id64e78fcb03c48b5dcb0feb21cb9cc495edd15e9

11 files changed, 122 insertions, 5 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 596ca34f2..9e7ba4646 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2568,6 +2568,12 @@ class MySQLDialect(default.DefaultDialect):
             # this would have been set by the default dialect already,
             # so set it again
             self.identifier_preparer = self.preparer(self)
+
+            # this will be updated on first connect in initialize()
+            # if using older mariadb version
+            self.delete_returning = True
+            self.insert_returning = True
+
         self.is_mariadb = is_mariadb
 
     def do_begin_twophase(self, connection, xid):
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index ce688741f..222f3a137 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1930,6 +1930,9 @@ class SQLiteDialect(default.DefaultDialect):
     tuple_in_values = True
     supports_statement_cache = True
     insert_null_pk_still_autoincrements = True
+    insert_returning = True
+    update_returning = True
+    delete_returning = True
 
     default_paramstyle = "qmark"
     execution_ctx_cls = SQLiteExecutionContext
@@ -2037,10 +2040,10 @@ class SQLiteDialect(default.DefaultDialect):
                 14,
             )
 
-            if self.dbapi.sqlite_version_info >= (3, 35):
+            if self.dbapi.sqlite_version_info < (3, 35):
                 self.update_returning = (
                     self.delete_returning
-                ) = self.insert_returning = True
+                ) = self.insert_returning = False
 
     _isolation_lookup = util.immutabledict(
         {"READ UNCOMMITTED": 1, "SERIALIZABLE": 0}
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index cab96eac1..4b312dceb 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -903,6 +903,10 @@ class StrCompileDialect(DefaultDialect):
     type_compiler_cls = compiler.StrSQLTypeCompiler
     preparer = compiler.IdentifierPreparer
 
+    insert_returning = True
+    update_returning = True
+    delete_returning = True
+
     supports_statement_cache = True
 
     supports_identity_columns = True
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index 74722e949..8605ea9c0 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -53,6 +53,22 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(sql.false(), "0")
         self.assert_compile(sql.true(), "1")
 
+    def test_plain_stringify_returning(self):
+        t = Table(
+            "t",
+            MetaData(),
+            Column("myid", Integer, primary_key=True),
+            Column("name", String, server_default="some str"),
+            Column("description", String, default=func.lower("hi")),
+        )
+        stmt = t.insert().values().return_defaults()
+        eq_ignore_whitespace(
+            str(stmt.compile(dialect=mssql.dialect())),
+            "INSERT INTO t (description) "
+            "OUTPUT inserted.myid, inserted.name, inserted.description "
+            "VALUES (lower(:lower_1))",
+        )
+
     @testing.combinations(
         ("plain", "sometable", "sometable"),
         ("matched_square_brackets", "colo[u]r", "[colo[u]]r]"),
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 3fb52416e..9d2c43bfe 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -61,6 +61,7 @@ from sqlalchemy.sql.expression import literal_column
 from sqlalchemy.testing import assert_raises_message
 from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import eq_
+from sqlalchemy.testing import eq_ignore_whitespace
 from sqlalchemy.testing import expect_warnings
 from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import mock
@@ -146,6 +147,25 @@ class CompileTest(ReservedWordFixture, fixtures.TestBase, AssertsCompiledSQL):
             dialect=dialect,
         )
 
+    def test_plain_stringify_returning(self):
+        t = Table(
+            "t",
+            MetaData(),
+            Column("myid", Integer, primary_key=True),
+            Column("name", String, server_default="some str"),
+            Column("description", String, default=func.lower("hi")),
+        )
+        stmt = t.insert().values().return_defaults()
+        eq_ignore_whitespace(
+            str(stmt.compile(dialect=mysql.dialect(is_mariadb=True))),
+            "INSERT INTO t (description) VALUES (lower(%s)) "
+            "RETURNING t.myid, t.name, t.description",
+        )
+        eq_ignore_whitespace(
+            str(stmt.compile(dialect=mysql.dialect())),
+            "INSERT INTO t (description) VALUES (lower(%s))",
+        )
+
     def test_create_index_simple(self):
         m = MetaData()
         tbl = Table("testtbl", m, Column("data", String(255)))
diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py
index 846001347..0a23282bf 100644
--- a/test/dialect/mysql/test_reflection.py
+++ b/test/dialect/mysql/test_reflection.py
@@ -369,7 +369,10 @@ class ReflectionTest(fixtures.TestBase, AssertsCompiledSQL):
 
             assert reflected.comment == comment
             assert reflected.kwargs["mysql_comment"] == comment
-            assert reflected.kwargs["mysql_default charset"] == "utf8"
+            assert reflected.kwargs["mysql_default charset"] in (
+                "utf8",
+                "utf8mb3",
+            )
             assert reflected.kwargs["mysql_avg_row_length"] == "3"
             assert reflected.kwargs["mysql_connection"] == "fish"
 
diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py
index 45a83ed77..96969b459 100644
--- a/test/dialect/oracle/test_compiler.py
+++ b/test/dialect/oracle/test_compiler.py
@@ -57,6 +57,22 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(sql.false(), "0")
         self.assert_compile(sql.true(), "1")
 
+    def test_plain_stringify_returning(self):
+        t = Table(
+            "t",
+            MetaData(),
+            Column("myid", Integer, primary_key=True),
+            Column("name", String, server_default="some str"),
+            Column("description", String, default=func.lower("hi")),
+        )
+        stmt = t.insert().values().return_defaults()
+        eq_ignore_whitespace(
+            str(stmt.compile(dialect=oracle.OracleDialect())),
+            "INSERT INTO t (description) VALUES (lower(:lower_1)) "
+            "RETURNING t.myid, t.name, t.description "
+            "INTO :ret_0, :ret_1, :ret_2",
+        )
+
     def test_owner(self):
         meta = MetaData()
         parent = Table(
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 9be76130d..5e5c4f9bd 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -56,6 +56,7 @@ from sqlalchemy.testing import fixtures
 from sqlalchemy.testing.assertions import assert_raises
 from sqlalchemy.testing.assertions import assert_raises_message
 from sqlalchemy.testing.assertions import AssertsCompiledSQL
+from sqlalchemy.testing.assertions import eq_ignore_whitespace
 from sqlalchemy.testing.assertions import expect_warnings
 from sqlalchemy.testing.assertions import is_
 from sqlalchemy.util import OrderedDict
@@ -101,6 +102,21 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
 
     __dialect__ = postgresql.dialect()
 
+    def test_plain_stringify_returning(self):
+        t = Table(
+            "t",
+            MetaData(),
+            Column("myid", Integer, primary_key=True),
+            Column("name", String, server_default="some str"),
+            Column("description", String, default=func.lower("hi")),
+        )
+        stmt = t.insert().values().return_defaults()
+        eq_ignore_whitespace(
+            str(stmt.compile(dialect=postgresql.dialect())),
+            "INSERT INTO t (description) VALUES (lower(%(lower_1)s)) "
+            "RETURNING t.myid, t.name, t.description",
+        )
+
     def test_update_returning(self):
         dialect = postgresql.dialect()
         table1 = table(
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index 286c6bcf8..643a56c1b 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -51,6 +51,7 @@ from sqlalchemy.testing import combinations
 from sqlalchemy.testing import config
 from sqlalchemy.testing import engines
 from sqlalchemy.testing import eq_
+from sqlalchemy.testing import eq_ignore_whitespace
 from sqlalchemy.testing import expect_raises
 from sqlalchemy.testing import expect_warnings
 from sqlalchemy.testing import fixtures
@@ -972,6 +973,21 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
                 "INTEGER) AS anon_1 FROM t" % subst,
             )
 
+    def test_plain_stringify_returning(self):
+        t = Table(
+            "t",
+            MetaData(),
+            Column("myid", Integer, primary_key=True),
+            Column("name", String, server_default="some str"),
+            Column("description", String, default=func.lower("hi")),
+        )
+        stmt = t.insert().values().return_defaults()
+        eq_ignore_whitespace(
+            str(stmt.compile(dialect=sqlite.SQLiteDialect())),
+            "INSERT INTO t (description) VALUES (lower(?)) "
+            "RETURNING myid, name, description",
+        )
+
     def test_true_false(self):
         self.assert_compile(sql.false(), "0")
         self.assert_compile(sql.true(), "1")
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 930f32b7b..1d3d17326 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -4914,6 +4914,21 @@ class StringifySpecialTest(fixtures.TestBase):
         stmt = table1.insert().values()
         eq_ignore_whitespace(str(stmt), "INSERT INTO mytable () VALUES ()")
 
+    def test_insert_return_defaults(self):
+        t = Table(
+            "t",
+            MetaData(),
+            Column("myid", Integer, primary_key=True),
+            Column("name", String, server_default="some str"),
+            Column("description", String, default=func.lower("hi")),
+        )
+        stmt = t.insert().values().return_defaults()
+        eq_ignore_whitespace(
+            str(stmt),
+            "INSERT INTO t (description) VALUES (lower(:lower_1)) "
+            "RETURNING t.myid, t.name, t.description",
+        )
+
     def test_multirow_insert(self):
         stmt = table1.insert().values([{"myid": 1}, {"myid": 2}])
         eq_ignore_whitespace(
diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py
index 808b047a2..071f595f3 100644
--- a/test/sql/test_insert.py
+++ b/test/sql/test_insert.py
@@ -1542,7 +1542,9 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
         )
 
         stmt = table.insert().return_defaults().values(id=func.foobar())
-        compiled = stmt.compile(dialect=sqlite.dialect(), column_keys=["data"])
+        dialect = sqlite.dialect()
+        dialect.insert_returning = False
+        compiled = stmt.compile(dialect=dialect, column_keys=["data"])
         eq_(compiled.postfetch, [])
         eq_(compiled.implicit_returning, [])
 
@@ -1551,7 +1553,7 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             "INSERT INTO sometable (id, data) VALUES " "(foobar(), ?)",
             checkparams={"data": "foo"},
             params={"data": "foo"},
-            dialect=sqlite.dialect(),
+            dialect=dialect,
         )
 
     def test_sql_expression_pk_autoinc_returning(self):