Derby
  1. Derby
  2. DERBY-3314

RAND(SEED INTEGER) builtin function always returns the same random value for a given seed.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.3.1.4, 10.3.2.1, 10.4.1.3
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal

      Description

      RAND or

      {fn RAND(seed)}

      exists to match the JDBC specification (section C.1)
      RAND(integer) Random floating point for seed integer

      Trouble is that Derby creates a new Random() instance for every call leading to the same return value for the same seed. Seems to be useful, the function should return a new random number even when handed the same seed.

      Some more specification is probably needed, when does a sequence based upon a seed start?

      • first call by any connection
      • sequence within a connection
      • sequence within a sql context (e.g. procedure call, statement etc.)

      Also need to be wary of memory leaks if the engine needs to hold onto Random objects beyond the lifetime of the RAND call.

      ij> values rand(3);
      1
      ----------------------
      0.731057369148862

      1 row selected
      ij> values rand(3);
      1
      ----------------------
      0.731057369148862

      1 row selected
      ij> values

      {fn rand(3)}

      ;
      1
      ----------------------
      0.731057369148862

      1 row selected

        Issue Links

          Activity

          Hide
          Daniel John Debrunner added a comment -

          ODBC has the RAND function optionally taking a seed value, JDBC doesn't. The optional seed value makes sense for the next example I found where RAND(seed) sets the seed value for subsequent calls to RAND() [no seed] within the same statement. Ie. the sequence of random numbers is within a statement.

          Basically these two statements would return the same set of values (three random numbers) since the statement starts a new random sequence and RAND(3) sets the seed of that sequence to be 3..

          VALUES RAND(3), RAND(), RAND();
          VALUES RAND(3), RAND(), RAND();

          Though that does imply some ordering in expression evaluation which I didn't think SQL guarantees.

          Show
          Daniel John Debrunner added a comment - ODBC has the RAND function optionally taking a seed value, JDBC doesn't. The optional seed value makes sense for the next example I found where RAND(seed) sets the seed value for subsequent calls to RAND() [no seed] within the same statement. Ie. the sequence of random numbers is within a statement. Basically these two statements would return the same set of values (three random numbers) since the statement starts a new random sequence and RAND(3) sets the seed of that sequence to be 3.. VALUES RAND(3), RAND(), RAND(); VALUES RAND(3), RAND(), RAND(); Though that does imply some ordering in expression evaluation which I didn't think SQL guarantees.
          Hide
          Kathey Marsden added a comment -

          Triaged for 10.5.2. Set urgency to Normal. I did not mark Wrong query result or Deviation from standard, because if I read the comments correctly, we just need a second RAND() function that takes no arguments, but first need the JDBC spec to allow for this.

          My only concern regarding urgency on this one is that if someone needed it fixed in a hurry, we won't be able to fix it because of the spec work that is required.

          Show
          Kathey Marsden added a comment - Triaged for 10.5.2. Set urgency to Normal. I did not mark Wrong query result or Deviation from standard, because if I read the comments correctly, we just need a second RAND() function that takes no arguments, but first need the JDBC spec to allow for this. My only concern regarding urgency on this one is that if someone needed it fixed in a hurry, we won't be able to fix it because of the spec work that is required.

            People

            • Assignee:
              Unassigned
              Reporter:
              Daniel John Debrunner
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development