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

support oracle syntax: rownum

    XMLWordPrintable

    Details

      Description

      Oracle ROWNUM is a pseudocolumn (not a real column) that is generated
      when running a select.

      ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N.

      For each row combination returned by a query, ROWNUM returns a number indicating the order in which Oracle selects the rows from the table (tables)

      The first row combination selected has a ROWNUM of 1, the second has 2, and so
      on.

      When used in a sub query, the subquery will generate a new ROWNUM sequence for
      each sub query execution.

      The ROWNUM pseudocolumn should be implemented under SQL_MODE=ORACLE.

      In many cases one could also replace WHERE ROWNUM <= X with LIMIT X.

      The most common usage of rownum is to limit the number of rows in the query:

      SELECT * from table where rownum < 10;
      

      Examples:

      create table t1 (a int primary key);
      create table t2 (b int primary key);
      insert into t1 values(1),(2),(3);
      insert into t2 values(1),(2),(3);
       
      select t1.a, (select t2.b from t2 where t1.a=t2.b and rownum < t1.a) from t1;
      

      The following query is undefined (can return any rows) as rownum is generated before ORDER BY is done and there is no guarantee in which order the database is returning rows.

      Select * from t1 order by t1.a desc having rownum <= 2;
      

      Authentic Oracle behavior:

      Prepare the data

      create table t1(c1 number, c2 number);
      create table t2(c1 number, c2 number);
      create table t3(c1 number, c2 number);
       
      insert into t1 values(1,1);
      insert into t1 values(2,2);
      insert into t1 values(3,3);
       
      insert into t2 values(1,1);
      insert into t2 values(2,2);
      insert into t2 values(3,3);
       
      insert into t3 values(1,1);
      insert into t3 values(2,2);
      insert into t3 values(3,3);
      

      Run queries

      SQL> select * from t1 where rownum<2;
      C1	C2
      1	1
       
      SQL> select t1.c1, t1.c2 ,rownum from t1 left join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1;
       
              C1         C2     ROWNUM
      ---------- ---------- ----------
               1          1          1
               2          2          2
               3          3          3
       
      SQL> select * from ( select t1.* , rownum row_id from t1 where rownum <= 5 ) where row_id > 2;
       
              C1         C2     ROW_ID
      ---------- ---------- ----------
               3          3          3
       
       
      SQL> select t1.c1, (select t2.c2 from t2 where t1.c1=t2.c2 and rownum < t1.c1) from t1;
       
              C1 (SELECTT2.C2FROMT2WHERET1.C1=T2.C2ANDROWNUM<T1.C1)
      ---------- --------------------------------------------------
               1
               2                                                  2
               3                                                  3
      

      One can also use ROWNUM with UPDATE:

      UPDATE table SET column = ROWNUM;
      

      In case of group by queries, the rownum will work on the originally found rows, not the result rows like LIMIT

      SELECT sum(c1) FROM t1 WHERE rownum<=2;
      

      This will return 3 (or at least not 6)

      ROWNUM cannot be used with HAVING (Oracle has the same restriction)
      ROWNUM should not be used in applications with the following constructs as the result will not be deterministic (it's legal to use it, but the rows used in the result set will differ based on engine or used optimizations):

      • In ON expressions
      • With GROUP BY
      • SELECT without an ORDER BY (This is a common case how ROWNUM is used and in this case we can replace it with LIMIT)

      oracle rownum reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

      Architecture:
      Add function ROWNUM() (Item_rownum()).

      • Map the result of ROWNUM() to 'last_join->accepted_records', which is incremented for each row added to the result set.
        This variable will be incremented in end_send(), end_send_group(), end_update() and end_unique_update()
      • Filesort() needs each own variable that is incremented for each accepted row.

      In oracle mode, if field with name 'rownum' is used and there is no match in
      any of the tables, map the field to Item_rownum()

      Things to consider and test in addition to the test in the suggested patch:

      • Limit optimization
        • If we have ROWNUM < # or ROWNUM <= # on the top level of the WHERE clause
          this should be converted to LIMIT. This is to be done in the
          JOIN::prepare phase(), probably after optimize_cond() which does
          propagate_cond_constants()
      • When using sum functions, it's not sent rows, it's examined rows we have to
        limit against
      • Filesort, when done over single table needs it's own handling
        (as there are no 'send_records' here). Limit is done according to found rows,
        not sorted order.
      • Ensure that ROWNUM also works with UPDATE and DELETE
      • UNION
        • ROWNUM should work independently for each UNION

        Attachments

        1. 10.5.6-rownum.patch
          107 kB
        2. rownum.result
          6 kB
        3. rownum.test
          5 kB

          Issue Links

            Activity

              People

              Assignee:
              monty Michael Widenius
              Reporter:
              woqutech woqutech
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration