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:
Tags: information_schema.STATISTICS, SHOW CREATE TABLE, USING HASH
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[23 Sep 2006 20:45] Roland Bouman
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.
[4 Oct 2006 13:48] Baron Schwartz
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.
[12 Oct 2006 17:42] Sergei Golubchik
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.
[6 May 2007 8:11] Roland Bouman
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
[6 May 2007 23:50] Jon Stephens
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.
[7 May 2007 6:52] Roland Bouman
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.
[4 Dec 2015 18:37] Seth Willits
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.