Combination of "insert ... on existing update ..." and computed column in pk

[Chris26]

Lets say we have a table like this:

create table dba.t1
(
  c1 integer not null compute(c2 * 1000 + c3),
  c2 smallint not null,
  c3 tinyint not null,
  c4 varchar(10),
  primary key (c1)
);


And we insert some records into it:
insert into dba.t1 (c2, c3, c4) values (2014, 1, 'abc');
insert into dba.t1 (c2, c3, c4) values (2014, 2, 'def');


And now we want to do:
insert into dba.t1 (c2, c3, c4) on existing update values (2014, 1, 'zzz');


I was expecting that this would update the c4 column to 'zzz' in the row where c1 = 2014001. However this is what happens:
Could not execute statement.
Primary key for table 't1' is not unique: Primary key value ('2014001')
SQLCODE=-193, ODBC 3 State="23000"
Line 1, column 1


I couldn't find anything in the help about this combination.
Is this a bug or is this limitation missing in the documentation?
Tested with version 16.0.0.2038

[VolkerBarth]

I guess you have found a subtle problem/feature in the order of sub-operations an insert statement has to do:

It seems the PK lookup does not take the computed PK into consideration.

That's wild-guessing, FWIW, I have once run into a somewhat similar problem with computed columns and before update triggers (and into a great discussion with Glenn), cf. that ooold NNTP thread from 2007:

Before update trigger and computed column, particularly Glenn's list of tasks...

Note, that list does not name when an INSERT On EXISTING would do the PK match look-up, and the order may have been changed for newer versions - at least the interference between computed columns and triggers has been modified with 11.0.0... - However, I remember I was very impressed (and somewhat "frightened") of the very details the server has to handle in the correct order:)

It might be interesting to check whether the MERGE statement would behave identically.

[MarkCulp]

The underlying issue here is that the COMPUTE expression values are not computed until AFTER the insert has occurred. I.e. the server first looks at each row using the original supplied values and makes a determination on whether there is going to be a collision, and if not then it does the insert. After the insert the compute clause is triggered and the value of the column is computed. In your example the computed values are causing a PK violation and hence the error that is raised.

Volker's answer using the MERGE statement is a nice solution to what you are trying to do.

[VolkerBarth]

In contrast to INSERT ON EXISTING, the MERGE statement allow you to specify the match condition (as discussed here) so you can define it based on the compute expression.

The following statements (based on your sample and the two initial INSERTs) will do both an update and an insert:

merge into dba.t1 (c2, c3, c4)
   using (select 2014, 1, 'zzz') as s1 (c2, c3, c4)
   on t1.c2 = s1.c2 and t1.c3 = s1.c3
   when not matched then insert 
   when matched then update;

merge into dba.t1 (c2, c3, c4)
   using (select 2014, 3, 'ghi') as s1 (c2, c3, c4)
   on t1.c2 = s1.c2 and t1.c3 = s1.c3
   when not matched then insert 
   when matched then update;

select * from dba.t1 order by 1;

will return:

c1, c2, c3, c4
2014001, 2014, 1, 'zzz'
2014002, 2014, 2, 'def'
2014003, 2014, 3, 'ghi'

---

Here I have used an equality comparison of columns c2 and c3 but you could use the compute expression itself, too, i.e. "on t1.c1 = s1.c2 * 1000 + s1.c3".

[Chris26]

This would do as a work around, but it's not the answer to the question. I think the answer should be that there is a limitation that is missing in the docs and that it will be added there.

[VolkerBarth]

I agree on your point of view - even more as the MERGE statement is certainly more difficult to write and read than INSERT ON EXISTING for a basically very simple statement like this one...

---

That being said, a different solution might be to change the table definition by

1. using c2/c3 as a composite primary key and
2. either getting rid of the computed column altogether or using that only as a UNIQUE key for FK relationships (as composite PKs have their drawbacks, too).

[Former Member]

I haven't visited the MERGE potentional here but this is what I can see about this.

Columns c2 and c3 are not the primary key and not a uniqueness constraint so your insert-on-existing-update operation is equivalent of these two statements

//insert into dba.t1 (c2, c3, c4) on existing update values (2014, 1, 'zzz');


insert into dba.t1 (c2, c3, c4) values (2014, 1, 'zzz');
update dba.t1 set c2=2014, c3=1, c4='zzz'; // conditionally executed iff the first fails

both of which will throw the same exception. The update takes that 'equivalent' form since there is nothing special about either c3 or c4 and so must be included into the SET-CLAUSE.

You can see that from adding those 2 columns as a table uniqueness constraint as follows:

 create table dba.t1  
 (  c1 integer not null compute(c2 * 1000 + c3),  
    c2 smallint not null,  
    c3 tinyint not null,  
    c4 varchar(10),  
    primary key (c1),  
    /***/unique (c2,c3)/***/ 
 );


and now the parser knows there is something special about c2 and c3 when it forms the update operation.
While not much of a workaround, hopefully that explains the behavior more clearly.

[VolkerBarth]

So you say "ON EXISTING" would also work with UNIQUE constraints, not necessarily with PRIMARY KEYs? - I'm asking as the feature is documented otherwise:

If you specify the ON EXISTING clause, the database server performs a primary key lookup for each input row.

[Chris26]

As far as I can tell I'm seeing the exact same behaviour with or without the unique constraint

[Former Member]

Christian, you are correct that the unique constraint is (alone) not sufficient to make the insert-on-existing statement (or the equivalent insert+update operations) work.

Volker, you are correct. If columns c2 and c3 were either the definition for the primary key then the parser would know those should be treated as being "special".

Without that the parser does not know is that those columns are effectively the primary key columns. Since the primary key designation belongs to a computed column (c1) definition the required "special meaning" of the computation is not transferred to (nor inherited by) the columns that computation is based upon.

[VolkerBarth]

To ask it the other way:

Does ON EXISTING only work when the PK value is explicitly given in the INSERT statement?

IMHO this assumption seems to be valid as both exceptions with omitted PK values won't work:

• A computed PK seems to fail, as stated in the previous samples.
• If, on the other hand, the PK column is defined by a DEFAULT, this will usually be a default that creates a different PK value each time (say, for AUTOINCREMENT / GETDATE() type defaults) so there will not be an existing matching PK, and ON EXISTING will therefore do a normal INSERT.

---

Theoretical exception: In case you have a PK with a DEFAULT with a constant expression and INSERT a row with that default used (by omitting the column in the INSERT statement), then this PK lookup will be successful as in the following sample:

create table dba.t2
(
  c1 integer not null default (2014 * 1000 + 1),
  c2 smallint not null,
  c3 tinyint not null,
  c4 varchar(10),
  primary key (c1)
);

insert into dba.t2 (c1, c2, c3, c4) values (default, 2014, 1, 'abc');
insert into dba.t2 (c1, c2, c3, c4) values (2014002, 2014, 2, 'def');
select * from t2;
-- next insert will find the matching default PK
insert into dba.t2 (c1, c2, c3, c4) on existing update values (default, 2014, 1, 'zzz');
-- next insert will again find the matching default PK
insert into dba.t2 (c2, c3, c4) on existing update values (2014, 1, 'xyz');

Nevertheless, that seems rather useless...

Note that a DEFAULT cannot reference other database objects, so you cannot use it to express the compute expression of t1.