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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
