[MDEV-24285] support oracle build-in function: sys_guid Created: 2020-11-26  Updated: 2021-06-01  Resolved: 2021-05-20

Status: Closed
Project: MariaDB Server
Component/s: Server
Fix Version/s: 10.6.1

Type: Task Priority: Major
Reporter: woqutech Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None

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

 Description   

SYS_GUID is an Oracle function, similar to the UUID function in mariadb, the document link is:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SYS_GUID.html#GUID-761E36B4-32DA-497D-8829-3D4653381F9B

there are two difference between sys_guid and uuid in mariadb :

  • output format: the output of sys_guid without '-' character.
  • use different algorithms,please note the red font in the example
    1. Execute sys_guid multiple times in a session, and add 1 to the 12th byte of output each time
    2. execute sys_guid in different sessions, the 6~16 bytes and last 4 bytes of output are different

example for oracle sys_guid

SQL> SELECT SYS_GUID() FROM DUAL;

SYS_GUID()
--------------------------------
B4F9C877B1264B49E05315500A0A222B

SQL> SELECT SYS_GUID() FROM DUAL;

SYS_GUID()
--------------------------------
B4F9C877B1274B49E05315500A0A222B

SQL> SELECT SYS_GUID() FROM DUAL;

SYS_GUID()
--------------------------------
B4F9C877B1284B49E05315500A0A222B

  • start new session and execute sys_guid

SQL> SELECT SYS_GUID() FROM DUAL;

SYS_GUID()
--------------------------------
B4F9CCEB9C1A4CBDE05315500A0AA3C2

SQL> SELECT SYS_GUID() FROM DUAL;

SYS_GUID()
--------------------------------
B4F9CCEB9C1B4CBDE05315500A0AA3C2

SQL> SELECT SYS_GUID() FROM DUAL;

SYS_GUID()
--------------------------------
B4F9CCEB9C1C4CBDE05315500A0AA3C2

I have implemented SYS_ GUID, the behavior and algorithms are the same as UUID, but the output format is different.

MariaDB [(none)]> select sys_guid();
+----------------------------------+
| sys_guid()                       |
+----------------------------------+
| 4FE31C7B2F8E11EB8DAAA87EEAED1F75 |
+----------------------------------+
1 row in set (0.000 sec)
 
MariaDB [(none)]> select sys_guid();
+----------------------------------+
| sys_guid()                       |
+----------------------------------+
| 50DEAB622F8E11EB8DAAA87EEAED1F75 |
+----------------------------------+



 Comments   
Comment by woqutech [ 2020-11-26 ]

add my patch

Comment by Michael Widenius [ 2021-01-04 ]

Patch is ok and I will apply it to 10.6.

Two comments:

  • The patch is reverse diff, which confused me for a short while (the new lines are deletions, not addiitions.
    The next time you submit a patch, please reverse the arguments to diff or use 'git show.
  • There was not test case for sys_guid(). I will add one.
  • We can't test the output of sys_guid(), but we can test the length of the string and that two calls gives different results.
Comment by woqutech [ 2021-01-05 ]

Thanks for comment and test case.
We will continue contribute other patch and will not this mistake next time

Generated at Thu Feb 08 09:28:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.