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
