Issue: bulk_update failed when using composite primary keys
Author: Raysmond
State: closed
Created: 2019-04-23 06:38:44

```python
class RawArticle(BaseModel):
    id = CharField()
    site_id = IntegerField(null=False)
    link_title = CharField(null=True)

    class Meta:
        table_name = 'dl_raw_article'
        primary_key = CompositeKey('site_id', 'id')
```

I'm using MySQL.


```python
articles = [a for a in RawArticle.select()]
print len(articles) #  I have 4 articles
articles[0].link_title = 'l1'
articles[1].link_title = 'l2'
articles[2].link_title = 'l3'
articles[3].link_title = 'l4'

with DB.atomic():
    RawArticle.bulk_update(articles, [RawArticle.link_title])
```

Here is the output

```
4
('UPDATE `dl_raw_article` SET `link_title` = CASE `dl_raw_article`.`site_id`, `dl_raw_article`.`id` WHEN (%s, %s) THEN %s WHEN (%s, %s) THEN %s WHEN (%s, %s) THEN %s WHEN (%s, %s) THEN %s END WHERE (`dl_raw_article`.`site_id`, `dl_raw_article`.`id` IN ((%s, %s), (%s, %s), (%s, %s), (%s, %s)))', [1, u'3eeda72c764090900bc35d72b1912a62', u'l1', 1, u'ae5503c96c81e717146d80a80a5aee76', u'l2', 1, u'f218ff642f1ae2a0f2eb0a956a3afb60', u'l3', 2, u'c0adb05d30afb8d4182795a86466f72e', u'l4', 1, u'3eeda72c764090900bc35d72b1912a62', 1, u'ae5503c96c81e717146d80a80a5aee76', 1, u'f218ff642f1ae2a0f2eb0a956a3afb60', 2, u'c0adb05d30afb8d4182795a86466f72e'])
2019-04-23 13:52:00,473:peewee:4451321280::90471::DEBUG: ('UPDATE `dl_raw_article` SET `link_title` = CASE `dl_raw_article`.`site_id`, `dl_raw_article`.`id` WHEN (%s, %s) THEN %s WHEN (%s, %s) THEN %s WHEN (%s, %s) THEN %s WHEN (%s, %s) THEN %s END WHERE (`dl_raw_article`.`site_id`, `dl_raw_article`.`id` IN ((%s, %s), (%s, %s), (%s, %s), (%s, %s)))', [1, u'3eeda72c764090900bc35d72b1912a62', u'l1', 1, u'ae5503c96c81e717146d80a80a5aee76', u'l2', 1, u'f218ff642f1ae2a0f2eb0a956a3afb60', u'l3', 2, u'c0adb05d30afb8d4182795a86466f72e', u'l4', 1, u'3eeda72c764090900bc35d72b1912a62', 1, u'ae5503c96c81e717146d80a80a5aee76', 1, u'f218ff642f1ae2a0f2eb0a956a3afb60', 2, u'c0adb05d30afb8d4182795a86466f72e'])
Traceback (most recent call last):
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/knowledge/domain/peewee_models.py", line 190, in <module>
    RawArticle.bulk_update(articles, [RawArticle.link_title])
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/peewee.py", line 5982, in bulk_update
    .where(cls._meta.primary_key.in_(id_list))
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/peewee.py", line 1778, in inner
    return method(self, database, *args, **kwargs)
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/peewee.py", line 1849, in execute
    return self._execute(database)
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/peewee.py", line 2316, in _execute
    cursor = database.execute(self)
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/peewee.py", line 2949, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/peewee.py", line 2943, in execute_sql
    self.commit()
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/peewee.py", line 2725, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/peewee.py", line 2936, in execute_sql
    cursor.execute(sql, params or ())
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/Users/raysmond/Baidu/Code/baidu/brand-ns/delphinus/venv/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
peewee.ProgrammingError: (1149, "syntax error! errno: 1 errmsg: syntax error, unexpected ',', in [0:74-74] key:,")
```

Obviously that the generate sql has syntax errors, since the part 

```sql
CASE `dl_raw_article`.`site_id`, `dl_raw_article`.`id` WHEN (%s, %s)
```
should be 

```sql
CASE (`dl_raw_article`.`site_id`, `dl_raw_article`.`id`) WHEN (%s, %s)
```

Thanks for reporting.

MySQL doesn't appear to support that syntax, as far as I can tell. [Docs](https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case) for 5.7. One would need to write:
```
CASE
  WHEN site_id = X1 AND id = Y1 THEN ...
  WHEN site_id = X2 AND id = Y2 THEN ...
END
```

Similarly, it doesn't appear to support tuple comparison in the WHERE clause.

Testing with a relatively recent version of Sqlite, I was able to get it working with minimal changes. Postgres looked to be roughly the same, but required explicit CASTs in places I wasn't expecting.

In short, I don't feel there's enough to be gained here by trying to support all these various edge-cases, so I've caused bulk_update() to raise an error if the model uses a composite pk.

Actually MySQL supports tuple comparison in the WHERE clause and the CASE clause you gave is right.

I just confirmed it in MySQL 5.7.  For example:

```sql
UPDATE `article` 
SET `title` = CASE 
	WHEN site_id = 1 and id = 'a1' THEN 'a11111'
	WHEN site_id = 1 and id = 'a2' THEN 'a22222'
END 
WHERE (`site_id`, `id`) IN ((1, 'a1'), (1, 'a2'))
```

This shall do the bulk_update work when using composite keys. By the way,  composite keys are widely using in our team, since we're using a distributed MySQL database. In this case, `site_id` is used to locate the node and `id` is the unique identifier. 

So do you think it's possible to support the syntax above?

Possibly, but as I was saying, Sqlite and Postgres both have their own quirks as well. Not to mention potential incompatibility in MySQL prior to 5.7?