ERROR 3146: Duplicate columns in select list of projection

Moderator: NorbertKrupa

Post Reply
mrian
Newbie
Newbie
Posts: 8
Joined: Wed Jul 17, 2013 3:12 am

ERROR 3146: Duplicate columns in select list of projection

Post by mrian » Wed Jan 29, 2014 11:20 am

Hi All,

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

Here's how to simulate it

Database: Vertica 7 (but I think same behaviour applies to other versions)

Code: Select all

    create table parent
    (
    parent_id int,
    description varchar(20),
    child01 number,
    child02 number
    );

    create table child
    (
    child_id int,
    child_description varchar(20)
    );

Code: Select all

    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):

Code: Select all

    => 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:

Code: Select all

    => 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:

Code: Select all

    => 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

mrian
Newbie
Newbie
Posts: 8
Joined: Wed Jul 17, 2013 3:12 am

Re: ERROR 3146: Duplicate columns in select list of projecti

Post by mrian » Wed Jan 29, 2014 1:09 pm

I was answered in the Vertica forum. Let me paste the link as well as the reply.

https://community.vertica.com/vertica/t ... ot_allowed
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/ ... #10391.htm
As the same thing runs perfectly in Oracle, this just validates that we have to code differently in Vertica. :oops:

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR 3146: Duplicate columns in select list of projecti

Post by JimKnicely » Wed Jan 29, 2014 1:37 pm

Hi,

There are some restrictions on creating pre-join projections. i.e. you can't have sub queries and you need to set up primary keys and foreign keys.

Why do you have child keys in your parent table?

Maybe a better solution would be:

Code: Select all

dbadmin=> create table parent (
dbadmin(> parent_id int not null,
dbadmin(> parent_description varchar(20)
dbadmin(> );
CREATE TABLE
dbadmin=> alter table parent add constraint parent_pk primary key (parent_id);
ALTER TABLE
dbadmin=> create table child (
dbadmin(> child_id int not null,
dbadmin(> child_description varchar(20),
dbadmin(> parent_id int not null
dbadmin(> );
CREATE TABLE
dbadmin=> alter table child add constraint child_pk primary key (child_id);
ALTER TABLE
dbadmin=> alter table child add constraint child_fk foreign key (parent_id) references parent (parent_id);

ALTER TABLE
dbadmin=>
dbadmin=> insert into parent values (1,'Parent 01');
insert into child values (100, '1hundred', 1);
insert into child values (200, '2hundred', 1);
commit;
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into child values (100, '1hundred', 1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into child values (200, '2hundred', 1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT

dbadmin=> create projection parent_child_projection
dbadmin-> as
dbadmin-> select
dbadmin-> parent.parent_id,
dbadmin-> parent.parent_description,
dbadmin-> child.child_id,
dbadmin-> child_description
dbadmin-> from parent
dbadmin-> join child on child.parent_id = parent.parent_id;
WARNING 4468:  Projection <snowfall.parent_child_projection> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> select start_refresh();
             start_refresh
----------------------------------------
 Starting refresh background process.

(1 row)

Code: Select all

dbadmin=> select
dbadmin-> parent.parent_id,
dbadmin-> parent.parent_description,
dbadmin-> child.child_id,
dbadmin-> child_description
dbadmin-> from parent
dbadmin-> join child on child.parent_id = parent.parent_id;
 parent_id | parent_description | child_id | child_description
-----------+--------------------+----------+-------------------
         1 | Parent 01          |      100 | 1hundred
         1 | Parent 01          |      200 | 2hundred
(2 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

mrian
Newbie
Newbie
Posts: 8
Joined: Wed Jul 17, 2013 3:12 am

Re: ERROR 3146: Duplicate columns in select list of projecti

Post by mrian » Wed Jan 29, 2014 1:59 pm

Thanks Jim.

Looks good. Our mindset (was) is still leaning on how our data structures were in previous databases supported. So this is a great exercise to look at things in a more Vertica-centric view.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR 3146: Duplicate columns in select list of projecti

Post by JimKnicely » Wed Jan 29, 2014 2:29 pm

Hi,

I'd look at the old design. It's a bad idea (imho) to have columns defined like you did. What happens when you add new children? Do you have to add new columns to the parent table? Yikes.

Anyway, I don't think you'll have issues with your design in Vertica. You won't be able to create pre-join projections but you should be fine with the super projections that Vertica created for you.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

mrian
Newbie
Newbie
Posts: 8
Joined: Wed Jul 17, 2013 3:12 am

Re: ERROR 3146: Duplicate columns in select list of projecti

Post by mrian » Wed Jan 29, 2014 2:37 pm

Will update you guys on what we finally come up with.

Jim, your inputs are much appreciated. Thanks!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR 3146: Duplicate columns in select list of projecti

Post by JimKnicely » Wed Jan 29, 2014 2:45 pm

Cool, keep us updated!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”