Derby
  1. Derby
  2. DERBY-4406

Wrong order when using ORDER BY on non-deterministic function

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 10.5.3.0, 10.6.1.0
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard

      Description

      If I read the SQL standard correctly, a statement such as "select random() as r from t order by random()" should be treated as "select random() as r from t order by r". Derby does however generate a second, hidden random() column by which the rows are ordered.

      ij> create table t(x int);
      0 rows inserted/updated/deleted
      ij> insert into t values 1,2,3,4,5;
      5 rows inserted/updated/deleted
      ij> – wrong result, not ordered by r
      ij> select random() as r from t order by random();
      R
      ----------------------
      0.1285512465366495
      0.5116860880915798
      0.21060042130229073
      0.2506706923680875
      0.6378857329935494

      5 rows selected
      ij> – correct result, ordered by r
      ij> select random() as r from t order by r;
      R
      ----------------------
      0.0749025910679918
      0.07694931688380491
      0.1724114605785414
      0.2268758969382877
      0.31900450349277965

      5 rows selected

        Issue Links

          Activity

          No work has yet been logged on this issue.

            People

            • Assignee:
              Nirmal Fernando
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development