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

strange/random select results

    XMLWordPrintable

    Details

      Description

      Strange select results when running this query "select * FROM tmp WHERE id in (select cast(rand()*10 as int)+1);" against a simple small table.

      This is easy enough to reproduce:

      CREATE TABLE `tmp` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `id_user` int(10) unsigned DEFAULT NULL,
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

      INSERT INTO `tmp` VALUES (1,80),(2,253),(3,333),(4,569),(5,606),(6,618),(7,660),(8,663),(9,690),(10,693);

      select * FROM tmp WHERE id in (select cast(rand()*10 as int)+1);
      Empty set (0.001 sec)

      select * FROM tmp WHERE id in (select cast(rand()*10 as int)+1);
      -----------+

      id id_user

      -----------+

      5 606
      9 690

      -----------+
      2 rows in set (0.001 sec)
      select * FROM tmp WHERE id in (select cast(rand()*10 as int)+1);
      -----------+

      id id_user

      -----------+

      1 80
      4 569
      8 663
      9 690

      -----------+
      4 rows in set (0.000 sec)

      but when I run select cast(rand()*10 as int)+1 alone, I only ever get one result...

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            mbfdias Miguel Dias
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.