WL#10744: Full-Text Index Type

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Support creation of Full-Text Index of a Sub-Document

Full-Text indexes allow searching the entire document (or a sub-document) for any 
text value contained.

Full-Text search of JSON documents may require a custom fulltext parser and/or 
additional querying extensions to be fully useful (e.g. json_textcontains() from 
Oracle).
## External Requirements

A xprotocol client

X1
:  MUST be able to create a FULLTEXT index on document of a collection

X2
:  MUST be able to create a FULLTEXT index on a sub-document of a collection

X3
:  MUST be able to optionally specify the name of a fulltext parser for a FULLTEXT index

X4
:  MUST be able to specify the name of the index

X5
:  MUST be able to specify if the (sub-)document has to exist

X6
:  MUST be able to drop the index by name via the drop_index admin command.

X7
:  MUST receive an Error message if the create_fulltext_index operation fails.

## Internal Requirements

The create_fulltext_index operation

F1
:  MUST fail if generated ALTER TABLE statement fails like

  * wrong fulltext parser
  * names too long
  * index with the same name exists
  * generated column with the same name exists

F2
:  MUST fail if a 'unique' FULLTEXT is requested
Background
==========

In the xplugin the `create_collection_index` `StmtExecute` command
of the `mysqlx` namespace is used to create indexes for collections.

See: `admin_cmd_handler.cc`

It takes the following arguments:

* schema: string
* collection: string
* name: string
* unique: bool
* type: string [optional, default='INDEX', allowed=INDEX|SPATIAL]
* constraint: object or list-of-objects
  * member: docpath
  * required: bool
  * type: string

Adding FULLTEXT support
=======================

new type: FULLTEXT
------------------

A new value for `.type` is to be supported in the index field specification:

    FULLTEXT

new, optional `.with_parser`
---------------------------

To specify the fulltext parser plugin a `FULLTEXT` index may specify:

    with_parser: string

default constraint.type
-----------------------

If no constraint.type is given, it defaults to:

    TEXT(64)

parameter schema
----------------

* schema: string
* collection: string
* name: string
* unique: bool
* type: string [optional, default='INDEX', allowed=INDEX|SPATIAL|FULLTEXT]
* with_parser: string [optional, default=empty]
* constraint: object or list-of-objects
  * member: docpath
  * required: bool
  * type: string [optional]

SQL generation
==============

ALTER TABLE, ADD COLUMN part:

    ADD COLUMN <col> TEXT
      GENERATED ALWAYS AS (JSON_EXTRACT(doc, <member>)) STORED

Notes:

* FULLTEXT indexes can currently only be created on STORED generated columns, not VIRTUAL
* FULLTEXT indexes cannot be created on a JSON column
* FULLTEXT indexes allow NULLable columns

ALTER TABLE, ADD INDEX part:

    ADD FULLTEXT INDEX <name> (<col>) WITH PARSER <with_parser>

Notes:

* the content of `with_parser` expects an identifier. Use identifier quoting if special characters are used.


Error and Warnings
==================

|Property       |Value                                                       |
|---------------|------------------------------------------------------------|
|Error code     |ER_X_CMD_ARGUMENT_VALUE                                     |
|Error text     |Unique fulltext index is not supported                      |
|New error code |No                                                          |


|Property       |Value                                                       |
|---------------|------------------------------------------------------------|
|Error code     |ER_X_CMD_ARGUMENT_VALUE                                     |
|Error text     |'with_parser' argument is supported for fulltext index only |
|New error code |No                                                          |


Example
-------

* schema: foo
* collection: col1
* name: ftidx
* unique: FALSE
* type: FULLTEXT
* with_parser: ngram
* constraint:
  * member: '$.subdoc'
  * required: FALSE

results in:

    ALTER TABLE col1
      ADD COLUMN ft TEXT
        GENERATED ALWAYS AS (doc->>'$.subdoc')) STORED,
      ADD FULLTEXT INDEX ftidx (ft) WITH PARSER ngram;

If `with_parser` is not specified:

    ALTER TABLE col1
      ADD COLUMN ft TEXT
        GENERATED ALWAYS AS (doc->>'$.subdoc')) STORED,
      ADD FULLTEXT INDEX ftidx (ft);