Hi,
please consider the following example:
CREATE TABLE A (
id long unique,
id_b long,
n int unique
);
CREATE TABLE B (
id long unique,
m int
);
A.id_b is a foreign key to B.id, but that's not too important for my
question.
What is important is that A.n is unique, but B.m is not. When I execute the
following query, I get an ordered list of A.n, ordered by the non-unique
B.m:
select
a.n
from
A as a
inner join
B as b
where
a.id = -8796892314892162149
and b.id = a.id_b
order by
b.m limit 100;
As an example, let's say the result was: {3,1,4,6,8}.
Now, because (in this example) I do paging with some very small pages, I
set an offset like this:
select
a.n
from
A as a
inner join
B as b
where
a.id = -8796892314892162149
and b.id = a.id_b
order by
b.m limit 100 offset 1;
I would expect to get {1,4,6,8}, however, I do not get the same order, but
something like {6,8,4,1}.
My guess is, that this is because B.m is not unique. In fact, all values
for B.m may be identical, so the returned order is still technically
correct.
My question is, is it wrong to assume that a join query like the one above,
returns results in the same order, even when different offsets are used and
the column I order by is not unique?
Thank you!
-hendrik
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
