Re: Possible bug: duplicate value order with non-unique indexes

[Dyre Tjeldvoll]

On 08/19/2014 08:43 PM, Jim Gray wrote:
I have a problem which I think is a bug, please let me know if you agree.

I don't, as there are NO guarantees about the order in which rows will 
be returned, unless you add an ORDER BY clause.* The existence of 
indices or comment overrides does not change that. If I understand your 
problem correctly you need to change your select to

SELECT * FROM t1 ORDER BY c2,c3

Then the returned rows will be returned in c2 order, with duplicates 
sorted according to c3

* This is very counter-intuitive as one would assume that the order in 
which rows were added to the table was implicitly recorded somewhere. 
But it is not.

HTH


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



--
Regards,

Dyre