table.index() not adding SPATIAL index for geometry column

[Frondor]

Environment

Knex version: 0.14.4
Database + version: MariaDB 10.2

Issue

At creating geospatial columns, when creating the indexes, it does not create the SPATIAL index, plus getting Error: BLOB/TEXT column 'locations' used in key specification without a key length, and as far as I know, you can't set a length to MULTIPOINT

table.specificType('locations', 'MULTIPOINT').notNullable()
table.index(['locations'], null, ['SPATIAL'])

table.specificType('location', 'POINT').notNullable()
table.index(['location'], null, 'SPATIAL')

Error:

[ { sql: 'create table `merchants` (`id` int unsigned not null auto_increment primary key, `category_id` int unsigned not null, `user_id` int unsigned not null, `locations` MULTIPOINT not null, `created_at` datetime, `updated_at` datetime)',
    bindings: [] },
  { sql: 'alter table `merchants` add constraint `merchants_category_id_foreign` foreign key (`category_id`) references `categories` (`id`) on delete CASCADE',
    bindings: [] },
  { sql: 'alter table `merchants` add constraint `merchants_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete CASCADE',
    bindings: [] },
  { sql: 'alter table `merchants` add index `merchants_locations_index`(`locations`)',
    bindings: [] } ]
[ { sql: 'drop table if exists `adonis_schema_lock`',
    bindings: [] } ]
{ Error: BLOB/TEXT column 'locations' used in key specification without a key length ... }
]

[elhigu]

Which would be expected queries in this case? I suppose this one is not what you were expecting:

alter table `merchants` add index `merchants_locations_index`(`locations`)

[Frondor]

Uh, forgot it, my bad:
ALTER TABLE merchants ADD SPATIAL INDEX merchants_locations_index (locations) (probably with)

[elhigu]

Looks like a bug then 👍 Need to check out if that is broken in all dialects.

[Frondor]

@elhigu I was giving it a try, and found out this bug has nothing to do with SPATIAL indexes, It's actually a limitation of the knex api.
As you can see here #203 (comment) (another issue that needs attention for this)
table.index(['location'], null, 'ANYTHING') is not using the index type in the query.

http://knexjs.org/#Schema-index the docs actually say you can provide the index type:

index — table.index(columns, [indexName], [indexType])
Adds an index to a table over the given columns. A default index name using the columns is used unless indexName is specified. The indexType can be optionally specified for PostgreSQL. Amazon Redshift does not allow creating an index.

But mysql dialects should also support the indexType for these use cases:

https://github.com/tgriesser/knex/blob/ee8cc35ecb41a8bc809f753b56e64691bd3fa25c/src/dialects/mysql/schema/tablecompiler.js#L160
https://github.com/tgriesser/knex/blob/ee8cc35ecb41a8bc809f753b56e64691bd3fa25c/src/dialects/mssql/schema/tablecompiler.js#L105
https://github.com/tgriesser/knex/blob/ee8cc35ecb41a8bc809f753b56e64691bd3fa25c/src/dialects/mysql/schema/tablecompiler.js#L160
https://github.com/tgriesser/knex/blob/ee8cc35ecb41a8bc809f753b56e64691bd3fa25c/src/dialects/oracle/schema/tablecompiler.js#L100

[Frondor]

Any updates on this?

[elhigu]

@Frondor I'm sure you would have known if you have done anything about this :) I highly doubt that anyone else is going to fix it.