Use full column->type processing for ON CONFLICT SET clause

Fixed bug in new "ON CONFLICT DO UPDATE" feature where the "set"
values for the UPDATE clause would not be subject to type-level
processing, as normally takes effect to handle both user-defined
type level conversions as well as dialect-required conversions, such
as those required for JSON datatypes.   Additionally, clarified that
the keys in the set_ dictionary should match the "key" of the column,
if distinct from the column name.  A warning is emitted
for remaining column names that don't match column keys; for
compatibility reasons, these are emitted as they were previously.

Fixes: #3888
Change-Id: I67a04c67aa5f65e6d29f27bf3ef2f8257088d073

2 files changed, 84 insertions, 4 deletions

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 3e7f584bf..99706bad8 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1,12 +1,12 @@
 # coding: utf-8
 
 from sqlalchemy.testing.assertions import AssertsCompiledSQL, is_, \
-    assert_raises, assert_raises_message
+    assert_raises, assert_raises_message, expect_warnings
 from sqlalchemy.testing import engines, fixtures
 from sqlalchemy import testing
 from sqlalchemy import Sequence, Table, Column, Integer, update, String,\
     func, MetaData, Enum, Index, and_, delete, select, cast, text, \
-    Text
+    Text, null
 from sqlalchemy.dialects.postgresql import ExcludeConstraint, array
 from sqlalchemy import exc, schema
 from sqlalchemy.dialects import postgresql
@@ -1089,7 +1089,7 @@ class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL):
             "(%(name)s) ON CONFLICT (myid) DO NOTHING"
         )
 
-    def test_do_update_set_clause_literal(self):
+    def test_do_update_set_clause_none(self):
         i = insert(self.table_with_metadata).values(myid=1, name='foo')
         i = i.on_conflict_do_update(
             index_elements=['myid'],
@@ -1102,6 +1102,25 @@ class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL):
             'INSERT INTO mytable (myid, name) VALUES '
             '(%(myid)s, %(name)s) ON CONFLICT (myid) '
             'DO UPDATE SET name = %(param_1)s, '
+            'description = %(param_2)s',
+            {"myid": 1, "name": "foo",
+             "param_1": "I'm a name", "param_2": None}
+
+        )
+
+    def test_do_update_set_clause_literal(self):
+        i = insert(self.table_with_metadata).values(myid=1, name='foo')
+        i = i.on_conflict_do_update(
+            index_elements=['myid'],
+            set_=OrderedDict([
+                ('name', "I'm a name"),
+                ('description', null())])
+        )
+        self.assert_compile(
+            i,
+            'INSERT INTO mytable (myid, name) VALUES '
+            '(%(myid)s, %(name)s) ON CONFLICT (myid) '
+            'DO UPDATE SET name = %(param_1)s, '
             'description = NULL',
             {"myid": 1, "name": "foo", "param_1": "I'm a name"}
 
@@ -1296,6 +1315,28 @@ class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL):
                             'DO UPDATE SET name = excluded.name '
                             "WHERE mytable.name != excluded.name")
 
+    def test_do_update_additional_colnames(self):
+        i = insert(
+            self.table1, values=dict(name='bar'))
+        i = i.on_conflict_do_update(
+            constraint=self.excl_constr_anon,
+            set_=dict(name='somename', unknown='unknown')
+        )
+        with expect_warnings(
+                "Additional column names not matching any "
+                "column keys in table 'mytable': 'unknown'"):
+            self.assert_compile(i,
+                                'INSERT INTO mytable (name) VALUES '
+                                "(%(name)s) ON CONFLICT (name, description) "
+                                "WHERE description != %(description_1)s "
+                                "DO UPDATE SET name = %(param_1)s, "
+                                "unknown = %(param_2)s",
+                                checkparams={
+                                    "name": "bar",
+                                    "description_1": "foo",
+                                    "param_1": "somename",
+                                    "param_2": "unknown"})
+
     def test_quote_raw_string_col(self):
         t = table('t', column("FancyName"), column("other name"))
 
diff --git a/test/dialect/postgresql/test_on_conflict.py b/test/dialect/postgresql/test_on_conflict.py
index 9cfe4432a..0e1dea06a 100644
--- a/test/dialect/postgresql/test_on_conflict.py
+++ b/test/dialect/postgresql/test_on_conflict.py
@@ -4,7 +4,7 @@ from sqlalchemy.testing.assertions import eq_, assert_raises
 from sqlalchemy.testing import fixtures
 from sqlalchemy import testing
 from sqlalchemy import Table, Column, Integer, String
-from sqlalchemy import exc, schema
+from sqlalchemy import exc, schema, types as sqltypes, sql
 from sqlalchemy.dialects.postgresql import insert
 
 
@@ -21,6 +21,18 @@ class OnConflictTest(fixtures.TablesTest):
             Column('name', String(50))
         )
 
+        class SpecialType(sqltypes.TypeDecorator):
+            impl = String
+
+            def process_bind_param(self, value, dialect):
+                return value + " processed"
+
+        Table(
+            'bind_targets', metadata,
+            Column('id', Integer, primary_key=True),
+            Column('data', SpecialType())
+        )
+
         users_xtra = Table(
             'users_xtra', metadata,
             Column('id', Integer, primary_key=True),
@@ -473,3 +485,30 @@ class OnConflictTest(fixtures.TablesTest):
                     (2, 'name2', 'name2@gmail.com', 'not')
                 ]
             )
+
+    def test_on_conflict_do_update_special_types_in_set(self):
+        bind_targets = self.tables.bind_targets
+
+        with testing.db.connect() as conn:
+            i = insert(bind_targets)
+            conn.execute(i, {"id": 1, "data": "initial data"})
+
+            eq_(
+                conn.scalar(sql.select([bind_targets.c.data])),
+                "initial data processed"
+            )
+
+            i = insert(bind_targets)
+            i = i.on_conflict_do_update(
+                index_elements=[bind_targets.c.id],
+                set_=dict(data="new updated data")
+            )
+            conn.execute(
+                i, {"id": 1, "data": "new inserted data"}
+            )
+
+            eq_(
+                conn.scalar(sql.select([bind_targets.c.data])),
+                "new updated data processed"
+            )
+