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.
Cannot perform UNION when one of the projected fields is NULL
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
SELECT customer_key as customer_key
FROM customer_dimension t0
UNION ALL SELECT CAST(NULL AS INT), AS customer_key
FROM employee_dimension
@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.
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
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
As I mentioned in my post, the following query fails in Vertica:
SELECT customer_key as customer_keyThis query does work in other databases. I guess it should work in Vertica as well.
FROM customer_dimension t0
UNION ALL SELECT null AS customer_key
FROM employee_dimension
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
