Search code examples
sqlnullunionexasol

Datatype mismatch error because of NULL values in union operation

AFAIU, an SQL engine internally assigns a datatype to NULL values, for example, some engines use the datatype integer as default. I know, that this may cause an error in UNION operations when the column of the other table is not compatible (e.g. VARCHAR) (here). Still, I struggle to understand why the following code works/does not work (on Exasol DB):

A)

This works

SELECT NULL AS TEST
UNION
SELECT DATE '2022-11-03' AS TEST
;

B)

But when I do 'the same' using a subquery, it throws a datatypes are not compatible error.

SELECT A.* FROM (SELECT NULL AS TEST) A
UNION
SELECT DATE '2022-11-03' AS TEST
;

C)

B can be fixed by explicit type casting of the NULL value:

SELECT A.* FROM (SELECT CAST(NULL AS DATE) AS TEST) A
UNION
SELECT DATE '2022-11-03' AS TEST
;

Still, I do not understand what happens in B behind the scenes, so A works but B does not. Apparently, the subquery (or a join) makes a difference for the type of the NULL column. But why?

Can anyone explain what exactly happens here?

PS. The same is the case for JOINS.

B2)

Does not work.

SELECT 'Dummy' AS C1, SELECT NULL AS TEST
UNION
SELECT 'Dummy' AS C1, SELECT DATE '2022-11-03' AS TEST
;

C2)

Does work.

SELECT 'Dummy' AS C1, SELECT CAST(NULL AS DATE) AS TEST
UNION
SELECT 'Dummy' AS C1, SELECT DATE '2022-11-03' AS TEST
;
Solution

  • In general, NULL has the type BOOLEAN in Exasol. You can check this either by using TYPEOF or DESCRIBE:

    select TYPEOF(NULL); -- The TYPEOF function is available since version 7.1

-- Old way to see the type of an expression:
create schema s;
create or replace table tmp as select null expr;
describe tmp;

    However, sometimes Exasol changes this type internally. One of these cases is if a NULL literal occurs directly inside an UNION clause. It only does this if it is an explicit NULL literal. E.g. the following also doesn't work:

    SELECT NULL+NULL AS TEST
UNION
SELECT DATE '2022-11-03' AS TEST;

    But it works if the expected type is BOOLEAN:

    SELECT NULL+NULL AS TEST
UNION
SELECT TRUE AS TEST;

    As you mentioned, the workaround is to wrap the expression in a CAST.