Should I need to explictly cast hard-coded values in UNION joins for ints not to end up as strings using mysqlnd?

Using MariaDB 10.3.9 and given the following query:

SELECT photoId FROM photos WHERE photoId = 123;

using PDO and mysqlnd in php var_dump will give int(123), as expected.

But if I change the query to

SELECT photoId FROM photos WHERE photoId = 123
UNION
SELECT 1 FROM photos WHERE photoId = 456;

Then the output becomes string(3) "123", string(1), "1".

If I change the query to

SELECT photoId FROM photos WHERE photoId = 123
UNION
SELECT CAST(1 AS UNSIGNED INT) FROM photos WHERE photoId = 456;

I correctly get int(123), int(1).

This seems like very strange behaviour – why is MariaDB choosing to cast the explicit 1 to a string, rather than leave it as the integer it’s written as?

Weirder still, I have photoId defined as unsigned. If I just use CAST(1 AS INT) it still comes through as a string, just because I missed the UNSIGNED part.

All topic