querybuilder chokes on empty filters

[ltalirz]

Passing a completely empty filter dictionary works:

In [5]: qb=QueryBuilder()

In [6]: qb.append(Node, filters={  })
Out[6]: <aiida.orm.querybuilder.QueryBuilder at 0x1047f74d0>

In [7]: qb.count()
Out[7]: 1L

However, e.g. an 'or' of empty filter dictionaries doesn't work.

In [1]: qb=QueryBuilder()

In [2]: qb.append(Node, filters={ 'or': [ {}, {} ]})
Out[2]: <aiida.orm.querybuilder.QueryBuilder at 0x1041e6950>

In [3]: qb.count()
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-3-5de0c881f933> in <module>()
----> 1 qb.count()

/Users/leopold/Personal/Postdoc-MARVEL/repos/aiida/aiida_rmq/aiida/orm/querybuilder.py in count(self)
   2129         """
   2130         query = self.get_query()
-> 2131         return self._impl.count(query)
   2132
   2133     def iterall(self, batch_size=100):

/Users/leopold/Personal/Postdoc-MARVEL/repos/aiida/aiida_rmq/aiida/orm/implementation/django/querybuilder.pyc in count(self, query)
    427         from django.db import transaction
    428         with transaction.atomic():
--> 429             return query.count()
    430
    431     def first(self, query):

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in count(self)
   3228         """
   3229         col = sql.func.count(sql.literal_column('*'))
-> 3230         return self.from_self(col).scalar()
   3231
   3232     def delete(self, synchronize_session='evaluate'):

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in scalar(self)
   2981         """
   2982         try:
-> 2983             ret = self.one()
   2984             if not isinstance(ret, tuple):
   2985                 return ret

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in one(self)
   2952         """
   2953         try:
-> 2954             ret = self.one_or_none()
   2955         except orm_exc.MultipleResultsFound:
   2956             raise orm_exc.MultipleResultsFound(

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in one_or_none(self)
   2922
   2923         """
-> 2924         ret = list(self)
   2925
   2926         l = len(ret)

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in __iter__(self)
   2993         if self._autoflush and not self._populate_existing:
   2994             self.session._autoflush()
-> 2995         return self._execute_and_instances(context)
   2996
   2997     def __str__(self):

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc in _execute_and_instances(self, querycontext)
   3016             close_with_result=True)
   3017
-> 3018         result = conn.execute(querycontext.statement, self._params)
   3019         return loading.instances(querycontext.query, result, querycontext)
   3020

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    946             raise exc.ObjectNotExecutableError(object)
    947         else:
--> 948             return meth(self, multiparams, params)
    949
    950     def _execute_function(self, func, multiparams, params):

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc in _execute_on_connection(self, connection, multiparams, params)
    267     def _execute_on_connection(self, connection, multiparams, params):
    268         if self.supports_execution:
--> 269             return connection._execute_clauseelement(self, multiparams, params)
    270         else:
    271             raise exc.ObjectNotExecutableError(self)

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_clauseelement(self, elem, multiparams, params)
   1058             compiled_sql,
   1059             distilled_params,
-> 1060             compiled_sql, distilled_params
   1061         )
   1062         if self._has_events or self.engine._has_events:

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1198                 parameters,
   1199                 cursor,
-> 1200                 context)
   1201
   1202         if self._has_events or self.engine._has_events:

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1411                 util.raise_from_cause(
   1412                     sqlalchemy_exception,
-> 1413                     exc_info
   1414                 )
   1415             else:

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    263     exc_type, exc_value, exc_tb = exc_info
    264     cause = exc_value if exc_value is not exception else None
--> 265     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    266
    267 if py3k:

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1191                         statement,
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:
   1195             self._handle_dbapi_exception(

/Users/leopold/Applications/miniconda3/envs/aiida_rmq/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
    507
    508     def do_execute(self, cursor, statement, parameters, context=None):
--> 509         cursor.execute(statement, parameters)
    510
    511     def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near ")"
LINE 4: ...E CAST(db_dbnode_1.type AS VARCHAR) LIKE '%' AND ()) AS anon...
                                                             ^
 [SQL: 'SELECT count(*) AS count_1 \nFROM (SELECT db_dbnode_1.id AS db_dbnode_1_id, db_dbnode_1.uuid AS db_dbnode_1_uuid, db_dbnode_1.type AS db_dbnode_1_type, db_dbnode_1.process_type AS db_dbnode_1_process_type, db_dbnode_1.label AS db_dbnode_1_label, db_dbnode_1.description AS db_dbnode_1_description, db_dbnode_1.ctime AS db_dbnode_1_ctime, db_dbnode_1.mtime AS db_dbnode_1_mtime, db_dbnode_1.dbcomputer_id AS db_dbnode_1_dbcomputer_id, db_dbnode_1.user_id AS db_dbnode_1_user_id, db_dbnode_1.public AS db_dbnode_1_public, db_dbnode_1.nodeversion AS db_dbnode_1_nodeversion \nFROM db_dbnode AS db_dbnode_1 \nWHERE CAST(db_dbnode_1.type AS VARCHAR) LIKE %(param_1)s AND ()) AS anon_1'] [parameters: {'param_1': '%'}] (Background on this error at: http://sqlalche.me/e/f405)

This means one currently cannot use '{}' to mean "no filter", which would be nice to have for #2400.

[ltalirz]

Cheers!

[chrisjsewell]

👍