Using 'limit' with 'count' throws ProgrammingError

[Karel-van-de-Plassche]

I have a long SQL query that throws a ProgrammingError. I've isolated it to the following query:

(Network.select() & Network.select()).limit(10).count()

Throws:

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
~/working/peewee/peewee.py in execute_sql(self, sql, params, commit)
   2481             try:
-> 2482                 cursor.execute(sql, params or ())
   2483             except Exception:

ProgrammingError: subquery in FROM must have an alias
LINE 1: SELECT COUNT(1) FROM ((SELECT "t1"."id", "t1"."feature_names...
                             ^
HINT:  For example, FROM (SELECT ...) [AS] foo.


During handling of the above exception, another exception occurred:

ProgrammingError                          Traceback (most recent call last)
~/working/QLKNN-develop/qlknn/plots/slicer.py in <module>()
----> 1 (Network.select() & Network.select()).alias('foo').limit(10).count()

~/working/peewee/peewee.py in inner(self, database, *args, **kwargs)
   1443             raise Exception('Query must be bound to a database in order '
   1444                             'to call "%s".' % method.__name__)
-> 1445         return method(self, database, *args, **kwargs)
   1446     return inner
   1447 

~/working/peewee/peewee.py in count(self, database, clear_limit)
   1701         except AttributeError:
   1702             pass
-> 1703         return Select([clone], [fn.COUNT(SQL('1'))]).scalar(database)
   1704 
   1705     @database_required

~/working/peewee/peewee.py in inner(self, database, *args, **kwargs)
   1443             raise Exception('Query must be bound to a database in order '
   1444                             'to call "%s".' % method.__name__)
-> 1445         return method(self, database, *args, **kwargs)
   1446     return inner
   1447 

~/working/peewee/peewee.py in scalar(self, database, as_tuple)
   1687     @database_required
   1688     def scalar(self, database, as_tuple=False):
-> 1689         row = self.tuples().peek(database)
   1690         return row[0] if row and not as_tuple else row
   1691 

~/working/peewee/peewee.py in inner(self, database, *args, **kwargs)
   1443             raise Exception('Query must be bound to a database in order '
   1444                             'to call "%s".' % method.__name__)
-> 1445         return method(self, database, *args, **kwargs)
   1446     return inner
   1447 

~/working/peewee/peewee.py in peek(self, database, n)
   1674     @database_required
   1675     def peek(self, database, n=1):
-> 1676         rows = self.execute(database)[:n]
   1677         if rows:
   1678             return rows[0] if n == 1 else rows

~/working/peewee/peewee.py in inner(self, database, *args, **kwargs)
   1443             raise Exception('Query must be bound to a database in order '
   1444                             'to call "%s".' % method.__name__)
-> 1445         return method(self, database, *args, **kwargs)
   1446     return inner
   1447 

~/working/peewee/peewee.py in execute(self, database)
   1514     @database_required
   1515     def execute(self, database):
-> 1516         return self._execute(database)
   1517 
   1518     def _execute(self, database):

~/working/peewee/peewee.py in _execute(self, database)
   1668     def _execute(self, database):
   1669         if self._cursor_wrapper is None:
-> 1670             cursor = database.execute(self)
   1671             self._cursor_wrapper = self._get_cursor_wrapper(cursor)
   1672         return self._cursor_wrapper

~/working/peewee/playhouse/postgres_ext.py in execute(self, query, commit, named_cursor, array_size, **context_options)
    424         if named_cursor:
    425             commit = __named_cursor__
--> 426         cursor = self.execute_sql(sql, params, commit=commit)
    427         if named_cursor:
    428             cursor = FetchManyCursor(cursor, array_size)

~/working/peewee/peewee.py in execute_sql(self, sql, params, commit)
   2487             else:
   2488                 if commit and not self.in_transaction():
-> 2489                     self.commit()
   2490         return cursor
   2491 

~/working/peewee/peewee.py in __exit__(self, exc_type, exc_value, traceback)
   2283             new_type = self.exceptions[exc_type.__name__]
   2284             exc_args = exc_value if PY26 else exc_value.args
-> 2285             reraise(new_type, new_type(*exc_args), traceback)
   2286 
   2287 EXCEPTIONS = {

~/working/peewee/peewee.py in reraise(tp, value, tb)
    166     def reraise(tp, value, tb=None):
    167         if value.__traceback__ is not tb:
--> 168             raise value.with_traceback(tb)
    169         raise value
    170 

~/working/peewee/peewee.py in execute_sql(self, sql, params, commit)
   2480             cursor = self.cursor(commit)
   2481             try:
-> 2482                 cursor.execute(sql, params or ())
   2483             except Exception:
   2484                 if self.autorollback and not self.in_transaction():

ProgrammingError: subquery in FROM must have an alias
LINE 1: SELECT COUNT(1) FROM ((SELECT "t1"."id", "t1"."feature_names...
                             ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

Tested on peewee-3.0.18 and peewee-3.0.19.

PS. This works correctly:

len((Network.select() & Network.select()).limit(10))

Is len() the canonical way to view the amount of rows returned?

[coleifer]

The resulting query looks like this, which doesn't quite make sense:

SELECT COUNT(1) FROM (
  SELECT "t1"."id" FROM "whatever" AS "t1"
    INTERSECT 
  SELECT "t2"."id" FROM "whatever" AS "t2"
) LIMIT 3 AS "_wrapped"

Why are you applying a LIMIT and also using COUNT?

[coleifer]

Looks like this query works, however:

SELECT COUNT(1) FROM (
  SELECT "t1"."id" FROM "whatever" AS "t1"
    INTERSECT 
  SELECT "t2"."id" FROM "whatever" AS "t2"
  LIMIT 3
) AS "_wrapped"

[coleifer]

Fixed by 42d9642