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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12, 5.5.34, 10.0.6
    • 5.5.35, 10.0.8, 5.3.13
    • 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

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.