enum values should be case insensitive

[zachmu]

They're case sensitive now. Compare mysql:

mysql> create table enums (a int primary key, b enum ('one', 'two', 'three'));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into enums values (1, 'one');
Query OK, 1 row affected (0.01 sec)

mysql> insert into enums values (2, 'TWO');
Query OK, 1 row affected (0.01 sec)

mysql> select * from enums;
+---+------+
| a | b    |
+---+------+
| 1 | one  |
| 2 | two  |
+---+------+
2 rows in set (0.00 sec)

In Dolt this causes an error:

enums> create table enums (a int primary key, b enum ('one', 'two', 'three'));
enums> insert into enums values (1, 'one');
Query OK, 1 row affected
enums> insert into enums values (2, 'TWO');
value TWO is not valid for this Enum

[Hydrocharged]

Is this actually a bug? This seems like a collation issue, Dolt defaults to a case-sensitive collation and I think MySQL defaults to a case-insensitive one.

[fulghum]

I think there are a couple issues here...

There definitely is a collation connection here, like Daylon mentions. MySQL 8's default collation (utf8mb4_0900_ai_ci ) is case-insensitive, which matches Dolt's current behavior, but since Dolt defaults to the utf8mb4_0900_bin, Dolt really should be treating enum values in a case-sensitive matter.

There's also an issue with Dolt not preserving the originally specified case for enum values in show create table and information_schema.columns.columntype. Regardless of the collation setting, MySQL preserves the original case in those places, so we should do the same.

I'll keep digging in, but wanted to share what I've tested and found out so far.

[fulghum]

https://github.com/dolthub/go-mysql-server/pull/1179 addresses the second issue in my previous comment – preserving the case when displaying table metadata.

There are still some behavioral differences between GMS and MySQL around handling case for enum values based on the collation in use though, so leaving this issue open to track those.

[Hydrocharged]

@fulghum Would you mind detailing any of the remaining issues with ENUM values? At least regarding all of the issues mentioned here, it seems that they have all been fixed.

[fulghum]

Thanks for pinging this one to close it out @Hydrocharged. I had a todo in my tests with a couple more cases to test after collation changes. I just added them in https://github.com/dolthub/go-mysql-server/pull/1196 and they are all working beautifully now that your collation work has landed, so I'll go ahead and close this issue out, too. 👏