Uploaded image for project: 'MariaDB Server'
  1. MDEV-5414

RAND() in a subselect : different behavior in MariaDB and MySQL

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.3.12, 5.5.34, 10.0.6
    • Fix Version/s: 5.5.35, 10.0.8, 5.3.13
    • Component/s: None
    • Labels:
    • Environment:
      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

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              fgaroby Francescu GAROBY
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: