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
-
Activity
Actually, materialization could be forced is a VIEW or a DERIVED TABLE uses rand() and other side effect function, but I can't recall why it was not done for views...
MySQL 5.6 does not have this bug with derived tables, because it doesn't implement the optimization of merging derived tables.
But MySQL 5.6 shows exactly the same bug with views.
This is a bug if different r1,r2,r3 is a bug in following example:
create table t1 (a int);
insert into t1 values (1),(2);
create view v1 (a,r) as select a,rand() from t1;
select a, r as r1, r as r2, r as r3 from v1;
drop view v1;
drop table t1;