Hey, I'm a lurker here and my response may be useless - but here goes:
As I understand the relational model, the result of 'select' is a set. And
that set only has an order after the set is defined and the ordering
criteria get applied. You are guaranteed that the elements 'a.n' will be
both unique and consistent across multiple executions of the same select
(on the same data). But I don't think there are guarantees about the order
of the selected field when the ordering is not based on that field.
I'd be interested to execute this:
select
a.n, b.m
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 /* with and without offset 1 */;
I'm not sure I can express the reason clearly, but having 'b.m' in the
result and ordering by 'b.m' will guarantee both repeatable elements and
repeatable order. And without that, all bets are off regarding the order.
In another aspect of your query, I'm not sure about the inner join. I
can't tell the nullability of any of your fields, so I suppose any of them
can be NULL. I'm not positive, but I think that an inner join excludes
records in which any field in the WHERE clause has a null value. Other
fields get included, even if a selected field is NULL. And I don't know
what happens in an 'order by' clause where the ordering field can contain
NULL values.
I look forward to a knowledgeable person's correction of any and all of
these ideas.
-- Bill
On Tuesday, November 19, 2013 10:06:58 AM UTC-5, hendrik wrote:
>
> 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.
