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

1 files changed, 44 insertions, 3 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"))