Unify the syntax for index creation

[killme2008]

What type of enhancement is this?

API improvement

What does the enhancement do?

• Create an inverted index explicitly:

CREATE TABLE monitoring_data (
    host STRING,
    region STRING PRIMARY KEY,
    cpu_usage DOUBLE,
    ts TIMESTAMP TIME INDEX,
    INVERTED INDEX(host, region)
);

• Create a skipping index:

CREATE TABLE sensor_data (
    domain STRING PRIMARY KEY,
    device_id STRING SKIPPING INDEX,
    temperature DOUBLE,
    `timestamp` TIMESTAMP TIME INDEX
);

• Create a full-text index:

CREATE TABLE logs (
    message STRING FULLTEXT,
    `level` STRING PRIMARY KEY,
    `timestamp` TIMESTAMP TIME INDEX
);

Full-text and skipping indexes can only be created using column options, while inverted indexes require INVERTED INDEX(col1, col2...).

We should standardize index creation:

• Allow inverted indexes to be created using column options.
• Support INDEX [ INVERTED | FULLTEXT | SKIPPING](col1 options, col2 options, col3 options).

Implementation challenges

No response

[waynexia]

I propose to support index at two positions.

• Column constrain

<COLUMN_NAME> <COLUMN_TYPE> [OTHER_CONSTRAINS] [INDEX_TYPE INDEX [WITH (key = "value")]]

E.g.:

`level` STRING PRIMARY KEY SKIPPING INDEX WITH (option = 'a'),

• Table constrain

<INDEX_TYPE> INDEX (<COLUMN_LIST) [WITH (key = "value)]

E.g.:

CREATE TABLE logs (
    message STRING,
    `level` STRING PRIMARY KEY,
    `timestamp` TIMESTAMP TIME INDEX,
+    FULLTEXT INDEX (`message`) WITH (option = 'a')
);

Notable changes alongside the grammar:

• We should enforce the word INDEX on creating index, like FULLTEXT INDEX instead of just FULLTEXT
• We can't exclude an index, so I'd like to drop the logic of creating INVERTED INDEX for all tags by default