Decouple compiler state from DML objects; make cacheable

Targeting select / insert / update / delete, the goal
is to minimize overhead of construction and generative methods
so that only the raw arguments passed are handled.   An interim
stage that converts the raw state into more compiler-ready state
is added, which is analogous to the ORM QueryContext which will
also be rolled in to be a similar concept, as is currently
being prototyped in I19e05b3424b07114cce6c439b05198ac47f7ac10.
the ORM update/delete BulkUD concept is also going to be rolled
onto this idea.   So while the compiler-ready state object,
here called DMLState, looks a little thin, it's the
base of a bigger pattern that will allow for ORM functionality
to embed itself directly into the compiler, execution
context, and result set objects.

This change targets the DML objects, primarily focused on the
values() method which is the most complex process.   The
work done by values() is minimized as much as possible
while still being able to create a cache key.   Additional
computation is then offloaded to a new object ValuesState
that is handled by the compiler.

Architecturally, a big change here is that insert.values()
and update.values() will generate BindParameter objects for
the values now, which are then carefully received by crud.py
so that they generate the expected names.   This is so that
the values() portion of these constructs is cacheable.
for the "multi-values" version of Insert, this is all skipped
and the plan right now is that a multi-values insert is
not worth caching (can always be revisited).

Using the
coercions system in values() also gets us nicer validation
for free, we can remove the NotAClauseElement thing from
schema, and we also now require scalar_subquery() is called
for an insert/update that uses a SELECT as a column value,
1.x deprecation path is added.

The traversal system is then applied to the DML objects
including tests so that they have traversal, cloning, and
cache key support.  cloning is not a use case for DML however
having it present allows better validation of the structure
within the tests.

Special per-dialect DML is explicitly not cacheable at the moment,
more as a proof of concept that third party DML constructs can
exist as gracefully not-cacheable rather than producing an
incomplete cache key.

A few selected performance improvements have been added as well,
simplifying the immutabledict.union() method and adding
a new SQLCompiler function that can generate delimeter-separated
clauses like WHERE and ORDER BY without having to build
a ClauseList object at all.   The use of ClauseList will
be removed from Select in an upcoming commit.  Overall,
ClaustList is unnecessary for internal use and only adds
overhead to statement construction and will likely be removed
as much as possible except for explcit use of conjunctions like
and_() and or_().

Change-Id: I408e0b8be91fddd77cf279da97f55020871f75a9

7 files changed, 384 insertions, 67 deletions

diff --git a/test/sql/test_compare.py b/test/sql/test_compare.py
index fb6c515e4..185244094 100644
--- a/test/sql/test_compare.py
+++ b/test/sql/test_compare.py
@@ -25,9 +25,12 @@ from sqlalchemy import tuple_
 from sqlalchemy import union
 from sqlalchemy import union_all
 from sqlalchemy import util
+from sqlalchemy.dialects import mysql
+from sqlalchemy.dialects import postgresql
 from sqlalchemy.schema import Sequence
 from sqlalchemy.sql import bindparam
 from sqlalchemy.sql import ColumnElement
+from sqlalchemy.sql import dml
 from sqlalchemy.sql import False_
 from sqlalchemy.sql import func
 from sqlalchemy.sql import operators
@@ -57,7 +60,6 @@ from sqlalchemy.sql.visitors import InternalTraversal
 from sqlalchemy.testing import eq_
 from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import is_false
-from sqlalchemy.testing import is_not_
 from sqlalchemy.testing import is_true
 from sqlalchemy.testing import ne_
 from sqlalchemy.testing.util import random_choices
@@ -96,6 +98,11 @@ class MyEntity(HasCacheKey):
     ]
 
 
+dml.Insert.argument_for("sqlite", "foo", None)
+dml.Update.argument_for("sqlite", "foo", None)
+dml.Delete.argument_for("sqlite", "foo", None)
+
+
 class CoreFixtures(object):
     # lambdas which return a tuple of ColumnElement objects.
     # must return at least two objects that should compare differently.
@@ -329,6 +336,64 @@ class CoreFixtures(object):
             ),
         ),
         lambda: (
+            table_a.insert(),
+            table_a.insert().values({})._annotate({"nocache": True}),
+            table_b.insert(),
+            table_b.insert().with_dialect_options(sqlite_foo="some value"),
+            table_b.insert().from_select(["a", "b"], select([table_a])),
+            table_b.insert().from_select(
+                ["a", "b"], select([table_a]).where(table_a.c.a > 5)
+            ),
+            table_b.insert().from_select(["a", "b"], select([table_b])),
+            table_b.insert().from_select(["c", "d"], select([table_a])),
+            table_b.insert().returning(table_b.c.a),
+            table_b.insert().returning(table_b.c.a, table_b.c.b),
+            table_b.insert().inline(),
+            table_b.insert().prefix_with("foo"),
+            table_b.insert().with_hint("RUNFAST"),
+            table_b.insert().values(a=5, b=10),
+            table_b.insert().values(a=5),
+            table_b.insert()
+            .values({table_b.c.a: 5, "b": 10})
+            ._annotate({"nocache": True}),
+            table_b.insert().values(a=7, b=10),
+            table_b.insert().values(a=5, b=10).inline(),
+            table_b.insert()
+            .values([{"a": 5, "b": 10}, {"a": 8, "b": 12}])
+            ._annotate({"nocache": True}),
+        ),
+        lambda: (
+            table_b.update(),
+            table_b.update().where(table_b.c.a == 5),
+            table_b.update().where(table_b.c.b == 5),
+            table_b.update()
+            .where(table_b.c.b == 5)
+            .with_dialect_options(mysql_limit=10),
+            table_b.update()
+            .where(table_b.c.b == 5)
+            .with_dialect_options(mysql_limit=10, sqlite_foo="some value"),
+            table_b.update().where(table_b.c.a == 5).values(a=5, b=10),
+            table_b.update().where(table_b.c.a == 5).values(a=5, b=10, c=12),
+            table_b.update()
+            .where(table_b.c.b == 5)
+            .values(a=5, b=10)
+            ._annotate({"nocache": True}),
+            table_b.update().values(a=5, b=10),
+            table_b.update()
+            .values({"a": 5, table_b.c.b: 10})
+            ._annotate({"nocache": True}),
+            table_b.update().values(a=7, b=10),
+            table_b.update().ordered_values(("a", 5), ("b", 10)),
+            table_b.update().ordered_values(("b", 10), ("a", 5)),
+            table_b.update().ordered_values((table_b.c.a, 5), ("b", 10)),
+        ),
+        lambda: (
+            table_b.delete(),
+            table_b.delete().with_dialect_options(sqlite_foo="some value"),
+            table_b.delete().where(table_b.c.a == 5),
+            table_b.delete().where(table_b.c.b == 5),
+        ),
+        lambda: (
             select([table_a.c.a]),
             select([table_a.c.a]).prefix_with("foo"),
             select([table_a.c.a]).prefix_with("foo", dialect="mysql"),
@@ -490,8 +555,12 @@ class CacheKeyFixture(object):
             if a == b:
                 a_key = case_a[a]._generate_cache_key()
                 b_key = case_b[b]._generate_cache_key()
-                is_not_(a_key, None)
-                is_not_(b_key, None)
+
+                if a_key is None:
+                    assert case_a[a]._annotations.get("nocache")
+
+                    assert b_key is None
+                    continue
 
                 eq_(a_key.key, b_key.key)
                 eq_(hash(a_key), hash(b_key))
@@ -506,6 +575,13 @@ class CacheKeyFixture(object):
                 a_key = case_a[a]._generate_cache_key()
                 b_key = case_b[b]._generate_cache_key()
 
+                if a_key is None or b_key is None:
+                    if a_key is None:
+                        assert case_a[a]._annotations.get("nocache")
+                    if b_key is None:
+                        assert case_b[b]._annotations.get("nocache")
+                    continue
+
                 if a_key.key == b_key.key:
                     for a_param, b_param in zip(
                         a_key.bindparams, b_key.bindparams
@@ -562,7 +638,18 @@ class CacheKeyFixture(object):
 
 
 class CacheKeyTest(CacheKeyFixture, CoreFixtures, fixtures.TestBase):
-    @testing.combinations(table_a.update(), table_a.insert(), table_a.delete())
+    # we are slightly breaking the policy of not having external dialect
+    # stuff in here, but use pg/mysql as test cases to ensure that these
+    # objects don't report an inaccurate cache key, which is dependent
+    # on the base insert sending out _post_values_clause and the caching
+    # system properly recognizing these constructs as not cacheable
+
+    @testing.combinations(
+        postgresql.insert(table_a).on_conflict_do_update(
+            index_elements=[table_a.c.a], set_={"name": "foo"}
+        ),
+        mysql.insert(table_a).on_duplicate_key_update(updated_once=None),
+    )
     def test_dml_not_cached_yet(self, dml_stmt):
         eq_(dml_stmt._generate_cache_key(), None)
 
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 5030f9df8..6b1f443e2 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -4818,7 +4818,11 @@ class ResultMapTest(fixtures.TestBase):
         Table("t1", m, astring)
         t2 = Table("t2", m, aint)
 
-        stmt = t2.insert().values(a=select([astring])).returning(aint)
+        stmt = (
+            t2.insert()
+            .values(a=select([astring]).scalar_subquery())
+            .returning(aint)
+        )
         comp = stmt.compile(dialect=postgresql.dialect())
         eq_(
             comp._create_result_map(),
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index 831f2a680..957fa890a 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -658,16 +658,18 @@ class DefaultTest(fixtures.TestBase):
                 [const],
             )
             assert_raises_message(
-                sa.exc.InvalidRequestError,
-                "cannot be used directly as a column expression.",
-                str,
-                t.insert().values(col4=const),
+                sa.exc.ArgumentError,
+                "SQL expression element expected, got %s"
+                % const.__class__.__name__,
+                t.insert().values,
+                col4=const,
             )
             assert_raises_message(
-                sa.exc.InvalidRequestError,
-                "cannot be used directly as a column expression.",
-                str,
-                t.update().values(col4=const),
+                sa.exc.ArgumentError,
+                "SQL expression element expected, got %s"
+                % const.__class__.__name__,
+                t.update().values,
+                col4=const,
             )
 
     def test_missing_many_param(self):
diff --git a/test/sql/test_deprecations.py b/test/sql/test_deprecations.py
index 06fe22fed..5b7b3bd1f 100644
--- a/test/sql/test_deprecations.py
+++ b/test/sql/test_deprecations.py
@@ -22,6 +22,7 @@ from sqlalchemy import String
 from sqlalchemy import table
 from sqlalchemy import testing
 from sqlalchemy import text
+from sqlalchemy import update
 from sqlalchemy import util
 from sqlalchemy import VARCHAR
 from sqlalchemy.engine import default
@@ -1823,3 +1824,75 @@ class DMLTest(fixtures.TestBase, AssertsCompiledSQL):
             "col3=:col3",
             inline_flag=True,
         )
+
+    def test_update_dialect_kwargs(self):
+        t = table("foo", column("bar"))
+
+        with testing.expect_deprecated_20("Passing dialect keyword arguments"):
+            stmt = t.update(mysql_limit=10)
+
+        self.assert_compile(
+            stmt, "UPDATE foo SET bar=%s LIMIT 10", dialect="mysql"
+        )
+
+    @testing.fixture()
+    def update_from_fixture(self):
+        metadata = MetaData()
+
+        mytable = Table(
+            "mytable",
+            metadata,
+            Column("myid", Integer),
+            Column("name", String(30)),
+            Column("description", String(50)),
+        )
+        myothertable = Table(
+            "myothertable",
+            metadata,
+            Column("otherid", Integer),
+            Column("othername", String(30)),
+        )
+        return mytable, myothertable
+
+    def test_correlated_update_two(self, update_from_fixture):
+        table1, t2 = update_from_fixture
+
+        mt = table1.alias()
+        with testing.expect_deprecated(
+            "coercing SELECT object to scalar subquery in a column-expression "
+            "context is deprecated"
+        ):
+            u = update(
+                table1,
+                values={
+                    table1.c.name: select(
+                        [mt.c.name], mt.c.myid == table1.c.myid
+                    )
+                },
+            )
+        self.assert_compile(
+            u,
+            "UPDATE mytable SET name=(SELECT mytable_1.name FROM "
+            "mytable AS mytable_1 WHERE "
+            "mytable_1.myid = mytable.myid)",
+        )
+
+    def test_correlated_update_three(self, update_from_fixture):
+        table1, table2 = update_from_fixture
+
+        # test against a regular constructed subquery
+        s = select([table2], table2.c.otherid == table1.c.myid)
+        with testing.expect_deprecated(
+            "coercing SELECT object to scalar subquery in a column-expression "
+            "context is deprecated"
+        ):
+            u = update(
+                table1, table1.c.name == "jack", values={table1.c.name: s}
+            )
+        self.assert_compile(
+            u,
+            "UPDATE mytable SET name=(SELECT myothertable.otherid, "
+            "myothertable.othername FROM myothertable WHERE "
+            "myothertable.otherid = mytable.myid) "
+            "WHERE mytable.name = :name_1",
+        )
diff --git a/test/sql/test_external_traversal.py b/test/sql/test_external_traversal.py
index 84d99d886..2a82c2cc1 100644
--- a/test/sql/test_external_traversal.py
+++ b/test/sql/test_external_traversal.py
@@ -7,6 +7,7 @@ from sqlalchemy import extract
 from sqlalchemy import ForeignKey
 from sqlalchemy import func
 from sqlalchemy import Integer
+from sqlalchemy import literal
 from sqlalchemy import literal_column
 from sqlalchemy import MetaData
 from sqlalchemy import select
@@ -37,7 +38,6 @@ from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import is_
 from sqlalchemy.testing import is_not_
 
-
 A = B = t1 = t2 = t3 = table1 = table2 = table3 = table4 = None
 
 
@@ -1961,29 +1961,53 @@ class ValuesBaseTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_add_kwarg(self):
         i = t1.insert()
-        eq_(i.parameters, None)
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+        eq_(compile_state._dict_parameters, None)
         i = i.values(col1=5)
-        eq_(i.parameters, {"col1": 5})
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+        self._compare_param_dict(compile_state._dict_parameters, {"col1": 5})
         i = i.values(col2=7)
-        eq_(i.parameters, {"col1": 5, "col2": 7})
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+        self._compare_param_dict(
+            compile_state._dict_parameters, {"col1": 5, "col2": 7}
+        )
 
     def test_via_tuple_single(self):
         i = t1.insert()
-        eq_(i.parameters, None)
+
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+        eq_(compile_state._dict_parameters, None)
+
         i = i.values((5, 6, 7))
-        eq_(i.parameters, {"col1": 5, "col2": 6, "col3": 7})
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+
+        self._compare_param_dict(
+            compile_state._dict_parameters, {"col1": 5, "col2": 6, "col3": 7},
+        )
 
     def test_kw_and_dict_simultaneously_single(self):
         i = t1.insert()
-        i = i.values({"col1": 5}, col2=7)
-        eq_(i.parameters, {"col1": 5, "col2": 7})
+        assert_raises_message(
+            exc.ArgumentError,
+            r"Can't pass positional and kwargs to values\(\) simultaneously",
+            i.values,
+            {"col1": 5},
+            col2=7,
+        )
 
     def test_via_tuple_multi(self):
         i = t1.insert()
-        eq_(i.parameters, None)
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+        eq_(compile_state._dict_parameters, None)
+
         i = i.values([(5, 6, 7), (8, 9, 10)])
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
         eq_(
-            i.parameters,
+            compile_state._dict_parameters, {"col1": 5, "col2": 6, "col3": 7},
+        )
+        eq_(compile_state._has_multi_parameters, True)
+        eq_(
+            compile_state._multi_parameters,
             [
                 {"col1": 5, "col2": 6, "col3": 7},
                 {"col1": 8, "col2": 9, "col3": 10},
@@ -1992,58 +2016,92 @@ class ValuesBaseTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_inline_values_single(self):
         i = t1.insert(values={"col1": 5})
-        eq_(i.parameters, {"col1": 5})
-        is_(i._has_multi_parameters, False)
+
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+
+        self._compare_param_dict(compile_state._dict_parameters, {"col1": 5})
+        is_(compile_state._has_multi_parameters, False)
 
     def test_inline_values_multi(self):
         i = t1.insert(values=[{"col1": 5}, {"col1": 6}])
-        eq_(i.parameters, [{"col1": 5}, {"col1": 6}])
-        is_(i._has_multi_parameters, True)
+
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+
+        # multiparams are not converted to bound parameters
+        eq_(compile_state._dict_parameters, {"col1": 5})
+
+        # multiparams are not converted to bound parameters
+        eq_(compile_state._multi_parameters, [{"col1": 5}, {"col1": 6}])
+        is_(compile_state._has_multi_parameters, True)
+
+    def _compare_param_dict(self, a, b):
+        if list(a) != list(b):
+            return False
+
+        from sqlalchemy.types import NullType
+
+        for a_k, a_i in a.items():
+            b_i = b[a_k]
+
+            # compare BindParameter on the left to
+            # literal value on the right
+            assert a_i.compare(literal(b_i, type_=NullType()))
 
     def test_add_dictionary(self):
         i = t1.insert()
-        eq_(i.parameters, None)
+
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+
+        eq_(compile_state._dict_parameters, None)
         i = i.values({"col1": 5})
-        eq_(i.parameters, {"col1": 5})
-        is_(i._has_multi_parameters, False)
+
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+
+        self._compare_param_dict(compile_state._dict_parameters, {"col1": 5})
+        is_(compile_state._has_multi_parameters, False)
 
         i = i.values({"col1": 6})
         # note replaces
-        eq_(i.parameters, {"col1": 6})
-        is_(i._has_multi_parameters, False)
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+
+        self._compare_param_dict(compile_state._dict_parameters, {"col1": 6})
+        is_(compile_state._has_multi_parameters, False)
 
         i = i.values({"col2": 7})
-        eq_(i.parameters, {"col1": 6, "col2": 7})
-        is_(i._has_multi_parameters, False)
+        compile_state = i._compile_state_cls(i, None, isinsert=True)
+        self._compare_param_dict(
+            compile_state._dict_parameters, {"col1": 6, "col2": 7}
+        )
+        is_(compile_state._has_multi_parameters, False)
 
     def test_add_kwarg_disallowed_multi(self):
         i = t1.insert()
         i = i.values([{"col1": 5}, {"col1": 7}])
+        i = i.values(col2=7)
         assert_raises_message(
             exc.InvalidRequestError,
-            "This construct already has multiple parameter sets.",
-            i.values,
-            col2=7,
+            "Can't mix single and multiple VALUES formats",
+            i.compile,
         )
 
     def test_cant_mix_single_multi_formats_dict_to_list(self):
         i = t1.insert().values(col1=5)
+        i = i.values([{"col1": 6}])
         assert_raises_message(
-            exc.ArgumentError,
-            "Can't mix single-values and multiple values "
-            "formats in one statement",
-            i.values,
-            [{"col1": 6}],
+            exc.InvalidRequestError,
+            "Can't mix single and multiple VALUES "
+            "formats in one INSERT statement",
+            i.compile,
         )
 
     def test_cant_mix_single_multi_formats_list_to_dict(self):
         i = t1.insert().values([{"col1": 6}])
+        i = i.values({"col1": 5})
         assert_raises_message(
-            exc.ArgumentError,
-            "Can't mix single-values and multiple values "
-            "formats in one statement",
-            i.values,
-            {"col1": 5},
+            exc.InvalidRequestError,
+            "Can't mix single and multiple VALUES "
+            "formats in one INSERT statement",
+            i.compile,
         )
 
     def test_erroneous_multi_args_dicts(self):
@@ -2072,7 +2130,7 @@ class ValuesBaseTest(fixtures.TestBase, AssertsCompiledSQL):
         i = t1.insert()
         assert_raises_message(
             exc.ArgumentError,
-            "Can't pass kwargs and multiple parameter sets simultaneously",
+            r"Can't pass positional and kwargs to values\(\) simultaneously",
             i.values,
             [{"col1": 5}],
             col2=7,
@@ -2080,17 +2138,18 @@ class ValuesBaseTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_update_no_support_multi_values(self):
         u = t1.update()
+        u = u.values([{"col1": 5}, {"col1": 7}])
         assert_raises_message(
             exc.InvalidRequestError,
-            "This construct does not support multiple parameter sets.",
-            u.values,
-            [{"col1": 5}, {"col1": 7}],
+            "UPDATE construct does not support multiple parameter sets.",
+            u.compile,
         )
 
     def test_update_no_support_multi_constructor(self):
+        stmt = t1.update(values=[{"col1": 5}, {"col1": 7}])
+
         assert_raises_message(
             exc.InvalidRequestError,
-            "This construct does not support multiple parameter sets.",
-            t1.update,
-            values=[{"col1": 5}, {"col1": 7}],
+            "UPDATE construct does not support multiple parameter sets.",
+            stmt.compile,
         )
diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py
index 7508ee6c7..8a067b65a 100644
--- a/test/sql/test_insert.py
+++ b/test/sql/test_insert.py
@@ -13,6 +13,7 @@ from sqlalchemy import Sequence
 from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import table
+from sqlalchemy import testing
 from sqlalchemy import text
 from sqlalchemy.dialects import mysql
 from sqlalchemy.dialects import postgresql
@@ -256,7 +257,7 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             "INSERT INTO mytable (myid, name) VALUES (:userid, :username)",
         )
 
-    def test_insert_values(self):
+    def test_insert_values_multiple(self):
         table1 = self.tables.mytable
 
         values1 = {table1.c.myid: bindparam("userid")}
@@ -961,7 +962,8 @@ class EmptyTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             dialect=dialect,
         )
 
-    def _test_insert_with_empty_collection_values(self, collection):
+    @testing.combinations(([],), ({},), ((),))
+    def test_insert_with_empty_collection_values(self, collection):
         table1 = self.tables.mytable
 
         ins = table1.insert().values(collection)
@@ -977,15 +979,6 @@ class EmptyTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             checkparams={"myid": 3},
         )
 
-    def test_insert_with_empty_list_values(self):
-        self._test_insert_with_empty_collection_values([])
-
-    def test_insert_with_empty_dict_values(self):
-        self._test_insert_with_empty_collection_values({})
-
-    def test_insert_with_empty_tuple_values(self):
-        self._test_insert_with_empty_collection_values(())
-
 
 class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
     __dialect__ = "default"
@@ -1123,6 +1116,62 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             dialect=dialect,
         )
 
+    def test_mix_single_and_multi_single_first(self):
+        table1 = self.tables.mytable
+
+        stmt = table1.insert().values(myid=1, name="d1")
+        stmt = stmt.values(
+            [{"myid": 2, "name": "d2"}, {"myid": 3, "name": "d3"}]
+        )
+
+        assert_raises_message(
+            exc.InvalidRequestError,
+            "Can't mix single and multiple VALUES formats in one "
+            "INSERT statement",
+            stmt.compile,
+        )
+
+    def test_mix_single_and_multi_multi_first(self):
+        table1 = self.tables.mytable
+
+        stmt = table1.insert().values(
+            [{"myid": 2, "name": "d2"}, {"myid": 3, "name": "d3"}]
+        )
+
+        stmt = stmt.values(myid=1, name="d1")
+
+        assert_raises_message(
+            exc.InvalidRequestError,
+            "Can't mix single and multiple VALUES formats in one "
+            "INSERT statement",
+            stmt.compile,
+        )
+
+    def test_multi_multi(self):
+        table1 = self.tables.mytable
+
+        stmt = table1.insert().values([{"myid": 1, "name": "d1"}])
+
+        stmt = stmt.values(
+            [{"myid": 2, "name": "d2"}, {"myid": 3, "name": "d3"}]
+        )
+
+        self.assert_compile(
+            stmt,
+            "INSERT INTO mytable (myid, name) VALUES (%(myid_m0)s, "
+            "%(name_m0)s), (%(myid_m1)s, %(name_m1)s), (%(myid_m2)s, "
+            "%(name_m2)s)",
+            checkparams={
+                "myid_m0": 1,
+                "name_m0": "d1",
+                "myid_m1": 2,
+                "name_m1": "d2",
+                "myid_m2": 3,
+                "name_m2": "d3",
+            },
+            dialect=postgresql.dialect(),
+        )
+
     def test_inline_default(self):
         metadata = MetaData()
         table = Table(
diff --git a/test/sql/test_update.py b/test/sql/test_update.py
index 78eecdf21..22c4b1743 100644
--- a/test/sql/test_update.py
+++ b/test/sql/test_update.py
@@ -148,7 +148,9 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL):
         u = update(
             table1,
             values={
-                table1.c.name: select([mt.c.name], mt.c.myid == table1.c.myid)
+                table1.c.name: select(
+                    [mt.c.name], mt.c.myid == table1.c.myid
+                ).scalar_subquery()
             },
         )
         self.assert_compile(
@@ -163,7 +165,9 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL):
         table2 = self.tables.myothertable
 
         # test against a regular constructed subquery
-        s = select([table2], table2.c.otherid == table1.c.myid)
+        s = select(
+            [table2], table2.c.otherid == table1.c.myid
+        ).scalar_subquery()
         u = update(table1, table1.c.name == "jack", values={table1.c.name: s})
         self.assert_compile(
             u,
@@ -630,6 +634,45 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             "mytable.name = :param_2 || mytable.name || :param_3",
         )
 
+    def test_update_ordered_parameters_multiple(self):
+        table1 = self.tables.mytable
+
+        stmt = update(table1)
+
+        stmt = stmt.ordered_values(("name", "somename"))
+
+        assert_raises_message(
+            exc.ArgumentError,
+            "This statement already has ordered values present",
+            stmt.ordered_values,
+            ("myid", 10),
+        )
+
+    def test_update_ordered_then_nonordered(self):
+        table1 = self.tables.mytable
+
+        stmt = table1.update().ordered_values(("myid", 1), ("name", "d1"))
+
+        assert_raises_message(
+            exc.ArgumentError,
+            "This statement already has ordered values present",
+            stmt.values,
+            {"myid": 2, "name": "d2"},
+        )
+
+    def test_update_no_multiple_parameters_allowed(self):
+        table1 = self.tables.mytable
+
+        stmt = table1.update().values(
+            [{"myid": 1, "name": "n1"}, {"myid": 2, "name": "n2"}]
+        )
+
+        assert_raises_message(
+            exc.InvalidRequestError,
+            "UPDATE construct does not support multiple parameter sets.",
+            stmt.compile,
+        )
+
     def test_update_ordered_parameters_fire_onupdate(self):
         table = self.tables.update_w_default