Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1414

Add RAND_INTEGER function, which returns a random integer modulo N

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.11.0
    • Component/s: None
    • Labels:

      Description

      Add a RANDOM function.
      It is not standard SQL, but most DBMSs have one, and it be useful.

      It will also allow us to better test non-deterministic expressions.

      I propose that:

      • RANDOM(n) returns an integer between 0 and n - 1 inclusive.
      • There is no facility to specify a seed at this time.
      • The random number generator is randomly seeded at query start time based on (say) the method used by Java's new Random(); if you run the query twice, you will (probably) not get the same results.
      • If you call the function twice in the same row, it will (probably) return different results.

        Issue Links

          Activity

          Hide
          julian.feinauer Julian Feinauer added a comment - - edited

          I added a pull request: https://github.com/apache/calcite/pull/339 with my suggestion how to implement this function.

          I had to resubmit the PR https://github.com/apache/calcite/pull/338 due to a problem with the CI / Validator.
          Sorry for that.

          Show
          julian.feinauer Julian Feinauer added a comment - - edited I added a pull request: https://github.com/apache/calcite/pull/339 with my suggestion how to implement this function. I had to resubmit the PR https://github.com/apache/calcite/pull/338 due to a problem with the CI / Validator. Sorry for that.
          Hide
          julianhyde Julian Hyde added a comment -

          Julian Feinauer, Looks good. A few comments:

          • Can you please rename it to RAND_INTEGER? This makes it consistent with RAND (which is in the ODBC spec and is therefore somewhat standard) but makes it clear that this is intended to produce integers, not doubles, and does not accept a seed argument. (Sorry to change my mind - I know I originally suggested naming it RANDOM.)
          • In SqlStdOperatorTable can you rename RANDOM_FUNCTION to RAND_INTEGER to be consistent with other fields, which are just the function name.
          • Can you add RAND_INTEGER(numeric) to reference.md.

          When you've fixed these, just add a commit to PR 339 and add a comment to this case; no need to create a new PR.

          Show
          julianhyde Julian Hyde added a comment - Julian Feinauer , Looks good. A few comments: Can you please rename it to RAND_INTEGER ? This makes it consistent with RAND (which is in the ODBC spec and is therefore somewhat standard) but makes it clear that this is intended to produce integers, not doubles, and does not accept a seed argument. (Sorry to change my mind - I know I originally suggested naming it RANDOM .) In SqlStdOperatorTable can you rename RANDOM_FUNCTION to RAND_INTEGER to be consistent with other fields, which are just the function name. Can you add RAND_INTEGER(numeric) to reference.md. When you've fixed these, just add a commit to PR 339 and add a comment to this case; no need to create a new PR.
          Hide
          julian.feinauer Julian Feinauer added a comment -

          Julian Hyde, I renamed everything to RAND_INTEGER and added a Comment in the Readme.md.
          Please feel free to give me any more hints if I should change something.

          Show
          julian.feinauer Julian Feinauer added a comment - Julian Hyde , I renamed everything to RAND_INTEGER and added a Comment in the Readme.md. Please feel free to give me any more hints if I should change something.
          Hide
          julianhyde Julian Hyde added a comment -
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/a2837823 . Thanks for the PR, Julian Feinauer !
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.11.0 (2017-01-11).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.11.0 (2017-01-11).

            People

            • Assignee:
              julian.feinauer Julian Feinauer
              Reporter:
              julianhyde Julian Hyde
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development