ArchLinux X86_64

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.

this is what I have when I execute this request on MySQL :
SELECT g.id AS id_galerie,
g.nom AS nom_galerie,
p.id AS id_peinture,
p.nom AS nom_peinture,
g.p_random AS r1,
g.p_random AS r2,
g.p_random AS r3
FROM
(
SELECT gal.id,
gal.nom,
(
SELECT pe.id
FROM peintures pe
WHERE pe.id_galerie = gal.id
ORDER BY RAND()
LIMIT 1
) AS p_random
FROM galeries gal
) g
LEFT JOIN peintures p
ON p.id = g.p_random
ORDER BY nom_galerie ASC

This is what I have when I execute this request on MariaDB :
SELECT g.id AS id_galerie,
g.nom AS nom_galerie,
p.id AS id_peinture,
p.nom AS nom_peinture,
g.p_random AS r1,
g.p_random AS r2,
g.p_random AS r3
FROM
(
SELECT gal.id,
gal.nom,
(
SELECT pe.id
FROM peintures pe
WHERE pe.id_galerie = gal.id
ORDER BY RAND()
LIMIT 1
) AS p_random
FROM galeries gal
) g
LEFT JOIN peintures p
ON p.id = g.p_random
ORDER BY nom_galerie ASC

Yes, of course.
The "galleries" table structure is :

CREATE TABLE IF NOT EXISTS `galleries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`year` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The "pictures" table structure is :
CREATE TABLE IF NOT EXISTS `pictures` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`width` float DEFAULT NULL,
`height` float DEFAULT NULL,
`year` int(4) DEFAULT NULL,
`technique` varchar(50) DEFAULT NULL,
`comment` varchar(2000) DEFAULT NULL,
`gallery_id` int(11) NOT NULL,
`type` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `gallery_id` (`gallery_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

ALTER TABLE `pictures`
ADD CONSTRAINT `pictures_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`);

Some data :

INSERT INTO `galleries` (`id`, `name`, `year`) VALUES
(1, 'Quand le noir et blanc invite le taupe', 2013),
(2, 'Une touche de couleur', 2012),
(3, 'Éclats', 2011),
(4, 'Gris béton', 2010),
(5, 'Expression du spalter', 2010),
(6, 'Zénitude', 2009),
(7, 'La force du rouge', 2008),
(8, 'Sphères', NULL),
(9, 'Centre', 2009),
(10, 'Nébuleuse', NULL);

INSERT INTO `pictures` (`id`, `name`, `width`, `height`, `year`, `technique`, `comment`, `gallery_id`, `type`) VALUES
(1, 'Éclaircie', 72.5, 100, NULL, NULL, NULL, 1, 1),
(2, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1),
(3, 'Nouveau souffle', 72.5, 100, NULL, NULL, NULL, 1, 1),
(4, 'Échanges (2)', 89, 116, NULL, NULL, NULL, 1, 1),
(5, 'Échanges', 89, 116, NULL, NULL, NULL, 1, 1),
(6, 'Fenêtre de vie', 81, 116, NULL, NULL, NULL, 1, 1),
(7, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1),
(8, 'Nouveau souffle (2)', 72.5, 100, NULL, NULL, NULL, 1, 1),
(9, 'Fluidité', 89, 116, NULL, NULL, NULL, 1, 1),
(10, 'Nouveau Monde', 89, 125, NULL, NULL, NULL, 1, 1),
(11, 'Mirage', 73, 100, NULL, NULL, NULL, 1, 1),
(12, 'Équilibre', 72.5, 116, NULL, NULL, NULL, 2, 1),
(13, 'Fusion', 72.5, 116, NULL, NULL, NULL, 2, 1),
(14, 'Étincelles', NULL, NULL, NULL, NULL, NULL, 3, 1),
(15, 'Régénérescence', NULL, NULL, NULL, NULL, NULL, 3, 1),
(16, 'Chaleur', 80, 80, NULL, NULL, NULL, 4, 1),
(17, 'Création', 90, 90, NULL, NULL, NULL, 4, 1),
(18, 'Horizon', 92, 73, NULL, NULL, NULL, 4, 1),
(19, 'Labyrinthe', 81, 100, NULL, NULL, NULL, 4, 1),
(20, 'Miroir', 80, 116, NULL, NULL, NULL, 5, 1),
(21, 'Libération', 81, 116, NULL, NULL, NULL, 5, 1),
(22, 'Éclats', 81, 116, NULL, NULL, NULL, 5, 1),
(23, 'Zénitude', 116, 89, NULL, NULL, NULL, 6, 1),
(24, 'Écritures lointaines', 90, 90, NULL, NULL, NULL, 7, 1),
(25, 'Émergence', 80, 80, NULL, NULL, NULL, 7, 1),
(26, 'Liberté', 50, 50, NULL, NULL, NULL, 7, 1),
(27, 'Silhouettes amérindiennes', 701, 70, NULL, NULL, NULL, 7, 1),
(28, 'Puissance', 81, 100, NULL, NULL, NULL, 8, 1),
(29, 'Source', 73, 116, NULL, NULL, NULL, 8, 1),
(30, 'Comme une ville qui prend vie', 50, 100, 2008, NULL, NULL, 9, 1),
(31, 'Suspension azur', 80, 80, NULL, NULL, NULL, 9, 1),
(32, 'Nébuleuse', 70, 70, NULL, NULL, NULL, 10, 1),
(33, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(34, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(35, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(36, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(37, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(38, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2);

See attached files (wrong and good results), to see what I have when I execute the request (the one with the 3 fields r1, r2 and r3)

Hi,

Thank you for the information. Yes, it's a bug.
As a workaround, you can set

optimizer_switch = 'derived_merge=off''

in your cnf file and/or client session.

I will put it all together again, just for convenience, no changes in the data or query comparing to the initial one apart from minor fixes in column names (nas => name).

Test case:

CREATE TABLE IF NOT EXISTS `galleries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`year` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `pictures` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`width` float DEFAULT NULL,
`height` float DEFAULT NULL,
`year` int(4) DEFAULT NULL,
`technique` varchar(50) DEFAULT NULL,
`comment` varchar(2000) DEFAULT NULL,
`gallery_id` int(11) NOT NULL,
`type` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `gallery_id` (`gallery_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

ALTER TABLE `pictures`
ADD CONSTRAINT `pictures_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`);

INSERT INTO `galleries` (`id`, `name`, `year`) VALUES
(1, 'Quand le noir et blanc invite le taupe', 2013),
(2, 'Une touche de couleur', 2012),
(3, 'Éclats', 2011),
(4, 'Gris béton', 2010),
(5, 'Expression du spalter', 2010),
(6, 'Zénitude', 2009),
(7, 'La force du rouge', 2008),
(8, 'Sphères', NULL),
(9, 'Centre', 2009),
(10, 'Nébuleuse', NULL);

INSERT INTO `pictures` (`id`, `name`, `width`, `height`, `year`, `technique`, `comment`, `gallery_id`, `type`) VALUES
(1, 'Éclaircie', 72.5, 100, NULL, NULL, NULL, 1, 1),
(2, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1),
(3, 'Nouveau souffle', 72.5, 100, NULL, NULL, NULL, 1, 1),
(4, 'Échanges (2)', 89, 116, NULL, NULL, NULL, 1, 1),
(5, 'Échanges', 89, 116, NULL, NULL, NULL, 1, 1),
(6, 'Fenêtre de vie', 81, 116, NULL, NULL, NULL, 1, 1),
(7, 'Architecture', 81, 100, NULL, NULL, NULL, 1, 1),
(8, 'Nouveau souffle (2)', 72.5, 100, NULL, NULL, NULL, 1, 1),
(9, 'Fluidité', 89, 116, NULL, NULL, NULL, 1, 1),
(10, 'Nouveau Monde', 89, 125, NULL, NULL, NULL, 1, 1),
(11, 'Mirage', 73, 100, NULL, NULL, NULL, 1, 1),
(12, 'Équilibre', 72.5, 116, NULL, NULL, NULL, 2, 1),
(13, 'Fusion', 72.5, 116, NULL, NULL, NULL, 2, 1),
(14, 'Étincelles', NULL, NULL, NULL, NULL, NULL, 3, 1),
(15, 'Régénérescence', NULL, NULL, NULL, NULL, NULL, 3, 1),
(16, 'Chaleur', 80, 80, NULL, NULL, NULL, 4, 1),
(17, 'Création', 90, 90, NULL, NULL, NULL, 4, 1),
(18, 'Horizon', 92, 73, NULL, NULL, NULL, 4, 1),
(19, 'Labyrinthe', 81, 100, NULL, NULL, NULL, 4, 1),
(20, 'Miroir', 80, 116, NULL, NULL, NULL, 5, 1),
(21, 'Libération', 81, 116, NULL, NULL, NULL, 5, 1),
(22, 'Éclats', 81, 116, NULL, NULL, NULL, 5, 1),
(23, 'Zénitude', 116, 89, NULL, NULL, NULL, 6, 1),
(24, 'Écritures lointaines', 90, 90, NULL, NULL, NULL, 7, 1),
(25, 'Émergence', 80, 80, NULL, NULL, NULL, 7, 1),
(26, 'Liberté', 50, 50, NULL, NULL, NULL, 7, 1),
(27, 'Silhouettes amérindiennes', 701, 70, NULL, NULL, NULL, 7, 1),
(28, 'Puissance', 81, 100, NULL, NULL, NULL, 8, 1),
(29, 'Source', 73, 116, NULL, NULL, NULL, 8, 1),
(30, 'Comme une ville qui prend vie', 50, 100, 2008, NULL, NULL, 9, 1),
(31, 'Suspension azur', 80, 80, NULL, NULL, NULL, 9, 1),
(32, 'Nébuleuse', 70, 70, NULL, NULL, NULL, 10, 1),
(33, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(34, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(35, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(36, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(37, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2),
(38, 'Œuvre commandée 120 P', 114, 195, NULL, NULL, NULL, 1, 2);

SELECT g.id AS gallery_id,
g.name 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
;

1. 1. 1. 1. End of test case

Expected result is 10 rows, ordered by the 2nd column (gallery name), no NULLs in 3rd or 4th columns since there is at least one picture for each gallery. Additionally, there are 3 columns r1, r2, r3 which select the exact same column and are supposed to be equal to each other and equal to the 3rd column.

Actual result on MySQL or on MariaDB without derived_merge satisfies the condition above, e.g.

gallery_id gallery_name picture_id picture_name r1 r2 r3
3 Éclats 14 Étincelles 14 14 14
9 Centre 31 Suspension azur 31 31 31
5 Expression du spalter 22 Éclats 22 22 22
4 Gris béton 17 Création 17 17 17
7 La force du rouge 27 Silhouettes amérindiennes 27 27 27
10 Nébuleuse 32 Nébuleuse 32 32 32
1 Quand le noir et blanc invite le taupe 37 Œuvre commandée 120 P 37 37 37
8 Sphères 28 Puissance 28 28 28
2 Une touche de couleur 13 Fusion 13 13 13
6 Zénitude 23 Zénitude 23 23 23

(actual values in columns 3-7 might differ).

Result with derived_merge has several problems:

• it does not always return all rows;
• it has NULLs in 3rd and 4th columns;
• r1, r2, r3 can be different.

Example:

gallery_id gallery_name picture_id picture_name r1 r2 r3
3 Éclats NULL NULL 15 14 14
9 Centre NULL NULL 31 30 31
4 Gris béton NULL NULL 17 18 19
10 Nébuleuse 32 Nébuleuse 32 32 32
8 Sphères 28 Puissance 29 29 29
2 Une touche de couleur NULL NULL 13 12 12
6 Zénitude 23 Zénitude 23 23 23

I wasn't able to reproduce it with a MERGE view instead of the FROM subquery, although maybe I was doing it in a wrong way.

It is problem of derived tables merging (which Mysql does not have so no problem there).

Workaround is
set optimizer_switch='derived_merge=off';

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