need clarification on CTE/join

[Marc Millas <marc.millas@xxxxxxxxxx>]

Hi,

if I do:

postgres=# with numb as(select ceiling(2582*random()) rand, generate_series(1,5) as monnum) select rand, monnum from numb;
 rand | monnum
------+--------
 1543 |      1
 2299 |      2
  205 |      3
  523 |      4
  677 |      5
(5 lines)

ok, fine. The random numbers are at random...and the generate_series are ordered...

I have a table firstnames(id serial, firstname text) with 2582 lines containing firstnames sorted in alphabetical order.

--when I do, with the same CTE:

postgres=# with numb as(select ceiling(2582*random()) rand, generate_series(1,5) as monnum) select monnum, firstname from numb, firstnames where numb.rand= firstnames.id ;
 monnum |  firstname
--------+-----------
      2 | Christine
      1 | Firas
      4 | Firmin
      3 | Rawane
      5 | Titania
(5 lignes)

which mean that what I get is a set of firstnames ordered according to the firstnames table, and NOT to the result of the CTE.

--Now if I cast the result of the ceiling function to int:

postgres=# with numb as(select ceiling(2582*random())::int rand, generate_series(1,5) as monnum) select monnum, firstname from numb,firstnames where numb.rand=firstnames.id;
 monnum |  prenom
--------+----------
      1 | Dexter
      2 | Harrison
      3 | Angilbe
      4 | Narcisse
      5 | Marcel
(5 lignes)

Now its ordered according to the CTE. (and the firstname list is at random)

I did test the same thing after putting the result of the CTE in a table, with the very same behaviour.

So.. I would like to understand the "why" of this behaviour, ie. the change of order when I do the cast.

(The original reason was that I was creating a test env with millions of rows with this kind of CTE and was quite surprised to discover that the result table was ordered...which was not at all my goal)

thanks,

Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com