We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Cannot perform UNION when one of the projected fields is NULL — Vertica Forum

Cannot perform UNION when one of the projected fields is NULL

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.

Comments

  • 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 
  • @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
  • 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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file