Add a qualifying character to multi INSERT..VALUES parameter names

Changed the naming convention used when generating bound parameters
for a multi-VALUES insert statement, so that the numbered parameter
names don't conflict with the anonymized parameters of a WHERE clause,
as is now common in a PostgreSQL ON CONFLICT construct.

Change-Id: I3188d100fe4d322a47d344d6a63d3e40b915f228
Fixes: #3828

2 files changed, 56 insertions, 0 deletions

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index ac8bb4815..52dd699fc 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1208,6 +1208,28 @@ class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL):
                             "WHERE mytable.name > %(name_1)s "
                             'DO UPDATE SET name = excluded.name')
 
+    def test_do_update_index_elements_where_target_multivalues(self):
+        i = insert(
+            self.table1,
+            values=[dict(name='foo'), dict(name='bar'), dict(name='bat')])
+        i = i.on_conflict_do_update(
+            index_elements=self.goofy_index.expressions,
+            index_where=self.goofy_index.dialect_options[
+                'postgresql']['where'],
+            set_=dict(name=i.excluded.name)
+        )
+        self.assert_compile(
+            i,
+            "INSERT INTO mytable (name) "
+            "VALUES (%(name_m0)s), (%(name_m1)s), (%(name_m2)s) "
+            "ON CONFLICT (name) "
+            "WHERE mytable.name > %(name_1)s "
+            "DO UPDATE SET name = excluded.name",
+            checkparams={
+                'name_1': 'm', 'name_m0': 'foo',
+                'name_m1': 'bar', 'name_m2': 'bat'}
+        )
+
     def test_do_update_unnamed_index_target(self):
         i = insert(
             self.table1, values=dict(name='foo'))
diff --git a/test/dialect/postgresql/test_on_conflict.py b/test/dialect/postgresql/test_on_conflict.py
index eb0298bc2..154d3fe58 100644
--- a/test/dialect/postgresql/test_on_conflict.py
+++ b/test/dialect/postgresql/test_on_conflict.py
@@ -210,6 +210,40 @@ class OnConflictTest(fixtures.TablesTest):
                 [(10, "I'm a name")]
             )
 
+    def test_on_conflict_do_update_multivalues(self):
+        users = self.tables.users
+
+        with testing.db.connect() as conn:
+            conn.execute(users.insert(), dict(id=1, name='name1'))
+            conn.execute(users.insert(), dict(id=2, name='name2'))
+
+            i = insert(users)
+            i = i.on_conflict_do_update(
+                index_elements=users.primary_key.columns,
+                set_=dict(name="updated"),
+                where=(i.excluded.name != 'name12')
+            ).values([
+                dict(id=1, name='name11'),
+                dict(id=2, name='name12'),
+                dict(id=3, name='name13'),
+                dict(id=4, name='name14'),
+            ])
+
+            result = conn.execute(i)
+            eq_(result.inserted_primary_key, [None])
+            eq_(result.returned_defaults, None)
+
+            eq_(
+                conn.execute(
+                    users.select().order_by(users.c.id)).fetchall(),
+                [
+                    (1, "updated"),
+                    (2, "name2"),
+                    (3, "name13"),
+                    (4, "name14")
+                ]
+            )
+
     def _exotic_targets_fixture(self, conn):
         users = self.tables.users_xtra