Unique index in local temporary table deletes conflicting rows

[VolkerBarth]

I came across that peculiar bug in V16.0.0.2798:

I'm using a local temporary table with a UNIQUE index to guarantee uniqueness. I cannot use a UNIQUE KEY because part of the values are allowed to be NULL, so I'm using an index with the WITH NULLS NOT DISTINCT clause.

Now, when updating several rows that would violate the uniqueness, those rows are silently deleted by the UPDATE statement, which is of course unexpected. (Aside: In my original code, I used an ordered UPDATE so rows would be modified "in correct order" – cf. the last remark – but that issue appears there, as well.)

drop table if exists LT_Test;
create local temporary table LT_Test (
   pk int primary key default autoincrement,
   Nr varchar(30) not null,
   NrSuffix varchar(5) null
) not transactional;

-- The combination of Nr and NrSuffix should be unique.
-- As NrSuffix is NULLable, an UNIQUE KEY is not allowed.
-- Therefore choose UNIQUE INDEX WITH NULLS NOT DISTINCT instead
create unique index I_LT_Test on LT_Test (Nr, NrSuffix)
   with nulls not distinct;

-- Insert 9 rows with Nr from 1 to 3 and NrSuffix in (NULL, '*2', '*3)
insert LT_Test(Nr, NrSuffix)
select SP1.row_num, if SP2.row_num > 1 then '*' || SP2.row_num else null end if as NrSuffix
from sa_rowgenerator(1, 3) SP1 cross apply sa_rowgenerator(1, 3) SP2
order by 1, 2;

select * from LT_Test
order by 1;

/*
-- lists
pk,Nr,NrSuffix
1,'1',
2,'1','*2'
3,'1','*3'
4,'2',
5,'2','*2'
6,'2','*3'
7,'3',
8,'3','*2'
9,'3','*3'
*/

-- update NrSuffix to a different value succeeds
update LT_Test
set NrSuffix = '*4'
where NrSuffix = '*3';

-- update NrSuffix to existing values fails with SQLCODE -196 as expected
-- (Index 'I_LT_Test' for table 'LT_Test' would not be unique)
update LT_Test
set NrSuffix = '*2'
where NrSuffix = '*4'

-- But also silently deletes the possibly conflicting rows
--> now rows with pk 3, 6 and 9 are lost

With a regular permanent table, the last statement is also refused, but as expected, the conflicting rows are left unmodified, i.e. that UPDATE is rolled back.

I have the impression that the particular strategy to temporarily allow non-uniquness during a single UPDATE statement as described here in step 6 does not work as expected with temporary tables.

[VolkerBarth]

Just to add:

The following "NrSuffix" switch operation works in this simple test but deletes rows in my real code:

-- switch Suffixes for each Nr,
-- i.e. pk 2 will get NrSuffix = '*3' and pk 3 = '*2*
update LT_Test
set NrSuffix = '*' || NewSuffix + 1
-- select *
from LT_Test inner join
   (select pk,
       row_number() over (partition by Nr order by pk desc) as NewSuffix
    from LT_Test) DT
   on LT_Test.pk = DT.pk
where NrSuffix is not null;

[former_SQLA_member1694885]

Hi Volker,

I rerun your repro in 17.0.9.4897 and experienced the same behavior. The key to the solution may be hidden in your question:

With a regular permanent table, the last statement is also refused, but as expected, the conflicting rows are left unmodified, i.e. that UPDATE is rolled back.

The same is true (on my sandbox) for local temp tables with "on commit preserve rows" and "on commit delete rows". So the relevant bit may be the "not transactional" directive, which means that the "...UPDATE is rolled back" may not lead to the desired result.
I wouldn't claim that the observed behavior is correct / desired. But by declaring "not transactional", you're leaving the usual consistency rules behind.
It would be interesting to check the ATOMIC clause for compound statements on such temp tables, but at the moment, I'm lacking the time for that.

HTH anyway
Volker

[VolkerBarth]

Thanks for the profound feedback, much appreciated!

FWIW, I have chosen a different approach, so the issue does not appear anymore. Otherwise I would have used the "ON COMMIT PRESERVE ROWS" clause as suggested by you. For typical use cases like the one I'm working on (i.e. importing/loading and transforming data), I generally prefer to use local temporary tables with NOT TRANSACTIONAL, mainly because ON COMMIT DELETE ROWS wouldn't work because of LOAD TABLE's automatic commits, and because of better performance compared to ON COMMIT PRESERVE ROWS. But of course better performance combined with wrong semantics is pointless:)