Issues SQL transaction with QueryBuilder

[mkotiuga]

When using the QueryBuilder (with AiiDA version 1.0.0b6)

When a field with the incorrect type is passed through filter an SQL error is thrown (no problem here). But any subsequent SQL query throws an error. Example: First a successful query, then one were a string is passed when an integer is expected (causing an error). Finally, a second run at the first successful query, now throws an error.

Input:
from aiida.orm import QueryBuilder
qb = QueryBuilder()
qb.append(Node, project=['id'], filters={'label': {'==': 'HFO test'}})
qb.all()
qb = QueryBuilder()
qb.append(Node, project=['id'], filters={'user_id': {'==': 'name'}})
qb.all()
qb = QueryBuilder()
qb.append(Node, project=['id'], filters={'label': {'==': 'HFO test'}})
qb.all()

I/O in verdi shell:

In [1]: from aiida.orm import QueryBuilder
ln [2]: qb = QueryBuilder()
   ...: qb.append(Node, project=['id'], filters={'label': {'==': 'HFO test'}})
   ...: qb.all()
Out[2]: [[2146], [2145], [2134], [2144], [2143], [2120], [2112], [2108], [2128]]
In [3]: qb = QueryBuilder()
   ...: qb.append(Node, project=['id'], filters={'user_id': {'==': 'name'}})
   ...: qb.all()
---------------------------------------------------------------------------
InvalidTextRepresentation                 Traceback (most recent call last)
/home/mkotiuga/.virtualenvs/aiida/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1243                     self.dialect.do_execute(
-> 1244                         cursor, statement, parameters, context
   1245                     )

/home/mkotiuga/.virtualenvs/aiida/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    551     def do_execute(self, cursor, statement, parameters, context=None):
--> 552         cursor.execute(statement, parameters)
    553 

InvalidTextRepresentation: invalid input syntax for integer: "name"
LINE 3: ...e_type AS VARCHAR) LIKE '%' AND db_dbnode_1.user_id = 'name'
--------------------------------------------------------
%%%%%%%%%VERY LONG ERROR CONTINUES%%%%%%%
---------------------------------------------------------
DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for integer: "name"
LINE 3: ...e_type AS VARCHAR) LIKE '%' AND db_dbnode_1.user_id = 'name'
                                                                 ^

[SQL: SELECT db_dbnode_1.id AS db_dbnode_1_id 
FROM db_dbnode AS db_dbnode_1 
WHERE CAST(db_dbnode_1.node_type AS VARCHAR) LIKE %(param_1)s AND db_dbnode_1.user_id = %(user_id_1)s]
[parameters: {'param_1': '%', 'user_id_1': 'name'}]

(Background on this error at: http://sqlalche.me/e/9h9h)
In [4]: qb = QueryBuilder()
   ...: qb.append(Node, project=['id'], filters={'label': {'==': 'HFO test'}})
   ...: qb.all()
____________________
%%%%%%%AGAIN VERY LONG ERROR - ending with%%%%%
__________________________
InternalError: (psycopg2.errors.InFailedSqlTransaction) current transaction is aborted, commands ignored until end of transaction block

[SQL: SELECT db_dbnode_1.id AS db_dbnode_1_id 
FROM db_dbnode AS db_dbnode_1 
WHERE CAST(db_dbnode_1.node_type AS VARCHAR) LIKE %(param_1)s AND db_dbnode_1.label = %(label_1)s]
[parameters: {'param_1': '%', 'label_1': 'HFO test'}]
(Background on this error at: http://sqlalche.me/e/2j85)

[ramirezfranciscof]

Hi @mkotiuga , thanks for the report! I think this buggy behavior has already been identified as part of issue #3230 (although for a different 'initial error'), is this correct @yakutovicha ?