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

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

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23066 RAND() evaluated and filtered twice with subquery

          • Major - Major loss of function.
          • Confirmed

          Activity

            Ascending order - Click to sort in descending order
            fgaroby Francescu GAROBY created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            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, r 2 and r3 have the same value in MySQL, but not in MariaDB.
            Is it a bug or is it a normal behavior ?             		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 :

            {code:sql}
            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
            {code}

            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 :

            {code:sql}
            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
            {code}

            r1, r 2 and r3 have the same value in MySQL, but not in MariaDB.
            Is it a bug or is it a normal behavior ?
            Labels MariaDB_5.5 galera
            elenst Elena Stepanova made changes -
            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 :

            {code:sql}
            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
            {code}

            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 :

            {code:sql}
            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
            {code}

            r1, r 2 and r3 have the same value in MySQL, but not in MariaDB.
            Is it a bug or is it a normal behavior ?             		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 :

            {code:sql}
            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
            {code}

            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 :

            {code:sql}
            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
            {code}

            {noformat}
            r1, r2 and r3 have the same value in MySQL, but not in MariaDB.
            {noformat}
            Is it a bug or is it a normal behavior ?
            fgaroby Francescu GAROBY made changes -
            Attachment good result on MySQL - rand in a subselect.png [ 25100 ]
            fgaroby Francescu GAROBY made changes -
            Attachment wrong result on MariaDB - rand in a subselect.png [ 25101 ]
            elenst Elena Stepanova made changes -
            Assignee Elena Stepanova [ elenst ]
            elenst Elena Stepanova made changes -
            Fix Version/s 10.0.8 [ 14200 ]
            Fix Version/s 5.5.35 [ 14000 ]
            Fix Version/s 5.3.13 [ 12602 ]
            Affects Version/s 5.3.12 [ 12000 ]
            Affects Version/s 5.5.34 [ 13700 ]
            Affects Version/s 10.0.6 [ 13202 ]
            Affects Version/s 5.5.34-galera [ 13900 ]
            Assignee Elena Stepanova [ elenst ] Igor Babaev [ igor ]
            sanja Oleksandr Byelkin made changes -
            Assignee Igor Babaev [ igor ] Oleksandr Byelkin [ sanja ]
            sanja Oleksandr Byelkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Labels upstream
            sanja Oleksandr Byelkin made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            sanja Oleksandr Byelkin made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            sanja Oleksandr Byelkin made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 31000 ] MariaDB v2 [ 45036 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 45036 ] MariaDB v3 [ 65577 ]
            Christopher Granahan Christopher Granahan made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 65577 ] MariaDB v4 [ 147321 ]

            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.