MySQL Bugs: #22632: SHOW CREATE TABLE is inconsistent in showing unsupported options

Bug #22632	SHOW CREATE TABLE is inconsistent in showing unsupported options
Submitted:	23 Sep 2006 20:45	Modified:	11 Oct 2006 17:54
Reporter:	Roland Bouman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.11-beta	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any
Tags:	information_schema.STATISTICS, SHOW CREATE TABLE, USING HASH

Description:
Creating an index with the USING HASH clause on a MyISAM or InnoDB does not result in a HASH index because HASH indexes are not supported for those storage engines. MySQL creates BTREE indexes instead. 

So far so good.

However, the SHOW CREATE TABLE output shows these indexes still with the USING HASH clause. This behaviour is inconsistent with that of the information_schema.STATISTICS system view. The information_schema.STATISTICS system view reports the actual index type, i.e. BTREE. The SHOW INDEX command also shows the actual index type, so the information_schema.STATISTICS system view is consistent with the SHOW INDEX command

How to repeat:
mysql> create table myisam_using_hash(
    ->     id int
    -> ,   key(id)
    ->     using hash
    -> ) engine = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table myisam_using_hash;
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                        |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| myisam_using_hash | CREATE TABLE `myisam_using_hash` (
  `id` int(11) DEFAULT NULL,
  KEY `id` (`id`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select index_name, index_type
    -> from   information_schema.statistics
    -> where  table_schema = schema()
    -> and    table_name = 'myisam_using_hash';

+------------+------------+
| index_name | index_type |
+------------+------------+
| id         | BTREE      |
+------------+------------+
1 row in set (0.00 sec)

mysql> show index from myisam_using_hash;
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| myisam_using_hash |          1 | id       |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Suggested fix:
In my opinion, the output from the SHOW CREATE TABLE statements should be equivalent to the output from the corresponding information_schema system view. It follows that SHOW CREATE TABLE should report the actual index type - NOT the declared type. 

On the other hand, I can imagine that some people feel there is merit in maintaining the original index definitions. It could be argued that it can be inferred that the index type cannot be HASH for other than MEMORY tables. In that case, the manual should clearly document the different output for these cases. Also, it would be nice if the SHOW CREATE TABLE output could use a sigle line comment that indicates that the actual index type is not HASH or something like that.

I think the SHOW CREATE output should show what's really there, not what the original CREATE TABLE statement said.  There's good precedent for this in other areas.  For example, in pre-5.0.3 if you created a BIT column it would be reported back as TINYINT, or mixing CHAR and VARCHAR columns really creates all VARCHARs which is shown by the output.  This definitely feels like a bug, not a feature.

The feature is that MySQL remembers USING HASH even for MyISAM tables. And if the table is later converted to HEAP, this clause will have an effect.
So, whoever fixes this bug (if there's anything to fix at all), don't break the above.

Hi Sergei, 

in itself, I think it's great that these things are remembered and I think it does make sense to use the SHOW CREATE TABLE statement to return the original definition (or something close to it)

That said, the memory is selective. For example, unsupported foreign key definitions are not remembered:

mysql> create table p(
    ->     id int not null primary key
    -> ) engine = MyISAM
    -> ;
Query OK, 0 rows affected (0.58 sec)

mysql> create table c(
    ->     p_id int not null
    -> ,   foreign key(p_id) references p(id)
    -> ) engine = MyISAM
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table c;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| c     | CREATE TABLE "c" (
  "p_id" int(11) NOT NULL,
  KEY "p_id" ("p_id")
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Another thing that comes to mind is the fact that some storage engines just fail particular index creation statements with an error, not allowing the table definition to remember anything at all about the index:

mysql> create table innodbfulltext(
    ->     t varchar(10)
    -> ,   fulltext index(t)
    -> );
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

mysql> create table t_csv(     id int unsigned not null  primary key ) engine = csv;
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

For the NDB engine, this is even true for the USING HASH/BTREE property:

mysql> create table t(
    ->     id int not null
    -> ,   name varchar(20)
    -> ,   primary key(id) using btree
    -> ,   index(name) using hash
    -> ) engine = ndb
    -> ;
ERROR 1005 (HY000): Can't create table 'test.t' (errno: 138)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                   |
+-------+------+---------------------------------------------------------------------------------------------------------------------------+
| Error | 1466 | Table storage engine 'ndbcluster' does not support the create option 'Ndb does not support non-unique hash based indexes' |
| Error | 1005 | Can't create table 'test.t' (errno: 138)                                                                                  |
+-------+------+---------------------------------------------------------------------------------------------------------------------------+

So this makes me wonder: how well defined is the behaviour of remembering the original definition? We should 
- make an effort to define reasonable behaviour (because no it seems rather hap-hazard), 
- verify if the current behaviour is reasonable (I think it is not at the moment, it seems very ad-hoc and dependent upon the engine), 
- make the current behaviour reasonable if necessary
- document the behaviour and its intention

How can something that never happened be "remembered"?

Statements that provide table metadata should accurately report what necessary to re-create the table as it *is*, not as some of us might like it to be.

If the current behaviour is intentional, then it's a misfeature, and needs to be fixed: SHOW statements should display the index types actually used.

Hi Jon,

Personally, I feel it's not that black and white - I feel it makes sense to remember the actual DDL that was issued. In a way, it has existed - as a syntactically valid DDL statement. It just happened to not match the specific semantics of the engine. 

If the metadata of the object that was actually created is available (for the particular case of the USING clause in index creation: SHOW INDEX STATUS, information_schema.STATISTICS), I feel it can be very useful to also be able to recreate the original DDL. That said, the functionality is now implemented just some of the time (why remember index types, but not foreign key constraints), and SHOW CREATE TABLE returns a mixture of some elements that were added during or after parsing the statement (not to mention that some DDL statements are rejected all together if the semantics don't match the particular engine, see the case for non-unique indexes USING HASH in NDB and f.e. InnoDB or MyISAM)

Personally, I would very much like a functionality where one could retrieve the actual, literal DDL as it was issued - including comments, (non significant) whitespace, preserving exact datatype synonyms and unsupported CONSTRAINT clauses (CHECK, FOREIGN KEY for non-innodb, index types etc. etc.), without implicit DEFAULT clauses etc, etc. as long as the metadata that reflects the actual state of affairs remains available through the information_schema.

I just discovered this issue myself, and it's quite confusing. It seems illogical that adding an index of a type (HASH) that is not supported by the table's current engine should be an error, not pretend to succeed and then inconsistently present what its type is.