We're using Laravel 8 and have recently updated to MariaDB 10.3 from 10.1.
I'm noticing an issue now when using unions in certain cases, specifically when using a placeholder select so that the number of columns are equal. The issue is that the integer values are being cast to strings. They're unsigned integers in the database and this wasn't an issue prior to changing the MariaDB version.
A simplified example to reproduce this would be:
dd(DB::select("(select id FROM users) union (select NULL AS id FROM users)"));
array:2 [
0 => array:1 [
"id" => "1"
]
1 => array:1 [
"id" => null
]
]
Notice that the id in the first element is cast to a string. From experimenting with this, it only happens when using the NULL AS column_name. If I were to select a column that actually is null in the database, this doesn't occur. For example:
dd(DB::select("(select id FROM users) union (select parent_id FROM users)"));
array:2 [
0 => array:1 [
"id" => 1
]
1 => array:1 [
"id" => null
]
]
Realistically, I'm using a union between different tables and the second table doesn't have the same column, which is why I'm using a null placeholder. But I figure anyone that wants to run this query and uses Laravel will at least have the users table.
Has anyone run into this before? I'm unsure of where the actual issue is, but as mentioned, this only happened after upgrading MariaDB. Any suggestions or insight would be greatly appreciated. Thanks!
Perhaps try explicitly casting the placeholder to an integer:
(SELECT id FROM users) UNION (SELECT CAST(NULL AS UNSIGNED INTEGER) AS id FROM users)
If the column types of a UNION are different, then MariaDB will implicitly cast them to a type that both sides can fit in. I am speculating that the NULL value is ambiguously typed and the implicit type casting is different in the newer version.