Hi Eric,
A JIRA will be nice for this failure. These predicates are generated and I
have made a mistake in not considering ISNULL predicates here. A fix will
be straightforward.
>>upsert into t values(null,2);
*** ERROR[4099] A NULL operand is not allowed in predicate
(TRAFODION.TPCDSGOOD.T.A = NULL).
The following issue is likely caused due to JIRA 1803. I think the upsert
did not add a row to both the table and the index. Only one worked. The
following select is likely going against the object that was not inserted
into.
A fix for 1803 will likely address this
>>upsert into t values(1,1);
--- 1 row(s) inserted.
>>select * from t;
A B
----------- -----------
? 1
--- 1 row(s) selected.
Thanks
Suresh
On Wed, Feb 3, 2016 at 1:56 PM, Eric Owhadi <[email protected]> wrote:
> Hi Dave
>
> I am aware of the implication you mention. But for BI scenario, we don’t
> use key access for single row retrieval and we can always make sure we can
> supply the partitioning column. So no big deal. I agree that the limitation
> on STORE BY would be more elegant, since it will not force a non-natural PK
> implementation aimed at partitioning. But I guess that’s no big deal. Also
> in BI scenario, we can always manually deal with the right semantic in the
> load phase without using upsert…
>
> I am more worried about the issue discovered when trying to understand
> what scenario was implemented…
>
> Eric
>
>
>
>
>
>
>
> *From:* Dave Birdsall [mailto:[email protected]]
> *Sent:* Wednesday, February 3, 2016 1:16 PM
> *To:* [email protected]
> *Subject:* RE: question on nullable column in primary key and impact on
> upsert
>
>
>
> Hi Eric,
>
>
>
> Seems like this business of adding a nullable column to the primary key
> for partitioning purposes creates a lot of headaches. By doing so, it
> changes the definition of the primary key for the table. But then it
> appears in the UPSERT scenario, you really wanted the old primary key, not
> the new. I’m wondering if this is just the first exposure while opening a
> can of worms.
>
>
>
> Another approach might be to broaden STORE BY to allow it to take
> non-primary-key columns. This would come at a price in performance though.
> Unique key operations would now have to check all partitions, unless the
> non-primary-key value was supplied (e.g. through a WHERE clause or a VALUES
> clause). But you are going to run into this limitation anyway in your
> schema design. When you want the old primary key semantic, you’ll face
> similar performance issues.
>
>
>
> Dave
>
>
>
> *From:* Eric Owhadi [mailto:[email protected]]
> *Sent:* Wednesday, February 3, 2016 10:43 AM
> *To:* [email protected]
> *Subject:* question on nullable column in primary key and impact on upsert
>
>
>
> This following question is about checking if adding nullable columns on PK
> for the sake of data partitioning can be done without too much impact on
> initial PK uniqueness semantic… specially for upsert statements…
>
>
>
> If I have a composite primary key with column A and B.
>
> A nullable
>
> B non nullable and unique
>
>
>
> I was wondering if the upsert statement would correctly guess the intend
> and given:
>
> Null,1 in the table
>
> If I upsert (1,1), it will treat it as an update and not an insert given
> the unique constraint on column B
>
>
>
> Without the unique constraint on column B, I would expect an insert…
>
>
>
> This is important to keep the semantic of PK, while artificially adding
> composite column to force data partitioning…
>
>
>
> The same question if I have now a PK like:
>
> A nullable
>
> B non nullable
>
> C non nullable
>
>
>
> And (B,C) constraint to UNIQUE
>
> Will upsert do the PK semantic as if PK was B,C only?
>
>
>
> OK, so instead of asking, I was thinking, let’s try:
>
> And here you go:
>
>
>
> >>create table t(a int, b int not null not droppable unique, primary
> key(a,b));
>
>
>
> --- SQL operation complete.
>
> >>insert into t values(null,1);
>
>
>
> --- 1 row(s) inserted.
>
> >>select * from t;
>
>
>
> A B
>
> ----------- -----------
>
>
>
> ? 1
>
>
>
> --- 1 row(s) selected.
>
> >>upsert into t values(null,2);
>
>
>
> *** ERROR[4099] A NULL operand is not allowed in predicate
> (TRAFODION.TPCDSGOOD.T.A = NULL).
>
>
>
> *** ERROR[8822] The statement was not prepared.
>
>
>
> >>upsert into t values(2,2);
>
>
>
> --- 1 row(s) inserted.
>
> >>upsert into t values(1,1);
>
>
>
> --- 1 row(s) inserted.
>
> >>select * from t;
>
>
>
> A B
>
> ----------- -----------
>
>
>
> ? 1
>
>
>
> --- 1 row(s) selected.
>
> >>insert into t values (2,2);
>
>
>
> *** ERROR[8102] The operation is prevented by a unique constraint.
>
>
>
> --- 0 row(s) inserted.
>
> >>select * from t;
>
>
>
> A B
>
> ----------- -----------
>
>
>
> ? 1
>
>
>
> --- 1 row(s) selected.
>
> >>select * from t where b=2;
>
>
>
> --- 0 row(s) selected.
>
> >>insert into t values (3,3);
>
>
>
> --- 1 row(s) inserted.
>
> >>select * from t;
>
>
>
> A B
>
> ----------- -----------
>
>
>
> ? 1
>
> 3 3
>
>
>
> --- 2 row(s) selected.
>
>
>
> So 2 questions after this test:
>
> - Shouldn’t upsert try to use special null semantic instead of
> failing on predicate evaluation?
>
> - Look like upsert of 2,2 succeeded, but next select * does not
> show it… however, trying to insert (2,2) fails due to unique key
> constraint… so look like the upsert worked half way…
>
> - Why would the second upsert says there was an insert, but the
> next select * statement shows that neither insert or update was performed?
>
>
>
> I guess I should file a JIRA… but what is the expected behavior?
>
>
>
> Eric
>
>
>
>
>
>
>
