Fixed bug when using multi-table UPDATE where a supplemental

table is a SELECT with its own bound parameters, where the positioning
of the bound parameters would be reversed versus the statement
itself when using MySQL's special syntax.
[ticket:2768]

4 files changed, 59 insertions, 2 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index f4dd923d6..29da708fc 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -7,6 +7,15 @@
     :version: 0.8.2
 
     .. change::
+        :tags: bug, mysql
+        :tickets: 2768
+
+        Fixed bug when using multi-table UPDATE where a supplemental
+        table is a SELECT with its own bound parameters, where the positioning
+        of the bound parameters would be reversed versus the statement
+        itself when using MySQL's special syntax.
+
+    .. change::
         :tags: bug, sqlite
         :tickets: 2764
 
diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index ba39a9175..2b33a5cb1 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -7,6 +7,15 @@
     :version: 0.9.0
 
     .. change::
+        :tags: bug, mysql
+        :tickets: 2768
+
+        Fixed bug when using multi-table UPDATE where a supplemental
+        table is a SELECT with its own bound parameters, where the positioning
+        of the bound parameters would be reversed versus the statement
+        itself when using MySQL's special syntax.  Also in 0.8.2.
+
+    .. change::
         :tags: bug, sqlite
         :tickets: 2764
 
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 0afcdfaec..7770c7fc7 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1606,8 +1606,6 @@ class SQLCompiler(engine.Compiled):
 
         extra_froms = update_stmt._extra_froms
 
-        colparams = self._get_colparams(update_stmt, extra_froms)
-
         text = "UPDATE "
 
         if update_stmt._prefixes:
@@ -1617,6 +1615,8 @@ class SQLCompiler(engine.Compiled):
         table_text = self.update_tables_clause(update_stmt, update_stmt.table,
                                                extra_froms, **kw)
 
+        colparams = self._get_colparams(update_stmt, extra_froms)
+
         if update_stmt._hints:
             dialect_hints = dict([
                 (table, hint_text)
diff --git a/test/sql/test_update.py b/test/sql/test_update.py
index 8695760fb..a8510f374 100644
--- a/test/sql/test_update.py
+++ b/test/sql/test_update.py
@@ -1,6 +1,7 @@
 from sqlalchemy import *
 from sqlalchemy import testing
 from sqlalchemy.dialects import mysql
+from sqlalchemy.engine import default
 from sqlalchemy.testing import AssertsCompiledSQL, eq_, fixtures
 from sqlalchemy.testing.schema import Table, Column
 
@@ -222,6 +223,44 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL):
         self.assert_compile(table1.update().values({expr: 'bar'}),
             'UPDATE mytable SET foo(myid)=:param_1')
 
+    def test_update_bound_ordering(self):
+        """test that bound parameters between the UPDATE and FROM clauses
+        order correctly in different SQL compilation scenarios.
+
+        """
+        table1 = self.tables.mytable
+        table2 = self.tables.myothertable
+        sel = select([table2]).where(table2.c.otherid == 5).alias()
+        upd = table1.update().\
+                    where(table1.c.name == sel.c.othername).\
+                    values(name='foo')
+
+        dialect = default.DefaultDialect()
+        dialect.positional = True
+        self.assert_compile(
+            upd,
+            "UPDATE mytable SET name=:name FROM (SELECT "
+            "myothertable.otherid AS otherid, "
+            "myothertable.othername AS othername "
+            "FROM myothertable "
+            "WHERE myothertable.otherid = :otherid_1) AS anon_1 "
+            "WHERE mytable.name = anon_1.othername",
+            checkpositional=('foo', 5),
+            dialect=dialect
+        )
+
+        self.assert_compile(
+            upd,
+            "UPDATE mytable, (SELECT myothertable.otherid AS otherid, "
+            "myothertable.othername AS othername "
+            "FROM myothertable "
+            "WHERE myothertable.otherid = %s) AS anon_1 SET mytable.name=%s "
+            "WHERE mytable.name = anon_1.othername",
+            checkpositional=(5, 'foo'),
+            dialect=mysql.dialect()
+        )
+
+
 
 class UpdateFromCompileTest(_UpdateFromTestBase, fixtures.TablesTest,
                             AssertsCompiledSQL):