Support indexes on `TEXT` and `BLOB` column types

[pbowyer]

MySQL supports indexes on TEXT and BLOB types[^1] - in single and composite indexes. They require a length prefix:

When you index a BLOB or TEXT column, you must specify a prefix length for the index. -- https://dev.mysql.com/doc/refman/8.0/en/column-indexes.html

When I try and create a table that indexes them, Dolt errors:

mysql> CREATE TABLE `foo` ( bar text, INDEX `idx_bar` (`bar`(20)) );
ERROR 1105 (HY000): index on text column 'bar' unsupported
mysql> CREATE TABLE `foo` ( bar tinytext, INDEX `idx_bar` (`bar`(20)) );
ERROR 1105 (HY000): index on text column 'bar' unsupported
mysql> CREATE TABLE `foo` ( bar blob, INDEX `idx_bar` (`bar`(20)) );
ERROR 1105 (HY000): index on byte column 'bar' unsupported

I checked the length prefix is not a problem:

mysql> CREATE TABLE `foo` ( bar char(200), INDEX `idx_bar` (`bar`(20)) );
Query OK, 0 rows affected (0.05 sec)

Real-world failing examples:

CREATE TABLE `site_content` (
/* ... */
`uri` text,
KEY `uri` (`uri`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `site_tmplvar_contentvalues` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tmplvarid` int(10) NOT NULL DEFAULT '0',
  `contentid` int(10) NOT NULL DEFAULT '0',
  `value` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tmplvarid` (`tmplvarid`),
  KEY `contentid` (`contentid`),
  KEY `tv_cnt` (`tmplvarid`,`contentid`),
  KEY `idx_values` (`value`(64)),
  KEY `idx_type_values` (`tmplvarid`,`value`(64))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[^1]: The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

[timsehn]

I get it now. Dolt does not support blob indexes yet.

[timsehn]

That's a big feature. We will not be able to support this soon.

[timsehn]

Related: #2987

[ajeloy]

Hello, I also have the same problem. When I try to import a SQL database to the Dolt environment I get that error.

[zachmu]

We can support the syntax for this very easily, less than a day's work. But it would silently ignore the index declaration for these kinds of columns.

Getting actual, real index support for TEXT / BLOB prefixes is more challenging, around a month of work.

If you need this to just parse and are fine without an actual index for the time being, let us know and we'll get a release out.

[jycor]

Hello @pbowyer and @ajeloy, I just merged a fix for this (mostly) to main, and it'll be in the release for dolt coming out today (v0.50.16)!

Currently, prefix indexes are only supported for SECONDARY indexes, and not PRIMARY keys. So, you can do something like this:

create table t (i int primary key, v varchar(10), unique index (v(1)));

However, the indexes created that include a prefix length won't be used; I'm working on a fix for that (will maybe be a week or two). Regardless, all syntax should parse and the results should be correct. If y'all get a chance, please try these out and make issues if you run into any problems.