Merge lp:~laurynas-biveinis/percona-server/tokudb-multiple-clust-keys into lp:percona-server/5.6

[Laurynas Biveinis]

2nd MP:

- Extended the testcase to test CREATE CLUSTERING INDEX ON, to test
  CLUSTERING in combination with UNIQUE (and CONSTRAINT if UNIQUE),
  to test LOCK and ALGORITHM clauses for ALTER TABLE ADD INDEX, and
  to test the same clauses without CLUSTERING.
- Declare new handlerton flag HTON_SUPPORTS_CLUSTERED_KEYS.
- Assign values to enum Key::Keytype to support or'ing of enum
  constants.
- In mysql_prepare_create_table(), allow presence of and handle
  Key::CLUSTERING and Key::UNIQUE in a same key->type. Check for
  HTON_SUPPORTS_CLUSTERED_KEYS if Key::CLUSTERING found, and return
  HA_ILLEGAL_HA_CREATE_OPTION if unsupported.
- In mysql_prepare_alter_table(), handle both HA_CLUSTERING and
  HA_NOSAME if both present in the same key_info->flags.
- In parser, replace opt_unique clause with opt_unique_or_clustering
  that allows UNIQUE, CLUSTERING, and a combination of the two where
  previously only UNIQUE was allowed in key definitions and CREATE
  INDEX statement. This also fixes a bug from the previous approach
  which did not allow ALGORITHM and LOCK clauses for ALTER TABLE
  ... CLUSTERING ...

http://jenkins.percona.com/job/percona-server-5.6-param/494/

1st MP:

    Add support for CLUSTERING key type that is a clustering secondary
    key, that is, the whole of the row can be accessed through the index
    without going through the primary clustered index. This implements
    the query parser and index definition persistence bits,
    https://blueprints.launchpad.net/percona-server/+spec/multiple-clustering-keys.

    - New lexer symbol CLUSTERING, new key type in the grammar CREATE TABLE and
      ALTER TABLE ADD INDEX clauses.
    - New key type Key::Keytype::CLUSTERING, handle it in
      INFORMATION_SCHEMA.TABLES, write it out to FRM as a combination of
      spatial and fulltext key types, handle this combination on FRM
      read. This is to avoid a FRM format change.
    - New testcase tokudb_clustering_key_grammar.

http://jenkins.percona.com/job/percona-server-5.6-param/473/

[Alexey Kopytov (akopytov)]

- s/INFORMATION_SCHEMA.TABLES/SHOW CREATE TABLE/ in the revision comment

- I don’t think we should allow creation of non-TokuDB tables with
  secondary clustering keys. Otherwise once the optimizer support is
  reviewed and merged, we will get optimizer making incorrect
  assumptions about secondary clustering keys for tables that don’t
  actually implement it. On top of that, bad and hard-to-diagnose
  things may happen on downgrade or migration to another server flavor;

- I see there’s no support for UNIQUE CLUSTERING KEYs. Let’s fix it if
  it’s an oversight, or document if it’s a known limitation?

- any specific reasons to add another reserved keyword to the parser?

review: Needs Fixing

[Laurynas Biveinis (laurynas-biveinis)]

Grammar rules for CLUSTERING do not include opt_index_lock_algorithm, which suggests that

ALTER TABLE t1 ADD CLUSTERING INDEX b (b), LOCK=EXCLUSIVE, ALGORITHM=COPY;

syntax is not supported. It probably should be.

review: Needs Fixing

[Laurynas Biveinis (laurynas-biveinis)]

CREATE CLUSTERING INDEX b ON t1 (b)

syntax is not tested by the testcase.

CREATE CLUSTERING INDEX b ON t1 (b) LOCK=EXCLUSIVE ALGORITHM=COPY

fails with a parse error due to that missing opt_index_lock_algorithm.

review: Needs Fixing

[Laurynas Biveinis (laurynas-biveinis)]

> - s/INFORMATION_SCHEMA.TABLES/SHOW CREATE TABLE/ in the revision
> comment

Fixed.

> - I don’t think we should allow creation of non-TokuDB tables with
> secondary clustering keys. Otherwise once the optimizer support is
> reviewed and merged, we will get optimizer making incorrect
> assumptions about secondary clustering keys for tables that don’t
> actually implement it. On top of that, bad and hard-to-diagnose
> things may happen on downgrade or migration to another server
> flavor;

I agree and this was how I implemented it the first time before
Tokutek asked me to change to be treated as a hint. I didn't think of
downgrade/migration issues then or I wouldn't have changed. (I don't
see how the optimizer could make incorrect assumptions because no
other storage engine would return HA_CLUSTERING in index_flags()).

Added a handlerton flag HTON_SUPPORTS_CLUSTERED_KEYS. This is
different from e.g. HA_CAN_RTREE etc, which are handler flags, but the
property of supporting clustered keys seems to me to be global and not
of table level for a SE.

For error message I reused ER_ILLEGAL_HA_CREATE_OPTION ("Table storage
engine 'InnoDB' does not support the create option 'CLUSTERING'")

> - I see there’s no support for UNIQUE CLUSTERING KEYs. Let’s fix it if
> it’s an oversight, or document if it’s a known limitation?

An oversight on my part, not sure about Tokutek. Syntax extended as
follows. Where previously "UNIQUE" was accepted as a valid modifier,
"CLUSTERING" is accepted too, as well as "UNIQUE CLUSTERING" and
"CLUSTERING UNIQUE":
- CREATE UNIQUE INDEX;
- as a part of key definition:
  - CREATE TABLE (..., [CONSTRAINT ... ] UNIQUE KEY ...,)
  - ALTER TABLE ADD [CONSTRAINT ...] UNIQUE KEY

The way the grammar was changed, it accepts "UNIQUE UNIQUE" and
"CLUSTERING CLUSTERING" too. IMHO benign.

Also now it is possible to create such "constraints" as CONSTRAINT c1
CLUSTERING KEY, which is not really a constraint at all, but again
IMHO benign and I don't see an easy way to forbid it without blowing
up shift/reduce conflict count in the parser.

One place where the syntax was not extended was field attribute
syntax:
  - CREATE TABLE (..., b INT UNIQUE CLUSTERING KEY, ...).
It appeared to require more changes to the parser, and can be done in
a follow-up if really needed.

>
> - any specific reasons to add another reserved keyword to the
> parser?

I have reviewed the existing keywords and couldn't choose any IMHO
suitable replacement candidate. Also, I'm not sure Tokutek would
appreciate a forced mass search-replace in their testsuite that also
makes 5.5 to 5.6 upmerges less automatic.

[Alexey Kopytov (akopytov)]

>> - s/INFORMATION_SCHEMA.TABLES/SHOW CREATE TABLE/ in the revision
>> comment
>
> Fixed.
>
>> - I don’t think we should allow creation of non-TokuDB tables with
>> secondary clustering keys. Otherwise once the optimizer support is
>> reviewed and merged, we will get optimizer making incorrect
>> assumptions about secondary clustering keys for tables that don’t
>> actually implement it. On top of that, bad and hard-to-diagnose
>> things may happen on downgrade or migration to another server
>> flavor;
>
> I agree and this was how I implemented it the first time before
> Tokutek asked me to change to be treated as a hint. I didn't think of
> downgrade/migration issues then or I wouldn't have changed. (I don't
> see how the optimizer could make incorrect assumptions because no
> other storage engine would return HA_CLUSTERING in index_flags()).
>

The best way to handle this has been implemented in MariaDB via
SE-specific options in CREATE/ALTER. I doubt we should port those
extensions as a part of this work, just pointing out that the way we are
implementing it will inevitably have unwanted side effects.

Wrt optimizer changes: there’s code in the optimizer that takes flags
from key definition into account (rather than just flags returned by the
storage engine). For example, some code checks key definition flags and
assumes it to be a regular key if none if the previous checks for
specific flags return true. If you allow CLUSTERING keys for storage
engine that don’t support them, you still get the HA_CLUSTERING key set
in key definition flags.

> Added a handlerton flag HTON_SUPPORTS_CLUSTERED_KEYS. This is
> different from e.g. HA_CAN_RTREE etc, which are handler flags, but the
> property of supporting clustered keys seems to me to be global and not
> of table level for a SE.
>
> For error message I reused ER_ILLEGAL_HA_CREATE_OPTION ("Table storage
> engine 'InnoDB' does not support the create option 'CLUSTERING'")
>

That looks good in general. Please also add code that enforces this
restriction to open_binary_frm(). I.e. it should fail with an error if a
non-InnoDB table happens to contain both HA_SPATIAL and HA_FULLTEXT
flags for an index.

>> - I see there’s no support for UNIQUE CLUSTERING KEYs. Let’s fix it if
>> it’s an oversight, or document if it’s a known limitation?
>
> An oversight on my part, not sure about Tokutek. Syntax extended as

Are you going to report it to Tokutek?

> follows. Where previously "UNIQUE" was accepted as a valid modifier,
> "CLUSTERING" is accepted too, as well as "UNIQUE CLUSTERING" and
> "CLUSTERING UNIQUE":
> - CREATE UNIQUE INDEX;
> - as a part of key definition:
> - CREATE TABLE (..., [CONSTRAINT ... ] UNIQUE KEY ...,)
> - ALTER TABLE ADD [CONSTRAINT ...] UNIQUE KEY
>
> The way the grammar was changed, it accepts "UNIQUE UNIQUE" and
> "CLUSTERING CLUSTERING" too. IMHO benign.
>

Hm, doesn’t look that benign to me. UNIQUE UNIQUE is invalid SQL which a
user may want to be aware of. It would be aware before the change. It
will not be aware after the change.

> Also now it is possible to create such "constraints" as CONSTRAINT c1
> CLUSTERING KEY, which is not really a constraint at a...

review: Needs Fixing

=== modified file 'include/my_base.h'
--- include/my_base.h	2013-12-05 17:23:10 +0000
+++ include/my_base.h	2014-02-26 15:07:38 +0000
@@ -258,11 +258,13 @@
 #define HA_SPATIAL		1024    /* For spatial search */
 #define HA_NULL_ARE_EQUAL	2048	/* NULL in key are cmp as equal */
 #define HA_GENERATED_KEY	8192	/* Automaticly generated key */
+#define HA_CLUSTERING           (1<<31) /* TokuDB CLUSTERING key */
 
         /* The combination of the above can be used for key type comparison. */
 #define HA_KEYFLAG_MASK (HA_NOSAME | HA_PACK_KEY | HA_AUTO_KEY | \
                          HA_BINARY_PACK_KEY | HA_FULLTEXT | HA_UNIQUE_CHECK | \
-                         HA_SPATIAL | HA_NULL_ARE_EQUAL | HA_GENERATED_KEY)
+                         HA_SPATIAL | HA_NULL_ARE_EQUAL | HA_GENERATED_KEY | \
+                         HA_CLUSTERING)
 
 /*
   Key contains partial segments.

=== modified file 'include/mysql_com.h'
--- include/mysql_com.h	2013-08-14 03:57:21 +0000
+++ include/mysql_com.h	2014-02-26 15:07:38 +0000
@@ -126,6 +126,8 @@
 #define FIELD_FLAGS_COLUMN_FORMAT 24    /* Field column format, bit 24-25 */
 #define FIELD_FLAGS_COLUMN_FORMAT_MASK (3 << FIELD_FLAGS_COLUMN_FORMAT)
 #define FIELD_IS_DROPPED (1<< 26)       /* Intern: Field is being dropped */
+#define CLUSTERING_FLAG (1 << 27)       /* Field has a secondary clustering
+                                        key */
 
 #define REFRESH_GRANT		1	/* Refresh grant tables */
 #define REFRESH_LOG		2	/* Start on new log file */

=== added file 'mysql-test/r/tokudb_clustering_key_grammar.result'
--- mysql-test/r/tokudb_clustering_key_grammar.result	1970-01-01 00:00:00 +0000
+++ mysql-test/r/tokudb_clustering_key_grammar.result	2014-02-26 15:07:38 +0000
@@ -0,0 +1,73 @@
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CLUSTERING KEY b (b)) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+ALTER TABLE t1 ADD CLUSTERING INDEX b (b);
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE CLUSTERING INDEX b ON t1 (b) LOCK=EXCLUSIVE ALGORITHM=COPY;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+ALTER TABLE t1 ADD CLUSTERING INDEX b (b), LOCK=EXCLUSIVE, ALGORITHM=COPY;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE CLUSTERING KEY b (b)) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE UNIQUE KEY b (b)) ENGINE=InnoDB;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE KEY b (b)) ENGINE=InnoDB' at line 1
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CLUSTERING CLUSTERING KEY b (b)) ENGINE=InnoDB;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CLUSTERING KEY b (b)) ENGINE=InnoDB' at line 1
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CLUSTERING UNIQUE KEY b (b)) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 UNIQUE CLUSTERING KEY b (b)) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 CLUSTERING UNIQUE KEY b (b)) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 UNIQUE UNIQUE KEY b (b)) ENGINE=InnoDB;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE KEY b (b)) ENGINE=InnoDB' at line 1
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 CLUSTERING KEY b (b)) ENGINE=InnoDB;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ENGINE=InnoDB' at line 1
+ALTER TABLE t1 ADD UNIQUE CLUSTERING INDEX b (b);
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+ALTER TABLE t1 ADD CLUSTERING UNIQUE INDEX b (b);
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+ALTER TABLE t1 ADD UNIQUE UNIQUE INDEX b (b);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE INDEX b (b)' at line 1
+ALTER TABLE t1 ADD CLUSTERING CLUSTERING INDEX b (b);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CLUSTERING INDEX b (b)' at line 1
+ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE CLUSTERING INDEX b (b);
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+ALTER TABLE t1 ADD CONSTRAINT c1 CLUSTERING UNIQUE INDEX b (b);
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE UNIQUE INDEX b (b);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE INDEX b (b)' at line 1
+ALTER TABLE t1 ADD CONSTRAINT c1 CLUSTERING INDEX b (b);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT CLUSTERING) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT CLUSTERING KEY) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT CLUSTERING UNIQUE) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT CLUSTERING UNIQUE KEY) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT UNIQUE CLUSTERING) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE t1 (a INT UNIQUE CLUSTERING KEY) ENGINE=InnoDB;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'CLUSTERING'
+CREATE TABLE CLUSTERING(a INT) ENGINE=InnoDB;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CLUSTERING(a INT) ENGINE=InnoDB' at line 1
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, g INT, UNIQUE KEY b (b)) ENGINE=InnoDB;
+ALTER TABLE t1 ADD UNIQUE INDEX c (c), LOCK=EXCLUSIVE, ALGORITHM=COPY;
+ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE INDEX g (g);
+ALTER TABLE t1 ADD INDEX d (d);
+CREATE INDEX e ON t1 (e) LOCK=EXCLUSIVE ALGORITHM=COPY;
+CREATE UNIQUE INDEX f ON t1 (f);
+DROP TABLE t1;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY b (b)) ENGINE=InnoDB;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE b (b)) ENGINE=InnoDB;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 UNIQUE b (b)) ENGINE=InnoDB;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT UNIQUE) ENGINE=InnoDB;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT UNIQUE KEY) ENGINE=InnoDB;
+DROP TABLE t1;

=== added file 'mysql-test/t/tokudb_clustering_key_grammar.test'
--- mysql-test/t/tokudb_clustering_key_grammar.test	1970-01-01 00:00:00 +0000
+++ mysql-test/t/tokudb_clustering_key_grammar.test	2014-02-26 15:07:38 +0000
@@ -0,0 +1,99 @@
+#
+# Test that TokuDB CLUSTERING key grammar addition syntax is understood (does not fail with
+# ER_PARSE_ERROR), but that non-supporting storage engines reject it by returning
+# ER_ILLEGAL_HA_CREATE_OPTION.
+#
+--source include/have_innodb.inc
+
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CLUSTERING KEY b (b)) ENGINE=InnoDB;
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+
+--error ER_ILLEGAL_HA_CREATE_OPTION
+ALTER TABLE t1 ADD CLUSTERING INDEX b (b);
+
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE CLUSTERING INDEX b ON t1 (b) LOCK=EXCLUSIVE ALGORITHM=COPY;
+
+--error ER_ILLEGAL_HA_CREATE_OPTION
+ALTER TABLE t1 ADD CLUSTERING INDEX b (b), LOCK=EXCLUSIVE, ALGORITHM=COPY;
+
+# CLUSTERING can be combined with UNIQUE, but neither of them can be combined with itself.
+# CLUSTERING cannot be the sole key type for a CONSTRAINT.
+
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE CLUSTERING KEY b (b)) ENGINE=InnoDB;
+--error ER_PARSE_ERROR
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE UNIQUE KEY b (b)) ENGINE=InnoDB;
+--error ER_PARSE_ERROR
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CLUSTERING CLUSTERING KEY b (b)) ENGINE=InnoDB;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CLUSTERING UNIQUE KEY b (b)) ENGINE=InnoDB;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 UNIQUE CLUSTERING KEY b (b)) ENGINE=InnoDB;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 CLUSTERING UNIQUE KEY b (b)) ENGINE=InnoDB;
+--error ER_PARSE_ERROR
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 UNIQUE UNIQUE KEY b (b)) ENGINE=InnoDB;
+--error ER_PARSE_ERROR
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 CLUSTERING KEY b (b)) ENGINE=InnoDB;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+ALTER TABLE t1 ADD UNIQUE CLUSTERING INDEX b (b);
+--error ER_ILLEGAL_HA_CREATE_OPTION
+ALTER TABLE t1 ADD CLUSTERING UNIQUE INDEX b (b);
+--error ER_PARSE_ERROR
+ALTER TABLE t1 ADD UNIQUE UNIQUE INDEX b (b);
+--error ER_PARSE_ERROR
+ALTER TABLE t1 ADD CLUSTERING CLUSTERING INDEX b (b);
+--error ER_ILLEGAL_HA_CREATE_OPTION
+ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE CLUSTERING INDEX b (b);
+--error ER_ILLEGAL_HA_CREATE_OPTION
+ALTER TABLE t1 ADD CONSTRAINT c1 CLUSTERING UNIQUE INDEX b (b);
+--error ER_PARSE_ERROR
+ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE UNIQUE INDEX b (b);
+--error ER_PARSE_ERROR
+ALTER TABLE t1 ADD CONSTRAINT c1 CLUSTERING INDEX b (b);
+
+DROP TABLE t1;
+
+# Test CLUSTERING as a field attribute, alone and in combination with UNIQUE
+
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT CLUSTERING) ENGINE=InnoDB;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT CLUSTERING KEY) ENGINE=InnoDB;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT CLUSTERING UNIQUE) ENGINE=InnoDB;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT CLUSTERING UNIQUE KEY) ENGINE=InnoDB;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT UNIQUE CLUSTERING) ENGINE=InnoDB;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+CREATE TABLE t1 (a INT UNIQUE CLUSTERING KEY) ENGINE=InnoDB;
+
+# CLUSTERING is not allowed as an identifier due to causing extra shift/reduce parser conflicts
+--error ER_PARSE_ERROR
+CREATE TABLE CLUSTERING(a INT) ENGINE=InnoDB;
+
+
+# Check that the grammar changes have not broken related CLUSTERING-less clauses
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, g INT, UNIQUE KEY b (b)) ENGINE=InnoDB;
+ALTER TABLE t1 ADD UNIQUE INDEX c (c), LOCK=EXCLUSIVE, ALGORITHM=COPY;
+ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE INDEX g (g);
+ALTER TABLE t1 ADD INDEX d (d);
+CREATE INDEX e ON t1 (e) LOCK=EXCLUSIVE ALGORITHM=COPY;
+CREATE UNIQUE INDEX f ON t1 (f);
+DROP TABLE t1;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY b (b)) ENGINE=InnoDB;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE b (b)) ENGINE=InnoDB;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, CONSTRAINT c1 UNIQUE b (b)) ENGINE=InnoDB;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT UNIQUE) ENGINE=InnoDB;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT UNIQUE KEY) ENGINE=InnoDB;
+DROP TABLE t1;

=== modified file 'sql/handler.h'
--- sql/handler.h	2014-02-25 07:30:22 +0000
+++ sql/handler.h	2014-02-26 15:07:38 +0000
@@ -1024,6 +1024,11 @@
 */
 #define HTON_SUPPORTS_ONLINE_BACKUPS (1 << 11)
 
+/**
+  Engine supports secondary clustered keys.
+*/
+#define HTON_SUPPORTS_CLUSTERED_KEYS (1 << 12)
+
 enum enum_tx_isolation { ISO_READ_UNCOMMITTED, ISO_READ_COMMITTED,
 			 ISO_REPEATABLE_READ, ISO_SERIALIZABLE};
 

=== modified file 'sql/lex.h'
--- sql/lex.h	2013-12-16 12:54:12 +0000
+++ sql/lex.h	2014-02-26 15:07:38 +0000
@@ -115,6 +115,7 @@
   { "CLIENT",		SYM(CLIENT_SYM)},
   { "CLIENT_STATISTICS",	SYM(CLIENT_STATS_SYM)},
   { "CLOSE",		SYM(CLOSE_SYM)},
+  { "CLUSTERING",       SYM(CLUSTERING_SYM)},
   { "COALESCE",		SYM(COALESCE)},
   { "CODE",             SYM(CODE_SYM)},
   { "COLLATE",		SYM(COLLATE_SYM)},

=== modified file 'sql/sql_class.h'
--- sql/sql_class.h	2014-02-25 07:30:22 +0000
+++ sql/sql_class.h	2014-02-26 15:07:38 +0000
@@ -294,7 +294,8 @@
 
 class Key :public Sql_alloc {
 public:
-  enum Keytype { PRIMARY, UNIQUE, MULTIPLE, FULLTEXT, SPATIAL, FOREIGN_KEY};
+  enum Keytype { PRIMARY= 0, UNIQUE= 1, MULTIPLE= 2, FULLTEXT= 4, SPATIAL= 8,
+                 FOREIGN_KEY= 16, CLUSTERING= 32 };
   enum Keytype type;
   KEY_CREATE_INFO key_create_info;
   List<Key_part_spec> columns;

=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc	2014-02-25 07:30:22 +0000
+++ sql/sql_parse.cc	2014-02-26 15:07:38 +0000
@@ -6920,13 +6920,20 @@
     lex->alter_info.key_list.push_back(key);
     lex->col_list.empty();
   }
-  if (type_modifier & (UNIQUE_FLAG | UNIQUE_KEY_FLAG))
+  if (type_modifier & (UNIQUE_FLAG | UNIQUE_KEY_FLAG | CLUSTERING_FLAG))
   {
-    Key *key;
+    Key::Keytype keytype= (enum Key::Keytype)0;
+    if (type_modifier & (UNIQUE_FLAG | UNIQUE_KEY_FLAG))
+      keytype= Key::UNIQUE;
+    if (type_modifier & CLUSTERING_FLAG)
+      keytype= (enum Key::Keytype)(keytype | Key::CLUSTERING);
+    /* The keytype must be set up by the above or we will end up attempting
+    to create a PRIMARY (== 0) key */
+    DBUG_ASSERT(keytype != 0);
+
     lex->col_list.push_back(new Key_part_spec(*field_name, 0));
-    key= new Key(Key::UNIQUE, null_lex_str,
-                 &default_key_create_info, 0,
-                 lex->col_list);
+    Key *key= new Key(keytype, null_lex_str, &default_key_create_info, 0,
+                      lex->col_list);
     lex->alter_info.key_list.push_back(key);
     lex->col_list.empty();
   }

=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc	2013-12-16 12:54:12 +0000
+++ sql/sql_show.cc	2014-02-26 15:07:38 +0000
@@ -1525,6 +1525,8 @@
       packet->append(STRING_WITH_LEN("FULLTEXT KEY "));
     else if (key_info->flags & HA_SPATIAL)
       packet->append(STRING_WITH_LEN("SPATIAL KEY "));
+    else if (key_info->flags & HA_CLUSTERING)
+      packet->append(STRING_WITH_LEN("CLUSTERING KEY "));
     else
       packet->append(STRING_WITH_LEN("KEY "));
 
@@ -5227,6 +5229,7 @@
     store_column_type(table, field, cs, IS_COLUMNS_DATA_TYPE);
     pos=(uchar*) ((field->flags & PRI_KEY_FLAG) ? "PRI" :
                  (field->flags & UNIQUE_KEY_FLAG) ? "UNI" :
+                 (field->flags & CLUSTERING_FLAG) ? "CLU" :
                  (field->flags & MULTIPLE_KEY_FLAG) ? "MUL":"");
     table->field[IS_COLUMNS_COLUMN_KEY]->store((const char*) pos,
                             strlen((const char*) pos), cs);

=== modified file 'sql/sql_table.cc'
--- sql/sql_table.cc	2013-12-16 08:45:31 +0000
+++ sql/sql_table.cc	2014-02-26 15:07:38 +0000
@@ -3757,7 +3757,7 @@
 	break;
     }
 
-    switch (key->type) {
+    switch ((unsigned)key->type) {
     case Key::MULTIPLE:
 	key_info->flags= 0;
 	break;
@@ -3780,6 +3780,19 @@
     case Key::FOREIGN_KEY:
       key_number--;				// Skip this key
       continue;
+    case Key::CLUSTERING | Key::UNIQUE:
+      key_info->flags= HA_NOSAME;
+      /* fall through */
+    case Key::CLUSTERING:
+      if (unlikely(!ha_check_storage_engine_flag(
+		     file->ht, HTON_SUPPORTS_CLUSTERED_KEYS)))
+      {
+	my_error(ER_ILLEGAL_HA_CREATE_OPTION, MYF(0),
+		 ha_resolve_storage_engine_name(file->ht), "CLUSTERING");
+	DBUG_RETURN(TRUE);
+      }
+      key_info->flags|= HA_CLUSTERING;
+      break;
     default:
       key_info->flags = HA_NOSAME;
       break;
@@ -7118,7 +7131,9 @@
         key_type= Key::FULLTEXT;
       else
         key_type= Key::MULTIPLE;
-      
+      if (key_info->flags & HA_CLUSTERING)
+	key_type= (enum Key::Keytype)(key_type | Key::CLUSTERING);
+
       if (index_column_dropped)
       {
         /*

=== modified file 'sql/sql_yacc.yy'
--- sql/sql_yacc.yy	2014-02-25 07:30:22 +0000
+++ sql/sql_yacc.yy	2014-02-26 15:07:38 +0000
@@ -1,5 +1,5 @@
-/*
-   Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights
+   reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -1029,7 +1029,7 @@
   Currently there are 161 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 162
+%expect 164
 
 /*
    Comments for TOKENS.
@@ -1114,6 +1114,7 @@
 %token  CLIENT_SYM
 %token  CLIENT_STATS_SYM
 %token  CLOSE_SYM                     /* SQL-2003-R */
+%token  CLUSTERING_SYM
 %token  COALESCE                      /* SQL-2003-N */
 %token  CODE_SYM
 %token  COLLATE_SYM                   /* SQL-2003-R */
@@ -1794,7 +1795,8 @@
         option_type opt_var_type opt_var_ident_type
 
 %type <key_type>
-        normal_key_type opt_unique constraint_key_type fulltext spatial
+        normal_key_type opt_unique_combo_clustering constraint_key_type 
+        fulltext spatial unique_opt_clustering unique_combo_clustering unique clustering
 
 %type <key_alg>
         btree_or_rtree
@@ -2445,7 +2447,7 @@
             }
             create_table_set_open_action_and_adjust_tables(lex);
           }
-        | CREATE opt_unique INDEX_SYM ident key_alg ON table_ident
+        | CREATE opt_unique_combo_clustering INDEX_SYM ident key_alg ON table_ident
           {
             if (add_create_index_prepare(Lex, $7))
               MYSQL_YYABORT;
@@ -6348,6 +6350,12 @@
         | opt_constraint constraint_key_type opt_ident key_alg
           '(' key_list ')' normal_key_options
           {
+            if (($1.length != 0) && ((enum Key::Keytype)$2 == Key::CLUSTERING))
+            {
+              /* Forbid "CONSTRAINT c CLUSTERING" */
+              my_parse_error(ER(ER_SYNTAX_ERROR));
+              MYSQL_YYABORT;
+            }
             if (add_create_index (Lex, $2, $3.str ? $3 : $1))
               MYSQL_YYABORT;
           }
@@ -6747,16 +6755,22 @@
             lex->type|= PRI_KEY_FLAG | NOT_NULL_FLAG;
             lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX;
           }
-        | UNIQUE_SYM
+        | unique_combo_clustering
           {
             LEX *lex=Lex;
-            lex->type|= UNIQUE_FLAG; 
+            if ($1 & Key::UNIQUE)
+              lex->type|= UNIQUE_FLAG; 
+            if ($1 & Key::CLUSTERING)
+              lex->type|= CLUSTERING_FLAG;
             lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX;
           }
-        | UNIQUE_SYM KEY_SYM
+        | unique_combo_clustering KEY_SYM
           {
             LEX *lex=Lex;
-            lex->type|= UNIQUE_KEY_FLAG; 
+            if ($1 & Key::UNIQUE)
+              lex->type|= UNIQUE_KEY_FLAG; 
+            if ($1 & Key::CLUSTERING)
+              lex->type|= CLUSTERING_FLAG;
             lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX; 
           }
         | COMMENT_SYM TEXT_STRING_sys { Lex->comment= $2; }
@@ -7139,7 +7153,8 @@
 
 constraint_key_type:
           PRIMARY_SYM KEY_SYM { $$= Key::PRIMARY; }
-        | UNIQUE_SYM opt_key_or_index { $$= Key::UNIQUE; }
+        | unique_combo_clustering opt_key_or_index { $$= $1; }
+
         ;
 
 key_or_index:
@@ -7158,9 +7173,37 @@
         | INDEXES {}
         ;
 
-opt_unique:
-          /* empty */  { $$= Key::MULTIPLE; }
-        | UNIQUE_SYM   { $$= Key::UNIQUE; }
+opt_unique_combo_clustering:
+          /* empty */          { $$= Key::MULTIPLE; }
+        | unique_combo_clustering
+        ;
+
+unique_combo_clustering:
+          clustering            { $$= $1; }
+        | unique_opt_clustering { $$= $1; }
+        ;
+
+unique_opt_clustering:
+          unique 
+          { 
+            $$= $1; 
+          }
+        | unique clustering
+          {
+            $$= (enum Key::Keytype)($1 | $2);
+          }
+        | clustering unique
+          {
+            $$= (enum Key::Keytype)($1 | $2);
+          }
+        ;        
+
+unique: 
+          UNIQUE_SYM     { $$= Key::UNIQUE; }
+        ;
+
+clustering:
+          CLUSTERING_SYM { $$= Key::CLUSTERING; }
         ;
 
 fulltext:

=== modified file 'sql/table.cc'
--- sql/table.cc	2013-12-05 17:23:10 +0000
+++ sql/table.cc	2014-02-26 15:07:38 +0000
@@ -854,11 +854,24 @@
   KEY_PART_INFO *key_part= &keyinfo->key_part[key_part_n];
   Field *field= key_part->field;
 
-  /* Flag field as unique if it is the only keypart in a unique index */
+  /* Flag field as unique and/or clustering if it is the only keypart in a
+  unique/clustering index */
   if (key_part_n == 0 && key_n != primary_key_n)
-    field->flags |= (((keyinfo->flags & HA_NOSAME) &&
-                      (keyinfo->user_defined_key_parts == 1)) ?
-                     UNIQUE_KEY_FLAG : MULTIPLE_KEY_FLAG);
+  {
+    if (((keyinfo->flags & HA_NOSAME) &&
+         (keyinfo->user_defined_key_parts == 1)))
+      field->flags|= UNIQUE_KEY_FLAG;
+
+    if (((keyinfo->flags & HA_CLUSTERING) &&
+         (keyinfo->user_defined_key_parts == 1)))
+      field->flags|= CLUSTERING_FLAG;
+
+    if (!(field->flags & (UNIQUE_KEY_FLAG | CLUSTERING_FLAG)))
+    {
+      field->flags|= MULTIPLE_KEY_FLAG;
+    }
+  }
+
   if (key_part_n == 0)
     field->key_start.set_bit(key_n);
   if (field->key_length() == key_part->length &&
@@ -1133,6 +1146,18 @@
     if (new_frm_ver >= 3)
     {
       keyinfo->flags=	   (uint) uint2korr(strpos) ^ HA_NOSAME;
+      /* Replace HA_FULLTEXT & HA_SPATIAL with HA_CLUSTERING. This way we
+      support TokuDB clustering key definitions without changing the FRM
+      format. */
+      if (keyinfo->flags & HA_SPATIAL && keyinfo->flags & HA_FULLTEXT)
+      {
+        if (!ha_check_storage_engine_flag(share->db_type(),
+                                          HTON_SUPPORTS_CLUSTERED_KEYS))
+          goto err;
+        keyinfo->flags|= HA_CLUSTERING;
+        keyinfo->flags&= ~HA_SPATIAL;
+        keyinfo->flags&= ~HA_FULLTEXT;
+      }
       keyinfo->key_length= (uint) uint2korr(strpos+2);
       keyinfo->user_defined_key_parts= (uint) strpos[4];
       keyinfo->algorithm=  (enum ha_key_alg) strpos[5];

=== modified file 'sql/unireg.cc'
--- sql/unireg.cc	2013-12-05 17:23:10 +0000
+++ sql/unireg.cc	2014-02-26 15:07:38 +0000
@@ -628,7 +628,15 @@
   key_parts=0;
   for (key=keyinfo,end=keyinfo+key_count ; key != end ; key++)
   {
-    int2store(pos, (key->flags ^ HA_NOSAME));
+    /* Replace HA_CLUSTERING with HA_SPATIAL | HA_FULLTEXT to allow storing
+    TokuDB keys without changing the FRM format.  */
+    uint16 key_flags= (uint16)key->flags;
+    if (key->flags & HA_CLUSTERING)
+    {
+      key_flags|= HA_SPATIAL;
+      key_flags|= HA_FULLTEXT;
+    }
+    int2store(pos, (key_flags ^ HA_NOSAME));
     int2store(pos+2,key->key_length);
     pos[4]= (uchar) key->user_defined_key_parts;
     pos[5]= (uchar) key->algorithm;

=== modified file 'tests/mysql_client_test.c'
--- tests/mysql_client_test.c	2013-08-14 03:57:21 +0000
+++ tests/mysql_client_test.c	2014-02-26 15:07:38 +0000
@@ -4676,7 +4676,8 @@
         fprintf(stdout, "\n  MULTIPLE_KEY_FLAG");
       if (field->flags & AUTO_INCREMENT_FLAG)
         fprintf(stdout, "\n  AUTO_INCREMENT_FLAG");
-
+      if (field->flags & CLUSTERING_FLAG)
+        fprintf(stdout, "\n  CLUSTERING_FLAG");
     }
   }
   mysql_free_result(result);