[MDEV-24525] sql_mode="oracle" does not support "rowid" pseudo column Created: 2021-01-05  Updated: 2021-01-05

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: woqutech.com Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Compatibility

Issue Links:
PartOf
is part of MDEV-19162 Some basic datatypes and functions in... Open

 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

 Comments   
Comment by woqutech.com [ 2021-01-05 ]

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
Comment by Michael Widenius [ 2021-01-05 ]

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)

Comment by woqutech.com [ 2021-01-05 ]

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 .
Generated at Thu Feb 08 09:30:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.