I have 2 tables : galeries (id, name) and pictures (id, name, galery_id which is a FK to galeries).
If I want to select all galeries and, for each, randomly select one of the related pictures, I do this request :
SELECT g.id AS gallery_id,
|
g.nas AS gallery_name,
|
p.id AS picture_id,
|
p.name AS picture_name
|
FROM
|
(
|
SELECT gal.id,
|
gal.name,
|
(
|
SELECT pi.id
|
FROM pictures pi
|
WHERE pi.gallery_id = gal.id
|
ORDER BY RAND()
|
LIMIT 1
|
) AS p_random
|
FROM galleries gal
|
) g
|
LEFT JOIN pictures p
|
ON p.id = g.p_random
|
ORDER BY gallery_name ASC
|
In MySQL, this request works godd : I have a picture randomly selected, and I have all information (like the name). But, in MariaDB, no. RAND() and g.p_random are not equals !
If I modify my request like this :
SELECT g.id AS gallery_id,
|
g.nas AS gallery_name,
|
p.id AS picture_id,
|
p.name AS picture_name,
|
g.p_random AS r1,
|
g.p_random AS r2,
|
g.p_random AS r3
|
FROM
|
(
|
SELECT gal.id,
|
gal.name,
|
(
|
SELECT pi.id
|
FROM pictures pi
|
WHERE pi.gallery_id = gal.id
|
ORDER BY RAND()
|
LIMIT 1
|
) AS p_random
|
FROM galleries gal
|
) g
|
LEFT JOIN pictures p
|
ON p.id = g.p_random
|
ORDER BY gallery_name ASC
|
r1, r2 and r3 have the same value in MySQL, but not in MariaDB.
|
Is it a bug or is it a normal behavior ?
Bug
Major
