Cypher on Oracle: randomUUID() gives SYS_GUID() values, not random version 4 UUIDs, and consecutive ones share most of their digits (port of drivine4j#401)

Closed Beginner friendly
  • Is something wrong?
#252 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

A pull request for this has already been merged.

  • #273 by @jasperblues — merged

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
78/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
python, sql
Domain
databases

Research direction

Start in drivine/cypher/sql/oracle_cypher_compiler.py at OracleCypherCompiler.uuid() and compare its current SYS_GUID() expression with the Oracle UUID() expression in the issue. Check how the compiler represents the SQL expression and whether the supported Oracle versions require the fallback. Done means compiled randomUUID() produces random version 4 UUID text on Oracle; the issue does not name a test file.

Written by the indexing model from the issue text.

Description

Port of liberation-data/drivine4j#401.

On Oracle, compiled Cypher's randomUUID() gives Oracle's SYS_GUID() written as UUID text. It's valid and unique, but it isn't version 4, and consecutive values share their first 11 or 12 characters. In 60 values from UNWIND range(1, 20) AS i RETURN randomUUID() compiled by this library on Oracle 23.26.3, 30 of the 32 hex digits were the same in every value:

5eeff3bf-6c75-0101-e063-090011ac4d78
5eeff3bf-6c76-0101-e063-090011ac4d78
5eeff3bf-6c77-0101-e063-090011ac4d78

Neo4j, Postgres (gen_random_uuid()) and SQLite all give random version 4 UUIDs.

Cause: OracleCypherCompiler.uuid() in drivine/cypher/sql/oracle_cypher_compiler.py compiles randomUUID() to LOWER(REGEXP_REPLACE(RAWTOHEX(SYS_GUID()), …)), the same expression as drivine4j.

Likely also affected, not tested: drivine/model/stamps.py makes its engine token from left(replace(randomUUID(), '-', ''), 16). On Oracle only about 16 bits of that change.

Fix: as in drivine4j#401. Use Oracle's UUID(), which returns a random version 4 UUID as RAW(16), and needs no grant beyond DB_DEVELOPER_ROLE:

LOWER(REGEXP_REPLACE(RAWTOHEX(UUID()), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5'))

Or use the DBMS_RANDOM fallback described there, if 23ai releases without UUID() must be supported. DBMS_CRYPTO needs EXECUTE granted by a DBA.

Dominant language
Python
Stars
1
Forks
0
Avg merge
1h 32m
Merged PRs (30d)
149

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from liberation-data/drivine-python

All issues in liberation-data/drivine-python

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.