We're updating the issue view to help you get more done.Learn more

RAND() in a subselect : different behavior in MariaDB and MySQL

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 ?

Status