UNION Gotchas: How NULL Values Can Cause Unexpected Results in MariaDB

In MariaDB versions prior to 10.2, there can be an unexpected data type conversion when using NULL values in UNION queries. Specifically, NULL might be treated as a decimal value (often with a default precision and scale), even if the other columns in the UNION result set are of different data types (like integers or strings).

Why It Happens:

• Implicit Type Conversion: By default, MariaDB attempts to implicitly convert the data types of columns in a UNION query to a common type that can accommodate all values.
• NULL's Ambiguity: NULL itself doesn't have a specific data type. In some cases, MariaDB might choose a decimal type as the common type, especially if there are already decimal values in the UNION.

Consequences:

• Unexpected Results: This behavior can lead to unexpected results in your query output. For example, you might see NULL values displayed with decimal places, even though they aren't actual numbers.
• Type Mismatches: If you're expecting specific data types (like integers) in your results, the implicit conversion to decimal can cause issues with further processing or analysis.

Solutions:

Here are two approaches to address this issue:

1. Explicit Type Casting:

   SELECT cast(20 as INT) UNION SELECT NULL UNION SELECT 2.2;
   

2. Order the UNION:

Best Practices:

• Be mindful of NULL values in UNION queries, especially if you're mixing data types.
• Consider using explicit type casting for NULL values to avoid ambiguity and ensure predictable results.
• If the order of subqueries might affect the outcome, experiment with different arrangements.

Additional Notes:

• This behavior was addressed in MariaDB versions 10.2 and later. The improved handling of NULL in UNION queries generally avoids the implicit conversion to decimal.
• If you're working with an older MariaDB version, be extra cautious with NULL values in UNIONs and consider the solutions mentioned above.

Example Codes Demonstrating NULL Treatment in MariaDB UNION Queries

• table1 (integer column named value)

We want to combine these tables using a UNION query, but table2 might contain NULL values.

Code 1: Implicit Conversion to Decimal (MariaDB < 10.2 possible behavior)

SELECT value FROM table1
UNION ALL
SELECT value FROM table2  -- Might cause NULL to be treated as decimal
UNION ALL
SELECT value FROM table3;

Result (possible outcome):

Explanation:

• Since table2 has a decimal column and might contain NULL, MariaDB (prior to 10.2) might choose decimal as the common data type for the UNION.
• This results in NULL being displayed with ".00" appended, even though it's not a real number.

Code 2: Explicit Type Casting (Recommended)

SELECT value FROM table1
UNION ALL
SELECT CAST(value AS INT) FROM table2  -- Cast NULL to integer
UNION ALL
SELECT value FROM table3;

Result:

• We explicitly cast the value from table2 to INT using CAST(value AS INT).
• This ensures NULL remains NULL in the result, avoiding unexpected decimal conversions.

Code 3: Order of UNION Subqueries (Might influence outcome in older MariaDB)

SELECT value FROM table1
UNION ALL
SELECT value FROM table3  -- Start with non-decimal column
UNION ALL
SELECT value FROM table2;

Result (possible outcome, especially in older MariaDB):

• By placing the table1 and table3 subqueries (with integer and string data types) first, we might influence the common data type selection.
• This could prevent NULL from being treated as a decimal in older MariaDB versions.

The COALESCE function allows you to specify a default value to return if the first argument is NULL. You can use this within the UNION subqueries to provide a consistent replacement for NULL:

SELECT value FROM table1
UNION ALL
SELECT COALESCE(value, 0) FROM table2  -- Replace NULL with 0 (or any desired value)
UNION ALL
SELECT value FROM table3;

• COALESCE(value, 0) checks if the value from table2 is NULL. If so, it returns 0 (you can replace 0 with any appropriate value).
• This ensures a consistent value for NULL entries, avoiding the decimal conversion issue.

CASE Expression:

Similar to COALESCE, you can use a CASE expression to define custom logic for handling NULL values in each UNION subquery:

SELECT value FROM table1
UNION ALL
SELECT CASE WHEN value IS NULL THEN 'N/A' ELSE value END FROM table2
UNION ALL
SELECT value FROM table3;

• The CASE expression checks if value from table2 is NULL.
• If it is, it returns the string "N/A" (you can customize this string).
• Otherwise, it returns the original value.

ISNULL Function (MariaDB 10.2+)

MariaDB 10.2 introduced the ISNULL function, which acts similarly to COALESCE but offers a more concise syntax:

SELECT value FROM table1
UNION ALL
SELECT ISNULL(value, 0) FROM table2  -- Replace NULL with 0
UNION ALL
SELECT value FROM table3;

• ISNULL(value, 0) returns 0 if value from table2 is NULL, otherwise it returns the original value.
• This method is only available in MariaDB 10.2 and later versions.

Choosing the Best Method:

The best approach depends on your specific needs and preferences:

• Explicit casting is clear and works consistently across MariaDB versions.
• COALESCE or ISNULL functions provide a more compact way to handle NULL with a predefined replacement value.
• CASE expression offers the most flexibility for defining custom logic based on NULL conditions.