5 Replies Latest reply on Mar 27, 2014 11:42 AM by Steven Hawkins

    MySQL TinyInt null is false

    gadeyne.bram Master

      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

        • 1. Re: MySQL TinyInt null is false
          Steven Hawkins Master

          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 Master

            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
              Steven Hawkins Master

              > 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 Master

                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=JDBCprincipal=intecalert@teiid-security vdbName=vdb3vdbVersion=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
                  Steven Hawkins Master

                  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