Please close answered here https://mariadb.com/kb/en/from_unixtime/#performance-considerations

What is the data type of the column timestamp ?
What did the condition filter out? From a glance it returned all records.

FROM_UNIXTIME() changed its return data type from DATETIME to TIMESTAMP in 11.7.1 under terms of this task: MDEV-15751.

Please fix the query to

 SELECT * FROM `some_table` WHERE (`timestamp` >= FROM_UNIXTIME(1) OR `timestamp` IS NULL)

or to:

 SELECT * FROM `some_table` WHERE (`timestamp` >= FROM_UNIXTIME(1) OR `timestamp`='0000-00-00 00:00:00' OR `timestamp` IS NULL)

depending on whether you need '0000-00-00 00:00:00' in the result set.

I'm the maintainer for the software @ItsOnlyBinary is having problems with. They are using our MySQL database support with MariaDB and this change has broken compatibility with MySQL. Do you have a recommended minimal change I can make to make this work as it did before? If possible I'd prefer to not have to make major schema changes in our stable branch especially given this works with MySQL.

For reference, this is how the column is created:

> `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

The query that is being executed is admittedly a bit weird but its being generated at runtime so we can't always generate something perfect.

I don't understand. What is your query supposed to be doing? If the column is of TIMESTAMP type, it is guaranteed to have a valid timestamp value, there is no need to check for that, you can as well write WHERE TRUE or drop the WHERE clause altogether.

SELECT * FROM `some_table`

would produce exactly the same result. And will also work on MySQL just fine.

The query is automatically generated at runtime. Although the timestamp passed to FROM_UNIXTIME() is 0 in this case it usually is not and this is an unexpected compatibility break with both MySQL and earlier versions of MariaDB for it to be suddenly doing something different for no apparent reason.

I see, thanks. So your generated query is something like

SELECT * FROM `some_table` WHERE (`timestamp` >= FROM_UNIXTIME(?) OR `timestamp` IS NULL)

Perhaps, you can rewrite it as

SELECT * FROM `some_table` WHERE (`timestamp` >= coalesce(FROM_UNIXTIME(?),0) OR `timestamp` IS NULL)

That should work both for MariaDB and MySQL.

The reason was to fix bugs, like

MariaDB [test]> select version();

+-----------------------+

| version()             |

+-----------------------+

| 10.5.28-MariaDB-debug |

+-----------------------+

MariaDB [test]> select from_unixtime(1729989100) < from_unixtime(1729992500);

+-------------------------------------------------------+

| from_unixtime(1729989100) < from_unixtime(1729992500) |

+-------------------------------------------------------+

|                                                     0 |

+-------------------------------------------------------+

Here, as you can see, in MariaDB 10.5, the first number of seconds is clearly smaller than the second one, but the result of the first FROM_UNIXTIME() is larger than the second (I'm in the CET time zone). This would likely break the logic of your application for a lot of values of the timestamp column. In 11.7 this was fixed.