0

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!

kenshin9
  • 2,215
  • 4
  • 23
  • 38
  • What exactly is the purpose of the query? And what's the problem with string? You're provoking it. – Martin Zeitler Feb 16 '22 at 05:42
  • The purpose is to combine records from different tables into a single data set. One of the tables utilizes a type ID, where another doesn't. The issue with the string is that it was unexpected, as that's not how it previously worked. If we were to use a strict comparison on that value, it's going to give the wrong results because it's a string rather than an integer. – kenshin9 Feb 18 '22 at 15:55

1 Answers1

1

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.

Remon Huijts
  • 661
  • 6
  • 7
  • I'd rather wonder, if `INTEGER` can even be `NULL`? – Martin Zeitler Feb 16 '22 at 05:40
  • @MartinZeitler I am not sure what you mean. Any `INT` column can be declared to allow or to not allow `NULL`. But even if the `id` column is declared as `INT UNSIGNED NOT NULL` then you can still UNION it with a nullable `INT` column or a `NULL` value. – Remon Huijts Feb 16 '22 at 06:30
  • Thanks for the feedback and insight. I had considered doing this before, but was curious if there was another way to fix things without having to alter the queries. Perhaps something like a configuration in regards to the casting. But it is what it is, and if this is the way to go, I'll come back and mark this as the answer once I get to test it out. – kenshin9 Feb 18 '22 at 15:59