MariaDB - failed select in DbDBI.pm

[zbchristian]

I am running the API V2 server in a docker container with MariaDB and ran into a problem.

In the module DbDBI.pm, the selection of the owner from the table ovms_owners fails due to a mistake in the WHERE clause in DbOwnerNameByID, DBOwnerIDByName and DbOwnerNameByID:
SELECT * FROM ovms_owners WHERE owner=? and status=1 AND deleted="0000-00-00 00:00:00"

The column deleted is a tinyint(1) and not of type datetime, so the select is always empty, at least in MariaDB.

So the select statement should look like: SELECT * FROM ovms_owners WHERE owner=? and status=1 AND deleted=0

Not sure, why this was not observed before. Might be related to the treatment by MariaDB

[dexterbg]

While the query may strictly be seen as incorrect or at least misleading, MariaDB just as MySQL normally compares an integer to a string by converting both to a double precision float. So deleted="0000-00-00 00:00:00" normally works just fine:

MariaDB [dexter]> select 0="0000-00-00 00:00:00", 1!="0000-00-00 00:00:00", 1="1.0ABC", 1="1.1ABC";
+-------------------------+--------------------------+------------+------------+
| 0="0000-00-00 00:00:00" | 1!="0000-00-00 00:00:00" | 1="1.0ABC" | 1="1.1ABC" |
+-------------------------+--------------------------+------------+------------+
|                       1 |                        1 |          1 |          0 |
+-------------------------+--------------------------+------------+------------+

If it doesn't in your case, you most probably have a custom MariaDB configuration, be aware that may then affect other queries & statements as well.

Feel free to post a PR.

[zbchristian]

@dexterbg The container is the official mariadb:latest , which is version 10.6.2, without any modifications.
You are correct, that the implicit cast should take care of this.
Not sure, why this fails in my setting. Have to check the defaults of the container