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

RAND() evaluated and filtered twice with subquery

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3, 10.4.8, 10.4, 10.5
    • Fix Version/s: 10.3, 10.4, 10.5
    • Component/s: None
    • Labels:
      None
    • Environment:
      Windows 10

      Description

      When calling RAND() in a subquery and filtering on it in the outer query, it appears that a random number is being generated twice, with both values checked against the WHERE clause. This issue sounds similar to MDEV-5414, but that issue is marked as fixed in 10.0.8. MySQL (or at least recent versions of it) does not appear to exhibit this problem.

      Example:

      WITH RECURSIVE one_hundred (N) AS (
        SELECT 1
        UNION ALL
        SELECT N+1 FROM one_hundred WHERE N < 100
      ),
      randomized (RANDOM_VALUE) AS (
        SELECT RAND()
        FROM one_hundred
      )
      SELECT COUNT(*)
      FROM randomized
      WHERE RANDOM_VALUE <= 0.1;
      

      This query should return 10 on average, but instead returns 1 on average.

      One workaround is to add a LIMIT clause to the inner query. The following code returns an average of 10 as expected:

      WITH RECURSIVE one_hundred (N) AS (
        SELECT 1
        UNION ALL
        SELECT N+1 FROM one_hundred WHERE N < 100
      ),
      randomized (RANDOM_VALUE) AS (
        SELECT RAND()
        FROM one_hundred
        LIMIT 999999999999
      )
      SELECT COUNT(*)
      FROM randomized
      WHERE RANDOM_VALUE <= 0.1;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              Christopher Granahan Christopher Granahan
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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