2 Replies Latest reply on Oct 9, 2015 8:54 AM by Salvatore R

    Invalid dates in MySQL source tables are shown as NULL values in Teiid

    Salvatore R Newbie

      Hi all,

       

      I am experiencing a problem on loading source tables from MySQL containing columns defined as DATE as datatype.

       

      In particular, I have this table:

      create table my.test (col1 DATE NOT NULL);
      
      

      with the following rows:

      insert into my.test VALUES('2015-10-01');
      insert into my.test VALUES('0000-00-00');
      
      

       

      When I query this table in Teiid, as a result, the invalid date '0000-00-00' is shown as NULL value.

      As a side effect, when I run a query with a "IS NOT NULL" condition:

       

      select * from my.test where col1 is not null;
      
      

       

      the query is fully pushed down to MySQL that returns both rows, since the NULL value is actually not null in the source:

      col1
      2015-10-01
      <null>

      but I would expect the second row to be not returned by this query.

       

      On the other side, the "IS NULL" condition returns the expected result (only the row with NULL value).

       

      I wondered if showing an invalid date as NULL is a correct/expected behavior.