Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.34, 10.0.6
-
None
-
ArchLinux X86_64
Description
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 ?
Attachments
Issue Links
- relates to
-
-
- Confirmed
-