Issue: SQLError: row value misused after upgrade
Author: brandond
State: closed
Created: 2019-05-03 20:00:44

I recently updated to 3.9.5 and I'm now experiencing the following errors with a query that was previously working:

```
[DEBUG] 2019-05-03T19:05:14.901Z        1eeeb36c-0a1e-44ec-91b8-ddd09d60ed4e    ('SELECT COUNT(1) FROM (SELECT DISTINCT ("t1"."ip_address", "t1"."port", "t1"."protocol", "t1"."plugin_id", "t1"."first_seen") FROM "vulnerability" AS "t1" WHERE ((("t1"."remediation_group_id" = ?) AND ("t1"."plugin_id" = ?)) AND ("t1"."guid" = ?))) AS "_wrapped"', [764, 103569, u'AGGAA5V0FSFJTAOW8FUSBXO8AGPWPK'])
[ERROR] 2019-05-03T19:05:14.902Z        1eeeb36c-0a1e-44ec-91b8-ddd09d60ed4e    Unable to update plugin ticket INC000008958030
Traceback (most recent call last):
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/ticketlib/remediation.py", line 466, in update_plugin_ticket
    host_count = hosts.count()
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 1785, in inner
    return method(self, database, *args, **kwargs)
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 2061, in count
    return Select([clone], [fn.COUNT(SQL('1'))]).scalar(database)
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 1785, in inner
    return method(self, database, *args, **kwargs)
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 2046, in scalar
    row = self.tuples().peek(database)
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 1785, in inner
    return method(self, database, *args, **kwargs)
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 2033, in peek
    rows = self.execute(database)[:n]
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 1785, in inner
    return method(self, database, *args, **kwargs)
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 1856, in execute
    return self._execute(database)
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 2027, in _execute
    cursor = database.execute(self)
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/peewee.py", line 2953, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/ec2-user/ansible-env/lib/python2.7/site-packages/playhouse/apsw_ext.py", line 112, in execute_sql
    cursor.execute(sql, params or ())
  File "src/cursor.c", line 1019, in APSWCursor_execute.sqlite3_prepare
  File "src/statementcache.c", line 386, in sqlite3_prepare
SQLError: SQLError: row value misused
``` 

Try rather than using distinct as a function, to append a call to .distinct() at the end of the query.

If I go back to 3.6.4 and rerun the exact same sequence, it looks like 3.9.5 is putting parentheses around the column list that weren't there before:

```
3.9.5: ('SELECT COUNT(1) FROM (SELECT DISTINCT ("t1"."ip_address", "t1"."port", "t1"."protocol", "t1"."plugin_id", "t1"."first_seen") FROM "vulnerability" AS "t1" WHERE ((("t1"."remediation_group_id" = ?) AND ("t1"."plugin_id" = ?)) AND ("t1"."guid" = ?))) AS "_wrapped"', [764, 22024, u'AGGAA5V0FSFJTAOW8FUSBXO8AGPWPK'])
3.6.4: ('SELECT COUNT(1) FROM (SELECT DISTINCT "t1"."ip_address", "t1"."port", "t1"."protocol", "t1"."plugin_id", "t1"."first_seen" FROM "vulnerability" AS "t1" WHERE ((("t1"."remediation_group_id" = ?) AND ("t1"."plugin_id" = ?)) AND ("t1"."guid" = ?))) AS "_wrapped"', [764, 22024, u'AGGAA5V0FSFJTAOW8FUSBXO8AGPWPK'])
```

I'm using this with sqlite - apsw 3.24.0.post1

There's some logic outside this block that's preparing a query for self.vulnerabilties, but I am calling distinct() at the end of the query rather than as a function.

```
vulns = (self.vulnerabilities
             .where(Vulnerability.plugin_id == plugin_id)
             .where(Vulnerability.guid == template_guid)
             .switch(Vulnerability))
hosts = (vulns.select(Vulnerability.ip_address, Vulnerability.dns_name)
              .distinct())
vuln_count = vulns.count()
```


Can you share what `self.vulnerabilities` is?

I'm wondering if you're re-selecting or somehow selecting a tuple instead of a list of columns?

In your example code you have `hosts = ...` but that doesn't appear to be in any way relevant to the error, right? I'm just not understanding why you included it.

For example, I'm trying to replicate this and cannot:

```python
class RD(TestModel):
    key = TextField()
    value = IntegerField()

nums = [0, 1, 2, 3, 2, 1, 0]
RD.insert_many([('k%s' % i, i) for i in nums]).execute()
assert RD.select(RD.key).distinct().count() == 4  # Works
```

This came up during regression testing and may possibly be relevant, 2692e781da2ebaa33ae72098b18122d3b2d46a30 -- but that being said, I cannot replicate the invalid SQL you are reporting.

Please provide more information or at least an example of how I can replicate this issue.

Sorry, I was trying to trim out some extra bits of non-peewee code and omitted the wrong bit. The full block is:

```
vulns = (self.vulnerabilities
             .where(Vulnerability.plugin_id == plugin_id)
             .where(Vulnerability.guid == template_guid)
             .switch(Vulnerability))
hosts = (vulns.select(Vulnerability.ip_address, Vulnerability.dns_name)
              .distinct())
vuln_count = vulns.count()
host_count = hosts.count()  # <-- this causes the exception
first_vuln = vulns.get()
plugin_obj = Plugin.get(id=plugin_id)
```

This is actually in a class method with a bunch of business logic in it that subclasses one of my Model classes, so self.vulnerabilities is a query against another table via foreign key backreference.

I'll try with that commit, and if that does not fix it I'll see if I can easily bisect and/or replicate.

2692e78 fixes this issue. Thanks!

FWIW, a bisect showed ff321c514c06858565635279367ecc891852047d as the breaking commit.

Thanks so much for bisecting, very helpful. I'll look into that some more, but glad the immediate issue is now resolved.