ORDER BY on ENUM/SET columns sorts alphabetically instead of by ordinal/bit position on ENGINE=DuckDB

INSTALL SONAME 'ha_duckdb';

CREATE TABLE t1 (id INT PRIMARY KEY, e ENUM('c','b','a')) ENGINE=DuckDB;

INSERT INTO t1 VALUES (1,'c'),(2,'b'),(3,'a');

SELECT id, e FROM t1 ORDER BY e;

CREATE TABLE t2 (id INT PRIMARY KEY, s SET('c','b','a')) ENGINE=DuckDB;

INSERT INTO t2 VALUES (1,'c'),(2,'b'),(3,'a'),(4,'a,c');

SELECT id, s FROM t2 ORDER BY s;

13.1.0-dbg>INSTALL SONAME 'ha_duckdb';

Query OK, 0 rows affected (0.951 sec)

13.1.0-dbg>CREATE TABLE t1 (id INT PRIMARY KEY, e ENUM('c','b','a')) ENGINE=DuckDB;

Query OK, 0 rows affected (0.031 sec)

13.1.0-dbg>INSERT INTO t1 VALUES (1,'c'),(2,'b'),(3,'a');

Query OK, 3 rows affected (0.039 sec)

Records: 3  Duplicates: 0  Warnings: 0

13.1.0-dbg>SELECT id, e FROM t1 ORDER BY e;

+----+------+

| id | e    |

+----+------+

|  3 | a    |

|  2 | b    |

|  1 | c    |

+----+------+

3 rows in set (0.056 sec)

13.1.0-dbg>

13.1.0-dbg>CREATE TABLE t2 (id INT PRIMARY KEY, s SET('c','b','a')) ENGINE=DuckDB;

Query OK, 0 rows affected (0.019 sec)

13.1.0-dbg>INSERT INTO t2 VALUES (1,'c'),(2,'b'),(3,'a'),(4,'a,c');

Query OK, 4 rows affected (0.016 sec)

Records: 4  Duplicates: 0  Warnings: 0

13.1.0-dbg>SELECT id, s FROM t2 ORDER BY s;

+----+------+

| id | s    |

+----+------+

|  3 | a    |

|  2 | b    |

|  1 | c    |

|  4 | c,a  |

+----+------+

4 rows in set (0.025 sec)

13.1.0-dbg>

13.1.0-dbg>CREATE TABLE t1 (id INT PRIMARY KEY, e ENUM('c','b','a')) ENGINE=InnoDB;

Query OK, 0 rows affected (0.013 sec)

13.1.0-dbg>INSERT INTO t1 VALUES (1,'c'),(2,'b'),(3,'a');

Query OK, 3 rows affected (0.002 sec)

Records: 3  Duplicates: 0  Warnings: 0

13.1.0-dbg>SELECT id, e FROM t1 ORDER BY e;

+----+------+

| id | e    |

+----+------+

|  1 | c    |

|  2 | b    |

|  3 | a    |

+----+------+

3 rows in set (0.000 sec)

13.1.0-dbg>

13.1.0-dbg>CREATE TABLE t2 (id INT PRIMARY KEY, s SET('c','b','a')) ENGINE=MyISAM;

Query OK, 0 rows affected (0.005 sec)

13.1.0-dbg>INSERT INTO t2 VALUES (1,'c'),(2,'b'),(3,'a'),(4,'a,c');

Query OK, 4 rows affected (0.002 sec)

Records: 4  Duplicates: 0  Warnings: 0

13.1.0-dbg>SELECT id, s FROM t2 ORDER BY s;

+----+------+

| id | s    |

+----+------+

|  1 | c    |

|  2 | b    |

|  3 | a    |

|  4 | c,a  |

+----+------+

4 rows in set (0.000 sec)

13.1.0-dbg>