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


Select is not working as expected — Vertica Forum

Select is not working as expected

Hi Vertica Team,

 I have executed following sequence of scripts.

--The following series of commands create a table named dim and the corresponding projection:
CREATE TABLE dim (pk INTEGER PRIMARY KEY, x INTEGER);
CREATE PROJECTION dim_p (pk, x) AS SELECT * FROM dim ORDER BY x UNSEGMENTED ALL NODES;

CREATE TABLE fact(fk INTEGER not null REFERENCES dim(pk));

INSERT INTO dim values (1,1);
INSERT INTO dim values (2,2);
INSERT INTO dim values (1,2); --Constraint violation
insert into fact values(1);
select analyze_constraints('dim');

select * from dim d, fact f where d.pk = f.fk;

For the first 10-15 time for the above statement i got the expected error.
ERROR 3149:  Duplicate primary/unique key detected in join [(public.fact x public.dim) using fact_super and dim_p_node0001 (PATH ID: 1)]; value [1]

but after that i got the following result. How Veritica is behaving like this.

Test=> select * from dim d, fact f where d.pk = f.fk;
 pk | x | fk
----+---+----
  1 | 1 |  1
  1 | 2 |  1
(2 rows)



Regards
--Noor Ahmed

Comments

  • Hi Noor,
    That's a very interesting observation. I was able to replicate the issue as well. However, after I added a create projection for the fact table, the constraint seems to be enforced strictly. Can you try replicating the issue with the following projection for fact:

    CREATE PROJECTION fact_p (fk) AS SELECT * FROM fact ORDER BY fk UNSEGMENTED ALL NODES;


    /Sajan
  • Hi Sajan,

    As per the your request i have created Projection, but of no use. Still i am getting two rows.

    select * from projections where anchor_table_name = 'fact'; 

    projection_schema_id | projection_schema |   projection_id   | projection_name | projection_basename |     owner_id      | owner_name |  anchor_table_id  | anchor_table_name |      node_id      |    node_name    | is_prejoin | created_epoch |    create_type    | verified_fault_tolerance | is_up_to_date | has_statistics | is_segmented | is_super_projection
    ----------------------+-------------------+-------------------+-----------------+---------------------+-------------------+------------+-------------------+-------------------+-------------------+-----------------+------------+---------------+-------------------+--------------------------+---------------+----------------+--------------+---------------------
        45035996273704976 | public            | 45035996273798986 | fact_super      | fact                | 45035996273704962 | dbadmin    | 45035996273798978 | fact              |                 0 |                 | f          |           638 | DELAYED CREATION  |                        0 | t             | f              | t            | t
        45035996273704976 | public            | 45035996273799064 | fact_p_node0001 | fact_p              | 45035996273704962 | dbadmin    | 45035996273798978 | fact              | 45035996273704980 | v_test_node0001 | f          |           639 | CREATE PROJECTION |                        0 | t             | f              | f            | t
    (2 rows)


    Test=> select * from dim d, fact f where d.pk = f.fk;
     pk | x | fk
    ----+---+----
      1 | 1 |  1
      1 | 2 |  1
    (2 rows)


Leave a Comment

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