Select is not working as expected

Noor_Ahmed

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

Sajan_Sangraula

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

Noor_Ahmed

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)