sea-orm icon indicating copy to clipboard operation
sea-orm copied to clipboard

Surprising ordering of native MySQL enums

Open Expurple opened this issue 2 years ago • 0 comments

I discovered this while working on #2014. I'll quote my code comment from there:

    // Native enum comparisons are not portable.
    //
    // Postgres enums are compared by their definition order
    // (see https://www.postgresql.org/docs/current/datatype-enum.html#DATATYPE-ENUM-ORDERING).
    // Tea was defined as ('EverydayTea', 'BreakfastTea'), so EverydayTea is considered "smaller" than BreakfastTea.
    //
    // SQLite doesn't support enum types and SeaORM works around this limitation by storing them as strings.
    // When treated as strings, EverydayTea is not "smaller" than BreakfastTea!
    //
    // MySQL should be the same as Postgres (see https://dev.mysql.com/doc/refman/8.0/en/enum.html#enum-sorting),
    // but in practice this test case behaves like SQLite. I'm not sure why.

This should probably be investigated, although I'm not interested in doing that myself

Expurple avatar Jan 12 '24 17:01 Expurple