#********************************************************
# wl6455: GIS Datatypes covers the following tests.
# DDL: ALTER PRIMARY, DROP KEY,INDEX,COLUMN TYPE etc
# INSERT/SELECT operations with WKT & WKB functions
# SELECT/DELETE/UPDATE with =,>,< predicate conditions
# Datatypes with Procedure (IN/INOUT/OUT,Cursors,Trigger)
# Datatypes with Aggregate functions
# File Formts Compressed & Dynamic
# All Geometric functions.
#********************************************************
--source include/have_geometry.inc
--source include/have_innodb.inc

SET default_storage_engine=InnoDB;
SET innodb_strict_mode=OFF;

#
# Spatial objects
#

CREATE TABLE gis_point  (fid INTEGER NOT NULL PRIMARY KEY, g POINT) ENGINE=InnoDB;
CREATE TABLE gis_line  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING) ENGINE=InnoDB;
CREATE TABLE gis_polygon   (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON) ENGINE=InnoDB;
CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT) ENGINE=InnoDB;
CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING) ENGINE=InnoDB;
CREATE TABLE gis_multi_polygon  (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON) ENGINE=InnoDB;
CREATE TABLE gis_geometrycollection  (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION) ENGINE=InnoDB;
CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY) ENGINE=InnoDB;

SHOW FIELDS FROM gis_point;
SHOW FIELDS FROM gis_line;
SHOW FIELDS FROM gis_polygon;
SHOW FIELDS FROM gis_multi_point;
SHOW FIELDS FROM gis_multi_line;
SHOW FIELDS FROM gis_multi_polygon;
SHOW FIELDS FROM gis_geometrycollection;
SHOW FIELDS FROM gis_geometry;

--echo #INSERT using all WKT & WKB functions
INSERT INTO gis_point VALUES
(101, ST_PointFromText('POINT(10 10)')),
(102, ST_PointFromText('POINT(20 10)')),
(103, ST_PointFromText('POINT(20 20)')),
(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));


INSERT INTO gis_line VALUES
(105, ST_LineFromText('LINESTRING(0 0,0 10,10 0)')),
(106, ST_LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
(107, ST_LineStringFromWKB(ST_AsWKB(LineString(Point(10, 10), Point(40, 10)))));


INSERT INTO gis_polygon VALUES
(108, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
(109, ST_PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
(110, ST_PolyFromWKB(ST_AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));


INSERT INTO gis_multi_point VALUES
(111, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
(112, ST_MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
(113, ST_MPointFromWKB(ST_AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));


INSERT INTO gis_multi_line VALUES
(114, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
(115, ST_MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
(116, ST_MLineFromWKB(ST_AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));


INSERT INTO gis_multi_polygon VALUES
(117, 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)))')),
(118, ST_MPolyFromText('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)))')),
(119, ST_MPolyFromWKB(ST_AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));


INSERT INTO gis_geometrycollection VALUES
(120, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
(121, ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(10 10,20 20))')),
(122, ST_GeometryFromWKB(ST_AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));

--echo #check INSERT using SELECT query
INSERT into gis_geometry SELECT * FROM gis_point;
INSERT into gis_geometry SELECT * FROM gis_line;
INSERT into gis_geometry SELECT * FROM gis_polygon;
INSERT into gis_geometry SELECT * FROM gis_multi_point;
INSERT into gis_geometry SELECT * FROM gis_multi_line;
INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
INSERT into gis_geometry SELECT * FROM gis_geometrycollection;

--echo #check format coversion functions & spatial values
SELECT fid, ST_AsText(g) FROM gis_point;
SELECT fid, ST_AsText(g) FROM gis_line;
SELECT fid, ST_AsText(g) FROM gis_polygon;
SELECT fid, ST_AsText(g) FROM gis_multi_point;
SELECT fid, ST_AsText(g) FROM gis_multi_line;
SELECT fid, ST_AsText(g) FROM gis_multi_polygon;
SELECT fid, ST_AsText(g) FROM gis_geometrycollection;
SELECT fid, ST_AsText(g) FROM gis_geometry;

--echo #2.1 check DDL functionality on GIS datatypes
CREATE TABLE tab(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB;

--echo #check information schema for all the columns refer to 14, except POINT which is 15
SELECT sc.name, sc.pos, sc.mtype
FROM information_schema.innodb_sys_columns sc
INNER JOIN information_schema.innodb_sys_tables st
ON sc.TABLE_ID=st.TABLE_ID
WHERE st.NAME='test/tab'
ORDER BY sc.name;

--echo #check Perform convesrion before INSERT using WKT functions
SET @c1=ST_PointFromText('POINT(10 10)');
SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
SET @c6=ST_MPolyFromText('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)))');
SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');

--echo #Insert the spatial values
INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);

--echo #check index on GIS datatypes
CREATE INDEX idx1 on tab(c2(5) DESC) USING BTREE;
CREATE INDEX idx3 on tab(c3(5) ASC) USING BTREE;
CREATE UNIQUE INDEX idx2 on tab(c8(5) ASC) ;

--echo #check equality predicate on the index columns
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');

--echo #check index with  WKB function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
FROM tab WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));

--echo #check index with  WKT function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');

--echo #check index with  WKB function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
FROM tab
WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));

--echo #check index with  WKT function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');

--echo #check index with  WKB function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
FROM tab
WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));

--echo #check range predicate on the index columns
--replace_column 10 #
--error ER_WRONG_ARGUMENTS
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');

--replace_column 10 #
--error ER_WRONG_ARGUMENTS
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
WHERE c3>=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');

--replace_column 10 #
--error ER_WRONG_ARGUMENTS
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab
WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');

--echo #check index with DELETE operation
--replace_column 10 #
EXPLAIN DELETE FROM tab
WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));


--echo #check the spatial values
SELECT ST_AsText(c1) FROM tab;
SELECT ST_AsText(c2) FROM tab;
SELECT ST_AsText(c3) FROM tab;
SELECT ST_AsText(c4) FROM tab;
SELECT ST_AsText(c5) FROM tab;
SELECT ST_AsText(c6) FROM tab;
SELECT ST_AsText(c7) FROM tab;
SELECT ST_AsText(c8) From tab;

--echo #check by modify the column type
ALTER TABLE tab MODIFY COLUMN c1 Geometry;
ALTER TABLE tab MODIFY COLUMN c2 Geometry;
ALTER TABLE tab MODIFY COLUMN c3 Geometry;
ALTER TABLE tab MODIFY COLUMN c4 Geometry;
ALTER TABLE tab MODIFY COLUMN c5 Geometry;
ALTER TABLE tab MODIFY COLUMN c6 Geometry;
ALTER TABLE tab MODIFY COLUMN c7 Geometry;

--echo #check column datatypes
SHOW FIELDS FROM tab;

--echo #check the data after modify
SELECT ST_AsText(c1) FROM tab;
SELECT ST_AsText(c2) FROM tab;
SELECT ST_AsText(c3) FROM tab;
SELECT ST_AsText(c4) FROM tab;
SELECT ST_AsText(c5) FROM tab;
SELECT ST_AsText(c6) FROM tab;
SELECT ST_AsText(c7) FROM tab;
SELECT ST_AsText(c8) From tab;

--echo #check by modify the column type
ALTER TABLE tab MODIFY COLUMN c1 POINT;
ALTER TABLE tab MODIFY COLUMN c2 LINESTRING;
ALTER TABLE tab MODIFY COLUMN c3 POLYGON;
ALTER TABLE tab MODIFY COLUMN c4 MULTIPOINT;
ALTER TABLE tab MODIFY COLUMN c5 MULTILINESTRING;
ALTER TABLE tab MODIFY COLUMN c6 MULTIPOLYGON;
ALTER TABLE tab MODIFY COLUMN c7 GEOMETRYCOLLECTION;

--echo #check column datatypes
SHOW FIELDS FROM tab;

--echo #check the data after modify
SELECT ST_AsText(c1) FROM tab;
SELECT ST_AsText(c2) FROM tab;
SELECT ST_AsText(c3) FROM tab;
SELECT ST_AsText(c4) FROM tab;
SELECT ST_AsText(c5) FROM tab;
SELECT ST_AsText(c6) FROM tab;
SELECT ST_AsText(c7) FROM tab;
SELECT ST_AsText(c8) From tab;

--echo #check change the column type
ALTER TABLE tab MODIFY COLUMN c1 BLOB;

--echo #check column datatypes
SHOW CREATE TABLE tab;

--echo #check the data after modify
SELECT ST_AsText(c1) FROM tab;

--echo #change the column name
ALTER TABLE tab CHANGE COLUMN c1 c0 GEOMETRY ;


--echo #check column datatypes
SHOW FIELDS FROM tab;

--echo #add primary key
ALTER TABLE tab ADD PRIMARY KEY pk2(c8(5));

--echo #check columns
SHOW FIELDS FROM tab;

--echo #drop key
ALTER TABLE tab DROP PRIMARY KEY;

--echo #check columns
SHOW FIELDS FROM tab;

--echo #cleanup the table
TRUNCATE TABLE tab;

--echo #check with procedures

delimiter |;

--echo #crate proc with INOUT params
CREATE PROCEDURE geom_insert(IN c1 POINT,IN c2 LINESTRING,IN c3 POLYGON,
IN c4 MULTIPOINT,IN c5 MULTILINESTRING, IN c6 MULTIPOLYGON,IN c7 GEOMETRYCOLLECTION,
IN c8 GEOMETRY)
BEGIN
INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
END |

delimiter ;|

--echo #set the spatial values
SET @c1=ST_PointFromText('POINT(10 10)');
SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
SET @c6=ST_MPolyFromText('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)))');
SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');

--echo #call the proc
CALL geom_insert(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);

--echo #check the values ofter proc call
SELECT ST_AsText(c0) FROM tab;
SELECT ST_AsText(c2) FROM tab;
SELECT ST_AsText(c3) FROM tab;
SELECT ST_AsText(c4) FROM tab;
SELECT ST_AsText(c5) FROM tab;
SELECT ST_AsText(c6) FROM tab;
SELECT ST_AsText(c7) FROM tab;
SELECT ST_AsText(c8) From tab;


--echo #set the input spatial value
SET @c9=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');

delimiter |;

--echo #crate a proc with INOUT params and pass a value.
CREATE PROCEDURE geominout(INOUT c9 GEOMETRY)
BEGIN

SELECT ST_AsText(@c9);
SET c9=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');

END|

delimiter ;|

--echo #call the proc and then change the spatial value of c8
CALL geominout(@c9);

--echo #now check the out spatial values should be changed to new values
SELECT ST_AsText(@c9);

#check GIS datatype with Triggers

--echo #delete the records
TRUNCATE TABLE tab;

--echo #create another table same as tab
CREATE TABLE tab2 AS SELECT * FROM tab;

--echo #check the table definition
SHOW CREATE TABLE tab2;


delimiter |;

--echo #create a tigger and populate the values into tab2
CREATE TRIGGER geom_trigger AFTER INSERT ON tab
FOR EACH ROW
BEGIN
  INSERT INTO tab2 VALUES (@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);
END|

delimiter ;|

--echo #set the spatial values
SET @c1=ST_PointFromText('POINT(10 10)');
SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
SET @c6=ST_MPolyFromText('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)))');
SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');

--echo #Insert the spatial values
INSERT INTO tab VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);

--echo #check the values whether populated
SELECT ST_AsText(c0) FROM tab2;
SELECT ST_AsText(c2) FROM tab2;
SELECT ST_AsText(c3) FROM tab2;
SELECT ST_AsText(c4) FROM tab2;
SELECT ST_AsText(c5) FROM tab2;
SELECT ST_AsText(c6) FROM tab2;
SELECT ST_AsText(c7) FROM tab2;
SELECT ST_AsText(c8) From tab2;

--echo #check Cursor with Geometry

delimiter |;

CREATE PROCEDURE geom_cursor()
BEGIN
  DECLARE v GEOMETRY;
  DECLARE c CURSOR FOR SELECT c8 FROM tab2;

  OPEN c;
  FETCH c INTO v;
  CLOSE c;

  SELECT ST_AsText(v);
END|

delimiter ;|

--echo # the following line was commented due the Bug#16282246
--echo # Once it is fixed, the comment will be removed.
--echo # right now bug fixing date is unknown.
#CALL geom_cursor();

--echo check self join
SELECT ST_AsText(a.c0),ST_AsText(a.c2),ST_AsText(a.c3)
FROM tab a,tab2 b WHERE a.c0=b.c0 AND a.c2=b.c2
AND a.c3=b.c3 AND a.c4=b.c4 AND a.c5=b.c5 AND a.c6=b.c6
AND a.c7=b.c7 AND a.c8=b.c8;

--echo #check equi join
SELECT ST_AsText(a.c2),ST_AsText(b.c2) FROM tab a,tab2 b WHERE a.c2=b.c2;

--echo #check DELETE stmt with Where clause and a constant predicate
DELETE FROM tab
Where c8=c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));

--echo #check the data, should 0
SELECT COUNT(*) FROM tab;

--echo #check UPDATE stmt with Where clause and a constant predicate
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(10 10),LineString(20 20, 30 30))');
UPDATE tab2 SET c8=@c8
WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));

--echo #check the column should be modified to LINESTRING(10 10,20 20,30 30)
SELECT ST_AsText(c8) From tab2;

--echo #check GIS datatypes with aggregate functions
SELECT COUNT(ST_AsText(g)) FROM gis_point;
SELECT COUNT(ST_AsText(g)) FROM gis_line;
SELECT COUNT(ST_AsText(g)) FROM gis_polygon;
SELECT COUNT(ST_AsText(g)) FROM gis_multi_point;
SELECT COUNT(ST_AsText(g)) FROM gis_multi_line;
SELECT COUNT(ST_AsText(g)) FROM gis_multi_polygon;
SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
SELECT COUNT(ST_AsText(g)) FROM gis_geometry;

SELECT SUM(ST_AsText(g)) FROM gis_point;
SELECT SUM(ST_AsText(g)) FROM gis_line;
SELECT AVG(ST_AsText(g)) FROM gis_polygon;
--echo #here it show some string value no meaning
SELECT MAX(ST_AsText(g)) FROM gis_multi_point;
--echo #here it show some string value no meaning
SELECT MIN(ST_AsText(g)) FROM gis_multi_line;
SELECT STD(ST_AsText(g)) FROM gis_multi_polygon;
SELECT COUNT(ST_AsText(g)) FROM gis_geometrycollection;
SELECT AVG(ST_AsText(g)) FROM gis_geometry;

--disable_warnings
--echo #check Datatypes with compression tables

CREATE TABLE tab3(c1 POINT,c2 LINESTRING,c3 POLYGON,C4 MULTIPOINT,c5 MULTILINESTRING ,
c6 MULTIPOLYGON,c7 GEOMETRYCOLLECTION ,c8 GEOMETRY) ENGINE=InnoDB
KEY_BLOCK_SIZE=8 ROW_FORMAT=COMPRESSED;

--echo #check file format
SHOW CREATE TABLE tab3;

ALTER TABLE tab3 KEY_BLOCK_SIZE=16 ROW_FORMAT=Dynamic;

--echo #check file format
SHOW CREATE TABLE tab3;

--echo #check information schema for all the columns refer to 14, except POINT which is 15
SELECT sc.name, sc.pos, sc.mtype
FROM information_schema.innodb_sys_columns sc
INNER JOIN information_schema.innodb_sys_tables st
ON sc.TABLE_ID=st.TABLE_ID
WHERE st.NAME='test/tab3'
ORDER BY sc.name;

--echo #check Perform convesrion before INSERT using WKT functions
SET @c1=ST_PointFromText('POINT(10 10)');
SET @c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');
SET @c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');
SET @c4=ST_MPointFromText('MULTIPOINT(0 0,5 5,10 10,20 20)');
SET @c5=ST_MLineFromText('MULTILINESTRING((1 1,2 2,3 3),(10 10,20 20,30 30))');
SET @c6=ST_MPolyFromText('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)))');
SET @c7=ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))');
SET @c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');

--echo #Insert the spatial values
INSERT INTO tab3 VALUES(@c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8);

--echo #check index on GIS datatypes
CREATE INDEX idx1 on tab3(c2(5) DESC) USING BTREE;
CREATE INDEX idx3 on tab3(c3(5) ASC) USING BTREE;
CREATE UNIQUE INDEX idx2 on tab3(c8(5) ASC) ;

--enable_warnings

--echo #check equality predicate on the index columns
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
WHERE c2=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');

--echo #check index with  WKB function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));

--echo #check index with  WKT function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');

--echo #check index with  WKB function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
FROM tab3
WHERE c3=ST_PolyFromWKB(ST_AsWkB(ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))')));

--echo #check index with  WKT function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
WHERE c8=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');

--echo #check index with  WKB function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
FROM tab3
WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));

--echo #check range predicate on the index columns
--replace_column 10 #
--error ER_WRONG_ARGUMENTS
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
WHERE c2>=ST_LineFromText('LINESTRING(10 10,20 20,30 30)');

--replace_column 10 #
--error ER_WRONG_ARGUMENTS
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
WHERE c3>=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');

--replace_column 10 #
--error ER_WRONG_ARGUMENTS
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
WHERE c8>=ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))');

--echo #check index with DELETE operation
--replace_column 10 #
EXPLAIN DELETE FROM tab3
WHERE c8=ST_GeometryFromWKB(ST_AsWKB(ST_GeomCollFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')));


--echo #check the spatial values
SELECT ST_AsText(c1) FROM tab3;
SELECT ST_AsText(c2) FROM tab3;
SELECT ST_AsText(c3) FROM tab3;
SELECT ST_AsText(c4) FROM tab3;
SELECT ST_AsText(c5) FROM tab3;
SELECT ST_AsText(c6) FROM tab3;
SELECT ST_AsText(c7) FROM tab3;
SELECT ST_AsText(c8) From tab3;

--echo #check with Row_format = Dynamic
--disable_warnings
ALTER TABLE tab3 ROW_FORMAT=Dynamic;
--enable_warnings

--echo #check file format
SHOW CREATE TABLE tab3;

--echo #check index with  WKB function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8)
FROM tab3 WHERE c2=ST_LineStringFromWKB(ST_AsWKB(ST_LineFromText('LINESTRING(10 10,20 20,30 30)')));

--echo #check index with  WKT function
--replace_column 10 #
EXPLAIN SELECT ST_ASText(c1),ST_AsText(c2),ST_AsText(c8) FROM tab3
WHERE c3=ST_PolyFromText('POLYGON((0 0,5 5,10 10,15 15,0 0),(10 10,20 20,30 30,40 40,10 10))');



--echo #check the Geometry property functions
SELECT fid, ST_Dimension(g) FROM gis_geometry;
SELECT fid, ST_GeometryType(g) FROM gis_geometry;
SELECT fid, ST_IsEmpty(g) FROM gis_geometry;
SELECT fid, ST_AsText(ST_Envelope(g)) FROM gis_geometry;
--replace_column 10 #
explain extended select ST_Dimension(g), ST_GeometryType(g), ST_IsEmpty(g), ST_AsText(ST_Envelope(g)) from gis_geometry;

--echo #check Geometry point functions
SELECT fid, ST_X(g) FROM gis_point;
SELECT fid, ST_Y(g) FROM gis_point;
--replace_column 10 #
explain extended select ST_X(g),ST_Y(g) FROM gis_point;

SELECT fid, ST_AsText(ST_StartPoint(g)) FROM gis_line;
SELECT fid, ST_AsText(ST_EndPoint(g)) FROM gis_line;
SELECT fid, ST_Length(g) FROM gis_line;
SELECT fid, ST_NumPoints(g) FROM gis_line;
SELECT fid, ST_AsText(ST_PointN(g, 2)) FROM gis_line;
SELECT fid, ST_IsClosed(g) FROM gis_line;
--replace_column 10 #
explain extended select ST_AsText(ST_StartPoint(g)),ST_AsText(ST_EndPoint(g)),
ST_Length(g),ST_NumPoints(g),ST_AsText(ST_PointN(g, 2)),ST_IsClosed(g) FROM gis_line;

SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_polygon;
SELECT fid, ST_Area(g) FROM gis_polygon;
SELECT fid, ST_AsText(ST_ExteriorRing(g)) FROM gis_polygon;
SELECT fid, ST_NumInteriorRings(g) FROM gis_polygon;
SELECT fid, ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;
--replace_column 10 #
explain extended select ST_AsText(ST_Centroid(g)),ST_Area(g),ST_AsText(ST_ExteriorRing(g)),
ST_NumInteriorRings(g),ST_AsText(ST_InteriorRingN(g, 1)) FROM gis_polygon;

SELECT fid, ST_IsClosed(g) FROM gis_multi_line;

SELECT fid, ST_AsText(ST_Centroid(g)) FROM gis_multi_polygon;
SELECT fid, ST_Area(g) FROM gis_multi_polygon;

SELECT fid, ST_NumGeometries(g) from gis_multi_point;
SELECT fid, ST_NumGeometries(g) from gis_multi_line;
SELECT fid, ST_NumGeometries(g) from gis_multi_polygon;
SELECT fid, ST_NumGeometries(g) from gis_geometrycollection;
--replace_column 10 #
explain extended SELECT fid, ST_NumGeometries(g) from gis_multi_point;

SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;
SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_line;
SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_polygon;
SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_geometrycollection;
SELECT fid, ST_AsText(ST_GeometryN(g, 1)) from gis_geometrycollection;
--replace_column 10 #
explain extended SELECT fid, ST_AsText(ST_GeometryN(g, 2)) from gis_multi_point;

SELECT g1.fid as first, g2.fid as second,
MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
--replace_column 10 #
explain extended SELECT g1.fid as first, g2.fid as second,
MBRWithin(g1.g, g2.g) as w, MBRContains(g1.g, g2.g) as c, MBROverlaps(g1.g, g2.g) as o,
MBREquals(g1.g, g2.g) as e, MBRDisjoint(g1.g, g2.g) as d, ST_Touches(g1.g, g2.g) as t,
MBRIntersects(g1.g, g2.g) as i, ST_Crosses(g1.g, g2.g) as r
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;

--echo # check support of Foreign Key constraint
CREATE TABLE parent (id GEOMETRY NOT NULL,PRIMARY KEY (id(10))) ENGINE=INNODB;

--error 1215
CREATE TABLE child (id GEOMETRY, parent_id GEOMETRY,
                    INDEX par_ind (parent_id(10)),
                    FOREIGN KEY (parent_id(10)) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

--echo #check partition table support
--error 1178
CREATE TABLE emp2(
    id GEOMETRY NOT NULL,
    store_name VARCHAR(30),
    parts VARCHAR(30),
    store_id GEOMETRY
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (10,20,30) ,
    PARTITION pEast VALUES IN (40,50,60) ,
    PARTITION pWest VALUES IN (70,80,100)
);

--echo #check start transaction commit & Rollback
START TRANSACTION;
DELETE FROM tab3;
ROLLBACK;

SELECT COUNT(*) FROM tab3;

START TRANSACTION;
DELETE FROM tab3;
COMMIT;

SELECT COUNT(*) FROM tab3;

DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point;
DROP TABLE gis_multi_line, gis_multi_polygon;
DROP TABLE gis_geometrycollection, gis_geometry;
DROP TABLE tab,tab2,tab3,parent,emp2;
DROP PROCEDURE geominout;
DROP PROCEDURE geom_insert;
DROP PROCEDURE geom_cursor;