Experiencing sorting problems with bigint columns

[Parth2692]

Experiencing sorting problems with bigint columns across tables tested.

Example:

In the table the projectid (bigint as per schema - which is correct) yet when sorted in SQL is sorting as per a string value

1000903 is returned as the minimum projectid value

998401 is returned as the maximum projectid value. 

[EktaPuri]

Can you send across schema of table using describe command.

Also at runtime cast it to bigint and then check.

[Parth2692]

[Parth2692]

[EktaPuri]

Can you cast explicitly to bigint and check, it's kinda strange

[Parth2692]

When I use CAST, it returns the data type as BIGINT, which is expected. However, if I'm reading directly from the Unity Catalog (UC) table, it should already return the BIGINT data type without requiring a cast. Is there a reason why this inconsistency is occurring?

[EktaPuri]

Try running explain command  what's happening in the background, also if table is small try to write in another table and check