Information_Schema.Columns => COLUMN_KEY in ('PRI','pri') causes matched behavior to MYSQL

[ywadi]

I stumbled upon this, when I connected my datasource through go-mysql-server to metabase;
Metabase was trying to scan tables with the query
SELECT A.TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, A.TABLE_NAME, A.COLUMN_NAME, B.SEQ_IN_INDEX KEY_SEQ, B.INDEX_NAME PK_NAME FROM INFORMATION_SCHEMA.COLUMNS A, INFORMATION_SCHEMA.STATISTICS B WHERE A.COLUMN_KEY in ('PRI','pri') AND B.INDEX_NAME='PRIMARY' AND A.TABLE_SCHEMA = database() AND B.TABLE_SCHEMA = database() AND A.TABLE_NAME='jokes' AND B.TABLE_NAME='jokes' AND A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME ORDER BY A.COLUMN_NAME

, and the error is as follows from the go terminal
operand should have &{%!d(string=pri) {6165 [112 114 105]} {6163 1073741823 4294967295 4294967295 309}} columns, but has 1"

After debuging, I found out that the ENUM in the sql/information_schema/information_schema.go has the "PRI" all caps,
MYSQL tends to allow the "pri" and ignore it, where as it seems go-mysql-server here is considering it a mis-match to the ENUM and causes the error reported back, is this something that can be fixed ? I assume its a simple fix?

[fulghum]

Hey @ywadi, thanks for taking the time to report this problem. We want Dolt and go-mysql-server to work with the MySQL ecosystem of tooling, and Metabase is definitely included in that.

I'm moving this over to the dolt repo, since it's an issue for dolt, too, and it'll be a little easier for our team to track over there. We'll get started digging into this one today for you.

[fulghum]

Quick update on this one... I debugged through this, and you are correct that MySQL is not strict about the values in a in clause. MySQL allows you to specify invalid enum values as well as values of different, non-coercible types. I've got a change locally that fixes Dolt/go-mysql-server to be less strict here, but I found one more edge case last night and want to do a little more testing to make sure I didn't miss anything. I'll get that PR published later today and we'll get this fixed up asap.

Thanks again for reporting this issue! This is a great one for us to fix.

[ywadi]

Awesome thank you for the awesome work!

[fulghum]

Hey @ywadi, I got the fix for this merged in to go-mysql-server and I'll get Dolt bumped to that new GMS version this afternoon. Once it's there, this fix will go out in the next Dolt release. That will likely be in the next couple of days, but don't hesitate to let us know if you'd like us to fast track a release for you so you can test out metabase again.

Thanks a ton for taking the time to report this issue so we could dig in and get this fixed! 🙏 Let us know if you see anything else odd or just have any questions and we'll be happy to help out.

[ywadi]

Awesome and my pleasure, I will let you know how it goes.