[Sqlalchemy-tickets] Issue #3846: ON CONFLICT index_where (index_predicate) ... missing FROM-clause entry for table.... (zzzeek/sqlalchemy)

[Pawel <iss...@bi...>]

New issue 3846: ON CONFLICT index_where (index_predicate) ... missing FROM-clause entry for table....
https://bitbucket.org/zzzeek/sqlalchemy/issues/3846/on-conflict-index_where-index_predicate

Pawel:

PostgreSQL 9.5
SQLAlchemy 1.1.3

Hi, got this error and I'm pretty sure it didn't occur in version beta `1.1.b3` was it? I got a similar problem then and at first I thought it can be related to #3807. But after some digging I must admit I'm a bit puzzled by PostgreSQL behavior Lack of good ON CONFLICT examples with index_where (index_predicate) and partial indexes in PostgreSQL doc doesn't help. 

The problem seems to be that PostgreSQL doesn't allow any qualification in WHERE clause between ON CONFLICT and SET phrases (index_predicate in doc). 

So this is wrong:
```
#!SQL
insert into users_xtra AS u (name, login_email, lets_index_this) values ('bla', 'bli', 'ble')
ON CONFLICT (name, lets_index_this) where (user_xtra.lets_index_this = 'unique_name') do update
set name = excluded.name, lets_index_this = excluded.lets_index_this;
``` 

and gives an error:

```
#!SQL
ERROR:  missing FROM-clause entry for table "user_xtra"
LINE 2: ON CONFLICT (name, lets_index_this) where (user_xtra.lets_in...
                                                                                    ^
```

This executes without error:
```
#!SQL
insert into users_xtra AS u (name, login_email, lets_index_this) values ('bla', 'bli', 'ble')
ON CONFLICT (name, lets_index_this) where (lets_index_this = 'unique_name') do update
set name = excluded.name, lets_index_this = excluded.lets_index_this;
``` 

Here is the test for this behavior.

```
#!diff
diff --git a/test/dialect/postgresql/test_on_conflict.py b/test/dialect/postgresql/test_on_conflict.py
index 154d3fe..bee30c9 100644
--- a/test/dialect/postgresql/test_on_conflict.py
+++ b/test/dialect/postgresql/test_on_conflict.py
@@ -1,5 +1,5 @@
 # coding: utf-8
+from sqlalchemy import text
 from sqlalchemy.testing.assertions import eq_, assert_raises
 from sqlalchemy.testing import fixtures
 from sqlalchemy import testing
@@ -30,6 +30,11 @@ class OnConflictTest(fixtures.TablesTest):
         )
         cls.unique_constraint = schema.UniqueConstraint(
             users_xtra.c.login_email, name='uq_login_email')
+        cls.unique_partial_index = schema.Index(
+            'idx_unique_partial_name',
+            users_xtra.c.name, users_xtra.c.lets_index_this,
+            unique=True,
+            postgresql_where=users_xtra.c.lets_index_this == 'unique_name')
         cls.bogus_index = schema.Index(
             'idx_special_ops',
             users_xtra.c.lets_index_this,
@@ -405,6 +410,44 @@ class OnConflictTest(fixtures.TablesTest):
                     lets_index_this='bogus')
             )
 
+    def test_on_conflict_do_update_exotic_targets_six(self):
+        users = self.tables.users_xtra
+
+        with testing.db.connect() as conn:
+            # use partial index
+            i = insert(users)
+            i = i.on_conflict_do_update(
+                index_elements=self.unique_partial_index.columns,
+                # or
+                #index_elements=[users.c.name, users.c.lets_index_this],
+                index_where=self.unique_partial_index.dialect_options['postgresql']['where'],
+                # or
+                #index_where=users.c.lets_index_this == 'unique_name',
+                # but this passes!
+                #  vvv
+                #index_where=text("lets_index_this = 'unique_name'"),
+                set_=dict(
+                    name=i.excluded.name,
+                    login_email=i.excluded.login_email),
+            )
+
+            conn.execute(
+                i,
+                [
+                    dict(name='same_name', login_email='ma...@gm...',
+                         lets_index_this='unique_name'),
+                    dict(name='same_name', login_email='ma...@gm...',
+                         lets_index_this='unique_name')
+                ]
+            )
+
+            eq_(
+                conn.execute(users.select()).fetchall(),
+                [
+                    (2, 'same_name', 'ma...@gm...', 'unique_name'),
+                ]
+            )
+
     def test_on_conflict_do_update_no_row_actually_affected(self):
         users = self.tables.users_xtra
 
```

So if this in fact is a valid behavior than compiler tests `test_do_update_index_elements_where_target`,
`test_do_update_index_elements_where_target_multivalues` in file `/test/dialect/postgresql/test_on_conflict.py ` validate wrong results!