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
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
0
Comments
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
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)