Cannot perform UNION when one of the projected fields is NULL

Carlos_Santos

It seems Vertica cannot perform a UNION when one of the projected fields is NULL.

If you execute the following query (the views used here are provided in the Vertica's examples):

SELECT customer_key as customer_key
FROM customer_dimension t0
UNION ALL SELECT null AS customer_key
FROM employee_dimension

Vertica returns this error:

 [Error Code: 3429, SQL State: 42804]  [Vertica][VJDBC](3429) ERROR: For 'UNION', types int and varchar are inconsistent
  [Vertica][VJDBC]Detail: Columns: customer_key and customer_key

This query works in other databases: I tested it with Oracle 11g, SQL Server 2012, MySQL 5.

I understand that if two data types are not compatible (like performing a union with a date and a number), Vertica does return an error. But in this case, Vertica should know that NULL is compatible with "customer_key" and the query should work.

Are there any future plans to make this query work?

Environment

Vertica 07.00.0100.
I am connecting with a JDBC connection. The version of the driver is 07.00.0201.

Business Impact

I am developing a connector to Vertica for the Denodo Platform.

Greg_Panzer

Hi Carlos, try explicitly casting the column that contains null values:

SELECT customer_key as customer_key
FROM customer_dimension t0
UNION ALL SELECT CAST(NULL AS INT), AS customer_key
FROM employee_dimension 

Carlos_Santos

@Greg, thanks for your suggestion. I already tried that and it works, but what I need is Vertica to support the scenario I described before.

knicely87

What's to fix?

dbadmin=> create table t as select null customer_key;
CREATE TABLE
dbadmin=> \d t
                                       List of Fields by Tables
  Schema  | Table |    Column    |    Type    | Size | Default | Not Null | Primary Key | Foreign Key
----------+-------+--------------+------------+------+---------+----------+-------------+-------------
 snowball | t     | customer_key | varchar(1) |    1 |         | f        | f           |
(1 row)

It's a VARCHAR

http://vertica-forums.com/viewtopic.php?f=63&t=682

Carlos_Santos

As I mentioned in my post, the following query fails in Vertica:

SELECT customer_key as customer_key
FROM customer_dimension t0
UNION ALL SELECT null AS customer_key
FROM employee_dimension

This query does work in other databases. I guess it should work in Vertica as well.