I'm not able to reproduce this on my dev box. Maybe it's caused by specific database settings ?

What happens when you run the query against the database directly ?

Database query failed. Error received from database was #0: 0

It does not actually say '0', but that FLOOR is an unknown identifier.
PR for ADODb to fix this zero message: https://github.com/ADOdb/ADOdb/issues/1061

PHP 7.4 comes with SQLite 3.31, which does not have the FLOOR function.
PHP 8.4 ships with SQLite 3.46 and doesn't have this feature either.
So why did it work for me? Because I put the newer SQLite 3.48 library in the PHP 8.4 directory and forgot about it, FLOOR is supported in it.
It looks like SQLite for FLOOR should be compiled with the SQLITE_ENABLE_MATH_FUNCTIONS flag.

By the way, is it mandatory to use this function in the MantisGraph plugin, or can we somehow get by without it?

Well it would have been worth mentioning somewhere that you were using SQLite, which is not (yet at least - see 0010573) supported - I was assuming you were on MySQL...

is it mandatory to use this function in the MantisGraph plugin, or can we somehow get by without it?

It is not, really. The requirement is to get an integer value without rounding, so floor was the most logical choice when I refactored graph_api.php last year. I'm fine with using an alternative as long as we get the same results. And we're dealing with unix timestamps here, so only positive values.

PHP 7.4 comes with SQLite 3.31, which does not have the FLOOR function.
PHP 8.4 ships with SQLite 3.46 and doesn't have this feature either.

I'm a bit surprised by this statement, because to my knowledge libsqlite3 is no longer bundled since 7.4, so PHP should use whatever version is installed on the system.

According to the SQLite change log, built-in math functions should be available since 3.35.0, assuming it has been compiled with the appropriate flag as you pointed out (which is the case on Ubuntu and Windows). So we could also decide to require this minimum version (and make sure.

Well it would have been worth mentioning somewhere that you were using SQLite

Yes, indeed. I almost always use SQLite and I don't even notice it anymore.

I'm a bit surprised by this statement

I am using PHP For Windows which is available here: https://windows.php.net/download/

Upstream issue https://github.com/ADOdb/ADOdb/issues/1061 has been fixed and will be released with ADOdb 5.22.9 (no announced date yet).

It seems that all new versions of PHP for Windows still come with a compiled SQLite that lacks the math option. -_-
A simple alternative solution is to replace "FLOOR(x)" with "ROUND(x - 0.5)".

PR: https://github.com/mantisbt/mantisbt/pull/2197

A simple alternative solution is to replace "FLOOR(x)" with "ROUND(x - 0.5)".

Mathematically not identical, but since we are only dealing with positive values (timestamps) here, that should not be an issue here.

That said, the behavior of ROUND is sometimes unexpected, for example in MySQL

ROUND() uses the following rules depending on the type of the first argument:

For exact-value numbers, ROUND() uses the “round half away from zero” or “round toward nearest” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

For approximate-value numbers, the result depends on the C library. On many systems, this means that ROUND() uses the “round to nearest even” rule: A value with a fractional part exactly halfway between two integers is rounded to the nearest even integer.

For example:

mysql> select round(0.5), round(cast(0.5 as float));
+------------+---------------------------+
| round(0.5) | round(cast(0.5 as float)) |
+------------+---------------------------+
|          1 |                         0 |
+------------+---------------------------+

So I'm a bit concern, and think that this should be carefully tested. Especially considering that SQLITE support is at this stage an experimental thing that only you are using...

Moreover, some DB's require a 2nd param for precision (e.g. for mssql round(x, 0)).

Oh! That's an idea - let's use “CAST”.

let's use “CAST”

Hmmm. We're going to have SQL portability issues with that.

mysql> select cast(0.5 as integer);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'integer)' at line 1

mysql> select cast(0.5 as unsigned);
+-----------------------+
| cast(0.5 as unsigned) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.001 sec)

So CAST seems to perform rounding too (although not on all DBs), which was unexpected and means it's no different from using ROUND and gets us back to my earlier concern...

In fact, in both of these expressions, the variable $t_seconds_per_day contains the integer 86400, and—at least in SQLite—the calculations are performed as integer operations in any case. And the rounding functions do nothing.

the calculations are performed as integer operations in any case

Integer division is an interesting idea, however not all DBs are doing it.

• / performs an integer division with MS SQL Server, PostgreSQL, SQLite
• On MySQL, MariaDB and Oracle, the result is a float.

MySQL has DIV operator, Oracle has \.

So the solution might be to determine the operator to use based on the DB being used.

SQLite supports ROUND(x,d); I think this is the most compatible option. PR updated.

@raspopov I spent sometime today reviewing / testing sqlite and provided some suggestions. It would be good to keep a running list of what is remaining to enable merging - e.g. adodb dev-master dependency. I'm looking forward to this being merged.

@vboctor, Thank you for your time. As I understand it, @mnewnham has joined the SQLite implementation for ADODb, and he has his own plans for the necessary SQLite functionality in the library.