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:


      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.