-
1. Re: MySQL TinyInt null is false
shawkins Mar 27, 2014 7:22 AM (in response to gadeyne.bram)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.
-
2. Re: MySQL TinyInt null is false
gadeyne.bram Mar 27, 2014 9:13 AM (in response to shawkins)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
-
3. Re: MySQL TinyInt null is false
shawkins Mar 27, 2014 9:22 AM (in response to gadeyne.bram)> 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.
-
4. Re: MySQL TinyInt null is false
gadeyne.bram Mar 27, 2014 11:16 AM (in response to shawkins)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.
-
5. Re: MySQL TinyInt null is false
shawkins Mar 27, 2014 11:42 AM (in response to gadeyne.bram)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