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

[mike matrigali]

I agree with dyre and dag, there is no guarantee of order.  derby
does not store the rows inserted in any guaranteed order, and underlying 
architecture does not support it.  You must use appropriate order by.  The
underlying storage is a set of pages and a set of rows on the pages.
Unordered base table selects will give you back rows in page order and
in row order per page - but there is no guarantee that has anything
to do with order of insertion.
It is confusing from outside in as new tables with single user inserts
likely will put rows in order but soon many different factors may result
in what you are observing: space reclamation, efficient size use of
partially filled pages, multi-user insert contention, ...


On 8/21/2014 1:30 AM, Dyre Tjeldvoll wrote:
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