Details
Major
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 ?
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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;
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.
Fix of the bug sent for review.
pushed to 5.3
To answer the question whether it's a bug or not, we'll need an example of actual result you are getting, I'm not sure I understand the problem from the verbal description.
If it turns out to be a bug, we will also need structures of the involved tables (SHOW CREATE TABLE <table name>) and, if possible, the data dump would help as well.