Hi,
we are using Dremio 3.1.9 and yesterday we noticed a strange behavior while selecting data from MariaDB.
It looks like the returned “date” values are 1 day behind the real value. To reproduce the error we created a MariaDB table with a date column and some data, then we selected from it.
CREATE TABLE testtable (
a_date date DEFAULT NULL,
a_datetime datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
From DBeaver we read the correct data
but from Dremio we read (also added some casts) something different
So, if we select a “date” value from MariaDB, we get the date with 1 day subtracted, if we cast that value to timestamp we get the correct value, but if we cast a “datetime” back to “date” we get, again, 1 day subtracted.
But now here it comes the fun part… we added another column at the end, a cast from MariaDB date to timestamp (as the third column in the previous screenshot) but trunc’d to ‘day’.
The new column is green circled and the red circled columns have changed their values!
I bet that there is something related to timezone undergoing (we are UTC+2 in this season and that 22:00:00 may be a clue), but I can’t accept the fact that the columns changed their values in the second query just cause I added one more column. Also this does not explain why ‘2018-05-07 11:00:00’ casts to ‘2018-05-06’ in the first query, and to ‘2018-05-07’ in the second one. This also affects comparisons in WHERE clauses, that’s how we noticed this (we expected different values, queried through ODBC).
Are we missing something or there is a (huge?) bug with MariaDB/MySql? (Same test with Postgres does not show this problem)
Thanks