############################################################################################
# Creating the spatial objects                                                             #
############################################################################################

let $count=100;
--echo #Creating the spatial Geometry object
USE test;


############################################################################################
# POINT: Check indexes for spatial data on same table with spatial index
############################################################################################

CREATE TABLE gis_point_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g POINT 
NOT NULL SRID 0,spatial index (g) ) engine=myisam;

let $i=$k;
while ($i)
{
  --eval INSERT INTO gis_point_with_spatial_index VALUES ($i,ST_PointFromText('POINT(100 10)'))
  dec $i;
}

select count(g) from gis_point_with_spatial_index where ST_AsText(g) like "%100 10%";
select ST_AsText(g) from gis_point_with_spatial_index where ST_AsText(g) like "%100 10%";

DROP TABLE gis_point_with_spatial_index;

############################################################################################
# LINESTRING: Check indexes for spatial data on same table with spatial index
############################################################################################

CREATE TABLE gis_line_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING 
NOT NULL SRID 0, spatial index (g) ) engine=myisam;

let $i=$k;
while ($i)
{
  --eval INSERT INTO gis_line_with_spatial_index VALUES ($i,ST_LineStringFromText('LINESTRING(100 10,10 100)'))
  dec $i;
}

select count(g) from gis_line_with_spatial_index where ST_AsText(g) like "%100 10%";
select ST_AsText(g) from gis_line_with_spatial_index where ST_AsText(g) like "%100 10%";

DROP TABLE gis_line_with_spatial_index;

############################################################################################
# POLYGON: Check indexes for spatial data on same table with spatial index
############################################################################################

CREATE TABLE gis_polygon_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON 
NOT NULL SRID 0, spatial index (g) ) engine=myisam;

let $i=$k;
while ($i)
{
  --eval INSERT INTO gis_polygon_with_spatial_index VALUES ($i,ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'))
  dec $i;
}

select count(g) from gis_polygon_with_spatial_index where ST_AsText(g) like "%10 10%";
select ST_AsText(g) from gis_polygon_with_spatial_index where ST_AsText(g) like "%10 10%";

DROP TABLE gis_polygon_with_spatial_index;

############################################################################################
# MULTIPOINT: Check indexes for spatial data on same table with spatial index
############################################################################################

CREATE TABLE gis_multipoint_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT
NOT NULL SRID 0, spatial index (g) ) engine=myisam;

let $i=$k;
while ($i)
{
   --eval INSERT INTO gis_multipoint_with_spatial_index VALUES ($i,ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)'))
  dec $i;
}

select count(g) from gis_multipoint_with_spatial_index where ST_AsText(g) like "%10 10%";
select ST_AsText(g) from gis_multipoint_with_spatial_index where ST_AsText(g) like "%10 10%";

DROP TABLE gis_multipoint_with_spatial_index;

############################################################################################
# MULTILINE: Check indexes for spatial data on same table with spatial index
############################################################################################

CREATE TABLE gis_multiline_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING
NOT NULL SRID 0, spatial index (g) ) engine=myisam;

let $i=$k;
while ($i)
{
   --eval INSERT INTO gis_multiline_with_spatial_index VALUES ($i,ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'))
  dec $i;
}

select count(g) from gis_multiline_with_spatial_index where ST_AsText(g) like "%10 0%";
select ST_AsText(g) from gis_multiline_with_spatial_index where ST_AsText(g) like "%10 0%";

DROP TABLE gis_multiline_with_spatial_index;

############################################################################################
# MULTIPOLYGON: Check indexes for spatial data on same table with spatial index
############################################################################################

CREATE TABLE gis_multipolygon_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON
NOT NULL SRID 0, spatial index (g) ) engine=myisam;

let $i=$k;
while ($i)
{
   --eval INSERT INTO gis_multipolygon_with_spatial_index VALUES ($i,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'))
  dec $i;
}

select count(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%10 10%";
select ST_AsText(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%10 10%";
select count(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%84 0%";
select ST_AsText(g) from gis_multipolygon_with_spatial_index where ST_AsText(g) like "%84 0%";

DROP TABLE gis_multipolygon_with_spatial_index;

############################################################################################
# GEOMETRY COLLECTION: Check indexes for spatial data on same table with spatial index
############################################################################################

CREATE TABLE gis_geometrycollection_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION 
NOT NULL SRID 0, spatial index (g) ) engine=myisam;

let $i=$k;
while ($i)
{
  --eval INSERT INTO gis_geometrycollection_with_spatial_index VALUES ($i,ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))'))
  dec $i;
}

select count(g) from gis_geometrycollection_with_spatial_index where ST_AsText(g) like "%10 10%";
select ST_AsText(g) from gis_geometrycollection_with_spatial_index where ST_AsText(g) like "%10 10%";

DROP TABLE gis_geometrycollection_with_spatial_index;

############################################################################################
# GEOMETRY COLLECTION: Check indexes for spatial data on same table with spatial index
############################################################################################

CREATE TABLE gis_geometry_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY 
NOT NULL SRID 0, spatial index (g) ) engine=myisam;

let $i=$k;
while ($i)
{
  --eval INSERT INTO gis_geometry_with_spatial_index VALUES ($i,ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))'))
  dec $i;
}

select count(g) from gis_geometry_with_spatial_index where ST_AsText(g) like "%10 10%";
select ST_AsText(g) from gis_geometry_with_spatial_index where ST_AsText(g) like "%10 10%";

DROP TABLE gis_geometry_with_spatial_index;

############################################################################################
# Check indexes for spatial data and normal data on same table with spatial index
############################################################################################
# Create some data for point table

CREATE TABLE gis_point_and_int_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING 
NOT NULL SRID 0,spatial index (g) ) engine=myisam;

let $i=$k;
while ($i)
{
  --eval INSERT INTO gis_point_and_int_with_spatial_index VALUES ($i,ST_PointFromText('POINT(100 10)'))
  dec $i;
}

select count(g) from gis_point_and_int_with_spatial_index where ST_AsText(g) like "%100 10%";
select ST_AsText(g) from gis_point_and_int_with_spatial_index where ST_AsText(g) like "%100 10%";

DROP TABLE gis_point_and_int_with_spatial_index;

############################################################################################
# Check indexes for spatial data and normal data on same table with spatial index
############################################################################################
CREATE TABLE gis_point_and_int_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g POINT 
NOT NULL SRID 0, a int,  spatial index (g) ) engine=myisam;
# Inserting the values specific to the spatial objects                                     #
--echo #INSERT valid entries to Point class
INSERT INTO gis_point_and_int_with_spatial_index VALUES
(101, ST_PointFromText('POINT(10 10)'),1),
(102, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))),21),
(103, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(100 20)'))),3),
(104, ST_GeomFromWKB(ST_AsWKB(ST_PointFromText('POINT(100 20)'))),3);

#check the index was created properly
SELECT COUNT(g) FROM gis_point_and_int_with_spatial_index  WHERE ST_AsText(g) LIKE "%point%";
#the count should be 4

#drop the index
ALTER TABLE gis_point_and_int_with_spatial_index DROP INDEX g;
CREATE SPATIAL INDEX g ON gis_point_and_int_with_spatial_index (g);
#check the index was created properly
SELECT COUNT(g) FROM gis_point_and_int_with_spatial_index  WHERE ST_AsText(g) LIKE "%point%";
#the count should be 4

#drop the spatial index
ALTER TABLE gis_point_and_int_with_spatial_index DROP INDEX g;
#create index on non-indexed non-spatial data
CREATE INDEX a ON gis_point_and_int_with_spatial_index (a) ;
#check for data consistency
select count(g) from gis_point_and_int_with_spatial_index  where a like "%3%";
select ST_AsText(g) from gis_point_and_int_with_spatial_index  where a like "%3%";

DROP TABLE gis_point_and_int_with_spatial_index;

############################################################################################
#POINT: Check indexes for spatial data and normal data on same table with spatial index
############################################################################################
CREATE TABLE gis_point_and_int_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g POINT 
NOT NULL SRID 0, a int,  index (a) ) engine=myisam;
# Inserting the values specific to the spatial objects                                     #
--echo #INSERT valid entries to Point class
INSERT INTO gis_point_and_int_with_spatial_index VALUES
(101, ST_PointFromText('POINT(10 10)'),1),
(102, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))),21),
(103, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(100 20)'))),3),
(104, ST_GeomFromWKB(ST_AsWKB(ST_PointFromText('POINT(101 20)'))),3);

#check the index was created properly
select count(g) from gis_point_and_int_with_spatial_index  where a like "%3%";
#the count should be 2
SELECT COUNT(a) FROM gis_point_and_int_with_spatial_index  WHERE a LIKE "%3%";
#the count should be 2

#drop the non spatial index and recreate the spatial index 
ALTER TABLE gis_point_and_int_with_spatial_index DROP INDEX a;
CREATE SPATIAL INDEX g ON gis_point_and_int_with_spatial_index (g);

#check the index was created properly
SELECT COUNT(g) FROM gis_point_and_int_with_spatial_index  WHERE ST_AsText(g) LIKE "%point%";
#the count should be 2
SELECT COUNT(a) FROM gis_point_and_int_with_spatial_index  WHERE ST_AsText(g) LIKE "%point%";
#the count should be 2

#create a spatial index on a non existing column
#CREATE SPATIAL INDEX g ON gis_point_and_int_with_spatial_index (b);

DROP TABLE gis_point_and_int_with_spatial_index ;

############################################################################################
#LINESTRING: Check indexes for spatial data and normal data on same table with spatial index
############################################################################################
CREATE TABLE gis_linestring_and_int_with_spatial_index  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING 
NOT NULL SRID 0, a int,  spatial index (g) ) engine=myisam;
# Inserting the values specific to the spatial objects                                     #
--echo #INSERT valid entries to Point class
INSERT INTO gis_linestring_and_int_with_spatial_index VALUES
(201,ST_LineFromText('LINESTRING(0 0,0 10,10 0)'),1),
(202,ST_LineFromText('LINESTRING(1 1,1 10,10 20)'),2),
(203,ST_LineFromText('LINESTRING(1 1,1 10,10 20)'),2);
#(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
#check the index was created properly
select count(g) from gis_linestring_and_int_with_spatial_index  where ST_AsText(g) like "%0%";
select count(a) from gis_linestring_and_int_with_spatial_index  where ST_AsText(g) like "%0%";
select count(a) from gis_linestring_and_int_with_spatial_index  where ST_AsText(g) like "%10 10%";

#drop the spatial index
ALTER TABLE gis_linestring_and_int_with_spatial_index DROP INDEX g;
#create index on non-indexed non-spatial data
CREATE INDEX a ON gis_linestring_and_int_with_spatial_index (a) ;

#check the index created on a
SELECT COUNT(g) FROM gis_linestring_and_int_with_spatial_index  WHERE a LIKE "%2%";

#drop the index
ALTER TABLE gis_linestring_and_int_with_spatial_index DROP INDEX a;
#create index on non-indexed spatial data
CREATE SPATIAL INDEX g ON gis_linestring_and_int_with_spatial_index (g) ;
#check the index created on a
SELECT COUNT(g) FROM gis_linestring_and_int_with_spatial_index  WHERE ST_AsText(g) LIKE "%2%";

DROP TABLE gis_linestring_and_int_with_spatial_index ;

############################################################################################
#MULTILINE: Check indexes for spatial data and normal data on same table with spatial index
############################################################################################
CREATE TABLE gis_multilinestring_and_int_with_spatial_index  (fid INTEGER PRIMARY KEY, g MULTILINESTRING 
NOT NULL SRID 0, a int,  spatial index (g) ) engine=myisam;
# Inserting the values specific to the spatial objects                                     #
--echo #INSERT valid entries to Point class
INSERT INTO gis_multilinestring_and_int_with_spatial_index VALUES
(300,ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'),1),
(301,ST_MultiLineStringFromText('MULTILINESTRING((130 48,180 21,10 90),(916 0,516 2373223,816 48))'),1),
(302,ST_MultiLineStringFromText('MULTILINESTRING((180 48,10 25891,160 0),(5416 0,19096 29093,19096 498))'),1);
#(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
#check the index was created properly
select count(g) from gis_multilinestring_and_int_with_spatial_index  where ST_AsText(g) like "%48%";
select count(a) from gis_multilinestring_and_int_with_spatial_index  where ST_AsText(g) like "%48%";


#drop the spatial index
ALTER TABLE gis_multilinestring_and_int_with_spatial_index DROP INDEX g;
#create index on non-indexed non-spatial data
CREATE INDEX a ON gis_multilinestring_and_int_with_spatial_index (a) ;

#check the index created on a
SELECT COUNT(g) FROM gis_multilinestring_and_int_with_spatial_index  WHERE a LIKE "%2%";

#drop the index
ALTER TABLE gis_multilinestring_and_int_with_spatial_index DROP INDEX a;
#create index on non-indexed spatial data
CREATE SPATIAL INDEX g ON gis_multilinestring_and_int_with_spatial_index (g) ;
#check the index created on a
SELECT COUNT(g) FROM gis_multilinestring_and_int_with_spatial_index  WHERE ST_AsText(g) LIKE "%48%";

DROP TABLE gis_multilinestring_and_int_with_spatial_index ;

############################################################################################
#POLYGON: Check indexes for spatial data and normal data on same table with spatial index
############################################################################################
CREATE TABLE gis_polygon_and_int_with_spatial_index  (fid INTEGER PRIMARY KEY, g POLYGON
NOT NULL SRID 0, a int,  spatial index (g) ) engine=myisam;
# Inserting the values specific to the spatial objects                                     #
--echo #INSERT valid entries to Point class
INSERT INTO gis_polygon_and_int_with_spatial_index VALUES
(300,ST_PolygonFromText('POLYGON((183670 10,2470 10,20 249380,10 254760,183670 10))'),12),
(301,ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'),2),
(302,ST_PolygonFromText('POLYGON((110 10,320 310,2550 8520,1059 2590,110 10))'),2),
(303,ST_PolygonFromText('POLYGON((180 160,55620 5610,240 206560,10 285960,180 160))'),2);
#(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
#check the index was created properly
select count(g) from gis_polygon_and_int_with_spatial_index  where ST_AsText(g) like "%10 10%";
select count(a) from gis_polygon_and_int_with_spatial_index  where ST_AsText(g) like "%10 10%";


#drop the spatial index
ALTER TABLE gis_polygon_and_int_with_spatial_index DROP INDEX g;
#create index on non-indexed non-spatial data
CREATE INDEX a ON gis_polygon_and_int_with_spatial_index (a) ;

#check the index created on a
SELECT COUNT(g) FROM gis_polygon_and_int_with_spatial_index  WHERE a LIKE "%2%";

#drop the index
ALTER TABLE gis_polygon_and_int_with_spatial_index DROP INDEX a;
#create index on non-indexed spatial data
CREATE SPATIAL INDEX g ON gis_polygon_and_int_with_spatial_index (g) ;
#check the index created on a
SELECT COUNT(g) FROM gis_polygon_and_int_with_spatial_index  WHERE ST_AsText(g) LIKE "%48%";

DROP TABLE gis_polygon_and_int_with_spatial_index ;

############################################################################################
#MULTIPOLYGON: Check indexes for spatial data and normal data on same table with spatial index
############################################################################################
CREATE TABLE gis_multipolygon_and_int_with_spatial_index  (fid INTEGER PRIMARY KEY, g multiPOLYGON
NOT NULL SRID 0, a int,  spatial index (g) ) engine=myisam;
# Inserting the values specific to the spatial objects                                     #
--echo #INSERT valid entries to Point class
INSERT INTO gis_multipolygon_and_int_with_spatial_index VALUES
(1,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1),
(2,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,864 0,8984 42,28 26),(2 18,66 348345723,73 9,48 6,2 18)),((519 18,67 18,348345767 13,59 13,519 18)))'),1),
(5,ST_MultiPolygonFromText('MULTIPOLYGON(((254397348 26,28 0,84 0,84 42,254397348 26),(52 18,634834576 23,73 9,48 34834576,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1),
(9,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,834834574 42,28 26),(52 18,348345766 23,73 9,434834578 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1);
#(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
#check the index was created properly
select count(g) from gis_multipolygon_and_int_with_spatial_index  where ST_AsText(g) like "%59 18%";
select count(a) from gis_multipolygon_and_int_with_spatial_index  where ST_AsText(g) like "%59 18%";


#drop the spatial index
ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX g;
#create index on non-indexed non-spatial data
CREATE INDEX a ON gis_multipolygon_and_int_with_spatial_index (a) ;

#check the index created on a
SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index  WHERE a LIKE "%1%";

#drop the index
ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX a;
#create index on non-indexed spatial data
CREATE SPATIAL INDEX g ON gis_multipolygon_and_int_with_spatial_index (g) ;
#check the index created on a
SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index  WHERE ST_AsText(g) LIKE "%18%";

############################################################################################
#Some erors around spatial index
############################################################################################
DROP TABLE gis_multipolygon_and_int_with_spatial_index ;
--error ER_SPATIAL_UNIQUE_INDEX
CREATE TABLE gis_multipolygon_and_int_with_spatial_index  (fid INTEGER NOT NULL, g MULTIPOLYGON PRIMARY KEY, a int) engine=myisam;

--error ER_SPATIAL_UNIQUE_INDEX
CREATE TABLE gis_multipolygon_and_int_with_spatial_index  (fid INTEGER NOT NULL, g MULTIPOLYGON, a int,  unique index (g) ) engine=myisam;

--error ER_SPATIAL_CANT_HAVE_NULL
CREATE TABLE gis_multipolygon_and_int_with_spatial_index  (fid INTEGER NOT NULL, g MULTIPOLYGON, a int,  spatial index (g) ) engine=myisam;

--error ER_SPATIAL_MUST_HAVE_GEOM_COL
CREATE TABLE gis_multipolygon_and_int_with_spatial_index  (fid INTEGER NOT NULL, g MULTIPOLYGON, a int,  spatial index (fid) ) engine=myisam;

CREATE TABLE gis_multipolygon_and_int_with_spatial_index  (fid INTEGER PRIMARY KEY, g MULTIPOLYGON
NOT NULL SRID 0, a int,  spatial index (g) ) engine=myisam;
# Inserting the values specific to the spatial objects                                     #
--echo #INSERT valid entries to Point class
INSERT INTO gis_multipolygon_and_int_with_spatial_index VALUES
(1,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1),
(2,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,864 0,8984 42,28 26),(2 18,66 348345723,73 9,48 6,2 18)),((519 18,67 18,348345767 13,59 13,519 18)))'),1),
(5,ST_MultiPolygonFromText('MULTIPOLYGON(((254397348 26,28 0,84 0,84 42,254397348 26),(52 18,634834576 23,73 9,48 34834576,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1),
(9,ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,834834574 42,28 26),(52 18,348345766 23,73 9,434834578 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),1);
#(204, LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10), Point(10, 10)))),4);
#check the index was created properly
select count(g) from gis_multipolygon_and_int_with_spatial_index  where ST_AsText(g) like "%59 18%";
select count(a) from gis_multipolygon_and_int_with_spatial_index  where ST_AsText(g) like "%59 18%";

#drop the spatial index
ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX g;
#create index on non-indexed non-spatial data
CREATE INDEX a ON gis_multipolygon_and_int_with_spatial_index (a) ;

#check the index created on a
SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index  WHERE a LIKE "%1%";

#drop the index
ALTER TABLE gis_multipolygon_and_int_with_spatial_index DROP INDEX a;
#create index on non-indexed spatial data
CREATE INDEX g ON gis_multipolygon_and_int_with_spatial_index (g) ;
DROP INDEX g ON gis_multipolygon_and_int_with_spatial_index;
#create index on non-indexed spatial data
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
CREATE SPATIAL INDEX a ON gis_multipolygon_and_int_with_spatial_index (a) ;
#create index on non-indexed spatial data
CREATE SPATIAL INDEX g ON gis_multipolygon_and_int_with_spatial_index (g) ;
#check the index created on a
SELECT COUNT(g) FROM gis_multipolygon_and_int_with_spatial_index  WHERE ST_AsText(g) LIKE "%18%";

DROP TABLE gis_multipolygon_and_int_with_spatial_index ;

--echo #
--echo # Bug #21087676 FORBID BUILDING NON-SPATIAL INDEX ON GEOMETRY COLUMNS
--echo #

--echo # Primary key on geometry column is not allowed.
--error ER_SPATIAL_UNIQUE_INDEX
CREATE TABLE t1 (g GEOMETRY SRID 0 PRIMARY KEY);
--error ER_SPATIAL_UNIQUE_INDEX
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL PRIMARY KEY);
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_SPATIAL_UNIQUE_INDEX
ALTER TABLE t1 ADD PRIMARY KEY (g);
DROP TABLE t1;

--echo # Unique index on geometry column is not allowed.
--error ER_SPATIAL_UNIQUE_INDEX
CREATE TABLE t1 (g GEOMETRY SRID 0 UNIQUE);
--error ER_SPATIAL_UNIQUE_INDEX
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL UNIQUE);
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_SPATIAL_UNIQUE_INDEX
CREATE UNIQUE INDEX g_idx ON t1(g);
--error ER_SPATIAL_UNIQUE_INDEX
ALTER TABLE t1 ADD UNIQUE INDEX (g);
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
--error ER_SPATIAL_UNIQUE_INDEX
CREATE UNIQUE INDEX g_idx ON t1(g);
--error ER_SPATIAL_UNIQUE_INDEX
ALTER TABLE t1 ADD UNIQUE INDEX (g);
DROP TABLE t1;

--echo # Prefix index on geometry column is not allowed.
--error ER_WRONG_SUB_KEY
CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g(10)));
--error ER_WRONG_SUB_KEY
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g(10)));
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_WRONG_SUB_KEY
CREATE INDEX g_idx ON t1(g(10));
--error ER_WRONG_SUB_KEY
ALTER TABLE t1 ADD INDEX (g(10));
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
--error ER_WRONG_SUB_KEY
CREATE INDEX g_idx ON t1(g(10));
--error ER_WRONG_SUB_KEY
ALTER TABLE t1 ADD INDEX (g(10));
DROP TABLE t1;

--echo # Multi-column index on geometry column is not allowed.
--error ER_TOO_MANY_KEY_PARTS
CREATE TABLE t1 (
  g1 GEOMETRY SRID 0 NOT NULL,
  g2 GEOMETRY SRID 0 NOT NULL,
  INDEX (g1, g2)
);
--error ER_TOO_MANY_KEY_PARTS
CREATE TABLE t1 (
  g1 GEOMETRY SRID 0,
  g2 GEOMETRY SRID 0,
  INDEX (g1, g2)
);
--error ER_TOO_MANY_KEY_PARTS
CREATE TABLE t1 (g GEOMETRY SRID 0, a INT, INDEX (g, a));
--error ER_TOO_MANY_KEY_PARTS
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, a INT, INDEX (g, a));
CREATE TABLE t1 (g1 GEOMETRY SRID 0, g2 GEOMETRY SRID 0, a INT);
--error ER_TOO_MANY_KEY_PARTS
CREATE INDEX g_idx ON t1(g1, g2);
--error ER_TOO_MANY_KEY_PARTS
CREATE INDEX g_idx ON t1(g1, a);
--error ER_TOO_MANY_KEY_PARTS
ALTER TABLE t1 ADD INDEX (g1, g2);
--error ER_TOO_MANY_KEY_PARTS
ALTER TABLE t1 ADD INDEX (g1, a);
DROP TABLE t1;
CREATE TABLE t1 (
  g1 GEOMETRY SRID 0 NOT NULL,
  g2 GEOMETRY SRID 0 NOT NULL,
  a INT
);
--error ER_TOO_MANY_KEY_PARTS
CREATE INDEX g_idx ON t1(g1, g2);
--error ER_TOO_MANY_KEY_PARTS
CREATE INDEX g_idx ON t1(g1, a);
--error ER_TOO_MANY_KEY_PARTS
ALTER TABLE t1 ADD INDEX (g1, g2);
--error ER_TOO_MANY_KEY_PARTS
ALTER TABLE t1 ADD INDEX (g1, a);
DROP TABLE t1;

--echo # Explicit B-tree index on geometry column is not allowed.
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g) USING BTREE);
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g) USING BTREE);
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
CREATE INDEX g_idx ON t1(g) USING BTREE;
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
ALTER TABLE t1 ADD INDEX (g) USING BTREE;
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
CREATE INDEX g_idx ON t1(g) USING BTREE;
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
ALTER TABLE t1 ADD INDEX (g) USING BTREE;
DROP TABLE t1;

--echo # Explicit hash index on geometry column is not allowed.
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g) USING HASH);
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g) USING HASH);
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
CREATE INDEX g_idx ON t1(g) USING HASH;
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
ALTER TABLE t1 ADD INDEX (g) USING HASH;
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
CREATE INDEX g_idx ON t1(g) USING HASH;
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
ALTER TABLE t1 ADD INDEX (g) USING HASH;
DROP TABLE t1;

--echo # Explicit ASC index on geometry column is not allowed.
# Without SPATIAL keyword.
--error ER_WRONG_USAGE
CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g ASC));
--error ER_WRONG_USAGE
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g ASC));
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_WRONG_USAGE
CREATE INDEX g_idx ON t1(g ASC);
--error ER_WRONG_USAGE
ALTER TABLE t1 ADD INDEX (g ASC);
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
--error ER_WRONG_USAGE
CREATE INDEX g_idx ON t1(g ASC);
--error ER_WRONG_USAGE
ALTER TABLE t1 ADD INDEX (g ASC);
DROP TABLE t1;
# With SPATIAL keyword.
--error ER_WRONG_USAGE
CREATE TABLE t1 (g GEOMETRY SRID 0, SPATIAL INDEX (g ASC));
--error ER_WRONG_USAGE
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, SPATIAL INDEX (g ASC));
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_WRONG_USAGE
CREATE SPATIAL INDEX g_idx ON t1(g ASC);
--error ER_WRONG_USAGE
ALTER TABLE t1 ADD SPATIAL INDEX (g ASC);
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
--error ER_WRONG_USAGE
CREATE SPATIAL INDEX g_idx ON t1(g ASC);
--error ER_WRONG_USAGE
ALTER TABLE t1 ADD SPATIAL INDEX (g ASC);
DROP TABLE t1;

--echo # Explicit DESC index on geometry column is not allowed.
# Without SPATIAL keyword.
--error ER_WRONG_USAGE
CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g DESC));
--error ER_WRONG_USAGE
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g DESC));
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_WRONG_USAGE
CREATE INDEX g_idx ON t1(g DESC);
--error ER_WRONG_USAGE
ALTER TABLE t1 ADD INDEX (g DESC);
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
--error ER_WRONG_USAGE
CREATE INDEX g_idx ON t1(g DESC);
--error ER_WRONG_USAGE
ALTER TABLE t1 ADD INDEX (g DESC);
DROP TABLE t1;
# With SPATIAL keyword.
--error ER_WRONG_USAGE
CREATE TABLE t1 (g GEOMETRY SRID 0, SPATIAL INDEX (g DESC));
--error ER_WRONG_USAGE
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, SPATIAL INDEX (g DESC));
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_WRONG_USAGE
CREATE SPATIAL INDEX g_idx ON t1(g DESC);
--error ER_WRONG_USAGE
ALTER TABLE t1 ADD SPATIAL INDEX (g DESC);
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
--error ER_WRONG_USAGE
CREATE SPATIAL INDEX g_idx ON t1(g DESC);
--error ER_WRONG_USAGE
ALTER TABLE t1 ADD SPATIAL INDEX (g DESC);
DROP TABLE t1;

--echo # Fulltext index on geometry column is not allowed.
--error ER_BAD_FT_COLUMN
CREATE TABLE t1 (g GEOMETRY SRID 0, FULLTEXT INDEX (g));
--error ER_BAD_FT_COLUMN
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, FULLTEXT INDEX (g));
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_BAD_FT_COLUMN
CREATE FULLTEXT INDEX g_idx ON t1(g);
--error ER_BAD_FT_COLUMN
ALTER TABLE t1 ADD FULLTEXT INDEX (g);
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
--error ER_BAD_FT_COLUMN
CREATE FULLTEXT INDEX g_idx ON t1(g);
--error ER_BAD_FT_COLUMN
ALTER TABLE t1 ADD FULLTEXT INDEX (g);
DROP TABLE t1;

--echo # Index on nullable geometry column is not allowed.
--error ER_SPATIAL_CANT_HAVE_NULL
CREATE TABLE t1 (g GEOMETRY SRID 0, INDEX (g));
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_SPATIAL_CANT_HAVE_NULL
CREATE INDEX g_idx ON t1(g);
--error ER_SPATIAL_CANT_HAVE_NULL
ALTER TABLE t1 ADD INDEX (g);
DROP TABLE t1;
--error ER_SPATIAL_CANT_HAVE_NULL
CREATE TABLE t1 (g GEOMETRY SRID 0, SPATIAL INDEX (g));
CREATE TABLE t1 (g GEOMETRY SRID 0);
--error ER_SPATIAL_CANT_HAVE_NULL
CREATE SPATIAL INDEX g_idx ON t1(g);
--error ER_SPATIAL_CANT_HAVE_NULL
ALTER TABLE t1 ADD SPATIAL INDEX (g);
DROP TABLE t1;

--echo # Index on non-nullable geometry column is allowed.
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g));
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
CREATE INDEX g_idx ON t1(g);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
ALTER TABLE t1 ADD INDEX (g), ALGORITHM=COPY;
SHOW CREATE TABLE t1;
DROP INDEX g ON t1;
ALTER TABLE t1 ADD INDEX (g), ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, SPATIAL INDEX (g));
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
CREATE SPATIAL INDEX g_idx ON t1(g);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
ALTER TABLE t1 ADD SPATIAL INDEX (g), ALGORITHM=COPY;
SHOW CREATE TABLE t1;
DROP INDEX g ON t1;
ALTER TABLE t1 ADD SPATIAL INDEX (g), ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
DROP TABLE t1;

--echo # Virtual generated geometry columns don't support spatial indexes.
CREATE TABLE t1 (x INTEGER,
                 y INTEGER,
                 g POINT AS (POINT(x, y)) VIRTUAL SRID 0 NOT NULL);
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
ALTER TABLE t1 ADD PRIMARY KEY (g);
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
CREATE INDEX g_idx ON t1(g);
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
CREATE INDEX g_idx ON t1(g(25));
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
CREATE INDEX g_idx ON t1(g) USING BTREE;
DROP TABLE t1;

--echo # Stored generated geometry columns follow the same rules as
--echo # non-generated columns.
CREATE TABLE t1 (x INTEGER,
                 y INTEGER,
                 g POINT AS (POINT(x, y)) STORED SRID 0 NOT NULL);
--error ER_SPATIAL_UNIQUE_INDEX
ALTER TABLE t1 ADD PRIMARY KEY (g);
CREATE INDEX g_idx ON t1(g);
DROP INDEX g_idx ON t1;
--error ER_WRONG_SUB_KEY
CREATE INDEX g_idx ON t1(g(25));
--error ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX
CREATE INDEX g_idx ON t1(g) USING BTREE;
DROP TABLE t1;

--echo #
--echo # Bug #27836608 INACCURATE ERROR THROWN WHILE CREATING SPATIAL INDEX OF
--echo # INVALID TYPE
--echo #

--echo # Explicit R-tree index on geometry column is allowed.
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL, INDEX (g) USING RTREE);
DROP TABLE t1;
CREATE TABLE t1 (g GEOMETRY SRID 0 NOT NULL);
CREATE INDEX g_idx ON t1(g) USING RTREE;
DROP INDEX g_idx ON t1;
ALTER TABLE t1 ADD INDEX (g) USING RTREE;
DROP TABLE t1;

--echo # Explicit R-tree index on non-gometry column is not allowed.
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
CREATE TABLE t1 (a INT NOT NULL, INDEX (a) USING RTREE);
CREATE TABLE t1 (a INT NOT NULL);
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
CREATE INDEX a_idx ON t1(a) USING RTREE;
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
ALTER TABLE t1 ADD INDEX (a) USING RTREE;
DROP TABLE t1;