[MDEV-7687] Can't create SPATIAL index on top of persistent virtual column Created: 2015-03-10  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: GIS, Virtual Columns
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Alexey Botchkov
Resolution: Unresolved Votes: 7
Labels: None

Issue Links:
Duplicate
duplicates MDEV-12384 Cannot create SPATIAL/RTREE index on ... Closed
Relates
relates to MDEV-10964 Support for NULL/NOT NULL syntax for ... Open

 Description   

I don't have the details of error reported , but i remember server complaining about unique index

The workaround was to materialized the GEOM column not using virtual column :

alter table zip_all add column coord point not null
update zip_all set coord = POINT(lat,lon);
alter table zip_all_bis ADD SPATIAL INDEX(coord)

Thanks

 Comments   
Comment by Sergei Golubchik [ 2015-03-10 ]

Can't create SPATIAL index on top of persistent virtual column? Or not persistent?

Comment by VAROQUI Stephane [ 2015-03-10 ]

Hi Sergei, was persistent

Comment by Sergei Golubchik [ 2015-03-10 ]

what mariadb version?

Comment by Maxim Fedulov [ 2016-03-21 ]

Got same issue.

CREATE TABLE `spatial_test` (
 
	`id` INT NOT NULL AUTO_INCREMENT,
 
	`lat` FLOAT NOT NULL DEFAULT '0',
 
	`lon` FLOAT NOT NULL DEFAULT '0',
 
	`geo` GEOMETRY AS (point(lat,lon)) PERSISTENT,
 
	PRIMARY KEY (`id`),
 
	SPATIAL INDEX `geo` (`geo`)
 
)
 
ENGINE=Aria
 
;
 
ERROR 1252 (42000): All parts of a SPATIAL index must be NOT NULL

mysql Ver 15.1 Distrib 10.1.11-MariaDB, for FreeBSD10.2 (amd64) using readline 6.3

Comment by Sergei Golubchik [ 2017-04-03 ]

This happens because all generated columns are considered NULLable. This is wrong, the server should be able to derive generated column's nullability automatically. Optionally we could allow user to force it, as in MDEV-10964

Generated at Thu Feb 08 07:21:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.