MySQL Bugs: #71403: ERROR 1167 (42000): The used storage engine can't index column on [VAR]CHAR(0)

Bug #71403	ERROR 1167 (42000): The used storage engine can't index column on [VAR]CHAR(0)
Submitted:	17 Jan 2014 0:46	Modified:	20 Jan 2014 7:01
Reporter:	Roel Van de Paar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> create table t1 (c1 char(20)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t1 modify c1 varchar (0);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

But, add a PK and it fails:

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t1 (c1 char(20) primary key) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t1 modify c1 varchar (0);
ERROR 1167 (42000): The used storage engine can't index column 'c1'

How to repeat:
drop table if exists t1;
create table t1 (c1 char(20) primary key) engine=innodb;
alter table t1 modify c1 varchar (0);

Suggested fix:
- Don't fail if a key is present if it doesn't fail when a key is not present.
- Out of interest: why does MySQL allow CHAR(0)/VARCHAR(0) columns?

This causes a lot of RQG errors due to random (x) insertions. Off course it could be avoided in the grammar, but that would reduce crash testing boundary value coverage.

Hello Roel,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

This is equivalent behavior to when creating tables:

mysql> create table t1 (c1 char(0)) engine=innodb;
Query OK, 0 rows affected (0.26 sec)

mysql> create table t1 (c1 char(0) primary key) engine=innodb;
ERROR 1167 (42000): The used storage engine can't index column 'c1'

The manual contains a paragraph on why CHAR(0) is allowed. (See http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html)

It is not obvious to me that one should be able to create indexes on all types supported.  Hence, closing this as not a bug.

Øystein, agree. Thanks