# Issues on where conditions testing booleans

**Marco Ardito**Jan 9, 2017 10:42 AM

Hi,

I have a dynamic vdb that loads both sqlserver and mysql models, working nicely in testing stage.

Since early development I noticed that I had problems filtering some table for booleans, and although I found some workaround to get what I was expecting, I think there's something strange...

Now I would like to understand if and what am I doing wrong, or if I have wrong expectations...

What it happens is that on some tables, which teiid DDL shows as "boolean" type, which SYS.columns reports as

- DataType boolean
- JavaClass java.lang.Boolean

in Mysql this is tinyint (1)

but in teiid I get SQL results like:

select ag.closed from ag/* => 29917 total records */ /* looking for true */ select ag.closed from ag as ag where ag.closed ; /* => 0 results */ select ag.closed from ag as ag where ag.closed = true/* => 0 results */ select ag.closed from ag as ag where ag.closed != false/* => 293 results */ select ag.closed from ag as ag where NOT (ag.closed = false)/* => 293 results */ /* looking for false */ select ag.closed from ag as ag where NOT ag.closed /* => 29917 results */ select ag.closed from ag as ag where ag.closed = false/* => 29624 results */ select ag.closed from ag as ag where ag.closed != true/* => 29917 results */ select ag.closed from ag as ag where NOT (ag.closed = true)/* => 29917 results */

while on another table, from a sql server model, also a boolean field is reported as

- DataType boolean
- JavaClass java.lang.Boolean

in sql server this is bit (1)

but I get always the same consistent result, independently from the where condition variation:

select a.a_ggi FROM otherdb.table as a /* => 12930 total records */ /* looking for NULLS */ select a.a_ggi FROM otherdb.table as a where a.a_ggi IS NULL /* => 7660 results */ select a.a_ggi FROM otherdb.table as a where a.a_ggi IS NOT NULL /* => 5270 results */ /* looking for true */ select a.a_ggi FROM otherdb.table as a where a.a_ggi /* => 6 results */ select a.a_ggi FROM otherdb.table as a where a.a_ggi = true /* => 6 results */ select a.a_ggi FROM otherdb.table as a where a.a_ggi != false /* => 6 results */ select a.a_ggi FROM otherdb.table as a where NOT (a.a_ggi = false) /* => 6 results */ /* looking for false */ select a.a_ggi FROM otherdb.table as a where NOT a.a_ggi /* => 5264 results */ select a.a_ggi FROM otherdb.table as a where a.a_ggi = false /* => 5264 results */ select a.a_ggi FROM otherdb.table as a where a.a_ggi != true /* => 5264 results */ select a.a_ggi FROM otherdb.table as a where NOT (a.a_ggi = true) /* => 5264 results */

which is what I was expecting also in the other case...

I didn't test this on all boolean columns, since I have now around 1300 booleans (~32000 total columns on ~1600 tables on 25 models), but I can test more if needed.

What is happening, what am I doing wrong, and how to correct, if possible, the boolean weird behavior?