[jira] [Commented] (TRAFODION-149) LP Bug: 1250183 - Clustering key conflicts with primary keys

["Atanu Mishra (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-149?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14696188#comment-14696188 ] 

Atanu Mishra commented on TRAFODION-149:
----------------------------------------

Anoop Sharma (anoop-sharma) wrote on 2013-11-24:	#1
When primary key and store by is specified, primary key will take precedence.

>>create table t (a int not null, b int not null, c int not null, d int, primary
 key (a, b, c)) store by (a);+>

--- SQL operation complete.
>>
>>invoke t;

-- Definition of Trafodion table TRAFODION.SCH.T
-- Definition current Sat Nov 23 22:05:39 2013

  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C INT NO DEFAULT NOT NULL NOT DROPPABLE
  , D INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC, B ASC, C ASC)

--- SQL operation complete.
>>

Changed in trafodion:
status:	New → Fix Committed
Weishiun Tsai (wei-shiun-tsai) wrote on 2013-12-03:	#2
Verified on a build from 12/02/2013, this problem has been fixed:

>>set schema trafodion.mytest;

--- SQL operation complete.
>>create table t (a int not null, b int not null, c int not null, d int, primary key (a, b, c)) store by (a);

--- SQL operation complete.
>>insert into t values (1, 1, 1, 1), (1, 2, 2, 2);

--- 2 row(s) inserted.

Changed in trafodion:
status:	Fix Committed → Fix Released


> LP Bug: 1250183 - Clustering key conflicts with primary keys
> ------------------------------------------------------------
>
>                 Key: TRAFODION-149
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-149
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-general
>            Reporter: Weishiun Tsai
>            Assignee: Anoop Sharma
>            Priority: Critical
>             Fix For: 0.8 (pre-incubation)
>
>
> When specifying STORE BY in a query together with PRIMARY KEY, the STORE BY seems to overwrite the PRIMARY KEY list.  The following example executes fine on SQ, but it gets an 8102 unique constraint error on Traf:
> >>set schema seabase.mytest;
> --- SQL operation complete.
> >>create table t (a int not null, b int not null, c int not null, d int, primary
>  key (a, b, c)) store by (a);
> --- SQL operation complete.
> >>insert into t values (1, 1, 1, 1), (1, 2, 2, 2);
> *** ERROR[8102] The operation is prevented by a unique constraint.
> --- 0 row(s) inserted.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)