Boolean renders as smallint

[windiana42]

I am not sure this is intentional rendering of sqlalchemy.Boolean():

str(sqa.Boolean())
'BOOLEAN'
str(sqa.Boolean().compile(engine.dialect))
'SMALLINT'

My assumption would be that boolean is either a new or not that well supported type of DB2 LUW. This is why SMALLINT might have been a legacy fix for that problem.

Btw., I discovered a quite serious bug in DB2 container image (icr.io/db2_community/db2) with boolean:

DROP TABLE IF EXISTS tmp01;
CREATE TABLE tmp01
(
    col1 BIGINT,
    col2 BIGINT,
    col3 BIGINT,
    col4 BIGINT
);

INSERT INTO tmp01 values (2, 1, 2, 33);

SELECT
       df4.col3 * 4 >= df4.col4                          AS y,
       df4.col1 <= df4.col2 AND df4.col3 * 4 >= df4.col4 AS z
FROM tmp01 AS df4;

This yields y=NULL probably due to common subexpression between y and z and fast path evaluation of z.

[bchoudhary6415]

Hi @windiana42,

Thank you for reporting this issue and for the detailed explanation.

Regarding your first point about sqlalchemy.Boolean():

You're right — I'm able to reproduce the behavior where str(sqa.Boolean()) renders as 'BOOLEAN', while str(sqa.Boolean().compile(engine.dialect)) renders as 'SMALLINT'. This is due to the fact that DB2 LUW started providing native BOOLEAN support from version 11.5 onwards. I've made the fix to support and will raise a PR for it soon.

As for the second point — the query result where column y returns NULL — I was also able to reproduce this behavior, even when running through python ibm_db. It does seem related to how the expression is optimized or evaluated internally, possibly due to shared subexpressions or short-circuit evaluation logic in DB2's query engine.

I'll run the same scenario using the DB2 CLI directly to further investigate, and I’ll update you with my findings soon.

Thanks again for the valuable feedback.

[windiana42]

import sqlalchemy as sa

url = "db2+ibm_db://db2inst1:password@localhost:50000/testdb"

engine = sa.create_engine(url)
print(sa.Boolean().compile(engine.dialect))

prints SMALLINT

with the following environment:

% pixi list -e py312ibm | grep -i '\(ibm\|sqlalchemy\)'
Environment: py312ibm
ibm_db                            3.2.6         py312h0b39219_0       12.2 MiB   conda  https://conda.anaconda.org/conda-forge/
ibm_db_sa                         0.4.2         py312h4a7af80_0       70.4 KiB   conda  https://conda.anaconda.org/conda-forge/
sqlalchemy                        2.0.43        py312h163523d_0       3.4 MiB    conda  https://conda.anaconda.org/conda-forge/

[bimalkjha]

@windiana42 It is the behavior of Db2 server and not the ibmdbsa or ibm_db python package. Client returns the value as returned by server. We verified it using Db2 CLP and ODBC drivers, both returns the same result as below:

 db2 "SELECT df4.col3 * 4 >= df4.col4 AS y, df4.col1 <= df4.col2 AND df4.col3 * 4 >= df4.col4 AS z FROM zurbie.tmp01 AS df4"

Y  Z
-- --
-   0

  1 record(s) selected.

> SQLExecDirect 1 "SELECT df4.col3 * 4 >= df4.col4 AS y,df4.col1 <= df4.col2 AND df4.col3 * >= df4.col4 AS z FROM tmp01 AS df4" -3
SQLExecDirect: rc = 0 (SQL_SUCCESS)
> fetchall 1
FetchAll:  Columns: 2
  Y Z
  -, 0
FetchAll: 1 rows fetched.

We see consistent behavior using all Db2 clients. So, it is not an issue with ibm_db or db2 client, but server return these values. Please take this issue with IBM Support team. Thanks.

[bchoudhary6415]

import sqlalchemy as sa

url = "db2+ibm_db://db2inst1:password@localhost:50000/testdb"

engine = sa.create_engine(url)
print(sa.Boolean().compile(engine.dialect))
prints SMALLINT

with the following environment:

% pixi list -e py312ibm | grep -i '\(ibm\|sqlalchemy\)'
Environment: py312ibm
ibm_db                            3.2.6         py312h0b39219_0       12.2 MiB   conda  https://conda.anaconda.org/conda-forge/
ibm_db_sa                         0.4.2         py312h4a7af80_0       70.4 KiB   conda  https://conda.anaconda.org/conda-forge/
sqlalchemy                        2.0.43        py312h163523d_0       3.4 MiB    conda  https://conda.anaconda.org/conda-forge/

@windiana42 Yes, I'm able to reproduce. fix will be delivered soon.
Thank you

[bchoudhary6415]

@windiana42 ,

The PR for this issue (#163) is merged.
I’m closing the issue now.

Thank you!