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

Ian_Clarito

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

Abhishek_Rana

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

Ian_Clarito

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.