Just a comment on what the "uniqueness constraint" means: In Trafodion, two rows (1, NULL) and (1, NULL) are considered duplicates and are rejected. That's probably ok and what many (most?) people mean. However, the SQL standard defines uniqueness with nullable columns differently, any row with a NULL value in the unique columns is considered unique since it cannot be proven to be non-unique.
Again, just a comment, I don't think anyone is planning to implement ANSI/ISO uniqueness semantics. Thanks, Hans On Tue, Feb 2, 2016 at 5:07 PM, Qifan Chen <[email protected]> wrote: > 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 >
