Re: Random Weighted Result Ordering

Eliot Gable wrote:
> rows. Basically, I thought that if the original data was:
>
> 50, 1, 5, 'data1'
> 55, 1, 4, 'data2'
> 34, 2, 0, 'data3'
> 90, 2, 1, 'data4'
> 95, 2, 1, 'data5
>
> And the input to the functions was:
>
> 50, 1, 5
> 55, 1, 4
> 34, 2, 0
> 90, 2, 1
> 95, 2, 1
>
> And the prioritized and weighted order came back:
>
> 50, 1, 5
> 55, 1, 4
> 95, 2, 1
> 90, 2, 1
> 34, 2, 0
>
> Then, if I INNER JOINED them like:
>
> (
>    50, 1, 5
>    55, 1, 4
>    95, 2, 1
>    90, 2, 1
>    34, 2, 0
> ) AS randomized INNER JOIN (
>    50, 1, 5, 'data1'
>    55, 1, 4, 'data2'
>    34, 2, 0, 'data3'
>    90, 2, 1, 'data4'
>    95, 2, 1, 'data5
> ) AS data ON (
> randomized.id <http://randomized.id> = data.id <http://data.id>
> )
>
> Then the rows would come back as:
>
> 50, 1, 5, 'data1'
> 55, 1, 4, 'data2'
> 95, 2, 1, 'data5'
> 90, 2, 1, 'data4'
> 34, 2, 0, 'data3
>
> Unfortunately, that does not seem to be happening. Before I spend a ton
> of time digging into this issue, I thought I would pose the questions here:
>
> Does anyone know for certain why this would not work? Or, does anyone
> know why this should work?

It should not "work" in the sense you mean, but it does "work" in the way that
SQL promises, namely that the order can be anything if you omit an ORDER BY
clause in the SELECT.

> I assumed that the order of the joins would preserve the ordering of the
> first set of data. However, I am worried about how the planner might
> re-arrange the joins on me, and I am wondering whether the order is
> guaranteed to be preserved like this in the first place... Does anyone
> know for sure about these assumptions?

SELECT makes no promise about the order of returned rows absent an ORDER BY
clause.  Your query lacks an ORDER BY clause.  You could therefore get any
order back, including the possibility of different orders from different runs
of the same query.

Tables in a relational database have no inherent order.  You took no steps
whatsoever to guarantee the order of rows returned by the SELECT, so you
should not be surprised at any order that comes back.

--
Lew