Issues on where conditions testing booleans

[m.ardito]

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?

[shawkins]

Can you provide more on how ag is defined and/or what the source queries are?

It it probably related to mapping a numeric value to boolean and then having an inconsistent handling of the expectation that false = 0, true = not 0.  Or a similar scenario involving the mapping from char.

For example you may have 29624 0/false values and 293 non 1 true values, which depending on the exact predicate that is pushed down would cause an issue.

[m.ardito]

Sure, I'll try:

here is the ag's relevant CREATE statement in mysql

CREATE TABLE `ag` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    ... (other fields)
    `closed` TINYINT(1) NULL DEFAULT '0',
    ... (other fields)
    PRIMARY KEY (`id`),
    ... (other indexes)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

in mysql I usually test those booleans in a simplified form (which in teiid also gives wrong results, while it works in mysql,  added below on each case)  which gives my "expected" results

about stored values in source, "true" is "-1", while "false" is "0", in all my mysql tables, afaik. I also just added those simplified form and results in the original post above, btw.

edit: btw I think this 0/-1 values come from a legacy msaccess frontend which uses the same tables... the whole project is to port apps to web through teiid, while keeping original management apps, atm.

select ag.closed from ag;/* => 29917 total records */

/* looking for true */
select ag.closed from ag as ag where ag.closed ;/* => 293 results <== simplified form  valid in mysql, gives 0 in teiid */

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 ;/* => 29624 results <== simplified form valid in mysql, gives 29917 in teiid */

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 */

mysq models are all loaded simply like

<model name="databasename">
<property name="importer.useFullSchemaName" value="false"/>
<source name="mysql5_databasename" translator-name="mysql5" connection-jndi-name="java:/databasename"/>
</model>

nothing fancy.

[m.ardito]

I edited the above reply, about those 0/-1 values in mysql. sql server fields have 0/1 instead.

also teiid gives:

select ag.closed from ag as ag where ag.closed != 0 /* 293 rows */
select ag.closed from ag as ag where ag.closed = -1 /* 0 rows */

select ag.closed from ag as ag where ag.closed = 0 /* 29624 rows */
select ag.closed from ag as ag where ag.closed != -1 /* 29917 rows */

edit: also, I just found this:

http://stackoverflow.com/questions/17367926/why-is-ms-access-boolean-true-1-rather-than-1-or-true

[shawkins]

From what I can see tinyint(1) for MySQL is reported as a bit type in JDBC, which we then map to boolean.  However tinyint(1) does not behave as you would expect with bit.  It still allows values in the range of -128 to 127 and worse the boolean value is true if the value is -1 or 1-127.  It also seems to behave oddly when queried using the true/false literals.  This implies we should be overriding mysql here and treating the column as a byte - otherwise we'd need more translator logic to attempt to smooth over this odd handling.

[m.ardito]

From what I can see tinyint(1) for MySQL is reported as a bit type in JDBC, which we then map to boolean.

In the generated DDL I find this for that 'closed' field:

closed boolean DEFAULT '0' OPTIONS (ANNOTATION '', NAMEINSOURCE '`closed`', NATIVE_TYPE 'BIT'),

This implies we should be overriding mysql here and treating the column as a byte

Do you mean I should create specific DDL in the dynamic xml for each mysql table with those tinyint (1) "booleans"? how would it need to be defined?

I could also create a view transforming the values maybe like " (- field) as field " so 0 will remain 0 but -1 becomes 1...?

But I have 17 tables, in the VDB, with at least one of such fields... and totally 690 tinyint (1) "boolean" fields ...

btw, this teiid server still has 9.0.2

new year's resolution: upgrade/update everything

[shawkins]

> Do you mean I should create specific DDL in the dynamic xml for each mysql table with those tinyint (1) "booleans"? how would it need to be defined?

As a workaround you'd have to change the Teiid type to byte instead of boolean, then handle the conversion of the byte value to boolean.

> I could also create a view transforming the values maybe like " (- field) as field " so 0 will remain 0 but -1 becomes 1...?

Yes, once you have the type as a numeric value and you know that the only values are -1 and 0, then you can handle the conversion to boolean in a view - it could be expressed with a case expression - case when col = -1 then true else false end

> But I have 17 tables, in the VDB, with at least one of such fields... and totally 690 tinyint (1) "boolean" fields ...

That would imply you'd rather see the translator try to compensate.  An issue will need to be captured.  It will take more work rather than simply changing the import to use a byte type instead, but it's possible using a case expression like above.

[m.ardito]

I just opened [TEIID-4693] mysql "booleans" translation issues  - JBoss Issue Tracker

Meanwhile, I can also use one of the forms like " field = false "  and " field != false " that seemed to work, until now...