Trino throwing error when key_column data type is UUID

[Ryuk-me]

Hey @erezsh, hope you are doing well.

I'm trying to find difference between trino DB's which is connected to a postgres database
I'm getting this error

raise NotImplementedError(f"Cannot use a column of type {kt} as a key")
NotImplementedError: Cannot use a column of type UnknownColType(text='uuid') as a key

Here is my code

from reladiff import connect_to_table, diff_tables

table1 = connect_to_table("trino://admin@localhost/source/public", "raw", "custid")
table2 = connect_to_table("trino://admin@localhost/target/public", "raw", "custid")

for sign, row in diff_tables(table1, table2):
    print(sign, row)

Postgres Database rows

Is there any fix?
When I make uuid as TEXT it works fine.

Thank you for your great work.

[erezsh]

Hello @Ryuk-me ,

Yes, it looks like Trino doesn't currently support UUID columns.

I think it's relatively easy to add, but it needs to be added to the sqeleton package.

If you'd like to open a PR, I can explain to you how to fix it yourself. Otherwise, I'll try to get to it myself in the coming days.

[Ryuk-me]

Hey @erezsh, thanks for the instructions after making the suggested changes, I'm getting this error

raise self._process_error(response["error"], response.get("id"))

trino.exceptions.TrinoUserError: TrinoUserError(type=USER_ERROR, name=FUNCTION_NOT_FOUND, message="Unexpected parameters (uuid) for function trim. Expected: trim(char(x)), trim(char(x), codepoints), trim(varchar(x)), trim(varchar(x), codepoints)", query_id=20240813_123832_00024_3mat3)     

Table structure postgres

CREATE TABLE test_table (
    id UUID PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO test_table (id, name) VALUES
('550e8400-e29b-41d4-a716-446655440000', 'Nk'),
('3b241101-e2bb-4255-8caf-4136caaae2e6', 'Bob');

Changes:

from reladiff import connect_to_table, diff_tables

table1 = connect_to_table("trino://admin@localhost/source/public", "test_table", "id")
table2 = connect_to_table("trino://admin@localhost/target/public", "test_table", "id")

for sign, row in diff_tables(table1, table2):
    print(sign, row)

I have installed sqeleton and reladiff locally and reinstalled using pip install -e . after making changes, as you can see in the below pictures both are installed locally, and both of the clones are UpToDate.

[erezsh]

Hi @Ryuk-me , sounds like you made progress!

I think this error is happening because the trino implementation (in sqeleton) of normalize_uuid() isn't correct. See, until now, Trino only supported String_UUID, but now that it can also be Native_UUID, this use-case needs to be addressed.

• The default implementation either converts to char or not, based on the type: https://github.com/erezsh/sqeleton/blob/master/sqeleton/abcs/mixins.py#L46

• Mysql takes the "easy" way and always casts to char: https://github.com/erezsh/sqeleton/blob/master/sqeleton/databases/mysql.py#L48

Hopefully that moves you forward! If not, create a draft pull-request with your changes. It will be easier to debug if I could run it myself.

[Ryuk-me]

Hey @erezsh, I've raised a draft PR please have a look whenever you're free.

PR
erezsh/sqeleton#20

[erezsh]

@Ryuk-me I created a PR that is supposed to solved this. Can you give it a try and let me know if it works for you too?

#28

[Ryuk-me]

Hey @erezsh, its working now.
Great work!!
Thank you.