Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

ERROR 3146: Duplicate columns in select list of projection not allowed

Hi All,

I've encountered an issue (or non-issue?) and would need some clarification:

Here's how to simulate it

Database: Vertica 7
create table parent
(
parent_id int,
description varchar(20),
child01 number,
child02 number
);

create table child
(
child_id int,
child_description varchar(20)
);
insert into parent values (1,'Parent 01',100,200);
insert into child values (100, '1hundred');
insert into child values (200, '2hundred');
commit;
=> select * from parent;
 parent_id | description | child01 | child02
-------------+---------------+----------+---------
         1      | Parent 01   |     100   |     200

=> select * from child;
 child_id | child_description
-----------+-------------------
      100  | 1hundred
      200  | 2hundred
I'm trying to connect the parent table with the child table (for each of its values):
=> select
-> parent.parent_id,
-> first_child.child_description first_child_description,
-> second_child.child_description second_child_description
-> from parent,
->      (select child_id first_child_id, child_description from child) first_child,
->      (select child_id second_child_id, child_description from child) second_child
-> where parent.child01 = first_child.first_child_id
-> and parent.child02 = second_child.second_child_id;

 parent_id | first_child_description | second_child_description
-------------+-----------------------------+--------------------------
         1      |      1hundred                | 2hundred
As you can see, the SELECT works. But when I try and create a projection using the same query, it fails:
=> create projection parent_child_projection
-> as
-> select
-> parent.parent_id,
-> first_child.child_description first_child_description,
-> second_child.child_description second_child_description
-> from parent,
->      (select child_id first_child_id, child_description from child) first_child,
->      (select child_id second_child_id, child_description from child) second_child
-> where parent.child01 = first_child.first_child_id
-> and parent.child02 = second_child.second_child_id;
ERROR 3146:  Duplicate columns in select list of projection not allowed
I can create a view using the same query though:
=> create view parent_child_view
-> as
-> select
-> parent.parent_id,
-> first_child.child_description first_child_description,
-> second_child.child_description second_child_description
-> from parent,
->      (select child_id first_child_id, child_description from child) first_child,
->      (select child_id second_child_id, child_description from child) second_child
-> where parent.child01 = first_child.first_child_id
-> and parent.child02 = second_child.second_child_id;

CREATE VIEW

=> select * from parent_child_view;
 parent_id | first_child_description | second_child_description
-------------+-----------------------------+--------------------------
         1      | 1hundred                     | 2hundred
Am I doing something wrong here? Hope you guys can help.

Thanks!

Ian

Comments

  • Hi,

    It is type of a pre-join projection, which doesn't allow duplicity.

    --(select child_id first_child_id, child_description from child) first_child,
    --(select child_id second_child_id, child_description from child) second_child

    Above two statements which are creating inline tables in your query are producing duplicate results & hence the error in creation of projection.

    Pre- join projections should always be created on primary-key , foreign-key combination of join predicates,in order to avoid duplicity.

    Also, as you have used sub-queries in you projection definition, so please keep in mind that Projections are not allowed to use sub queries, as is the case here in your projection definition.

    You can refer to below doucmentation for more understanding on it:


    https://my.vertica.com/docs/6.1.x/HTML/index.htm#10391.htm


    Regards'

    Abhishek


  • Thanks Abhishek! Makes sense. I was wondering as the same query works in Oracle. It just validates that we have to code differently in Vertica. :)

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.