Hi Suresh, Good to know that the unique constraint is still maintained.
Thank you. --Qifan On Tue, Feb 2, 2016 at 5:03 PM, Suresh Subbiah <[email protected]> wrote: > Hi , > > Uniqueness constraints are still maintained with nullable columns in the > clustering key. For example > > CREATE TABLE TRAFODION.JIRA.T1 > ( > A INT DEFAULT NULL SERIALIZED > , B INT DEFAULT NULL SERIALIZED > , PRIMARY KEY (A ASC, B ASC) > ) > ; > > >>insert into t1 values (1, null) ; > > --- 1 row(s) inserted. > >>insert into t1 values (1, null) ; > > *** ERROR[8102] The operation is prevented by a unique constraint. > > --- 0 row(s) inserted. > >>select * from t1 where a = 1 and b is null ; > > A B > ----------- ----------- > > 1 ? > > --- 1 row(s) selected. > > Here is the explain plan for this query, indicating that correct begin-key, > end-key is generated. > > TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN > TABLE_NAME ............... T1 > REQUESTS_IN .............. 1 > ROWS_OUT ................. 1 > EST_OPER_COST ............ 0.01 > EST_TOTAL_COST ........... 0.01 > DESCRIPTION > max_card_est ........... 1 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > scan_type .............. subset scan of table TRAFODION.JIRA.T1 > object_type ............ Trafodion > cache_size ........... 100 > probes ................. 1 > rows_accessed .......... 1 > column_retrieved ....... #1:1,#1:2 > key_columns ............ A, B > executor_predicates .... B is null and (A = %(1)) > begin_key .............. (A = %(1)), (B = NULL) > end_key ................ (A = %(1)), (B = NULL) > > > I do not know if there are remaining places in our code where we assume > that a key predicate will NOT contain <key-col> IS NULL type predicates. > For most part I think we are OK and hopefully remaining issues can be > treated as bugs. > > On Tue, Feb 2, 2016 at 4:18 PM, Qifan Chen <[email protected]> wrote: > > > The unique constraint probably could be broken during the execution. > > > > For example, Probe-cache requires at most one row returned per probe and > a > > violation could mean more than one row is returned. Currently, the cache > is > > not built for such condition. > > > > Thanks --Qifan > > > > On Tue, Feb 2, 2016 at 4:09 PM, Eric Owhadi <[email protected]> > wrote: > > > > > Can you elaborate on this? > > > As I understand Suresh message, even with NULLABLE columns, we still > > assume > > > uniqueness of the concatenation of all column making up the PK. > > > So your sentence "a full PK will map to up to one row" is still > > respected? > > > Or am I misunderstanding? > > > Eric > > > > > > -----Original Message----- > > > From: Qifan Chen [mailto:[email protected]] > > > Sent: Tuesday, February 2, 2016 4:05 PM > > > To: dev <[email protected]> > > > Subject: Re: [DISCUSS] Change the default setting of > > > ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT to ON > > > > > > Allowing NULL in primary key columns could break some existing > > assumptions > > > in our code base on PKs. For example, a full PK will map to up to one > > row, > > > which is a logic constraint that the compiler takes full advantage of. > > > > > > Thanks --Qifan > > > > > > On Tue, Feb 2, 2016 at 4:01 PM, Dave Birdsall <[email protected] > > > > > wrote: > > > > > > > ANSI in general does not concern itself with physical issues. So no > > > > partitioning, no indexing, and so on in ANSI. > > > > > > > > > > > > -----Original Message----- > > > > From: Eric Owhadi [mailto:[email protected]] > > > > Sent: Tuesday, February 2, 2016 1:59 PM > > > > To: [email protected] > > > > Subject: RE: [DISCUSS] Change the default setting of > > > > ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT to ON > > > > > > > > Great, so my 2 cts is that we should not worry about ANSI on the > > > > default not supporting NULL, since ANSI did not account for > > > > partitioning... > > > > Eric > > > > > > > > -----Original Message----- > > > > From: Roberta Marton [mailto:[email protected]] > > > > Sent: Tuesday, February 2, 2016 3:55 PM > > > > To: [email protected] > > > > Subject: RE: [DISCUSS] Change the default setting of > > > > ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT to ON > > > > > > > > Store by is not ANSI standard. > > > > > > > > Roberta > > > > > > > > -----Original Message----- > > > > From: Eric Owhadi [mailto:[email protected]] > > > > Sent: Tuesday, February 2, 2016 1:53 PM > > > > To: [email protected] > > > > Subject: RE: [DISCUSS] Change the default setting of > > > > ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT to ON > > > > > > > > Do you happen to know if STORE BY is part of the ANSI standard, and > if > > > > ANSI mandate that anything in STORE BY must be part of the PK? > > > > Eric > > > > > > > > -----Original Message----- > > > > From: Dave Birdsall [mailto:[email protected]] > > > > Sent: Tuesday, February 2, 2016 3:45 PM > > > > To: [email protected] > > > > Subject: RE: [DISCUSS] Change the default setting of > > > > ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT to ON > > > > > > > > Our default behavior would not be ANSI standard if this is changed. I > > > > don’t know if that is a compelling reason not to do this though. > > > > > > > > Dave > > > > > > > > -----Original Message----- > > > > From: Suresh Subbiah [mailto:[email protected]] > > > > Sent: Tuesday, February 2, 2016 1:37 PM > > > > To: [email protected] > > > > Subject: [DISCUSS] Change the default setting of > > > > ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT to ON > > > > > > > > Hi, > > > > > > > > Do you foresee potential problems if we change the default setting > > > > for ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT to ON. ? > > > > It does seem that users are asking for using and setting this CQD in > > > > their individual applications. > > > > Other than JIRA 1801, there are no known issues when this attribute > is > > > > set to ON. > > > > Having the default set to ON could make Trafodion easier to use. > > > > > > > > I can file a JIRA if there is agreement or if we think the discussion > > > > should be moved to a JIRA. > > > > > > > > Thank you > > > > Suresh > > > > > > > > > > > > > > > > -- > > > Regards, --Qifan > > > > > > > > > > > -- > > Regards, --Qifan > > > -- Regards, --Qifan
