Forum Programmation.SQL RAND() dans un SubSelect : comportement étrange

Posté par . Licence CC by-sa
1
7
déc.
2013

Bonjour,
J'ai 2 tables (galeries et peintures) avec des champs tout ce qu'il y a de plus classiques (id et nom pour l'une, id, nom et id_galerie pour l'autre) et je cherche à récupérer la liste de mes galeries et, pour chacune d'elles, une des peintures qui lui sont rattachées, choisie aléatoirement.
J'ai donc la requête suivante :

SELECT  g.id AS id_galerie,
        g.nom AS nom_galerie,
        p.id AS id_peinture,
        p.nom AS nom_peinture
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

Et là, c'est le drame ! Cette requête, exécutée sur une base MySQL 5.1.44 (serveur de prod) fonctionne bien, mais la même, exécutée sur une base MariaDB 5.5.34 (serveur perso) me renvoie des résultats totalement incohérents, avec des valeurs NULL pour id_peinture et donc nom_peinture !
Et, en rajoutant le champ p_random dans le SELECT final, quelle ne fut pas ma surprise de découvrir que sa valeur n'est pas identique à celle renvoyée par p.id, malgré le LEFT JOIN !

Là, je vous avoue que je ne comprends plus rien. Fais-je quelque chose d'incorrect ou aurais-je levé un bug, selon vous ?

Edit : suite à une installation de MySQL 5.6.15, en remplacement de MariaDB, tout fonctionne normalement. J'en déduis que le bug est du coté de MariaDB, et non de ma requête.

  • # jointure

    Posté par . Évalué à 0. Dernière modification le 07/12/13 à 17:20.

    ton code me semble bien compliqué,
    ce serait plus propre, plus lisibile, et plus rapide de faire une simple jointure entre les deux tables (sur l'id peinture present dans les deux table).

    SELECT galerie.id as id_galerie, galerie.nom as nom_galerie,.... 
    FROM pe LEFT JOIN gal ON gal.peinture_id=pe.id 
    ORDER by RAND() 
    LIMIT 1;
    • [^] # Re: jointure

      Posté par (page perso) . Évalué à 2.

      Mais là, cela ne fait pas la même chose. Son code renvoie, pour chaque galerie, une peinture au hasard. Ta requête renvoie une galerie et une peinture au hasard.

      • [^] # Re: jointure

        Posté par . Évalué à 2.

        En effet, c'est bien ce que je cherche à faire : pour chaque galerie, prendre, au hasard une des peintures parmi celles qui lui sont associées.

    • [^] # Re: jointure

      Posté par . Évalué à 2.

      Non seulement ça ne correspond pas à ce que je cherche à faire, comme le fait remarquer Francesco, mais en plus il y a une erreur dans ta requête : ma table galeries n'a pas de champs peinture_id !
      C'est la table peintures qui a un champ id_galerie

      • [^] # Re: jointure

        Posté par . Évalué à 4.

        le nom des champs importe peu c'est pour la logique plus que la pratique.

        ce qui me choque dans ton algo, c'est que tu :
        - cherche une peinture au hazard (ton p_random)

        • lie le resultat à la galerie qui va ( from galeries gal)

        • ou tu refais une jointure sur la table des peintures

        tu pourrais surement faire comme je le propose, sans le limit1 mais avec un group by

        SELECT galerie.id as id_galerie, galerie.nom as nom_galerie,peinture.id, peinture.nom .... 
        FROM peintures LEFT JOIN galerie ON galerie.id=peinture.galerie_id 
        ORDER by RAND() 
        GROUP BY galerie.id

        le regroupement va forcer le fait qu'il n'y aura qu'une seule ligne pour chaque galerie, et c'est la derniere (ou la premiere je ne sais plus) ligne de peinture pour cette galerie qui sera retenue.

  • # Vérifications d'usage...

    Posté par (page perso) . Évalué à 1.

    Juste pour être sur de ne pas chercher en vain, peux tu nous confirmer que la table "peintures" contient des enregistrements au niveau de ta base MariaDB ?

    Après, tu as 3 requêtes imbriquées les unes dans les autres. Je les testerai donc les unes après les autres afin de déterminer où se situe le problème, c'est-à-dire d'abord la requête avec le ORDER BY RAND(), puis la requête qui l'englobe et qui sélectionne une galerie.

    • [^] # Re: Vérifications d'usage...

      Posté par . Évalué à 2.

      Oui, il y a bien des données dans mes tables (les galeries ont 0, 1 ou plusieurs peintures qui leur sont associées, pour avoir tous les cas possibles).
      Et j'ai testé morceau par morceau, en commençant par la plus imbriquée, celle que tu cites. L'id renvoyé est bien toujours un de ceux associé à la galerie correspondante.

  • # Je confirme : ça sent le bug !

    Posté par . Évalué à 3.

    Bon, j'ai finalement tenté une autre "solution" : j'ai installé MySQL 5.6.15 en remplacement de MariaDB sur ma machine (une ArchLinux), sans toucher aux bases, aux tables ou aux données.
    Résultat : la même requête fonctionne parfaitement, cette fois !

    Bref, ça pue le bug dans MariaDB, mais grave…

    • [^] # Re: Je confirme : ça sent le bug !

      Posté par (page perso) . Évalué à 6.

      Juste un test à faire. Peux-tu exécuter cette requête et nous dire ce qu'elle renvoie ?

      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

      Si jamais tu as des valeurs différentes pour r1, r2 et r3, cela signifierait peut être que la valeur renvoyée par la colonne p_random est évaluée à chaque fois que tu fais un appel à p_random, et non calculée une fois pour toute. Maintenant, est-ce un bug ? Je ne sais pas. Un comportement qui diffère entre MariaDB et MySQL… oui !

  • # C'est confirmé : c'est un bug !

    Posté par . Évalué à 2.

    Suite à l'ouverture d'un ticket, il vient de m'être confirmé qu'il s'agit bien d'un bug, et qu'un workaround (que je n'ai pas testé, n'étant pas chez moi) existe.

Suivre le flux des commentaires

Note : les commentaires appartiennent à ceux qui les ont postés. Nous n'en sommes pas responsables.