jeudi 23 novembre 2017

MYSQL Retrieving random tuple always returns same pattern

I am trying to populate a table by generating random relations by foreign keys and I'm using this code:

delimiter //
CREATE DEFINER = 'root'@'localhost'
PROCEDURE populate()
BEGIN
  DECLARE i INT default 1;
  declare quantity int default 0;
  DECLARE j INT default 1;
  while i <= 1500 do
    set j = floor(RAND()*(5)+1);
    while j <= 10 do
        set quantity = floor(RAND()*(900)+100);
        select @product := idProduct from Product where idProduct
            not in (select idProduct from Parcel where idOrder = i) order by RAND() limit 1;
      insert into DB.Table(idProduct, idOrder, quantity) values (@product, i, quantity);
      set j = j + 1;
    end while;
    set i = i + 1;
  end while;
    END ; //
delimiter ;

What I am expecting as a result is something like this:

idProduct   idOrder
1           1
5           1
9           1
8           1
7           1
6           1
2           2
1           2
3           2
8           2
7           2

But I always get values in idProduct descending like this:

idProduct   idOrder
11          1
10          1
9           1
8           1
7           1
6           1
11          2
10          2
9           2
8           2
7           2

What could be the problem?




Aucun commentaire:

Enregistrer un commentaire