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
            View 7 older comments
            sanja Oleksandr Byelkin added a comment -

            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;

            sanja Oleksandr Byelkin added a comment - 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;
            sanja Oleksandr Byelkin added a comment -

            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...

            sanja Oleksandr Byelkin added a comment - 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...
            serg Sergei Golubchik added a comment -

            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.

            serg Sergei Golubchik added a comment - 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.
            sanja Oleksandr Byelkin added a comment -

            Fix of the bug sent for review.

            sanja Oleksandr Byelkin added a comment - Fix of the bug sent for review.
            sanja Oleksandr Byelkin added a comment -

            pushed to 5.3

            sanja Oleksandr Byelkin added a comment - pushed to 5.3

            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.