Preserve order in update method

In some DBs the UPDATE operation is order dependent, so the operation
behaves differently depending on the order of the values.

As an example, imagine a volumes table with columns 'status' and
'previous_status' and we want to update a volume that has 'available' in
the status column.

If the SQL query is performed as:

 UPDATE volumes SET previous_status=status, status='new' WHERE id=1;

This will result in a volume with 'new' status and 'available'
previous_status both on SQLite and MariaDB, but if we reverse the
columns:

 UPDATE volumes SET status='new', previous_status=status WHERE id=1;

We will get the same result in SQLite but will result in a volume with
status and previous_status set to 'new' in MariaDB, which is not what we
want.

So order must be taken into consideration in some cases and it should be
allowed to ve specified via the Query update method or the values method
of an update.

This patch fixes this issue by preserving the order of parameters in
updates and allowing to receive not only dictionaries in update and
values but also ordered dictionaries and list/tuples of value pairs
(like dict and OrderedDict do).

fixes #3541

1 files changed, 18 insertions, 8 deletions

diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py
index e6f16b698..614f9413b 100644
--- a/lib/sqlalchemy/sql/crud.py
+++ b/lib/sqlalchemy/sql/crud.py
@@ -26,7 +26,7 @@ values present.
 """)
 
 
-def _get_crud_params(compiler, stmt, **kw):
+def _get_crud_params(compiler, stmt, keep_order=False, **kw):
     """create a set of tuples representing column/string pairs for use
     in an INSERT or UPDATE statement.
 
@@ -64,12 +64,12 @@ def _get_crud_params(compiler, stmt, **kw):
     # if we have statement parameters - set defaults in the
     # compiled params
     if compiler.column_keys is None:
-        parameters = {}
+        parameters = util.OrderedDict()
     else:
-        parameters = dict((_column_as_key(key), REQUIRED)
-                          for key in compiler.column_keys
-                          if not stmt_parameters or
-                          key not in stmt_parameters)
+        parameters = util.OrderedDict((_column_as_key(key), REQUIRED)
+                                      for key in compiler.column_keys
+                                      if not stmt_parameters or
+                                      key not in stmt_parameters)
 
     # create a list of column assignment clauses as tuples
     values = []
@@ -97,7 +97,7 @@ def _get_crud_params(compiler, stmt, **kw):
         _scan_cols(
             compiler, stmt, parameters,
             _getattr_col_key, _column_as_key,
-            _col_bind_name, check_columns, values, kw)
+            _col_bind_name, check_columns, values, kw, keep_order=keep_order)
 
     if parameters and stmt_parameters:
         check = set(parameters).intersection(
@@ -202,7 +202,7 @@ def _scan_insert_from_select_cols(
 
 def _scan_cols(
     compiler, stmt, parameters, _getattr_col_key,
-        _column_as_key, _col_bind_name, check_columns, values, kw):
+        _column_as_key, _col_bind_name, check_columns, values, kw, keep_order):
 
     need_pks, implicit_returning, \
         implicit_return_defaults, postfetch_lastrowid = \
@@ -210,6 +210,16 @@ def _scan_cols(
 
     cols = stmt.table.columns
 
+    if keep_order:
+        # Order columns with parameters first, preserving their original order,
+        # and then the rest of the columns
+        keys = tuple(parameters.keys()) if parameters else tuple()
+        table_cols = tuple(cols)
+        cols = sorted(table_cols,
+                      key=(lambda x: keys.index(_getattr_col_key(x))
+                           if _getattr_col_key(x) in keys
+                           else len(keys) + table_cols.index(x)))
+
     for c in cols:
         col_key = _getattr_col_key(c)
         if col_key in parameters and col_key not in check_columns: