[Bug]: UNIQUEIDENTIFIER ORDER BY uses PostgreSQL left-to-right sorting instead of SQL Server right-to-left byte order

[PauloHMattos]

What happened?

Description

When querying a table with an ORDER BY clause on a UNIQUEIDENTIFIER column, Babelfish sorts the values using PostgreSQL's standard left-to-right byte evaluation (like standard UUIDs). However, native SQL Server evaluates UNIQUEIDENTIFIER sorting starting with the last 6 bytes (bytes 10-15), moving right-to-left through the remaining byte blocks.

Our .NET applications utilize custom sequential GUID generator (that is like NEWSEQUENTIALID()) specifically designed to put the timestamp in the last 6 bytes to prevent page splits and index fragmentation. Because Babelfish sorts these left-to-right, migrated databases utilizing these IDs will suffer from random B-Tree index insertions, leading to heavy index bloat, buffer thrashing, and increased WAL generation.

Expected Behavior (SQL Server)

Given two GUIDs:

Guid1: 00000000-0000-0000-0000-FFFFFFFFFFFF

Guid2: FFFFFFFF-FFFF-FFFF-FFFF-000000000000

SQL Server evaluates bytes 10-15 first. Therefore, Guid2 (ends in 00s) should sort BEFORE Guid1 (ends in FFs).

Actual Behavior (Babelfish)

Babelfish evaluates bytes 0-3 first. Therefore, Guid1 (starts with 00s) sorts BEFORE Guid2 (starts with FFs).

Steps to Reproduce

The following minimal C# console application, using Microsoft.Data.SqlClient, works as expected in native SQL Server, but throws an exception against a Babelfish endpoint.

// GUID 1: Zeros at the start, F's at the end
Guid guid1 = new Guid("00000000-0000-0000-0000-FFFFFFFFFFFF");

// GUID 2: F's at the start, Zeros at the end
Guid guid2 = new Guid("FFFFFFFF-FFFF-FFFF-FFFF-000000000000");

// * EXPECTED BEHAVIOR (NATIVE SQL SERVER):
//      SQL Server evaluates uniqueidentifiers starting with the last 6 bytes (bytes 10-15).
//      Therefore, GUID 2 (ends in 00s) should sort BEFORE GUID 1 (ends in FFs).
//
// * ACTUAL BEHAVIOR (BABELFISH/POSTGRESQL):
//      PostgreSQL evaluates UUIDs left-to-right.
//      Therefore, GUID 1 (starts with 00s) sorts BEFORE GUID 2 (starts with FFs).

await using var connection = new SqlConnection(Database.ConnectionString.AppConnString);
await connection.OpenAsync();

await using var setupCmd = connection.CreateCommand();
setupCmd.CommandText =
    @"
        DROP TABLE IF EXISTS UniqueIdentifierSortTest;
        CREATE TABLE UniqueIdentifierSortTest (Id UNIQUEIDENTIFIER);
    
        INSERT INTO UniqueIdentifierSortTest (Id) VALUES (@id1), (@id2);
    ";
setupCmd.Parameters.AddWithValue("@id1", guid1);
setupCmd.Parameters.AddWithValue("@id2", guid2);
await setupCmd.ExecuteNonQueryAsync();

await using var selectCmd = connection.CreateCommand();
selectCmd.CommandText = "SELECT Id FROM UniqueIdentifierSortTest ORDER BY Id ASC;";

var sortedResults = new List<Guid>();
await using var reader = await selectCmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    sortedResults.Add(reader.GetGuid(0));
}

Console.WriteLine("--- GUID Sort Order Test ---");
Console.WriteLine($"Inserted GUID 1: {guid1}");
Console.WriteLine($"Inserted GUID 2: {guid2}");
Console.WriteLine("\nResults after 'ORDER BY Id ASC':");

for (int i = 0; i < sortedResults.Count; i++)
{
    Console.WriteLine($"{i + 1}. {sortedResults[i]}");
}

if (sortedResults[0] == guid2)
{
    Console.WriteLine("[SUCCESS] Sorted correctly according to SQL Server rules.");
}
else
{
    throw new InvalidOperationException(
        "[BUG DETECTED] Sorted incorrectly according to standard PostgreSQL left-to-right rules."
    );
}

Version

BABEL_5_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Ubuntu (Default)

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct.

[Deepesh125]

Thanks @PauloHMattos for reporting this issue. I did quick investigation and here is the details - All the uniqueidentifier's comparison functions delegate directly to PostgreSQL's internal uuid_* functions, e.g.,
CREATE FUNCTION sys.uniqueidentifierlt(...) AS 'uuid_lt' ... and
CREATE FUNCTION uniqueidentifier_cmp(...) AS 'uuid_cmp' ...

Now, PostgreSQL's uuid_internal_cmp is simply memcmp:
return memcmp(arg1->data, arg2->data, UUID_LEN); // straight bytes 0-15

whereas TSQL's expected sort behavior for uniqueidentifier compares byte groups in this order:

1. Bytes 10-15 (node / last 6 bytes)
2. Bytes 8-9 (clock_seq)
3. Bytes 6-7 (time_hi_and_version)
4. Bytes 4-5 (time_mid)
5. Bytes 0-3 (time_low)

Within each group, comparison is unsigned left-to-right.

[Deepesh125]

Filed BABEL-6854 for internal tracking.