Strange behavior with enum in composite unique key in a table without primary key

[Wuvist]

I've a very strange table:

CREATE TABLE `accounts` (
  `user_id` int NOT NULL,
  `type` enum ('free', 'vip') NOT NULL DEFAULT 'free',
  `money` int(8) NOT NULL DEFAULT 0,
  UNIQUE KEY `user_id_type` (`user_id`, `type`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

Usually, the UNIQUE KEY is should be declared as primary key, but somehow it's declared as UNIQUE KEY only, and the table has no other keys.

If I create this table in go-mysql-server v0.12.0, and run the following query

mysql> insert into `accounts` (user_id, `type`, money)values(1,"free",1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from `accounts`;
+---------+------+-------+
| user_id | type | money |
+---------+------+-------+
|       1 | free |     1 |
+---------+------+-------+
1 row in set (0.00 sec)

mysql> select * from `accounts` where user_id=1 and `type`="free";
Empty set (0.00 sec)

It failed to return result when type is in where condition.

I test the same table & query on MySQL 5.7.35, although it works:

mysql> select * from `accounts` where user_id=1 and `type`="free";
+---------+------+-------+
| user_id | type | money |
+---------+------+-------+
|       1 | free |     1 |
+---------+------+-------+
1 row in set (0.00 sec)

but it seems that MySQL 5.7.35 consider the unique key as primary key:

mysql> desc accounts;
+---------+--------------------+------+-----+---------+-------+
| Field   | Type               | Null | Key | Default | Extra |
+---------+--------------------+------+-----+---------+-------+
| user_id | int(11)            | NO   | PRI | NULL    |       |
| type    | enum('free','vip') | NO   | PRI | free    |       |
| money   | int(8)             | NO   |     | 0       |       |
+---------+--------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

It works if unique key is declared as primary in go-mysql-server v0.12.0, it also works if type is not enum but int.

I suppose this is a extreme corner case and may not worth fixing, but would like to report it here first in case it's related to some other hidden bug related to enum support.

[timsehn]

Great bug. We'll get on this today.

[druvv]

Hey @Wuvist, thanks for the bug! I'll reproduce this and get a fix out.

Just curious, what backend are you using for go-mysql-server? Dolt?

[druvv]

MySQL says:

If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

From the above, the problem seems to be in the describe command. I will update the behavior to match MySQL.

[timsehn]

I think this is also a bug @druvv:

mysql> select * from `accounts` where user_id=1 and `type`="free";
Empty set (0.00 sec)

We should return a row here.

[druvv]

Hey @Wuvist, I couldn't reproduce this issue using Dolt as a backend. Are you by chance running go-mysql-server with the memory engine?

[Wuvist]

Hey @Wuvist, I couldn't reproduce this issue using Dolt as a backend. Are you by chance running go-mysql-server with the memory engine?

Yup, I'm using go-mysql-server with the memory engine for unit testing in my go application and encounter this issue.

I didn't try Dolt.

[Wuvist]

Another finding maybe related related.

Consider the following table:

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `hobby` enum ('swimming', 'running', 'singing') NOT NULL DEFAULT 'swimming',
  `sports` SET('SWIM', 'TENNIS', 'BASKETBALL', 'FOOTBALL') NOT NULL DEFAULT ("SWIM, FOOTBALL") ,
  `sports2` SET('SWIM', 'TENNIS', 'BASKETBALL', 'FOOTBALL') NOT NULL DEFAULT "SWIM,FOOTBALL",
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

In existing v0.12.0 release, querying information_schema select c.column_name, c.column_default from information_schema.columns as c where table_name = 'users'

will return:

id	NULL
hobby	swimming
sports	"SWIM, FOOTBALL"
sports2	SWIM,FOOTBALL

which is correct.

But in current main branch ( commit `c5b2d44) .

it returns:

id	NULL
hobby	1
sports	'SWIM, FOOTBALL'
sports2	9

The default values stored in information_schema are wrong:

• hobby: should be swimming
• sports: should be double quoted "SWIM, FOOTBALL" , instead of single quoted
• sports2: should be SWIM,FOOTBALL

[timsehn]

I think I'm going to resolve this one. Please create a new bug in go-mysql-server if you have a persistent problem there. We've made changes to information_schema latesly and that issue should be fixed.