sql.true() does not work for IBM DB2

[chujie_89]

When using sql.true() in query filter to query data in db2. It parse the sql.true() to 'true', which is a string. However, if changed sql.true() to 'True', it works well.

E.g. the following code works well.

class TT(Base):
    __tablename__ = 'tt'

    id = Column(Integer, primary_key = True)
    btest = Column('btest', Boolean, default = True)

db2 = sqlalchemy.create_engine('ibm_db_sa://neutron:neutron@localhost:50000/ntrnovs?charset=utf8')
session = sessionmaker( autocommit = False, autoflush = False, bind = db2)()
print session.query(TT).filter(TT.btest==True).all()[0].btest

However, if I changed the 'True' to 'sql.true()', it raise the following errors.

[root@rhel62 ~]# python db2_select.py 
Traceback (most recent call last):
  File "db2_select.py", line 18, in <module>
    print session.query(TT).filter(TT.btest==sql.true()).all()[0].btest
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line 2115, in all
    return list(self)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line 2227, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py", line 2242, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/usr/lib/python2.6/site-packages/ibm_db_sa/ibm_db.py", line 104, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib64/python2.6/site-packages/ibm_db_dbi.py", line 1334, in execute
    self._set_cursor_helper()
  File "/usr/lib64/python2.6/site-packages/ibm_db_dbi.py", line 1217, in _set_cursor_helper
    raise self.messages[len(self.messages) - 1]
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0401N  The data types of the operands for the operation "=" are not compatible or comparable.  SQLSTATE=42818 SQLCODE=-401 'SELECT tt.id AS tt_id, tt.btest AS tt_btest \nFROM tt \nWHERE tt.btest = true' ()

[root@rhel62 ~]# rpm -qa | grep sqlalchemy python-sqlalchemy-0.7.9-1

[Mike Bayer]

this is downstream. Right at this part of ibm_db_sa: https://code.google.com/p/ibm-db/source/browse/ibm_db_sa/ibm_db_sa/base.py?repo=ibm-db-sa#612 they need to add:

supports_native_boolean = False

this issue should be reported on the IBM db2 side.

[chujie_89]

Thanks, I'll report an issue to the IBM db2 side. :-)

[Former user]

This fails on sqlalchemy 0.8.4 also, even after patching DB2Dialect with supports_native_boolean = False. And actually that's the default value in sqlalchemy.engine.default.DefaultDialect anyway, which DB2Dialect extends, so I'm not sure how that would fix it since it should be the default.

The true() expression works OK on 0.9.6 but I'm assuming that's related to these changes in 0.9:

http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#improved-rendering-of-boolean-constants-null-constants-conjunctions

http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#none-can-no-longer-be-used-as-a-partial-and-constructor

Which says:

Another variant that works on all backends on 0.9, but on 0.8 only works on backends that support boolean constants:

from sqlalchemy.sql import true

condition = true()

for cond in conditions: condition = cond & condition

stmt = stmt.where(condition)

On 0.8, this will produce a SELECT statement that always has AND true in the WHERE clause, which is not accepted by backends that don’t support boolean constants (MySQL, MSSQL). On 0.9, the true constant will be dropped within an and_() conjunction.

OpenStack is gating on sqlalchemy 0.9.6 now so that's why these changes work:

https://review.openstack.org/#/c/86494/

Actually back when that change was merged it was gating on sqlalchemy 0.9.4, but that still has the changes from 0.9 so it works:

http://logs.openstack.org/94/86494/2/check/check-tempest-dsvm-neutron/2f9d2b7/logs/pip-freeze.txt.gz

But fails with 0.8.4, which from the 0.9 change log sounds like that's expected since it's not supported for backends that don't support native boolean, if I'm understanding this correctly.