Boolean handling in filter

[GoogleCodeExporter]

I think there's a problem where the DB2 dialect isn't handling Boolean values 
in queries properly.

This error shows up in the OpenStack Keystone server when configured to use 
DB2. Other dialects don't have a problem with this code.

The Keystone server does the following query:

        endpoints = (session.query(Endpoint).
                     options(sql.joinedload(Endpoint.service)).
                     filter(Endpoint.enabled == True).all())

Not surprisingly, the endpoint table has a Boolean column called enabled. The 
column type in the DB is SMALLINT, from describe table:

ENABLED                         SYSIBM    SMALLINT                     2     0 
No

What steps will reproduce the problem?
1. I think you could recreate this pretty easily by creating a table with a 
boolean column and then querying it using filter(Column == True).

What is the expected output? What do you see instead?

The exception that's generated includes the SQL. I'm not going to print the 
whole thing here because it's obvious where the prob is: "WHERE 
endpoint.enabled = true". Since enabled is a smallint it should be using "1" 
where "true" is.

What version of the product are you using? On what operating system?

 SQLAlchemy==0.8.4
 sqlalchemy-migrate==0.9.1
 ibm-db==2.0.5
 ibm-db-sa==0.3.1

Ubuntu Linux 12.04

Please provide any additional information below.

I'm no sqlalchemy expert but the other dialects implement visit_true that 
returns '1'. I haven't checked if these are actually called when running with 
mysql.

Original issue reported on code.google.com by BrantKnu...@gmail.com on 25 Sep 2014 at 4:02

[GoogleCodeExporter]

I think other projects in openstack are not using == True/False anymore (I 
think there is actually a hacking rule for this now), they should be checking 
against sqlalchemy.true() and false() (same with null() for == None).

Original comment by mattrie...@gmail.com on 25 Sep 2014 at 4:20

[GoogleCodeExporter]

I tried sqlalchemy.sql.true() here rather than True and the result was the 
same. There's other places where sqlalchemy.sql.expression.true() is used in 
this same part (as the server_default for some columns).

Original comment by BrantKnu...@gmail.com on 25 Sep 2014 at 10:14

[GoogleCodeExporter]

Here's the code in keystone: 
http://git.openstack.org/cgit/openstack/keystone/tree/keystone/catalog/backends/
sql.py?id=2fc25ff9bb2480d04acae60c24079324d4abe3b0#n276

Original comment by BrantKnu...@gmail.com on 25 Sep 2014 at 10:15

[GoogleCodeExporter]

I changed ibm_db_sa/base.py to do

 class DB2Compiler(compiler.SQLCompiler):

     def visit_true(self, expr, **kw):
         return '1'

(copied from dialects/mssql/base.py) and then I don't get an error... so that's 
one way to fix it.

Original comment by BrantKnu...@gmail.com on 25 Sep 2014 at 10:24

[GoogleCodeExporter]

Hi Brant,
 Your suggested fix is already in the git branch, on may 18, 2014 this fix has been committed to git with https://code.google.com/p/ibm-db/source/detail?r=0f1adccd488f16ba43c0611e1690834651c34e5e&repo=ibm-db-sa . And this will get included in ibm_db_sa new release.

Original comment by rahul.pr...@in.ibm.com on 26 Sep 2014 at 9:00

[GoogleCodeExporter]

Fix has released with ibm_db_sa-0.3.2

Original comment by rahul.pr...@in.ibm.com on 5 Nov 2014 at 6:30

• Changed state: Fixed