UNIX_TIMESTAMP return value change not documented

[Kolbe]

Hi,

In MariaDB 5.3.5, a change was made to UNIX_TIMESTAMP() so that it returns NULL instead of "0" for a nonsense argument. This is a change in behavior from upstream Oracle MySQL releases.

mysql 5.5.22 (root) [test]> select unix_timestamp('(null)');
+--------------------------+
| unix_timestamp('(null)') |
+--------------------------+
| 0 |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql 5.5.23-MariaDB (root) [test]> select unix_timestamp('(null)');
+--------------------------+
| unix_timestamp('(null)') |
+--------------------------+
| NULL |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

See:

http://kb.askmonty.org/en/mariadb-535-changelog/

http://bazaar.launchpad.net/~maria-captains/maria/5.3/revision/3435

a) I believe this change should be reverted, so that MariaDB's behavior matches that of the upstream product.

b) If Monty Program chooses not to revert this change, they must better document this incompatibility at http://kb.askmonty.org/en/unix_timestamp/ and http://kb.askmonty.org/en/mariadb-versus-mysql-compatibility/

[gu lei (gulei-om)]

I cannot migrate to MariaDB from Oracle MySQL because sql failed on MariaDB slave but succeeded on Oracle Mysql master.

Error 'Column 'sale_time' cannot be null' on query. Default database: 'xmhouse'. Query: 'insert into dmc_sale_time (proj_id,sale_time) values ('1269',unix_timestamp('2013-10'))'

MariaDB [(none)]> select unix_timestamp('2013-10');
+---------------------------+
| unix_timestamp('2013-10') |
+---------------------------+
| NULL |
+---------------------------+
1 row in set, 1 warning (0.00 sec)

[Elena Stepanova (elenst)]

Hi,

Due to migration of the bug tracking to JIRA, this bug was tracked as https://mariadb.atlassian.net/browse/MDEV-646.

This difference is documented at https://mariadb.com/kb/en/mariadb-versus-mysql-compatibility/ , which says

<quote>
MariaDB does more strict checking of date, datetime and timestamp values. For example UNIX_TIMESTAMP('x') now returns NULL instead of 0.
</quote>

For migrating via replication, you can just use row binary log format, it is generally safer anyway.