Support parsing MySQL's USING [ BTREE | HASH ] index type syntax everywhere where it is supported

[knutwannheden]

As MySQL's CREATE TABLE allows for inline index creation, the statement also has the USING [ BTREE | HASH ] clause.

In MySQL BTREE is also the default for most storage engines, but for some it is also HASH.

Note that CREATE INDEX ... USING BTREE was already implemented in #7749.

[lukaseder]

I think we should focus on implementing your parser heuristics idea (#9084). We should collect a set of syntaxes that these heuristics should parse, and we can ignore them. That might scale a bit better in the future, than creating an issue on GitHub per syntax.

[knutwannheden]

I think we should focus on implementing your parser heuristics idea (#9084). We should collect a set of syntaxes that these heuristics should parse, and we can ignore them. That might scale a bit better in the future, than creating an issue on GitHub per syntax.

Yes, I also like that idea. I will look into it.

[lukaseder]

Related #9217

[lukaseder]

I don't fully trust those heuristics yet, as discussed in that other issue. Meanwhile, supporting this relatively common clause in the parser seems not too difficult. I'll take this and implement it "classically".

In fact, we might want to support index types natively (see #9230) and translate them between dialects, in case of which this particular syntax needs to be parsed explicitly anyway.

[lukaseder]

Heh, there's some additional fun caveat in the syntax:

| {INDEX|KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...

Where

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

So, this clause can be placed before or after the column list... :-)

[lukaseder]

Even this parses o_O

create table t (i int, index using btree (i) using hash);

[lukaseder]

And then, we can also use the syntax with constraints (which automatically create indexes, too)

[lukaseder]

It is, however, not possible with foreign key constraints

[lukaseder]

Notice, in issue #9120, the syntax exposing the problem was an ALTER TABLE .. ADD PRIMARY KEY (..) USING BTREE statement! See https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

[lukaseder]

It is not possible to specify these clauses on inline constraint definitions, such as:

create table t (i int primary key using btree);

[lukaseder]

These both work:

create table t (i int, primary key using btree (i));
create table t (i int, primary key (i) using btree);

[lukaseder]

Of course, this whole duality of supporting that index type before or after the column spec also exists in create index and are not yet supported by jOOQ. These both work:

create index i using btree on t (a);
create index i on t (a) using btree;