Hi,
I am not sure this can be considered a bug. The SQL model is relational,
and a priori rows can
appear in any order unless the query specifies an ORDER BY.
The index in this case is on c2 only, so as for the order of c1, bets
are off.
If you want the rows to come back sorted on c1 as well you could make a
compound index on (c2, c1).
Thanks,
Dag
On 19. aug. 2014 20:43, Jim Gray wrote:
I have a problem which I think is a bug, please let me know if you agree.
Also, has anyone else has encountered it and developed a workaround?
The problem deals with the ordering of duplicate values within a
non-unique index.
I have an application that depends upon duplicates occurring in
creation order,
but, it appears that the order is different for INSERT versus UPDATE.
In my test case below, INSERT always puts the latest duplicate value last.
However, when an UPDATE creates a new duplicate value, it is placed
first in index order.
The data is randomized with unique values for the first column, and
duplicate values in the second column.
The third column has the encounter order of the duplicate value.
create table t1(c1 int, c2 int, c3 int);
create unique index t1_i1 on t1(c1);
create index t1_i2 on t1(c2);
insert into t1 values(7, 3, 1);
insert into t1 values(96, 2, 1);
insert into t1 values(44, 5, 1);
insert into t1 values(95, 2, 2);
insert into t1 values(68, 1, 1);
insert into t1 values(65, 3, 2);
insert into t1 values(62, 1, 2);
insert into t1 values(84, 3, 3);
insert into t1 values(55, 5, 2);
insert into t1 values(5, 3, 4);
insert into t1 values(25, 5, 3);
insert into t1 values(21, 7, 1);
insert into t1 values(45, 9, 1);
insert into t1 values(43, 0, 1);
insert into t1 values(71, 4, 1);
insert into t1 values(33, 4, 2);
insert into t1 values(24, 1, 3);
insert into t1 values(91, 0, 2);
insert into t1 values(60, 1, 4);
insert into t1 values(39, 3, 5);
select * from t1 -- DERBY-PROPERTIES index = t1_i2
;
> C1 |C2 |C3
-----------------------------------
43 |0 |1
91 |0 |2
68 |1 |1
62 |1 |2
24 |1 |3
60 |1 |4
96 |2 |1
95 |2 |2
7 |3 |1
65 |3 |2
84 |3 |3
5 |3 |4
39 |3 |5
71 |4 |1
33 |4 |2
44 |5 |1
55 |5 |2
25 |5 |3
21 |7 |1
45 |9 |1
update t1 set c2 = 5, c3 = 0 where c1 = 7;
select * from t1 -- DERBY-PROPERTIES index = t1_i2
;
> C1 |C2 |C3
-----------------------------------
43 |0 |1
91 |0 |2
68 |1 |1
62 |1 |2
24 |1 |3
60 |1 |4
96 |2 |1
95 |2 |2
65 |3 |2
84 |3 |3
5 |3 |4
39 |3 |5
71 |4 |1
33 |4 |2
*7 |5 |0 <- First in duplicate order after update*
44 |5 |1
55 |5 |2
25 |5 |3
21 |7 |1
45 |9 |1
delete from t1 where c1 = 7;
insert into t1 values(7,5,0);
select * from t1 -- DERBY-PROPERTIES index = t1_i2
;
> C1 |C2 |C3
-----------------------------------
43 |0 |1
91 |0 |2
68 |1 |1
62 |1 |2
24 |1 |3
60 |1 |4
96 |2 |1
95 |2 |2
65 |3 |2
84 |3 |3
5 |3 |4
39 |3 |5
71 |4 |1
33 |4 |2
44 |5 |1
55 |5 |2
25 |5 |3
*7 |5 |0 <- Last in duplicate order after insert*
21 |7 |1
45 |9 |1
