MySQL TinyInt null is false

[gadeyne.bram]

Hi,

I'm not sure if this is a Teiid issue or a SQuirrel issue or a MySQL issue.

In my MySQL database I have some columns who are defined as TINYINT(1) NULL

Executing query's in SQuirrel gives me some results where all NULL values were changed to false.

I found in some other posts that I should set the tinyInt1isBit property to false on my MySQL driver.

I did this on my MySQL datasources in the configuration file but this did not resolve the issue.

Is there a similar property for the Teiid driver or is there a Teiid setting that I need to adjust?

With kind regards

Bram

[shawkins]

There's no specific property for our driver.  We'll simply pull the values from the mysql driver and then ensure that they convert to the target datatype - but we have no conversion that takes null to false.  You'll likely have to dig some more around what needs to be set at the mysql driver level.

[gadeyne.bram]

Hi,

Thank you for the quick reply!

I've found a solution but it's not completely solid.

I've set the property tinyInt1isBit = false and regenerated the model with teiid designer. Now I noticed that native type BIT was changed to TINYINT and that datatype boolean was changed to byte.

If I rerun my query now, I see that indeed NULL is handles as NULL and not as false.

I still think that TEIID did not completely handle the previous mapping correctly because I had a statement containing "case when somebooleancolumn IS NULL then 'null' else case when somebooleancolumn then 't' else 'f' end end". SQuirrel received this as a string so the processing of the column had to be done server side.There were no 'null' values only 'f' values so teiid mapped all NULL's to false. The property tinyInt1isBit = false was already set so I suppose that the MySQL driver did send the content as a tinyint in stead of a BIT.

With kind regards

Bram

[shawkins]

> I still think that TEIID did not completely handle the previous mapping correctly

It would be fairly straight-forward to determine what is going on there.

Increase logging or use the command log to capture the source sql.  Issue the source sql directly against mysql.  Of course the initial thing that you are looking for is that the source sql was pushed as expected.  If not - for example if the searched case is not pushed, then that could explain why the column was still retrieved with null mapped to false.

[gadeyne.bram]

Hi Steven,

I've searched the logs and found these:

13:09:52,592 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #4) SVd4eF3Cg3jq

START USER COMMAND:

startTime=2014-03-27 13:09:52.592

requestID=SVd4eF3Cg3jq.10

txID=null

sessionID=SVd4eF3Cg3jq  applicationName=JDBC

principal=intecalert@teiid-security

vdbName=vdb3

vdbVersion=1

sql=select inf.id as infectionid, inf.infectionfocus as infectionfocus, inf.infectionfocusprobability as infectionfocusprobability, inf.infectionprobability as infectionprobability,

inf.infectionseverity as infectionseverity, inf.admissionid as admissionid, inf.adequate as adequate, case when inf.adequatetherapy is null then 'null' else case when inf.adequatetherapy then 'waar' else 'onwaar' end end as adequatetherapy,

inf.bacteremia as bacteremia,

o.starttime as orderstarttime, o.endtime as orderendtime, o.id as orderid, rank() over(partition by inf.id order by o.ordertime desc) as orderrang

from cos2_infections inf

join cos2_patient_admission pa on pa.admissionid = inf.admissionid and pa.ward in ('ward1','ward2') and pa.admissiontime > PARSEDATE('2013-01-01','yyyy-MM-dd')

join cos2_infections_singlemedicationhistory infsmh on inf.id = infsmh.infectionid

join cos2_orders o on o.medicationhistoryid = infsmh.historyid

order by inf.id, orderrang

13:09:52,616 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue53) SVd4eF3Cg3jq SVd4eF3Cg3jq.10.4.1 Processing NEW request: SELECT cosara2.cos2_infections.id, cosara2.cos2_infections.infectionfocus, cosara2.cos2_infections.infectionfocusprobability, cosara2.cos2_infections.infectionprobability, cosara2.cos2_infections.infectionseverity, cosara2.cos2_infections.admissionid, cosara2.cos2_infections.adequate, cosara2.cos2_infections.adequatetherapy, cosara2.cos2_infections.bacteremia, cosara2.cos2_orders.starttime, cosara2.cos2_orders.endtime, cosara2.cos2_orders.id, cosara2.cos2_orders.ordertime FROM cosara2.cos2_infections, cosara2.cos2_patient_admission, cosara2.cos2_infections_singlemedicationhistory, cosara2.cos2_orders WHERE (cosara2.cos2_patient_admission.admissionid = cosara2.cos2_infections.admissionid) AND (cosara2.cos2_infections.id = cosara2.cos2_infections_singlemedicationhistory.infectionid) AND (cosara2.cos2_orders.medicationhistoryid = cosara2.cos2_infections_singlemedicationhistory.historyid) AND (cosara2.cos2_patient_admission.ward IN ('ward1', 'ward2')) AND (cosara2.cos2_patient_admission.admissiontime > {ts'2013-01-01 00:00:00.0'})

So it seems like the case statement is not pushed to MySQL. This was executed on te version using BIT and boolean.

[shawkins]

So this reflects that the driver setting is still not having an effect.

If you use show plan debug, then the plan should have annotations as to why the case statement is not pushed.  Of the top of my head I can't think of any reason why it wouldn't be.

Steve