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

sql_mode="oracle" does not support "rowid" pseudo column

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • None

    Description

      Each row of data in oracle database has a unique identifier(or rowid), which is usually used to access the data inside the oracle. Rowid requires 10 bytes of storage space and uses 18 characters to display. This value indicates the physical location of the row in the oracle database. You can use rowid in a query to indicate that the value is included in the query result.

      It takes 10 bytes or 80 binary bits to save the rowid. The 80 binary bits are:

      • The data object number indicates the number of the database object to which this row belongs. Each data object is uniquely assigned a number when the database is created, and this number is unique. The data object number occupies approximately 32 bits.
      • Corresponding to the file number, indicating the number of the file where the row is located, and each file label in the table space is unique. The position occupied by the file number is 10 bits.
      • The block number indicates the position of the block of the file where the line is changed. The block number requires 22 bits.
      • The row number indicates the specific position of the row in the row directory. The row number requires 16 bits.
        This adds up to 80 bits.

      Oracle's physical extension ROWID has 18 bits, and each bit is coded with 64 bits, which are represented by 64 characters A~Z, a~z, 0~9, +, /. A means 0, B means 1,...Z means 25, a means 26,...z means 51, 0 means 52,..., 9 means 61, + means 62, and / means 63.

      select rowid,empno from scott.emp;
      		 
       
      		 
      ROWID EMPNO
      		 
      ------------------ ----------
      		 
      AAAR3sAAEAAAACXAAA 7369
      		 
      AAAR3sAAEAAAACXAAB 7499
      		 
      AAAR3sAAEAAAACXAAC 7521
      		 
      AAAR3sAAEAAAACXAAD 7566

      Through the dbms_rowid package, you can directly get the information contained in the specific rowid:

      SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.rowid_row_number(rowid) row_number from emp;
      		 
       
      		 
      OBJECT_ID    FILE_ID BLOCK_ID ROW_NUMBER
      		 
       
      		 
      ---------- ---------- ---------- ----------
      		 
       
      		 
           73196    4     151  0
      		 
           73196    4     151  1
      		 
           73196    4     151  2
      		 
           73196    4     151  3
      		 
           73196    4     151  4

      Oracle doc: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ROWID-Pseudocolumn.html#GUID-F6E0FBD2-983C-495D-9856-5E113A17FAF1

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-19162 Some basic datatypes and functions in oracle compatibility mode do not work

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            woqutech.com woqutech.com added a comment -

            Some key information for implement, please check it and give some suggestion:

            • Like oracle, we use 10 bytes to represent ROWID. The difference is that 4 bytes space id corresponds to oracle object id, 4 bytes page id corresponds to block no, and 2 bytes row no corresponds to row no. Because there is only one file in the mariadb tablespace, we think that the file no in the oracle rowid can only be 1 in mariadb
            • When allocating memory to the record in the sturct TABLE structure, allocate 18 more bytes to store the ROWID
            • Add rowid field (or virtual column) in struct TABLE_SHARE to identify rowid column
            • In the row_sel_store_mysql_rec function, if there is a rowid in the select list, the actual value of the rowid is generated according to the first description rule and returned to the server layer together with other fields
            woqutech.com woqutech.com added a comment - Some key information for implement, please check it and give some suggestion: Like oracle, we use 10 bytes to represent ROWID. The difference is that 4 bytes space id corresponds to oracle object id, 4 bytes page id corresponds to block no, and 2 bytes row no corresponds to row no. Because there is only one file in the mariadb tablespace, we think that the file no in the oracle rowid can only be 1 in mariadb When allocating memory to the record in the sturct TABLE structure, allocate 18 more bytes to store the ROWID Add rowid field (or virtual column) in struct TABLE_SHARE to identify rowid column In the row_sel_store_mysql_rec function, if there is a rowid in the select list, the actual value of the rowid is generated according to the first description rule and returned to the server layer together with other fields
            monty Michael Widenius added a comment - - edited

            How is ROWID used in the applications you are looking at?
            Is it not enough that the ROWID is unique for the table or has it to be unique over all tables?
            Why not just return the primary key that InnoDB always creates?
            (For other storage engines we would have an option to force a hidden primary key when tables are created. See MDEV-21181 Automatic invisible primary key)

            monty Michael Widenius added a comment - - edited How is ROWID used in the applications you are looking at? Is it not enough that the ROWID is unique for the table or has it to be unique over all tables? Why not just return the primary key that InnoDB always creates? (For other storage engines we would have an option to force a hidden primary key when tables are created. See MDEV-21181 Automatic invisible primary key)
            woqutech.com woqutech.com added a comment - - edited

            The table definition on oracle is

            ```
            CREATE TABLE "CIM"."TASKLIST" ("ID" VARCHAR2(42) NOT NULL ENABLE, "STATIONLINE" VARCHAR2(4000) ... "BELONGCITY" VARCHAR2(42)) ;
            ALTER TABLE "CIM"."TASKLIST" ADD PRIMARY KEY ("ID") USING INDEX ENABLE;
            ```

            and the select and delete is

            ```
            select null from TASKLIST where rowid = : plsqldev_rowid for update nowait;
            DELETE FROM TASKLIST WHERE ROWID = : PLSQLDEV_ROWID;
            ```

            In this case,

            • we can not use _rowid feature
            • user has the primary key id( is a VARCHAR2(42)), so InnoDB will not create a hidden primary key. we can not use hidden primary key for ROWID.
            • map a VARCHAR2(42) to 10 byte ROWID may have the same ROWID for two different row .
            woqutech.com woqutech.com added a comment - - edited The table definition on oracle is ``` CREATE TABLE "CIM"."TASKLIST" ("ID" VARCHAR2(42) NOT NULL ENABLE, "STATIONLINE" VARCHAR2(4000) ... "BELONGCITY" VARCHAR2(42)) ; ALTER TABLE "CIM"."TASKLIST" ADD PRIMARY KEY ("ID") USING INDEX ENABLE; ``` and the select and delete is ``` select null from TASKLIST where rowid = : plsqldev_rowid for update nowait; DELETE FROM TASKLIST WHERE ROWID = : PLSQLDEV_ROWID; ``` In this case, we can not use _rowid feature user has the primary key id( is a VARCHAR2(42)), so InnoDB will not create a hidden primary key. we can not use hidden primary key for ROWID. map a VARCHAR2(42) to 10 byte ROWID may have the same ROWID for two different row .

            People

              Unassigned Unassigned
              woqutech.com woqutech.com
              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.