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
>
