GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator

Row validation of NULL UUIDs in Oracle produces an incorrect value

Open
#1,716 0 comments 0 reactions 0 assignees View on GitHub
type: bug
Dominant language
Python
Stars
524
Forks
171
Avg merge
5d 15h
Merged PRs (30d)
4

Description

**Describe the bug**
Oracle GUID is output in a different format to PostgreSQL UUID, we create a consistent value across the two engines by using a fairly complex expression in the Oracle SQL statement. For example:

```
concat(
concat(
concat(
concat(
concat(
concat(
concat(
concat(substr(lower(rawtohex(t1.col_uuid)), :param_1 + :param_2, :param_3), :param_4),
substr(lower(rawtohex(t1.col_uuid)), :param_5 + :param_6, :param_7)
),
:param_8
),
substr(lower(rawtohex(t1.col_uuid)), :param_9 + :param_10, :param_11)
),
:param_12
),
substr(lower(rawtohex(t1.col_uuid)), :param_13 + :param_14, :param_15)
),
:param_16
),
substr(lower(rawtohex(t1.col_uuid)), :param_17 + :param_18, :param_19)
) AS cast_uuid_string__col_uuid
```

Unfortunately when the GUID is NULL the expression above outputs:

```
----
```

We need to revisit this.

**What version of DVT are you using?**
8.5.2

**What type of connections are you using for source and target?**
Oracle/PostgreSQL

**Screenshots**
If applicable, add screenshots to help explain your problem.

**How are you executing DVT?**
Ex: CLI commands on local computer or VM, within a DAG, Cloud Run job, etc

**Additional context**
Add any other context about the problem here.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.